SQL Server Performance Forum – Threads Archive
QUERY throwing Error- Need help-Urgent
TABLECREATE 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
)
QUERY INSERT UserActivity
SELECT suser_sname(sid) as name,
spid,
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)
ERROR
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 —
Frank
http://www.insidesql.de
Try SELECT LEN(suser_sname(sid)),
LEN(db_name(dbid)),
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" —
Frank
http://www.insidesql.de
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
]]>