Dahab
3/27/2007 1:30:00 PM
Hi,
If you use a string variable to store the sql string, and use with
parameters and
Exec( ) to execute it,
you can specify variables in the query.
DECLARE @sql NVARCHAR(4000)
SET @sql = 'Select Col1, Col2
FROM OPENQUERY(MSORACLE1W, ''SELECT Col1,Col2
FROM Table
WHERE Con1 = ''''Con'''' and Date >=
'+''''''+ CONVERT(nvarchar(30), @LastUpdate, 106) +''''''+' '')
'
Exec(@sql)
D.
<themarkfords@yahoo.com> wrote in message
news:1174999539.294254.325940@p77g2000hsh.googlegroups.com...
> Hi all,
> Long time programmer/jack-of-all-trades. Using a linked server set up
> in SQL Server 2000, connected to an Oracle 9i instance. I need to
> query specific rows of data in several large Oracle tables from
> several SQL Server stored procedures via an already defined linked
> server using the MS Oracle data provider.
> If you're reading this I'm sure you know, there are issues here.
> 1) OpenQuery : You cannot use variables when using the OpenQuery
> method of retrieving data through the link. Basic select <column name>
> or select * queries work fine but you cannot restrict with a where
> unless the condition is hard coded. So I will be forced to either
> throw the data into a temp table or a cursor, neither of which I want
> to do.
> 2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
> name.schema_name.table_name
> In this case, the queries execute, but Oracle number datatypes throw
> an error as follows:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column
> 'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
> '"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
> 130 at compile time and 5 at run time].
>
> I did quite a bit of poking around, and tried many permutations of
> syntax, including considering the "lazy schema validation" for the
> linked server definition, which is not an option for me or in SQL 2k,
> and making changes to the datatypes in Oracle, which is also not an
> option for me, all to no avail.
>
> Any viable options or potential solutions would be greatly
> appreciated.
>
> Thanks,
> Kmaz
>