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 ')'.

Question

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 ')'.

durai raj s 20

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 ')'.

durai raj s 20 Reputation points

2023-03-18T07:31:46.13+00:00

I am trying to fetch data from Linked Server (SESDATA) through open query above mentioned error has come

Accepted answer

1 additional answer

Your answer

durai raj s 20 Reputation points

2023-03-18T07:31:46.13+00:00

I am trying to fetch data from Linked Server (SESDATA) through open query above mentioned error has come

Answer 1

Viorel 122.6K

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.

durai raj s 20 Reputation points

2023-03-18T08:16:54.4366667+00:00

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'.
Viorel 122.6K Reputation points

2023-03-18T08:36:06.4066667+00:00

Probably you must write where Name=''''ABCD'''' and.
durai raj s 20 Reputation points

2023-03-18T09:22:55.5666667+00:00

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
durai raj s 20 Reputation points

2023-03-18T09:56:39.4266667+00:00
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');

Viorel 122.6K

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

durai raj s 20 Reputation points

2023-03-18T10:42:48.0533333+00:00

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.
durai raj s 20 Reputation points

2023-03-18T11:44:55.18+00:00

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
Viorel 122.6K Reputation points

2023-03-18T13:43:45.0966667+00:00

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'.

Answer 2

Mojgaan Kataanforoush 0

SELECT country , region, city

FROM HR.Employees

ORDER BY country, region, city

union all

SELECT country, region, city

FROM Production.Suppliers

ORDER BY country, region, city;

hello after type this query i have problem and show error by union all.

Incorrect syntax near the keyword 'union'.

Naomi Nosonovsky 8,431 Reputation points

2024-08-21T12:45:52.7666667+00:00
You can only have one ORDER BY in the query, e.g.

SELECT country , region, city FROM HR.Employees union all SELECT country, region, city FROM Production.Suppliers ORDER BY country, region, city;

Share via

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 ')'.

1 additional answer

Your answer