accessing from T-SQL a database on another DBMS

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 - WaldoYou 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" <wvandenbroeck@.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|||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.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.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" <wvandenbroeck@.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
>

accessing from T-SQL a database on another DBMS

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
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.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.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" <wvandenbroeck@.discussions.microsoft.com> wrote in message
> news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
>

accessing from T-SQL a database on another DBMS

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 - WaldoYou would have to set up the other instance as a "linked server". But what y
ou want to do requires a
distributed transaction (with DTC running and all that jazz) so I would re-t
hink the approach if
possible.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"wvandenbroeck" <wvandenbroeck@.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|||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.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.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" <wvandenbroeck@.discussions.microsoft.com> wrote in message
> news:2A416FD1-02F9-427D-9748-7F2CE4E08CDD@.microsoft.com...
>

accessing fake data inside an INSTEAD OF trigger

Hello,
Is it possible to access inserted data through an UPDATE INSTEAD OF
trigger if the condition of the query does not match any row in a
table?
For example :
UPDATE myTable SET myColumn1 = 'abc' WHERE myColumn2 = 'xyz'
If there is no row inside myTable matching the condition "WHERE
myColumn2 = 'xyz'", the INSTEAD OF trigger is activated without any
error but the inserted table remains empty.
Is there a way to access the value 'abc' in such a situation?
thank you
sNo, you do not have access to the originating command, nor any
parameters of that command. What you have access to are the DELETED
and INSERTED tables, and the table that was updated.
Roy Harvey
Beacon Falls, CT
On 2 Mar 2006 15:34:44 -0800, "steven" <stevenshawk@.gmail.com> wrote:

>Hello,
>Is it possible to access inserted data through an UPDATE INSTEAD OF
>trigger if the condition of the query does not match any row in a
>table?
>For example :
>UPDATE myTable SET myColumn1 = 'abc' WHERE myColumn2 = 'xyz'
>If there is no row inside myTable matching the condition "WHERE
>myColumn2 = 'xyz'", the INSTEAD OF trigger is activated without any
>error but the inserted table remains empty.
>Is there a way to access the value 'abc' in such a situation?
>thank you
>s|||OK ... but this data should exist somewhere.
I mean if the server returns an empty cursor when I do a SELECT * FROM
INSERTED within the trigger, it is because it has access to myColumn2 =
'xyz'. Any API to access the query content, even if it does not match
anything in the table?
thank you
Roy Harvey wrote:
> No, you do not have access to the originating command, nor any
> parameters of that command. What you have access to are the DELETED
> and INSERTED tables, and the table that was updated.
> Roy Harvey
> Beacon Falls, CT
>
> On 2 Mar 2006 15:34:44 -0800, "steven" <stevenshawk@.gmail.com> wrote:
>|||On 3 Mar 2006 07:30:03 -0800, "steven" <stevenshawk@.gmail.com> wrote:

>OK ... but this data should exist somewhere.
>I mean if the server returns an empty cursor when I do a SELECT * FROM
>INSERTED within the trigger, it is because it has access to myColumn2 =
>'xyz'. Any API to access the query content, even if it does not match
>anything in the table?
>thank you
Nothing like that I've ever heard of. Or imagined, for that matter.
Roy Harvey
Beacon Falls, CT

accessing external webservice or webpage from SQL 2005

Is it possible to access a webservice from within SQL 2005?

What I am looking to do is place a trigger on a specific table, and detect if a specific column is being updated. If it is, I want to launch a robust process that does x, y and z. Something like this:

1) record is updated indicating that an account is closed.

2) SQL launched a webservice/ aspx page that builds an HTML email template and then mail it.

thanks!

SQL Server 2005 does have XML web service support. Seehttp://msdn2.microsoft.com/en-us/library/ms191274.aspx.|||

Wow, thanks for the speedy reply. The documentation behind the link you sent seems to refer to SQL server fielding SOAP requests and "listening" to and delivering data, where the SQL server is processing the request.

But is the opposite possible? SQL calling another webservice?

Access Subforms not updatable

Hello,
I have an Access client that has a subform based on a VIEW. I wrote the view
with an instead of insert trigger. I also included the WITH VIEW_METADATA
option. The problem is this. If I have the VIEW_METADATA option in my VIEW,
the forms recordset is not updatable, but if it ALTER the VIEW, excluding the
option, then the recordset is updatable, but the trigger won't work. I need
the trigger to work. Is there anything that I can do.
Thank you,
Brandon
What is the trigger doing? Perhaps you can handle the whole process a
different way. Triggers often introduce unwanted complexity and have
unintended consequences.
--Mary
On Sat, 11 Sep 2004 19:39:02 -0700, "Brandon Campbell"
<BrandonCampbell@.discussions.microsoft.com> wrote:

>Hello,
>I have an Access client that has a subform based on a VIEW. I wrote the view
>with an instead of insert trigger. I also included the WITH VIEW_METADATA
>option. The problem is this. If I have the VIEW_METADATA option in my VIEW,
>the forms recordset is not updatable, but if it ALTER the VIEW, excluding the
>option, then the recordset is updatable, but the trigger won't work. I need
>the trigger to work. Is there anything that I can do.
>Thank you,
>Brandon