String or binary data would be truncated!!!

Question

String or binary data would be truncated!!!

JMSM

SSCertifiable

Points: 5440
More actions
March 2, 2010 at 7:56 am

#217344

Hello,
I've run a profiler into a trace file 'D:\Traces\Troubleshoot-TRACES.trc' but when i insert all data in a table the following error appears.
Note: The code of the table and insert statement appears at bottom of the post.
Msg 8152, Level 16, State 10, Line 1
String or binary data would be truncated.
The statement has been terminated.
The problem as i can see was in the TextData field and i've changed from varchar (4000) to varchar (8000) but the error became the same.
Any idea?
Hope u can help me.
Regards,
JMSM 😉
create table TraceResults(
TextData VARCHAR(4000),
Duration INT,
Reads INT,
Writes INT,
CPU INT,
StartTime DATETIME,
ProcedureName VARCHAR(100),
LoginName varchar(50),
NTUserName varchar(50))
go
insert into TraceResults (TextData, Duration, Reads, Writes, CPU, StartTime, LoginName, NTUserName)
select TextData, Duration/1000, Reads, Writes, CPU, StartTime, LoginName, NTUserName
from fn_trace_gettable('D:\Traces\Troubleshoot-TRACES.trc',1)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

JMSM (3/2/2010)
Hello,
I've run a profiler into a trace file 'D:\Traces\Troubleshoot-TRACES.trc' but when i insert all data in a table the following error appears.
Note: The code of the table and insert statement appears at bottom of the post.
Msg 8152, Level 16, State 10, Line 1
String or binary data would be truncated.
The statement has been terminated.
The problem as i can see was in the TextData field and i've changed from varchar (4000) to varchar (8000) but the error became the same.
Any idea?
Hope u can help me.
Regards,
JMSM 😉
create table TraceResults(
TextData VARCHAR(4000),
Duration INT,
Reads INT,
Writes INT,
CPU INT,
StartTime DATETIME,
ProcedureName VARCHAR(100),
LoginName varchar(50),
NTUserName varchar(50))
go
insert into TraceResults (TextData, Duration, Reads, Writes, CPU, StartTime, LoginName, NTUserName)
select TextData, Duration/1000, Reads, Writes, CPU, StartTime, LoginName, NTUserName
from fn_trace_gettable('D:\Traces\Troubleshoot-TRACES.trc',1)

Why you create separate table just use select * into 🙂

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 2

The issue is that you don't have enough space for something. I'd try varchar(max). If that doesn't work, then it's not that field. It might be any of the char fields. You can insert them one by one to debug which one it is.

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 3

Or just run this -- sp_help 'fn_trace_gettable'

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

JMSM SSCertifiable Points: 5440 More actions · Answer 4

Thanks a lot everybody.

I've used the following statement on insert

SET ANSI_WARNINGS OFF

insert into TraceResults(TextData, Duration, Reads, Writes, CPU, StartTime, LoginName, NTUserName)

select TextData, Duration/1000, Reads, Writes, CPU, StartTime, LoginName, NTUserName

from fn_trace_gettable('D:\Traces\Troubleshoot-TRACES.trc',1)

SET ANSI_WARNINGS ON

Is there any problem to the final result?

Regards,

JMSM 😉

Lowell SSC Guru Points: 323504 More actions · Answer 5

i just create a view of a trace,a nd then scripted the view to be a table;

CREATE VIEW sp_DMLTrace

AS

SELECT * FROM ::fn_trace_gettable('c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\MyDMLtrace2.trc', default)

here's it's definition; does it match what you were using?

CREATE TABLE [dbo].[SP_DMLTRACE] (

[TEXTDATA] NTEXT NULL,

[BINARYDATA] IMAGE NULL,

[DATABASEID] INT NULL,

[TRANSACTIONID] BIGINT NULL,

[LINENUMBER] INT NULL,

[NTUSERNAME] NVARCHAR(256) NULL,

[NTDOMAINNAME] NVARCHAR(256) NULL,

[HOSTNAME] NVARCHAR(256) NULL,

[CLIENTPROCESSID] INT NULL,

[APPLICATIONNAME] NVARCHAR(256) NULL,

[LOGINNAME] NVARCHAR(256) NULL,

[SPID] INT NULL,

[DURATION] BIGINT NULL,

[STARTTIME] DATETIME NULL,

[ENDTIME] DATETIME NULL,

[READS] BIGINT NULL,

[WRITES] BIGINT NULL,

[CPU] INT NULL,

[PERMISSIONS] BIGINT NULL,

[SEVERITY] INT NULL,

[EVENTSUBCLASS] INT NULL,

[OBJECTID] INT NULL,

[SUCCESS] INT NULL,

[INDEXID] INT NULL,

[INTEGERDATA] INT NULL,

[SERVERNAME] NVARCHAR(256) NULL,

[EVENTCLASS] INT NULL,

[OBJECTTYPE] INT NULL,

[NESTLEVEL] INT NULL,

[STATE] INT NULL,

[ERROR] INT NULL,

[MODE] INT NULL,

[HANDLE] INT NULL,

[OBJECTNAME] NVARCHAR(256) NULL,

[DATABASENAME] NVARCHAR(256) NULL,

[FILENAME] NVARCHAR(256) NULL,

[OWNERNAME] NVARCHAR(256) NULL,

[ROLENAME] NVARCHAR(256) NULL,

[TARGETUSERNAME] NVARCHAR(256) NULL,

[DBUSERNAME] NVARCHAR(256) NULL,

[LOGINSID] IMAGE NULL,

[TARGETLOGINNAME] NVARCHAR(256) NULL,

[TARGETLOGINSID] IMAGE NULL,

[COLUMNPERMISSIONS] INT NULL,

[LINKEDSERVERNAME] NVARCHAR(256) NULL,

[PROVIDERNAME] NVARCHAR(256) NULL,

[METHODNAME] NVARCHAR(256) NULL,

[ROWCOUNTS] BIGINT NULL,

[REQUESTID] INT NULL,

[XACTSEQUENCE] BIGINT NULL,

[EVENTSEQUENCE] BIGINT NULL,

[BIGINTDATA1] BIGINT NULL,

[BIGINTDATA2] BIGINT NULL,

[GUID] UNIQUEIDENTIFIER NULL,

[INTEGERDATA2] INT NULL,

[OBJECTID2] BIGINT NULL,

[TYPE] INT NULL,

[OWNERID] INT NULL,

[PARENTNAME] NVARCHAR(256) NULL,

[ISSYSTEM] INT NULL,

[OFFSET] INT NULL,

[SOURCEDATABASEID] INT NULL,

[SQLHANDLE] IMAGE NULL,

[SESSIONLOGINNAME] NVARCHAR(256) NULL,

[PLANHANDLE] IMAGE NULL)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!