call external stored procedure

Question

Post reply

call external stored procedure

chinewbie

SSC-Addicted

Points: 414
More actions
July 2, 2008 at 7:53 pm

#381501

Hi:
We have two separate sql db. We want to update username info real time one db when it is updated in another database. So for example if username is updated in DB1, update DB2 also with changed username. is better to use a .net assemblely in sql server? The filed that need to be update is basically username. One field only
So when table is updated in DB1, update trigger calls the assembley, through external command and than in assembley , a stored procedure is called which updated the data into other database.
I somewhere read about linked database. What is that?
I am not sure how to go about this rela time. I will appreciate any input or suggestions

Viewing 9 posts - 1 through 8 (of 8 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

There are several ways to do this, SQL CLR routines are probably one of the less advantageous ways.

Other ways:

1) SQL Replication

2) Triggers and Linked-Servers

3) Service Broker & EndPoints

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

chinewbie SSC-Addicted Points: 414 More actions · Answer 2

Thanks for reply! can you please elaborate on these. I am anewbie and not exact fimilar with. I will appreciate guide me here. Another thing is only one field need to be modified. Not whole lot of data. So ma not sure about replication.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

This is not something that you should do with an assembly. A linked server can be created following the instructions in the BOL:

http://msdn.microsoft.com/en-us/library/ms190479.aspx

Then a DML Trigger on the server being updated can be created to propogate the update to the table on the linked server.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Jonathan Kehayias (7/3/2008)
This is not something that you should do with an assembly. A linked server can be created following the instructions in the BOL:
http://msdn.microsoft.com/en-us/library/ms190479.aspx
Then a DML Trigger on the server being updated can be created to propogate the update to the table on the linked server.

I agree with Jonathan, this is probably the way you wan to go. The only modification that I would offer to this is that if the linked server connection is slow (they can be, quite often), this may slow the trigger down also.

since DML triggers are synchronous, this could cause your user to hang when accessing this table. If this is a problem, you can address it by having the trigger SEND to a Service Broker queue instead and then have the queue's service routine send to the linked server.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

I almost made a comment about offloading to Service Broker for exactly what RBarryYoung points out. You could hit latency issues on the linked server, but you could also hit some issues with MSDTC and distributed transactions. Service Broker isn't my forte though, so I couldn't offer an example of how you would do it. CLR is definately not an appropriate solution for this though.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

I am doing this right now, but unfortunately, one of the things that I have learned about Service Broker is that it is hard to do anything "simply". There are a lot of catches and "gotchas", so it's really had to post a simple example that's also a good example.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

chinewbie SSC-Addicted Points: 414 More actions · Answer 7

Thanks for all your help and suggestio. At this point it seems it might be easy to update in two place separately. I will look further into this. let you know how it goes. Thanks again

krayknot SSCertifiable Points: 7998 More actions · Answer 8

Executing a stored procedure on another database / server is very easy: follow the following steps:

1. if you have to execute stored procedure in the same server but in different database, then prefix the name of the database as:

[dbname].sp_name

2. If the servers are different then create a linked server definition using sp_addlinkedserver. To view information about the linked servers defined in a given instance of SQL Server, use sp_linkedservers. after this prefix the link server name followed by the dbname.

kshitij kumar
kshitij@krayknot.com
www.krayknot.com