Running stored procedure containing openquery from a trigger

Hi,
I have a stored procedure called sp_imp - it works fine from query
analyser or when executed using ADO etc..
I wanted to run it away from the client because it took 16 seconds +
so I did the following
Created a new table called tbl_trigger and put a trigger on it that
basiclly runs the first stored procedure on INSERT
CREATE TRIGGER trg_Import ON dbo.tbl_trigger
FOR INSERT
AS
DECLARE @.BatchNo2 VARCHAR(9)
DECLARE @.get_uid VARCHAR(9)
SELECT @.get_UID = (SELECT @.@.IDENTITY)
/*PRINT(@.GET_UID + ' TEST')*/
SELECT @.BatchNo2 = (SELECT BATCH_NO from Tbl_Trigger WHERE ID =
@.GET_UID)
/*PRINT(@.batchNo2 + ' TEST2')*/
EXEC prc_import_Order @.batchNo = @.batchNo2
I then made the client call a new stored procedure called sp_ins does
the following
CREATE PROCEDURE dbo.sp_ins (@.batchNo varchar(9))
AS
DECLARE @.TSQL varchar(4000)
SET @.TSQL = 'INSERT INTO tbl_trigger (batch_no) VALUES (' +
@.BATCHNO + ')'
EXEC (@.TSQL)
GO
When I run the stored procedure on its own it works fine, from teh
access client, from query analyser etc etc. However when it is run
from the trigger it fails
<--PRINT put in the trigger to make sure the the correct parameter is
flowing through from trigger to SP-->
INSERT INTO tbl_trigger (batch_no) VALUES (132209)
12 TEST
132209 TEST2
132209
INSERT INTO [gload].[dbo].[tbl_OHeader] (...) SELECT ... FROM
Openquery(BS01BAA, 'SELECT * FROM GML01A.YSBTCHP_LINK TM INNER JOIN
BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO = T1.EORNO INNER JOIN
GML01A.GLOAD_CUST T2 ON T1.ECSTN9 = T2.ECSTNC AND T1.EDTNO5 =
T2.EDTNOC WHERE TM.BATCH_NUMBER = 132209') ORDER BY EORNO
<--PRINT put in the trigger to make sure the the correct parameter is
flowing through from trigger to SP-->
<--The error-->
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. One or more arguments
were reported invalid by the provider.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 90 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
OLE DB error trace [OLE/DB Provider 'MSDASQL'
ITransactionJoin::JoinTransaction returned 0x80070057: One or more
arguments were reported invalid by the provider.].
<--end error-->
Help me
The stored procedure that works in QA or from a ADO call is as
follows :-
CREATE PROCEDURE dbo.prc_Import_Order (@.batchNo varchar(9))
AS
DECLARE @.TSQL varchar(8000)
/*
================================================== =======
Here we grab the header information and put it to the table
tbl_OHeader
*Still missing customer information - DONE
*Missing Text for the customer
================================================== =======
*/
SET @.TSQL = 'INSERT INTO [gload].[dbo].[tbl_OHeader]
(BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,BATCH_DATE,BA TCH_TIME,SBUSY,ISHRR,GORTP,EORNO,ECSTN9,EDTNO5,FSO CN,FSSDN,FSTXT,GPLID1,ECCODA,USXRTA,FSCPR1,ESOEB,
[@.SDOE],TSTOE,DSCRF1,DSCNT1,[@.SRDD],GRUTE1,SSOVR,ESOVB,
[@.SOVD],TSOVT,SSCCH,[@.SCHD],ESCHB,[@.SCRD],ESCRB,
[@.PFED],ESITE2,SASPO,SASWO,FPIDD1,ESTOR1,EOROR,GSTDC1,GSW DC1,RWDIS1,
[@.DLPA],SACKN,SPICK,SISSU,SINVC,GCNRC1,ESRPP,ESRPS,ISFTR, EORNP,FBORD,TSRDD3,FCRSK1,FSHB1,FSHB2,FSHB3,
' +
'FSHB4,FSHB5,FSHM1,FSHM2,FSHM3,GSPID1,FASOO,SPOAS, SWOAS,ECSTNC,EDTNOC,DDNAM,ADAD1,ADAD2,ADAD3,ADAD4, ADPC1,ADPC2,DCNAM,
ACAD1, ACAD2, ACAD3, ACAD4, ACPC1,ACPC2) '+
'SELECT BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,[@.BATCH_DATE],
[@.BATCH_TIME],SBUSY,ISHRR,GORTP,EORNO,ECSTN9,EDTNO5,FSOCN,FSSDN ,FSTXT,GPLID1,ECCODA,USXRTA,FSCPR1,ESOEB,
[@.SDOE],TSTOE,DSCRF1,DSCNT1,[@.SRDD],GRUTE1,SSOVR,ESOVB,
[@.SOVD],TSOVT,SSCCH,[@.SCHD],ESCHB,[@.SCRD],ESCRB,
[@.PFED],ESITE2,SASPO,SASWO,FPIDD1,ESTOR1,EOROR,GSTDC1,GSW DC1,RWDIS1,
[@.DLPA],SACKN,SPICK,SISSU,SINVC,GCNRC1,ESRPP,ESRPS,ISFTR, EORNP,FBORD,TSRDD3,FCRSK1,FSHB1,FSHB2,FSHB3,FSHB4, FSHB5,FSHM1,FSHM2,
'+
'FSHM3,GSPID1,FASOO,SPOAS,SWOAS,ECSTNC,EDTNOC,DDNA M,ADAD1,ADAD2,ADAD3,ADAD4,ADPC1,ADPC2,DCNAM,
ACAD1, ACAD2, ACAD3, ACAD4, ACPC1,ACPC2 FROM Openquery(BS01BAA, '+
'''SELECT * FROM GML01A.YSBTCHP_LINK TM '+
'INNER JOIN BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO = T1.EORNO '+
'INNER JOIN GML01A.GLOAD_CUST T2 ON T1.ECSTN9 = T2.ECSTNC AND
T1.EDTNO5 = T2.EDTNOC '+
'WHERE TM.BATCH_NUMBER = ' + @.BATCHNO + ''') ORDER BY EORNO'
PRINT @.TSQL
EXEC (@.TSQL)
/*
------
End the header output SQL
------
*/
/*
================================================== ========================
Here we grab the line/line Extra/product detail information and put
it to the table tbl_OLine
================================================== ========================
*/
SET @.TSQL = 'INSERT INTO [gload].[dbo].[tbl_OLine]
(BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,BATCH_DATE,BA TCH_TIME,
SDRCD,GORTP8,EORNO8,LORDS4,
[@.SRDD2],QREQB,QREQC,QPIKB,QPIKC,QDESB,QDESC,QINVB,QINVC,Q PODB1,QPODC1,FDEFC,FINFC,FDUES2,EITN12,LISGT2,EITN 22,EITN32,EITN42,EITN52,ESTORH,TSRDD1,FSSB1,FSSB2, FSSB3,FSSM1,FSSM2,SDRCD_old,GORTP1,EORNO1,LORDS,IS HRR1,EITM16,EITM26,EITM36,EITM46,EITM56,ESTOR2,ITM RR6,LISGS6,UOMSQ,
[UPRC2],CUPRC2,UOMPC2,FPOVR,GSQDC2,RQDIS1,GSTDC2,RTDIS1,[ TOVL],CTOVL,
[TOVI],CTOVI,FSSDN1,FSTXT1,FSILP,EOROR1, '+
'EITM1B,EITM2B,EITM3B,EITM4B,EITM5B,[@.SRDD1],
[@.SODD],EVATCB,GCNRC2,SASPO1,SASWO1,ESRPP1,ESRPS1,ISFTR1, FSRFD,TSRDD2,FSDB1,FSDB2,FSDB3,FSDM1,FSDM2,FOATT,F WOAT,ELOTNK,ELOTSK,GPLIDA,EOAIDA,ELAYN1,ITEM,DITMD ,UOMTU,MUDN3,QGWGT)
'+
'SELECT BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,[@.BATCH_DATE],
[@.BATCH_TIME],SDRCD,GORTP8,EORNO8,LORDS4,
[@.SRDD2],QREQB,QREQC,QPIKB,QPIKC,QDESB,QDESC,QINVB,QINVC,Q PODB1,QPODC1,FDEFC,FINFC,FDUES2,EITN12,LISGT2,EITN 22,EITN32,EITN42,EITN52,ESTORH,TSRDD1,FSSB1,FSSB2, FSSB3,FSSM1,FSSM2,
SDRCD,GORTP1,EORNO1,LORDS,ISHRR1,EITM16,EITM26,EIT M36,EITM46,EITM56,ESTOR2,ITMRR6,LISGS6,UOMSQ,
[UPRC2],CUPRC2,UOMPC2,FPOVR,GSQDC2,RQDIS1,GSTDC2,RTDIS1,[ TOVL],CTOVL,
[TOVI],CTOVI,FSSDN1,FSTXT1,FSILP,EOROR1,EITM1B,EITM2B,EI TM3B,EITM4B,
'+
'EITM5B,[@.SRDD1],
[@.SODD],EVATCB,GCNRC2,SASPO1,SASWO1,ESRPP1,ESRPS1,ISFTR1, FSRFD,TSRDD2,FSDB1,FSDB2,FSDB3,FSDM1,FSDM2,FOATT,F WOAT,ELOTNK,ELOTSK,GPLIDA,EOAIDA,ELAYN1,ITEM,DITMD ,UOMTU,MUDN3,QGWGT
FROM Openquery(BS01BAA, '+
'''SELECT * FROM GML01A.YSBTCHP_LINK TM '+
'INNER JOIN BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO = T1.EORNO '+
'INNER JOIN BS01BAA.BSSODSP T2 ON T1.EORNO = T2.EORNO8 '+
'INNER JOIN BS01BAA.BSSODRP T3 ON T2.EORNO8 = T3.EORNO1 AND
t2.LORDS4 = T3.LORDS '+
'INNER JOIN GML01A.GLOAD_ITEM T4 ON T2.EITN12 = T4.ITEM '+
'WHERE TM.BATCH_NUMBER = ' + @.BATCHNO + ''') ORDER BY EORNO, LORDS4'
PRINT @.TSQL
EXEC (@.TSQL)
/*
------
End the Line output SQL
------
*/
/*
================================================== ========================
Here we grab the order text table to output ready for later when we
grab that data and put it
onto the paperwork
================================================== ========================
-Old sql
SET @.TSQL = 'INSERT INTO [gload].[dbo].[tbl_OText] (BATCH_NO,
EORNO3, LORDS1, FTEXT, FTXPC, DTEXT1, ISNTR) '+
'SELECT BATCH_NUMBER,EORNO3,LORDS1,FTEXT,FTXPC,DTEXT1,ISNT R FROM
Openquery(BS01BAA, '+
'''SELECT BATCH_NUMBER, EORNO3, LORDS1,FTEXT,FTXPC,DTEXT1,ISNTR FROM
GML01A.YSBTCHP_LINK TM '+
'INNER JOIN BS01BAA.BSSOTRP T1 ON TM.BATCH_OI_NO = T1.EORNO3 '+
'WHERE TM.BATCH_NUMBER = ' + @.BATCHNO + ''') ORDER BY EORNO3,
LORDS1'
*/
SET @.TSQL = 'INSERT INTO [gload].[dbo].[tbl_OText] (BATCH_NO,
EORNO3, LORDS1, FTEXT, FTXPC, DTEXT1, ISNTR) '+
'SELECT BATCH_NUMBER,EORNO3,LORDS1,FTEXT,FTXPC,DTEXT1,ISNT R FROM
Openquery(BS01BAA, '+
'''SELECT BATCH_NUMBER, EORNO3, LORDS1,FTEXT,FTXPC,DTEXT1,ISNTR FROM
GML01A.YSBTCHP_LINK TM '+
'INNER JOIN GML01A.GLOAD_TEXT T1 ON TM.BATCH_OI_NO = T1.EORNO3 '+
'WHERE TM.BATCH_NUMBER = ' + @.BATCHNO + ''') ORDER BY EORNO3,
LORDS1'
EXEC (@.TSQL)
SET @.TSQL = 'INSERT INTO [gload].[dbo].[tbl_CText] (IACCK, EDTNP4,
NTEXT2, ITXRR5, INTRR3, BATCH_NO) '+
'SELECT IACCK, EDTNP4, NTEXT2, ITXRR5, INTRR3, BATCH_NUMBER FROM
Openquery(BS01BAA, '+
'''SELECT BATCH_NUMBER, IACCK, EDTNP4, NTEXT2, ITXRR5, INTRR3,
BATCH_NUMBER FROM GML01A.YSBTCHP_LINK TM '+
'INNER JOIN BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO = T1.EORNO '+
'INNER JOIN BS01BAA.BCTEXTP T2 ON T1.ECSTN9 = T2.IACCK AND T1.EDTNO5
= EDTNP4 '+
'WHERE TM.BATCH_NUMBER = ' + @.BATCHNO + ''') ORDER BY INTRR3'
EXEC (@.TSQL)
/*
------
End the Order text output SQL
------
*/
/*
================================================== ========================
Here we update the YSBTCHP_DONE table to show we have processed the
batch
*If somone tries a batch for a second time, it wont proces it above
but will
add another entry into this table, must find a way to stop that!
================================================== ========================
*/
SET @.TSQL = 'INSERT INTO Openquery(GMP01A, '+
'''SELECT DONE_BTCH FROM GMP01A.YSBTCHP_DONE WHERE 1=0 '+
''') VALUES ('+@.BATCHNO+')'
EXEC (@.TSQL)
/*
------
End the header output SQL
------
*/
SET @.TSQL = 'SELECT * FROM Openquery(BS01BAA, '+
'''SELECT * FROM GML01A.YSBTCHP_LINK TM '+
'INNER JOIN BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO = T1.EORNO '+
'INNER JOIN BS01BAA.BSSODSP T2 ON T1.EORNO = T2.EORNO8 '+
'INNER JOIN BS01BAA.BSSODRP T3 ON T2.EORNO8 = T3.EORNO1 AND
t2.LORDS4 = T3.LORDS '+
'WHERE TM.BATCH_NUMBER = ' + @.BATCHNO + ''') ORDER BY EORNO, LORDS4'
GO
I beleive now after doing a simple stored procedure it has something
to do with not liking
openquery going through a linked server..
Anyone have any thoughts on this?
|||The access violation looks like a bug in SQL Server. Suggest you open a
support call with MS.
"Reggie" <Antony.Symonds@.gmail.com> wrote in message
news:1179503282.752406.226620@.h2g2000hsg.googlegro ups.com...
> Hi,
> I have a stored procedure called sp_imp - it works fine from query
> analyser or when executed using ADO etc..
> I wanted to run it away from the client because it took 16 seconds +
> so I did the following
> Created a new table called tbl_trigger and put a trigger on it that
> basiclly runs the first stored procedure on INSERT
>
> CREATE TRIGGER trg_Import ON dbo.tbl_trigger
> FOR INSERT
> AS
> DECLARE @.BatchNo2 VARCHAR(9)
> DECLARE @.get_uid VARCHAR(9)
> SELECT @.get_UID = (SELECT @.@.IDENTITY)
> /*PRINT(@.GET_UID + ' TEST')*/
> SELECT @.BatchNo2 = (SELECT BATCH_NO from Tbl_Trigger WHERE ID =
> @.GET_UID)
> /*PRINT(@.batchNo2 + ' TEST2')*/
> EXEC prc_import_Order @.batchNo = @.batchNo2
>
> I then made the client call a new stored procedure called sp_ins does
> the following
>
> CREATE PROCEDURE dbo.sp_ins (@.batchNo varchar(9))
> AS
> DECLARE @.TSQL varchar(4000)
> SET @.TSQL = 'INSERT INTO tbl_trigger (batch_no) VALUES (' +
> @.BATCHNO + ')'
> EXEC (@.TSQL)
> GO
>
> When I run the stored procedure on its own it works fine, from teh
> access client, from query analyser etc etc. However when it is run
> from the trigger it fails
> <--PRINT put in the trigger to make sure the the correct parameter is
> flowing through from trigger to SP-->
> INSERT INTO tbl_trigger (batch_no) VALUES (132209)
> 12 TEST
> 132209 TEST2
> 132209
> INSERT INTO [gload].[dbo].[tbl_OHeader] (...) SELECT ... FROM
> Openquery(BS01BAA, 'SELECT * FROM GML01A.YSBTCHP_LINK TM INNER JOIN
> BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO = T1.EORNO INNER JOIN
> GML01A.GLOAD_CUST T2 ON T1.ECSTN9 = T2.ECSTNC AND T1.EDTNO5 =
> T2.EDTNOC WHERE TM.BATCH_NUMBER = 132209') ORDER BY EORNO
> <--PRINT put in the trigger to make sure the the correct parameter is
> flowing through from trigger to SP-->
>
> <--The error-->
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDASQL' reported an error. One or more arguments
> were reported invalid by the provider.
> ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 90 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> OLE DB error trace [OLE/DB Provider 'MSDASQL'
> ITransactionJoin::JoinTransaction returned 0x80070057: One or more
> arguments were reported invalid by the provider.].
> <--end error-->
> Help me
>