If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Passing variables into an OPENQUERY

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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???
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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???
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On