Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dpnadam
Starting Member
29 Posts |
 Posted - 2011-03-10 : 04:37:02
|
| HiI have a database table that needs to update a table on another database when a change has occured. The databases and the schemas are different. Would Service Broker be an appropriate solution for this?Thanks |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-10 : 08:43:01
|
| It depends...yes SB is appropriate for it, but depending on the frequency and what type of updates are being made, there may be better tools -- triggers, replication, sql agent job running custom script... |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-03-10 : 08:43:14
|
| Yes, Service Broker is suited for stuff like this. Are the databases on the same server?- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
dpnadam
Starting Member
29 Posts |
Posted - 2011-03-10 : 09:23:33
|
Thanks for your replies.quote:
Originally posted by russell
It depends...yes SB is appropriate for it, but depending on the frequency and what type of updates are being made, there may be better tools -- triggers, replication, sql agent job running custom script...
The frequency would be a couple of times a week roughly. The process would be, a user inserts or updates records in the first database through a front end application. These records then need to be inserted into a table into another database, or the corresponding record updated. One extra requirement is that the new or updated record from the first database can be inserted into different databases depending on a chosen value for each record made in the application. I think replication is not an option for this as I am using Sql Server 2005 Express. One issue I'm concerned with in a trigger is how to specify which database to send the data to. Either IF statements or dynamic sql (which I really dont want to use)?quote:
Originally posted by Lumbago
Yes, Service Broker is suited for stuff like this. Are the databases on the same server?
Yes they are both on the same server.Thanks. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-03-10 : 09:34:32
|
| In your case then, I would use SB, or maybe a SQL Agent job.I think you're on the right track. |
|
|
|
dpnadam
Starting Member
29 Posts |
Posted - 2011-03-10 : 09:41:35
|
quote:
Originally posted by russell
In your case then, I would use SB, or maybe a SQL Agent job.I think you're on the right track.
Thanks russel. I guess it just leaves SB then as SQL Agent isn't included in SQL 05 Express. Unless I'm missing it...(Oh and just to clarify my earlier post about the frequency. The updates/inserts need to be reflected straight away in the other databases. It isn't a scheduled process or anything. But there would only be updates/inserts a couple times a week.)Thanks again. |
|
|
|
|
|
|
|
|
Is Service Broker appropriate for database Sync?