Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2008
    Posts
    5

    Unhappy Passing variables into an OPENQUERY

    Hi all,

    I am very new to all of this so if I ask what appears to be dumb question I apoligize up front.

    I have an Access Front End using a Pass-Through Query to send a variable to a Stored Procedure in MSSQL which is accessing a linked table in a iSeries DB2. Mouthful.

    It works great, but slower than it should be. I am querying the entire table in DB2 rather than passing the variable directly to it.

    Here is what I have.
    Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[sp_Job_Master] 
    	-- Add the parameters for the stored procedure here
    	@Job_No varchar(6)
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    SELECT
    	Job_No AS Job_No, 
    	Cost_Code AS Cost_Code, 
    	Descript AS Descript, 
    	JC_Seg1 AS JC_Seg1, 
    	JC_Seg2 AS JC_Seg2, 
    	JC_Seg3 AS JC_Seg3,
    	Hours_Budget as Hours_Budget,
    	Hours_JTD as Hours_JTD,
    	Hours_Remaining as Hours_Remaining,
    	Budget as Budget,
    	Cost_To_Date as Cost_To_Date,
    	Open_Committed as Open_Committed,
    	Remaining as Remaining,
    	Type as Type,
    	Cost_Type as Cost_Type,
    	Status as Status
    
    FROM OPENQUERY(
    ALPHA, 
    '
    
    	SELECT  
    		tbl_Job_Mstr.CJBNO as Job_No,
    		tbl_Job_Mstr.CFMJC AS Cost_Code,  
    		tbl_Job_Mstr.CD20A AS Descript, 
    		tbl_Job_Mstr.CJCS1 AS JC_Seg1,  
    		tbl_Job_Mstr.CJCS2 AS JC_Seg2, 
    		tbl_Job_Mstr.CJCS3 AS JC_Seg3,  
    		Double(Sum(tbl_Job_Mstr.CBDHR+tbl_Job_Mstr.CCGHR)) AS Hours_Budget,  
    		Double(Sum(tbl_Job_Mstr.CRGHJ+tbl_Job_Mstr.COVHJ+tbl_Job_Mstr.COTHJ)) AS Hours_JTD, 
    		Double(Sum((tbl_Job_Mstr.CBDHR+tbl_Job_Mstr.CCGHR) - (tbl_Job_Mstr.CRGHJ+tbl_Job_Mstr.COVHJ+tbl_Job_Mstr.COTHJ))) AS Hours_Remaining, 
    		Double(Sum(tbl_Job_Mstr.CAMBD+tbl_Job_Mstr.CAMCG)) AS Budget,  
    		Double(Sum(tbl_Job_Mstr.CCJTD)) AS Cost_To_Date,  
    		Double(Sum((tbl_Job_Mstr.CAMPO + tbl_Job_Mstr.CAMRU))) as Open_Committed, 
    		Double(Sum((tbl_Job_Mstr.CAMBD+tbl_Job_Mstr.CAMCG)-tbl_Job_Mstr.CCJTD - (tbl_Job_Mstr.CAMPO + tbl_Job_Mstr.CAMRU))) AS Remaining, 
    		tbl_Job_Mstr.CRCTY AS Type, 
    		tbl_Job_Mstr.CCSTY AS Cost_Type, 
    		tbl_Job_Mstr.CSTAT AS Status 
    	FROM 
    		tbl_Job_Mstr 
    	Where 
    		( 
    		(tbl_Job_Mstr.CCONO=1) 
    		AND 
    		(Left(tbl_Job_Mstr.CJBNO,1)<>''X'') 
    		AND 
    		(Length(trim(tbl_Job_Mstr.CJBNO))>5) 
    		) 
    	Group By 
    		tbl_Job_Mstr.CCONO, 
    		tbl_Job_Mstr.CDVNO, 
    		tbl_Job_Mstr.CJBNO, 
    		tbl_Job_Mstr.CFMJC,  
    		tbl_Job_Mstr.CD20A, 
    		tbl_Job_Mstr.CJCS1,  
    		tbl_Job_Mstr.CJCS2, 
    		tbl_Job_Mstr.CJCS3, 
    		tbl_Job_Mstr.CRCTY, 
    		tbl_Job_Mstr.CCSTY,  
    		tbl_Job_Mstr.CSTAT 
    	ORDER BY  
    		tbl_Job_Mstr.CCONO, 
    		tbl_Job_Mstr.CDVNO, 
    		tbl_Job_Mstr.CJBNO, 
    		tbl_Job_Mstr.CRCTY, 
    		tbl_Job_Mstr.CCSTY, 
    		tbl_Job_Mstr.CFMJC 
    
    	FOR READ ONLY
    
    '
    )
    
    Job_Master
    
    	Where
    		(
    		(Job_No = @Job_No)
    		)
    
    END
    Rather that have the variable at the end in a seperate Where statement I would like it as part of the query I send to the iSeries. I think that this means it has to be part of three/four seperate variables.

    Variable_1 = First Part of query
    Variable_2 = @Job_No
    Variable_3 = The rest of the query

    But no matter what I do I cannot get it to work.

    P.S. The DB2 SQL statement it should look like this.
    Code:
    	Where 
    		( 
    		(tbl_Job_Mstr.CCONO=1) 
    		AND 
    		(Left(tbl_Job_Mstr.CJBNO,1)<>''X'') 
    		AND 
    		(Length(trim(tbl_Job_Mstr.CJBNO))>5) 
    		AND 
    		(tbl_Job_Mstr.CJBNO = ''M01234'') 
    		)
    I apoligize again for the long post and what may seem simple.

    Thank you again.
    Last edited by kenln; 10-08-08 at 13:07.

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb One method...

    Is to change the pass-through on the fly:

    Once your form? has the required parameters, call a module which changes the pass through:

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef

    Set db = CurrentDb
    Set qd = db.QueryDefs("qryNameOfYourExistingPassThrough")

    Dim strSQL As String

    strSQL = "SQL OF YOUR EXISTING STRING...WHERE stringparameterfieldnameexample = '@strParameter@' AND numericalparameterfieldnameexample = @intParemeter@...ORDER BY etc."

    strSQL = Replace(strSQL, "@strParameter@", strAStringParameter)
    strSQL = Replace(strSQL, "@intParemeter@", intAnIntegerParameter)
    <....For each parameter required..

    qd.SQL = strSQL

    set qd = nothing
    set db = nothing

    Or you could use a stored procedure perhaps - Anyhow give this method a go and see how you get on...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Sep 2008
    Posts
    5
    Actually my passthrough looks like this
    Code:
    Execute sp_Job_Master 'M01234'
    Which I actually do change programmatically depending on the job number, in this case "M01234". So this part is okay and it works as written.

    The problem is, the way the Stored Procedure is written is calls for all the records from the iSeries DB2 then filters it in MSSQL. This is unnecessary bandwidth and processor time.

    It would be more effecient to build the variable as part of the query sent to DB2. That's where I'm stuck.

    I have checked google and microsoft and see examples but cannot get it to work.

    Any ideas???

  4. #4
    Join Date
    Sep 2008
    Posts
    5
    I got this much to work in a SQL statement.

    Two problems
    1) I have no idea how to get this to a Stored Procedure
    2) I still cannot get the Job_No variable to load on execution.

    Code:
    DECLARE @Job_No varchar(6)
    , @OPENQUERY_1 nvarchar(4000)
    , @OPENQUERY_2 nvarchar(4000)
    , @OPENQUERY_3 nvarchar(4000) 
    
    
    set @Job_No = '''''''M06142'''''''
    
    Set @OPENQUERY_1 = 
    '
    SELECT
    	Job_No AS Job_No, 
    	Cost_Code AS Cost_Code, 
    	Descript AS Descript, 
    	JC_Seg1 AS JC_Seg1, 
    	JC_Seg2 AS JC_Seg2, 
    	JC_Seg3 AS JC_Seg3,
    	Hours_Budget as Hours_Budget,
    	Hours_JTD as Hours_JTD,
    	Hours_Remaining as Hours_Remaining,
    	Budget as Budget,
    	Cost_To_Date as Cost_To_Date,
    	Open_Committed as Open_Committed,
    	Remaining as Remaining,
    	Type as Type,
    	Cost_Type as Cost_Type,
    	Status as Status
    
    	FROM OPENQUERY(ALPHA, 
    
    ''
    	SELECT  
    		Job_Master.CJBNO as Job_No,
    		Job_Master.CFMJC AS Cost_Code,  
    		Job_Master.CD20A AS Descript, 
    		Job_Master.CJCS1 AS JC_Seg1,  
    		Job_Master.CJCS2 AS JC_Seg2, 
    		Job_Master.CJCS3 AS JC_Seg3,  
    		Double(Sum(Job_Master.CBDHR+Job_Master.CCGHR)) AS Hours_Budget,  
    		Double(Sum(Job_Master.CRGHJ+Job_Master.COVHJ+Job_Master.COTHJ)) AS Hours_JTD, 
    		Double(Sum((Job_Master.CBDHR+Job_Master.CCGHR) - (Job_Master.CRGHJ+Job_Master.COVHJ+Job_Master.COTHJ))) AS Hours_Remaining, 
    		Double(Sum(Job_Master.CAMBD+Job_Master.CAMCG)) AS Budget,  
    		Double(Sum(Job_Master.CCJTD)) AS Cost_To_Date,  
    		Double(Sum((Job_Master.CAMPO + Job_Master.CAMRU))) as Open_Committed, 
    		Double(Sum((Job_Master.CAMBD+Job_Master.CAMCG)-Job_Master.CCJTD - (Job_Master.CAMPO + Job_Master.CAMRU))) AS Remaining, 
    		Job_Master.CRCTY AS Type, 
    		Job_Master.CCSTY AS Cost_Type, 
    		Job_Master.CSTAT AS Status 
    	FROM 
    		Job_Master AS Job_Master 
    	Where 
    		( 
    		(Job_Master.CCONO=1) 
    		AND 
    		(Left(Job_Master.CJBNO,1)<> ''''X'''') 
    		AND 
    		(Length(trim(Job_Master.CJBNO))>5) 
    		AND 
    		(Job_Master.CJBNO = '
    
    Set @OPENQUERY_2 = '''''M06142'''''
    
    Set @OPENQUERY_3 = 
    
    	') 
    	) 
    
    	Group By 
    		Job_Master.CCONO, 
    		Job_Master.CDVNO, 
    		Job_Master.CJBNO, 
    		Job_Master.CFMJC,  
    		Job_Master.CD20A, 
    		Job_Master.CJCS1,  
    		Job_Master.CJCS2, 
    		Job_Master.CJCS3, 
    		Job_Master.CRCTY, 
    		Job_Master.CCSTY,  
    		Job_Master.CSTAT 
    	ORDER BY  
    		Job_Master.CCONO, 
    		Job_Master.CDVNO, 
    		Job_Master.CJBNO, 
    		Job_Master.CRCTY, 
    		Job_Master.CCSTY, 
    		Job_Master.CFMJC 
    
    	FOR READ ONLY
    
    ''
    ) 
    
    Job_Master
    '
    
    
    EXEC (@OPENQUERY_1 + @OPENQUERY_2 + @OPENQUERY_3)
    This is the part I'd like to change but it doesn't work

    Code:
    Set @OPENQUERY_2 = '''''M06142'''''
    to

    Code:
    Set @OPENQUERY_2 = @Job_No
    So far I am still stuck.

    Any ideas anyone???

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •