June 8, 2009 at 2:44 am
Hi,
I have linked server "L1". Im trying to write a query to fetch data from the linked server.
qry: Select empName From Employee where empId = @empId
This need to be embed with openquery.
slect * from openquery(L1, 'Select empName From Employee where empId = + '' @empId'')
Above query is giving parsing errors. It seems to3be related to single quotes.
Can any one suggest wahts the exact query.
Regards,
Gowtam
June 8, 2009 at 3:00 am
I'd use a 4 level naming convention:
slect *
from [LinkedServerName].[Database].[Owner].[Table]
Where [...]
that should do. You can only use the LinkedServerName if you have registered the server.
Hope this helps
Cheers,
Matt
June 8, 2009 at 3:04 am
This is due to a limitation of openquery. When you use openquery function, you can not use variables in it. This means that you can use dynamic SQL that includes the call for the open query in it (see an untested example in the code window. Don't forget to change the DBName and SchemaName to the real names of the database and schema) or use 4 parts naming convention.
declare @sql varchar(max)
declare @EmpID varchar(10)
set @EmpID = 'E12'
set @sql = 'select * from openquery(L1, ''select empName From DBName.SchemaName.Employee where empId = ''''' + @EmpID + ''''''')'
exec(@sql)
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply