SQL Server Cursor Cutting Off

Question

I'm currently putting together a script that will stage some data in a SQL server to be extracted by another process. However it appears that the cursor im building is cutting off.

For example, the script should look through all DB's that have a name like TESTDB_ but cuts off half way through the script, and when printing it out the statement isn't even fully generated. Below is a sample of the script.

declare @sql varchar(8000), @envname varchar(10), @dbs varchar(100)
declare @db CURSOR
set @sql = ''
set @db = cursor for
    --dynamically get the current servers that are online
    select name from sys.databases where name like 'TESTDB_%'
open @db
fetch next from @db into @dbs
while @@fetch_status = 0
    begin
    set @sql=@sql+'
insert into DB1 (DBNAME,TEST1,TEST2,TEST3,TEST4,TEST5) 
select  DB_NAME() as ''DBNAME'', a.Test1, b.Test2, c.test3, d.test4, e.test5 
from ['+@dbs+'].[client].Table1 a 
    inner join ['+@dbs+'].[client].table2 b on a.ID=b.Id
    inner join ['+@dbs+'].[client].table3 c on c.ID=a.Id
    inner join ['+@dbs+'].[client].table4 d on d.ID=a.Id
    inner join ['+@dbs+'].[client].table5 e on e.ID=a.Id
'           
fetch next from @db into @dbs
    end
close @db
deallocate @db
print @sql 
set nocount off

Answer 1

Try this:

SET NOCOUNT ON; 

declare @sql varchar(8000), @envname varchar(10), @dbs varchar(100)
declare @db CURSOR
set @sql = ''
set @db = cursor for
    --dynamically get the current servers that are online
    select name from sys.databases where name like 'TESTDB_%'
open @db
fetch next from @db into @dbs
while @@fetch_status = 0
    begin
    SELECT @sql+='
insert into DB1 (DBNAME,TEST1,TEST2,TEST3,TEST4,TEST5) 
select  DB_NAME() as ''DBNAME'', a.Test1, b.Test2, c.test3, d.test4, e.test5 
from ['+@dbs+'].[client].Table1 a 
    inner join ['+@dbs+'].[client].table2 b on a.ID=b.Id
    inner join ['+@dbs+'].[client].table3 c on c.ID=a.Id
    inner join ['+@dbs+'].[client].table4 d on d.ID=a.Id
    inner join ['+@dbs+'].[client].table5 e on e.ID=a.Id
'           
fetch next from @db into @dbs
    end
close @db
deallocate @db
print (@sql) 

Results: