OpenQuery & Quotes Syntax

I'm trying to select records within a date range from a linked server. If I
don't include the WHERE part and it's variables, my sql retrieves all
records. I also can hard code the WHERE criteria like "loadID=500".
SQL returns an error on my below code saying"
Incorrect syntax near '+'
What am I doing wrong with my commas and plus syntax? It looks correct, but
doesn't like my plus syntax for some reason.
CODE ***********
declare @.dtStartDate datetime, @.dtEndDate datetime
set @.dtStartDate = GetDate()
set @.dtEndDate = '20060701'
SELECT * FROM OPENQUERY(myLinkServer, 'SELECT loadID, loadDate
FROM load
WHERE loadDate >= ''' + convert(char(8), @.dtStartDate, 112) +
'''' + ' AND '
+ '' + 'loadDate <= ''' + convert(char(8), @.dtEndDate, 112) +
'''')On my system that string works fine. Using the current date for the
variables I get:
SELECT loadID, loadDate FROM load WHERE loadDate >= '20060515' AND
loadDate <= '20060515'
Are you certain that this is the line with the error?
Try to avoid building strings as parameters like this. Instead, assign the
string to a variable, and pass the variable as the parameter. You can
output the variable in order to make sure it contains the correct string.
You can then execute this string seperately in Query Analyzer to simplify
troubleshooting.
"Scott" <sbailey@.mileslumber.com> wrote in message
news:%23jm8YzCeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> I'm trying to select records within a date range from a linked server. If
I
> don't include the WHERE part and it's variables, my sql retrieves all
> records. I also can hard code the WHERE criteria like "loadID=500".
> SQL returns an error on my below code saying"
> Incorrect syntax near '+'
> What am I doing wrong with my commas and plus syntax? It looks correct,
but
> doesn't like my plus syntax for some reason.
>
> CODE ***********
> declare @.dtStartDate datetime, @.dtEndDate datetime
> set @.dtStartDate = GetDate()
> set @.dtEndDate = '20060701'
> SELECT * FROM OPENQUERY(myLinkServer, 'SELECT loadID, loadDate
> FROM load
> WHERE loadDate >= ''' + convert(char(8), @.dtStartDate, 112)
+
> '''' + ' AND '
> + '' + 'loadDate <= ''' + convert(char(8), @.dtEndDate, 112)
+
> '''')
>|||SELECT * FROM OPENQUERY(myLinkServer, 'SELECT loadID, loadDate
FROM
load
WHERE
loadDate >= convert(char(8), getdate(), 112) +
AND loadDate <= convert(char(8), ''20060701'', 112)')
Could u please try with this?