CBTr.Net Tech Dump

For some funny and technical stuff on Microsoft SQL 2008, hit up MS SQL Experience The funny section is funny, I got a good laugh from it. The technical stuff is pretty dry …

I was requested to update 7 sql jobs on 8 sql servers this afternoon. The specifics was shown to me via the GUI and I REALLY REALLY REALLY hate doing stuff thru the GUI. To many pointing and clicking and possibility of making a mistake somewhere. The requirement was that in the existing SQL job, [...]

Have you ever wonder how many commands are sitting in your replication queue without having to open up the replication monitor? Or wish that an alert can be sent to you if the commands in the queue passes some sort of threshold? Well, you’re in luck. I wrote the following script back in Apirl when [...]

Prior to SQL 2008, how would you go about declaring and setting a variable? You would do something like this: declare @cmd varchar(100) set @cmd = ‘move /Y path/of/source path/of/target’ It takes two lines: one to declare the variable and then one more to set it to whatever you want. In SQL 2008, you can [...]

You can use the SQL function RAISERROR to spit back error messages to the caller, write it to the event & application log, or cause a job to fail and exit if it matches the criteria. When I first read about this function, I was a bit confused but having used it for a while [...]

Damnit, I had some issues trying to restore this database in SQL 2008 this afternoon. It has the new FILESTREAM thing. You can download the sample database here. when restoring using regular restore statement like: restore database AdventureWorks from disk = ‘C:\program files\microsoft sql server\100\tools\samples\adventureworks2008.bak’ with move ‘AdventureWorks2008_Data’ to ‘C:\Program Files\Microsoft SQL Server\MSSQL10.SQL08\MSSQL\DATA\AdventureWorks2008_Data.mdf’ , move [...]

I have a linked server from SQL 2005 to SQL 2000. Yesterday I tried to update a table on SQL 2000 from SQL 2005. The UPDATE statement looks like this: update sql2000.dbname.dbo.tablename set db = getdate() where processid = 3 SQL errored out with: Could not create an acceptable cursor The table I was trying [...]

Last night, I had to come up with a script that will capture the duration of a SQL job and if that duration exceeded XX min, an email needed to be sent. I didn’t feel like writing a script and I know that someone out had probably written it. I found a few but they [...]

I was doing some work this afternoon and needed to make inserts into some linked server. My syntax was fairly simple: [on serverA\serverA] insert into [server2\server2].dbname.dbo.copy_stats select top 1 * from load_stats order by loadid desc This should work but instead I got the following error: The OLE DB provider “SQLNCLI” for linked server “server1\server1″ [...]

SQL Server offers the system stored procedure sp_procoption which can be used to designate one or more stored procedures to automatically execute when the SQL Server service is started. This is a handy option that can be leveraged for a variety of uses. For instance, you may have an expensive query in your database which [...]