Professional Web Applications Themes

Problem pulling data from oracle - Microsoft SQL / MS SQL Server

Hi, I am trying to write a procedure in SQL server which is pulling data from oracle db using OLE and updating tables. I am running into an date time issue. Here's the select statement SELECT DISTINCT R.AGREEMENT_NUM AS PAID, R1.RELATIONSHIP_SUBTYPE AS PSubType FROM ORCLOLE..PRF_USER.RELATIONSHIP R, ORCLOLE..PRF_USER.RELATIONSHIP_SUBTYPE R1 WHERE ((UPPER(R.AGREEMENT_NUM) LIKE 'DAR%') OR (UPPER(R.AGREEMENT_NUM) LIKE 'CAN%')) AND UPPER(R.AGREEMENT_NUM) NOT LIKE 'DAR99%' AND UPPER(R.AGREEMENT_NUM) NOT LIKE 'DAR88%' AND UPPER(R.AGREEMENT_NUM) NOT LIKE 'CAN%.' AND R.END_DATE >= DATEADD(day, -120, GETDATE()) The getdate portion of this is giving an error Error converting data type DBTYPE_DBTIMESTAMP to datetime Basically in oracle its just sysdate-120 but ...

  1. #1

    Default Problem pulling data from oracle

    Hi,

    I am trying to write a procedure in SQL server which is pulling data
    from oracle db using OLE and updating tables. I am running into an date
    time issue. Here's the select statement

    SELECT DISTINCT R.AGREEMENT_NUM AS PAID, R1.RELATIONSHIP_SUBTYPE AS
    PSubType
    FROM ORCLOLE..PRF_USER.RELATIONSHIP R,
    ORCLOLE..PRF_USER.RELATIONSHIP_SUBTYPE R1
    WHERE ((UPPER(R.AGREEMENT_NUM) LIKE 'DAR%') OR
    (UPPER(R.AGREEMENT_NUM) LIKE 'CAN%'))
    AND UPPER(R.AGREEMENT_NUM) NOT LIKE 'DAR99%' AND
    UPPER(R.AGREEMENT_NUM) NOT LIKE 'DAR88%'
    AND UPPER(R.AGREEMENT_NUM) NOT LIKE 'CAN%.' AND R.END_DATE >=
    DATEADD(day, -120, GETDATE())

    The getdate portion of this is giving an error

    Error converting data type DBTYPE_DBTIMESTAMP to datetime

    Basically in oracle its just sysdate-120 but because SQL server doesn't
    recognize sysdate, it errors out there.
    Another thing, it was working fine with 8i. As soon as we upgraded to
    9i, this started happening. Does anyone know a compatible equivalent of
    getdate that both systems might recognize?

    Bobby

    Bobby Singh Guest

  2. #2

    Default Problem pulling data from oracle

    Hi,

    I am trying to write a procedure in SQL server which is pulling data
    from oracle db using OLE and updating tables. I am running into an date
    time issue. Here's the select statement

    SELECT DISTINCT R.AGREEMENT_NUM AS PAID, R1.RELATIONSHIP_SUBTYPE AS
    PSubType
    FROM ORCLOLE..PRF_USER.RELATIONSHIP R,
    ORCLOLE..PRF_USER.RELATIONSHIP_SUBTYPE R1
    WHERE ((UPPER(R.AGREEMENT_NUM) LIKE 'DAR%') OR
    (UPPER(R.AGREEMENT_NUM) LIKE 'CAN%'))
    AND UPPER(R.AGREEMENT_NUM) NOT LIKE 'DAR99%' AND
    UPPER(R.AGREEMENT_NUM) NOT LIKE 'DAR88%'
    AND UPPER(R.AGREEMENT_NUM) NOT LIKE 'CAN%.' AND R.END_DATE >=
    DATEADD(day, -120, GETDATE())

    The getdate portion of this is giving an error

    Error converting data type DBTYPE_DBTIMESTAMP to datetime

    Basically in oracle its just sysdate-120 but because SQL server doesn't
    recognize sysdate, it errors out there.
    Another thing, it was working fine with 8i. As soon as we upgraded to
    9i, this started happening. Does anyone know a compatible equivalent of
    getdate that both systems might recognize?

    Bobby

    Bobby Singh Guest

  3. #3

    Default Re: Problem pulling data from oracle

    dont cross post



    "Bobby Singh" <bobsingh1hotmail.com> wrote in message
    news:3E1E0984.CD6EF460hotmail.com...
    Hi,
    I am trying to write a procedure in SQL server which is pulling data from
    oracle db using OLE and updating tables. I am running into an date time
    issue. Here's the select statement
    SELECT DISTINCT R.AGREEMENT_NUM AS PAID, R1.RELATIONSHIP_SUBTYPE AS
    PSubType
    FROM ORCLOLE..PRF_USER.RELATIONSHIP R,
    ORCLOLE..PRF_USER.RELATIONSHIP_SUBTYPE R1
    WHERE ((UPPER(R.AGREEMENT_NUM) LIKE 'DAR%') OR (UPPER(R.AGREEMENT_NUM)
    LIKE 'CAN%'))
    AND UPPER(R.AGREEMENT_NUM) NOT LIKE 'DAR99%' AND
    UPPER(R.AGREEMENT_NUM) NOT LIKE 'DAR88%'
    AND UPPER(R.AGREEMENT_NUM) NOT LIKE 'CAN%.' AND R.END_DATE >=
    DATEADD(day, -120, GETDATE())
    The getdate portion of this is giving an error
    Error converting data type DBTYPE_DBTIMESTAMP to datetime
    Basically in oracle its just sysdate-120 but because SQL server doesn't
    recognize sysdate, it errors out there.
    Another thing, it was working fine with 8i. As soon as we upgraded to 9i,
    this started happening. Does anyone know a compatible equivalent of getdate
    that both systems might recognize?
    Bobby


    David Sharples Guest

  4. #4

    Default Re: Problem pulling data from oracle

    Bobby Singh wrote:
    > Hi,
    >
    > I am trying to write a procedure in SQL server which is pulling data
    > from oracle db using OLE and updating tables. I am running into an date
    > time issue. Here's the select statement
    >
    > SELECT DISTINCT R.AGREEMENT_NUM AS PAID, R1.RELATIONSHIP_SUBTYPE AS
    > PSubType
    > FROM ORCLOLE..PRF_USER.RELATIONSHIP R,
    > ORCLOLE..PRF_USER.RELATIONSHIP_SUBTYPE R1
    > WHERE ((UPPER(R.AGREEMENT_NUM) LIKE 'DAR%') OR
    > (UPPER(R.AGREEMENT_NUM) LIKE 'CAN%'))
    > AND UPPER(R.AGREEMENT_NUM) NOT LIKE 'DAR99%' AND
    > UPPER(R.AGREEMENT_NUM) NOT LIKE 'DAR88%'
    > AND UPPER(R.AGREEMENT_NUM) NOT LIKE 'CAN%.' AND* R.END_DATE >=
    > DATEADD(day, -120, GETDATE())***
    >
    > The getdate portion of this is giving an error
    >
    > Error converting data type DBTYPE_DBTIMESTAMP to datetime
    >
    > Basically in oracle its just sysdate-120 but because SQL server doesn't
    > recognize sysdate, it errors out there.
    > Another thing, it was working fine with 8i. As soon as we upgraded to
    > 9i, this started happening. Does anyone know a compatible equivalent of
    > getdate that both systems might recognize?
    >
    > Bobby
    >
    According to the following Microsoft kb article, a DBTYPE_DBTIMESTAMP is
    down to billionths of a second.

    [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;299905[/url]

    According to Note 149118.1 ([url]http://metalink.oracle.com):[/url]

    <quote>
    Oracle9i introduces/externalizes many features for date functionality as
    part of Oracle's commitment to conform to ANSI SQL specifications.

    Data is normalized to a database time zone when stored in the Oracle
    database and adjusted to the session time zone when the data is selected
    by users.
    </quote>

    Sounds like that's why it started happening in 9i.

    Karsten Farrell Guest

  5. #5

    Default Re: Problem pulling data from oracle

    Bobby Singh wrote:
    > Hi,
    >
    > I am trying to write a procedure in SQL server which is pulling data
    > from oracle db using OLE and updating tables. I am running into an date
    > time issue. Here's the select statement
    >
    > SELECT DISTINCT R.AGREEMENT_NUM AS PAID, R1.RELATIONSHIP_SUBTYPE AS
    > PSubType
    > FROM ORCLOLE..PRF_USER.RELATIONSHIP R,
    > ORCLOLE..PRF_USER.RELATIONSHIP_SUBTYPE R1
    > WHERE ((UPPER(R.AGREEMENT_NUM) LIKE 'DAR%') OR
    > (UPPER(R.AGREEMENT_NUM) LIKE 'CAN%'))
    > AND UPPER(R.AGREEMENT_NUM) NOT LIKE 'DAR99%' AND
    > UPPER(R.AGREEMENT_NUM) NOT LIKE 'DAR88%'
    > AND UPPER(R.AGREEMENT_NUM) NOT LIKE 'CAN%.' AND* R.END_DATE >=
    > DATEADD(day, -120, GETDATE())***
    >
    > The getdate portion of this is giving an error
    >
    > Error converting data type DBTYPE_DBTIMESTAMP to datetime
    >
    > Basically in oracle its just sysdate-120 but because SQL server doesn't
    > recognize sysdate, it errors out there.
    > Another thing, it was working fine with 8i. As soon as we upgraded to
    > 9i, this started happening. Does anyone know a compatible equivalent of
    > getdate that both systems might recognize?
    >
    > Bobby
    >
    According to the following Microsoft kb article, a DBTYPE_DBTIMESTAMP is
    down to billionths of a second.

    [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;299905[/url]

    According to Note 149118.1 ([url]http://metalink.oracle.com):[/url]

    <quote>
    Oracle9i introduces/externalizes many features for date functionality as
    part of Oracle's commitment to conform to ANSI SQL specifications.

    Data is normalized to a database time zone when stored in the Oracle
    database and adjusted to the session time zone when the data is selected
    by users.
    </quote>

    Sounds like that's why it started happening in 9i.

    Karsten Farrell Guest

Similar Threads

  1. Pulling data across domains
    By CreativeStride in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: September 23rd, 04:50 AM
  2. problem pulling text file from database
    By quiero mas in forum Coldfusion Database Access
    Replies: 4
    Last Post: March 22nd, 10:29 PM
  3. pulling data frm disconnected system.
    By anil kumar.m in forum ASP.NET Web Services
    Replies: 3
    Last Post: October 16th, 04:43 PM
  4. Replies: 0
    Last Post: August 12th, 04:37 PM
  5. #23668 [Opn->Fbk]: oracle<defunct> problem apache2+php+oracle
    By sniper@php.net in forum PHP Development
    Replies: 0
    Last Post: July 11th, 04:56 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139