The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine DI-ERP2 Error:2148007941 (0x80080005).

Today when I was trying to uninstall SQL Server If got below error:

TITLE: Microsoft SQL Server 2005 Setup

------------------------------

The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine MAIN Error:2148007941 (0x80080005). 

Than I searched the net and found below command file and I ran this and it fixed my WMI problem. Copy below content into notepad as save in C Drive root as fixwmi.cmd and then from a command line run c:\fixwmi.cmd.

It takes several minutes to complete and at points it appears that it is not running but it is. After it is complete, my problem is solved.

The content of cmd file is:

@echo on
cd /d c:\temp
if not exist %windir%\system32\wbem goto TryInstall
cd /d %windir%\system32\wbem
net stop winmgmt
winmgmt /kill
if exist Rep_bak rd Rep_bak /s /q
rename Repository Rep_bak
for %%i in (*.dll) do RegSvr32 -s %%i
for %%i in (*.exe) do call :FixSrv %%i
for %%i in (*.mof,*.mfl) do Mofcomp %%i
net start winmgmt
goto End

:FixSrv
if /I (%1) == (wbemcntl.exe) goto SkipSrv
if /I (%1) == (wbemtest.exe) goto SkipSrv
if /I (%1) == (mofcomp.exe) goto SkipSrv
%1 /RegServer

:SkipSrv
goto End

:TryInstall
if not exist wmicore.exe goto End
wmicore /s
net start winmgmt

:End

SQL Backup job failed with exitcode: 880 SQL error code: 942 [SQLSTATE 42000] (Error 50000). The step failed.

When a database is offline and you try to take backup of that database than below error will show:

SQL Backup job failed with exitcode: 880 SQL error code: 942 [SQLSTATE 42000] (Error 50000). The step failed.

So, before running backup on any database, check whether the database is online.
Now to make database online, you can use below methods:

Method 1

use master
go
Alter Database <Database_Name> SET Online

Method 2

Open SSMS, select database, right click. Select Tasks and Select Bring Online.

Deleted all login from SQL Server and now unable to Login to SQL Server

Mistakenly I had deleted all logins from SQL Server and now unable to login to SQL Server. When I try to login I got below error.

Login failed for user '  domain\name'. (Microsoft SQL Server, Error: 18456)

To resolve the issues I follow below steps:

Click START - Run - cmd (Command Prompt)

Stop the sql server : NET STOP MSSQLSERVER

Now Start the sql server with single user mode from command prompt:

NET START MSSQLSERVER /m

Connect the SQL Server using the Windows Authentication. To find out the windows server name

•      click start
•      Right click on my computer
•      Select properties
•      computer name

If above steps are not working, connect SSMS with windows Authentication.

Create login and provide sysadmin rights.

Stop the sql server running the below command in command prompt.

NET STOP MSSQLSERVER

Start the sql server with multi user mode running the below command in command prompt

NET START MSSQLSERVER

Database Stuck In Restoring State

Sometime, You may found a database that is restoring mode after so many hours and after refresh also, the database is still restoring.

This usually occurs when a user who initiated the restore, was disconnected during the restore process and leaves the restore process. 
OR
The User  forget to include WITH RECOVERY with Restore script.

Solution:
Run RESTORE DATABASE [Database_Name] WITH RECOVERY  to complete roll forward. 
If this failed, than you may have to drop the database and restoring from backup 
(make sure you include RECOVERY in your restore script this time).

Detect torn pages in SQL Server

Use below query to find the torn pages in SQL Server 2005 or later versions. 
Suspect_pages table is located in MSDB database.

SELECT db_name(database_id) DatabaseName, file_id, page_id, last_update_date
FROM msdb.dbo.suspect_pages WHERE event_type = 3

The above query result will display torn page and that page located in which file and when torn page issues happened that date will show.

Disable all SQL Server Jobs

Use below script to disable all active Jobs in SQL Server:

BEGIN
  SET NoCount On
  DECLARE @JobName nVarChar(128), @Enabled int  
  SELECT Name, [Enabled] INTO #Temp_Jobs FROM msdb.dbo.sysjobs WHERE [Enabled] = 1  
  WHILE Exists(SELECT TOP 1 Name FROM #Temp_Jobs) 
  BEGIN
     SELECT TOP 1 @JobName = Name, @Enabled = [Enabled] FROM #Temp_Jobs
     PRINT ('Job Name:  ' + Cast(@JobName AS nVarChar) + ' , Status: ' + Cast(@Enabled AS VarChar))
     EXEC msdb.dbo.sp_update_job @Job_Name = @JobName , @Enabled = 0
DELETE FROM #Temp_Jobs WHERE Name = @JobName     
  END
  DROP TABLE #Temp_Jobs
  SET NoCount OFF
END

Stop and Resume Log Shipping in SQL Server

Method 1:

Follow below steps to stop the Log Shipping in SQL Server 2008:

1) Right click on LogShipping Primary Server Database.
2) Select the Transaction Log shipping option.
3) Click ... option under secondary server instances and databases Restore Tab from the window.
4) Select the General. 
5) Uncheck the Enable option.
6) Select the OK button.

Follow below steps to resume the Log Shipping in SQL Server 2008:

1) Right click on LogShipping Primary Server Database.
2) Select the Transaction Log shipping Option.
3) Click ... option under secondary server instances and databases Restore Tab from the window.
4) Selct the General. 
5) Check the Enable option.
6) Select the OK button.

SQL performance slowing down

I was working on a database since last couple of month. Suddenly it was slowing down day by day. I had not changes anything in the database. 

Then I checked the database properties and found that recovery model was set to Full. 
Full recovery model was not needed as I do not use incremental backup. 
So I set recovery model to Simple and my problem is solved.

Change Database compatibility level

You can change the database compatibility level through Management Studio. (Go to Database Properties - Option).

Here is the script to change the database compatibility level through SQL:

ALTER DATABASE <DB_Name> SET SINGLE_USER 
GO 
EXEC sp_dbcmptlevel <DB_Name>, 100; 
GO 
ALTER DATABASE <DB_Name> SET MULTI_USER 
GO

In above script change <DB_Name> with Database Name (you want to change the compatibility level).

Compatibility Level :

SQL Server 2008 : 100
SQL Server 2005 : 90
SQL Server 2000 : 80
SQL Server 7.0 : 70

ACID rule of thumb for transactions

A transaction must be:

1. Atomic - It is one unit of work and does not dependent on previous and following transactions.
2. Consistent - Data is either committed or roll back, no ?in-between? case where something has been updated and something hasn't.
3. Isolated - no transaction sees the intermediate results of the current transaction).
4. Durable - the values persist if the data had been committed even if the system crashes right after.

Query to get All Databases Size

User below query to find Database Size :

SELECT  d.name AS [Database Name],

        ROUND(SUM(mf.size) * 8 / 1024, 0) [Size (MB)]

FROM    sys.master_files mf

        INNER JOIN sys.databases d ON d.database_id = mf.database_id

WHERE   d.database_id > 4 

GROUP BY d.name 

ORDER BY d.name

Database Engine Tuning Adviser not found

Today, I want to optimize some some queries. I searched the net and found that we can use Database Engine Tuning Adviser for this. But I could not found that in SSMS. 

Again I searched the net and found that it is not available with SQL server express edition.

So I uninstalled the SQL Server Express Edition and  installed SQL Server Developer Edition. 

Now I got the Database Engine Tuning Adviser

SQL Version 32bit or 64 bit

The version information of SQL server you are running can be returned using below query:

SELECT @@VERSION

If the version returned contained x86 that means it is a 32 bit installation and if it contains x64 then it means it is a 64 bit installation.

Backup file information

Below script shows you all database backup files. 

It shows you Database Name, backup done by which user, backup file location, backup start date time and end date time and backup file size.

SELECT database_name, user_name as ExecutedBy, physical_device_name,
backup_start_date, backup_finish_date, backup_size
FROM msdb..backupset bckset
INNER JOIN msdb..backupmediafamily bckfmly
ON bckset.media_set_id = bckfmly.media_set_id

Error 233

Sometime you may get the below error while connecting to SQL Server.

A Connection was successfully established with the server, but than an error occurred during the login process, (Provider: Shared Memory Provider, error: 0 - No process is on the other end end of pipe.)
(Microsoft SQL Server Error: 233)

The above error clearly states that maximum value for user connection is already reached so you can't login to SQL Server. If you are the system administrator than you can increase the maximum value by using the SP sp_configure.

Use the below steps to increase the value. 

Open the SQL Server Management Studio.
Write down the below queries:

sp_configure 'show advanced options', 1
Go
reconfigure
Go
sp_configure 'user connections', 0
Go
reconfigure
Go

Date time formatting in SQL Server

Below is a small script that shows you the date output in different format:

It just outputs the current date in the various formats so you can quickly find the one your after


DECLARE @date datetime
DECLARE @i int
SET @date = getdate()
SET @i = 1
WHILE(@i <= 14)
  BEGIN
 PRINT CONVERT(VarChar, @i) + ': ' + CONVERT(VarChar, @date, @i)        
 PRINT CONVERT(VarChar, @i+100) + ': ' + CONVERT(VarChar, @date, @i+100)    
 SET @i = @i + 1
  END 
PRINT CONVERT(VarChar, 20) + ': ' + CONVERT(VarChar, @date, 20)        
PRINT CONVERT(VarChar, 120) + ': ' + CONVERT(VarChar, @date, 120)    
PRINT CONVERT(VarChar, 21) + ': ' + CONVERT(VarChar, @date, 21)        
PRINT CONVERT(VarChar, 121) + ': ' + CONVERT(VarChar, @date, 121)    
PRINT CONVERT(VarChar, 126) + ': ' + CONVERT(VarChar, @date, 126)    
PRINT CONVERT(VarChar, 127) + ': ' + CONVERT(VarChar, @date, 127)    
PRINT CONVERT(VarChar, 130) + ': ' + CONVERT(VarChar, @date, 130)    
PRINT CONVERT(VarChar, 131) + ': ' + CONVERT(VarChar, @date, 131)    

Query to create a Table of dates between date range specified

Use below query to create a table of date for given date range:

DECLARE @FromDt AS DateTime = '2013-09-01', @ToDt AS DateTime = '2013-12-31'

CREATE TABLE #TempDtTable(VchDt VARCHAR(10));
WHILE @FromDt <= @ToDt
BEGIN
INSERT INTO #TempDtTable VALUES (CONVERT(VARCHAR(10), @FromDt , 121))
SET @FromDt = DATEADD(DAY, 1, @FromDt)
END
SELECT * FROM #TempDtTable
DROP TABLE #TempDtTable

Restart Computer Failure While uninstalling SQL Server 2008

You may get the below error while uninstalling SQL server: 

A computer restart is required. You must restart this computer before installing SQL Server.

So, now you restart the system. and try again but you got the same error. Now it seems that there are some bad registry entries: Now you had to to do the following

Leave the Setup Support Rules UI open if you are already in there
Open Regedit (Registry Editor)

Go to the following registry key "HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession Manager"

Delete the "PendingFileRenameOperations"

Click “Re-Run” in Setup Support Rules launch the uninstall again

So now you will have passed the restart computer check and you can continue and get rid of that pesky old version of SQL Server.

Msg 1934, Level 16, State 1, Line 2

Today I was running below query to rebuild all indexes:

EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD'

But it failed and shown below error:

Msg 1934, Level 16, State 1, Line 2
ALTER INDEX failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

I searched for this on net and got some below solution:

EXEC sp_msforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD'

It means you need the SET QUOTED_IDENTIFIER ON in the sp_msForEachTable as well, because sp_msForEachTable does not have the right setting.

A connection was successfully established with the server, but then an error occurred during the pre-login handshake.

Today when I was connecting my SQL server from some client system, connection established successfully with the server. However, then an error occurred during the pre-login handshake. Following is the detailed error i am receiving. 

(provider: SSL Provider, error: 0 - The wait operation timed out.)
A connection was successfully established with the server, but then an error occurred during the pre-login handshake.

Then I check that TCP/IP protocol is not enabled for clients. Without TCP/IP protocol you can not access the sql server from other clients. Following is the method to enable the protocol.

Go to the Start-->Programs-->SQL Server--> Configuration Tools-->SQL Configuration Manager.

Expand the SQL Network Configuration and click on the PROTOCOLS node

Right click on TCP/IP and open up the PROPERTIES panel

Select the IP ADDRESS tab

Make sure the values for the IP ADDRESS fields are correct and match the system it is running on. If its not the same then provide the valid ip through which you want to access sql server from client machine.

Restart the service, and I I can connect to SQL Server from client.

Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)

Some times we may get the below error while updating the login from dbo to some other login from user mapping window.

1. I have database which is mapped with dbo login and dbo schema. 
2. I tried to update the database with app_user login, then it displays below error:

Cannot alter the user 'dbo'. (Microsoft SQL Server, Error: 15150)

To fix this issue we need to change the default owner to 'sa' and then tried to update the login. 

Use database_name
sp_changedbowner 'sa'

Get Login Session count, Session Mode, User Name, Machine name

sys.dm_exec_sessions SP stores the login information about users like username, login time, machine name, authentication mode (Windows or SQL).Therefore we can easily determine the current state of SQL server like Session count, User name, machine name etc. Use below query to get the result:


SELECT 'Authentication Method'=(CASE WHEN nt_user_name IS not null THEN 'Windows Authentication' ELSE 'SQL Authentication' END),
login_name AS 'Login Name',
ISNULL(nt_user_name,'N/A') AS 'Windows Login Name',
COUNT(session_id) AS 'Session Count',
host_name As 'Host'
FROM sys.dm_exec_sessions
GROUP BY login_name,nt_user_name,host_name

Procedures / Commands used in Sql Server Jobs

It is very difficult to find a procedures / Commands used in SQL Server Jobs from the huge cluster of Jobs through SQL Server Management Studio. You can get a list of all the Procedures / commands that are used in Sql Server Jobs.


USE msdb
GO
SELECT sj.name AS job_name, st.command
FROM sysjobs sj
JOIN sysjobsteps st
ON sj.job_id = st.job_id

You can use a where clause to get to know about procedures / command used in a particular jobs:

where st.command like '%Proc_toFind%'  

Changing the Default Database

By default SQL Server loads master databse.
Sometimes it is required to change the default database in SQL Server. Use below query to chage the default database:

ALTER LOGIN [ArunL\Arun] with default_database = DemoData

Find database file size

Use below query to get the database file size. I have used system view “sysaltfiles” from “master” database which keeps information about each and every database file available in current SQL Server instance. 


SELECT DB_NAME(dbid) AS DatabaseName, 
Name AS LogicalFileName, 
CASE WHEN GroupID=1 THEN 'Data' ELSE 'Log' END AS FileType, 
FileName AS FilePath, size AS TotalPage, 
(Size*8192E)/1048576 AS FileSizeInMB
FROM master..SysAltFiles

Open failed. Could not open file mdf for file number 2. OS error 5(Access denied)

Today my one client told me that their database could not access in yesterday, we try to use the database and got message error 

the database could not access due to disk space or insufficient memory

First I checked the disk space and found that there is ample disk space.

Then I checked SQL Server error log, It shows the below error:

FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file DATABASE_log.ldf ‘. Diagnose and correct the operating system error, and retry the operation.

Generally an OS error 5 means there is no permission to modify a particular file. Than I suspect that the account that is running SQL Server Service does not have permissions to modify the LDF and MDF files.

So, then I checked the write permission to the file by right click on the files (both mdf and LDF) and select properties and than from security TAB checked the write permission to these files. I set the write permission. 

Now problem solved.

Swap data between columns

Sometime we may mistakenly insert data into a wrong column if we have two identical columns with same data type. To correct this we have to delete the data from the wrong column and insert that in the proper column. Here we had to do lot of work. Swapping can be done :
                              
1.with the help of a temporary variable .
   or
2.directly.

Suppose we have a table named Customer with 3 columns: CustomerID, FName, LName.
       
              CustomerID: int 
              FName: varchar(35)
              LName: varchar(35)

and we have inserted some rows to the table.

Insert into Customer values(1,'Ladha','Arun')
Insert into Customer values(2,'Daga','Ashit')
Insert into Customer values(3,'Shah','Nishi')
Insert into Customer values(4,'Singh','Ayan')

Here I had inserted FName data in LName and LName data into FName.
Now, I want to swap them.

1. Swap with the help of temporary variable:
-------------------------------------------------
DECLARE @temp AS varchar(10)

UPDATE Customer SET @temp=LName, LName=FName, FName=@temp

  The resulting  table can be verified.

2.directly:
------------
UPDATE Customer SET LName = FName, FName = LName

This query can also swap the column data.

database last accessed date time

Sometime you may required to know the last accessed date and time of database..
Below query returns the lass accessed date and time of SQL Database.


SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate
FROM
    (SELECT
        DB_NAME(database_id) DatabaseName
        , last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update
    FROM sys.dm_db_index_usage_stats) AS PivotTable
UNPIVOT 
    (LastAccessDate FOR last_user_access IN
        (last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update)
    ) AS UnpivotTable
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY MAX(LastAccessDate)

The biggest problem of above query is that all information will be lost when you restart SQL Server. So, this is not applicable for servers which frequently restarted. 

Other problem is related to Full-Text-Catalogs. If you have enabled Full-Text-Index on your tables, you might see a record saying that database has been accessed without any user operations. This is because of Full-Text-Indexes.

Verifying Foreign Keys in MS SQL Server

Today I was checking one Demo database and surprised to see that foreign key columns contained some values which were not stored in the referenced tables. The foreign key constraints were there, but they had been deactivated with the WITH NOCHECK clause. 

The below script verify the foreign key constraint:

DECLARE @TableName sysname, @ForeignKey sysname, @String NVARCHAR(1000)

DECLARE cur CURSOR FOR

SELECT t.name, fk.name

FROM sys.foreign_keys fk

INNER JOIN sys.objects t ON t.object_id = fk.parent_object_id

ORDER BY t.name, fk.name

OPEN cur 

FETCH cur INTO @TableName, @ForeignKey

WHILE @@FETCH_STATUS = 0 BEGIN

SET @String = 'ALTER TABLE ' + @TableName + 

' WITH CHECK CHECK CONSTRAINT ' + @ForeignKey

PRINT @String

BEGIN TRY

EXEC (@String)

END TRY

BEGIN CATCH

PRINT 'Conflict in ' + @TableName + N'.' + @ForeignKey

DBCC CHECKCONSTRAINTS (@TableName)

END CATCH

FETCH cur INTO @TableName, @ForeignKey

END

CLOSE cur

DEALLOCATE cur

SELECT t.name, fk.name, fk.is_disabled, fk.is_not_trusted

FROM sys.foreign_keys fk

INNER JOIN sys.objects t ON t.object_id = fk.parent_object_id

WHERE fk.is_not_trusted = 1

ORDER BY t.name, fk.name

The script tries to enable each foreign key constraint it finds in the database using the WITH CHECK CHECK CONSTRAINT clause. 

If the check fails, it calls DBCC CHECKCONSTRAINTS which returns the set

of recordings violating the foreign key constraint. 

The result of the script execution shows you which tables and records need to be cleaned up. 

Run the script repeatedly after data cleanup until a single empty result set is returned.

The database cannot be opened because it is version 706. This server supports version 661 and earlier

Today I was trying to Attach a database in SQL server 2008 R2.

I got below error while attaching database:

This is because the data file I was trying to attach is from SQL server 2012. I was trying to attach this in SQL Server 2008. The only solution to this problem is to generate script from SQL server 2012 and than run that script in SQL server 2008.

Get the list of parameters for Stored Procedure

You can get the list of parameters for Stored procedure in two ways:

Solution 1


SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME = 'SP_TempProduct'

Solution 2  

SELECT * FROM SysColumns WHERE ID = OBJECT_ID('SP_TempProduct')  

Cannot insert an explicit value into a timestamp column. Use INSERT with a colimn list to exclude the timestamp column, or insert a DEFAULT into the timestamp column

You will get below error while inserting data into table with TimeStamp column:

Cannot insert an explicit value into a timestamp column. Use INSERT with a colimn list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

The main reason for this error is timestamp data type works in the same way as identity field. You don’t need to pass the value explicitly for this fields, SQL server automatically insert the value based on the system data and time.

If you need to insert a formatted date or time then you need to use datatime data type or change the SQL query.

Error Message 3241

You may get below error message on your screen during SQL server database restore:

“An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)"
Additional information:
The media family on device 'DataBkp.bak' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)”
This generally happens due to many reasons but the most common reason is version mismatch between backup & restore SQL server (To perform backup & restore operation of your SQL server database, you will have required two SQL servers with same version or higher version). You can't restore a backup that was taken from higher version of SQL server to lower version of SQL server. You can not restore a data backup taken on SQL Server 2008 to SQL Server 2005. To solve this error you have to check the SQL Server version of both SQL Server. Run below command to check version: 

Select @@version;
Go

If this command verifies that backup taken SQL server version is higher than restore SQL server version then you have two choice: 
either 
upgrade restore SQL server 
or 
restore backup on the higher version of SQL server.

Alternate Solution: You can also resolve SQL server error message 3241 by performing following steps:

1. Create a blank database in SQL server 2005
2. Generate the script using 'Generate Script Wizard'
3. Now create structure of your database
4. Finally use Import/Export data wizard to import your data.

SQL script to find Foreign key dependencies

Today I was looking for the code to find all Foreign Keys in my table and it’s reference Table. I used the below code t oget that:

SELECT CAST(F.Name AS VarChar(255)) AS [Foreign Key Name], 
CAST(C.Name AS VarChar(255)) AS [Foreign Table], 
CAST(FC.Name AS VarChar(255)) AS [Foreign Column], 
CAST(p.Name AS VarChar(255)) AS [Primary Table], 
CAST(RC.Name AS VarChar(255)) AS [Primary Column] 
FROM 
sysobjects AS F INNER JOIN 
sysobjects AS C ON F.Parent_obj = C.ID 
INNER JOIN sysreferences AS R ON F.ID = R.ConstID 
INNER JOIN sysobjects AS P ON R.RKeyID = p.ID 
INNER JOIN syscolumns AS RC ON R.RKeyID = RC.ID AND R.RKey1 = RC.ColID 
INNER JOIN syscolumns AS FC ON R.FKeyID = FC.ID AND R.FKey1 = FC.ColID 
LEFT JOIN syscolumns AS RC2 ON R.RKeyID = RC2.ID AND R.RKey2 = RC.ColID LEFT JOIN syscolumns FC2 ON R.FKeyID = FC2.ID AND R.FKey2 = FC.ColID 
WHERE F.Type = 'F' 
ORDER BY CAST(P.Name AS VarChar(255))

Left Padding Numerics with Leading Zeros

Many times you may need to show numeric data with a fixed length. If you cast an int to a string datatype you often get a bunch of trailing spaces. What if you convert the int to a fixed length string with STR(), thereby right-aligning the int data, and then change the leading spaces to zero characters with REPLACE(). 

Check example to show the integer 24 as a six character fixed length string with leading zeros:

DECLARE @i int

SET @i = 24

SELECT REPLACE(STR(@i, 6, 0), ' ', '0')

Split Comma-Separated Strings into Table

The below Split function is Table-valued function which would help us splitting comma-separated (or any other delimiter value) string to individual string.

CREATE FUNCTION dbo.SplitString(@String VarChar(8000), @Delimiter Char(1))
returns @TempTable TABLE (Data VarChar(8000))
AS
BEGIN
    DECLARE @iTemp Int
    DECLARE @Value VarChar(8000)

    SELECT @iTemp = 1       
        IF len(@String) < 1 or @String is null return

    WHILE @iTemp != 0
   BEGIN
   SET @iTemp = CharIndex(@Delimiter, @String)

       IF @iTemp != 0
       SET @Value = Left(@String, @iTemp - 1)
ELSE
SET @Value = @String

       IF (len(@Value) > 0)
       INSERT INTO @temptable(Data) Values(@Value)

       SET @String = Right(@String, len(@String) - @iTemp)
   IF len(@String) = 0 Break
END
return
END

Aboce function can be used as

SELECT TOP 5 * FROM dbo.SplitString('Lake Town,Bangur Avenue,Kestopur, Baguiati',',')  

The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)

You must have seen below error while renaming or deleting database. 

The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)" 

Above error normally occurs when the database is in Multi User mode where users are accessing your database or some objects are referring to your database. 

To resolve this error.
First set the database in Single user mode. 
ALTER DATABASE DemoData SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Perform the required action.

Again set the database in multi user mode.

ALTER DATABASE DemoData SET MULTI_USER WITH ROLLBACK IMMEDIATE

Restart SQL Server from command prompt

Sometime time you may need to restart SQL server from command prompt. Below command is used to stop and start SQL Server: 

Net Stop mssql$YourSQLServerInstanceName

Net Start mssql$YourSQLServerInstanceName

Replace carriage return and line feed in a SQL server varchar field

I have been facing an issue while importing some data from SQL to CSV file. In one of my SQL fields user entered Enter Key or Line feed, because of that my exported CSV file was not in good format. Rows were broken in between. The bwloe query replaces the Carriage return and line feed with 'a space:

SELECT REPLACE(REPLACE(Remarks, Char(10),' '), Char(13), ' ') as Remarks FROM DemoTable

Change the Default Value of a Column in already created Table

Use below method to alter or change the default value of a column of an existing table:


ALTER TABLE <Table_Name> DROP CONSTRAINT <Column_Constraint_Name>  
GO
ALTER TABLE <Table_Name> WITH NOCHECK   
ADD CONSTRAINT [<Column_Constraint_Name>] DEFAULT (-10) FOR <Column_Name>

Insert record if not exist

Sometime you may need to insert record in one table if that table does not contain the record. You can use IF to check if something exists (or not), and then act accordingly:


IF NOT EXISTS (SELECT * FROM CustomerMaster WHERE FName = 'Sholo' AND LName = 'Twango')

BEGIN

INSERT INTO 

CustomerMaster 

(FName, LName) 

VALUES 

(

'Sholo', 

'Twango'

) 

END

Querying special character like Percentage in Like operator

It is very tricky to search percentage character in some column. The below query will throw an error on execution:

SELECT LedgerName FROM LedgerMaster WHERE LedgerName LIKE '% %%' 

Instead of above query we had to use below query:


SELECT LedgerName FROM LedgerMaster WHERE LedgerName LIKE '% |%%' ESCAPE '|'

"ESCAPE" keyword are used within an Query to tell the SQL Server that the escaped part of the Query string should be handled differently. In above Query ESCAPE character is mentioned as '\' hence character after '|' is processed differently instead of normal there '%' character is search in "LedgerName" column of "LedgerMaster" table.

Get Start and End date of Fortnight

Sometime it is needed to get the start and end date of fortnight.
Use below query to get the result:


DECLARE @DateInput DATETIME
SET @DateInput = '28-Jan-2013'

DECLARE @FNStartDate DATETIME
DECLARE @FNEndDate DATETIME

IF DAY(@DateInput) < 16  -- First Half of Month
BEGIN
SET @FNStartDate = CAST(CAST(YEAR(@DateInput) AS VARCHAR(4)) + '/' + CAST(MONTH(@DateInput) AS VARCHAR(2)) + '/01' AS DATETIME)
SET @FNEndDate = CAST(CAST(YEAR(@DateInput) AS VARCHAR(4)) + '/' + CAST(MONTH(@DateInput) AS VARCHAR(2)) + '/15' AS DATETIME)
END
ELSE -- Second Half of Month
BEGIN
SET @FNStartDate = CAST(CAST(YEAR(@DateInput) AS VARCHAR(4)) + '/' + CAST(MONTH(@DateInput) AS VARCHAR(2)) + '/16' AS DATETIME)
SET @FNEndDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@DateInput)+1,0))
END

SELECT @FNStartDate AS 'StartDate', @FNEndDate AS 'EndDate'

Slow Query Performance in SqlDataReader vs. Management Studio

Today I was working on a query for a web application, I noticed that the query which executes within a second in Management Studio, was taking nearly a minute when executed by a stored procedure called via SqlDataReader.ExecuteReader(). I was surprised to see the result as I had not seen such huge difference of time in query execution. The query performs a LIKE operation on an indexed computed NVARCHAR column. 

The SET ARITHABORT setting which seems to take a different value in Management Studio and .Net libraries. The ARITHABORT setting is by itself a source of many questions.

The setting of SET ARITHABORT is set at execute or run time and not at parse time.

SET ARITHABORT must be ON when creating or manipulating indexes on computed columns or indexed views.

After adding a SET ARITHABORT ON statement to the stored procedure, the query performed the same in both conditions.

Get First and last day of week

Use below function to get the First day of week


CREATE FUNCTION Week1stDay (@DateInput DateTime)
-- Add the parameters for the function here

RETURNS DateTime
AS
BEGIN
DECLARE @Result DateTime

-- Add the T-SQL statements to compute the return value here
-- 1 -> Sunday, 7 -> Saturday
SELECT @Result = DATEADD(DAY, 1- DATEPART(DW, @DateInput), @DateInput)
RETURN @Result
END

Use below function to get the Last day of week

CREATE FUNCTION WeekLastDay (@DateInput DateTime)

-- Add the parameters for the function here

RETURNS DateTime
AS
BEGIN
DECLARE @Result DateTime
-- Add the T-SQL statements to compute the return value here
-- 1 -> Sunday, 7 -> Saturday
SELECT @Result = DATEADD(DAY, 7- DATEPART(DW, @DateInput), @DateInput)
RETURN @Result
END

Run below script to get the values from function:

SELECT dbo.Week1stDay(GETDATE()) AS StartDate

SELECT dbo.WeekLastDay(GETDATE()) AS EndDate