April 7, 2016 at 12:26 pm
I'm having a permissions issue of some kind trying to use a sequence as a default constraint. I'll provide scripts where I can, but because we're using certificates and it's across databases, I can't give full repro scripts.
CREATE SEQUENCE sqFoo_ID
AS BIGINT
START WITH 1
INCREMENT BY 65536;
CREATE TABLE Foo(
ID BIGINT NOT NULL
CONSTRAINT pkFoo PRIMARY KEY CLUSTERED
CONSTRAINT dfFoo_ID DEFAULT( NEXT VALUE FOR sqFoo_ID ),
Descrip VARCHAR(10) NOT NULL
);
GO
INSERT INTO Foo( Descrip ) VALUES( 'A' )
This works fine inside management studio. However, if I take that insert statement and put it into a stored procedure on another database on the same server it bombs with the error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
If I change the insert statement in the stored procedure to:
INSERT INTO Foo( ID, Descrip ) VALUES( NEXT VALUE FOR sqFoo_ID, 'A' )
it works fine, but I don't want to change the procedure because I have a lot of them that would have to change. That was the point of putting the default value on the column. In the database containing the table and the sequence, there is a user that is identified by a certificate and that is a member of the db_owner role. The stored procedure in the other database has the certificate added to it. Inserts and updates normally work fine on tables in this database. It's only when I tried to add a default constraint using a sequence that I started having problems.
Any ideas?
April 7, 2016 at 12:39 pm
This appears to be functionally equilavent to how identity based identifiers work, only what you're doing is a lot more complicated and requires coding in every stored procedure that inserts. Why not use IDENTITY for populating the default constraint?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 7, 2016 at 12:45 pm
Because I need to be able to bulk generate IDs in the middle tier layer and use them prior to actually batch saving the data. I'm batch processing large numbers of parent/child records, and I need to be able to set the parent ID in the children prior to saving. Using a sequence, I can request a block of numbers and assign them to both parent and child prior to the save. Since I want to be able to specify the values on the save, using an identity would be a pain since I have to turn it off and then on for every insert, and then I'd have to select them back out.
[Edit]
Everything above is for some new development. I have some existing code and procedures that will be saving single records and I was hoping to avoid having to change all those procedures by adding the default constraint to the new column.
April 7, 2016 at 3:41 pm
I always insert the parent row first, get @@IDENTITY into a variable or output parameter, and then use that ID to insert child rows. Getting ETL processing in that order is easier than resorting into code arounds.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 7, 2016 at 3:46 pm
As indicated by Eric, you may be better off choosing a different approach entirely.
However, try schema-qualifying the reference to the SEQUENCE. There's been some screwiness around that: http://sqlblog.com/blogs/maria_zakourdaev/archive/2014/09/02/choosing-sequence-instead-of-identity-watch-your-step.aspx
Also, from https://support.microsoft.com/en-us/kb/2958429:
1347204 FIX: Access Violation when you insert data into a table that has a column with a sequence default from a cross-database transaction in SQL Server 2012
Cheers!
April 28, 2016 at 7:41 am
Thank you for the link. The problem was schema qualification. As soon as I schema qualified the sequence in the constraint, everything works beautifully.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply