mojinator.com

How to Kill Open User Processes in SQL

Sometimes when I attempt to restore an SQL database, I get a message pretty much saying ‘the DB is in use’. So I use this, to ensure any open connections to the DB are disconnected (killed):

DECLARE @dbName NVARCHAR(50)
SET @dbName = N'NameOfYourDB'

DECLARE @sqlKill VARCHAR(max)
SET @sqlKill = ''

SELECT @sqlKill = @sqlKill + 'Kill ' + CONVERT(VARCHAR, SPId) + ';'
FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@dbName) AND SPId <> @@SPId

EXEC(@sqlKill)

Tags: SQL

Use TSQL to Create a Text File

I recently had to shift the responsibility of creating a text file from the web server, to the DB server, this is how I did it:

DECLARE @OLEfilesytemobject INT
DECLARE @OLEResult INT
DECLARE @FileID INT  

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @OLEfilesytemobject OUT
IF (@OLEResult <> 0)
	PRINT 'Error: Scripting.FileSystemObject...'
ELSE
BEGIN  

	-- check if file exists
	EXEC sp_OAMethod @OLEfilesytemobject, 'FileExists', @OLEresult OUT, @filePathAndName   

	-- if file exists
	IF (@OLEresult=1)
	BEGIN
		PRINT 'File exists, abort...'
	END
	ELSE IF (@OLEresult=0)
	BEGIN
		PRINT 'File does not exist, continue...'
		-- open file
		EXECUTE @OLEResult = sp_OAMethod @OLEfilesytemobject, 'OpenTextFile', @FileID OUT, @filePathAndName, 8, 1
		-- write text to the file
		EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @fileContents
	END

	EXECUTE @OLEResult = sp_OADestroy @FileID
	EXECUTE @OLEResult = sp_OADestroy @OLEfilesytemobject 

	SET @fileCreated = 1

END

If you want to append to an existing text file, simply remove the if statement on line 6 of the code.

Also, there are some settings on the SQL instance you need to update, in able to access the file system on the server:

Enable OLE Automation

• Open Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration > Click Surface Area Configuration for Features
• Under Database Engine > OLE Automation, check Enable OLE Automation

Give DB user rights to EXECUTE relevant file system functions

• In SQL Server Management Studio, add the SQL login that has been created for use by this project to the ‘Master’ SQL System Database
• Browse to the database Master > Programmability > Extended Stored Procedures > System Extended Stored Procedures
• For each of the following system stored procedures, add the user (applied in step 1 above) to the permissions list and ‘Grant’ the Explicit > Execute right:
• sys.sp_OACreate
• sys.sp_OADestroy
• sys.sp_OAMethod

Tags: SQL

How to rename a table in SQL, using SP_RENAME

This is an easy way to rename a table in SQL:

EXEC SP_RENAME '[existing_table_name]', '[new_table_name]'


I’ve used this to create archives of tables, you can rename the existing table name, by appending a timestamp as follows:

DECLARE @archiveTableName NVARCHAR(50)
SET @archiveTableName = 'tbl_myRecords_archived_' + CONVERT(VARCHAR(50) ,CURRENT_TIMESTAMP, 120)
SET @archiveTableName = REPLACE(@archiveTableName, ' ', '')
SET @archiveTableName = REPLACE(@archiveTableName, '-', '')
SET @archiveTableName = REPLACE(@archiveTableName, ':', '')
EXEC SP_RENAME 'tbl_myRecords', @archiveTableName


Tags: SQL