COANetwork
asked on
Compile-time conditions in stored proc?
I have some stored procs that reference a few linked servers in a distributed transaction. This works OK in live, but in my DR location I have fewer actual machines, and they contain databases that reside on separate machines in live.
Under the circumstances, i would have to use loop-back linked server names in DR, which would kill the distributed trans, or I would have to have two versions of the code - for DR and Live, that reference actual machine names - which pretty much defeats the purpose of disaster recovery.
I have tried solving the issue by adding conditions like
Is there a way of adding conditional statements that would be evaluated at "prepare" stage?
Or perhaps someone could suggest a different way of achieving what I am after?
Under the circumstances, i would have to use loop-back linked server names in DR, which would kill the distributed trans, or I would have to have two versions of the code - for DR and Live, that reference actual machine names - which pretty much defeats the purpose of disaster recovery.
I have tried solving the issue by adding conditions like
IF @@SERVERNAME = 'SQLPROD1' EXEC SQLPROD2.MyDB.dbo.mySP
ELSE EXEC SQLDR2.MyDB.dbo.mySPIs there a way of adding conditional statements that would be evaluated at "prepare" stage?
Or perhaps someone could suggest a different way of achieving what I am after?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad I had a good idea. Usually you school me. ;-)
ASKER
Both viable ideas, guys. thanks... I have to say that dropping and recreating linked servers sounds a bit clumsy to me, though, and dynamic SQL introduces its own complications because my stored procs have output parameters. Besides, isn't there a performance hit with dynamic SQL because it has to re-generate the execution plan? Or is that a moot point, because SPs (and their execution plans) are atomic, anyway, even when sub-called?
Actually, there is yet another complication to this - I also have a QA environment where server names are yet again different, but there I also have the added complication of database names being different (MyDB_QA1, MyDB_QA2, MyDB_Dev, etc) I do not think that problem can be resolved by architectural changes, as originally pointed out - I have to have these multiple copies of the databases. I guess I will have to go the dynamic SQL route... Thanks again.
Actually, there is yet another complication to this - I also have a QA environment where server names are yet again different, but there I also have the added complication of database names being different (MyDB_QA1, MyDB_QA2, MyDB_Dev, etc) I do not think that problem can be resolved by architectural changes, as originally pointed out - I have to have these multiple copies of the databases. I guess I will have to go the dynamic SQL route... Thanks again.
Definitely a more elegant solution. You just me realize I have a bias onto not mixing up dynamic SQL and multi server distributed transactions for security purposes and error feed backing reasons. Thanks (I do mean it) :)