Dynamic SQL and warnig message

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamic SQL and warnig message

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-08-31 : 11:30:36

Here is the dynamic SQL that I am trying to run.

declare @column varchar(3), @tsql varchar(200)

declare c1 cursor
for
select name from syscolumns where id = (select id from sysobjects where name = 'outcomes_1')
and name between '0' and 'A'
open c1
fetch next from c1 into @column
while (@@FETCH_STATUS = 0)
begin

set @tsql = 'insert into outcomes_final select '''+@column+''' as ''category'',q_id, qr_status_dt, org_id, p_id, sum(['+@column+']) as [count] from outcomes_1 group by qr_status_dt, org_id, p_id, q_id, ['+@column+'] having sum(['+@column+']) > 0'
exec (@tsql)

fetch next from c1 into @column

end

close c1
deallocate c1

The output looks fine, but it keep giving me warning message says:

warning:String or binary data would be truncated.
The statement has been terminated.
Server: Msg 8152, Level 16, State 9, Line 1

Warning: Null value is eliminated by an aggregate or other SET operation.
Server: Msg 8152, Level 16, State 9, Line 1

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-31 : 12:03:40

1. One of the insert statements tries to insert data that is too long for the recieving column.
Check that the (char,varchar,binary, and varbinary) columns in the outcomes_final table are broad enough.

2. The group by statement groups a value that is null, usually not a problem.

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-31 : 12:12:47

By the way, we prefer set based approaches on this site


select 'insert into outcomes_final select ''' + sc.name + ''' as category,q_id, qr_status_dt, org_id, p_id, sum([' + sc.name + ']) as [count] from outcomes_1 group by qr_status_dt, org_id, p_id, q_id, [' + sc.name + '] having sum([' + sc.name + ']) > 0'
from syscolumns sc where sc.id = (select id from sysobjects where name = 'outcomes_1')
and sc.name between '0' and 'A'

rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-31 : 12:51:20

1) declare @tsql varchar(2000) should do it

2) Set ANSI_WARNINGS Off .... Set ANSI_WARNINGS On

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources