The text data type is handled. It is cast as varchar(max) to address the varbinary incompatibility with text. This cast maybe not enough if your data has non-ansi values, so cast to nvarchar(max) could be used instead. I tweaked the proc to cast text as nvarchar and so far found no issues:

use AdventureWorks;

go

set nocount on;

go

create table dbo.test (col1 int not null, col2 text);

insert into dbo.test (col1, col2) values (1, 'sample 1,sample 1');

insert into dbo.test (col1, col2) values (2, 'sample 2,sample 2');

go

exec dbo.usp_generate_inserts 'dbo.test';

go

set nocount off;

go

Don't forget to drop the table. Amazingly when I include the statement to drop the table into the code block I cannot preview or submit my post, get the IE cannot display the page error.

Results:

insert into dbo.test ([col1], [col2]) values (0x00000001, 0x730061006d0070006c006500200031002c00730061006d0070006c00650020003100);

insert into dbo.test ([col1], [col2]) values (0x00000002, 0x730061006d0070006c006500200032002c00730061006d0070006c00650020003200);

As you can see from above, casting text as nvarchar makes 2 bytes per letter. For example, letter s has a code of 115, which is represented in hex as 7300. 115 is hex 73 (7 * 16 + 3) and zero is 00.

I discussed the problem and possible solution to the issue with large data values few posts above this one (on the same page, see my reply to Chris). Another thing I want to mention is the size of the data that SQL Server Management Studio displays in the query results window. Defaults are pretty small, so you might consider tweaking them to allocate enough space to fit the results. I mean adjusting Tools - Options - Query Results - SQL Server - Results to Grid - Maximum Characters Retrrieved - Non XML data and

Tools - Options - Query Results - SQL Server - Results to Text - Maximum number of characters displayed in each column settings of the Management Studio.

Oleg