July 11, 2013 at 10:56 am
I'm trying to write an sp to query an Oracle db through openquery. The oracle portion of the query I've written works when run through the Oracle sqldeverloper, but the overall procedure returns an error when I try to run it through ssms.
declare @rec_id int
set @rec_id = 263703 -- for testing purposes
declare @query nvarchar(4000)
declare @sql nvarchar(4000)
declare @linked_server nvarchar(4000)
set @linked_server = 'VTRACK'
SET @query = 'SELECT * FROM OPENQUERY(' + @query + ','
SET @sql = '''select i.id, sys.utl_raw.cast_to_varchar2(dbms_lob.substr( i.raw_data, dbms_lob.getlength(i.raw_data), 1)) AS message
from fmc_in i inner join fmc_hail_messages_gui_v m on i.id = substr(m.creator_name,instr(m.creator_name,'':'')+1,length(m.creator_name)-9)
where m.id = '' + @rec_id + '')'''
exec (@query+@sql)
The error I get is
Incorrect syntax near 'select i.id, sys.utl_raw.cast_to_varchar2(dbms_lob.substr( i.raw_data, dbms_lob.getlength(i.raw_data), 1)) AS message
from fmc_'
but I don't see what's wrong. Is there another way to approach this?
July 11, 2013 at 11:05 am
Mark Harley (7/11/2013)
I'm trying to write an sp to query an Oracle db through openquery. The oracle portion of the query I've written works when run through the Oracle sqldeverloper, but the overall procedure returns an error when I try to run it through ssms.
declare @rec_id int
set @rec_id = 263703 -- for testing purposes
declare @query nvarchar(4000)
declare @sql nvarchar(4000)
declare @linked_server nvarchar(4000)
set @linked_server = 'VTRACK'
SET @query = 'SELECT * FROM OPENQUERY(' + @query + ','
SET @sql = '''select i.id, sys.utl_raw.cast_to_varchar2(dbms_lob.substr( i.raw_data, dbms_lob.getlength(i.raw_data), 1)) AS message
from fmc_in i inner join fmc_hail_messages_gui_v m on i.id = substr(m.creator_name,instr(m.creator_name,'':'')+1,length(m.creator_name)-9)
where m.id = '' + @rec_id + '')'''
exec (@query+@sql)
The error I get is
Incorrect syntax near 'select i.id, sys.utl_raw.cast_to_varchar2(dbms_lob.substr( i.raw_data, dbms_lob.getlength(i.raw_data), 1)) AS message
from fmc_'
but I don't see what's wrong. Is there another way to approach this?
Your problem is due to not setting your variable to something.
declare @query nvarchar(4000)
SET @query = 'SELECT * FROM OPENQUERY(' + @query + ','
@query IS NULL so this will mean that after you run this it will still be NULL.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2013 at 11:05 am
Firstly, shouldn't
SET @query = 'SELECT * FROM OPENQUERY(' + @query + ','
actually be
SET @query = 'SELECT * FROM OPENQUERY(' + @linked_server + ','
?
As for the syntax error, print out the result before you EXEC it and see what the output is.
Not sure why you're breaking the thing up into multiple variables, it's nowhere near 8000 characters long (two nvarchar(4000))
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 11, 2013 at 11:17 am
GilaMonster (7/11/2013)
Firstly, shouldn't
SET @query = 'SELECT * FROM OPENQUERY(' + @query + ','actually be
SET @query = 'SELECT * FROM OPENQUERY(' + @linked_server + ','?
As for the syntax error, print out the result before you EXEC it and see what the output is.
Not sure why you're breaking the thing up into multiple variables, it's nowhere near 8000 characters long (two nvarchar(4000))
Yes, you were right about my mixing up the @linked_server and @query variables. I'm following an example on how to pass variables through openquery calls (http://support.microsoft.com/kb/314520), which is why I've broken things out as they are. If I ever get this working I will trim it down, but for the moment that's not a pressing issue.
I think part of the problem lies with the multiple nested quotes, which I find hard to keep track of. What's the general rule for their use, and is there a better alternative?
July 11, 2013 at 4:23 pm
See here for tips on how writing OPENQUERY with dynamic SQL and still stay sane: http://www.sommarskog.se/dynamic_sql.html#OPENQUERY
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply