OpenQuery and passing variables

[douglascfast]

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

[Erland Sommarskog]

(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

[douglascfast]

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

[Erland Sommarskog]

(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