I have a set of python scripts to help me manage a few SQL Servers at work, and one of the things I do is take database backups using BACKUP DATABASE and BACKUP LOG.  I’ve been using pymssql to connect, but today tried switching to pyodbc.  pymssql seems to be having momentum problems, so I figured I’d try the alternative and see how well it works.

I ran into two issues:

• pymssql supports “%s” as a parameter placeholders, and pyodbc wants “?”
• BACKUP and RESTORE operations on SQL Server don’t run like normal queries

The first was trivial to fix, the second took some digging.  If you try to run a BACKUP via pyodbc, the cursor.execute() call starts and finishes with no error, but the backup doesn’t get made.  With help from CubicWeb‘s post MS SQL Server Backuping gotcha, I learned that BACKUP and RESTOREs over ODBC trigger some kind of asynchronous / multiple result set mode.  To get around this, you can poll for file size (as Cubicweb did), but that gets ugly when making a backup on a remote server.

In a backup, I think each “X percent processed.” message is considered a different result set to ODBC. Instead of polling the file size, you can call cursor.nextset in a loop to get all the “percent processed” sets:

After adding that while loop, backups of small and medium sized databases worked like a charm.

A new decade means time for a fresh windows install at work.  I ran into some trouble with windows 7, visual studio 2008, and SQL 2008 Express.  Here’s how I resolved them.  Contrary to most things I found on the web, I’m not using betas or release candidates.

First off, installing SQL 2008 Express.  I only wanted the management tools, and this was a little hard to come by.  I downloaded various EXE files from MSDN, but none of them worked (they would error out, bring up an seemingly unrelated installer, or any other confusing behavior that may have led you here).  Here’s what worked for me:

1. Be sure any previous installation attempts have been purged via Add / Remove Programs
2. Go to the “other install options” page for SQL express: http://www.microsoft.com/express/Database/default.aspx#Installation_Options
3. Click the “Management Tools” install button (for me that’s: http://www.microsoft.com/web/gallery/install.aspx?appsxml=www.microsoft.com%2Fweb%2Fwebpi%2F2.0%2FWebProductList.xml%3Bwww.microsoft.com%2Fweb%2Fwebpi%2F2.0%2FWebProductList.xml&appid=134%3B135)
4. Install the “Microsoft Web Platform Installer” (MWPI) if it asks you to
5. Should be straightforward from here on

The funny thing here is the MWPI seems to download an installer that looks a lot like the one at Microsoft® SQL Server® 2008 Management Studio Express that didn’t work for me.

Next up, Visual Studio 2008 (VS2008).  My company has an MSDN subscription, so we downloaded an ISO (named en_visual_studio_2008_professional_x86_x64wow_dvd_X14-26326.iso) and I used freeware MagicISO to mount it, then ran “setup.exe”.  The install failed on the “Microsoft Visual Studio Web Authoring Component” (MVSWAC).  Here’s what worked for me:

1. IF YOU WANT SQL2008, DO THAT FIRST
2. Be sure any previous installation attempts have been purged via Add / Remove Programs
3. Download WebDesignerCore.EXE from microsoft
4. Run it
5. Install VS2008 from disc/iso as normal.

Digging into the ISO using 7zip, the problem is /WCU/WebDesignerCore/WebDesignerCore.EXE is corrupt.  To get VS2008 to install cleanly, first we need to install MVSWAC, at which point the VS2008 installer will happily skip past the corrupt file.  I ran across several blog/forum posts with horror stories about VS2008 installing SQL2005, and needing to uninstall half the planet to get things working right.

As always, be sure to hit up windows update, and change your update settings so you get fixes for VS2008 and SQL2008.

An update trigger using COLUMNS_UPDATED() was failing, and I figured out the problem:

Some simple SQL:

SELECT CAST(0×0200 as bigint), 0×0200 | 0
SELECT CAST(0×02000 as bigint), 0×02000 | 0
SELECT CAST(0×020000 as bigint), 0×020000 | 0
SELECT CAST(0×0200000 as bigint), 0×0200000 | 0
SELECT CAST(0×02000000 as bigint), 0×02000000 | 0
SELECT CAST(0×020000000 as bigint), 0×020000000 | 0
SELECT CAST(0×0200000000 as bigint), 0×0200000000 | 0
I had “IF COLUMNS_UPDATED() | 0 > 0″ in my trigger, and on large values of COLUMNS_UPDATED(), bitwise ORing with zero equals zero.  Of course, “IF COLUMNS_UPDATED() | 0 > 0″ is a retarded way to say “IF COLUMNS_UPDATED() > 0″, but whatever codepath that large number triggers wigs out there to.  Try these out:

SELECT 1 WHERE 0×0200000000 > 0
SELECT 1 WHERE CAST(0×0200000000 as bigint) > 0

So, to fix my trigger, I have “IF CAST(COLUMNS_UPDATED() as bigint) > 0″, which still seems a little retarded, but at least my trigger works.

Spent too much damn time debugging the database layer in rails again today. This was the error:

OLE error code:80004005 in Microsoft OLE DB Provider for SQL Server
Cannot create new connection because in manual or distributed transaction mode.

This was getting thrown after an insert, and the problem was very non-obvious.  I found one potential solution on Occasionally Useful Software‘s post about Ruby and SQL Server, but that seemed a little heavy-handed.

Somewhere in the bowels of ActiveRecord, dbi.rb, ADO.rb, and the SQLNCLI driver, something expected one result-set per SQL command. My insert was firing a database trigger, and so the ruby stack saw two result-sets, one saying “1 row affected” for the original insert, one saying “0 rows affected” for the trigger. I’m still a little confused, as I thought those “rows affected” messages were separate from proper result-sets (the results from a SELECT, for example), but apparently those little buggers count enough.

Adding a “SET NOCOUNT ON” to the top of the trigger fixed it.