Increasing Message Size in SQL 2005

Question

I am currently writing a script that intelligently strips a database down into a series of ordered INSERT statements with seeded identity columns that will allow me to place the records into a new database without destroying any keys or relationships.

I am using the PRINT function to write the finished insert statements to the message window and saving to query to a file. However it seems like there is a max character limit for the message window, is there any way to change that?

This is a database with 120k users and I will end up with hundreds of thousands of insert statements so the file is gonna be pretty big.

Answer 1

I think we've all had this problem at some point, I'll tell you how I ended up fixing it. Every message I wanted to output was inserted into a TEXT column on a separate table (in fact a separate database in my case). Once there you can export it to text, etc.

Answer 2

why not use:

bcp Utility or How to: Run the SQL Server Import and Export Wizard

it would be incredibly slow to populate a large database with an insert for every row.

EDIT based on OP's comments

You could create staging tables with a single varchar(max) column that contain the actual INSERT statements. Instead of printing the INSERT, insert it into a staging table. You can then use BCP to export the INSERTs out from the staging tables to a file, and then just run that file (now full of INSERTs).

Answer 3

Unfortunately no,
From http://msdn.microsoft.com/en-us/library/ms176047.aspx

A message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated. The varchar(max) and nvarchar(max) data types are truncated to data types that are no larger than varchar(8000) and nvarchar(4000).

I had to do something similar a few months back. I wrote a c# application to write the sql for me.

Regards,