Easy way to reseed identity column ? TIP #77

 

Sometimes, we may require to reseed identity column of a table due to various reason.

For example we have deleted a record from a Student which have an identity column StudentId.  Below is schema

StudentSchema

Now it has 3 rows as shown below

StudentRows

Now suppose we have deleted record 3 which is studentID 3.

Now when we want to insert a new record StudentId start with 4 and we want it should be 3.

So how to check and reseed it ?

To achieve this task SQL SERVER provided DBCC CHECKIDENT facility.

DBCC CHECKIDENT has 2 commands

1) To Check current Identity value.

DBCC CHECKIDENT (TABLENAME, NORESEED)

2) To reseed an identity column we have following command

DBCC CHECKIDENT ( TABLENAME , RESEED, VALUE) 

Lets check student table identity to check current column value and current Identity value we will write following command

DBCC CHECKIDENT(tblStudentSource,NORESEED)

See below snap when after running this command

Identity_Check

Now if you see 3 is current identity and column value 2 it means if I Insert a new row in tblStudentSource then that row will have studentId = 4 as a next identity column

Now I need to reseed it to 3 means if Insert a new record then studentId should be 3 to do this I have to reseed tblStudentSource.

DBCC CHECKIDENT(tblStudentSource,RESEED,2)

StudentReseed

Now to cross check whether it is reseed or not I write NORESEED command again as get following result

Check_Ressed

It means identity value 2 and current column value is 2 means if now I insert a new record we will get studentId = 3 as a identity column this what we want actually.

So. We did NORESSED, RESSED an identity column.

I hope it may help you somewhere in your work.

Thanks

Rj

Advertisements

How to pause execution for particular time or interval ? TIP #71

 

Suppose, We are working on a stored procedure in which some complex operation is going. We are fetching some value from a table and running some custom operations and after ending of this complex operation, we want a pause of suppose 10 seconds to run another job then in such situation we will use

WAITFOR  DELAY HH:MM: SS” 

For example if we want to run select command (second statement) after 10 second delay of first command execution then we will use this as shown in below

INSERT INTO tblStudent (StudentId, StudentName)

VALUES (‘123’,’ABc’)

— in the background we are running some cursor which would take aprox 10 seconds so we are waiting for 10 seconds here

WAITFOR  DELAY 00:00:10

SELECT * FROM tblStudentHistory

In other situation suppose you want to run specific command after particular time then in such cases we will use “WAITFOR TIME  HH: MM: SS”

suppose

“WAITFOR TIME 22:30:45 “   it means SQL statement will pause till 10:30:45 PM

I hope this tips may help you somewhere in your project.

Thanks

Enjoy!!!

RJ!!!

Unable to Login “Microsoft SQL Server, Error: 18456” error in SQL SERVER 2008 R2

Dear Friends,
Sometimes it happens when you did everything correct like Installation with mix mode, created new user assign credentials etc but still not able to login with the username and password credential.
You get “Unable to Login – Microsoft SQL Server Error: 18456”.
To resolve this error you need to do following steps
1) Login with Windows Authentication
2) Open server properties using right click
3) Go on security TAB
4) Check SQL Server And Windows Authentication mode Checkbox

Now you can login with user credentials.

Thanks & Best Regards,
Rajat Jaiswal

A new milestone 125th post on Indiandotnet.wordpress.com so far so good

Dear Friends,
This post is different post from my regular technical articles and it is my 125th post on Indiandotnet.wordpress.com.

I would like to thank all of my friends, family members, colleges & most valuable the reader of this blog who continuously giving feedback and improving my skill also.
So thank you very very much.
Keep reading, Keep commenting, and keep giving feedback.
I need more improvement and I know you people will help me in sharing my thoughts.
God bless you all.


Happy Holidays.


Merry Christmas.
Thanks
Rajat Jaiswal

Unable to Login SQL SERVER forgot password

Hello Friends,
I have an interesting thing to share.

 Just day before yesterday I forgot my Denali server password & server not having windows Authentication.

 I am unable to login to the server.  So I just tried a trick and it worked successfully.
I just open the service account of SQL SERVER and change its login to Local System Account as shown in below fig.
And this trick worked successfully and now I can login using windows account.


I hope this will help to you also if by chance you forgot your password to and same situation exists.

Thanks
Rajat Jaiswal

Reseed Auto Generated Id column in SQL SERVER

Dear friends,

Some time we need to reseed auto generated column values. To do this task we need to perform following action.

DECLARE @MaxId AS INT

 SELECT @MaxId = MAX(coursed)

FROM dbo.Courses

DBCC CHECKIDENT (Courses, RESEED, @MaxId)

 I hope this will help you.

 Thanks & Regards

Rajat Jaiswal

Secure your code from Reverse engineering & encrypt your code

Hello friends,
After developing complex business logic application in .NET most of this we think how to secure our application code? How to prevent our website or software from reverse engineering?, Encrypt your code.
Because we know there are many tools which break the DLL like Reflector.

To prevent the DLL from reverse engineering there are many obfuscation tool. There are many obfuscation tools. Obfuscator (DotFuscator community edition) is one of the free tools which provide protection from reverse engineering.
It obfuscates the DLL. It is easy in use.  The steps are very simple just select the DLL and build.
It will obfuscate the code.
Hope it will help in securing your application from Hacker from reverse engineering.

Thanks & Regards
Rajat Jaiswal

Create Database with isqlW command

Hello friends,
Batch files many times reduce our work. Many tables, stored procedures, functions times we need to create same database, tables, stored procedures, and pre pop data on multiple instances or even same environment. Then in that case we just create scripts and run again and again in SQL Management Studio. But if you are in hurry and you want to run the script on single click without opening file then the easiest  way is use ” ISQLW” .isqlw is the  another way and we can use it with batch file.
So here are the basic steps:-
Suppose I want to create friends DB database with all theand pre pop data.
Then step 1:- Generate, a script file in which we will keep all the create table’s statements, create procedures statements and pre pop scripts.
Step2:- create a batch file for this just open notepad and save the file with extension “.bat” then the file is saved as batch file. (A batch file is executable file)
Step 3: Just write isqlw command in that as shown below
IsqlW -E -i “\fdb.sql” -o “\rajat.txt”
Where -I switch for input file – o for output file.  With isqlw you can give credential also but my sql server is with mix mode so there is no problem.
After writing isqlw statement just save it and run it will create database FriendDB with all the tables, stored procedures and prepop data.
And the entire log will register in rajat.txt file.
In this way you can enjoy one click database creation with all tables’ stored procedures and pre pop data.

Thanks & Esteemed Regards
Rajat Jaiswal

 

Restore Database from Zip File in SQL Server.

Recently one of my colleagues shared a link which is very useful in many ways.

 http://clay.lenharts.net/blog/category/mssql-compressed-backup/

The link is very useful for restoring compressed backup. Hope this will help you also.

trust me it saved my whole day work because my production database size is  100 GB.

Thanks & Regards

Rajat Jaiswal