Sql Server 2005 triggers and connecting to another DB using ODBC

Question

Sql Server 2005 triggers and connecting to another DB using ODBC

barry.whitley

Grasshopper

Points: 12
More actions
January 4, 2010 at 7:44 am

#217938

Hi,
Can anyone tell me if you can connect to another Database/Table(s) using an ODBC source, via a trigger?
I want to connect to an Interbase Database (That has an ODBC source)and update/Insert into a table from a trigger on a table in a SqlServer 2005 DB.
Thank You.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Sure you could do it, but I strongly recommend against it. Anytime you are doing something outside of the SQL Server instance then you are asking for trouble. You should be looking at Service Broker or insert changes into a "queue" table that you read using an Agent job or a Windows service. This gives you an asynchronous process to transfer the data. The reason for this is that if you can't connect to the Interbase database for any reason your SQL Server transaction will fail if you put it into a trigger. Check out this article[/url] for help with triggers.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

barry.whitley Grasshopper Points: 12 More actions · Answer 2

Hi Jack

Thanks for the prompt reply.

Can you be more specific as to where I can find creating an ODBC connection via a trigger as there is a lot of information in the url you gave.

Thanks

BazzaW.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

I don't know that you can create an ODBC connection in a trigger. It "may" be possible. I was assuming you could use a linked server or OPENQUERY(), they allow for use of an ODBC connection, but the DSN has to exist.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Sunny-138471 Ten Centuries Points: 1162 More actions · Answer 4

I believe you would have to add a linked server and make remote procedure calls through the trigger. Please see documention for sp_addlinkedserver to add the remote server as a connection. This can be done outside the rigger and only once.