thefrozencoder

Programming and Technology blog

Update to Narayana Vyas Kondreddi's generate insert script for SQL Server 2012

If you are like me you like re-using tools that you find extremely simple and easy to use. That is the case with Narayana Vyas Kondreddi’s sp_generate_inserts script for SQL Server 2000, 2005, (works with 2008 yet not documented). So when I installed SQL Server 2012 developer my first item was to re-create all of the system stored procs I have in my toolbox.

Unfortunately running the script from the above link will generate an error because the code references a system stored procedure that no longer exists in SQL Server 2012 master.dbo.sp_MS_upd_sysobj_category.  This stored procedure toggles the system mode feature where any object created while the mode is enabled is flagged as a system object.  Since the undocumented sproc does not exist the script will be created but it will not work properly.

However there is a small update that can be done to the original script to fix the issue.

You need to remove the following lines

--Turn system object marking on
EXEC master.dbo.sp_MS_upd_sysobj_category 1
GO

And replace the following lines

--Turn system object marking off
EXEC master.dbo.sp_MS_upd_sysobj_category 2
GO

With

EXEC sys.sp_MS_marksystemobject sp_generate_inserts
GO

The script should ruin without any errors and work as it did in the previous versions.

I have emailed the owner of the script so hopefully he will update the script for 2012.