In Sql Server 2000, there is an option to script out all jobs in an instance. That option disappeared in Sql Server 2005. You can still do it. In Object Explorer, click on Jobs folder. Then go to the Object Explorer Details window. All jobs in this instance will be listed there. Select all, and…

When you use DMO to connect to a Sql Server 2005 instance, you will likely get the following error: Microsoft SQL-DMO (ODBC SQLState: 42000) error ‘800ad903’ [Microsoft][ODBC SQL Server Driver][SQL Server]To connect to this server you must use SQL Server Management Studio or SQL Server Management Objects (SMO). This is because in Sql Server 2005,…

In Activity Monitor (Sql Server 2005) or Current Activities (Sql Server 2000), if you suspect a job has been running unusually long, all you see is something like this: SQLAgent – TSQL JobStep (Job 0x1D8274F1H00B7D44912BD78DCADB1AD0 : Step 1) Use this statement to find out the job name: select name from msdb.dbo.sysjobs where job_id = cast(0x1D8274F1H00B7D44912BD78DCADB1AD0…

Say you are migrating databases from one server to another. You don’t want users connect to the old instance by mistake. You can do that by putting them all offline. Below is the script t set nocount on select ‘alter database ‘ + name + ‘ set offline’ from sysdatabases where name not in (‘master’,…

I had some trouble installing SP2 on a Sql Server 2005 64-bit cluster for a client, running on Windows 2003 SP2 OS. All other components patched successfully, except Sql Server Service. It is the single component that failed. The error number on the active node, where I started the install, is error number 11009. The…