Syntax error trying to use a variable in my query on a remote server

Question

I want to query MySql database in MSSQL using linked server however I keep getting this error: Msg 102, Level 15, State 1, Procedure uspGetTimeLog, Line 16 Incorrect syntax near '+'.

Here is the sql code below

 SELECT * FROM  OPENQUERY([MYSQLCONN], 
        'SELECT e_id, TDate, Entry, `Exit` FROM timemgt.daymaster 
        WHERE TDate >= ''''' + @frmDate + ''''' ')

This is working

SELECT * FROM  OPENQUERY([MYSQLCONN], 
    'SELECT e_id, TDate, TIME_FORMAT(Entry, ''%T'') AS ''Entry'', 
    TIME_FORMAT(`Exit`, ''%T'') AS ''Exit'' FROM timemgt.daymaster 
    WHERE TDate >= ''2017-01-01'' AND TDate <= ''2017-01-01''')

This is not working

DECLARE @frmDate VARCHAR(10)
DECLARE @toDate VARCHAR(10)
SET @frmDate = '2017-01-01'
SET @toDate = '2017-01-01'

SELECT * FROM  OPENQUERY([MYSQLCONN], 
    'SELECT e_id, TDate, TIME_FORMAT(Entry, ''%T'') AS ''Entry'', 
    TIME_FORMAT(`Exit`, ''%T'') AS ''Exit'' FROM timemgt.daymaster 
    WHERE TDate >=''' + @frmDate + ''' AND TDate <= '''+ @toDate +'''')

Error am getting

Msg 102, Level 15, State 1, Line 9 Incorrect syntax near '+'.

There after I will pass it to a temporary table

5 single quotes before the + and after the next one - that seems a strange amount. — Andrew, Jan 15 '18 at 16:41
what do you mean with ''''' (5 single quotes) ???? explain your goal — ScaisEdge, Jan 15 '18 at 16:50
Basically I want to create a stored procedure in MSSQL that will take a parameter @frmDate and will make use of that parameter in MYSQL query. — Tund Dunt, Jan 15 '18 at 17:53

score 0 · Accepted Answer · answered Jan 15 '18 at 18:03

I do similar things, but not with MySQL. However, I suspect it will work with MySQL as well.

Instead of this:

WHERE TDate >= ''''' + @frmDate + '''''

you want something like this:

WHERE TDate >= ' + @frmDateString + '

where @frmDateString is a string in this format.

{ts 'yyyy-mm-dd HH:mm:ss'}

You will have to experiment with the number of quotes. I also do things a bit different than you. My technique is:

declare @sql as nvarchar(max);
set @sql = 'select * from openquery(
SERVERNAME,
''
select etc
where SomeDateField > = ' + @dateString + '
etc
''
)
';

exec sp_executesql @sql;

The significance is that my method requires more single quotes.

Also significant is that I use a scalar function to convert my dates to the properly formatted strings.

Edit starts here

I read the comment about the stored procedure after posting this answer. You will want to use my method.

Syntax error trying to use a variable in my query on a remote server

1 Answers1