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(douglascfast@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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:
> (douglascfast@.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, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(douglascfast@.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, esquel@.sommarskog.se

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

Open the link in new window

When i giving a url passing some parameter..in Jump to Url of cell navigation properties..

In VS Studio its opening in new window ..but when deployed in server and its opening same browser while my requirement is to open in new window..

You need to set the LinkTarget deviceinfo to "_blank". This can be set using a url parameter or in the rsreportserver.config file.

See this link for more information on setting the device info:

http://msdn2.microsoft.com/en-us/library/ms155397.aspx

This link has information on the device info settings for the html renderer.

http://msdn2.microsoft.com/en-us/library/ms155395.aspx

|||

I have Used this code in jump to url of navigation text box void(window.open(url))" its working and had satisfied mine requirement..

sql

Open the link in new window

When i giving a url passing some parameter..in Jump to Url of cell navigation properties..

In VS Studio its opening in new window ..but when deployed in server and its opening same browser while my requirement is to open in new window..

You need to set the LinkTarget deviceinfo to "_blank". This can be set using a url parameter or in the rsreportserver.config file.

See this link for more information on setting the device info:

http://msdn2.microsoft.com/en-us/library/ms155397.aspx

This link has information on the device info settings for the html renderer.

http://msdn2.microsoft.com/en-us/library/ms155395.aspx

|||

I have Used this code in jump to url of navigation text box void(window.open(url))" its working and had satisfied mine requirement..