CONCAT_NULL_YIELDS_NULL

CONCAT_NULL_YIELDS_NULL Can be used to control the Concatenation results of stings with NULLS.

When turned on:
Concatenation of string with NULL Results in NULL.

When turned Off:
Concatenation of string with NULL Results in String itself.

Sample Queries:
[sql]
SET CONCAT_NULL_YIELDS_NULL ON

SELECT ‘TestString’ + NULL AS Result
–Results in NULL
SET CONCAT_NULL_YIELDS_NULL OFF

SELECT ‘TestString’ + NULL AS Result
–Results in TestString
[/sql]
The first result would be NULL and the Second result would be TestString.

In the above query the Impact of changing the property value can be clearly seen.

CONCAT_NULL_YIELDS_NULL setting can be changed in Session or in Database.

Setting CONCAT_NULL_YIELDS_NULL value in Session:
[sql]
SET CONCAT_NULL_YIELDS_NULL ON
SET CONCAT_NULL_YIELDS_NULL OFF
[/sql]

If this setting is changed in the Session then the impact of changing this value can be seen in the current session only. All the other session will have its default behaviour.

Setting CONCAT_NULL_YIELDS_NULL value for a Database:
[sql]
ALTER DATABASE <<databasename>> SET CONCAT_NULL_YIELDS_NULL OFF
ALTER DATABASE <<databasename>> SET CONCAT_NULL_YIELDS_NULL ON
[/sql]
If the value of CONCAT_NULL_YIELDS_NULL is not set in the query then the database property for CONCAT_NULL_YIELDS_NULL is used By default .

How to check the current setting of CONCAT_NULL_YIELDS_NULL?

The Below query fetches the session value for the CONCAT_NULL_YIELDS_NULL property.
[sql]
SELECT SESSIONPROPERTY(‘CONCAT_NULL_YIELDS_NULL’)
–Result 1 if set to ON, 0 if Set to OFF
[/sql]

The Below query fetches the database value for the CONCAT_NULL_YIELDS_NULL property.
[sql]
SELECT NAME,IS_CONCAT_NULL_YIELDS_NULL_ON FROM SYS.DATABASES
–Result 1 if set to ON, 0 if Set to OFF
[/sql]

Point to Note:

– Some clients Like SQL Server Management studio by default send the CONCAT_NULL_YIELDS_NULL ON command while initiating each database connection.
Hence even after changing the database property the result cannot be seen in SSMS.

– Setting this value in stored procedures may cause the procedure to recompile every time its executed.

– This setting in future versions of SQL Server is going to be Always ON, So be cautious while using this.

http://support.microsoft.com/kb/294942
http://msdn.microsoft.com/en-us/library/ms176056.aspx

Now Available In OVI Store – Free Application – SQLServerLearner.com

sqlserverlearner.com is now available in Nokia ovi Store.
This is a free Application that can be used from Nokia Mobile Phones!! 🙂

SQLServerLearner.com in Nokia OVI Store

Download Link:
http://store.ovi.com/content/261911

Do share your comments about this new application!!!

How to connect to database in sql server management studio

Steps to connect to a database in SQL Server Management Studio (ssms)

In SSMS click on file – Connect to object explorer.

Connect Object Explorer

In the connect to server give the details of server name , login ID and password.

Connect to Server

Click on connect.

Note: Select the correct Authentication while connecting to the server. Contact your database administrator for details of the authentication that you have to use while connecting.

START STOP RESTART SQL Server 2008 from command prompt

Following commands can be used to start stop SQL Server service from command prompt.
Note: Run Command prompt as Administrator

Start SQL Server 2008

[sourcecode language=”plain”]net start mssqlserver[/sourcecode]

Start SQL Server 2008 - mssqlserver service from Command Prompt

Stop SQL Server 2008

[sourcecode language=”plain”]net stop mssqlserver[/sourcecode]

STOP SQL Server 2008 - mssqlserver service from Command Prompt

Restart SQL Server 2008

[sourcecode language=”plain”]
net stop mssqlserver
net start mssqlserver
[/sourcecode]

Advanced Topics:
If you want to start/Stop a specific sql server instance replace mssqlserver with the name of the instance specific service[sourcecode language=”plain”]
net stop <<sqlserverservicename>>
net start <<sqlserverservicename>>
[/sourcecode]

For SQL Server Express the name would be MSSQL$SQLEXPRESS
[sourcecode language=”plain”]
net stop MSSQL$SQLEXPRESS
net start MSSQL$SQLEXPRESS
[/sourcecode]

How to find the service name?
1. Open Services.msc (open Run (windowskey+r) then type services.msc and click on ok)
2. In the read through the services(or click S to find all services starting with s)
3. Select a SQL Server service and doubleclick on it(or rightcick-properties)
4. In the popup you will have service name
5. Note down the service name (This is the name of the service – Display name is used for readability)

SQL Server Service Pop up

Following are the few of SQL Server related service names:

Display Name Service Name
SQL Server (MSSQLSERVER) MSSQLSERVER
SQL Server Agent (MSSQLSERVER) SQLSERVERAGENT
SQL Server Analysis Services (MSSQLSERVER) MSSQLServerOLAPService
SQL Server Browser SQLBrowser
SQL Server Integration Services 10.0 MsDtsServer100
SQL Server Reporting Services (MSSQLSERVER) ReportServer
SQL Server VSS Writer SQLWriter SQLWriter

Following are the few of SQL Server instance specific service names:

Display Name Service Name
SQL Server (SQLEXPRESS MSSQL$SQLEXPRESS
SQL Server Agent (SQLEXPRESS) SQLAgent$SQLEXPRESS

Additional Notes:
If you try to start the SQL Server service which is already started you get the below error:

The requested service has already been started.
More help is available by typing NET HELPMSG 2182.

If you try to stop the SQL Server service which is not yet started you get the below error:

The SQL Server (MSSQLSERVER) service is not started.
More help is available by typing NET HELPMSG 3521.

If you try to start or stop SQL Server service when you do not have permissions you get the following error:

System error 5 has occurred.
Access is denied.

Resolutions:
1- Try to open command prompt as Administrator if it is not running in Admin mode.
2- Try to get Admin permissions on the Server.

If you try to start or stop SQL Server service when you do not have service installed you get the following error:

The service name is invalid.
More help is available by typing NET HELPMSG 2185.

Resolutions:
1. Check if SQL Server is installed on the server
2. Try to open command prompt as Administrator if it is not running in Admin mode.
3. Try to get Admin permissions on the Server.
2- Try to get Admin permissions on the Server.

Unable to start/stop any SQL Server sevice:
1. Check if the service name is correct.
You can use services.msc or use the command
[sourcecode language=”plain”]
sc query | findstr /I “MSSQL”
[/sourcecode]
2. Check if the Command prompt is in Admin mode
3. Check SQL Server error logs for additional details