Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » String or binary data would be truncated!!!
Add to briefcase

String or binary data would be truncated!!! Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
JMSM
Posted Tuesday, March 02, 2010 7:56 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:03 AM
Points: 185, Visits: 751
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
muthukkumaran Kaliyamoorthy
Posted Tuesday, March 02, 2010 8:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:22 AM
Points: 1,123, Visits: 4,423
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
Steve Jones - SSC Editor
Posted Tuesday, March 02, 2010 8:14 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 1:14 AM
Points: 31,433, Visits: 13,746
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
muthukkumaran Kaliyamoorthy
Posted Tuesday, March 02, 2010 8:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:22 AM
Points: 1,123, Visits: 4,423
Or just run this -- sp_help 'fn_trace_gettable'

Muthukkumaran Kaliyamoorthy

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

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

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 5:03 AM
Points: 185, Visits: 751
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
Lowell
Posted Tuesday, March 02, 2010 8:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 11,645, Visits: 27,738
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.