September 3, 2015 at 7:43 am
Hi all,
I am able to create the table below but trying to execute 'Insert' query below I get this :
Msg 8152, Level 16, State 10, Line 2
String or binary data would be truncated.
The statement has been terminated.
CREATE TABLE TraceResults (
TextData VARCHAR(4000),
Duration INT,
Reads INT,
Writes INT,
CPU INT,
StartTime DATETIME,
ProcedureName VARCHAR(100)
)
GO
INSERT INTO TraceResults
(TextData, Duration, Reads, Writes, CPU, StartTime)
SELECT TextData, Duration/1000, Reads, Writes, CPU, StartTime
FROM fn_trace_gettable('C:\My\WebForums20150903090429.trc',1)
September 3, 2015 at 7:47 am
Tac11 (9/3/2015)
Hi all,I am able to create the table below but trying to execute 'Insert' query below I get this :
Msg 8152, Level 16, State 10, Line 2
String or binary data would be truncated.
The statement has been terminated.
CREATE TABLE TraceResults (
TextData VARCHAR(4000),
Duration INT,
Reads INT,
Writes INT,
CPU INT,
StartTime DATETIME,
ProcedureName VARCHAR(100)
)
GO
INSERT INTO TraceResults
(TextData, Duration, Reads, Writes, CPU, StartTime)
SELECT TextData, Duration/1000, Reads, Writes, CPU, StartTime
FROM fn_trace_gettable('C:\My\WebForums20150903090429.trc',1)
Which column is causing the error?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 3, 2015 at 7:52 am
doesn't tell me that, I posted the error
September 3, 2015 at 7:58 am
Tac11 (9/3/2015)
doesn't tell me that, I posted the error
Can't you tell by looking at the data? Can you post a few random samples here?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 3, 2015 at 8:14 am
It's got to be TextData, hasn't it? That's the only string or binary column that's being inserted. Looks like you've captured events in your trace that have more than 4000 characters of text data. You'll either need to make the column wider or change your INSERT statement so it only takes the first 4000 characters of TextData.
John
Edit - Chris, apologies if you were asking a rhetorical question in an attempt to get the original poster to think it through!
September 3, 2015 at 8:19 am
John Mitchell-245523 (9/3/2015)
It's got to be TextData, hasn't it? That's the only string or binary column that's being inserted. Looks like you've captured events in your trace that have more than 4000 characters of text data. You'll either need to make the column wider or change your INSERT statement so it only takes the first 4000 characters of TextData.John
Edit - Chris, apologies if you were asking a rhetorical question in an attempt to get the original poster to think it through!
No worries JM. Ever seen a stored procedure with a name longer than 100 characters? Neither have I 🙂
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 3, 2015 at 8:25 am
ChrisM@Work (9/3/2015)
John Mitchell-245523 (9/3/2015)
It's got to be TextData, hasn't it? That's the only string or binary column that's being inserted. Looks like you've captured events in your trace that have more than 4000 characters of text data. You'll either need to make the column wider or change your INSERT statement so it only takes the first 4000 characters of TextData.John
Edit - Chris, apologies if you were asking a rhetorical question in an attempt to get the original poster to think it through!
No worries JM. Ever seen a stored procedure with a name longer than 100 characters? Neither have I 🙂
I agree TextData is likely the source of the problem and that stored procedure names longer than 100 characters are rare. Just in case some names are longer than 100 characters, it's easy to make that column wider, just to prove the point.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 3, 2015 at 8:37 am
Cool!!! After I changed to 'TextData VARCHAR(8000)' it worked. Thanks smart people.
September 3, 2015 at 9:22 am
Of course, since MS uses sysname as the data type for name in the system views like sys.tables, sys.columns, sys.objects, etc. I would use sysname as the data type for holding the procedure name in the user defined table.
September 3, 2015 at 11:49 am
And since the text data could be larger than 8k, I'd go with varchar(max) myself for the TextData column.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply