Creating a Trigger which updates a linked server

Hi all,
I read some message in FAQ about my problem but i doesn't work any
more...
I have 2 SQL-Servers that use sql-server and windows security
integrity.
On first Server, i add a linkServer to the other by using
sp_addlinkedserver and use a specified user ...
So i can use select command, execute store procedure, update , insert
and any command to read,update or write data .
I said Coool :-) I could put arrival data from one database to the
other database by using a trigger !!!
Oouchh !!! PROBLEM , PROBLEM , PROBLEM ...
When the trigger is firing, and only when i want to execute the insert
command, anything happen ... Also the @.@.error don't work...
Let me show what happen in the trigger :
CREATE TRIGGER [ADD_VALUE] ON [dbo].[table]
AFTER INSERT
AS
DECLARE ...
SET XACT_ABORT ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET implicit_transactions off
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT ...
FROM OPENXML (@.idoc, '/EXECID',2)
WITH ( ... )
EXEC sp_xml_removedocument @.idoc
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST
TOTO')
SET @.v_StrMess = 'The problem is HERE ...'
--raiserror (@.v_StrMess , 16 , 1)
COMMIT TRAN
if @.@.error >0
BEGIN
raiserror (@.v_StrMess , 16 , 1)
END
SET implicit_transactions on
END
I know that it's possible to update a linkedserver whith a trigger but
i don't know how !!!
Please, could you explain in good word how to do it or is there
another solution ?
Thx for answers ...
ByeWhat is the error you are getting?
AMB
"Mick" wrote:

> Hi all,
> I read some message in FAQ about my problem but i doesn't work any
> more...
> I have 2 SQL-Servers that use sql-server and windows security
> integrity.
> On first Server, i add a linkServer to the other by using
> sp_addlinkedserver and use a specified user ...
> So i can use select command, execute store procedure, update , insert
> and any command to read,update or write data .
> I said Coool :-) I could put arrival data from one database to the
> other database by using a trigger !!!
> Oouchh !!! PROBLEM , PROBLEM , PROBLEM ...
> When the trigger is firing, and only when i want to execute the insert
> command, anything happen ... Also the @.@.error don't work...
> Let me show what happen in the trigger :
> CREATE TRIGGER [ADD_VALUE] ON [dbo].[table]
> AFTER INSERT
> AS
> DECLARE ...
> SET XACT_ABORT ON
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> SET implicit_transactions off
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT statement using OPENXML rowset provider.
> SELECT ...
> FROM OPENXML (@.idoc, '/EXECID',2)
> WITH ( ... )
> EXEC sp_xml_removedocument @.idoc
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST
> TOTO')
> SET @.v_StrMess = 'The problem is HERE ...'
> --raiserror (@.v_StrMess , 16 , 1)
> COMMIT TRAN
> if @.@.error >0
> BEGIN
> raiserror (@.v_StrMess , 16 , 1)
> END
> SET implicit_transactions on
>
> END
>
>
> I know that it's possible to update a linkedserver whith a trigger but
> i don't know how !!!
> Please, could you explain in good word how to do it or is there
> another solution ?
> Thx for answers ...
> Bye
>|||@.@.Error is only captured for the very LAST executed statement. In your case,
you would always be 0.
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
SET @.v_StrMess = 'The problem is HERE ...' -- <++++You reset the
@.@.error value here
You should change it to this to capture the @.@.error and set your @.msg
declare @.err int
BEGIN DISTRIBUTED TRANSACTION
INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
SELECT @.err=@.@.error, @.v_StrMess = 'The problem is HERE ...'
IF @.err=0
COMMIT
ELSE
BEGIN
ROLLBACK
raiserror (@.v_StrMess , 16 , 1)
END
-oj
"Mick" <mickymickmc@.yahoo.fr> wrote in message
news:79cab08b.0502151254.3b8d3d9a@.posting.google.com...
> Hi all,
> I read some message in FAQ about my problem but i doesn't work any
> more...
> I have 2 SQL-Servers that use sql-server and windows security
> integrity.
> On first Server, i add a linkServer to the other by using
> sp_addlinkedserver and use a specified user ...
> So i can use select command, execute store procedure, update , insert
> and any command to read,update or write data .
> I said Coool :-) I could put arrival data from one database to the
> other database by using a trigger !!!
> Oouchh !!! PROBLEM , PROBLEM , PROBLEM ...
> When the trigger is firing, and only when i want to execute the insert
> command, anything happen ... Also the @.@.error don't work...
> Let me show what happen in the trigger :
> CREATE TRIGGER [ADD_VALUE] ON [dbo].[table]
> AFTER INSERT
> AS
> DECLARE ...
> SET XACT_ABORT ON
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> SET implicit_transactions off
> -- Create an internal representation of the XML document.
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> -- Execute a SELECT statement using OPENXML rowset provider.
> SELECT ...
> FROM OPENXML (@.idoc, '/EXECID',2)
> WITH ( ... )
> EXEC sp_xml_removedocument @.idoc
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST
> TOTO')
> SET @.v_StrMess = 'The problem is HERE ...'
> --raiserror (@.v_StrMess , 16 , 1)
> COMMIT TRAN
> if @.@.error >0
> BEGIN
> raiserror (@.v_StrMess , 16 , 1)
> END
> SET implicit_transactions on
>
> END
>
>
> I know that it's possible to update a linkedserver whith a trigger but
> i don't know how !!!
> Please, could you explain in good word how to do it or is there
> another solution ?
> Thx for answers ...
> Bye|||Thx for your answer ...
But the problem is not the @.@.error message or else !!!
The problem is that when the insert instruction start, nothing happen
...
I run a trace to understand what sql-server that run trigger do but
when the BEGIN DISTRIBUTED TRANSACTION start , there is the end of
transaction and nothing else.
For example, when i make an insert into srvdb1 by using sql-query
analyser, the trigger start and i wait until the connection is
broken...
i will test something and go back later ...
"oj" <nospam_ojngo@.home.com> wrote in message news:<eruW3o7EFHA.2176@.TK2MSFTNGP15.phx.gbl>.
.
> @.@.Error is only captured for the very LAST executed statement. In your cas
e,
> you would always be 0.
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
> SET @.v_StrMess = 'The problem is HERE ...' -- <++++You reset the
> @.@.error value here
> You should change it to this to capture the @.@.error and set your @.msg
> declare @.err int
> BEGIN DISTRIBUTED TRANSACTION
> INSERT INTO LNK_SRVDB.MY_BASE.DBO.TABLE_TEST VALUES('TOTO','TEST TOTO')
> SELECT @.err=@.@.error, @.v_StrMess = 'The problem is HERE ...'
> IF @.err=0
> COMMIT
> ELSE
> BEGIN
> ROLLBACK
> raiserror (@.v_StrMess , 16 , 1)
> END
>
> --
> -oj
>
> "Mick" <mickymickmc@.yahoo.fr> wrote in message
> news:79cab08b.0502151254.3b8d3d9a@.posting.google.com...