Sql Server Compatibility : Null Values in Identity Column

Hi all,

I've a compatibility issue with an application wich was designed to work with sql server 6.5. Now, the application works fine in production with sql server 2000, but I can't configure my test server to reproduce the production behaviour.

My problem is that the request send to the database by oledb, send null values in a column of type identity and i've an error with sql server 2000.

ex : Consider that I've a table TEST with three columns COL1, COL2, COL3 and COL1 is an identity column (with auto-increment).
The request is : insert into TEST (COL1, COL2, COL3) values (null, 'aaa', 'bbb')

If someone knows how i've to configure my server to accept this request, it will be very helpful !

Thansk,

Benjamin

Answer this question

Sql Server Compatibility : Null Values in Identity Column

Piotr Trojanowski
Try using

SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

for the table.

That will allow you to bypass it. But an Identity MUST NOT be duplicate in the table, so you are stuck with only one row with a Null Value (But since you asked for valueS i am not sure this will work for you)

If you need more then one row tha has no ID then that row does not qualify as a identity since that would mean duplicate values
nOrphf

you don't need to specify any value to the auto-increment field. hust ignore it & it should be O.K.
Sabita
Thanks for your help, but I think I've not correctly explained my problem. I can't modified the application, so I've just to find a way to configure sql server to accept null values in an identity column and to generate a new identity each time... is it possible any ideas
daveremy
Yes i know, but i can't modify the code... So i've to configure my sql server to accept it...

Shash

This is a little bit tricky.

If you are not allowed to alter the application (Let me guess... You are migrating from oracle ) then you have to be a bit creative about it.

If you pass a Value for an identity colum , then you are trying to insert a value. SQL Server will only supply a value itself when you are NOT passing a Value (Null is also "a Value" here)

So you have to intercept the Data before it flows into your table and remove the NULL colum. This could be done with a trigger.

How is the table setup that you are using (especially the identity field)

[Edit:]
Ok... Let me correct myself... This was VERY tricky ;)

Here is a code example...

CREATE TABLE Sales
   (SaleID INT IDENTITY(100000,1),
    Text varCHAR(200),
   CONSTRAINT ID_PK PRIMARY KEY (SaleID)
   )

-- This is a plain example table... it has just one field for the demo
-- You would have to rename the ORIGINAL Table to something like sales_Base

create view vwSales as
select cast(saleID as int) SaleID, Text from Sales

-- Now we need to create a view that looks like the original table, but we need to "decouple" the SalesID from the "real" salesID - This is done by Casting it into its own Datatype. Since it is now a computed collumn SQL wont fuss if we try to insert a NULL here. If you just use SalesID then it will still cause problems since SQL will know that you cannot insert a NULL Value

create trigger SalesTest2
on vwSales
instead of INSERT
as
print 'Trigger hit'
insert into sales (text)
select text from inserted

-- This trigger just ignores the SalesID column and inserts "just" the text field into the base table

insert into vwsales (saleid,text) values (null,'AAA')

--And a test to show it works... Puh... this was a hard one ;)

Sql Server Compatibility : Null Values in Identity Column

Answer this question