December 5, 2007 at 9:07 am
I am trying to insert data into a Oracle table and use an Oracle sequence from a SQL Server 2005 stored procedure. I get the following error.
Msg 128, Level 15, State 1, Line 7
The name "PDEV1..DPI.SEQ_DPI.nextval" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
PDEV1 being the linked server and DPI being the database.
I have tried this with PDEV1..DPI.SEQ_DPI.nextval
DPI.SEQ_DPI.nextval
SEQ_DPI.nextval
I get the same error message. Code is below.
BEGIN DISTRIBUTED TRANSACTION
BEGIN TRANSACTION
if @product_count = 0
BEGIN
set @tmpSQL = 'insert into product
(PRODUCT_ID,ITEM_NUMBER ,UPSELL_ITEM_NUMBER,
SKU_NUMBER,UPSELL_SKU_NUMBER,
PRODUCT_DESC,RETAIL_PRICE,
POSTAGE_HANDLING,PDS_AVAILABLE_FLAG,PDS_FLAG,FRANCHISE_ID)
values
( PDEV1..DPI.SEQ_DPI.nextval,'+@Q+ @ITEM_NUMBER+@Q +','+@Q+@UPSELL_ITEM_NUMBER+@Q +','+
@Q+@ITEM_NUMBER+@Q +','+@Q+ @UPSELL_ITEM_NUMBER +@Q+','+
@Q+@ITEM_DESCRIPTION +@Q+','+CONVERT(NVARCHAR(13),@DEFAULT_PRICE) +','+
CONVERT(NVARCHAR(13),@ADDITIONAL_PH) +','+@Q+@ESSP_AVAILABLE +@Q+','+@Q+ @ESSP_FLAG +@Q +', 205 )'
set @tmpSQL = ltrim(rtrim(@tmpSQL))
EXEC (@tmpSQL)
SET @tmpSQL = ''
Any help would be appreciated.
December 5, 2007 at 9:32 am
When you're inserting a list of static values you use:
INSERT INTO {columns}
VALUES ( {ValuesList} )
When you're inserting values where some of them are generated from the database, you typically use:
INSERT INTO {columns}
SELECT {columns}
FROM ....
Try replacing the VALUES() section with:
SELECT Link..Sequence.NextVal FROM Link.DUAL
December 6, 2007 at 8:15 am
Using Using the Select now gives me a different error message.
set @sequence = ' Select PDEV1..DPI.SEQ_DPI.nextval '
set @tmpSQL = 'insert into PDEV1..SYS.PRODUCT
(PRODUCT_ID,ITEM_NUMBER ,UPSELL_ITEM_NUMBER,
SKU_NUMBER,UPSELL_SKU_NUMBER,
PRODUCT_DESC,RETAIL_PRICE,
POSTAGE_HANDLING,PDS_AVAILABLE_FLAG,PDS_FLAG,FRANCHISE_ID)'+ + @sequence + ','+@Q+
@ITEM_NUMBER+@Q +','+@Q+
@UPSELL_ITEM_NUMBER+@Q +','+
@Q+@ITEM_NUMBER+@Q +','+
@Q+ @UPSELL_ITEM_NUMBER +@Q+','+
@Q+@ITEM_DESCRIPTION +@Q+','+
CONVERT(NVARCHAR(13),@DEFAULT_PRICE) +','+
CONVERT(NVARCHAR(13),@ADDITIONAL_PH) +','+
@Q+@ESSP_AVAILABLE +@Q+','+
@Q+ @ESSP_FLAG +@Q +','+
'205 from PDEV1..SYS.DUAL '
Which give me the sql statment
insert into PDEV1..SYS.PRODUCT
(PRODUCT_ID,ITEM_NUMBER ,UPSELL_ITEM_NUMBER,
SKU_NUMBER,UPSELL_SKU_NUMBER,
PRODUCT_DESC,RETAIL_PRICE,
POSTAGE_HANDLING,PDS_AVAILABLE_FLAG,PDS_FLAG,FRANCHISE_ID)
Select PDEV1..SYS.SEQ_DPI.nextval ,'MA42','MA42 U',
'MA42','MA42 U',
'TEST PRODUCT FOR STAR --> PEARS FEED 2 ',89.99,
0.00,'N','N',205 from PDEV1..SYS.DUAL
And now I get the following error message if run in a query window alone
Msg 4104, Level 16, State 1, Line 7
The multi-part identifier "PDEV1..SYS.SEQ_DPI.NEXTVAL" could not be bound.
If I run it in a query window with as it is in the stored procedure with the BEGIN DISTRIBUTED TRANSACTION I get this error message.
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "MSDAORA" for linked server "PDEV1" was unable to begin a distributed transaction.
December 6, 2007 at 8:52 am
It appears that using an Oracle Sequence like this won't work.
2 options:
1. Create a stored procedure on Oracle and pass it the parameters for INSERT. The stored proc can get the Sequence.NextVal when called.
2. Pre-select the sequence into a SQL variable. You need to setup a linked server for this, and use OpenQuery. Take the resulting value and combine it into your INSERT SQL.
Declare @NextSeq As Int
Select @NextSeq = NextVal
From OpenQuery(YourLinkName, 'Select YOURSEQ.NEXTVAL FROM DUAL')
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply