Linking 64-bit SQL2k5 to 32-bit SQL2k ... or, How to Avoid Wasting 10 Days Waiting For Straight Answers
They say a picture is worth a thousand words, so here's a thousand words. The situation is that we have a 32-bit SQL2k cluster and a linked 32-bit SQL2k machine (they link in both directions) and we need to execute distributed transactions across both machines and in both directions. This works fine, and has been working for many months (after we spent a month trying to get our hosting provider to correctly install MSDTC ... another story entirely).
It was recommended by a consultant (I know, I know, readers of WorseThanFailure aka TheDailyWTF are snickering up their sleeves at me) that we replace the cluster with 64-bit windows and 64-bit SQL2k5. He had the picture of our network and assured us that it wouldn't cause any problems. Predictably, it caused a host of problems immediately. It's been 2 weeks and we're still not done with the project.
The problem is that we were getting a pretty mystifying error message:
The stored procedure required to complete this operation could not
be found on the server. Please contact your system administrator.
Msg
7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset
"DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server
"<LinkedServerName>". The provider supports the interface, but returns a
failure code when it is used.
I found the MS knowledgebase article which was, as per usual, not really as helpful as it could have been. I spent 10 days talking to the database guys at our web host (and these guys are typically pretty sharp) and it finally came down to "you are on your own," which is what I figured. We all looked at the KB article and couldn't really figure out what, exactly, we were supposed to do. As a former programmer, I figured I'd dive into some code and lo-and-behold I had a breakthrough in like 45 seconds.
When SQL2k5 64-bit is trying to select data as part of a distributed transaction, it tries to run a stored proc called "sp_tables_info_rowset_64". That jumped out at me immediately. The 32-bit database has a proc called "sp_tables_info_rowset" that does the same thing. So I added the following stored proc to my 32-bit SQL2k instance:
create procedure
sp_tables_info_rowset_64 @table_name sysname, @table_schema sysname = null, @table_type nvarchar(255) = null
as
declare @Result int
select @Result
= 0
exec @Result = sp_tables_info_rowset
@table_name,
@table_schema,
@table_type
So ... kancho to the knowledgebase article that was going to have me run a 935KB sql script that would make massive changes to the master database. From the command line. On a Saturday morning. If I would have done it their way, I'd be here all day. Doing it my way, I'm done in time to dash off a blog entry and then have breakfast with a beautiful girl.
Note to Microsoft: This is why people hate upgrading. You make our lives suck when we try it. Remember that we don't want to spend our weekends fixing stuff. It's not about the money. It's about finding out why they call it a Brazilian wax.