Linked Servers - Query Issue

[cLFlaVA]

Hi People...

I have an MS SQL Server database linked to an Oracle database. The oracle database table is set up with two columns: itplan_lookup_id and itplan_project_name (both numeric, a sequence has been created for the _id column).

I am trying to run the following query from SQL Server:

  insert KINTANA..KN.KN_GCR_ITPLAN_LOOKUP( itplan_project_id )
      select p.sub_id
        from sub_project p join cost_release r
          on p.sub_id = r.sub_id
       where p.sub_project_stat_cd = 'ACT'
         and r.release_year = year(getdate())
    group by p.sub_id
      having sum(r.release_amount) > 0
    order by p.sub_id;

I get the following error:

(0 row(s) affected)

Server: Msg 7344, Level 16, State 1, Procedure update_kintana_lookups, Line 9
OLE DB provider 'MSDAORA' could not INSERT INTO table '[KINTANA]..[KN].[KN_GCR_ITPLAN_LOOKUP]' because of column 'ITPLAN_LOOKUP_ID'. The column used the default value.
[OLE/DB provider returned message: Multiple-step operation generated errors. Check each status value.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowsetChange::InsertRow returned 0x80040e21:  Data status sent to the provider: [COLUMN_NAME=ITPLAN_PROJECT_ID STATUS=DBSTATUS_S_OK], [COLUMN_NAME=ITPLAN_LOOKUP_ID STATUS=DBSTATUS_S_DEFAULT]. Data status returned from the provider: [COLUMN_NAME=ITPLAN_PROJECT_ID STATUS=DBSTATUS_E_UNAVAILABLE], [COLUMN_NAME=ITPLAN_LOOKUP_ID STATUS=DBSTATUS_E_BADSTA...

How can I resolve this issue? I assume it has to do with the column I am ignoring -- I thought it would just auto-increment, but I'm being moronic most likely.

In Oracle, I would use the sequence_name.nextval call, but not here...

Thanks for your help,

Cory



*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[chrissie1]

did you use openquery(linkedservername,'blablasql')?


Christiaan Baes
Belgium

"My new site" - Me

 

[cLFlaVA]

hmm, not sure how that works... this gives me an error:

  insert KINTANA..KN.KN_GCR_ITPLAN_LOOKUP
      select openquery( KINTANA, 'select kn_gcr_itplan_seq.nextval from dual' )
           , p.sub_id
        from sub_project p join cost_release r
          on p.sub_id = r.sub_id
       where p.sub_project_stat_cd = 'ACT'
         and r.release_year = year(getdate())
    group by p.sub_id
      having sum(r.release_amount) > 0
    order by p.sub_id;

obviously that's the wrong syntax, but how would I get the next sequence number from the linked database, and data from the home database, then insert it into the linked database?



*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[cLFlaVA]

figured it out...

insert KINTANA..KN.KN_GCR_ITPLAN_LOOKUP
      select (select * from openquery( KINTANA, 'select kn.kn_gcr_itplan_seq.nextval from dual' ))
           , p.sub_id
        from sub_project p join cost_release r
          on p.sub_id = r.sub_id
       where p.sub_project_stat_cd = 'ACT'
         and r.release_year = year(getdate())
    group by p.sub_id
      having sum(r.release_amount) > 0
    order by p.sub_id;

you get a star 'cause i like you.



*cLFlaVA
----------------------------
[tt]( <P> <B>)¹³ * (<P> <.</B>)[/tt]

[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[chrissie1]

Thank you, kind weird man.

Christiaan Baes
Belgium

"My new site" - Me