Including parameters in OPENQUERY

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Including parameters in OPENQUERY

Author

Topic

Son Volt
Starting Member

4 Posts

Posted - 2005-12-15 : 12:22:59

I'm trying to setup a stored procedure in SQL Server 2000 to execute a query on an Oracle database. The linked server is setup fine but I'm having problems getting the parameter included in my query correctly. I execute the sp with

exec spMark_DNU_On_Downtime '52'

This line WHERE wc_num = '' + @wcID + '' of my OPENQUERY is causing all the headaches. I keep getting "invalid number" returned from Oracle. I'm not sure if I'm escaping the quotes correctly or what. Any thoughts?


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




-- =============================================
-- Author:		John Robinson
-- Create date: December 14, 2005
-- Description: Set all equipment calibration records to DNU if
--              the mill is considered down for that shift - 
--              A mill is considered down if the tonnage is less
--              than 180,000 lbs in a shift.
-- =============================================
ALTER PROCEDURE [dbo].[spMark_DNU_On_Downtime]
	-- Add the parameters for the stored procedure here

	@wcID char
	
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 cid FROM  CML2Archive.CML2Tandem.dbo.GagTrends
	--WHERE cid = '15159'
	select * from OPENQUERY(MIPS, 
	'SELECT COUNT(coil_num) AS coilCnt,
			SUM(produced_wgt) AS weight,
			SUM(produced_length) AS length,
			SUM(bonus_base_pct) AS bonus
	 FROM MIPS.coil_dtl 
	 WHERE wc_num = '' + @wcID + ''
	 AND coil_num = coil_num_at_wc
	 AND to_char(produced_date_time, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-12-15 07:00:00''
	 AND to_char(produced_date_time, ''YYYY-MM-DD HH24:MI:SS'') < ''2005-12-15 19:00:00''')

 

END
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON

TimS
Posting Yak Master

198 Posts

Posted - 2005-12-15 : 16:59:14

Try this

select * from OPENQUERY(MIPS,
'SELECT COUNT(coil_num) AS coilCnt,
SUM(produced_wgt) AS weight,
SUM(produced_length) AS length,
SUM(bonus_base_pct) AS bonus
FROM MIPS.coil_dtl
WHERE wc_num = ''' + @wcID + '''
AND coil_num = coil_num_at_wc
AND to_char(produced_date_time, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-12-15 07:00:00''
AND to_char(produced_date_time, ''YYYY-MM-DD HH24:MI:SS'') < ''2005-12-15 19:00:00''')

TimS
Posting Yak Master

198 Posts

Posted - 2005-12-15 : 17:17:15

Tested below code

declare @wcID char(4)
declare @sql_str nvarchar (4000)

SET @wcID = '1'

SET @sql_str =
'SELECT COUNT(coil_num) AS coilCnt,
SUM(produced_wgt) AS weight,
SUM(produced_length) AS length,
SUM(bonus_base_pct) AS bonus
FROM MIPS.coil_dtl
WHERE wc_num = ''' + @wcID + '''
AND coil_num = coil_num_at_wc
AND to_char(produced_date_time, ''YYYY-MM-DD HH24:MI:SS'') > ''2005-12-15 07:00:00''
AND to_char(produced_date_time, ''YYYY-MM-DD HH24:MI:SS'') < ''2005-12-15 19:00:00'''

SET @sql_str = N'select * from OPENQUERY(MIPS, ''' + REPLACE(@sql_str, '''', '''''') + ''')'

PRINT @sql_str

EXEC (@sql_str)

Son Volt
Starting Member

4 Posts

Posted - 2005-12-15 : 17:38:12

Thanks! It worked perfectly... Is there an easy explanation why it was crapping out on me?

TimS
Posting Yak Master

198 Posts

Posted - 2005-12-15 : 18:28:17

The OPENQUERY does not like string operations ( + ) or even string variables much.

I have found it works the way I showed above best for me; I assume there is a better way but not yet found one.

Tim S

parkstech
Starting Member

5 Posts

Posted - 2006-01-06 : 11:02:26

Unfortunately I have to disagree with what you have above. I am doing the same thing and it does not work.

DECLARE @db2 TABLE
(
[BLNO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RELCOM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

DECLARE @bill AS varchar(50)
DECLARE ax CURSOR FOR
SELECT BLNO FROM [dbo].[stg_DwRkemBlno]

OPEN ax

FETCH NEXT FROM ax
INTO @bill

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO @db2
SELECT BLNO, RELCOM
FROM (SELECT BLNO, RELCOM
FROM OPENQUERY(NAMDWH_GOV, 'SELECT BLNO, RELCOM FROM DB2ADMIN.DW_RKEM_BLNO_RELCOM_TBL WHERE RELCOM = 1
AND BLNO = ''' + @bill + ''' ') Rowset_1) DERIVEDTBL

FETCH NEXT FROM ax
INTO @bill
END

CLOSE ax
DEALLOCATE ax

SELECT * FROM @db2

GO

What is it I am missing here?

parkstech
Starting Member

5 Posts

Posted - 2006-01-06 : 11:33:31

Hello? Buehler? We have tried every rendition of what you have shown here and even removed the cursor statement, adjust for our table and we are finding that this is wrong in so many different ways. I am not trying to be uncooperative in the least but at least provide something in your example that works

parkstech
Starting Member

5 Posts

Posted - 2006-01-06 : 13:31:24

Here is what we have now. Keep in mind that the cursor loses the @db2 table declaration.

DECLARE @bill varchar(50)
DECLARE @sql_str nvarchar(4000)
DECLARE @ins_str varchar(4000)

DECLARE ax CURSOR FOR
SELECT BLNO FROM [dbo].[stg_DwRkemBlno]

DECLARE @db2 TABLE
(
[BLNO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RELCOM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

OPEN ax

FETCH NEXT FROM ax
INTO @bill

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql_str = 'SELECT BLNO, RELCOM FROM DB2ADMIN.DW_RKEM_BLNO_RELCOM_TBL WHERE RELCOM = 1 AND BLNO = ''' + @bill + ''''
SET @sql_str = N'SELECT BLNO, RELCOM FROM OPENQUERY(NAMDWH_GOV, ''' + REPLACE(@sql_str, '''', '''''') + ''')'

SET @ins_str = 'INSERT INTO @db2 '
EXEC(@ins_str+@sql_str)

FETCH NEXT FROM ax
INTO @bill
END

CLOSE ax
DEALLOCATE ax

SELECT * FROM @db2

GO

parkstech
Starting Member

5 Posts

Posted - 2006-01-06 : 13:39:29

Disregard everything please, we will find the answer ourselves. Your site was recommended by someone here at our company but it appears they were misled into believing that this was a viable source of help.

TimS
Posting Yak Master

198 Posts

Posted - 2006-01-06 : 19:13:28

NOTE: While I think you are out of line; I will point out your major mistake.

Table Varibles can NOT be used to get result of stored procedures because of scoping issues.

Try using a temp table!

CREATE TABLE #db2
(
[BLNO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RELCOM] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)

Edit: see this thread also http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59444

PS: In group like this it is always better to create a new thread and not hijack another.
People tend to look for threads without a reply to them.

And insults do NOT make us want to answer peoples question.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-01-06 : 20:50:46

quote:
Originally posted by parkstech

Disregard everything please, we will find the answer ourselves. Your site was recommended by someone here at our company but it appears they were misled into believing that this was a viable source of help.

you have a lot to learn my friend. I think i'll make sure to ignore you in the future as well...

-ec

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-01-07 : 00:19:41

Such a shame we are doomed to miss out on the many truly boneheaded posts with which you surely would have supplied us.

It is truly our loss.

What shall we all do with our time now?

Kristen
Test

22859 Posts

Posted - 2006-01-07 : 06:19:17

blindman : I'm not sure I could cope with that much entertainment ... :)

Kristen

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-01-07 : 08:02:24

Hehehe

Parkstech, I am very sorry that you don't understand SQL very well, and you aren't patient enough to wait for a response during a quiet time of the day / week. You should work on those.

In the meantime, I once heard a story that went something like this :

quote:

I read a while ago about a farmer who
was, for whatever reason, taking his leisure leaning on his gatepost when a
car with a couple in it pulled up and asked for directions to the next town.
Once they'd got the directions, they asked how friendly the people in the
next town were. The farmer asked them how they found the last town in that
respect and it turned out that the reason they were asking was that the
people in the last town were rude, unhelpful and generally horrible. The
farmer shook his head sadly, informed them that the people in the next town
were just as bad and sent them on their way. A bit later, another car pulled
up and asked the same questions (one of those days, I suppose). Again the
farmer asked them what they thought of the people in the last town and their
faces lit up. Oh, the people in the last town were lovely. Helpful,
courteous and remarkably obliging. The farmer smiled and told them that the
people in the next town were every bit as nice.

I hope you find something in that to help you along in your miserable existence.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

jhermiz

3564 Posts

Posted - 2006-01-09 : 17:18:47

Maybe he was pressured on time...get it done or be fired!

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-01-09 : 19:25:29

No excuse for being rude.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

PennIT
Starting Member

1 Post

Posted - 2007-06-26 : 10:24:41

I know i'm coming in late on this but i needed some answers and found this resource. The solution provided works fine and i appreciate all your help

jwalantsoneji
Starting Member

2 Posts

Posted - 2007-11-08 : 04:28:10

And how to use % sign with like key words in openrowset?

------------------------------------------------------------
Jwalant N. Soneji (BE IT) India
------------------------------------------------------------
Mobile: +91 9869115916
http://profiles.yahoo.com/jnsoneji
http://space.msn.com/jnsoneji
------------------------------------------------------------

Kristen
Test

22859 Posts

Posted - 2007-11-08 : 14:00:51

I suggest you start a new thread, and post then code you are trying to use

Kristen

geebake
Starting Member

1 Post

Posted - 2007-12-12 : 18:15:01

Well, I found this really late, but it's solved my problem instantly. Thank you guys soooo much.

Greg

paul.cox
Starting Member

1 Post

Posted - 2008-02-12 : 11:08:54

Thanks for the nice information. This was exactly the answer I needed to avoid what could have been a big problem.