<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
Administering
»
String or binary data would be truncated.
String or binary data would be truncated.
Rate Topic
Display Mode
Topic Options
Author
Message
mahesh.dasoni
mahesh.dasoni
Posted Tuesday, September 25, 2012 8:24 PM
SSC Journeyman
Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 81,
Visits: 864
I am getting error in below code:
SET NOCOUNT ON
CREATE TABLE #EVENTS (LOGDATE DATETIME, PROCESSINFO VARCHAR(50), VCHMESSAGE VARCHAR(800), SOURCE VARCHAR(20), CROW INT)
-- CHECK FOR SQL INSTALLATION WHETHER IF 2000 OR 2005 --
-- IN CASE OF 2000, XP_READERRORLOG GIVES ONLY SQL SERVER LOGS AND OUTPUTS ONLY 2 COLUMNS --
-- IN CASE OF 2005, XP_READERRORLOG GIVES BOTH SQL SERVER AND AGENT LOGS --
IF (SELECT LEFT(CAST(SERVERPROPERTY('PRODUCTVERSION') AS VARCHAR),1)) = 8
BEGIN
INSERT INTO #EVENTS (VCHMESSAGE, CROW) EXEC XP_READERRORLOG
UPDATE #EVENTS SET LOGDATE = SUBSTRING(VCHMESSAGE,1,19), PROCESSINFO = 'SERVER', SOURCE = 'SQL SERVER'
WHERE SUBSTRING(VCHMESSAGE,1,3)='200' and substring(VCHMESSAGE,5,5)='-'
END
ELSE
BEGIN
INSERT INTO #EVENTS (LOGDATE, PROCESSINFO, VCHMESSAGE) EXEC XP_READERRORLOG 0,1 -- CURRENT LOGS, SQL SERVER
UPDATE #EVENTS SET SOURCE = 'SQL SERVER'
INSERT INTO #EVENTS (LOGDATE, PROCESSINFO, VCHMESSAGE) EXEC XP_READERRORLOG 0,2 -- CURRENT LOGS, SQL SERVER AGENT
UPDATE #EVENTS SET SOURCE = 'SQL SERVER AGENT' WHERE SOURCE IS NULL
END
The error which i am getting is :
Msg 8152, Level 16, State 2, Procedure xp_readerrorlog, Line 1
String or binary data would be truncated.
Please if some can help......
Post #1364400
Lynn Pettis
Lynn Pettis
Posted Wednesday, September 26, 2012 1:10 AM
SSC-Insane
Group: General Forum Members
Last Login: Today @ 8:42 AM
Points: 21,832,
Visits: 27,857
I'd say you need to expand the size of one or more of your varchar columns. Not sure which but that's where I'd start. My google-fu isn't very strong today.
Lynn Pettis
For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here
or
when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here
and
here
Managing Transaction Logs
SQL Musings from the Desert
Fountain Valley SQL
(My Mirror Blog)
Post #1364466
mahesh.dasoni
mahesh.dasoni
Posted Wednesday, September 26, 2012 1:28 AM
SSC Journeyman
Group: General Forum Members
Last Login: Yesterday @ 9:25 AM
Points: 81,
Visits: 864
It got resolved by changing VCHMESSAGE VARCHAR(800)
to VCHMESSAGE VARCHAR(max)
Post #1364478
Sean Lange
Sean Lange
Posted Wednesday, September 26, 2012 7:57 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 8,960,
Visits: 8,527
That is one of the most irritating things about sql server. There is a connect issue somewhere to change the message of that error to include the column name. DUH!!! Of course the engine knows which column threw the error, not returning it in the error message is just sad.
_______________________________________________________________
Need help? Help us help you.
Read the article at
http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Moden's
splitter
.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1364699
Dennis Post
Dennis Post
Posted Thursday, September 27, 2012 7:30 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 109,
Visits: 305
Hi,
Before inserting the data, find out what the MAX(LEN(Col)) is for each column. Compare the results to your variable declarations and destination table design.
Btw, varchar(max) isn't recognized in SQL 2000. Better find out what it needs to be at the moment and add a little buffer. But if must be as big as possible use varchar(8000) instead.
Goodluck.
For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1365250
Sean Lange
Sean Lange
Posted Thursday, September 27, 2012 7:35 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 8,960,
Visits: 8,527
D.Post (9/27/2012)
Hi,
Before inserting the data, find out what the MAX(LEN(Col)) is for each column. Compare the results to your variable declarations and destination table design.
Btw, varchar(max) isn't recognized in SQL 2000. Better find out what it needs to be at the moment and add a little buffer. But if must be as big as possible use varchar(8000) instead.
Goodluck.
varchar(4000) is the largest size in sql 2000.
_______________________________________________________________
Need help? Help us help you.
Read the article at
http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Moden's
splitter
.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1365258
Dennis Post
Dennis Post
Posted Thursday, September 27, 2012 8:58 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 109,
Visits: 305
Oops!
Thanks Sean!
Haven't used it in many years.
For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Post #1365328
Luis Cazares
Luis Cazares
Posted Thursday, September 27, 2012 10:00 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 1,088,
Visits: 2,193
I disagree, varchar can hold up to 8000 characters in 2000 (nvarchar will only hold up to 4000, though).
http://msdn.microsoft.com/en-us/library/aa258242(SQL.80).aspx
Another option is to use text columns
Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1365363
Sean Lange
Sean Lange
Posted Thursday, September 27, 2012 11:54 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 8,960,
Visits: 8,527
Luis Cazares (9/27/2012)
I disagree, varchar can hold up to 8000 characters in 2000 (nvarchar will only hold up to 4000, though).
http://msdn.microsoft.com/en-us/library/aa258242(SQL.80).aspx
Another option is to use text columns
Doh!!! You are right Luis, thanks for the correction to my incorrect correction.
I too haven't 2000 in a long long time.
_______________________________________________________________
Need help? Help us help you.
Read the article at
http://www.sqlservercentral.com/articles/Best+Practices/61537/
for best practices on asking questions.
Need to split a string? Try Jeff Moden's
splitter
.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Post #1365425
Luis Cazares
Luis Cazares
Posted Thursday, September 27, 2012 12:10 PM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 1,088,
Visits: 2,193
Sean Lange (9/27/2012)
Luis Cazares (9/27/2012)
I disagree, varchar can hold up to 8000 characters in 2000 (nvarchar will only hold up to 4000, though).
http://msdn.microsoft.com/en-us/library/aa258242(SQL.80).aspx
Another option is to use text columns
Doh!!! You are right Luis, thanks for the correction to my incorrect correction.
I too haven't 2000 in a long long time.
I remember the number because of the 8K Splitter.
I just had to find the reference to confirm it.
Luis C.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1365437
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
