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)
Falling Skies – Religious Hang Ups
I’m really enjoying Falling Skies, I have to say. I was drawn to it for the Steven Spielberg connection, so I thought it can’t be that bad (despite The 4400) and so far it isn’t. It’s a very similar set up to The Walking Dead, but I think I prefer Falling Skies.
What I don’t like, is that at the end of the last episode I watched (Season 1, Episode 4, Grace), it went all religious with hand-holding and praying.
I hope this is just a tip of the cap (although the necessity of this is bad enough) to keep Christian America happy and doesn’t progress into an overriding moralistic sub-plot.
Rant-over.
Doug Stanhope’ll make me feel better.
My New Favourite Band, The Decemberists
A great acoustic performance from the lead singer of my new favourite band, The Decemberists
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
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
New (to me) .NET Type ‘List’
I need to use this more, I’m sure it’s (not noticably) slower than ArrayList but it is strongly-typed and it inherits standard collection functions such as .IndexOf()
Dim myListOfStrings As List(Of String)
Dim myListOfMojinators As List(Of MyCustomClass.Mojinator)
Cool CSS3 and jQuery Animation Demos
www.webdesignersblog.net/css3/35-best-awesome-css3-animation-demos
Inserting a new blank row in a DataTable
This always escapes me when I need it, so here it is for posterity:
Dim myDataRow As DataRow = myDataTable.NewRow()
myDataTable.Rows.InsertAt(myDataRow, 0)
The second arguement is the index at which the new row should be added.
An alternate short-hand way of writing this:
myDataTable.Rows.InsertAt(myDataTable.NewRow(), 0)
This is just a flickr test post
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas non arcu sit amet ipsum auctor placerat. Nunc euismod sagittis dolor, id suscipit mi volutpat eu. Morbi sem arcu, mattis vulputate condimentum at, faucibus ut nulla. Proin id elementum nunc. Proin nec hendrerit ante.
Testing Flickr Gallery plugin:
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas non arcu sit amet ipsum auctor placerat. Nunc euismod sagittis dolor, id suscipit mi volutpat eu. Morbi sem arcu, mattis vulputate condimentum at, faucibus ut nulla. Proin id elementum nunc. Proin nec hendrerit ante.
Vestibulum lobortis, augue non volutpat imperdiet, nunc erat adipiscing enim, sit amet hendrerit lorem ipsum et orci. Vestibulum imperdiet, tellus at mattis viverra, enim odio lobortis quam, sed pretium diam nisl ac nisl.
Integer in massa neque. Curabitur ultricies consectetur metus, vitae rutrum tellus feugiat sit amet.
Hello world!
Welcome to WordPress. This is your first post. Edit or delete it, then start blogging!
Sample syntax code:
Dim str_demo as string
str_demo = "Hello World!"
Response.Write("str_demo says:" & str_demo)
Last bit of text





