Linked server OPENQUERY, error 'The name <> is not a valid identif

Hi,
I am working on exporting data from a remote MSSQL db using la inked server.
Both SQL server 2000.
So far I have a SELECT against a linked server with a hard coded value in
the WHERE clause and it works fine:
SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')
I try to put this exact query into a variable and run it:
declare @.tsql varchar(8000)
SELECT @.tsql='SELECT * FROM OPENQUERY(SERVER_NAME,''SELECT id,[date]FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688'')'
EXEC @.tsql
I get an error:
The name 'SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date]FROM
DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')' is not a valid identifie
r.
The query specified in the error runs just fine on its own (see my first
step).
I searched the web and tech groups for answers but can't find any
information on this error. Please help.
Thank you, A"Alexis" <Alexis@.discussions.microsoft.com> wrote in message
news:539D21E3-6961-49BD-9892-279744A3A5C9@.microsoft.com...
> EXEC @.tsql
Add some parens:
EXEC (@.tsql)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Can you try using:
...
exec (@.tsql)
AMB
"Alexis" wrote:

> Hi,
> I am working on exporting data from a remote MSSQL db using la inked serve
r.
> Both SQL server 2000.
> So far I have a SELECT against a linked server with a hard coded value in
> the WHERE clause and it works fine:
> SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')
> I try to put this exact query into a variable and run it:
> declare @.tsql varchar(8000)
> SELECT @.tsql='SELECT * FROM OPENQUERY(SERVER_NAME,''SELECT id,[date]FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688'')'
> EXEC @.tsql
> I get an error:
> The name 'SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date]FROM
> DB_NAME.dbo.TABLE_NAME WHERE id= 111204892204688')' is not a valid identif
ier.
> The query specified in the error runs just fine on its own (see my first
> step).
> I searched the web and tech groups for answers but can't find any
> information on this error. Please help.
> Thank you, A
>

Linked server OPENQUERY, error 'The name <> is not a valid ide

Adam,
Thank you for replying to my first message.
Now, can I pass a variable of type integer into that dynamic SELECT
statement? I know it works with character variables, but I really need to
pass in a numeric field. Or do I have to resort to conversions?
declare @.id int
select @.id = 1
SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
DB_NAME.dbo.TABLE_NAME WHERE server=' + @.id + ')'
I get Incorrect syntax near '+'.
Thank you.
"Adam Machanic" wrote:

> "Alexis" <Alexis@.discussions.microsoft.com> wrote in message
> news:539D21E3-6961-49BD-9892-279744A3A5C9@.microsoft.com...
> Add some parens:
> EXEC (@.tsql)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>You'll have to build the string dynamically -- you can't pass any kind of
variable into OPENQUERY.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Alexis" <Alexis@.discussions.microsoft.com> wrote in message
news:9B3439B9-7AF2-4B81-A7A3-64BF5A5CAD8A@.microsoft.com...
> Adam,
> Thank you for replying to my first message.
> Now, can I pass a variable of type integer into that dynamic SELECT
> statement? I know it works with character variables, but I really need to
> pass in a numeric field. Or do I have to resort to conversions?
> declare @.id int
> select @.id = 1
> SELECT * FROM OPENQUERY(SERVER_NAME,'SELECT id,[date] FROM
> DB_NAME.dbo.TABLE_NAME WHERE server=' + @.id + ')'
> I get Incorrect syntax near '+'.
> Thank you.
>