SpurData Blog

Just sticking this here for my own future reference: I asked on 09/11/2024: Wasn't there already an ODBC drive for SQL Server? I'm so confused, lots of drivers have come and gone, and I can't remember their history or which one I should use for which version of SQL Server. CoPilot Responds: I get it, the history of SQL Server drivers can be quite confusing! Here’s a brief overview to help clarify things: ODBC Drivers : SQL Server ODBC Driver : This was the original driver and is still part of Windows Data Access Components, but it’s not recommended for new development 1 . SQL Server Native Client (SNAC) : Introduced with SQL Server 2005, it included both ODBC and OLE DB interfaces. It was used up to SQL Server 2012 1 . Microsoft ODBC Driver for SQL Server : This is the current driver, introduced after SQL Server 2012. It’s updated with the latest features and is recommended for new development 1 . OLE DB Drivers : Microsoft OLE DB Provider for SQL Server (SQLOLEDB) : Part o...

I've been playing with SQL Agent tokens more, researching how Ola Hallengren uses them in his famous Maintenance Solution, and how you can use them. Particularly I was looking for a way to specify the "ServerName" in the Output File path. You can do this using a token instead of having to customize the MaintenanceSolution.sql script for each server. This is helpful when you're doing environment takeovers and trying to get consistent backups going across several servers. Here is an example of our solution, which is working well. You would modify line 29 of Ola's script:   DECLARE @OutputFileDirectory nvarchar ( max ) = '\\MyBackupServer\DBBACKUPS\ $(ESCAPE_SQUOTE(SRVR)) \OUTPUT' Then for each job step, Ola will setup the following output file. This can be seen in the "Advanced" properties of a particular Job Step. '\\MyBackupServer\DBBACKUPS\$(ESCAPE_SQUOTE(SRVR))\OUTPUT\$(ESCAPE_SQUOTE(JOBNAME))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(DAT...

As of SQL Server 2012, SQL and other Microsoft services run on “virtual accounts” by default instead of Network Service. This is part of a defense-in-depth strategy whereby the service running SQL is isolated from other services on the box. The virtual accounts have names like “NT SERVICE\MSSQLSERVER” and “NT SERVICE\SQLSERVERAGENT”. These are local accounts and have pretty limited access to the network. When they do access the network, they present the credentials of the Server, using the server’s name in the form of “DOMAIN\ServerName$”, eg, “Contoso\FinanceServer$”.

If you find the topic of SQL Server deadlocks a little intimidating and mysterious, you are not alone. But fear not, this Quick Start lays out the practical shortcut I bet is sufficient for most scenarios. Definition: What is a Deadlock A deadlock occurs when 2 threads are competing for similar resources, and Thread 1 HAS A and WANTS B, and Thread 2 HAS B and WANTS A. They’re stuck. SQL Server picks a loser and throws error 1205 "Transaction... has been chosen as the deadlock victim." The BIG PROBLEM if your application has a lot of deadlocks is unpredictable application behavior . This can especially show up in apps that call large stored procs where the failing statement gets rolled back, but the proc itself may just keep on trucking with the next statement. This is all based on what kind of error handling you're doing, whether you have XACT_ABORT on, whether you're doing explicit transactions with BEGIN TRAN or not, etc. Very Probably your application is no...

This is the best link ever on SQL Server support dates, not sure how I missed it before: http://support.microsoft.com/lifecycle/?c2=1044  

Neat trick in SQL Server 2005. If you need to reassign or replace a SQL Agent Operator, you can do so during the delete of the old operator. Like I had a bunch of stuff attached to a "ThisDBA" type operator and needed to reassign it to the "DBAGroup" operator. Here was the fastest path: Create your new Operator. Under "SQL Server Agent", "Properties", "Alert System": Change the "fail-safe operator" to your New Operator. Delete your Old Operator. Near the bottom of the Delete dialog screen is a "Reassign Operator" checkbox. Check that and choose your New Operator from the Popup. Click "Okay" twice and now your jobs and alerts are all attached to the New Operator. SWEET!

If you're trying to apply SQL 2005 SP4 (or any SQL service pack), and it's failing, and you're seeing error "MSP Error:  29537  SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]Invalid object name 'MSreplication_subscriptions'", then you've hit this bug which Microsoft is currently saying they "Won't Fix": https://connect.microsoft.com/SQLServer/feedback/details/521231/failure-during-server-script-upgrade-process-on-database-in-sp-vupgrade-replication-procedure#tabs Probably you've done one of 2 things: You're using synonyms that match the system replication table names You've got some vestige replication tables laying around in one of your old databases My case was the latter. The fix was to find the vestige tables and get rid of them. I found them using the following script: [sourcecode language="sql"] /* This will detect db's containing vestiges of Replication...