SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


String or binary data would be truncated.


String or binary data would be truncated.

Author
Message
mahesh.dasoni
mahesh.dasoni
Old Hand
Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)

Group: General Forum Members
Points: 325 Visits: 1025
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......
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)SSC Guru (93K reputation)

Group: General Forum Members
Points: 93311 Visits: 38955
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.

Cool
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)
mahesh.dasoni
mahesh.dasoni
Old Hand
Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)

Group: General Forum Members
Points: 325 Visits: 1025
It got resolved by changing VCHMESSAGE VARCHAR(800)

to VCHMESSAGE VARCHAR(max)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61501 Visits: 17954
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 Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Dennis Post
Dennis Post
SSC Eights!
SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)

Group: General Forum Members
Points: 963 Visits: 547
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.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61501 Visits: 17954
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. :-D

_______________________________________________________________

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 Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Dennis Post
Dennis Post
SSC Eights!
SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)

Group: General Forum Members
Points: 963 Visits: 547
Oops! Blush
Thanks Sean!
Haven't used it in many years.



For better, quicker answers on T-SQL questions, read Jeff Moden's suggestions.

"Million-to-one chances crop up nine times out of ten." ― Terry Pratchett, Mort
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41286 Visits: 19815
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Sean Lange
Sean Lange
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: General Forum Members
Points: 61501 Visits: 17954
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 Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41286 Visits: 19815
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search