471,090 Members | 1,403 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

OpenQuery and passing variables

Anyone,

Is this possible?

I am connecting to a TeraData server via MS SQL 8.0 using the OpenQuery
statement. I need to pass a list of ever-changing deal numbers My
list of numbers are stored as a table on MS SQL.

So what I want is this

Select * from OpenQuery(TeraSrvr, "
Select Col1
, Col2
, Col3
From Teradata_Table_1

Where Deal_no in (Select Deal_no from SQLTable)
")

Now I know that wont work, but How can I pass 184 Deal Numbers from my
SQL server to this query before it is sent to the Teradata server to be
done? Do I have to keep re-doing an in statement each month?

Anyone can help?

Doug

Jul 23 '05 #1
3 13138
(do**********@hotmail.com) writes:
So what I want is this

Select * from OpenQuery(TeraSrvr, "
Select Col1
, Col2
, Col3
From Teradata_Table_1

Where Deal_no in (Select Deal_no from SQLTable)
")

Now I know that wont work, but How can I pass 184 Deal Numbers from my
SQL server to this query before it is sent to the Teradata server to be
done? Do I have to keep re-doing an in statement each month?


To do it with OPENQUERY you would have to use dynamic SQL to build
the SQL statement, and also to execute the OPENQUERY thing, as
OPENQUERY does not take parameters of any kind of whatsoever.

But cannot you not use a linked server instead:

SELECT t.*
FROM TeraSrvr.db.catalog.Teradata_Table_1 t
WHERE t.Deal_no in (Select s.Deal_no from SQLTable s)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Thanks for this, I will chekc on the link bit below. Can you help me
with how to do this in Dynamic SQL. I can't find how to get a list of
my numbers into a Var so I can pass them on and use them in the
openquery statement.

Thanks again

Doug

Erland Sommarskog wrote:
(do**********@hotmail.com) writes:
So what I want is this

Select * from OpenQuery(TeraSrvr, "
Select Col1
, Col2
, Col3
From Teradata_Table_1

Where Deal_no in (Select Deal_no from SQLTable)
")

Now I know that wont work, but How can I pass 184 Deal Numbers from my
SQL server to this query before it is sent to the Teradata server to be
done? Do I have to keep re-doing an in statement each month?


To do it with OPENQUERY you would have to use dynamic SQL to build
the SQL statement, and also to execute the OPENQUERY thing, as
OPENQUERY does not take parameters of any kind of whatsoever.

But cannot you not use a linked server instead:

SELECT t.*
FROM TeraSrvr.db.catalog.Teradata_Table_1 t
WHERE t.Deal_no in (Select s.Deal_no from SQLTable s)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp


Jul 23 '05 #3
(do**********@hotmail.com) writes:
Thanks for this, I will chekc on the link bit below. Can you help me
with how to do this in Dynamic SQL. I can't find how to get a list of
my numbers into a Var so I can pass them on and use them in the
openquery statement.


Alas, in SQL 2000 the only safe way is to run a loop over the table.
There are some shortcuts, but they unrely undefined behaviour, so I
advise against such use.

For dynamic SQL in general, I have a longer article on the topic on
my web site: http://www.sommarskog.se/dynamic_sql.html.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Matthew Robinson | last post: by
1 post views Thread by Consuelo Guenther | last post: by
7 posts views Thread by Khai | last post: by
5 posts views Thread by Shawn Northrop | last post: by
6 posts views Thread by coool | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.