Problems with dynamic string in Openquery

Question

Post reply

Problems with dynamic string in Openquery

joey.burgett

Valued Member

Points: 66
More actions
October 8, 2008 at 2:30 pm

#193991

use [TSQLTestServer]
SELECT * INTO ##g_temp
FROM
OPENQUERY(LocalSQL,
'
SELECT * FROM [Schema].[TableName] WHERE ModifiedDateTime > ''Oct 8 2008 12:01PM''
EXCEPT
SELECT * FROM [LinkedServer].[DBName].[Schema].[TableName] WHERE ModifiedDateTime > ''Oct 7 2008 12:01PM''
')
DROP TABLE ##g_temp
The above works fine in the analyzer But when I use the same line in a stored procedure, it says incorrect syntax near 'Oct'. I'm building the dynamic string as follows:
DECLARE @LinkedNameServer varchar(500)
DECLARE @Schema varchar(100)
SET @Schema = 'Schema'
DECLARE @TableName varchar(100)
SET @TableName = 'TableName'
DECLARE @LastSyncDate datetime
SET @LastSyncDate = CAST('Oct 8 2008 12:01PM' AS DATETIME)
SET @LinkedNameServer =
'SELECT * FROM [' + @Schema + '].[' + @TableName + ']
WHERE [' + @Schema + '].[' + @TableName + '].[ModifiedDateTime] > ''' + CAST(@LastSyncDate AS DATETIME) + '''
EXCEPT
SELECT * FROM [LinkedSQLName].[DBName].[' + @Schema + '].[' + @TableName + ']
WHERE [LinkedSQLName].[DBName].[' + @Schema + '].[' + @TableName + '].[ModifiedDateTime] > ''' + CAST(@LastSyncDate AS DATETIME) + ''' '
DECLARE @SRVLinkName varchar(100)
SET @SRVLinkName = 'LocalSQL'
DECLARE @CMD varchar(500)
SET @CMD = ('SELECT * INTO ##g_temp FROM OPENQUERY(' + @SRVLinkName + ',''' + @LinkedNameServer + ''')')
EXEC(@CMD)
I'm sure it's something simple I'm just not seeing and thought I'd post it here for another fresh set of eyes! 🙂
Joey Burgett

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33508 More actions · Answer 1

Try changing the notation of the date.

Instead of

CAST('Oct 8 2008 12:01PM' AS DATETIME)

use CAST('20081008 12:01PM' AS DATETIME)

You should also check if the local settings of the machine are the same.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Sakthivel Chidambaram SSCertifiable Points: 5531 More actions · Answer 2

Hi,

Sometimes you may need to add two single quotes when using strings in OPENQUERY... just recollect that..

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Hi Joey

It's good practice to print the string you're going to execute. Try this, there are a couple of minor changes:

[font="Courier New"]DECLARE @LinkedNameServer VARCHAR(500)

DECLARE @Schema VARCHAR(100)

SET @Schema = 'Schema'

DECLARE @TableName VARCHAR(100)

SET @TableName = 'TableName'

DECLARE @LastSyncDate DATETIME, @cLastSyncDate VARCHAR (20)

SET @LastSyncDate = DATEADD(dd,DATEDIFF(dd, 0, GETDATE()), 0) -- today, with no time component

SET @cLastSyncDate = CAST(@LastSyncDate AS VARCHAR)

SET @LinkedNameServer =

'SELECT * FROM [' + @Schema + '].[' + @TableName + ']

WHERE [' + @Schema + '].[' + @TableName + '].[ModifiedDateTime] > ''' + @cLastSyncDate + '''

EXCEPT

SELECT * FROM [LinkedSQLName].[DBName].[' + @Schema + '].[' + @TableName + ']

WHERE [LinkedSQLName].[DBName].[' + @Schema + '].[' + @TableName + '].[ModifiedDateTime] > ''' + @cLastSyncDate + ''' '

DECLARE @SRVLinkName VARCHAR(100)

SET @SRVLinkName = 'LocalSQL'

DECLARE @CMD VARCHAR(500)

SET @CMD = ('SELECT * INTO ##g_temp FROM OPENQUERY(' + @SRVLinkName + ',''' + @LinkedNameServer + ''')')

PRINT @CMD

--EXEC(@CMD)

[/font]

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

joey.burgett Valued Member Points: 66 More actions · Answer 4

Thanks to you all for responding.

I've tried the suggestions and upon further testing, it appears that using ANY quoted value inside the dynamically generated statement fails.

I tried it against a numeric column where no quotes are required and the dynamic string executes.

Grrr

Joey Burgett

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

I'm sorry Joey but that simply isn't correct. Here's part of the WHERE clause from a very similar situation to yours, OPENROWSET with dynamic SQL:

AND CREATE_DTTM >= ''''' + @sDateRangeStart + '''''

AND CREATE_DTTM < ''''' + @sDateRangeEnd + ''''' '')'

It works just fine. Data gets pulled from 32 servers into my reporting server.

You need to experiment a little more, and always always print the string before attempting to execute.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 6

joey.burgett (10/9/2008)
Thanks to you all for responding.
I've tried the suggestions and upon further testing, it appears that using ANY quoted value inside the dynamically generated statement fails.
I tried it against a numeric column where no quotes are required and the dynamic string executes.
Grrr
Joey Burgett

As Sakthivel already wrote, you simply need to replace the single quote with 2 singles qoutes when you are creating a dynamic SQL statement.

--------------------------------------------------------------
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/