Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

String or binary data would be truncated!!! Expand / Collapse
Author
Message
Posted Tuesday, March 2, 2010 7:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:29 AM
Points: 186, Visits: 811
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)

Post #875191
Posted Tuesday, March 2, 2010 8:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:10 AM
Points: 1,155, Visits: 4,624
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

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #875196
Posted Tuesday, March 2, 2010 8:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 33,088, Visits: 15,197
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #875201
Posted Tuesday, March 2, 2010 8:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, July 24, 2014 7:10 AM
Points: 1,155, Visits: 4,624
Or just run this -- sp_help 'fn_trace_gettable'

Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #875209
Posted Tuesday, March 2, 2010 8:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:29 AM
Points: 186, Visits: 811
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

Post #875218
Posted Tuesday, March 2, 2010 8:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #875233
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse