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):
- T-SQL PRINT restricts the length of the output string to 8000 (non-Unicode) characters, or 4000 Unicode characters (the limit of VARCHAR and NVARCHAR, respectively).
- osql (now obsolete and replaced by sqlcmd) had a problem breaking lines longer than 255 characters.
- PRINT has/had a problem with subsequent line breaks
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:
- printing from a SQLCLR procedure, but this method (SqlPipe.Send()) also restricts the string length to 4000 characters.
- using sp_executesql to implement a modified version of LongPrint()
- provide an alternative interface to dbscript to generate value scripts in C# rather than TSQL
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… 😉
devioblog 