This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(9.6, 33%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDR%3C/text%3E%3C/svg%3E)
Query
Declare @Sql_Query nvarchar(max)
set @Sql_Query =('select Name,Age,Year,DateofBirth,City,Correction,Time,Id,No From Student.dbo.TestResult where Name=ABCD and Age=21');
set @Sql_Query ='select * from Openquery ([SESDATA],'+@SQL_QUERY+')'
exec sp_executesql @Sql_Query
error
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
I am trying to fetch data from Linked Server (SESDATA) through open query above mentioned error has come
Try adjusting one of the lines:
set @Sql_Query ='select * from Openquery ([SESDATA], ''' + @SQL_QUERY + ''')'
Maybe the real query need some more “'” if parameterised statements cannot be used.
Thank you for your reply..
As per comments, we have changed the query..Then execute
The following error has come
OLE DB provider "SQLNCLI11" for linked server "SESDATA" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 207, Level 16, State 1, Line 1
Invalid column name '21'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ABCD'.
Probably you must write where Name=''''ABCD'''' and.
After changing the following error has come
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'select'.
Msg 105, Level 15, State 1, Line 3
Unclosed quotation mark after the character string '
exec sp_executesql @Sql_Query
Thank you for your support..Now it is working..
I have one doubt, In our query, name data is fetched from local server table. how to pass name through variables
Below query is correct ?
Ex : declare @TestName varchar(5)
select @TestName=Report.Name (\\Report is in local server table)
set @Sql_Query =('select Name,Age,Year,DateofBirth,City,Correction,Time,Id,No From Student.dbo.TestResult where Name=''''+@TestName'''' and Age=21');
Try something like this:
set @Sql_Query = 'select Name,Age,Year,DateofBirth,City,Correction,Time,Id,No From Student.dbo.TestResult where Name=' + quotename(@TestName, '''') + ' and Age=21'
set @Sql_Query ='select * from Openquery ([SESDATA], ' + quotename(@SQL_QUERY, '''') + ')'
print @Sql_Query -- print the query to check and execute manually
The following error has come while executing the query
select @TestName=Report.Name
Msg 4104, Level 16, State 1, Procedure data_change1, Line 32
The multi-part identifier "Report.Name" could not be bound.
while Directly pass value, it will give output related to selected name=ABCD & Age=21
But indirectly pass value through variable ,that time no output come
set @Sql_Query = 'select Name,Age,Year,DateofBirth,City,Correction,Time,Id,No From Student.dbo.TestResult where Name=' + quotename(@TestName, '''') + ' and Age=21'
set @Sql_Query ='select * from Openquery ([SESDATA], ' + quotename(@SQL_QUERY, '''') + ')'
exec sp_executesql @Sql_Query
Output: commands completed successfully & no output come
If it works in case of select @TestName='ABCD', but does not work in case of select @TestName=Report.Name, then probably Report.Name is not 'ABCD'.