Here is a sproc I wrote to handle a similar problem:

/*This can execute dynamically generated SQL statements

up to approx 156000 chars in length.



Since you can't pass this as a variable the proc takes

the table and fieldname of a text value to execute.



A where statement can be included and should fully 

declare the table name.  Try to keep this statement as

simple as possible.



This can also handle fields in #temp tables



Author: Keith Henry (k_count at hotmail)*/

create proc executelargesql(@tablename sysname, @fieldname sysname, @where nvarchar(3000) = null) as

begin

declare @len int, @isql nvarchar(4000), @i int, @lsql nvarchar(4000)

create table #isqlvars (dlen int, hold ntext) --#table created before sp_executesql will be visible inside it



--first we need to get the length of the field we are dealing with

if left(@tablename, 1) = '#' --we have a # table as source, look in temp db

set @isql = 'declare @type sysname' + char(10) + 

'select @type = t.[name] from tempdb.dbo.sysobjects o inner join tempdb.dbo.syscolumns c on o.[id] = c.[id] inner join tempdb.dbo.systypes t on c.xtype = t.xtype where left(o.[name], len(''' + @tablename + ''')) = ''' + @tablename + ''' and c.[name] = ''' + @fieldname + '''' + char(10) + 

'insert #isqlvars (dlen,hold) select case @type when ''ntext'' then (datalength([' + @tablename + '].[' + @fieldname + '])/2) else datalength([' + @tablename + '].[' + @fieldname + ']) end,[' + @tablename + '].[' + @fieldname + '] from [' + @tablename + ']' + isnull(' where' + @where,'')

else

set @isql = 'declare @type sysname' + char(10) + 

'select @type = t.[name] from dbo.sysobjects o inner join dbo.syscolumns c on o.[id] = c.[id] inner join dbo.systypes t on c.xtype = t.xtype where o.[name] = ''' + @tablename + ''' and c.[name] = ''' + @fieldname + '''' + char(10) + 

'insert #isqlvars (dlen,hold) select case @type when ''ntext'' then (datalength(dbo.[' + @tablename + '].[' + @fieldname + '])/2) else datalength(dbo.[' + @tablename + '].[' + @fieldname + ']) end,dbo.[' + @tablename + '].[' + @fieldname + '] from dbo.[' + @tablename + ']' + isnull(' where' + @where,'')



exec sp_executesql @isql

if @@error <> 0 or @@rowcount <> 1 goto doh



select @isql = '', @i = 0, @lsql = null, @len = v.dlen from #isqlvars v



while @i <= @len --this can loop up to 39 times before it becomes too big for sp_executesql to handle

select @isql = @isql + char(10) +

'declare @sql' + cast(@i as varchar) + ' nvarchar(4000)' + char(10) + 

'select @sql' + cast(@i as varchar) + ' = substring(hold,' + cast(@i as varchar) + ',4000) from #isqlvars',

@lsql = isnull(@lsql + '+','') + '@sql' + cast(@i as varchar), 

@i = @i + 4000



select @isql = @isql + char(10) + 'exec (' + @lsql + ')'



exec sp_executesql @isql

if @@error <> 0 goto doh



goto done

doh:

print 'An error has occured '

print @isql

select * from #isqlvars

done:

drop table #isqlvars

end

go

Keith Henry