May 15, 2012 at 10:43 am
Hi
I run an ASP site which stores its data in sql server 2008. One of its uses is to store blog posts, which until now have been stored in a varchar(7999) field, but today the user wants to upload a 9k character post. When he tried it is gave the error:
microsoft ole db provider for sql server error '80040e57'
string or binary data would be truncated
I changed the datatype to varchar(max) thinking that this would hold way in excess of 8k characters, but I get the same error.
I've rechecked the db, the field type has definitely changed to varchar(max)
Is varchar(max) also limited?
Any guidance gratefully received.
Regards
Tim
May 15, 2012 at 11:01 am
have you changed the server code which talks to DB? Or, if you are using stored proc, have you change the parameter definition there?
May 15, 2012 at 11:03 am
varchar(max) allows upto 2GB size of data, so that should not be the issue.
I suspect, your ASP application is using old sql driver to connect sql server.
if you have not installed SQL server native client, install first on ASP server machine, and modify your connection string if required
I hope this will help
May 15, 2012 at 11:04 am
Check layers between the user and the table. Proc, code that calls the proc (or inline SQL), connection, et al. The problem will be in one of those.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 15, 2012 at 11:45 am
Hi Guys
Thanks for the replies.
The code that does the insert is standard Dreamweaver CS5 insert code, no SP involved:
If (CStr(UploadFormRequest("MM_insert")) = "form1") Then
If (Not MM_abortEdit) Then
' execute the insert
Dim MM_editCmd
Set MM_editCmd = Server.CreateObject ("ADODB.Command")
MM_editCmd.ActiveConnection = MM_connMP_STRING
MM_editCmd.CommandText = "INSERT INTO dbo.tblBlog (BlogDt, BlogHead, UplBy, BlogTxt, BlogPic) VALUES (?, ?, ?, ?, ?)"
MM_editCmd.Prepared = true
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 135, 1, -1, MM_IIF(UploadFormRequest("BlogDt"), UploadFormRequest("BlogDt"), null)) ' adDBTimeStamp
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 201, 1, 250, UploadFormRequest("BlogHead")) ' adLongVarChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 5, 1, -1, MM_IIF(UploadFormRequest("UplBy"), UploadFormRequest("UplBy"), null)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 201, 1, -1, UploadFormRequest("BlogTxt")) ' adLongVarChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 201, 1, 250, UploadFormRequest("BlogPic")) ' adLongVarChar
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
DW doesn't differentiate between different lengths of text field - it's just text, numeric or date.
The server is a W2008 Web Edition machine , pre-installed with SQL Server 2008, so I can't see that the driver is going to be an issue, is it?
The connection string is:
Dim MM_connConnection_STRING
MM_connConnection_STRING = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=dbThisDB;User ID=Username;Password=password;;Data Source=xxx.xxx.xxx.xxx"
Thanks again for your ideas.
May 15, 2012 at 12:02 pm
Are you 100% certain that the column that is throwing this error is the one you changed to varchar(max)? Is it possible that there are more characters than a field allows for one of the other columns?
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 15, 2012 at 12:29 pm
tim 37654 (5/15/2012)
Hi GuysThanks for the replies.
The code that does the insert is standard Dreamweaver CS5 insert code, no SP involved:
If (CStr(UploadFormRequest("MM_insert")) = "form1") Then
If (Not MM_abortEdit) Then
' execute the insert
Dim MM_editCmd
Set MM_editCmd = Server.CreateObject ("ADODB.Command")
MM_editCmd.ActiveConnection = MM_connMP_STRING
MM_editCmd.CommandText = "INSERT INTO dbo.tblBlog (BlogDt, BlogHead, UplBy, BlogTxt, BlogPic) VALUES (?, ?, ?, ?, ?)"
MM_editCmd.Prepared = true
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 135, 1, -1, MM_IIF(UploadFormRequest("BlogDt"), UploadFormRequest("BlogDt"), null)) ' adDBTimeStamp
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 201, 1, 250, UploadFormRequest("BlogHead")) ' adLongVarChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 5, 1, -1, MM_IIF(UploadFormRequest("UplBy"), UploadFormRequest("UplBy"), null)) ' adDouble
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 201, 1, -1, UploadFormRequest("BlogTxt")) ' adLongVarChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 201, 1, 250, UploadFormRequest("BlogPic")) ' adLongVarChar
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close
DW doesn't differentiate between different lengths of text field - it's just text, numeric or date.
The server is a W2008 Web Edition machine , pre-installed with SQL Server 2008, so I can't see that the driver is going to be an issue, is it?
The connection string is:
Dim MM_connConnection_STRING
MM_connConnection_STRING = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=dbThisDB;User ID=Username;Password=password;;Data Source=xxx.xxx.xxx.xxx"
Thanks again for your ideas.
Can you restart your web server?
May 15, 2012 at 12:54 pm
Can you try changing provider from SQLOLEDB to SQLNCLI in connection string?
May 15, 2012 at 1:00 pm
Try type 200 instead of 201.
May 15, 2012 at 2:29 pm
Hi guys
Thanks for all the ideas.
Yes, I am certain its only the long memo field which is doing this. To prove it I removed the insert behaviour for all form elements apart from the one I suspected and it still gave the same error.
One thing I should say is that my form also contains a file upload field so is enctype multipart/form-data - I can't see why that would have anything to do with it, but it may be relevant.
I tried restarting the web server, same problem.
I tried changing the data type to 200 instead of 201, same problem.
I tried setting the data provider to SQLNCLI instead of SQLOLEDB, got a different error:
ADODB.Recordset error '800a0e7a'
Provider cannot be found. It may not be properly installed.
I have also tried removing all the server behaviours in DW and starting again, same problem.
This is very frustrating!
Thanks again for all the ideas.
May 15, 2012 at 3:26 pm
Actually, I think that you want data type 203 instead of 201 (or 200).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 15, 2012 at 3:41 pm
Check the table you're inserting into for triggers, etc.... If the trigger is trying to do something with the very large column IT may be getting the truncation error.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 16, 2012 at 12:06 am
Thanks for all the ideas. I tried them all but none fixed the problem, which led me back to the conclusion that the data type change must have not worked perfectly. If you've eliminated every other possibility, the one that's left must be the problem…..
I decided this morning to try creating a new form and a new table, so I duplicated the old table minus data. To my surprise the data type of the field in the dupe table was showing as varchar(7999). So I went back to the original table and changed the data type again and this fixed the problem. Somehow SSMS seemed to be showing the data type as changed but the process was evidently not complete. Redoing it and then duping the table again showed that this time the data type was changed properly.
And that's fixed it.
It's very odd!
Many thanks to everyone for their ideas.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply