October 2, 2003 at 9:43 am
I have 2 databases, test1 and test2. I have a trigger in test1 database which inserts records into test2 database. But for doing this I have to hard code "insert into test2.dbo.sometable values" etc into the trigger in test1 database. This is fine as long as no refresh or restores are done.. But people usually have multiple databases( like prod, test etc) and if they restore from one to other, you have to manually go and change all the references, this becomes a tedious process for them, is there a better way to do this ? I will really appreciate any feedback on this.
Thanks
Sonali
October 2, 2003 at 9:52 am
Rename original database name and restore the backup to the original name. In this way, you don't have to change the code to refer to the new restored database.
October 2, 2003 at 9:55 am
That won't work, customers can have multiple databases on same instance, you cannot have 2 databases with the same name , right ?
If one is PROD and the other is DEV.. PROD has hardcoded triggers poining to PROD1 and DEV has hard coded triggers pointing to DEV1, so If restore PROD over DEV, those triggers which are hard coded, now for DEV wiill point to PROD1 and not to DEV1,
thanks
Sonali
October 2, 2003 at 10:00 am
quote:
is there a better way to do this ?
Replication?
--Jonathan
--Jonathan
October 2, 2003 at 10:04 am
I guess you want to keep both database in synchronization. When you restore PROD to DEV, You should restore both PRDO and PROD1 to development system, Right?
October 2, 2003 at 10:48 am
Yes, restoring prod to prod1 and dev to dev1 is not a issue, the problem is prod has hard coded triggers that point to prod1 but after restore from prod to dev the triggers instead of pointing to dev1 still points to prod1. I then have to manually go and change all the reference, but for customers, this is too much sometimes, as this needs to be changed in lot of places....
October 2, 2003 at 10:51 am
Its like this, say this trigger is in PROD database
create trigger my_tr
...
..
insert into prod1.dbo.work
values (..)
go
etc
end
go
SO when I restore this database over dev this trigger will still have reference to prod1 instead of dev1
Thanks
Sonali
October 2, 2003 at 12:21 pm
Once database restored, script all triggers, replace all db name at once, and recreate them. It shouldn't be too much work.
October 3, 2003 at 2:58 am
Not much help here, right?
Unfortunately cross-database connections are all hard-coded in SQL Server because it can't do double evaluation. That would be to use a variable for object name. In trigger you might get away with Dynamic SQL (build and execute a string), but that would cost you a lot of performance.
Another way to connect to a different server.database is using a view. That has to use hard coded names too, but there's a small trick I use: all connections to different servers and databases go through views that have the same prefix, for example xvw (external view). When you list them in EM, they are somewhere at the bottom. It is much easier to check all xvws if they are connected to the right database than checking all triggers. If your database connects to several others, you could also put something at the beginning of a name to distiguish the database they point to. That also looks useful sorted alphabetically.
Hope that will make your life a bit easier.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply