Solved

How to call Oracle sequence via SQL server 2005 linked server

Posted on 2012-03-19
2
1,426 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL State HYT00. Timeout expired proplem 8 46
Force XMLSEQUENCE to return empty tags for null values. 10 47
Rewriting a simple query 2 36
Query Task 8 23
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

752 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