| Author |
Topic |
|
M.E.
Aged Yak Warrior
539 Posts |
 Posted - 2002-07-11 : 10:12:20
|
| I'm writing a insert that will call other databases for inserts.Some psuedo code for you...Create trigger insert_trigger_134409for insert---other misc stuffinsert into corporate_test.dbo.table_8394values(select * from inserted)--5 or so other insert statements all inserting to DB Corporate_testThats the basic coding and there will be about 12 of these triggers (or something simular). As a business rule here, a piece of coding must be created in the Corp_Developement DB. Once approved it's moved to the corp_test where the actual data in the tables is tested. Once approved it goes to corp_SO where it sits until the final DB version comes out.. which will be named then. Ain't this just a pain in the ass? Instead of going through each trigger and replacing the database name in all the insert statements everytime they upgrade it, I would like to make the DB it calls a variable so only a single variable will need to be changed.Something like this : @Database..TablenameDynamic SQL will work for this..set @sql = 'Insert into '+@DB+'..tablename values(select * from inserted)'exec @sqlHowever they don't really like Dynamic SQL here either and would prefer to avoid it. Told them I'd research it.. But to the best of my knowledge this is the only way to do this... Any other methods that might work for this?-----------------------Take my advice, I dare ya |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-11 : 10:18:36
|
| Oh dear ... is it really an absolute requirement that object names be changed as they move through the implementation cycle? That's a recipe for disaster.Jonathan Boott, MCDBA |
 |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-07-11 : 10:24:28
|
| Heh, Thats the first thing I said to them. Unfortunately due to the number of people working on this project and the various things needed to test, we really have no choice. I'm just junior programmer on this project, so unfortunately I don't get much input on stuff like that anyway-----------------------Take my advice, I dare ya |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-11 : 10:28:51
|
AFAIK dynamic SQL is the only way to use a variable database name.But...A. You have to go through all of the triggers to change the @DB variable's value anyway (!) so how hard is it to just do it the right way and change the database names?  And if you forget to change the variable in one trigger...B. 12 triggers is a lot? Unless each trigger has something like 5,000 lines of code, it shouldn't take you more than 20 minutes to change them. 20 minutes to deploy a test database to production is pretty damn fast!And if you do have triggers with 5,000 lines of code....oh boy, don't get me started.Don't want to sound preachy, but many times there's a good reason why things are not made easier for you/me/us. It keeps you on your toes!  And if you're a junior programmer and the senior people are suggesting the dynamic SQL route, you need to smack them. "Recipe for disaster" really doesn't fully describe it! Any programming team that takes these kinds of shortcuts is gonna do a lot of rework. |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-07-11 : 10:40:08
|
quote:
"Recipe for disaster" really doesn't fully describe it! Any programming team that takes these kinds of shortcuts is gonna do a lot of rework.
Just trying to spare M.E. the coming pain ... but yes, disaster might not be a strong enough word ...Jonathan Boott, MCDBA |
|
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2002-07-11 : 11:13:06
|
| The problem here is that once you start changing database/object names to roll into production you rolling untested code into production.This is a great use of multiple instances of SQL Server. Create one instance for DEV with the names you need. Create a second instance for TEST with the identical names. Then you can run the exact same code in DEV, TEST and PROD. And you only need one physical server. The database server name should be configured outside the application anyway -- registry, XML file, post-it note , etc.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-07-11 : 12:12:24
|
quote:
A. You have to go through all of the triggers to change the @DB variable's value anyway (!) so how hard is it to just do it the right way and change the database names? And if you forget to change the variable in one trigger...
the DB name will be set from a tableset @dbname = dbname from dbnametableor something like that. That way I only got to change the value once. Bleh... I got the strange feeling I might be going through each trigger and changing the DB name manuallyquote:
B. 12 triggers is a lot? Unless each trigger has something like 5,000 lines of code, it shouldn't take you more than 20 minutes to change them. 20 minutes to deploy a test database to production is pretty damn fast! And if you do have triggers with 5,000 lines of code....oh boy, don't get me started.
Actually, there is probably over 300 lines per. But all it is are a bunch of column listings.insert into blah(col1,col2,...col94) values(select ........ from bleg)insert into...One I think goes on for about 700 lines like that. quote:
The database server name should be configured outside the application anyway -- registry, XML file, post-it note , etc.
We tend to use interoffice post it notes for info like that quote:
This is a great use of multiple instances of SQL Server. Create one instance for DEV with the names you need. Create a second instance for TEST with the identical names. Then you can run the exact same code in DEV, TEST and PROD. And you only need one physical server.
Actually, I really like that idea. Thnx Graz-----------------------Take my advice, I dare ya |
|
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2002-07-11 : 16:22:49
|
quote:
I got the strange feeling I might be going through each trigger and changing the DB name manually
ctrl+H in Query analyzer. That's all. No Biggy. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-07-11 : 16:27:08
|
quote:
quote:
The database server name should be configured outside the application anyway -- registry, XML file, post-it note , etc.
We tend to use interoffice post it notes for info like that
I find that tatooing it onto the forehead of junior programmers is a convenient method.  |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-07-11 : 17:36:52
|
quote:
I find that tatooing it onto the forehead of junior programmers is a convenient method.
They prefer tatooing it their to foot and stamping it on my ass actually-----------------------Take my advice, I dare yaEdited by - M.E. on 07/11/2002 17:37:12 |
|
|
|
|
Database name as a var.. dyn sql the only way?