Making a Stored Procedure Available to All Databases

I need to create a stored procedure today that could be used on any database within a given SQL Server instance. After some digging I found that there are three requirements for such a stored procedure:
1. The stored procedure must be created in the master database.
2. The name of the stored procedure must start with “sp_“.
3. The stored procedure must be marked as a system object.


The first two are quite easily accomplished but the third requirement requires the use of an undocumented stored procedure named sys.sp_MS_marksystemobject which will mark the created stored procedure as a system object.
-- 1. Create the procedure in the master database
USE master
GO
-- 2. Create the procedure with the prefix sp_
CREATE PROCEDURE sp_[Stored_Procedure_Name]
AS
BEGIN
    -- Insert the logic of your stored procedure here
END
GO
-- 3. Mark the stored procedure as a system object
EXEC sys.sp_MS_marksystemobject sp_[Stored_Procedure_Name]
After you complete the above three steps, you can run the stored procedure on any of the databases found in that instance of SQL Server.
Note: If you need to “unmark” the procedure as a system object just simply drop the procedure and recreate it.

SQL Server Denali: IIF Logical Function

If you have developed some sort of applications using Microsoft Visual Studio.net, then you are definitely familiar with “IIF” logical function. Prior to SQL Server Denali we had used “CASE” instead of “IIF” as this logical function was not available. But in SQL Server Denali CTP3, “IIF” is available with same ease and functionality.

According to BOL”IIF is a shorthand way for writing a CASE statement. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation. That is, the true_value is returned if the Boolean expression is true, and the false_value is returned if the Boolean expression is false or unknown. true_value and false_value can be of any type. The same rules that apply to the CASE statement for Boolean expressions, null handling, and return types also apply to IIF.

The fact that IIF is translated into CASE also has an impact on other aspects of the behavior of this function. Since CASE statements can nested only up to the level of 10, IIF statements can also be nested only up to the maximum level of 10. Also, IIF is remoted to other servers as a semantically equivalent CASE statement, with all the behaviors of a remoted CASE statement.” 

Enable CLR

In SQL Server the CLR feature is "turned off by default" for some security purposes. Once you have the CLR enabled, you can write user-defined functions, triggers, stored procedures, user-defined aggregates, and user-defined types in VB.Net and C#. To enable it


EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
/* Turn advanced options back off */
EXEC sp_configure 'show advanced options' , '0';
GO

Function to Get Specific Part of String

SUBSTRING is a good function to get a part of string, but when a string consists of many parts, getting specific part of it, is a bit painful task. Hopefully, following function will help you to achieve such tasks. 


The function takes a string and section number of string which is required and separator, which separates different sections of given string.


How To Use: SELECT dbo.fn_GetPartString('First,Second,Third,Forth,Fifth',3,',')


CREATE FUNCTION dbo.fn_GetPartString
(
            @StrTemp NVARCHAR(1000),
            @Section SMALLINT,
            @Separator NCHAR(1)


RETURNS NVARCHAR(2000)
BEGIN
      DECLARE
                  @StartPos INT,
                  @EndPos INT,
                  @Cycle INT,
                  @Result NVARCHAR(1000)


            SELECT @Cycle = 0,
                        @StartPos = 0
                        WHILE @Cycle < @Section-1
                              BEGIN
                                    SELECT @StartPos =    
                                    CHARINDEX(@Separator,@StrTemp,@StartPos)+1
                                    IF @StartPos = 1
                                          SELECT @Cycle = @Section
                                    ELSE                                           
                                          SELECT @Cycle = @Cycle + 1
                              END
            SELECT @EndPos  = CHARINDEX(@Separator,@StrTemp,@StartPos)
            SELECT @Result = LTRIM(RTRIM(SUBSTRING(@StrTemp,@StartPos,
                    CASE @EndPos WHEN 0 THEN (LEN(@StrTemp)+1)-@StartPos
                    ELSE (@EndPos-@StartPos) END)))
        RETURN @Result
END

Remove Extra Spaces From String Value

Extra spaces between characters of a string value is a common problem. Here is a script to remove such extra spaces.
--Creating a temporary table
CREATE TABLE #TempTable (strTemp VARCHAR(2000))
--Insert some value to test
INSERT  INTO #TempTable
SELECT  'This     is my                         Blog.               '
UNION ALL
SELECT 'It         contains            useful      information    regarding       SQL'
-- Lets remove extra spaces and tabs
WHILE 1 = 1
    BEGIN
        UPDATE  #TempTable SET strTemp = 
        REPLACE(SUBSTRING(strTemp, 1,
CHARINDEX('  ', strTemp, 1) - 1) + ' '
        + LTRIM(SUBSTRING(strTemp,
CHARINDEX('  ', strTemp, 1), 8000)),'  ',' ')
        WHERE CHARINDEX('  ', strTemp, 1) > 0
        IF @@rowcount = 0
            BREAK
    END
--Lets see the updated result
SELECT strTemp FROM #TempTable
--drop temporary table
DROP TABLE #TempTable

Copy table structure

1.Go to your Enterprise Manager
2.Go to the database where the required table is located
3.Select the table of your choice
4.Simply press Ctrl+C
5.Then go to Query Analyser and do Ctrl+V
6.Then Press F5

Export data from SQL Server to Excel without using SSIS or DTS

Normally for exporting data from SQL Server to Excel one would use DTS (SQL 2k) or SSIS (SQL Server 2005). For some reason if at all you want to do it via query read on:

Step 1: Execute the code snippet
Exec sp_configure 'show advanced options', 1;
Go
Reconfigure;
Go Exec sp_configure 'Ad Hoc Distributed Queries', 1;
Go
Reconfigure;
Go Step 2: Create the excel file and then add the headings in the .xls file. [Important] The heading should be the same as that of the table columns. 

Insert into Openrowset ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\TestExcel.xls;' , 'Select * from [ProductMaster$]') Select ProductID, ProductName from dbo.ItemMaster

Points which might interest you: 
1. As long as the file is within your C: drive this sample would work. If at all your database is in a different machine from that .xls file you need to change Database=c:\TestExcel.xls; to UNC path. For example, Database=\\Servername\shareName (And need to provide appropriate permission).
2. Instead of "ProductMaster" replace it with your excel worksheet name.

Registry writing and regisrty reading through SQL

In SQL server we have 2 undocumented stored procedures for reading from registry and for writing into registry. For reading from registry we use the xp_regread and for writing into registry we use xp_regwrite undocumneted extended stored procedures. These two SP`s can be found in master database of a particular server.

Usage :-
EXEC xp_regread @rootkey, @key,[@value_name],[@Value]
Example:-
EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQLServer\80\Replication\Subscriptions\',
@value_name= 'SubscriberEncryptedPasswordBinary'

EXEC xp_regwrite @rootkey,@key,@value_name,@type,@value
Example:-
EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test',
@value_name='TestValue', @type='REG_SZ', @value='Test'

Backup Analysis Service Database

An Analysis Service database can be backed up using SSMS or XMLA Query Editor.

Backup using SSMS:

1. Login to SSAS instance using SSMS

2. Right-Click on the Analysis service database you want to backup.

3. And Choose the option Backup.

4. Now in the Backup Database page, Enter the location to where you want the backup file to be placed.

5. Choose the other options, if required.

6. And Click on OK to backup the database.
Backup Using XMLA Query Editor:
1. Open the XMLA Query Editor using the New Query Option.
2. Enter the below query.
 <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> 
   <Object>
     <DatabaseID>Test</DatabaseID>
   </Object>
   <File>D:\Test.abf</File>
   <AllowOverwrite>true</AllowOverwrite>
 </Backup>
3. Replace the DatabasesID with the Analysis Service database name which you want to backup.

4. And replace the File to where(location on disk) you want the analysis service database to be backed up to.

5. Execute the Query.

Useful DBCC Commands

The Transact-SQL programming language provides DBCC statements that act as Database Console Commands for SQL Server.

Database Console Command statements are grouped into the following categories.

1. Maintenance

2. Miscellaneous

3. Informational

4. Validation

DBCC commands take input parameters and return values. All DBCC command parameters can accept both Unicode and DBCS literals.

Some of the Maintenance DBCC Statements are:

DBCC CLEANTABLE

DBCC INDEXDEFRAG

DBCC DBREINDEX

DBCC SHRINKDATABASE

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

DBCC SHRINKFILE

DBCC UPDATEUSAGE

Some of the Miscellaneous DBCC Statements are:

DBCC dllname (FREE)

DBCC HELP

DBCC FREESESSIONCACHE 

DBCC TRACEOFF

DBCC FREESYSTEMCACHE

DBCC TRACEON

Some of the Informational DBCC Statements are:

DBCC INPUTBUFFER

DBCC SHOWCONTIG

DBCC OPENTRAN

DBCC SQLPERF

DBCC OUTPUTBUFFER

DBCC TRACESTATUS

DBCC PROCCACHE

DBCC USEROPTIONS

DBCC SHOW_STATISTICS

Some of the Validation DBCC Statements are:

DBCC CHECKALLOC

DBCC CHECKFILEGROUP

DBCC CHECKCATALOG

DBCC CHECKIDENT

DBCC CHECKCONSTRAINTS  

DBCC CHECKTABLE

DBCC CHECKDB

Some of the useful DBCC commands along with their syntax and examples are listed down and are available for download. Click here to download your copy now.
Reference: BOL

Checking Existence and to Drop Temp Tables Checking Existence and to Drop Temp Tables

Temporary tables are useful for storing the data temporarily during the data processing. There are two types of temp tables available in SQL Server namely global temporary tables and local temporary tables. 
The name of Global temp tables prefix with '##' and Local temp tables prefix with that of '#'.

To check existence of a Global temparary table, drop and recreate the same use below approach.
IF OBJECT_ID('tempdb.dbo.##globalTempTable') IS NOT NULL THEN
BEGIN 
           DROP TABLE ##globalTempTable
END IF
CREATE TABLE  ##globalTempTable(ID INT NOT NULL)GO
To check existence of a Local temparary table, drop and recreate the same use below approach.
IF OBJECT_ID('tempdb.dbo.#localTempTable') IS NOT NULL THEN
BEGIN 
           DROP TABLE #localTempTable
END IF
CREATE TABLE  #localTempTable(ID INT NOT NULL)
GO

Delete from Registry using SQL

xp_regdeletevalue and xp_regdeletekey are the two undocumented stored procedures that helps in deleting values and keys from registry. These stored procedures should be used very vary carefully as there are chances of harming the system and system may crash. 

xp_regdeletekey
This is an extended stored procedure that will delete an entire key from the registry.
EXEC xp_regdeletekey @rootkey,@key
Example:-
EXEC master..xp_regdeletekey @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Test'
xp_regdeletevalue
This is an extended stored procedure that will delete a particular value for a key in the registry.
EXEC xp_regdeletevalue @rootkey,@key,@value_name
Example:-
EXEC master..xp_regdeletevalue @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Test', @value_name='TestValue'

SQL Server Denali: Format()

Converting DateTime to a specific format is bit difficult task as you need to remember specific format number, which you can use with CONVERT(). Like if you need to convert date to dd.mm.yy format, then you can do it as follow:

SELECT CONVERT(NVARCHAR(30),GETDATE(),104) AS Formatted_Date

Now using SQL Server Denali, we can use a function FORMAT() to format datetime.

FORMAT ( value, format [, culture ] )

DECLARE @dtDate datetime = GETDATE();

SELECT  FORMAT(@dtDate, 'dd.MM.yyy')  AS 'dd.MM.yy',

        FORMAT(@dtDate, 'dd/MMM/yyy') AS 'dd/MMM/yy',

        FORMAT(@dtDate, 'dd/MM/yyyy') AS 'dd/MM/yyyy',

        FORMAT(@dtDate, 'MMM dd, yy') AS 'MMM dd, yy',

        FORMAT(@dtDate, 'MMMM dd, yyyy (dddd)') AS 'MMMM dd, yyyy (dddd)',

        FORMAT(@dtDate, 'dddd MMMM dd, yyyy ') AS 'MMMM dd, yyyy (dddd)',

        FORMAT(@dtDate, 'hh:mm:ss') AS 'hh:mm:ss'

FORMAT() is not formatting only Date/TIME, It format other DataTypes also.

DECLARE @ITemp int = 15;

SELECT FORMAT(@ITemp,'e') AS Scientific,

              FORMAT(@ITemp,'p') AS [Percent],

              FORMAT(@ITemp,'c') AS Currency,

              FORMAT(@ITemp,'x') AS HexaDecimal

Note: In above examples we have not used, third parameter i.e. culture. If value for culture is not provided then it will use session default language. In my case it is "en-US"

Moving msdb database

Since msdb is a system databases, we cannot move this just by detach and attach process, as we cannot attach or detach a system database.

1. First get the list of msdb files by using this query
   
   select name,physical_name from sys.master_files whereDB_NAME(database_id)='msdb'  
2. Then for each msdb database file that you need to move, execute statements like below
   Alter Database msdb modify
   
   file (NAME = 'MSDBData' ,
   FILENAME = 'Drive:\Path\MSDBData.mdf') -- Mention the new location
   
   
   Alter Database msdb modify
   file (NAME = 'MSDBLog' ,
   FILENAME = 'Drive:\Path\MSDBLog.ldf') -- Mention the new location
3. Stop SQL Services
4. Move the files manually to the new location
5. Start SQL Services
6. Verify the new Location
   
   select name,physical_name from sys.master_files whereDB_NAME(database_id)='msdb'
   
   
   If the SQL Server Instance is configured with Database Mail option, then after the msdb movement you will have to verify that the database mail is working fine by sending a test email.

Status of the Database

The Different Statuses that a database can undergo

1 = autoclose (ALTER DATABASE)
4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY)
8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY)
16 = torn page detection (ALTER DATABASE)
32 = loading
64 = pre recovery
128 = recovering
256 = not recovered
512 = offline (ALTER DATABASE)
1024 = read only (ALTER DATABASE)
2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER)
4096 = single user (ALTER DATABASE)
32768 = emergency mode
4194304 = autoshrink (ALTER DATABASE)
1073741824 = cleanly shutdown

ParseName

PARSENAME retrieves parts of string delimited by dots. It is used to split DataBaseServer, DataBaseName, ObjectOwner and ObjectName but you can use it to split IP addresses, names etc
DECLARE @ParseString VARCHAR(100)
    SELECT @ParseString = 'DataBaseServer.DataBaseName.ObjectOwner.ObjectName'

    SELECT PARSENAME(@ParseString,4),
    PARSENAME(@ParseString,3),
    PARSENAME(@ParseString,2),
    PARSENAME(@ParseString,1)


    CREATE TABLE #Test (
   SomeField VARCHAR(49))

    INSERT INTO #Test
    VALUES ('aaa-bbbbb')

    INSERT INTO #Test
    VALUES ('ppppp-bbbbb')

    INSERT INTO #Test
    VALUES ('zzzz-xxxxx')

    --using PARSENAME
    SELECT PARSENAME(REPLACE(SomeField,'-','.'),2)
    FROM #Test


Another example:
CREATE TABLE BadData (FullName VARCHAR(20) NOT NULL);
    INSERT INTO BadData (FullName)
    SELECT 'Clinton, Bill' UNION ALL
    SELECT 'Johnson, Lyndon, B.' UNION ALL
    SELECT 'Bush, George, H.W.';


Split the names into 3 columns
Your output should be this: LastName FirstName MiddleInitial Clinton Bill Johnson Lyndon B. Bush George H.W.
SELECT FullName,PARSENAME(FullName2,NameLen+1) AS LastName,
    PARSENAME(FullName2,NameLen) AS FirstName,
    COALESCE(REPLACE(PARSENAME(FullName2,NameLen-1),'~','.'),'') AS MiddleInitial
    FROM(
    SELECT LEN(FullName) -LEN(REPLACE(FullName,',','')) AS NameLen,
    REPLACE(REPLACE(FullName,'.','~'),', ','.') AS FullName2,FullName
    FROM BadData) x


However there is one caveat when using the parsename function; if the string you are parsing contains more than 4 sections (3 dots), the function will return NULL.
SELECT PARSENAME('a.b.c.d', 1) -- Returns d
    SELECT PARSENAME('a.b.c.d.e', 1) -- Returns NULL


ParseName is a handy function to use, but you must be aware of what your data looks like.

Query to find Dependent Objects

Use below query to find the Dependent Objects:


Select DISTINCT (OBJECT_SCHEMA_NAME(id)+'.'+ OBJECT_NAME(id)) AS [Object Name] ,(OBJECT_SCHEMA_NAME(depid)+'.'+ OBJECT_NAME(depid)) AS [Is Dependant on],OBJECTPROPERTYEX(id,'BaseType') AS [Object Type],OBJECTPROPERTYEX(depid,'BaseType') AS [Object Type of Is Dependant on] From sys.sysdepends Where OBJECTPROPERTYEX(id,'IsMSShipped')=0 -- To get only user created object

Stuff

STUFF is another function that is hardly used, it is useful if you want to replace or add characters inside data Take a look at the code below. the first STUFF will replace X with 98765, the second STUFF will place 98765 before the X and the third stuff will replace X- with 98765
DECLARE @v VARCHAR(11)
    SELECT @v ='-X-'

    SELECT STUFF(@v, 2, 1, '98765'),
    STUFF(@v, 2, 0, '98765'),
    STUFF(@v, 2, 2, '98765')

The STUFF function is very handy if you need to insert dashes in a social security. You can accomplish that by using the function STUFF twice instead of using substring,left and right
DECLARE @v VARCHAR(11)
    SELECT @v ='123456789'

    SELECT @v,STUFF(STUFF(@v,4,0,'-'),7,0,'-')

Functions of master, msdb, model, tempdb databases

The Master database holds information for all databases located on the SQL Server instance and is the glue that holds the engine together. Because SQL Server cannot start without a functioning master database, you must administer this database with care.


The msdb database stores information regarding database backups, SQL Agent information, DTS packages, SQL Server jobs, and some replication information such as for log shipping.


The tempdb holds temporary objects such as global and local temporary tables and stored procedures.


The model is essentially a template database used in the creation of any new user database created in the instance.

Reverse

REVERSE just reverses the value, for example the code below returns CBA
SELECT REVERSE('ABC')
Reverse is handy if you need to split values, take a look at this example
CREATE TABLE #TestTable (csz CHAR(49))
    INSERT INTO #TestTable VALUES ('city ,st 12223')
    INSERT INTO #TestTable VALUES ('New York City,NY 10028')
    INSERT INTO #TestTable VALUES ('Princeton , NJ 08536')
    INSERT INTO #TestTable VALUES ('Princeton,NJ 08536 ')
    INSERT INTO #TestTable VALUES ('Long Island City, NY 10013')
    INSERT INTO #TestTable VALUES ('Long Island City, NY 10013 ')
    INSERT INTO #TestTable VALUES ('Long Island City , NY 10013')
    INSERT INTO #TestTable VALUES ('Long Island City ,NY 10013 ')
 
    SELECT LEFT(csz,CHARINDEX(',',csz)-1)AS City,
    LEFT(LTRIM(SUBSTRING(csz,(CHARINDEX(',',csz)+1),4)),2) AS STATE,
    RIGHT(RTRIM(csz),CHARINDEX(' ',REVERSE(RTRIM(csz)))-1) AS Zip
    FROM #TestTable

GetUTCDate

SELECT GETUTCDATE()
Returns the datetime value representing the current UTC time (Universal Time Coordinate or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which SQL Server is running.

And that is all, those are the ten functions that you should be using but currently you are not using all of them. Look them up in Books On Line so that you can see some more examples.


The difference between GETDATE() and GETUTCDATE() is time zone number of the SQL Server machine. Note that the local time is from SQL server machine, not your local machine if you are connected to the server remotely. The local time is also adjusted by day-light savings.


It mean
GETDATE = Returns the day of the month for the specified date according to local time.
GETUTCDATE() = Returns the day (date) of the month in the specified date according to universal time.


Using GETUTCDATE instead of GETDATE was a simple solution to a complicated problem to solve timezones and daylight savings changes.
Example:
DECLARE @local_time DATETIME;
DECLARE @gmt_time DATETIME;
SET @local_time = GETDATE();
SET @gmt_time = GETUTCDATE();
SELECT 'Server local time: '
   + CONVERT(VARCHAR(40),@local_time);
SELECT 'Server GMT time: '
   + CONVERT(VARCHAR(40),@gmt_time);
SELECT 'Server time zone: '
   + CONVERT(VARCHAR(40),
      DATEDIFF(hour,@gmt_time,@local_time));
GOOutput:
Server local time: Apr 26 2011 09:47PM
Server GMT time: Apr 26 2011  04:17PM
Server time zone: 5

When SQL Server was last started

Whenever SQL Server is (re)started ‘TempDB’ gets recreated. So the below query should give us the time of when ‘SQL Server’ was last started. 

Select create_date from sys.databases where [name] = 'tempdb' 
 When a SQL Server starts all the tasks which it initiates makes an entry in sysprocesses table with its ‘login time’. That said, all we need to do is find the least login_time which has got recorded in that table!

1. Select min(login_time) from master.dbo.sysprocesses 
OR 
2. Select login_time from master.dbo.sysprocesses where spid = 1 
OR 
3. Select login_time from sys.dm_exec_sessions where session_id = 1

Get all the skipped identity values from a table

Today, someone asked me to give a list of all the identity values in a table that have been skipped. The Database have a table with an identity column, the problem with identity columns is that if an insert is rolled back or fails in any way then the identity value could not be reused. Identifying gaps is pretty easy to do if you have a table of numbers in your database.

To get all the skipped identity values I perform certain action.

First I create a table with numbers between 1 and 2048

CREATE TABLE TempNumber(Number INT NOT NULL PRIMARY KEY)

GO

INSERT INTO TempNumber

SELECT Number + 1

FROM Master..spt_values s

WHERE s.Type='P'

Now, I create another table with some numbers WITH Gap

CREATE TABLE #NumberGap(ID INT)

INSERT INTO #NumberGap VALUES(1)

INSERT INTO #NumberGap VALUES(2)

INSERT INTO #NumberGap VALUES(4)

INSERT INTO #NumberGap VALUES(5)

INSERT INTO #NumberGap VALUES(9)

INSERT INTO #NumberGap VALUES(12)

Here is the code that will return the gaps (the values 3,6,7,8,10,11) from the #NumberGap

SELECT Mumber

FROM TempNumber AS T

LEFT JOIN #NumberGap AS N  ON T.Number = N.ID

WHERE T.Number < (SELECT MAX(ID) FROM #NumberGap) 

AND  N.ID IS NULL

Here I had used a simple left join, and also check for the max value otherwise we will get everything back that is greater than the max value in the #NumberGap table.

Workaround for 'Divide by zero error encountered'

Sample Table Structure for demo purpose

CREATE TABLE TestDivideByZero
(
WebSite varchar(50),
NumOfHits int,
Income int
)
Go
Insert dummy records
Insert into dbo.TestDivideByZero values ('a.com', 100, 20)
Insert into dbo.TestDivideByZero values ('b.com', 10, 0)
Insert into dbo.TestDivideByZero values ('c.com', 300, 25)
Insert into dbo.TestDivideByZero values ('d.com', 1300, 225)
Go
Query to produce 'Divide by zero error encountered.' error
Select WebSite, NumOfHits / Income from dbo.TestDivideByZero
Go This would throw the below error:
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
To solve this error make use of NULLIF function. Like, if the value is 0 then return NULL. For better understanding execute the below query and see it for yourself.
Select WebSite, NumOfHits / NullIf(Income,0) as ColumnName from TestDivideByZero
Go

Arithmetic overflow error converting IDENTITY to data type int

Today I ran into the below error:

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.

As the error suggests in the table the max limit of INT (2147483647) has reached.

I am creating one example For the people who haven't seen this error before.

Create table MaxInt
(
  SlNo int identity,
  FirstName varchar(30)
)
Go

---Inserting my first record
Insert into MaxInt values ('FirstRecord')

/* I Reseed to last few numbers */
DBCC CHECKIDENT ( MaxInt, ReSeed, 2147483646)

--Try inserting a record. It would
Insert into MaxInt values ('LastRecord')

/* Trying to insert another one .... now you would see the error mentioned above as we have reached the max of INT already */
Insert into MaxInt values ('LastRecord')

Select * from MaxInt

SlNo	FirstName
1	FirstRecord
2147483647	LastRecord

At this instance my first aim was to make the app up and running at the earliest and then think of the rest! :)

So i decided to reseed the identity column to -2147483648 and have the values count upward towards zero.

DBCC CHECKIDENT ( MaxInt, ReSeed, -2147483648) 
Go

Insert into MaxInt values ('NewRec')
Insert into MaxInt values ('NewRec')

Now we can insert another 2 Billions records into the table.

[Obviously this is just the idea. Before using it please test against your application code and whether it is fine with having negative values in the table etc.,]

That said, Once we reach the Max limit of INT this is not the only solution we have. Other options which we need to consider are:

1. Convert the column from Int to BigInt datatype 
2. Other version of the given solution is to start the number from 0 and make it count downwards as –1, –2, –3 etc.,