September 1, 2009 at 6:58 am
SQL NOVICE HERE 🙂
I have 2 3rd party applications that write to their own sql server 2005 instances on different machines within my companies network. I would like to place simple triggers on a table from each of these sql servers that would then update one of my sql databases located in the same network but on different machines, can that be done?? or what is best practice to do something likke that?
September 1, 2009 at 7:11 am
This can be done using a Linked server.
http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx
you then refer to the linked table using the four part naming convention linked_server_name.catalog.schema.object_name
September 1, 2009 at 7:14 am
thanks so much 🙂 . I think this will work perfectly 🙂
September 1, 2009 at 7:58 am
Be careful with triggers here, especially if they could get multiple rows updated. I say that because many SQL novices don't write triggers well.
Perhaps you could explain what you are trying to do specifically, and we could give you advice. A Linked Server will allow you to send an update to another server, but that might not be the best solution, depdending on what you are trying to accomplish.
September 1, 2009 at 11:48 am
i have 3 databases. db1 is my primary application. db2 and db3 are 3rd party applications that certain users enter in detailed information about products. These 3 applications are currently not connected.When a user adds detailed information into either db2 or db3, i want to write information inot a lookup table on db1 that will contain db2&db3's id fields so the web developement that is going on in house, can easily go to the db2 and db3 information if needed while displaying information for db1 product.
So when the web team looks up a part in db1, they will query a lookup table that will have the product id in db1, if it contains other ids from either db2 or db3, it will then go and get the information from the other db's. The link db is just to have a trigger on db2 and db3 when a new record is added or changed and will write that to the lookup table in db1.
i hope that makes sence... 🙂 thanks for you time to everyone by the way 🙂
September 1, 2009 at 12:04 pm
It would be easier to create a view in db1 that queries the tables in db2 and db3. Or one view for each, if the table structures aren't really compatible.
Triggers across the network can create problems, because if they fail for any reason, they will roll back the whole transaction. Even if they don't fail, they can slow down updates and inserts because of network latency, and that can be a problem all by itself.
Views won't get out of synch, won't block anything, and aren't as time-sensitive.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 1, 2009 at 12:16 pm
Do you know of a good article to read on views?
September 1, 2009 at 12:18 pm
I will still need to set up linked servers to be able to let db1 access db2 and db3 thought correct?
September 2, 2009 at 7:34 am
Yes, you'll still need linked servers. You'll need those no matter what method you use, if you want SQL servers to talk to each other.
Check out "create view" in Books Online. Start from there. They're really just saved select statements.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply