Solved

How to call Oracle sequence via SQL server 2005 linked server

Posted on 2012-03-19
2
1,344 Views
Last Modified: 2012-03-21
Hello Experts,

I am having trouble inserting rows into a Oracle table (which has a sequence generator) via SQL server 2005 linked server. If i dont include the column it errors out saying
'"ORA-01400: cannot insert NULL into ("STKANAQA_USER1"."KC_GEM_EVENT"."EVENTID") "

EventId is the column which is being enforced by sequential value. I am not sure how to call it from SQL server.

Insert OPENQUERY(STKANAQA,'select eventtypeid,eventdate,messageid from kc_gem_Event')

select '9',getutcdate(),messageid  from current_diff_gen
0
Comment
Question by:parpaa
2 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 400 total points
ID: 37738973
The preferred method is to create an Oracle stored procedure to do the insert and execute that from SQL Server passing the 3 columns you generate there.  

If that is not possible, you might try this technique  (and I'm assuming you are inserting a single record here):

Declare @Next As Int

Select @Next = Next
From OpenQuery(STKANAQA, 'Select YOURSEQ.NEXTVAL as Next FROM DUAL')

Insert OPENQUERY(STKANAQA,'select eventid, eventtypeid,eventdate,messageid from kc_gem_Event')

select @Next, '9',getutcdate(), messageid  from current_diff_gen
0
 

Author Comment

by:parpaa
ID: 37739318
Yep that helped. There might be multiple records.. I will try to use a cursor to insert. Thanks again!!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now