Limitations of the T-SQL PRINT Command

One of the main functions of dbscript is to allow the user to select a set of tables and columns. This selection is used to generate T-SQL code (either a set of statements or the definition of a stored procedure) which can then be executed on a live database to generate INSERT/UPDATE statements reproducing the values of the selected tables and columns. An earlier post illustrated this functionality.

While this works in principle (at least for MS SQL Server), there are restrictions in the T-SQL command that make live harder than necessary if you want to script long text or binary values (NVARCHAR(MAX), VARBINARY(MAX):

During research I found a couple of work-arounds for the 4000/8000 byte limit:

  • stored procedure LongPrint on ask.sqlservercentral
  • stored procedure LongPrint on Adam Anderson’s blog

Both procedures try to find a line break in the string to be output, split the string at the line breaks and prints the substrings accordingly. However, from looking at the code, the procedures only work correctly if there is a line break found within 4000 characters, which is a problem if

  • you have a list of column values without line breaks (in case of dbscript: INSERT and UPDATE values)
  • you want to output binary values whose hexadecimal representation is longer than 4000 characters

With all these restrictions in place, I considered some other solutions:

The sp_executesql solution is necessary because scripts should be generated without any requirements in the target databases. The idea is to encapsulate a version of LongPrint in a TSQL variable, and invoke this code using sp_executesql and passing the string to be output.

Preparing this blog I came to the solution that the current implementation is good enough for most cases. In cases where output length exceeds certain limits, or for databases that enforce similar limits or do not support outputting text at all, a generic C# implementation in the dbscript backend may be the best solution.

To be continued…πŸ˜‰

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: