Update SPL's programmatically

Question

Post reply

Update SPL's programmatically

joepacelli

SSCrazy

Points: 2088
More actions
May 21, 2010 at 7:42 am

#224923

So here is the situation. We have 2 similar applications. One is using an Informix database, the other is using a SQL Server 2008 database. We are migrating the clients from the Informix application to the SQL Server application. Since the database structure between these applications is different we created a staging database in SQL Server 2008. The table structures within this database mimic the table definitions from the Informix database.
We use SSIS to move the data from the Informix database to the SQL Server staging database. We then wrote a variety of migration SPL's to migrate the data from the staging database to the destination database.
This is a medical application so we can't just migrate the data and tell the client go for it. We need to migrate the data and allow them to validate the data. So once our migration is complete we copy the staging database and the destination database and reattach them as training databases.
The problem now is if we need to debug anything we can't since the SPL's within our staging database are referencing the original destination name.
For example;
StageDestination
BC
After the migration;
StageDestination
BTrainCTrain
So the SPL's in BTrain still will reference the C database
I can write a SPL to replace the database name from B -> BTrain and C -> CTrain respectively but now how do I programmatically update this SPL on the staging database?
Because the definition that I pull from sys.procedures is a create statement. So I need to drop this SPL and then re-create it with these changes applied.
Any help would be appreciated.
Joe

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

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

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

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

Paul White
SQLPerformance.com
SQLkiwi blog
@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: 150467 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
SQLPerformance.com
SQLkiwi blog
@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: 90303 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