SQL: Using dates in the WHERE clause

To delete records of from a specific table, the date column cannot be searched as another text/varchar column. The column needs to be converted to a specific format and and search needs to performed on it.
 
Example: To delete all records in the table where the dates are 2nd and 26th of March 2009, here [...]

Read Full Post »

SQL Server 2008 – Jump start !

If you are interested about the latest offerings in SQL Server 2008 …

A complete set of training material has been released by Microsoft – which is free to access and download. There are 05 tracks available
 
1) Overview
2) Database Infrastructure
3) Business Intelligence track
4) Developer track (software + service)
5) Application compatibility and upgrade
 
Also, the virtual [...]

Read Full Post »

SQL : Deadlock priority

While using BizTalk with SQL, I have often come across scenarios where my database instances are being deadlocked. Mostly this has happened because another instance of my application has access to it.
One of the great things about BizTalk – is the retry functionality of the ports. Even, if my resources are deadlocked - BizTalk would retry [...]

Read Full Post »

Reset Auto Increment ID value / Insert Custom ID value

Often while testing our application, we perform several insert/update/deletes on a table. Assuming the primary key (identifier) was an AUTO increment ID field, after several test runs there is a point – where we would like to start fresh – again!. (A familiar pattern while developing/debugging J )
After deleting the table records, my next ID [...]

Read Full Post »

SQL: View locks on your database

It is generally a good practice -during dev work to view the impact that your components could have your application database. Of the many analysis that could be done in this regard ,  one such metrics is the monitoring of locks. SQL provides one such stored proc called the sp_lock. In using this, I have to filter [...]

Read Full Post »

Failed to connect to server "machinename" (Microsoft.SqlServer.SmoEnum)

To reproduce the error..
Created a SQL login – Windows Authentication
The default DB = myTempDb
Deleted myTempDb using the Sql Server Management Studio.

Solution:
Change the default Db to master and it solves the problem..
sp_defaultdb ‘MyDomain\Zeeshan’, master

Read Full Post »