-
Using OpenQuery and Linked Server and passing a Parm
I am trying to run a script that calls a proc on db2/400 and cannot seem to get ot to work. Can somenone help please?
Code:
DECLARE @JobNumber1 char(12)
SET @JobNumber1 = ' 3505040'
DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = 'AS400SRV_MSDASQL'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'Call QGPL.get_eqmqty(?)' + ',''' + @JobNumber1 + ')'
EXEC (@OPENQUERY+@TSQL)
The value of my string is:
SELECT * FROM OPENQUERY(AS400SRV_MSDASQL,'Call QGPL.get_eqmqty(?),' 3505040)
The error I get is:
Incorrect syntax near '3505040'.
Thank you.
Last edited by snufse; 04-01-2009 at 09:19 AM.
-
Tried "SELECT * FROM OPENQUERY(AS400SRV_MSDASQL,'Call QGPL.get_eqmqty(?), 3505040')"?
-
Well, I tried to execute:
Code:
SELECT * FROM OPENQUERY(AS400SRV_MSDASQL,'Call QGPL.get_eqmqty(?), 3505040')
Error:
OLE DB provider "MSDASQL" for linked server "AS400SRV_MSDASQL" returned message "[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token , was not valid. Valid tokens: <END-OF-STATEMENT>.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "Call QGPL.get_eqmqty(?), 3505040" for execution against OLE DB provider "MSDASQL" for linked server "AS400SRV_MSDASQL".
-
Are you on Windows x86-64 environment?
-
-
What's syntax of CALL on as400?
-
Proc on the AS400 below:
Code:
create procedure get_eqmqty
(in @jobnumber char(12))
result set 1
language sql
reads sql data
set option datfmt = *iso
begin
declare c1 scroll cursor with return for
select glsub, sum(decimal(glu * .01, 31, 2)), glum
from vgiprddta/f0911li
where glmcu = @jobnumber and
(globj = '63500 ' or globj = '73500 ' or globj = '73510 ') and
(gllt = 'AA' or gllt = 'AU') and
glexa = 'Equipment Distribution'
group by glsub, glum;
open c1;
end;
-
How do you call the sp on as400?
-
Not sure what you mean.
This is how you may call the sp on the AS400 (from sequel 2005)
Code:
Exec ('Call QGPL.get_eqmqty(?)', @JobNumber1) AT AS400SRV_MSDASQL
-
-
Yes , this code works fine. Here is the working code snippet:
Code:
@JobNumber int,
@JobNumber1 char(12)
as
CREATE TABLE #JDE_EqmTable
( jde_cost_code nvarchar(15) unique,
jde_sum_hrs decimal(8,2),
jde_uom char(2)
)
insert into #JDE_EqmTable
Exec ('Call QGPL.get_eqmqty(?)', @JobNumber1) AT AS400SRV_MSDASQL
The reason I'm trying to use an OpenQuery is because the "insert into ... exec" does not always work. From time to time it does not seem to connect to the AS400. Most of the time we can see an entry in the qhst like:
Code:
*SIGNON server job 957502/QUSER/QZSOSIGN processing request for user APPNET
User APPNET from client 10.92.6.44 connected to job 958666/QUSER/QZDASOINIT
and things work fine. Other times it seems the call to the AS400 does not go thru and there are no entries in the qhst. When this happens the vb program gets a command timeout and the proc on the sequel box just keeps running for days (have to restart the server). Any ideas what may cause this to happen?
-
-
I'm not sure, that is what I'm trying to find out. At this point I do not really know where to start, whether to look at the sequel box settings or the AS400.
-
Saw similar issue, have to restart sql to get linked server work whenever lost communication between sql and non-sql linked server.
-
Yes, we have to restart sql every time this happens and it happens several times in a day. Had hoped to find what the cause is.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|