Table has identity value with primary key as big integer.

Stored procedure is running Return Scope_Identity() which works until the identity values exceeds the value of the implicit integer conversion, 2,147,483,646.

This is documented, but rarely run into.

The correct best practice is too use an output parameter for the stored procedure.

This requires changing the procedure and calling code, which can be difficult if this happens in production environment during a busy period.

The temporary solution is too reset the identity value to a negative values, as the range of an integer, -2,147,483,646 to +2,147,483,646.

Watch when you do this, as checkident('tablename') will reset an identity value, so be sure to use noreseed as part of the command.

For monitoring purposes:

 

and some discussion on the work-around, which is too return the value as an output parameter.

 

https://stackoverflow.com/questions/25915653/return-bigint-in-a-stored-procedure

Granting Create Table permissions to a specific schema requires, granting create table at the database level and alter at the schema level (doing no grants to the dbo schema).

Create user [some user] from external provider

go

Grant alter,select,insert,update,delete on schema::[schema name] to user [some user]

go

-- if control or references is needed

-- grant control,references on schema:: [schema name] to user [some user]

grant create table, create view, create function,create type to [some user]

go

Always confuses me to remember the grant alter and the create table portions.

Recently I was given several text files with 500k to 1 million update statements in each that needed to be run.

Try loading and running this in SQL Server Management studio and you'll find that if it does load and run, sometimes it fails part through with memory error or other issues (all related to the client, not the engine).

It would have been better for the teams to work together and write this as a table join update as opposed to generating so many individual statements, but that ship had sailed and now these needed to be run.

I ended up "Walking the Dog" or RBAR (Row by agonizing Row).

Load the text files to a table, use a cursor to read each row and dynamically execute it.  I threw in a global counter i could query to figure out where it was at in the process.  In total it took about 2-3 hours to run.

Here is how to walk the dog.

SET NOCOUNT ON; 

  

DECLARE @sqlstmt nvarchar(4000)

 

create table ##Global_Count (current_rownum int)

insert into ##Global_Count values (0)

 

 

PRINT '-------- starting --------'; 

  

DECLARE cur_statements CURSOR FOR  

SELECT sqlstmt FROM DocumentId_3

  

OPEN cur_statements 

  

FETCH NEXT FROM cur_statements INTO @sqlstmt

 

WHILE @@FETCH_STATUS = 0 

BEGIN 

       Exec sp_Executesql @sqlstmt

       update ##Global_Count set current_rownum = current_rownum + 1

FETCH NEXT FROM cur_statements INTO @sqlstmt

END

 

Close cur_statements

Deallocate cur_statements

 

select * from ##Global_Count

drop table ##Global_Count

Best practice would be to always specify a name.

But if you inherit a database across 1000's of locations and have to write update scripts, you may come across the need to drop a constraint for a column when you don't know the constraint name.

This script will find the constraint name based off the column and dynamically generate a drop statement for it.

SET @SelectStatement =  (SELECT 'ALTER TABLE [Machines] DROP CONSTRAINT ' + name

                                   FROM sysobjects               

                    WHERE parent_obj = object_id('TableName') AND xtype = 'D' AND name LIKE '%ColumnName%')

                           EXECUTE sp_executesql @SelectStatement

Seedling sourced from my Dad in South Carolina, and transplanted to larger containers, once they are big enough I'm going to plan them in a line and make a boundary at the back of my property.

There was water rationing at the time, but the "money" quote.

"But that's OK, there's always Beer and Plenty of it.".