Update SPL's programmatically

Question

Post reply

Update SPL's programmatically

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

Using a synonym to point to the correct database seems a natural solution.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

joepacelli SSCrazy Points: 2088 More actions · Answer 2

Unfortunately you can not create a synonym for a database, a schema yes, but not the database itself.

Paul White SSC Guru Points: 150468 More actions · Answer 3

joepacelli (5/21/2010)
Unfortunately you can not create a synonym for a database, a schema yes, but not the database itself.

I really thought you could. Sorry about that.

Would it be practical to create synonyms/unbound views to the base tables/views instead?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

joepacelli SSCrazy Points: 2088 More actions · Answer 4

No. Let me explain a little more.

Let's say I'm reviewing the logs and notice we have errors migrating a patients vitals.

Normally I would find our Vitals.Migrate SPL and step through it for the vital in question.

But since we copied the database to BTrain and CTrain I can't since the SPL might contain references to B and C

So let's say the vitals.migrate looks something like this

create procedure vitals.migrate

(

vitalid int

)

begin

do some logic

insert into C.<schema>.<table> values

end

So now on the BTrain database if I try to execute this SPL it will try to insert into the C database.

I've written and SPL that takes 2 parameters, oldDB and newDB.

This will then create a cursor that will read INFORMATION_SCHEMA.ROUTINES

I will do a replace of oldDB with the newDB

I look at the type and change replace the create to an alter

I then try to execute the following

EXEC (@spl)

This is the SPL

But I recieve all these incorrect syntax

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 5

joepacelli (5/21/2010)
So let's say the vitals.migrate looks something like this
create procedure vitals.migrate
(
vitalid int
)
begin
do some logic
insert into C.<schema>.<table> values
end

This seems like a perfect fit for synonyms. Yes, you are going to have to create synonyms for all objects accessed - and use those synonyms in production also.

For example - in the above, instead of coding the database into the query you would use the synonym for the object. In production, you would create the following:

CREATE SYNONYM someSchema.someObject FOR c.someSchema.someObject;

In your code, you then have:

SELECT ...

FROM someSchema.someObject

Now, in the BTrain database - you create the following synonym:

CREATE SYNONYM someSchema.someObject FOR ctrain.someSchema.someObject;

When you use the above code on the BTrain system it will use the ctrain database instead of using the 'c' database because that is how the synonym was created in that database.

This will allow for generic code to be built so it can be moved from system to system and reference the correct databases on that system.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs