openquery / oracle.

  • hi,

    It doesnt work, why?

    declare @datesvarchar(6)

    set @dates = 200408

     select * from

     openquery(PRO, 'SELECT  * FROM SAPR3.ZSDOSCORE  WHERE FKART = ''RE''

         AND SUBSTR(FKDAT, 1, 6) = '@dates'')

    How can I use value-dates in openquery. PRO is oracle server.

    Thanks zdenek

  • 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

  • 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.



    Shamless self promotion - read my blog http://sirsql.net

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply