Controls the concatenation results with NULL are treated as NULL or empty string values with SET CONCAT_NULL_YIELDS_NULL

When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'Bunty' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'Bunty' + NULL yields Bunty.

PRINT 'Setting CONCAT_NULL_YIELDS_NULL ON';

GO

SET CONCAT_NULL_YIELDS_NULL ON;

GO

SELECT 'Bunty' + NULL ;

GO

SET CONCAT_NULL_YIELDS_NULL OFF;

GO

SELECT 'Bunty' + NULL;

GO

Below TSQL returns the current setting value for CONCAT_NULL_YIELDS_NULL

SELECT   SESSIONPROPERTY ('CONCAT_NULL_YIELDS_NULL')
 

How to change Dynamic Port of the SQL Server Named Instance to Static port on stand alone or SQL 2005 cluster?

SQL server the default instance has fixed TCP/IP port 1433 while for the named instances it has random dynamic selected port assigned. This is the default selection while you are installing SQL server. You might require changing the SQL server dynamic port to static port, especially when clients connect to the SQL server thru firewall. In that case particular port requires opening on firewall to allow connection to SQL Server.

Standalone Instance:

StartàProgram FilesàMicrosoft SQL 2005àConfiguration toolsàSQL Server Configuration manager.

Selection SQL Server 2005 network configuration, click on the “Protocols for ” and on right hand side right click  and go to properties of TCP/IP as shown in below screenshot.

Click on the IP Addresses tab on the top and keep the Dynamic port row Blank and write the require port number on TCP Ports row in the IP ALL section as shown in below screenshot.

It require the restart the SQL server services to change the take effects.

You can verify the setting by below registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ \MSSQLServer\SuperSocketNetLib\Tcp

Clustered Instance:

It requires following the same above steps but sometimes when you restart the services / node port change back to dynamic port. This is because while you restart the services local copy of registry not change and checkpoint get the old port number.

So you need to follow below steps.

Disable the check pointing to the quorum

Cluster res "SQL Server (Instance Name)" /removecheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.3\MSSQLSERVER"

Change the Dynamic port of the SQL server to static on all nodes as above mentioned steps.

Enable the check pointing to the quorum

Cluster res "SQL Server (Instance Name)" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.3\MSSQLSERVER"

Make sure to give appropriate path for MSSQLSERVER. In above example I have MSSQL.3 because it is third instance I have installed. You may have different values.

Bring the SQL Server online after change.

Make sure port change after SQL services online.

How to find text/word inside SQL Server store procedure

Any of the following snippet allows you find a specific word or words from all SQL store procedure. This can be very useful if you want to find all references to table/specific word and remove/replace them. Below code should work on SQL server 2005/2008 (R2).

SELECT DISTINCT

        ( OBJECT_NAME(id) )

FROM    syscomments

WHERE   text LIKE '%Search_word%'

SELECT  ROUTINE_NAME ,

        ROUTINE_DEFINITION

FROM    INFORMATION_SCHEMA.ROUTINES

WHERE   ROUTINE_DEFINITION LIKE '%Search_word%'

        AND ROUTINE_TYPE = 'PROCEDURE'

      

SELECT  Name

FROM    sys.procedures

WHERE   OBJECT_DEFINITION(object_id) LIKE '%Search_word%'

   

SELECT  OBJECT_NAME(object_id)

FROM    sys.sql_modules

WHERE   Definition LIKE '%Search_word%'

        AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1