accessing from T-SQL a database on another DBMS

23 Nov 2007 10:22 AM

wvandenbroeck

tHi

I want to build a trigger to modify data in a table in a database that is
running on another Database Engine in may LAN. I don' t know if that is
possible. If it is, how will I make the connection to that database?

Thanks in advance - Waldo

23 Nov 2007 10:41 AM

Tibor Karaszi

You would have to set up the other instance as a "linked server". But what you want to do requires a
distributed transaction (with DTC running and all that jazz) so I would re-think the approach if
possible.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Show quote

"wvandenbroeck" <wvandenbro***@discussions.microsoft.com> wrote in message
news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@microsoft.com...
> tHi
>
> I want to build a trigger to modify data in a table in a database that is
> running on another Database Engine in may LAN. I don' t know if that is
> possible. If it is, how will I make the connection to that database?
>
> Thanks in advance - Waldo

23 Nov 2007 3:44 PM

TheSQLGuru

I concur. Consider some form of asynchronous mechanism where by you put the
requisite information in a queing table and pull from the other db engine to
do the updates.

You can get DTC to do what you need however, but it is often a PITA. :)

--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.

Show quote

"Tibor Karaszi" <tibor_please.no.email_kara***@hotmail.nomail.com> wrote in
message news:eh02e1bLIHA.4684@TK2MSFTNGP06.phx.gbl...
> You would have to set up the other instance as a "linked server". But what
> you want to do requires a distributed transaction (with DTC running and
> all that jazz) so I would re-think the approach if possible.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "wvandenbroeck" <wvandenbro***@discussions.microsoft.com> wrote in message
> news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@microsoft.com...
>> tHi
>>
>> I want to build a trigger to modify data in a table in a database that is
>> running on another Database Engine in may LAN. I don' t know if that is
>> possible. If it is, how will I make the connection to that database?
>>
>> Thanks in advance - Waldo
>
>