CODEWITHSUNDEEP
sql-serversql-server-2005t-sqlprinting
FlyingStreudel
FlyingStreudel

Reputation: 4464

Increasing Message Size in SQL 2005

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.

Upvotes: 1

Views: 2839

Answers (4)

Omesh
Omesh

Reputation: 29091

I think it can be done by printing it in a chunks

below code does it for a NVARCHAR variable named @query 

PRINT SUBSTRING(@query, 1, 4000)
PRINT SUBSTRING(@query, 4001,8000)
PRINT SUBSTRING(@query, 8001, 12000)
PRINT SUBSTRING(@query, 12001, LEN(@query))

Upvotes: 0

KM.
KM.

Reputation: 103587

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).

Upvotes: 1

Biff MaGriff
Biff MaGriff

Reputation: 8231

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,

Upvotes: 1

kprobst
kprobst

Reputation: 16651

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.

Upvotes: 2

Related Questions