Solved

How to call Oracle sequence via SQL server 2005 linked server

Posted on 2012-03-19
2
1,374 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

810 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