November 9, 2011 at 9:06 am
Hello everyone.
I've been trying to execute an openquery statement where a table is loaded using data from a linked server. The query is successful if the WHERE clause is not used. However, I want to limit the data retrieved via the WHERE clause. After many attempts working with the syntax I have not been successful in achieving a successful execution. The correct usage of quotes (double quotes?) is extremely frustrating. Would anyone care to take a shot at this and maybe provide a few tips. Thanks much in advance.
John
** the code below is using all single quotes.
----------------------------------------------------------------------------
OPEN HOST_Cursor
FETCH NEXT FROM HOST_Cursor INTO @LinkedServer,@port_nbr
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = N'INSERT dbo.SERVER_LEVEL_Access_cmpr
SELECT *
From OPENQUERY ([' + @linkedServer + ',' + @port_nbr + '],
''SELECT
@@servername
,name
,type_desc
,convert(varchar(10),create_date,121)
,convert(varchar(10),getdate(),121)
,null
,null
FROM master.sys.server_principals
WHERE type_desc not in ('SERVER_ROLE' , 'CERTIFICATE_MAPPED_LOGIN') ')
EXEC (@SQL)
FETCH NEXT FROM HOST_Cursor INTO @LinkedServer,@port_nbr
END
CLOSE HOST_Cursor
November 9, 2011 at 9:13 am
it appears this is a case of needing to "embed" the single quotes within a quoted string. Anytime a sql statement is embedded in a "string" all items normally single-quoted require an extra one.
here is an example using two single quotes together based on your query: WHERE type_desc not in (''SERVER_ROLE'' , ''CERTIFICATE_MAPPED_LOGIN'')
try adding the extra single quote to the items in the where clause that are already single-quoted.
does this make sense? I hope I did not make it more confusing.
November 9, 2011 at 9:41 am
thanks for the reply. Adding the single quotes allowed the 'parse' check to be successful but when the code was executed, a syntax error occured.
--
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'SERVER_ROLE'.
Msg 105, Level 15, State 1, Line 14
Unclosed quotation mark after the character string ')'.
November 9, 2011 at 9:44 am
You need quadruple quotes when you're in double dynamic sql.
November 9, 2011 at 9:49 am
applebyte (11/9/2011)
thanks for the reply. Adding the single quotes allowed the 'parse' check to be successful but when the code was executed, a syntax error occured.--
Msg 102, Level 15, State 1, Line 14
Incorrect syntax near 'SERVER_ROLE'.
Msg 105, Level 15, State 1, Line 14
Unclosed quotation mark after the character string ')'.
Yes, i was wondering about that too. You have the select statement embedded too, but do not close the select statement either. Since the SELECT statement is embedded in a string, the single-quoted items in the where have to "double-quoted" twice.
Here is the final version of your SQL. Note the 4 single-quotes on the where clause items.
SET @SQL = N'INSERT dbo.SERVER_LEVEL_Access_cmpr
SELECT *
From OPENQUERY ([' + @linkedServer + ',' + @port_nbr + '],
''SELECT
@@servername
,name
,type_desc
,convert(varchar(10),create_date,121)
,convert(varchar(10),getdate(),121)
,null
,null
FROM master.sys.server_principals
WHERE type_desc not in (''''SERVER_ROLE'''' , ''''CERTIFICATE_MAPPED_LOGIN'''') '') '
Another helpful trick I usually do in these cases is actually PRINT the variable during tests without performing the "EXEC(@SQL)" command. This way you can copy the output and try it to see if all the single-quoted items are terminated correctly with the correct number of single-quotes. It can be very tricky to get them right sometimes.
I hope this helps.
EDIT: Also notice the terminated "SELECT" clause with the additional quotes prior to the end parantheses { '') ' }
November 9, 2011 at 10:22 am
Success! Gentlemen, thanks so much for your assitance. Your responses have been extremely helpful and insightful.
John
November 18, 2011 at 10:00 pm
I'm a little late to this question, but here's my chicken-#$%^ way I deal with things like this when the OPENROWSET target is small: I read everything in to a temp table (no WHERE clause), then run my real query w/ the WHERE clause against the temp table. For 2,000 row text files, it works just fine. Obviously if your WHERE was necessary to reduce 15,000,000 rows to 17, this wouldn't be so good, but then you probably would be reaching for SSIS or BULK COPY anyway.
Rich
November 22, 2011 at 6:50 am
Good tip. thanks rich.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply