Blog Archives

Scripting data in SQL 2008 Enterprise Manager

Posted on November 23, 2009
by

Did you know this incredibly useful feature now comes with SQL 2008′s Enterprise Manager? I haven’t tested it with multiple tables that have complex relationships, but if you want to script data with more complexity, you should consider Red Gate’s SQL Data Compare.

Script Data in MS SQL Server 2008 Database Tables using Generate SQL Server Script Wizard

If neither is feasible, one of the most popular, creative and often-overlooked options is to use Microsoft Excel and write a formula to generate INSERT statements. Drag the cell corner to copy this formula for multiple rows. There are also many other code generation possibilities from this technique if you think creatively.

SQL Queries from Excel

Scripting SQL data is easy to do, make it part of your build process! Stick it in SVN and you bring version control to your database development.

LinkedInDiggRedditTumblrGoogle GmailPrintFriendlyEmailShare
[ 1 Reply ]

SQL 2008: Saving changes is not permitted

Posted on October 15, 2009
by

Ran across a small issue with SQL Server 2008 Management Studio when attempting to add an identity column to a table which didn’t have one. As this action requires the entire table to be rebuilt (which Management Studio does behind-the-scenes), I was presented with an error message saying that ‘Saving changes is not permitted’.

By default in Management Studio 2008, you’re prevented from saving changes in the designer that require table re-creation. You can turn off this setting easily and the blog post at this link shows you how. :-)

SQL Server 2008 Designer Behavior Change: Saving Changes Not Permitted

Hopefully this might save you fifteen minutes one day.

LinkedInDiggRedditTumblrGoogle GmailPrintFriendlyEmailShare
[ 1 Reply ]

ADO.Net performance: ExecuteReader vs ExecuteNonQuery vs ExecuteScalar

Posted on September 20, 2009
by

Just re-linking a two year old blog post with performance advice that every developer using ADO.Net should read.

ExecuteReader, ExecuteNonQuery, ExecuteScalar … When to use What ?

Further notes:

  • Here is a good example (post #3) of how to implement OUTPUT parameters in a stored procedure using ExecuteNonQuery(), ignore post #2, he doesn’t know what he’s talking about.
  • Another example with just the ADO.Net side from MSDN: Executing a Command and Accessing Output Parameters.
  • The ExecuteNonQuery method returns the number of rows affected by the query (typically for Insert, Update, or Delete operations). From the MSDN page above.
LinkedInDiggRedditTumblrGoogle GmailPrintFriendlyEmailShare
Leave a Reply ?

Switch to our mobile site