OPENQUERY SYNTAX IS INSANE

Question

OPENQUERY SYNTAX IS INSANE

applebyte

Ten Centuries

Points: 1028
More actions
November 9, 2011 at 9:06 am

#246502

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

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

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

John Michael Robertson SSC Eights! Points: 988 More actions · Answer 1

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.

applebyte Ten Centuries Points: 1028 More actions · Answer 2

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You need quadruple quotes when you're in double dynamic sql.

John Michael Robertson SSC Eights! Points: 988 More actions · Answer 4

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

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 { '') ' }

applebyte Ten Centuries Points: 1028 More actions · Answer 5

Success! Gentlemen, thanks so much for your assitance. Your responses have been extremely helpful and insightful.

John

Rich Mechaber SSChampion Points: 11130 More actions · Answer 6

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

applebyte Ten Centuries Points: 1028 More actions · Answer 7

applebyte

Ten Centuries

Points: 1028

November 22, 2011 at 6:50 am

#1411449

Good tip. thanks rich.