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

SSIS Import/Export Wizard: “Unexpected Unrecoverable Error” in SQL 2008 R2.

In my last class I was teaching Import/Export feature for SQL 2008 on windows 7 thru  SQL Server Management Studio’s import and export wizard. I am getting “Unexpected and Unrecoverable Error” message with Abort,Retry and Cancel button. Any button you press it just abnormally close the import export wizard. This was very annoying and I was not able to get my work done.  

After some research I have found that you need to install the Extended .Net Framework 4. After installing this everything works as expected. You need to make sure to do windows update after the install as there is security patch for it.

Some people also suggest different method to edit “C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTSWizard.exe.config"  and comment out/delete . I tried this option but in my case it not works. 

I suggest to install .Net framework 4.

How to select / delete/update NULL record?

You cannot use  = NULL , you have to use IS NULL

e.g

-- Create temporary table

CREATE TABLE #TempSP (eid INT IDENTITY, Value1 varchar(10))

-- Insert sample values

INSERT INTO #TempSP values ('Value1')

INSERT INTO #TempSP values ('Value2')

INSERT INTO #TempSP values ('Value3')

INSERT INTO #TempSP values (NULL)

-- Select query for check what's in table

SELECT * FROM #TempSP

-- Select query where value1 is NULL

SELECT * FROM #TempSP

WHERE value1 IS NULL -- WHERE value1 = NULL won't work

-- Delete rows where value1 is NULL

DELETE FROM #TempSP

WHERE Value1 IS NULL  -- WHERE Value1 = NULL won't work

-- Select query for check what's in table

SELECT * FROM #TempSP

-- Drop temporary table

DROP TABLE #TempSP

ACID Properties:

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee database transactions are processed reliably.

Transaction: A transaction is one or more actions that are defined as single unit of work.

Atomicity: All or nothing execution.

In a sequence of steps either all actions occur or none. Atomicity means that users do not have to worry about the effect of incomplete transactions. If an action fails half-way through a transaction, then all previous actions in the transaction must be rolled back as if they never happened.

Example: Moving money from savings to checking accounts , withdrawing money from bank accounts

Consistency : Execution of transaction results in consistent database.

The database is transformed from one valid state to another valid state.

Example: If someone moves 5,000$ from their bank account that information is logged. If it can't be logged then the money is not transferred.

Isolation : Many transactions may execute concurrently but each is unaware of the others.

The results of a transaction are invisible to other transactions until the transaction is complete.

Example:  If someone querying balance amount at 12.00 AM , now at 12.01 AM user Bunty deposits 250$. The query results even if it finishshes at 12.04AM will not show the changes made by Bunty.

Durability : Persistence.

Once transaction is committed (completed), the results of a transaction are permanent and survive future system and media failures.

Example: Once your seat is reserved on an airplane, even if the system crashes the moment it finishes writing that information in the database your seat will still be reserved.

SQL Server : List all failover clustered instance nodes on which an instance of SQL Server can run and Find active node query

SELECT * FROM ::fn_virtualservernodes()

Above query returns the list of Nodes on which SQL Server can run. If the current server instance is not a clustered server, fun_virtualservernodes returns empty rowset.

User must have VIEW SERVER STATE permission to execute above query successfully. 

Note : if you have SQL 2000 , Microsoft recommending to use

Select * from sys.dm_os_cluster_nodes

How to determine the active node of a SQL Active-Passive Failover Cluster programmatically from T-SQL?

Select ServerProperty('ComputerNamePhysicalNetBIOS')

SQL Server : Last Backup / Restore date of the database.

Please find below two methods to know last backup date of SQL database.

1.       In SSMS , right click on database and check the properties. Refer  Last database backup which is available under General  tab.

2.       Query : you can query msdb..backupset table.

SELECT  database_name ,

        MAX(backup_finish_date) AS backup_finish_date

FROM    msdb..backupset

WHERE   database_name = 'Database_Name'

GROUP BY database_name

Last Restore date of database Query

                SELECT  destination_database_name ,

        MAX(restore_date) AS restore_date

FROM    msdb..restorehistory

WHERE   destination_database_name = 'Database_Name'

GROUP BY destination_database_name
 

Find the all databases restore date with SourceDB , SourceFile and Backup Date

USE msdb
GO
SELECT  DBRestored = destination_database_name ,
        RestoreDate = restore_date ,
        SourceDB = b.database_name ,
        SourceFile = physical_name ,
        BackupDate = backup_start_date
FROM    RestoreHistory h
        INNER JOIN BackupSet b ON h.backup_set_id = b.backup_set_id
        INNER JOIN BackupFile f ON f.backup_set_id = b.backup_set_id
ORDER BY RestoreDate

SQL Server : sa account locked out / FIX Error : 18486 Login Failed for user ‘sa’ because the account is currently locked out. The system administrator can unlock it. Unlock SA login

There may be multiple situations which end up with SA password locked. 

Brute force attack: attempt to guess a password using different possible combination,if you have password policy setting enable which locked the account after certain attempts.

Multiple unsuccessful attempts to guess ‘SA’ password. Etc...

Fix:

1.

Login with other SQL user which has sysadmin role.

Unlocked ‘SA’ login WITH ALTER command

ALTER LOGIN sa WITH PASSWORD = 'currentpassword' UNLOCK ;

GO

2. Unlocked with SSMS.

Microsoft SQL Server management studio-->Logins-->sa-->Properties-->Status-->unchecked the Login is locked out check box

3. Sometime because of Enforce password policy enable , SQL server not giving you specific message when you trying to unlocked the account. In this case first you need to unchecked the ‘Enforced password policy’ , unchecked the Login is locked press OK and again the set the the ‘Enforced Password policy’ option once account is unlocked.

It is good practice to disable the sa user to prevent SQL server from hacking. Almost everyone is aware of the sa account. This can be the potential security risk. Even if you provide strong password hackers can lock the account by providing the wrong password. As a best practice you can disable the sa account and use another account with same privileges.in this case you have different user should have same access as ‘SA’

it is also possible that to not have a user with sytem admin rights in your database and you only have activated SQL Server authentication.

It is possible to change the Server authentication with change value of the registry key "HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode" to 2 for Mixed mode. Then restart the SQL Server service. Now you can login to SQL server with windows authentication and change the password with the procedure discuss in above steps.