split comma separated string to integer

Sometime we need to split a comma separated string to a Table of integers. The below function will split the given comma-separated string into integers and process the results.


CREATE Function SplitStringtoInt(@strTemp nVarChar(4000)) Returns @intTable Table([Value] [Int] NOT NULL)
AS
BEGIN
    DECLARE @intValue nVarChar(100)
    DECLARE @pos int
    -- TRIMMING THE BLANK SPACES
    SET @strTemp = LTRIM(RTRIM(@strTemp))+ ',' 
    -- OBTAINING THE STARTING POSITION OF COMMA IN THE GIVEN STRING
    SET @pos = CHARINDEX(',', @strTemp, 1) 
    -- CHECK IF THE STRING EXIST FOR US TO SPLIT
    IF REPLACE(@strTemp, ',', '') <> '' 
    BEGIN
        WHILE @pos > 0
        BEGIN
-- GET THE 1ST INT VALUE TO BE INSERTED
            SET @intValue = LTRIM(RTRIM(LEFT(@strTemp, @pos - 1))) 
            IF @intValue <> ''
            BEGIN
                INSERT INTO @intTable (Value) 
                VALUES (CAST(@intValue AS bigint)) 
            END
            -- RESETTING THE INPUT STRING BY REMOVING THE INSERTED ONES
            SET @strTemp = RIGHT(@strTemp, LEN(@strTemp) - @pos) 
            -- OBTAINING THE STARTING POSITION OF COMMA IN THE RESETTED NEW STRING
            SET @pos = CHARINDEX(',', @strTemp, 1) 
        END
    END    
    RETURN
END

Usage: SELECT * FROM dbo. SplitStringtoInt ('12345,87612,988473')

Query to write/create a file

This SQL stored procedure will allow you to write to the file on your system where SQL Server is running. If you are using this with your local SQL server then it will write and create files on your local file system and if you are on the remote machine, the file system will be the remote file system.

You need to reconfigure some advanced SQL server settings to use below stored procedure. Use the below configuration query to enable 'OLE Automation Procedures'. If this is not enabled and you try executing the procedure you will get errors.

Use master

GO

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1

GO

--To enable Ole automation feature

EXEC sp_configure 'Ole Automation Procedures', 1;

GO

RECONFIGURE;

GO

Now, the stored procedure to create file in local system: 

Create Procedure  [dbo].[USP_SaveFile](@text as NVarchar(Max),@Filename Varchar(200)) 

AS

Begin

declare @Object int,

        @rc int, -- the return code from sp_OA procedures 

        @FileID Int

EXEC @rc = sp_OACreate 'Scripting.FileSystemObject', @Object OUT

EXEC @rc = sp_OAMethod  @Object , 'OpenTextFile' , @FileID OUT , @Filename , 2 , 1 

Set  @text = Replace(Replace(Replace(@text,'&','&'),'<' ,'<'),'>','>')

EXEC @rc = sp_OAMethod  @FileID , 'WriteLine' , Null , @text  

Exec @rc = master.dbo.sp_OADestroy @FileID   

  

Declare @Append  bit

Select  @Append = 0

  

If @rc <> 0

Begin

    Exec @rc = master.dbo.sp_OAMethod @Object, 'SaveFile',null,@text ,@Filename,@Append

End

Exec @rc = master.dbo.sp_OADestroy @Object 

End

In this procedure the first parameter will take the text to be written to the file and the second parameter will take the complete path of the file to be created with the text in it. You can also use the same procedure to write binary files to the file system, you just need to check and change the file extension in the second parameter. 

EXEC USP_SaveFile 'Writing data to text file', 'D:\Temp\MSSQL.txt'

Useful views and stored procedures

• sys.databases : Lists all the databases in Sql Server.
• sys.tables : Lists all the Tables in the database.
• sys.views : Lists all the views in the database.
• sys.procedures : Lists all the Procedures in the database.
• sys.triggers : Lists all the Triggers in the database.
• sys.columns : Lists all the columns of tables in database.
• sys.syscolumns : Lists all the columns in database including of those SP.
• sys.key_constraints : Lists all primary key or unique constraints in database. For primary key TYPE = 'PK' and for unique keys TYPE = 'UQ'
• sys.check_constraints : Lists all the Check Constraints in the database.
• sys.default_constraints : Lists all the Default Constarints in the database.
• sys.foreign_keys : Lists all the Foreign Keys in the database.
• sys.syslogins : Lists all the login names in server.
• sys.sql_logins : Lists all the Sql Authentication Logins in server.
• sys.sysusers : Lists all the users in database.

Check leap year

There are various techniques to determine whether a given year is a leap year. You can use the below SQL function to check for leap year:


CREATE FUNCTION F_BIT_LEAP_YEAR
(@p_year SMALLINT)
RETURNS BIT
AS
BEGIN
    DECLARE @p_leap_date SMALLDATETIME
    DECLARE @p_check_day TINYINT

    SET @p_leap_date = CONVERT(VARCHAR(4), @p_year) + '0228'
    SET @p_check_day = DATEPART(d, DATEADD(d, 1, @p_leap_date))
    IF (@p_check_day = 29)
        RETURN 1

    RETURN 0  
END

Recompile Stored Procedures, Views and Functions

Sometimes after massive changes in MSSQL database, there is a need to recompile all stored procedures, user-defined functions and views in the database in order to MSSQL will refresh stores procedures execution plans stored in memory in order to reflect recent schema changes. Below is a small MSSQL code snipped written solely for maintenance purposes. It goes through database objects and performs recompilation using sp_recompile system stored procedure.


DECLARE proccurs CURSOR
FOR
    SELECT [name] FROM sysobjects
WHERE xtype in ('p', 'v', 'fn')

OPEN proccurs
DECLARE @pname VARCHAR(60)

FETCH NEXT FROM proccurs INTO @pname
WHILE @@fetch_status = 0
BEGIN
    EXEC sp_recompile @pname
    FETCH NEXT FROM proccurs INTO @pname
END
CLOSE proccurs
DEALLOCATE proccurs

Cleaning up TempDB

In most situations, TempDB is not used heavily enough to be a problem, but occasionally due to non-ideal code, many tables are created and left with no regular cleanup.

The most obvious flaw in using Temp Tables in a 24 X 7 Productions Environment, is that if the SQL Server never reboots, than the TempDB never gets cleaned out (unless it’s by the code that created the Temp Tables, and that never happens. To deal with this issue, I found a SP that runs once an hour, deleting Temp Tables that are more than 12 hours old (unless they reside in a Table (TempTableToKeep) that I use to store the names of tables I want to keep, and the date/time to finally delete them.


IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[TempTableToKeep]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[TempTableToKeep]
GO
CREATE TABLE [dbo].[TempTableToKeep] (
[TempTable] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateToDelete] [datetime] NOT NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT name FROM sysobjects WHERE  name = N'sp_DropTempTables' AND type = 'P')
DROP PROCEDURE sp_DropTempTables
GO
CREATE PROCEDURE sp_DropTempTables
AS
DECLARE @Cursor AS CURSOR
DECLARE @Name AS VARCHAR(100)
DECLARE @TableName AS SYSNAME
DECLARE @Owner AS VARCHAR(100)
DECLARE @SQL AS NVARCHAR(200)
SET @Cursor = CURSOR SCROLL FOR
SELECT    tempdb.dbo.sysobjects.name, tempdb.dbo.sysobjects.*
FROM    TempTableToKeep
RIGHT OUTER JOIN tempdb.dbo.sysobjects ON TempTableToKeep.TempTable = tempdb.dbo.sysobjects.name
WHERE    ((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = 'U') AND (TempTableToKeep.DateToDelete < GETDATE())) OR
((tempdb.dbo.sysobjects.crdate < DATEADD(hh, -12, GETDATE())) AND (tempdb.dbo.sysobjects.type = 'U') AND (TempTableToKeep.DateToDelete IS NULL))
OPEN @Cursor
FETCH FIRST FROM @Cursor
INTO @Name, @Owner
WHILE (@@FETCH_STATUS = 0)
BEGIN
If (@@FETCH_STATUS = 0)
BEGIN
IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE  name = @Name AND type = 'U')
BEGIN
SET @SQL = 'DROP TABLE tempdb..' + @Name
--PRINT @SQL
EXECUTE sp_executesql @SQL
END
FETCH NEXT FROM @Cursor
INTO @Name
END
END
CLOSE @Cursor
DEALLOCATE @Cursor
GO

Ref: http://gaptheguru.wordpress.com/2012/02/16/cleaning-up-tempdb/

Find the job activity details

I have got a script to find the job activity details, this script will give the output similar to job activity monitor in SQL2005 or later. The script will fetch the following details

• Server Name
• Job Name
• Job Status
• Last Run Status
• Last Run Date and Time
• Run Duration
• Next Run Date and Time
• Message about status of the job

SET NOCOUNT ON
--Checking for SQL Server verion
IF CONVERT(tinyint,(SUBSTRING(CONVERT(CHAR(1),SERVERPROPERTY('productversion')),1,1))) <> 8
BEGIN
---This is for SQL 2k5 and SQL2k8 servers
SET NOCOUNT ON
SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,
j.name AS job_name,
CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,
CASE jh.run_status WHEN 0 THEN 'Error Failed'
                WHEN 1 THEN 'Succeeded'
                WHEN 2 THEN 'Retry'
                WHEN 3 THEN 'Cancelled'
                WHEN 4 THEN 'In Progress' ELSE
                'Status Unknown' END AS 'last_run_status',
ja.run_requested_date as last_run_date,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,
ja.next_scheduled_run_date,
CONVERT(VARCHAR(500),jh.message) AS step_description
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id)  from msdb.dbo.sysjobactivity) ORDER BY job_name,job_status
END
ELSE
BEGIN
--This is for SQL2k servers
SET NOCOUNT ON
DECLARE @SQL VARCHAR(5000)
--Getting information from sp_help_job to a temp table
SET @SQL='SELECT job_id,name AS job_name,CASE enabled WHEN 1 THEN ''Enabled'' ELSE ''Disabled'' END AS job_status,
CASE last_run_outcome WHEN 0 THEN ''Error Failed''
                WHEN 1 THEN ''Succeeded''
                WHEN 2 THEN ''Retry''
                WHEN 3 THEN ''Cancelled''
                WHEN 4 THEN ''In Progress'' ELSE
                ''Status Unknown'' END AS  last_run_status,
CASE RTRIM(last_run_date) WHEN 0 THEN 19000101 ELSE last_run_date END last_run_date,
CASE RTRIM(last_run_time) WHEN 0 THEN 235959 ELSE last_run_time END last_run_time,
CASE RTRIM(next_run_date) WHEN 0 THEN 19000101 ELSE next_run_date END next_run_date,
CASE RTRIM(next_run_time) WHEN 0 THEN 235959 ELSE next_run_time END next_run_time,
last_run_date AS lrd, last_run_time AS lrt
INTO ##jobdetails
FROM OPENROWSET(''sqloledb'', ''server=(local);trusted_connection=yes'', ''set fmtonly off exec msdb.dbo.sp_help_job'')'
exec (@SQL)
--Merging run date & time format, adding run duration and adding step description
select Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,jd.job_name,jd.job_status,jd.last_run_status,
CONVERT(DATETIME,RTRIM(jd.last_run_date)) +(jd.last_run_time * 9 + jd.last_run_time % 10000 * 6 + jd.last_run_time % 100 * 10) / 216e4 AS last_run_date,
CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108) AS run_duration,
CONVERT(DATETIME,RTRIM(jd.next_run_date)) +(jd.next_run_time * 9 + jd.next_run_time % 10000 * 6 + jd.next_run_time % 100 * 10) / 216e4 AS next_scheduled_run_date,
CONVERT(VARCHAR(500),jh.message) AS step_description
from (##jobdetails jd  LEFT JOIN  msdb.dbo.sysjobhistory jh ON jd.job_id=jh.job_id AND jd.lrd=jh.run_date AND jd.lrt=jh.run_time) where step_id=0 or step_id is null
order by jd.job_name,jd.job_status
--dropping the temp table
drop table ###jobdetails
END

Output:

Rebuild MSDB Database

If the transaction log file for MSDB database is accidentally deleted than follow below steps to rebuild the MSDB database:

1. Stop the SQL Server service & start the command prompt with elevated administrative privilege & execute the following command:
   NET START MSSQLSERVER /T3608
2. Once you start the SQL Server with trace flag 3608, you will be able to detach the msdb database. To do that, execute the following command in SQLCMD mode:
   SQLCMD -E -S DBS03 -dmaster -Q"EXEC sp_detach_db msdb"
3. Rename the msdb data file, and execute the instmsdb.sql file from the install folder, as shown below:
   SQLCMD -E -S DBS03 -i"C:\SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Install\instmsdb.sql" -o"C:\instmsdb.out"
4. Review the instmsdb.out for any errors and re-apply the service packs.
5. Stop the SQL Server.
6. Start the SQL Server normally.

Creating a linked server in SQL Server for My SQL Database

Here I am describing how to create a Linked Server reference in SQL Server which points at a MySQL database, and how to query through the Linked Server connection.

1. Download the MySQL ODBC driver from dev.mysql.com

2. Install MySQL ODBC driver on Server where SQL Server resides
- Double Click Windows Installer file and follow directions.

3. Create a DSN using the MySQL ODBC driver
Start-> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC)
- Click on the System DSN tab
- Click Add
- Select the MySQL ODBC Driver
- Click Finish

On the Login Tab:
- Type a descriptive name for your DSN.
- Type the server name or IP Address into the Server text box.
- Type the username needed to connect to the MySQL database into the user text box.
- Type the password needed to connect to the MySQL database into the password text box.
- Select the database you’d like to start in.

On the Advanced Tab:
Under Flags 1:
- Check Don’t Optimize column Width.
- Check Return Matching Rows
- Check Allow Big Results
- Check Use Compressed Protocol
- Check BIGINT columns to INT
- Check Safe
Under Flags 2:
- Check Don’t Prompt Upon Connect
- Check Ignore # in Table Name
Under Flags 3:
- Check Return Table Names for SQLDescribeCol
- Check Disable Transactions

Now Test your DSN by Clicking the Test button

4. Create a Linked Server in SSMS for the MySQL database
SSMS (SQL Server Management Studio -> Expand Server Objects
- Right Click Linked Servers -> Create New Linked Server

On the General Page:
- Linked Server: Type the Name for your Linked Server
- Server Type: Select Other Data Source
- Provider: Select Microsoft OLE DB Provider for ODBC Drivers
- Product name: Type MySQLDatabase
- Data Source: Type the name of the DSN you created

On The Security Page
- Map a login to the Remote User and provide the Remote User’s Password
- Click Add under Local Server Login to Remote Server Login mappings:
- Select a Local Login From the drop down box
- Type the name of the Remote User
- Type the password for the Remote User

5. Change the Properties of the Provider MSDASQL
Expand Providers -> Right Click MSDASQL -> Select Properties
- Enable Nested Queries
- Enable Level Zero Only (this one’s the kicker)
- Enable Allow InProcess
- Enable Supports ‘Like’ Operator

6. Change settings in SQL Server Surface Area Configuration for Features
- Enable OPENROWSET and OPENDATASOURCE support.
sp_configure ‘show advanced options’, 1
reconfigure
sp_configure ‘Ad Hoc Distributed Queries’, 1
reconfigure

7. Change settings in SQL Server Surface Area Configuration for Services and Connections
- Enable Local and Remote connections via TCP/IP and named pipes

8. Restart the SQL Server and SQL Server Agent services.

9. Testing the Connection – Example Query:
select * from openquery(MyLinkedServer,’select * from MyTable’)

Ref: http://blog.lekevin.com/computer/creating-a-linked-server-in-sql-server-for-a-mysql-database/

Check constraint to allow alphabets and numbers only in SQL Server

How to create a check constraint to allow alphabets i.e. A-Z characters only or alphabets + numbers with no special characters in Sql Server?
Here is the scenario, you have column with varchar/char datatype and you don’t want user to any Special characters like ~, !, #, $, %, ^.Check constraint for allowing Alphabets only 

ALTER TABLE TableName ADD CONSTRAINT Only_Characters CHECK ColumnName NOT LIKE '%[^A-Z ]%' 

Check constraint for allowing Alphabets  + Numbers  only

ALTER TABLE TableName ADD CONSTRAINT Only_Characters_And_Numebrs CHECKColumnName NOT LIKE '%[^A-Z0-9 ]%' 

Note: Remember to add extra space. 

Errors related to restore database

Error Restore backup on same server


Sometime when you try to restore the backup of the same database on the same server with a different name using query like this: 

RESTORE DATABASE DemoData FROM DISK = 'c:\Temp\DempDB.bak’ 

You may get the following error:


Msg 1834, Level 16, State 1, Line 1 
The file 'C:\Program Files\Microsoft SQL 
Server\MSSQL10.SQLSERVER2008SE\MSSQL\DATA\DemoData.mdf' cannot be overwritten.  It is being used by database 'DemoDB'. 
Msg 3156, Level 16, State 4, Line 1 
File 'Vinay' cannot be restored to 'C:\Program Files\Microsoft SQL 
Server\MSSQL10.SQLSERVER2008SE\MSSQL\DATA\DemoData.mdf'. Use WITH MOVE to identify a valid location for the file. 
Msg 1834, Level 16, State 1, Line 1 
The file 'C:\Program Files\Microsoft SQL 
Server\MSSQL10.SQLSERVER2008SE\MSSQL\DATA\ DemoData _log.LDF' cannot be overwritten.  It is being used by database '  DemoDB'. 
Msg 3156, Level 16, State 4, Line 1 
File 'Vinay_log' cannot be restored to 'C:\Program Files\Microsoft SQL 
Server\MSSQL10.SQLSERVER2008SE\MSSQL\DATA\ DemoData _log.LDF'. Use WITH MOVE to identify a valid location for the file. 
Msg 3119, Level 16, State 1, Line 1 
Problems were identified while planning for the RESTORE statement. Previous messages provide 
details. 
Msg 3013, Level 16, State 1, Line 1 
RESTORE DATABASE is terminating abnormally. 



The reason for this is that when you restore the database without explicitly define the data and log file information it will take the same information which was there from BACKUP database, but when you restore on the same database where the original database already have the data and log file exists, restore will fail with above error.


This error will not occur when you restore the database from “GUI” where it takes the new 
database name as file name for mdf and ldf files.

Alternatively you may provide the file name explicitly when you restore in script.

First check the file information with Restore Headeronly command. and run restore including file (mdf and ldf) path.

Restore failed if target database is in use


You will get below error when you try to restore the database if it is already in use by other 
processes(user) 


Msg 3102, Level 16, State 1, Line 1 
RESTORE cannot process database 'a' because it is in use by this session. It is recommended that the master database be used when performing this operation. 
Msg 3013, Level 16, State 1, Line 1 
RESTORE DATABASE is terminating abnormally.


Always check if any active session exists for the database which you are going to restore, or the restoring database name already exits. Restore require exclusive (X) permission on database.

Restore failed when insufficient free space on destination path


Msg 3257, Level 16, State 1, Line 1 
There is insufficient free space on disk volume 'C:\' to create the database. The database requires 521461696 additional free bytes, while only 518231616 bytes are available. 
Msg 3119, Level 16, State 4, Line 1 
Problems were identified while planning for the RESTORE statement. Previous messages provide details. 
Msg 3013, Level 16, State 1, Line 1 
RESTORE DATABASE is terminating abnormally.


If the destination database size is higher than the free space available on the drive where we are restoring, we will get this error, check the database size required  with Restore FileListOnly command. Restore will create the same size of mdf and ldf of the state when you backed up the source database, you cannot shrink any file once you backed up. Restore will make exact same state of files and database where you backed up.

When try to restore Newer version of sql server to older version gives error


Server: Msg 602, Level 21, State 50, Line 1 
Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

It is not possible to restore the database from newer version to older version.

Linked server error message 7399

Sometime you may get the below error when you run a query on Excel or DBF file in SQL Server:

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "".

This error generally comes only when you try to query from a remote machine. And this is something to do with the Security Context of the Linked Server.

Solution to this problem is very simple. Simply follow steps -
1. Open the SQL Server Management Studio and go to Server Objects and then to Linked Server.
2. Right click on your Linked Server Name, and click on Properties.
3. Go to Security Page. Here there are two options. You can try any one option.

• Select the option “Be made without using a security context” by clicking on radio button.

                                                  OR

• Select the last option “Be made using this security context ”.

When click on the radio button before “Be made using this security context ”, the“Remote login” and “With password” text boxes become active to be filled in.
Now in the “Remote login”, enter Admin as the login user. Leave the password text box as blank.
Now just click on OK, and now can run the query against your linked server without any error.

Database is in transition

When you alter the database to “OFFLINE” and other transactions are happening on that database then the database will be in transition and will require the transition Session to complete, or we may have to kill that session. Database OFFLINE state requires “Exclusive Lock” on the database. So always use “With Rollback Immediate” 

Alter Database <DB> Set Offline With Rollback Immediate;