How does one run the following command in the context of the sa account while logged in as themselves (not sa). The "SQL Authentication" user does not have access to master.
EXECUTE master.dbo.xp_sendmail @.recipients = 'hi@.hotmail.ca', @.subject = 'Script fired!', @.message = 'Hi'How does one run the following command in the context of the sa account while logged in as themselves (not sa). The "SQL Authentication" user does not have access to master.
EXECUTE master.dbo.xp_sendmail @.recipients = 'hi@.hotmail.ca', @.subject = 'Script fired!', @.message = 'Hi'
sql2k does not support runas. You have to wait for yukon.
Showing posts with label trigger. Show all posts
Showing posts with label trigger. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
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
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
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
>
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
>
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,BATCH_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,
[@.PFED],ESITE2,SASPO,SASWO,FPIDD1,ESTOR1,EOROR,GSTDC1,GSWDC1,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,GPLID=1,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,GSWDC1,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 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,BATCH_DATE,BATCH_TIME,
SDRCD,GORTP8,EORNO8,LORDS4,
[@.SRDD2],QREQB,QREQC,QPIKB,QPIKC,QDESB,QDESC,QINVB,QINVC,QPODB1,QPODC1,FDEF=C,FINFC,FDUES2,EITN12,LISGT2,EITN22,EITN32,EITN42,EITN52,ESTORH,TSRDD1,FSSB=1,FSSB2,FSSB3,FSSM1,FSSM2,SDRCD_old,GORTP1,EORNO1,LORDS,ISHRR1,EITM16,EITM2=6,EITM36,EITM46,EITM56,ESTOR2,ITMRR6,LISGS6,UOMSQ,
[=A3UPRC2],CUPRC2,UOMPC2,FPOVR,GSQDC2,RQDIS1,GSTDC2,RTDIS1,[=A3TOVL],CTOVL,
[=A3TOVI],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,FWOAT,ELOTNK,ELOTSK,GPLIDA,EOAIDA,ELAYN1,ITE=M,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,QPODB1,QPODC1,FDEF=C,FINFC,FDUES2,EITN12,LISGT2,EITN22,EITN32,EITN42,EITN52,ESTORH,TSRDD1,FSSB=1,FSSB2,FSSB3,FSSM1,FSSM2,
SDRCD,GORTP1,EORNO1,LORDS,ISHRR1,EITM16,EITM26,EITM36,EITM46,EITM56,ESTOR2,=ITMRR6,LISGS6,UOMSQ,
[=A3UPRC2],CUPRC2,UOMPC2,FPOVR,GSQDC2,RQDIS1,GSTDC2,RTDIS1,[=A3TOVL],CTOVL,
[=A3TOVI],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,FWOAT,ELOTNK,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,DTEXT1,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,DTEXT1,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, 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 =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 ... 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
>
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,BATCH_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,
[@.PFED],ESITE2,SASPO,SASWO,FPIDD1,ESTOR1,EOROR,GSTDC1,GSWDC1,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,GPLID=1,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,GSWDC1,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 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,BATCH_DATE,BATCH_TIME,
SDRCD,GORTP8,EORNO8,LORDS4,
[@.SRDD2],QREQB,QREQC,QPIKB,QPIKC,QDESB,QDESC,QINVB,QINVC,QPODB1,QPODC1,FDEF=C,FINFC,FDUES2,EITN12,LISGT2,EITN22,EITN32,EITN42,EITN52,ESTORH,TSRDD1,FSSB=1,FSSB2,FSSB3,FSSM1,FSSM2,SDRCD_old,GORTP1,EORNO1,LORDS,ISHRR1,EITM16,EITM2=6,EITM36,EITM46,EITM56,ESTOR2,ITMRR6,LISGS6,UOMSQ,
[=A3UPRC2],CUPRC2,UOMPC2,FPOVR,GSQDC2,RQDIS1,GSTDC2,RTDIS1,[=A3TOVL],CTOVL,
[=A3TOVI],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,FWOAT,ELOTNK,ELOTSK,GPLIDA,EOAIDA,ELAYN1,ITE=M,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,QPODB1,QPODC1,FDEF=C,FINFC,FDUES2,EITN12,LISGT2,EITN22,EITN32,EITN42,EITN52,ESTORH,TSRDD1,FSSB=1,FSSB2,FSSB3,FSSM1,FSSM2,
SDRCD,GORTP1,EORNO1,LORDS,ISHRR1,EITM16,EITM26,EITM36,EITM46,EITM56,ESTOR2,=ITMRR6,LISGS6,UOMSQ,
[=A3UPRC2],CUPRC2,UOMPC2,FPOVR,GSQDC2,RQDIS1,GSTDC2,RTDIS1,[=A3TOVL],CTOVL,
[=A3TOVI],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,FWOAT,ELOTNK,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,DTEXT1,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,DTEXT1,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, 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 =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 ... 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
>
Tuesday, February 21, 2012
Running SP with a different user
We are using SQL2000. User insert table in one database DB1 and trigger insert the record into another database DB2. In this scenario, is it possible have a trigger in DB1 to execute a stored procedure in DB2 with a different user?
try define trigger with execute as clause
CREATE TRIGGER YourNAme ON Yourtable
WITH EXECUTE AS 'CompanyDomain\SqlUser1'
AFTER INSERT, UPDATE AS.......
--call your procedure here
GO
Running SP with a different user
We are using SQL2000. User insert table in one database DB1 and trigger insert the record into another database DB2. In this scenario, is it possible have a trigger in DB1 to execute a stored procedure in DB2 with a different user?
I think you may use openrowset in the trigger to update the other database. it is not the best practice but it is the only solution I know
OPENROWSET('SQLOLEDB','ServerName';'user';'Password',
'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname')|||
hello,
if you will be doing this most of the times
you might as well use the sp_addlinkserver options "useself" set to false to create a
linked server
Syntax
sp_addlinkedsrvlogin [ @.rmtsrvname = ] 'rmtsrvname'
[ , [ @.useself = ] 'useself' ]
[ , [ @.locallogin = ] 'locallogin' ]
[ , [ @.rmtuser = ] 'rmtuser' ]
[ , [ @.rmtpassword = ] 'rmtpassword' ]
by setting useself to false you can enter the desired username and password
regards
|||
USE DB1
Go
EXEC sp_
Trigger ON INSERT
USE DB2
Go
EXEC sp_
Adamus
|||i wish the new sp for 200 would allow "execute as" feature in 2k5|||If this is in the trigger, is there any way I can run stored procedure with OpenRowSet and make trigger code invisible to the user who is inserting record since the password is there.
Thanks,
Subscribe to:
Posts (Atom)