SQL Opinion needed.

[Peager]

I have an environment where our live database, (MC_Live), gets backed up and restored to a development environment, (MC_Training), periodically. In this database, there are some stored procedures that update a second database via a linked server connection. When working in MC_Live, we want to update a 'Live' linked database, (MobileFrame). When connected to MC_Training, we need to hit the 'development' linked database (MobileDev) on a second server.

Ok, that said, I have a Merge statement something like this:

Merge [MobileData].[dbo].[SomeTable]
Using (SELECT col1, col2 
       FROM  [MC_Live].[dbo].[AnotherTable]
       WHERE ...)
   :
   :

Now for the $64k question. The 'LiveData' above is on a different server from the 'DevData' that needs to be updated. Which would be a better approach to change database/table references based on the database where the procedure resides:

1) Use a full IF db_name() = 'LiveData' Begin ... End IF db_Name() = 'MC_Training BEGIN .... END

2) Use dynamic SQL to change the [Server].[Database].[Schema].

references based on the Database we're attached to.

Opinions anyone?

Much thanks in advance,

Paul

 

[gmmastros]

I would suggest that you do a little research on SYNONYMS. If you create a synonym for the linked server/database in all of your code, then the synonym would be the only thing that needs to change between your production and development environment.

This may not be the right solution for you, but I suspect it is exactly what you're looking for.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Peager]

Thanks George, I'll look into it.

 

[Peager]

George, is this what you were suggesting?

	IF DB_NAME() = 'MC_Live'
		CREATE SYNONYM MFServer FOR [MobileFrame].[dbo].[PPTKTHD] 
	IF DB_NAME() = 'MC_Training'
		CREATE SYNONYM MFServer FOR [MCSQL2008R2].[MobileDev].[dbo].[PPTKTHD]
    MERGE MFServer AS T -- Target....
	-- This is the record set source to use to update MobileFrame
        USING 
            ( SELECT    LOC
                       ,JOB_NO

If so, I am getting some errors that are new to me....

Msg 325, Level 15, State 1, Procedure MOB_PPTktHd_Merge, Line 8
Incorrect syntax near 'MERGE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.
Msg 156, Level 15, State 1, Procedure MOB_PPTktHd_Merge, Line 30
Incorrect syntax near the keyword 'AS'.

Do synonyms work with the MERGE statement?

 

[gmmastros]

Uh.... yeah. I haven't used the merge statement yet, so I cannot guarantee that is works with synonyms, but I would be extremely surprised if it didn't.

Can you tell me what the compatibility level is of those 2 databases?

If you're not sure how to get that information, then run this on both servers...

Select name, compatibility_level From master.sys.databases

I suspect that the MERGE statement only works with a certain compatibility level. My guess is... you need to use SQL2008 or higher for the MERGE statement to work, and you probably have a SQL2005 instance, or the compatibility level is set to 90.

Please let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Peager]

OK, I got it working... now we just have a small Kerbos problem which my network guru says he can handle. I did run into a bit of quirkieness that everyone should know about. When using the aliases for Target (T) and Source (S) I found that the order of the fields in the WHEN MATCHED clause was critical. The Source fields had to be placed before the Target fields or the ALTER statement would not pass muster through the parser and errors would be declared for 'unknown fields'. I swapped the comparisons as shown below and all appears to work well.

ALTER PROCEDURE [dbo].[MOB_POLines_Merge]
AS 

	SET NOCOUNT ON ;

	IF DB_NAME() = 'MC_Live'
		CREATE SYNONYM MFServer FOR [MobileFrame].[dbo].[PO_LINE] ;
	IF DB_NAME() = 'MC_Training'
		CREATE SYNONYM MFServer FOR [MCSQL2008R2].[MobileDev].[dbo].[PO_LINE];
	--ALTER DATABASE MC_Live
	--	SET COMPATIBILITY_LEVEL = 100; 
	
	MERGE MFServer AS T -- Target....
	-- This is the record set source to use to update MobileFrame
    USING (SELECT   PL.DIV
		   ,PL.DPT
               :                :
	   FROM  .... ) AS S 
    ON (T.External_Key = Cast(S.Div AS VarChar(2))+':'+
	                      S.Dpt+':'+S.Po_No+':'+
	                 Cast(S.Line AS VarChar(6))) 
	-- When matched and something changed....
    WHEN MATCHED AND (  [S].Loc		<> [T].[LOC]
                     OR [S].Item_No	<> [T].[ITEM_NO]
                     OR [S].DESCRIPTION <> [T].[DESCRIPTION]
                     OR [S].Ord		<> [T].[ORD]
                     OR [S].Rec		<> [T].[REC]
                     OR [S].To_Receive	<> [T].[TO_RECIEVE]	
	      	)
        THEN UPDATE 
	   SET [LOC]		= S.Loc
                    :
                    :
    WHEN NOT MATCHED --We've got a new record to insert
        THEN INSERT ...
             VALUES ...
    WHEN NOT MATCHED BY SOURCE ...
--OUTPUT
--     $action
;
	DROP SYNONYM MFServer