Some techniques Help you to test the connection of SQL Server between the Servers

Sometimes I faced problems while installing SQL Server and Configuration the Farm and these problems happened between the connection of SQL Server and frontend servers. These problems sometimes related to ports , firewall or these server internally and the others in DMZ etc… so these are some techniques will help you to test the connection and find out which port is used by SQL server.



For example to check which port currently used by SQL Server you have many options:

1.  Go to regedit and the registry key for SQL Server instance name

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQLServer\SuperSocketNetLib\Tcp\IPAll

Note: there are two different type of ports in SQL server:

a. Static port: by this option you select specific port and always SQL Server uses this port to listen to the connection.
b. Dynamic port: by this option each time SQL Server restarted it will assign a new port if the last used port closed or not available.

2.Go to SQL Server Configuration Manager >> SQL Server Network Configuration >> Protocols for [Instance Name] >> TCP/IP then right click and select properties

3.Use this Query

use master

go

Xp_readerrorlog

4.Use this Query (this query return information about the connection established to this instance of SQL Server)

select net_transport,local_tcp_port from sys.dm_exec_connections

5.Go to command prompt and run this command which return the list of listened and established prots


netstat –a

6.To test the connection between the servers for example login to frontend server to see this server can connect to SQL server .Go to Control Panel\All Control Panel Items\Administrative Tools and click on Data Sources (ODBC) and try to connect to the instance name.

7.To test the connection also you can use the following steps:
    a. Create abc.udl text file (extension .udl)
    b.Double click to this file and then select the provider and enter the connection information

Last notes:

1. when you test the connection, port... first check it locally in SQL Server server and make sure you can connect to SQL Server
2. when you install SQL Server the default port used is 1433.
3. try to check if you can connect to SQL Server from other server internally or externally and if you face any problems always check the connectivity between the servers and the firewall.
4. there are also other options to check the ports and the connection between the server like telnet ,look@me and other third party tools.

System.Data.SqlClient.SqlError -When Restoring a Database to SQL Server 2008

Today my friend tried to restore a database backup to SQL Server 2008 and he got the following error

Msg 3176, Level 16, State 1, Line 1 File 'D:\SQL Server 2008 DBs\test01.mdf' is claimed by 'SCTA_ORG_SECOND'(3) and 'SCTA_ORGANIZATION'(1). The WITH MOVE clause can be used to relocate one or more files.

Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally

let's resolve this error then we will explain the reason of this error so at the same screen used for restoring database of Microsoft SQL Server Management Studio 

select Script Action to New Query Window as the above image so you will get the following T-SQL and here we will know the reason

RESTORE DATABASE [test01] FROM  DISK = N'D:\SCTA_Org2.bak' WITH  FILE = 1,
    MOVE N'SCTA_ORGANIZATION' TO N'D:\SQL Server 2008 DBs\test01.mdf',
    MOVE N'SCTA_ORG_SECOND' TO N'D:\SQL Server 2008 DBs\test01.MDF',
    MOVE N'SCTA_ORGANIZATION_log' TO N'D:\SQL Server 2008 DBs\test01_1.ldf',
    NOUNLOAD,  STATS = 10
GO

so you will notice there are two files of mdf with the same name so just change the name of second one to test02 or to test01.ndf ( different extension) then run the command and it's successfully restored.

so the logical answer for this error first test01.mdf is a primary data file and the second is the secondary data file but the extension and name are same so that way you have to change the name or extension of second file with any other name or extension.

Note: the extension is anything ( it can be .fad or .ndf but .ndf is best practice to determine what this file for for example .ldf for log file , .ndf  for secondary data files ..).

Finally : I think the original database backup come from SQL Server 2000 and maybe this behavior allowed in SQL Server 2000 or the name is a case sensitive ( test01.mdf not like test01.MDF).

Moving tempdb to new clustered disk with SQL Server 2008

If you create new clustered disk for tempdb database and you want to move the current tempdb database to this new clustered disk you have to do the following:
so after you add new storage to SQL Server services group

and this disk for example label as I: so if you try to run the following command:

USE master
GO

ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'I:\MSSQL10_50.SQL2008\MSSQL\DATA\tempdb.mdf')
GO

ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'I:\MSSQL10_50.SQL2008\MSSQL\Data\templog.ldf')
GO

you will get this error

Msg 5184, Level 16, State 1, Line 1
Cannot use file 'I:\MSSQL10_50.SQL2008\MSSQL\DATA\tempdb.mdf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.

Msg 5184, Level 16, State 1, Line 1
Cannot use file 'I:\MSSQL10_50.SQL2008\MSSQL\Data\templog.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it.
 
so to resovle the above error you have to add the new Clustered disk to dependencies of SQL Server resource

so go to Other Resources and Right Click on Sql Server instance resource and then go to dependencies
and then add a Dependency and choose Clustered disk (I:)

Now again re-run the T-Sql statement and then restart the SQL Server instance serivce.

NOTE: SQL Server will create new tempdb database in the new place so you have to go to the old space where the original files of tempdb database and remove it.

Done.

Regular expressions with SQL Server

All of us know the importance of using regular expressions to filter your results or check some specific rules if it exists or not for example to check if the inserted email in the correct format or to check if the inserted string has numbers or not ..Etc and of course you can use regular expressions in many ways with Microsoft technologies especially with SQL Server for example you can write CLR function or write it in your T-SQL code.

I will show you some examples of using Regular expressions with Arabic language in SQL Server:

--In this example we want to exclude from the results
--any Name has any charts excpet the arabic charts
DECLARE @Students TABLE (Name nvarchar(20))

INSERT @Students VALUES
(N'فادي'),
(N'أحمد ! فادي'),
(N'فادي أحمد عبدالوهابabc')

SELECT Name FROM @Students 
            WHERE Name Not Like N'%[^أ-ي]%'

And the result of the above query as the following:
Name
------------------------------
فادي

So the above result only returns the first inserted row because it's only the Name has Arabic charts only.


Note: PatIndex function Returns the starting position of the first occurrence of a pattern in a specified expression

--In this example we will see the power or PatIndex
--so we want to extract the arabic charts only
--from a sentense has arabic and english word
DECLARE @NAME nvarchar(50)
SET @NAME = N'Fadi فادي أحمد عبدالوهاب'

SELECT Substring(@Name,PATINDEX(N'%[أ-ي]%',@NAME),5000)

And the result of the above query as the following :
--------------------------------------------------
فادي أحمد عبدالوهاب

And the last example in .net code by using CLR fucntion

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString fnVerfiyIsContainsArabic(string input)
    {
        //First Define an Object from Regex and pass your Pattern
        System.Text.RegularExpressions.Regex regObj = 
  new System.Text.RegularExpressions.Regex("[^أ-ي]");

        //Second check if the input text Is match the pattern or not
        bool flag = regObj.IsMatch(input);
        if (flag)
            //if it's matching print the following text 
            return "It's not only Contains Arabic Charts :" + input;
        else
            //if it's not matching print the following text
            return "It's only Contains Arabic Charts :" + input;
    }
};

Then run the following queries:

I hope you get some benefits from reading this article and good luck.

Using ALL, Some and Any with Sub Queries

In case a sub Query return a single value , here you can write a normal query without using comparison operators like the following an example:

DECLARE @Employees Table (Id int , Name varchar(50) , Salary int)
INSERT INTO @Employees 
SELECT 1 ,'Fadi'  ,  10000  UNION ALL 
SELECT 2 ,'Shadi' ,  20000  UNION ALL 
SELECT 3 ,'Tareq' ,  30000  UNION ALL 
SELECT 4 ,'Rola'  ,  40000  UNION ALL 
SELECT 5 ,'Rana'  ,  50000  UNION ALL 
SELECT 6 ,'Rasha' ,  60000

-- we will use this query as sub Query return a single value
-- it's return the Average of Salary
SELECT AVG(Salary) FROM @Employees

SELECT * FROM @Employees
WHERE Salary > (SELECT AVG(Salary) FROM @Employees)

 The result of the above query:

35000


Id          Name                                               Salary
----------- -------------------------------------------------- -----------
4           Rola                                               40000
5           Rana                                               50000
6           Rasha                                              60000

What if your Sub Query returns a list of values, in this case you will need to use the comparison operators to compare each value return by a sub query so to simplify these queries let's see the benefits of ALL , Any and Some.

Let's start by ALL by this example:

-- we will use this query as sub Query 
SELECT Salary FROM @Employees WHERE Salary < 40000

SELECT * FROM @Employees
WHERE  Salary > ALL 
(SELECT Salary FROM @Employees WHERE Salary < 40000)
Salary
-----------
10000
20000
30000


Id          Name                                               Salary
----------- -------------------------------------------------- -----------
4           Rola                                               40000
5           Rana                                               50000
6           Rasha                                              60000

ALL keyword equal to AND comparison operator so it means the values return by a Query must be Salary > 30000 and Salary > 20000 and Salary > 10000.

Note: if the sub query return no rows, a comparison always true so means it will return all rows.

--we will use this query as sub Query 
-- this query will return no rows
SELECT Salary FROM @Employees WHERE Salary > 80000

SELECT * FROM @Employees
WHERE  Salary > ALL (SELECT Salary FROM @Employees WHERE Salary > 80000)
Salary
-----------


Id          Name                                               Salary
----------- -------------------------------------------------- -----------
1           Fadi                                               10000
2           Shadi                                              20000
3           Tareq                                              30000
4           Rola                                               40000
5           Rana                                               50000
6           Rasha                                              60000

ANY key word equal to OR comparison operator so it means the values return by a Query must be greater than one on any values 30000, 20000 and 10000.

Note: SOME is equal to ANY but SOME (ANSI Standard keyword).

--any one condition true return the value
SELECT * FROM @Employees
WHERE  Salary > ANY (SELECT Salary FROM @Employees WHERE Salary < 40000)

--any one condition true return the value
SELECT * FROM @Employees
WHERE  Salary > SOME (SELECT Salary FROM @Employees WHERE Salary < 40000)
Id          Name                                               Salary
----------- -------------------------------------------------- -----------
2           Shadi                                              20000
3           Tareq                                              30000
4           Rola                                               40000
5           Rana                                               50000
6           Rasha                                              60000

Note: if the sub query return no rows or null value, a comparison always false so means it will return nothing.

SELECT * FROM @Employees
WHERE  Salary > ANY (SELECT Salary FROM @Employees WHERE Salary > 80000)
Id          Name                                               Salary
----------- -------------------------------------------------- -----------

COALESCE and ISNULL functions

Both of these functions are similar which allow you to replace a null value of column with any expression belong to the same data type of this column except in one point, COALESCE has ability in case you need to pass a list of expressions to be replaced when the value comes NULL.

Let's see these examples:

DECLARE @Table Table
        (Col1 VARCHAR(10) NULL ,
         Col2 INT NULL,
         Col3 INT NULL)

INSERT INTO @Table
SELECT  'Microsoft',1,100 UNION ALL
SELECT  NULL,2,200        UNION ALL
SELECT  NULL,NULL,300     UNION ALL
SELECT  NULL,NULL,NULL 

--Both Functions are similar In Case of One Expression 
SELECT Col1 , ISNULL(Col1,'No Value') AS [Col1 with ISNULL] 
FROM @Table
SELECT Col1 , COALESCE(Col1,'No Value') AS [Col1 with COALESCE] 
FROM @Table

--COALESCE allows you to pass a list of expressions.
--When first Expression (in this case Col2 ) is null then 
--it will take the next expression (in this case Col3) and 
--replaced with Null value and so on.
SELECT Col1 , COALESCE(Col1,CAST(Col2 AS VARCHAR(10)),
                            CAST(Col3 AS VARCHAR(10))) AS [List of expressions wiht COALESCE] FROM @Table

A SELECT statement with TOP clause options

TOP clause can limits the numbers of rows return by the SELECT query .There are three options used with TOP clause in the SELECT Query:

1. TOP with number of rows
2. TOP with percentage of rows
3. TOP with ties

So let's see these examples to demonstrate the options:

USE AdventureWorks
GO

-- To return only 2 rows from the result set 
SELECT TOP 2 Name , ProductNumber ,ListPrice
FROM Production.Product
ORDER BY ListPrice

--TO return only the 2% from the result set 
SELECT TOP 2 PERCENT Name , ProductNumber ,ListPrice
FROM Production.Product
ORDER BY ListPrice

-- TO return only 2 rows from the result set 
-- plus : extra rows will returned if their values
-- (Based on ORDER BY column) match the vlaue of last row
SELECT TOP 2 WITH TIES Name , ProductNumber ,ListPrice
FROM Production.Product
ORDER BY ListPrice DESC

Attach MDF file in case of missing LDF file

In case you have a database with .mdf file and without having the .ldf file of a database so to restore your database or reattach it again do the following:
First open SSMS and right click in Database folder in object explorer and choose Attach….

Then click on Add button to add .mdf file of a database you need to attach it as the following

Note the Message column of .ldf file shows (Not Found) so select this row and then remove it as the following

Then press OK to reattach your database with new .ldf file.
Or use the following command to attach database file with only .mdf file:

USE [master]
GO
CREATE DATABASE [TestDb] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\TestDb.mdf' )
 FOR ATTACH
GO

and you will get the following result:
File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\TestDb_log.ldf" may be incorrect.

New log file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\TestDb_log.LDF' was created.

SQL Server - Case Sensivity and Accent sensivity determined by a Collation

Sql server 2005/2008 support Unicode characters so you can present your data in any language so for example if your Database collation is (a Latin) and you want to store Arabic language words you need to define a Unicode data type like(nchar,nvarchar,ntext) to stored your data properly and to avoid the question marks problem (???) except if your database collation is Arabic and in this case you does not need to define your columns as a Unicode type you can use the type like(char,varchar,text) to store Arabic language because in this case the collation is responsible to display your character language.
Note:
SQL Server 2005/2008 allows you to make collation choices at the server, database, column, and expression level.
Case Sensivity and accent sensivity determined by a Collation so let's see how we can define the case sensitive and accent sensitive by using collation for two main languages Arabic and English.

--This example show the Case sensitive for English Language
DECLARE @Name1 varchar(25) ='SQL SERVER' 
       ,@Name2 varchar(25) ='sql server' 

--Here do not consider the Case sensitive        
IF(@Name1 COLLATE SQL_Latin1_General_Cp1_CI_AS = @Name2 COLLATE SQL_Latin1_General_Cp1_CI_AS)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

--Here do consider the Case sensitive
IF(@Name1 COLLATE SQL_Latin1_General_Cp1_CS_AS = @Name2 COLLATE SQL_Latin1_General_Cp1_CS_AS)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

the result of the above script as follwoing:
They are the same
They are not the same

--This example show the Accent Sensitive for Arabic language
DECLARE @Name3 nvarchar(25) ='أ' 
       ,@Name4 nvarchar(25) ='آ' -- try to change it to ؤ – ئ – إ –ء-

--Here do not consider the Accent Sensitivity       
IF(@Name3 COLLATE Arabic_CS_AI= @Name4 COLLATE Arabic_CS_AI)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

--Here do consider the Accent Sensitivity
IF(@Name3 COLLATE Arabic_CS_AS= @Name4 COLLATE Arabic_CS_AS)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

the result of the above script as follwoing:
They are the same
They are not the same

SQL Server and short tips – 3

• Sql server Express 2005/2008 utilizes max 1 GB for CPU as well as for Ram and 4 GB for max database size.
• statistics about SQL Server 2008 :
• Max Data storage per database (524272 TB)
• Max Concurrent User Connection (32767)
• Max Databases per server (32767)
• Max tables per database (2147483647)
• Max columns per table (1024)
• To hide the system database in Object Explorer: SSMS >Tools>Options>Hide system objects in Object Explorer then restart Sql Server.
• Keep in your administrative tools, Activity Monitor can offer to you the Recent Expensive queries.
• To see how many pages ,extents and the extent Switches in your database tables use the following command: DBCC SHOWCONTIG
• Case sensitivity in SQL Server:

--SQL Server 2005/2008 allows you to make collation choices 
--at the server, database, column, and expression levels
--Case Sensivity determined by Collation
--Oracle, by default is case sensitive whereas 
--SQL Server installations by default are case-insensitive

DECLARE @Name1 varchar(25) ='SQL SERVER' 
       ,@Name2 varchar(25) ='sql server' 

--Here do not consider the Case Sensitivity       
IF(@Name1 COLLATE SQL_Latin1_General_Cp1_CI_AS= @Name2 COLLATE SQL_Latin1_General_Cp1_CI_AS)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

--Here do consider the Case Sensitivity
IF(@Name1 COLLATE SQL_Latin1_General_Cp1_CS_AS= @Name2 COLLATE SQL_Latin1_General_Cp1_CS_AS)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')   
-------------------------------------------------------------------- 
DECLARE @Name3 nvarchar(25) ='أ' 
       ,@Name4 nvarchar(25) ='إ' --try to change it to ء – آ - ؤ -ئ

--Here do not consider the Accent Sensitivity       
IF(@Name3 COLLATE Arabic_CI_AI= @Name4 COLLATE Arabic_CI_AI)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

--Here do consider the Accent Sensitivity
IF(@Name3 COLLATE Arabic_CS_AS= @Name4 COLLATE Arabic_CS_AS)
    RAISERROR('They are %s',10,1,'the same')
ELSE
    RAISERROR('They are %s',10,0,'not the same')

Cannot resolve collation conflict for column 1 in SELECT statement

My friend has faced this problem against some queries so the cause of this problem as described in above title related to the Collation.

He has a table with columns with different collation at Column level for example a Table called "Students" with "FirstName" and "LastName" columns and each of these columns has a different collation so the "FirstName with SQL_Latin1_General_CP1_CI_AI collation " and "LastName with Arabic collation" and when he write the following query he got an error:

SELECT FirstName + ' ' +LastName
FROM dbo.Students

The solution for this problem is easy so rewrite the above query as following:

SELECT FirstName + ' ' + LastName  COLLATE SQL_Latin1_General_CP1_CI_AI LastName
FROM dbo.Students 

As well as if you try to write a query as the following:

SELECT * FROM dbo.Students 
WHERE FirstName = LastName

You will get the following error:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Arabic_CI_AS" and "SQL_Latin1_General_CP1_CI_AI" in the equal to operation

To fix it, rewrite the above query as following:

SELECT * FROM dbo.Students 
WHERE FirstName = LastName COLLATE SQL_Latin1_General_CP1_CI_AI

SQL Server and short tips – 2

• Sqlcmd utility comes in replacement of osql utility (with SQL 2000 version) and isql utility (with SQL 7.0 version) but still you can run osql with SQL Server 2005/2008.
• Sqlcmd used to reach the sql server instance from outside of SSMS or GUI for repetitive tasks or automate the execution of SQL scripts.
• To see all commands of sqlcmd use the following: sqlcmd /?
• You can access the instance of SQL Server as Administrator and only one connection allowed for troubleshoot problems even if the server does not responses to any client connections.

Let's see this example which passes a variable from the sqlcmd to sql script file

1. Create in c:\script.sql file
2. Write in this file the following command:

USE AdventureWorks
GO
SELECT $(ColumnName) FROM $(TableName)
GO

3. Open cmd window then run this command

sqlcmd -S .\sql2008 -E -i c:\script.sql -v ColumnName="ProductNumber" TableName="Production.Product"

And then press enter.

• You can shut down your instance (stop the instance service) by using the following command:

After connection has made, write the following:

Shutdown with nowait

• And last tip is a Security in Sql 2005/2008 is inherited in Hierarchical scope.

SQL Server and short tips – 1

• SQL Server is a backend system and its size limited by the Hard disk.
• SQL Server History:
• SQL 4.2 (oct 92).
• SQL 6.0 (jun 95).
• SQL 6.5(Apr 96).
• SQL 7.0(jan 99).
• SQL 2000(Aug 2000).
• SQL 2005(Nov 2005).
• SQL 2008(Aug 2008).
•  T-SQL is a Interpreted language and it’s not a full programming language.T-SQL comes from the SQL ANSI Standard language like PL,Postgre and MySQL.
• Sql Server 2005 express work only on 32 bit but Sql Server 2008 express work on 32/64 bit
• you can upgrade from SQL Server 2005/2008 Developer Edition to Enterprise edition
• You can run Sql 2005 or SQL 2008 side by side with earlier version but you need to consider the order of the installation and likewise you can use the Default instance name in different version.
• You can upgrade from SQL 2000(sp3) to SQL 2005 or 2008 but if you want to update earlier version before 2000 you have to upgrade it to SQL 2000 then to SQL 2005 or 2008.
• Database compatibility Level:
•  60 for SQL 6.0
• 65 for SQL 6.5
• 70 for SQL 7.0
• 80 for SQL 2000
• 90 for SQL 2005
• 100 for SQL 2008

So what is Database compatibility level?
Database compatibility represents the current version of Database which uses the specific version features or syntax that means for example some statements work fine with SQL Server 2000 and does not work with SQL 2005 so to run this statement in SQL Server 2005 keep the Database compatibility in 80 level.

You can change the database compatibility from SSMS or by using sp_dbcmptlevel system stored procedure.

• What about if I want to save my session with current Connections, Queries and other files so I can return back to it later on, so we can use a Solution in SSMS.

Let's see this example:

1. Open you SSMS and then go to File>New>Project and choose SQL Server Scripts
2. Open Solution Explorer and start your work so connect to the specific instance and run some queries even add some files as references and so on.

SQL Server and Arabic language

I have read some articles written about how this products Office 2010 by default support not only Hijri Date but also UmAlQura Date and this good news.

This article show you some points related to Arabic language (which is my mother tongue) with Sql Server.

In general Sql server deals with any language in the same way except some issues become different from language to other language.
So let's begin by Collation and see how Sql Server stored data and retrieve data in Arabic language.

What is a Collation?

A collation determines the rules SQL Server uses to compare and sort character data and determines which characters can be stored in non-Unicode character data types. Collation can be specified at the instance level, database, column level or clause level.

So let's begin by this example to make the points clear:

CREATE TABLE [dbo].[Test](
--here stored data as ASCII char
    [Name_Char_Latain] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--here stored data as Unicode
    [Name_nChar_Latain] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
--here stored data as ASCII char
    [Name_Char_Arabic] [char](10) COLLATE Arabic_CI_AI NULL,
--here stored data as Unicode
    [Name_nChar_Arabic] [nchar](10) COLLATE Arabic_CI_AI NULL
) ON [PRIMARY]

GO

If your database collation is SQL_Latin1_General_CP1_CI_AS and you tried to insert Arabic data you will get ??? Characters so there are two solutions for these problems: Change database collation or use N' '

You can change database collation as following:

USE [master]
GO
ALTER DATABASE [TestDb] COLLATE Arabic_100_CI_AI
GO

Or you can write your statement like this

INSERT dbo.test VALUES (N'بدر',N'بدر',N'بدر',N'بدر');

After we created the table then inserts these records

INSERT dbo.test VALUES ('بدر','بدر','بدر','بدر');
INSERT dbo.test VALUES ('احمد','احمد','احمد','احمد');
INSERT dbo.test VALUES ('أحمد','أحمد','أحمد','أحمد');
INSERT dbo.test VALUES ('أيات','أيات','أيات','أيات');

Then run these commands:

USE TestDb
GO

SELECT * FROM dbo.test ORDER BY Name_Char_Latain
SELECT * FROM dbo.test ORDER BY Name_nChar_Latain
SELECT * FROM dbo.test ORDER BY Name_Char_Arabic
SELECT * FROM dbo.test ORDER BY Name_nChar_Arab

And you will get result set as the below:

Now what about the hijri date and UmAlQura date?

By default Sql server support Hijri date format and you can do it by using CONVERT function so let's see these examples:

SELECT CONVERT(char(40),GETDATE(),130)
SELECT CONVERT(char(40),GETDATE(),131)

The result set:


29 ربيع الاول 1431 12:53:11:130AM

29/03/1431 12:53:11:130AM

So Sql Server does a great effort to display the date as Hijri but what about the UmAlQura date.

You need to write custom code using .NET CLR to implement it in SQL Server and this great feature introduced with version of SQL Server 2005 to enable you to write .Net code and by this approach we see new functions and data types like HIERARCHYID.

Let's see how we can Convert Gregorian Date to UmAlQura Date by using .Net UDF:

1. First open VS 2005/2008/2010 and create CLR database project

2. Choose a database connection for your reference

3. Right click on your project and Add New item.. and select UDF

4. Add reference using System.Globalization;

5. The complete code:

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString UmAlQuraConverter(DateTime date, string format)
    {
        return new SqlString(getUmAlQuraDate(date, format));
    }

    static string getUmAlQuraDate(DateTime date, string format)
    {
        UmAlQuraCalendar umAlQuraCal = new UmAlQuraCalendar();
        CultureInfo umAlQuraCulture = new CultureInfo("ar-SA");
        umAlQuraCulture.DateTimeFormat.Calendar = umAlQuraCal;
        return date.ToString(format, umAlQuraCulture);
    }
};

Then choose deploy solution

Then run this command to enable using CLR in your SQL server instance

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

Then run this command to get UmAlQura Date

SELECT dbo.UmAlQuraConverter(GETDATE(),'dd/MM/yyyy')

And you will get result as UmAlQura Date.


Note:
If you use Sql server database as backend system and .net application as frontend system ,in this case leave your date column as it's (Gregorian date) and just change the formatting at design level or user interface application by using inline function to format the date or use Global file to change it at the application level so by this approach you decrease the operations of convert date at Sql server and you avoid to have multiple columns or create custom data type or use custom functions and this make the integration easy to you because your data stored as standard or default data types.

Setting value to a variable by using SELECT or SET statement

You can assign value to a variable by using SET or SELECT statement and almost they work the same except that a SELECT statement has the ability to have the source value come from a column within the SELECT statement.


Let's see these examples to clarify the difference between the two statements:

USE AdventureWorks
GO

DECLARE @Res DateTime
-- Assign value to a variable by using SET statement
SET @Res = GETDATE();
SELECT @Res

SET @Res = (SELECT ModifiedDate FROM Person.Contact WHERE ContactID = 1)
SELECT @Res
-------------------------------------------------------------------------
--Just this case is show the diff between the SET and SELECT
-- It will cause an error
SET @Res = ModifiedDate FROM Person.Contact WHERE ContactID = 1
SELECT @Res
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-- Assign value to a variable by using SELECT statement
SELECT @Res = GETDATE();
SELECT @Res

SELECT @Res = (SELECT ModifiedDate FROM Person.Contact WHERE ContactID = 1)
SELECT @Res
--------------------------------------------------------------------------
-- It will success
SELECT @Res = ModifiedDate FROM Person.Contact WHERE ContactID = 1
SELECT @Res
--------------------------------------------------------------------------

Full Text Search and Service Broker in SQL Server 2008

Some basic points to remember before Implementing Full Text Search:

• Full Text Search scenario come when you have queries beyond the Like clause capabilities against word or phrase.
•  In SQL Server 2008, Full Text Search service is fully integrated with SQL Server database engine.
• Search results from data source like PDF which stored as varbinary data type.
• Search specific word or phrase
• SBS is a process of sending and receiving asynchronous messages in the same database or between different instances of Sql Server.

FOR XML modes

Why retrieve data as XML structure?

1. You need to pass the data as XML structure to others like outside applications
2. Sometime if you retrieve the data as XML structure is easier in read or describable more than if you retrieve it as ROWS AND COLUMNS like for example if you retrieve the authors and their books.

To retrieve the result set as XML structure there are four modes:

1. FOR XML RAW
2. FOR XML AUTO
3. FOR XML EXPLICIT
4. FOR XML PATH

So let's start by FOR XML RAW: It returns each row as an XML element and each column as an XML attribute.

Let's see examples:

USE AdventureWorks
GO

SELECT TOP 3 ProductID
            ,Name
            ,ProductNumber
FROM   Production.Product
FOR XML RAW

Result set of the above query:

--You can specify the name of root as well as name of elements
SELECT TOP 3 ProductID
            ,Name
            ,ProductNumber
FROM   Production.Product
FOR XML RAW('Product'),ROOT('Products')

Result set of the above query:

Note:

Simply remove the attribute if the value is NULL.So you can define you result as separate it element to show the empty value or Null.

SELECT TOP 3 ProductID
            ,Name
            ,ProductNumber
FROM   Production.Product
FOR XML RAW('Product'),ROOT('Products'),ELEMENTS

Now let's start by FOR XML AUTO: It returns result as hierarchy structure and only one level supported.

SELECT TOP 3 p.ProductID
            ,p.Name
            ,pm.Name as 'Model'
FROM   Production.Product p
INNER JOIN Production.ProductModel pm
ON p.ProductModelID = pm.ProductModelID
FOR XML AUTO,ROOT('Products')

Now let's start by FOR XML PATH: allows for the easy creation of different XML structures by simply interpreting column names specified using an XPath-like expression.

Let's see this example:

SELECT TOP 4
    ProductID AS 'ProductID'
    ,ProductNumber AS "comment()"
    ,CAST('<Break/>' AS XML) AS "node()"
FROM Production.Product p
FOR XML PATH('Product'), ROOT('Products');

Result set of the above query:

Now let's start by FOR XML EXPLICIT: provides very specific control over your XML structure.

But if you want to use it there are a set of rules to follow them to build your select statement.

Some of these rules:

1. You must include two columns in your select statement:
    a. Tag column with a numerical value for each level of the hierarchy
    b. Parent column with a numerical value based in your Tag column value
2. you must create a SELECT statement for each level of that hierarchy and use UNION ALL clauses to join those statements.
Let's see this example:

USE AdventureWorks
GO

SELECT 1 AS Tag, 
        NULL AS Parent,
        ProductID AS [Product!1!ProductID], 
        NULL AS [ProductName!2!Name!ELEMENT]
FROM Production.Product  
WHERE Name like 'a%'
UNION ALL
SELECT 2 AS Tag, 
        1 AS Parent,
        ProductID , 
        Name
FROM Production.Product  
WHERE Name like 'a%'
FOR XML EXPLICIT;

Result set of the above query:

Note:

Notice an alias to ProductID, Name column. Notice I use a very specific structure to define the alias name

XML Data type (Internally, stored using the varbinary(max))

Xml data type used to store:
1. XML fragments
2. XML documents

 XML fragment lacks the XML declaration () and does not have to have a root element .

The XML data type can be either typed or untyped. Typed simply means that an XML schema collection is assigned to the type to verify its contents.

Let's see an example to clarify the idea:

USE TestDb
GO

CREATE XML SCHEMA COLLECTION EmployeeSchema
AS
'<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="unqualified">
    <xs:element name="employee">
        <xs:complexType>
            <xs:attribute name="FullName" type="xs:string"/>
            <xs:attribute name="IdentityNo" type="xs:integer"/>
        </xs:complexType>
    </xs:element>
</xs:schema>';
GO

CREATE TABLE Employees
(
     EmpID INT IDENTITY PRIMARY KEY
     --this column called as typed because its contents need 
     --to match the schema
    ,FullName XML(DOCUMENT EmployeeSchema)
    --this column called as untyped 
    ,FullName2 XML NULL
)
GO

-- Will succeed:
INSERT Employees VALUES 
('<employee FullName="Fadi Ahmad Abdulwahab" IdentityNo="123234532"/>','<data>fadi</data>');
-- Will not succeed:
INSERT Employees VALUES 
('<employee FullName="Fadi Ahmad Abdulwahab" IdentityNo="fadi"/>','<data>fadi</data>');
GO

Last point need to take care of it:


If you need to change the schema you must first alter all columns that use the schema to instead use untyped XML. Then, you must drop the schema collection, re-create it with the added attributes, and finally alter the columns again to use the schema. And this also harms the performance of Sql server engine because it needs to convert typed data to untyped data.

Note:SQL SERVER 2008 provide you with many of methods that can help you to query or retrieve data as XML.

Create Maintenance Plan for scheduled backup task

My friend (work as Network engineer) asked me how we can create maintenance plan for backup Sql Server database so I wrote this article to simplify and clarify the task to him.

First of all, What is Maintenance Plan?
Maintenance plan contains group of tasks as graphical tool to simplify the job of DBA to perform tasks and configure its options like backup database, shrink database,… without need to write any T-SQL statements.

Maintenance plan Tasks:

1. Backup Database task
    Described As the title of task
2. Check Database Integrity task
    Checks the structural integrity of the database. For Examples would include checking disk space allocations inside SQL, checking consistency between system metadata tables, and checking the structures that make tables.
3. Execute SQL Server Agent Job task
    The Execute SQL Server Agent Job Task can be used to run a SQL Server Agent Job which is created on the SQL Server Instance. This task is only available when you are creating a Maintenance Plans using SSIS designer.
4. Execute T-SQL statement task
    Described As the title of task
5. History Clean Up task
    Deletes the historical data related to database backups and restore activities, SQL Server Agent Job history, database maintenance plan history etc
6. Maintenance clean up task
    Remove the older files like maintenance plan execution reports, database backup files etc
7. Notify Operator task
   Send messages to the SQL Server Agent Operator when a task has successfully completed or failed
8. Rebuild Index task
   Recreates an index with new settings you define in the wizard. Although this task can reorganize an index
9. Reorganize Index task
    Perform "Fragmentation" and it exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file
10. Shrink Database task
    Reduce the physical size of the database and logs by removing the empty data and log pages.
11. Update statistics task
    Force the SQL Engine to reexamine statistics on columns and indexes

A DBA can create a database maintenance plan either by using the Maintenance Plan Wizard or by using the SQL Server Integration Services (SSIS) designer (for Advanced Options)

Let's see an example which create scheduled automated backup and Send an Email by using Database mail.

We can divide this example into three part as following:
a. Create scheduled backup
b. Configure the Database mail and Operator
c. Add Notify Operator task to maintenance plan

Steps to Create scheduled backup:

1. Connect to SSMS
2. Right click on Maintenance Plans folder and choose New Maintenance Plan…
3. Enter a name for your maintenance Plan. Ex: " AdventureWorks_ScheduledBackup"
4. At the left bottom of maintenance plan designer add the following tasks:
    a. Shrink Database task
    b. Backup Database task
    c. Maintenance clean up task

5. Now let's configure the options for each task

6. So right click on Shrink database task and choose Edit
7. Select AdventureWorks database and then click ok

8. Now let's configure the options for Backup Database task

9. So right click on Backup database task and choose edit
10. Select AdventureWorks database ,full type backup ,place of storing backup and the start name convention (bak) then select ok

11. Now let's configure the options for maintenance cleanup task

12. So right click on it maintenance cleanup task and choose edit
13. Select option as the below image

14. Now let's configure the schedule for the above plan

15. So at the above of maintenance plan designer select

16. And select as the below image

Steps to Configure Database mail and Operators:

1. You can refer to this link to know how to configure Database mail for SQL Server 2008
2. To adding new Operator just go to Operators folder and choose New operator

3. Enter the name and email of current operator as following:

Steps to add notify operator task to our maintenance plan

1. So add two Notify Operator Task to our maintenance plan as following
2. As you see one for success task and one for failed task

3. Now right click on each Notify Operator task and choose edit

4. Select the option as follow for each task

 
Finally save maintenance plan and test it.

Spatial Data types

Sql Server 2008 introduced new types of spatial data including the following types:
1. Geometry data type.
A data type used to store two and three dimensional data coordinates. It is used to store planar (flat-earth) data. It is generally used to store XY coordinates that represent points, lines, and polygons in a two-dimensional space
2. Geography data type.
A data type used to store ellipsoidal data, such as latitude and longitude coordinates. It is used to store ellipsoidal (round-earth) data. It is used to store latitude and longitude coordinates that represent points, lines, and polygons on the earth’s surface

So by using the above types you can store data like latitude,lognlatitude,…which can be used in conjunction with for example Microsoft Virtual Earth to provide a visual representation of your geospatial data.

These types implemented by .Net CLR data types which mean they can support various properties and methods specific to the data.

Let's see examples:

USE TestDb
GO

CREATE TABLE EmployeesAddress
(EmployeeID int IDENTITY PRIMARY KEY,
AddressName NVARCHAR(50),
Location    GEOGRAPHY)
GO

--The Point extended static geography method constructs an
--instance representing a point that includes information 
--on the longitude, latitude, and SRID.
--The Parse extended static geography method returns a geography instance when the input
--is expressed in the OGC WKT representation.
INSERT INTO EmployeesAddress
VALUES
('ALRiyadh',
    GEOGRAPHY::Parse('POINT(-83.0086 39.95954)'));
GO    
    
SELECT * FROM EmployeesAddress
GO

--Other example use GEOMETRY data type
DECLARE @s GEOMETRY
SET @s = 'LINESTRING (  69 26, 69 23, 69 21, 67 20, 65 20, 
          63 18, 58 17, 52 17, 51 17, 49 17, 45 18, 44 20, 
          44 21, 42 26, 42 29, 42 32, 42 35, 43 35, 47 38, 
          50 41, 55 42, 58 42, 65 44, 66 44, 67 44, 68 45, 
          69 47, 70 48, 70 50, 71 51, 70 56, 68 60, 68 63, 
          66 65, 65 66, 63 68, 60 71, 59 71, 57 71, 55 71, 
          51 69, 45 65, 44 63, 42 62, 41 59, 41 57, 41 56, 
          41 54, 42 53 )'
          
SELECT @s