Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to call Oracle sequence via SQL server 2005 linked server

Posted on 2012-03-19
2
Medium Priority
?
1,560 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 1600 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Viewers will learn how the fundamental information of how to create a table.

971 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