Trigger Problem

[pcawdron]

I'm having problems with a trigger that should work.

When a row is added to table1 I need to trigger some SQL that goes away to DB2 database and grabs information about the inserted data and adds it to table2

I've developed a procedure using OPENQUERY on a AS400 linked server that works beautifully. It's called GetAdditionalAS400Data. If I open the query analyser and type GetAdditionalAS400Data '12345B' it appends a row to the second table. So now, it should just be a case of calling that procedure from a trigger.

create trigger getAdditionalInformation
on dbo.table1
for INSERT
as
declare @ref char(7)
select @ref = xRef from INSERTED
exec('GetAdditionalAS400Data ''' + @ref + '''')
go

But it doesn't work. I've even tried simplifying things. The line below runs from SQL Server Query Analyser and adds a row to table2.

exec('GetAdditionalAS400Data ''12345''')

But this trigger fails....

alter trigger getAdditionalInformation
on dbo.table1
for INSERT
as
exec('GetAdditionalAS400Data ''12345''')
go

The error message is... ODBC SQL Server Driver OLE/DB Message Returned : AS400 Driver Not Capable. The operation could not be performed because the OLE DB Provider MSDASQL was unable to begin a distributed transaction.

What I don't understand is that it runs fine in SQL Query Analyser???

Should I be using something other than OPENQUERY to retrieve my information from the AS400?

Any ideas?

How would you do this?

 

[sguslan]

It will help greatly if you port the stored procedure code.

Also try :

exec GetAdditionalAS400Data @ref
instead of
exec('GetAdditionalAS400Data ''' + @ref + '''')

 

[pcawdron]

Sguslan,

I tried your suggestion, but the same error message occurs.

Here's the stored procedure code

CREATE PROCEDURE dbo.GetAdditionalAS400Data (@GMACRef char(7))
as
declare @sqlstr varchar(800)
select @sqlstr = 'SELECT
Ref,
VIN,
BusinessName,
VehicleDescription
COALESCE(EngineNumber,''''Unknown''''),
COALESCE(BodyNumber,''''Unknown''''),
COALESCE(StockNumber,''''Unknown'''')
FROM
VehicleDealerView
WHERE
Ref = ''''' + @GMACRef + '''''
)
'

exec('INSERT INTO dbo.tbl_SwapOut_Additional_Details
(GMACRef,VIN,GMACDealer,[Description],Engine,Body,Stock)
SELECT *
FROM OPENQUERY(AS400RUMBA,''' + @sqlstr +''' )' )
GO

If I type GetAdditionalAS400Data 123456B in SQL Server Query Analyser it works, but the trigger doesn't. After thinking about it overnight I think the Trigger is trying to involve the OPENQUERY pass-through query in a transaction that can be rolled back and the AS400 is saying no way. At least, that's the only explaination I can come up with to explain the error message.

Any ideas?

Thanks again for all your help. It's gratefully appreciated

Cheers,
Peter

 

[tlbroadbent]

Check out this link. The newsgroup reply may provide the info you need.

http://groups.google.com/groups?selm=qi0dgt0k86uoln5gqgsqj9kut6i1vuq75n@4ax.com

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[rjensenx]

I have a similar problem with an odbc connection to sybase from sql server. A stored procedure to update sybase tables from sql server table runs great, when I execute the stored procedure from a trigger I get:

Server: Msg 7391, Level 16, State 1, Procedure tr_ron, Line 6
The operation could not be performed because the OLE DB provider 'MSDASQL' was unable to begin a distributed transaction.
[OLE/DB provider returned message: [MERANT][ODBC Sybase driver]Distributed transaction enlistment failed.]

Has anyone found a workaround? any help is appreciated.

 

[tlbroadbent]

Have you tried SET XACT_ABORT ON before executing the statement that causes the trigger to fire? I don't know if this will help when the remote server is Sybase but it is normally required for distributed transactions.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[LloydDobler]

pcawdron:

I noticed you have an SQL server accessing an AS400 server. I've been able to create a linked server, but am unable to view any data in the tables, the only options are copy and help. I was wondering if you could provide some help as to how you connect to your AS400 and how you can view the data in the tables. Thanks

 

[Sylvor]

Bendall,

You cannot view the content of a linked server table from Enterprise Manager, you must run a query either using the four-part name of the server or, as it's linked, use OPENQUERY.

EG:

SELECT OPENQUERY(linked_server_name, 'SELECT * FROM table_name')

I appreciate this post is aeons old, but you never know, it may be something bendall still wonders about

D

Dave Bennett