Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Using OpenQuery and Linked Server and passing a Parmr

  1. #1
    Join Date
    Mar 2009
    Posts
    32

    Question 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.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Tried "SELECT * FROM OPENQUERY(AS400SRV_MSDASQL,'Call QGPL.get_eqmqty(?), 3505040')"?

  3. #3
    Join Date
    Mar 2009
    Posts
    32
    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".

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Are you on Windows x86-64 environment?

  5. #5
    Join Date
    Mar 2009
    Posts
    32
    Running x86-32.

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    What's syntax of CALL on as400?

  7. #7
    Join Date
    Mar 2009
    Posts
    32
    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;

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    How do you call the sp on as400?

  9. #9
    Join Date
    Mar 2009
    Posts
    32
    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

  10. #10
    Join Date
    Sep 2002
    Posts
    5,938
    Does it work?

  11. #11
    Join Date
    Mar 2009
    Posts
    32
    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?

  12. #12
    Join Date
    Sep 2002
    Posts
    5,938
    Any network issue?

  13. #13
    Join Date
    Mar 2009
    Posts
    32
    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.

  14. #14
    Join Date
    Sep 2002
    Posts
    5,938
    Saw similar issue, have to restart sql to get linked server work whenever lost communication between sql and non-sql linked server.

  15. #15
    Join Date
    Mar 2009
    Posts
    32
    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
  •