October 21, 2004 at 7:58 am
I have found that the easiest way to get the a SQL Server query to work correctly when querying a remote Oracle database is to connect to it using sqlplus (the TNS must already be set up for the SQL Server to connect to it), write the query using Oracle syntax (at least that way you know it works on the Oracle database so you only SQL Server to worry about) and paste this as the OPENQUERY SQL:
Often it's best to use Oracle functions to convert data types rather than cast/convert. If I recall correctly you use
TO_DATE('string', 'format')
TO_CHAR(datefield, 'YYYY/MM/DD') (use whatever format you need for the view)
have a look at http://www-db.stanford.edu/~ullman/fcdb/oracle/or-time.html
Hope this helps,
Andrew
October 22, 2004 at 5:25 am
The other problem is that you can't pass dynamic parameters into an openquery. You need to dynamically build the sql and then execute it...
declare @dates varchar(6), @sql varchar(2000)
set @dates = 200408
set @sql = 'select * from
openquery(PRO, ''SELECT * FROM SAPR3.ZSDOSCORE WHERE FKART = ''''RE''''
AND SUBSTR(FKDAT, 1, 6) = '''''+@dates+''''''')'
--print @sql
exec (@sql)
I always use the print @sql at first to be sure that the query gets built correctly, assuming there are no problems with that I would uncomment the exec and comment the print.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply