September 9, 2009 at 12:24 pm
I am trying to dynamically read an Oracle table across a SQL Server linked server.
My oracle 10g database has a table of SQL function text, some of which I would like to instantiate in my SQL Server 2000 database. The functions to instantiate will be determined at run-time via parameters.
My problem is creating a version of the SELECT statement that will load the function text in a varaiable that I can then execute. If this were a query within the same SQL Server database, I'd be able to code:
DECLARE @V_METHOD_TEXT VARCHAR(4000)
SELECT @V_METHOD_TEXT=METHOD_TEXT FROM DBO.DID_METHOD_DB_ASSOC WHERE METHOD_ID=19
EXEC (@V_METHOD_TEXT)
Travelling across the Oracle bridge makes things more complicated.
The four-part query doesn't work because of data consistency issues. The query
SELECT * FROM DDSQ..DDSMAIN.TG_METHOD_DB_ASSOC
yields the error:
OLE DB error trace [Non-interface error: Column 'DATABASE_TYPE_ID' (compile-time ordinal 1) of object '"DDSMAIN"."TG_METHOD_DB_ASSOC"' was reported to have a DBTYPE of 129 at compile time and 130 at run time].
Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'OraOLEDB.Oracle' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
Question 1
-------------
The columns in my Oracle database are all varchar (not varchar2), so I don't understand how these could be incompatible. Any thoughts? If I can get the four-part query to work, I'll have no need for OpenQuery (the subject of question 2).
Assuming that I was not going to get the four-part query to wok, I've begun exploring using OPENQUERY, which avoids the conversion error by passing the query to Oracle for processing. The code is in the code-start / code-end block below.
-------------- CODE START ----------------------------------------------
declare @i nvarchar(50)
declare @s-2 nvarchar(200)
declare @m varchar(2)
-- Identify the method to retrieve
SET @m='19'
-- Dynamically create the select statement to retrieve just the needed method
SET @s-2='select method_text from DDSMAIN.DID_METHOD_DB_ASSOC where database_type_id=2 and method_id=' + @m
SET @s-2='select method_text from openquery(ddsq,''' + convert(nvarchar, @s-2) + ''')'
-- Execute the openquery statement to retrieve the function text.
exec sp_executesql @s-2, N'@i varchar(50) output', @i output
-- Execute the function text to create the function.
exec(@i)
-------------- CODE END -----------------------------------------------
Unfortunately, this doesn't work either. It yields the following error:
[OLE/DB provider returned message: ORA-00942: table or view does not exist]
OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandPrepare::Prepare returned 0x80040e14].
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'OraOLEDB.Oracle'.
This error message is false. When I execute the openquery without using a variable and without sp_executesql, it works fine:
-------------- WORKING QUERY ---------------------------------------------------
select method_text from openquery(ddsq,'select method_text from DDSMAIN.DID_METHOD_DB_ASSOC where database_type_id=2 and method_id=19')
-------------- WORKING QUERY END -----------------------------------------------
This working query won't help however, because it doesn't provide the method_text in a variable that I can then execute.
QUESTION 2
---------------
How can I get the sp_executesql form of the query to recognize the table?
Help!
What can I do to make either the four-part query or the sp_executesql query work?
Thanks,
Tom
October 20, 2009 at 1:15 pm
Did you ever figure this out? I might have recently done something similar, but its been over a month since this post.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply