Thank you very much Terry. I used the AdventureWorks database when I worked on the script, which does not have any tables with text columns. I tested the generation of the insert statements against other database which had tables with text columns but this was as far as I went. Here is one workaround I can suggest. It is admittedly silly, but it works. The script below assumes that we are where we were left off: the table dbo.test is created, but generated insert statements fail miserably.

Here is the script, but please replace the occurences of the word alterz with alter. If I spell them out correctly in the code block then I cannot submit the post 🙁

set nocount on;

go

delete dbo.test;

go

-- this does not work, there is a good reason why Microsoft recommends

-- not to use the text data type, but switch to varchar(max) instead

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

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

go

alterz table dbo.test alter column col2 varchar(max);

go

-- this works

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

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

go

-- now revert the table back to what it was

alterz table dbo.test alter column col2 text;

go

select * from dbo.test;

go

set nocount off;

go

Here is the dump from results window:

Msg 206, Level 16, State 2, Line 4

Operand type clash: varbinary is incompatible with text

col1 col2

-----------------------------

1 sample 1,sample 1

2 sample 2,sample 2

The idea is to temporarily alter the suspect table changing the text columns to varchar(max) and then reverting the change back to what it was. Though I would seriously consider permanent altering of the tables with text columns to replace those with varchar(max) or nvarchar(max). Hope this helps.

Oleg