Error :Execute Trigger From Remote Server To another Server By Linked Server

i did "Linked server" between To Servers , and it's Working.

For Example :

Server 1 =S1.

Server = S2.

i create table in S1 : name = TblS1

i create same table in S2 : name TblS2

and i create trigger(name tr_cpD) From S1 in TblS1 For send data To TblS2 in S2

/****************** trigger Code ***************

CREATE TRIGGER dbo.tr_cpD

ON dbo.TblS1

AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

insert into [S2].[dbname].[dbo].[TblS2] Select ID,Name from inserted

END

**************************************************

result is :

Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14

The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.

Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14

Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

how i can execute this trigger

Just to clarify: does the insert statement work outside of the trigger from the same S1 server?|||

Yes.

it's Work from Stored Procduers without TRAN (Transaction) .

if i use Transaction From Stored Procduers is not work.

Give me same result above.

|||Is MSDTC enabled on both servers? I think insert from the trigger into a linked server is done inside the distributed transaction.
But I would expect some transaction specific error if it was not so.|||

Microsoft proived some solution, how to overcome this problem. Try it.

http://support.microsoft.com/kb/873160

I tried in the LAN its working fine. But, still in the WAN is not working.

Error :Execute Trigger From Remote Server To another Server By Linked Server

i did "Linked server" between To Servers , and it's Working.

For Example :

Server 1 =S1.

Server = S2.

i create table in S1 : name = TblS1

i create same table in S2 : name TblS2

and i create trigger(name tr_cpD) From S1 in TblS1 For send data To TblS2 in S2

/****************** trigger Code ***************

CREATE TRIGGER dbo.tr_cpD

ON dbo.TblS1

AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

insert into [S2].[dbname].[dbo].[TblS2] Select ID,Name from inserted

END

**************************************************

result is :

Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14

The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.

Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14

Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

how i can execute this trigger

Just to clarify: does the insert statement work outside of the trigger from the same S1 server?|||

Yes.

it's Work from Stored Procduers without TRAN (Transaction) .

if i use Transaction From Stored Procduers is not work.

Give me same result above.

|||Is MSDTC enabled on both servers? I think insert from the trigger into a linked server is done inside the distributed transaction.
But I would expect some transaction specific error if it was not so.|||

Microsoft proived some solution, how to overcome this problem. Try it.

http://support.microsoft.com/kb/873160

I tried in the LAN its working fine. But, still in the WAN is not working.

Error - subquery returning more than one value

Hie.

I have a trigger that monitors changes to my table fields but I get an error saying subquery returned more than one value.Below is the code for my trigger, hope you will figure out whats happening..

CREATE TRIGGER trgmyTableAuditFields ON myTable
WITH ENCRYPTION
FOR INSERT, UPDATE, DELETE
AS
BEGIN

DECLARE @.col VARCHAR(200)
DECLARE @.primarykeycol VARCHAR(200)
DECLARE @.sql VARCHAR(2000)
DECLARE @.action VARCHAR(200)
DECLARE @.AuditInsert BIT
DECLARE @.AuditUpdate BIT
DECLARE @.AuditDelete BIT

SELECT @.action =
CASE
WHEN (SELECT COUNT(*) FROM deleted) = 0 THEN 'INSERTION'
WHEN (SELECT COUNT(*) FROM inserted) = 0 THEN 'DELETION'
ELSE 'UPDATE'
END

DECLARE curAuditFields CURSOR FOR
SELECT c.[name], ac.[AuditInsert], ac.[AuditUpdate], ac.[AuditDelete]
FROM syscolumns c
INNER JOIN sysobjects o ON o.[id] = c.[id]
INNER JOIN aucAuditColumns ac ON ac.[Column] = c.[name] AND ac.[Table] = o.[name]
WHERE o.[name] = 'myTable'
AND c.[xtype] NOT IN (35, 99, 34)
OPEN curAuditFields

FETCH NEXT FROM curAuditFields INTO @.col, @.AuditInsert, @.AuditUpdate, @.AuditDelete

SELECT [EffectiveDate],[LowerThresholdAmount],[ThresholdAmount],[Percentage],[ID] INTO #new FROM inserted
SELECT [EffectiveDate],[LowerThresholdAmount],[ThresholdAmount],[Percentage],[ID] INTO #old FROM deleted

WHILE @.@.FETCH_STATUS = 0 BEGIN

IF (@.action = 'INSERTION' AND @.AuditInsert = 1) OR
(@.action = 'UPDATE' AND @.AuditUpdate = 1) OR
(@.action = 'DELETION' AND @.AuditDelete = 1)
BEGIN

SET @.sql = 'IF (SELECT [' + @.col + '] FROM #new) <> (SELECT [' + @.col + '] FROM #old) OR (SELECT COUNT(*) FROM #old) = 0 OR (SELECT COUNT(*) FROM #new) = 0 BEGIN' + CHAR(10) +
'DECLARE @.old VARCHAR(50)' + CHAR(10) +
'DECLARE @.new VARCHAR(50)' + CHAR(10) +
' DECLARE @.RecordVal VARCHAR(50)' + CHAR(10) +

' SELECT @.new = CAST([' + @.col + '] AS VARCHAR(50)) FROM #new' + CHAR(10) +
' SELECT @.old = CAST([' + @.col + '] AS VARCHAR(50)) FROM #old' + CHAR(10) +
' SELECT @.RecordVal = CAST([ID] AS VARCHAR(50)) FROM #old' + CHAR(10) +
'IF @.RecordVal IS NULL BEGIN' + CHAR(10) +
'SELECT @.RecordVal = CAST([ID] AS VARCHAR(50)) FROM #new' + CHAR(10) +
'END' + CHAR(10) +

'INSERT INTO AuditTable([Username], ModificationDate, SourceTable, ModifiedField, OldValue, NewValue, [Action], [RecordID])' + CHAR(10) +
'VALUES (''' + SUSER_SNAME(SUSER_SID()) + ''',''' + CAST(GETDATE() AS VARCHAR(20)) + ''', ''myTable'', ''' + @.col + ''', @.old, @.new, ''' + @.action + ''', @.RecordVal)' + CHAR(10) +
'END'
EXEC (@.sql)
END

FETCH NEXT FROM curAuditFields INTO @.col, @.AuditInsert, @.AuditUpdate, @.AuditDelete
END

DROP TABLE #new
DROP TABLE #old

CLOSE curAuditFields
DEALLOCATE curAuditFields
END

Quote:

Originally Posted by Aleck

Hie.

I have a trigger that monitors changes to my table fields but I get an error saying subquery returned more than one value.Below is the code for my trigger, hope you will figure out whats happening..

CREATE TRIGGER trgmyTableAuditFields ON myTable
WITH ENCRYPTION
FOR INSERT, UPDATE, DELETE
AS
BEGIN

DECLARE @.col VARCHAR(200)
DECLARE @.primarykeycol VARCHAR(200)
DECLARE @.sql VARCHAR(2000)
DECLARE @.action VARCHAR(200)
DECLARE @.AuditInsert BIT
DECLARE @.AuditUpdate BIT
DECLARE @.AuditDelete BIT

SELECT @.action =
CASE
WHEN (SELECT COUNT(*) FROM deleted) = 0 THEN 'INSERTION'
WHEN (SELECT COUNT(*) FROM inserted) = 0 THEN 'DELETION'
ELSE 'UPDATE'
END

DECLARE curAuditFields CURSOR FOR
SELECT c.[name], ac.[AuditInsert], ac.[AuditUpdate], ac.[AuditDelete]
FROM syscolumns c
INNER JOIN sysobjects o ON o.[id] = c.[id]
INNER JOIN aucAuditColumns ac ON ac.[Column] = c.[name] AND ac.[Table] = o.[name]
WHERE o.[name] = 'myTable'
AND c.[xtype] NOT IN (35, 99, 34)
OPEN curAuditFields

FETCH NEXT FROM curAuditFields INTO @.col, @.AuditInsert, @.AuditUpdate, @.AuditDelete

SELECT [EffectiveDate],[LowerThresholdAmount],[ThresholdAmount],[Percentage],[ID] INTO #new FROM inserted
SELECT [EffectiveDate],[LowerThresholdAmount],[ThresholdAmount],[Percentage],[ID] INTO #old FROM deleted

WHILE @.@.FETCH_STATUS = 0 BEGIN

IF (@.action = 'INSERTION' AND @.AuditInsert = 1) OR
(@.action = 'UPDATE' AND @.AuditUpdate = 1) OR
(@.action = 'DELETION' AND @.AuditDelete = 1)
BEGIN

SET @.sql = 'IF (SELECT [' + @.col + '] FROM #new) <> (SELECT [' + @.col + '] FROM #old) OR (SELECT COUNT(*) FROM #old) = 0 OR (SELECT COUNT(*) FROM #new) = 0 BEGIN' + CHAR(10) +
'DECLARE @.old VARCHAR(50)' + CHAR(10) +
'DECLARE @.new VARCHAR(50)' + CHAR(10) +
' DECLARE @.RecordVal VARCHAR(50)' + CHAR(10) +

' SELECT @.new = CAST([' + @.col + '] AS VARCHAR(50)) FROM #new' + CHAR(10) +
' SELECT @.old = CAST([' + @.col + '] AS VARCHAR(50)) FROM #old' + CHAR(10) +
' SELECT @.RecordVal = CAST([ID] AS VARCHAR(50)) FROM #old' + CHAR(10) +
'IF @.RecordVal IS NULL BEGIN' + CHAR(10) +
'SELECT @.RecordVal = CAST([ID] AS VARCHAR(50)) FROM #new' + CHAR(10) +
'END' + CHAR(10) +

'INSERT INTO AuditTable([Username], ModificationDate, SourceTable, ModifiedField, OldValue, NewValue, [Action], [RecordID])' + CHAR(10) +
'VALUES (''' + SUSER_SNAME(SUSER_SID()) + ''',''' + CAST(GETDATE() AS VARCHAR(20)) + ''', ''myTable'', ''' + @.col + ''', @.old, @.new, ''' + @.action + ''', @.RecordVal)' + CHAR(10) +
'END'
EXEC (@.sql)
END

FETCH NEXT FROM curAuditFields INTO @.col, @.AuditInsert, @.AuditUpdate, @.AuditDelete
END

DROP TABLE #new
DROP TABLE #old

CLOSE curAuditFields
DEALLOCATE curAuditFields
END

i did not continue reading your code...am not sure how efficient it is to put a CUSOR inside a trigger. maybe you would want to find another way of doing this

ERR_SYSERR(104):PRM:SQLExecDirect failed (trigger)

The trigger fire correctly with insert new comment, but when user update a
comment field, I got the following error: ERR_SYSERR(104):PRM:SQLExecDirect
failed
I create a trigger as following:
DECLARE @.commentsOut AS VARCHAR(2000), @.acct AS BigInt, @.sub AS smallInt
IF UPDATE(comment1) OR UPDATE(comment2) OR UPDATE(comment3) OR
UPDATE(comment4)
--Get Acct, Sub number
SELECT @.acct = memb.acct, @.sub = prof.no
FROM memb
JOIN prof ON memb.rowno = prof.rowno
--Get Comments
EXEC usp_LMA_CUBE_trig_comments @.acct, @.sub, @.comments = @.commentsOUT OUTPUT
--Update or insert into other server with linkserver
EXEC usp_LMA_CUBE_upd_comments @.acct, @.sub, @.commentsOUT
Please help,
CulamI run an update statement in Query Analyzer, it works fine, but not in the
application.
"culam" wrote:

> The trigger fire correctly with insert new comment, but when user update a
> comment field, I got the following error: ERR_SYSERR(104):PRM:SQLExecDire
ct
> failed
> I create a trigger as following:
> DECLARE @.commentsOut AS VARCHAR(2000), @.acct AS BigInt, @.sub AS smallInt
> IF UPDATE(comment1) OR UPDATE(comment2) OR UPDATE(comment3) OR
> UPDATE(comment4)
> --Get Acct, Sub number
> SELECT @.acct = memb.acct, @.sub = prof.no
> FROM memb
> JOIN prof ON memb.rowno = prof.rowno
> --Get Comments
> EXEC usp_LMA_CUBE_trig_comments @.acct, @.sub, @.comments = @.commentsOUT OUTP
UT
> --Update or insert into other server with linkserver
> EXEC usp_LMA_CUBE_upd_comments @.acct, @.sub, @.commentsOUT
> Please help,
> Culam|||On Mon, 28 Mar 2005 16:21:03 -0800, culam wrote:

>The trigger fire correctly with insert new comment, but when user update a
>comment field, I got the following error: ERR_SYSERR(104):PRM:SQLExecDirec
t
>failed
>I create a trigger as following:
>DECLARE @.commentsOut AS VARCHAR(2000), @.acct AS BigInt, @.sub AS smallInt
>IF UPDATE(comment1) OR UPDATE(comment2) OR UPDATE(comment3) OR
>UPDATE(comment4)
>--Get Acct, Sub number
>SELECT @.acct = memb.acct, @.sub = prof.no
>FROM memb
>JOIN prof ON memb.rowno = prof.rowno
>--Get Comments
>EXEC usp_LMA_CUBE_trig_comments @.acct, @.sub, @.comments = @.commentsOUT OUTPU
T
> --Update or insert into other server with linkserver
>EXEC usp_LMA_CUBE_upd_comments @.acct, @.sub, @.commentsOUT
Hi Culam,
First, I'd like to point out two errors in your trigger code that are
probably not the cause of the error message you report (at least not
directly), but that are important nonetheless.
First: triggers should refer to the inserted and/or deleted pseudotables
to find out which rows were affected by the operation that fired the
trigger. If you refer to the base tables only, you're working on the
entire set of rows in the table, whether updated or not.
Second: triggers fire once per executed statement, not once per row
affected. Your code should ensure that it will also work properly if the
inserted and/or deleted pseudotables contain no rows or more than one
row. If you just use SELECT @.xxx = yyy FROM inserted to copy the new
data in a variable, SQL Server will pick (randomly) one of the new rows,
ignoring all others. It won't even raise an error or warning condition!!
Another (potential - I don't really know your requirements) flaw in your
code is the lack of BEGIN and END after the IF. Are you aware that you
need BEGIN and END if you want more than one statement to be executed
conditionally? Your current code is equivalent to
IF UPDATE(..) OR UPDATE(..) ...
BEGIN
-- Get Acct, Sub number
SELECT @.acct = ..., @.sub = ...
FROM ...
END
-- Get Comments
EXEC usp_LMA_CUBE_trig_comments ...
-- update or insert ...
EXEC usp_LMA_CUBE_upd_comments ...
If an update is executed but none of the columns comment1 through
comment4 is in the SET list, then the stored procedures will be executed
with @.acct and @.sub equal to NULL.
Now we get to your actual error message. I've never seen this message
before; in fact, I've never seen an error message on SQL Server that
uses this format. I suspect that your client software has done some
reformatting of the error message. Could you check the error message you
get when you run the same update statement from Query Analyzer?
I'm quite sure that the error is not raised in the code you posted. That
leaves the two stored procedures as possible offenders. But since you
didn't post the code of these procedures, I can't say any more than
that. Maybe the error will spontaneoously disappear once you fix the
other issues I've mentioned. If not, then I suggest you code the updated
trigger code plus the code of both stored procedures. Posting the DDL
(as CREATE TABLE statements) for all tables involved, plus some rows of
sample data (as INSERT statements) might help as well.
Oh, and by the way - have you considered normalizing your data, storing
the comments in a seperate table where they probably belong? Names like
comment1, comment2, comment3, comment4 r of a repeating group...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)