So we've been being pestered by our DBAs (you know the ones) to move our BizTalk Server 2006's Data Tier from our aging SQL Server 2000 Cluster to our new shiny SQL Server 2005 Cluster. The end of our fiscal year was approaching so we brought in a consultant to help us. I highly recommend Catapult Systems if you need any BizTalk help. They're smart guys and have reasonable rates (in comparison to Oracle, who doesn't?). We met with our consultant Mark and gave him the tour of our environment and he came back a week later with a solid procedure... BAM, MsgBox, WSS, Jobs, DBs... all of it. We executed the procedure along with enough red lines to maintain their human status. All seemed to go well - hosts started, jobs started, Group Hub back up, but wait! Couple of jobs are failing? Hmmm. Update some physical paths in the backup job and all is well. Wait. Tracked Message Copy keeps failing:
Executed as user: HARRIS\MLBSQLVLM1. Could not find server 'mlbxxx\sql3' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. [SQLSTATE 42000] (Error 7202). The step failed.
Now this is very confusing. We're all on one server. No need for any linked servers. Why does it think mlbxxx\sql3 is a linked server it can't connect with. Is it a permission issue? I checked and double checked the database configuration from the old server and nothing jumped out at me. OK. Time to step into the deep dark reaches of the BizTalk stored procedures... the starting point: bts_CopyTrackedMessagesToDTA at line 70:
exec ('INSERT INTO [' + @dtaDbServer + '].[' + @dtaDbName + '].[dbo].[Tracking_Parts1](uidMessageID, nvcPartName, uidPartID, uidOldPartID, nNumFragments, imgPart, imgPropBag)
SELECT TOP ' + @nTotalNumParts + ' mp.uidMessageID, mp.nvcPartName, mp.uidNewPartID, mp.uidOldPartID, p.nNumFragments, p.imgPart, p.imgPropBag
FROM (SELECT TOP 50 uidMessageID, nID FROM TrackingMessageReferences WITH (ROWLOCK INDEX(IX_TrackingMessageReferences)) ORDER BY nID ASC) as t
INNER LOOP JOIN #TrkMsgNewPartIDs mp WITH (ROWLOCK) ON t.uidMessageID = mp.uidMessageID
INNER LOOP JOIN Parts p WITH (ROWLOCK INDEX(IX_Parts)) ON mp.uidOldPartID = p.uidPartID
OPTION (KEEPFIXED PLAN)')
The thing that jumped out at me was the use of the full 4 part reference to the TrackingParts1 table. This should be extra clear. There should be no confusion. Let me look in sys.server... it's wrong... it has MLBYYY\sql3. How could this be... how can I ask the server it's name:
select
SERVERPROPERTY('servername'); returns mlbxxx\sql3. correct. strange.
Is there another way? Yes.
select
@@servername; return mlbyyy\sql3 - wrong. It thinks it has two names?
The detail here is how each command gets the name. The SERVERPROPERTY comes from the Windows OS. @@servername is the name set during installation. I am guessing the SQL Server was built from an image and the @@servername was never adjusted when the machine was renamed. So why didn't my query work? It appears that when you specify the full four part name of a table, it goes to sys.servers to find out about the specified server. If you don't specify a server (less than full 4 part naming), it bypasses sys.servers and assumes local or gets the name from the SERVERPROPERTY.
The fix:
sp_dropserver <mlbyyywhatever was returned by @@servername>;
go;
sp_addserver 'mlbxxx\sql3', local;
go
then restart the server or instance. See the KB article for the gory details. Once this was corrected, everything worked like a charm.