Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OPENQUERY SYNTAX IS INSANE Expand / Collapse
Author
Message
Posted Wednesday, November 9, 2011 9:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 21, 2013 10:30 AM
Points: 26, Visits: 150
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
Post #1203002
Posted Wednesday, November 9, 2011 9:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 8:24 AM
Points: 262, Visits: 348
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.
Post #1203010
Posted Wednesday, November 9, 2011 9:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 21, 2013 10:30 AM
Points: 26, Visits: 150
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 ')'.
Post #1203044
Posted Wednesday, November 9, 2011 9:44 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:48 AM
Points: 21,388, Visits: 9,604
You need quadruple quotes when you're in double dynamic sql.
Post #1203052
Posted Wednesday, November 9, 2011 9:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 8:24 AM
Points: 262, Visits: 348
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 { '') ' }
Post #1203059
Posted Wednesday, November 9, 2011 10:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 21, 2013 10:30 AM
Points: 26, Visits: 150
Success! Gentlemen, thanks so much for your assitance. Your responses have been extremely helpful and insightful.

John
Post #1203088
Posted Friday, November 18, 2011 10:00 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
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
Post #1208761
Posted Tuesday, November 22, 2011 6:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 21, 2013 10:30 AM
Points: 26, Visits: 150
Good tip. thanks rich.
Post #1210109
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse