SQL XML Assembly Blows Up and How to Fix It

Our rather complicated SQL XML assembly blew up Friday after one of our developers did a backup/restore of our development database to make a testing database. The problem occured only when the original database was restored to make a new database

The lead developer and I tried to sort it out but didn't get anywhere. This morning I asked the data warehouse developer to look at this morning. He thought the problem was with permissions.

Here is what happened:

There are three stored procs associated closely with the assembly: usp_InsertExceptionLog, usp_getXMLReference, and usp_GenerateGroupReferenceXML.

Two of the procs, usp_InsertExcetionLog and usp_GetXMLReference contained hard coded references to the original database. Code example of usp_GetXMLReference:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [BANDS].[dbo].[usp_GetXMLReference]
(
@Group_ID INT,
@Name varchar(100),
@Desc varchar(500),
@Order int,
@Effective DATETIME,
@Active bit,
@XMLOut xml output
) AS

BEGIN TRY

INSERT INTO [dbo].[Reference]
([Group_ID]
,[Name]
,[Description]
,[Sort]
,[Effective]
,[Active])
VALUES
(@Group_ID, @Name, @Desc, @Order,
GETDATE(), @Active)

SET @XMLOut =
(
SELECT [Group_ID]
,[Name]
,[Description]
,[Sort]
,[Effective]
,[Active]
FROM [PALS].[dbo].[Reference] AS Reference
WHERE Group_ID = @Group_ID
FOR XML AUTO
)

END TRY
BEGIN CATCH
EXEC usp_InsertExceptionLog
END CATCH


So, the new Bands_Test database was trying to use the Bands assembly. That will not work.

All we had to do is go back and take all the hard coded database names out of the references.