Execute Database script generated by MS SQL Server

Firstly, script your database tables with Sql Server Management studio.

Then in your script, remove descriptive headers, SET ANSI_NULL, SET QUOTED_IDENTIFIER and GO. These things are used by Query Designer in SSMS.

Secondly, you can run your script with SqlCommand.ExecuteNonQuery() without any problem.

Advertisements

Auto increment column int MS SQL Server

I sometimes want to do this to create a field with auto-increment value to use as a sorting index. It is easy to achieve with MYSQL, I am going to show you how to achieve it with MS SQL 2008

Go to design view of your table, select the column which you want it to auto-increment. There will be a ‘Column Properties’ in the lower half of the designer. If you use Right-Click on the table and view its properties, there you can’t do any modification, which is strange to me. Anyway, go find ‘Identity Specification’ in ‘Column Properties’ located at the lower half of the designer.
Choose ‘Yes’ to (is identity)
Set ‘Identity Increment’ to some number, I usually use 1
Set ‘Identity Seed’ to some number, I usually use 1 too.
Save it, Then this column will generate auto-increment value when you insert new record to this table.

This is another very common things that people wants to do, IMHO.
enjoy it and cheers.