Djabarov, Robert
Robert.Djabarov at usaa.com
Wed Mar 19 11:12:21 CST 2003
As far as I know, SET operations require a user to belong at a minimum to db_ddladmin database role, even when it is within the stored procedure that the user is allowed to execute. And also, I don't believe you can alter SEED and INCREMENT properties of an identity field without re-creating the table or removing IDENTITY all together and re-adding it with new values. Robert Djabarov Senior SQL Server DBA USAA IT/DBMS ? (210) 913-3148 - phone ? (210) 753-3148 - pager -----Original Message----- From: John Frederick [mailto:j.frederick at att.net] Sent: Wednesday, March 19, 2003 8:10 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer]basic question I'm don't know what automatic behaviors apply to in identity field, but in SQL Server, you have more control. In code, you can use "SET IDENTITY_INSERT ON" to completely turn off the constraints, enabling you to insert records with unused values corresponding to deleted records. I haven't tried it, but even without this, you might be able to plug new values into the IDENTITY SEED AND IDENTITY INCREMENT properties of the field as long as you didn't create values that violated constraints. -----Original Message----- From: dba-sqlserver-admin at databaseadvisors.com [mailto:dba-sqlserver-admin at databaseadvisors.com]On Behalf Of Antony, Fredrick Sent: Tuesday, March 18, 2003 11:49 PM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer]basic question No Identity is meant for identical value. There is no way of resetting it Regards Fred -----Original Message----- From: Susan Harkins [mailto:harkins at iglou.com] Sent: Wednesday, March 19, 2003 6:10 AM To: SQLList Subject: [dba-SQLServer]basic question In Access, a compact will reset the AutoNumber seed value to one more than the largest AN value already in the table. Is there anyway to do this with an Identity column in SQL Server? Susan H. _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com