QUERY throwing Error- Need help-Urgent | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

QUERY throwing Error- Need help-Urgent

CREATE TABLE [dbo].[UserActivity] (
[name] [varchar] (30) NULL ,
[spid] [smallint] NOT NULL ,
[db] [varchar] (30) NULL ,
[hostname] [varchar] (30) NOT NULL ,
[program_name] [varchar] (30) NOT NULL ,
[login_time] [datetime] NOT NULL ,
[last_batch] [datetime] NOT NULL ,
[closed_by] [datetime] NULL ,
[net_address] [varchar] (12) NULL ,
[net_library] [varchar] (12) NULL
SELECT suser_sname(sid) as name,
db_name(dbid) as db,
hostname, program_name,
login_time, last_batch,
NULL, net_address, net_library
from master..sysprocesses as P
where spid not between 1 and 6
AND NOT EXISTS (select * from UserActivity as U
where P.login_time = U.login_time
and P.spid = U.spid)
Server: Msg 8152, Level 16, State 2, Procedure RefreshUserActivity, Line 1
String or binary data would be truncated.
The statement has been terminated.

This Query is not working properly giving above error. Can anyone pointout whts the reason? Its workin fine in my local machine, not in my production servers . all have sqlserver 2000 installed.
Usually this happens when you dimension a variable or a column too small. Try increasing the string columns in your table. This should do it —

Try SELECT LEN(suser_sname(sid)),
LEN(hostname), LEN(program_name),
LEN(net_address), LEN(net_library)
from master..sysprocesses as P to see if any of these fields exceeds the field length of the target field. That old procedure is still causing errors, I see …
Forgot to add: Most likely reason to me seems to be the column "program_name" —

Excelent!!! i am all set now. Thanks a TON Adriaan and FrankKalis .<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br /><br />Rajiv<br />SQL-DBA