Runtime error 713

Dear Gurus,

I have developed a package with VB6 & crystal report10. While installing it in Client machines i get error as "Run time Error 713".
I searched for it but only found that it is " Appication - Defined or Object define error 713"..Help me to solve this asap..

Thanx in Advance,
RevolutionWhen creating the Package did you add all the required dlls?
Also search here for your solution
http://support.businessobjects.com/

Runtime data source error

Hello

I have a defined data source to an oracle server. I've alredy intalled oracle client, and setup my data source to save the user and password. I'm using .NET provider/OracleClient DataProvider Connection. When I click on "Test Connection" Button, SSIS reports SUCCESS. In Connection Manager TAB I created on connection called "OracleServer" from my oracle data source, described above.

In my package, I defined a DataReaderSource task, I specified "OracleServer" as a connection to it. I can preview data and view oracle's columns name..., so it make me think that everything is fine. But when It execute the task it FAIL and notify logon error and that password can't be blank.

Please help, I need read from the oracle server!!

Thank you.

This may depend on the connection manager, the provider and your ProtectionLevel in the package. Check these.

One of them means you loose the password. If you don't know, between you clicking Execute in the designer and the package running, it is saved, and loaded into a separate execution host. The host then runs the package and passes back the debug messages to BIDS/VS to update the UI with pretty colours for you. So even though the password is there in your current BIDS/VS session, it is probably being lost on the save, prior to being loaded into the execution host. If you close the package in BIDS, and re-open do you still have the password? I would guess not, and this is the issue you see when trying to execute it.

You can play with the various settings, but my favoured approach is to set the package ProtectionLevel to DontSaveSensitive. This means no passwords are ever saved, but you then use a Configuration to set them. Going forward every time the package is loaded, by an execution host or the designer or other tool, it will read the configuration and apply the password or any other configuration you may have set. Problem solved! it may seem like hard work, but if you plan on deploying packages this is really got to be good practise.

|||

Hello,

When I close BIDS and reopen it, I can still see the password. However let me tell you that I already find the solution.

The problem was that my oracle server is 10.1.x and my oracle's drivers installed on SQL Server machine was 10.2.x. Only I unistalled these drivers and install 10.1 version and everything works!.

thanks for your advices.

Hernan.

RunningValue question

I have a form that uses some header information (like client ID with name, address, etc) and then uses a table for orders that customer has placed. I now need to print something like a page number (but not in the header or footer). When I try RowNumber, this does not work as the dataset is returning multiple records for the first customer and then the next item in the list (the next customer ID) skips the number of records from the first. So I need the first one to have 1, the second to have 2 and so on. I tries RunningValue, but for each record it starts back at 1. What I need is like a counter.

How would I go about this and thanks for the help.

guy,

what was the scope of your runningvalue expression?

Ham

|||I was using the dataset itself and then I tried the table that holds the orders.|||

Try

RunningValue(MyFieldsCount.value,Count,Nothing)

Is will eliminated the scope and you should then get continously counting rows.

Ham

|||Using this I get an error message about aggregate functions only being allowed to use report items contained in the page header or page footer.|||

Guy,

I thought that you were not trying to place this in the header or footer and that you wanted a way to have a continues count of your rows, maybe I'm missing something, are you trying to get a page count, row count, and some of count?

Ham

|||should read "some other count"

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

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
>