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 meThe 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)
/*
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
Here we grab the header information and put it to the table
tbl_OHeader
*Still missing customer information - DONE
*Missing Text for the customer
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D
*/
SET @.TSQL =3D 'INSERT INTO [gload].[dbo].[tbl_OHeader]
(BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,BAT
CH_DATE,BATCH_TIME,SBUSY,ISHRR,GORT=
P,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,
& #91;@.PFED],ESITE2,SASPO,SASWO,FPIDD1,EST
OR1,EOROR,GSTDC1,GSWDC1,RWDIS1,
& #91;@.DLPA],SACKN,SPICK,SISSU,SINVC,GCNRC
1,ESRPP,ESRPS,ISFTR,EORNP,FBORD,TSR
DD3=
,FCRSK1,FSHB1,FSHB2,FSHB3,
' +
=20
'FSHB4,FSHB5,FSHM1,FSHM2,FSHM3,GSPID1,FA
SOO,SPOAS,SWOAS,ECSTNC,EDTNOC,DDNAM=
,ADAD1,ADAD2,ADAD3,ADAD4,ADPC1,ADPC2,DCN
AM,
ACAD1, ACAD2, ACAD3, ACAD4, ACPC1,ACPC2) '+
'SELECT BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,[@.BATCH_DATE],
& #91;@.BATCH_TIME],SBUSY,ISHRR,GORTP,EORNO
,ECSTN9,EDTNO5,FSOCN,FSSDN,FSTXT,GP
LID=
1,ECCODA,USXRTA,FSCPR1,ESOEB,
[@.SDOE],TSTOE,DSCRF1,DSCNT1,[@.SRDD],GRUTE1,SSOVR,ESOVB,
[@.SOVD],TSOVT,SSCCH,[@.SCHD],ESCHB,[@.SCRD],ESCRB,
& #91;@.PFED],ESITE2,SASPO,SASWO,FPIDD1,EST
OR1,EOROR,GSTDC1,GSWDC1,RWDIS1,
& #91;@.DLPA],SACKN,SPICK,SISSU,SINVC,GCNRC
1,ESRPP,ESRPS,ISFTR,EORNP,FBORD,TSR
DD3=
,FCRSK1,FSHB1,FSHB2,FSHB3,FSHB4,FSHB5,FS
HM1,FSHM2,
'+
=20
'FSHM3,GSPID1,FASOO,SPOAS,SWOAS,ECSTNC,E
DTNOC,DDNAM,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 =3D T1.EORNO '+
'INNER JOIN GML01A.GLOAD_CUST T2 ON T1.ECSTN9 =3D T2.ECSTNC AND
T1.EDTNO5 =3D T2.EDTNOC '+
'WHERE TM.BATCH_NUMBER =3D ' + @.BATCHNO + ''') ORDER BY EORNO'
PRINT @.TSQL
EXEC (@.TSQL)
/*
----=
---
End the header output SQL
----=
---
*/
/*
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Here we grab the line/line Extra/product detail information and put
it to the table tbl_OLine
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
*/
SET @.TSQL =3D 'INSERT INTO [gload].[dbo].[tbl_OLine]
(BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,BAT
CH_DATE,BATCH_TIME,
SDRCD,GORTP8,EORNO8,LORDS4,
& #91;@.SRDD2],QREQB,QREQC,QPIKB,QPIKC,QDES
B,QDESC,QINVB,QINVC,QPODB1,QPODC1,F
DEF=
C,FINFC,FDUES2,EITN12,LISGT2,EITN22,EITN
32,EITN42,EITN52,ESTORH,TSRDD1,FSSB=
1,FSSB2,FSSB3,FSSM1,FSSM2,SDRCD_old,GORT
P1,EORNO1,LORDS,ISHRR1,EITM16,EITM2=
6,EITM36,EITM46,EITM56,ESTOR2,ITMRR6,LIS
GS6,UOMSQ,
& #91;=A3UPRC2],CUPRC2,UOMPC2,FPOVR,GSQDC2
,RQDIS1,GSTDC2,RTDIS1,[=A3TOVL]
,CTOVL,
& #91;=A3TOVI],CTOVI,FSSDN1,FSTXT1,FSILP,E
OROR1, '+
'EITM1B,EITM2B,EITM3B,EITM4B,EITM5B,[@.SRDD1],
& #91;@.SODD],EVATCB,GCNRC2,SASPO1,SASWO1,E
SRPP1,ESRPS1,ISFTR1,FSRFD,TSRDD2,FS
DB1=
,FSDB2,FSDB3,FSDM1,FSDM2,FOATT,FWOAT,ELO
TNK,ELOTSK,GPLIDA,EOAIDA,ELAYN1,ITE=
M,DITMD,UOMTU,MUDN3,QGWGT)
'+
'SELECT BATCH_TYPE,BATCH_NUMBER,BATCH_OI_NO,[@.BATCH_DATE],
& #91;@.BATCH_TIME],SDRCD,GORTP8,EORNO8,LOR
DS4,
& #91;@.SRDD2],QREQB,QREQC,QPIKB,QPIKC,QDES
B,QDESC,QINVB,QINVC,QPODB1,QPODC1,F
DEF=
C,FINFC,FDUES2,EITN12,LISGT2,EITN22,EITN
32,EITN42,EITN52,ESTORH,TSRDD1,FSSB=
1,FSSB2,FSSB3,FSSM1,FSSM2,
SDRCD,GORTP1,EORNO1,LORDS,ISHRR1,EITM16,
EITM26,EITM36,EITM46,EITM56,ESTOR2,=
ITMRR6,LISGS6,UOMSQ,
& #91;=A3UPRC2],CUPRC2,UOMPC2,FPOVR,GSQDC2
,RQDIS1,GSTDC2,RTDIS1,[=A3TOVL]
,CTOVL,
& #91;=A3TOVI],CTOVI,FSSDN1,FSTXT1,FSILP,E
OROR1,EITM1B,EITM2B,EITM3B,EITM4B,
'+
'EITM5B,[@.SRDD1],
& #91;@.SODD],EVATCB,GCNRC2,SASPO1,SASWO1,E
SRPP1,ESRPS1,ISFTR1,FSRFD,TSRDD2,FS
DB1=
,FSDB2,FSDB3,FSDM1,FSDM2,FOATT,FWOAT,ELO
TNK,ELOTSK,GPLIDA,EOAIDA,ELAYN1,ITE=
M,DITMD,UOMTU,MUDN3,QGWGT
FROM Openquery(BS01BAA, '+
'''SELECT * FROM GML01A.YSBTCHP_LINK TM '+
'INNER JOIN BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO =3D T1.EORNO '+
'INNER JOIN BS01BAA.BSSODSP T2 ON T1.EORNO =3D T2.EORNO8 '+
'INNER JOIN BS01BAA.BSSODRP T3 ON T2.EORNO8 =3D T3.EORNO1 AND
t2.LORDS4 =3D T3.LORDS '+
'INNER JOIN GML01A.GLOAD_ITEM T4 ON T2.EITN12 =3D T4.ITEM '+
'WHERE TM.BATCH_NUMBER =3D ' + @.BATCHNO + ''') ORDER BY EORNO, LORDS4'
PRINT @.TSQL
EXEC (@.TSQL)
/*
----=
---
End the Line output SQL
----=
---
*/
/*
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Here we grab the order text table to output ready for later when we
grab that data and put it
onto the paperwork
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
-Old sql
SET @.TSQL =3D 'INSERT INTO [gload].[dbo].[tbl_OText] (BATCH_NO
,
EORNO3, LORDS1, FTEXT, FTXPC, DTEXT1, ISNTR) '+
'SELECT BATCH_NUMBER,EORNO3,LORDS1,FTEXT,FTXPC,D
TEXT1,ISNTR 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 =3D T1.EORNO3 '+
'WHERE TM.BATCH_NUMBER =3D ' + @.BATCHNO + ''') ORDER BY EORNO3,
LORDS1'
*/
SET @.TSQL =3D 'INSERT INTO [gload].[dbo].[tbl_OText] (BATCH_NO
,
EORNO3, LORDS1, FTEXT, FTXPC, DTEXT1, ISNTR) '+
'SELECT BATCH_NUMBER,EORNO3,LORDS1,FTEXT,FTXPC,D
TEXT1,ISNTR 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 =3D T1.EORNO3 '+
'WHERE TM.BATCH_NUMBER =3D ' + @.BATCHNO + ''') ORDER BY EORNO3,
LORDS1'
EXEC (@.TSQL)
SET @.TSQL =3D 'INSERT INTO [gload].[dbo].[tbl_CText] (IACCK, E
DTNP4,
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 =3D T1.EORNO '+
'INNER JOIN BS01BAA.BCTEXTP T2 ON T1.ECSTN9 =3D T2.IACCK AND T1.EDTNO5
=3D EDTNP4 '+
'WHERE TM.BATCH_NUMBER =3D ' + @.BATCHNO + ''') ORDER BY INTRR3'
EXEC (@.TSQL)
/*
----=
---
End the Order text output SQL
----=
---
*/
/*
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
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!
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
*/
SET @.TSQL =3D 'INSERT INTO Openquery(GMP01A, '+
'''SELECT DONE_BTCH FROM GMP01A.YSBTCHP_DONE WHERE 1=3D0 '+
''') VALUES ('+@.BATCHNO+')'
EXEC (@.TSQL)
/*
----=
---
End the header output SQL
----=
---
*/
SET @.TSQL =3D 'SELECT * FROM Openquery(BS01BAA, '+
'''SELECT * FROM GML01A.YSBTCHP_LINK TM '+
'INNER JOIN BS01BAA.BSSOHRP T1 ON TM.BATCH_OI_NO =3D T1.EORNO '+
'INNER JOIN BS01BAA.BSSODSP T2 ON T1.EORNO =3D T2.EORNO8 '+
'INNER JOIN BS01BAA.BSSODRP T3 ON T2.EORNO8 =3D T3.EORNO1 AND
t2.LORDS4 =3D T3.LORDS '+
'WHERE TM.BATCH_NUMBER =3D ' + @.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.googlegroups.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 ... FRO
M
> 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
>sql