Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to call Oracle sequence via SQL server 2005 linked server

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

580 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