NEED HELP Solving error 7321 - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

NEED HELP Solving error 7321

Author

Topic

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-05 : 22:58:48

-
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-05 : 23:06:31

you can't pass in a variable to the query part of OPENQUERY().

form the query in a string and then pass in to OPENQUERY()


declare @query nvarchar(max)
select @query = 'SELECT TERMINAL_ID,CNT ... WHERE TERMINAL_ID = ' + @terminal
SELECT * FROM OPENQUERY(ServerLinkEODWMain, @query)

KH
[spoiler]Time is always against us[/spoiler]

somenoob
Posting Yak Master

112 Posts

Posted - 2011-09-05 : 23:13:03

Thanks for the fast respone. Really needed that.

but now after editing the codes you provided, it gave me incorrect syntax near '@query'. (microsoft SQL Server, Error: 102)

Please Help. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-06 : 01:56:52

remove the single quote around @query

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-06 : 02:11:27

oh seems like OPENQUERY can't accept that. Looks like got to use Dynamic SQL

try this


declare @sql nvarchar(max)
select @sql = 'SELECT * FROM OPENQUERY(ServerLinkEODWMain, ''' + @query + ''')'
print @sql
exec (@sql)

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-06 : 02:43:48

what is the data type for @terminal ?

convert to varchar and concatenate


WHERE TERMINAL_ID = ' + convert(varchar(10), @terminal)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources