Sql Server tSql: DELETE statement executes but won't delete

This post has moved along with the rest of this blog to WordPress please go to this link here: http://obscureproblemsandgotchas.com/uncategorized/sql-server-tsql-delete-statemen/

I have no words for how bad this is

This post has moved along with the rest of this blog to WordPress please go to this link here: http://obscureproblemsandgotchas.com/uncategorized/i-have-no-words-for-how-bad-this-is/

How to use Excel and tSQL to help you Generate Code

This post has moved along with the rest of this blog to WordPress please go to this link here: http://obscureproblemsandgotchas.com/uncategorized/how-to-use-excel-and-tsql-to-help-you/

TSQL: Passing objects to stored procedures via XML

This post has moved along with the rest of this blog to WordPress please go to this link here: http://obscureproblemsandgotchas.com/uncategorized/tsql-passing-objects-to-stored/

How to Kill all Processes for a Specific Database

Usually you would just use the Activity Monitor like shown below...

The mundane repetition of right click each process and selecting
Kill Process becomes annoying after the n_th process. 

...buuuuut if you need to restore your Dev Database with fresh data, you need to kill all of the individual connections/processes before attempting a restore. Well when you are doing development work you can have over 10+ connections and using the Activity Monitor is very annoying to close all of them. Therefore I have come up with the following tSQL query:

SELECT
 dbid, 
 db_name(dbid) as DatabaseName,
 spid,
 KillStatement = 'KILL ' + CAST(spid AS varchar(10)) + '; '
FROM sys.sysprocesses
WHERE dbid = 15 -- Put your Databases ID here

The idea behind this query is to first find out what you database's ID is, enter the ID in the where clause and generate the KILL statements. Copy and paste the KILL statements and execute all of them at once. You will be warned about killing your own connection though like this:

Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.

No worries though you can just kill that process in the Activity Monitor or just close the query window.

Enjoy

How to get Filename and File Extension from a Column with tSQL

This post has moved along with the rest of this blog to WordPress please go to this link here: http://obscureproblemsandgotchas.com/uncategorized/how-to-get-filename-and-file-extension/

Straight to the Point: Query for Database and Table Sizes

This post has moved along with the rest of this blog to WordPress please go to this link here: http://obscureproblemsandgotchas.com/uncategorized/straight-to-point-query-for-database/

Selecting Random Date Between a Date Range with tSQL

This post has moved along with the rest of this blog to WordPress please go to this link here: http://obscureproblemsandgotchas.com/uncategorized/selecting-random-date-between-date/

UPDATE SELECT FROM JOIN

This post has moved along with the rest of this blog to WordPress please go to this link here: http://obscureproblemsandgotchas.com/uncategorized/update-select-from-join/