Text Insert Thru VB.NET Stored Proc Causes Severe Error

Posted on 2003-11-11
Medium Priority
Last Modified: 2011-09-20
I have an ASP.NET application powered by VB.NET that I'm using to insert a large string of text into a SQL Server 2000 table. The column the text is being inserted into is datatype 'text'. There is a textbox on the ASP.NET page. When my user enters text (copy for an html email) into the textbox and hits the submit button, the contents of the textbox are inserted via a stored procedure into the the appropriate table in a SQL Server 2000 database.

I'm working with two SQL Servers: SQL Server A and SQL Server B. SQL Server A is physically located ~20 feet from the machine hosting the ASP.NET application. SQL Server B is located ~2000 miles away from the machine hosting the ASP.NET application.

When my connection string points to SQL Server A, the stored procedure flawlessly inserts the large block of text into the database. When my connection string points to SQL Server B, the stored procedure 'fails' when I add the same block of text and I catch the following error "A severe error occurred on the current command. The results, if any, should be discarded." and sometimes that error is accompanied by this error "General network error. Check your network documentation."

The only thing I change is the connection string. The database structures, including table columns and datatypes and stored procs, are exactly the same on both Server A and Server B.

Also, if I cut the large bit of text in half or just try to upload one line of text, the ASP.NET application will successfully insert the text into Server B. So the problem may have something to do with the size of the text block (I estimate the text is about 7k-15k worth of data).

Also, if I execute the stored procedure via Query Analyzer using the exact same parameters that I pass thru using the ASP.NET application, the sproc executes and the data is inserted.

Any ideas what I'm missing here? The errors aren't too informative and my google and yahoo searches aren't yielding much info. I can post the sprocs or any of the VB.NET code, though I don't think that's the problem since the code works if I enter a smaller block of text and works on one server and not the other. I'm choosing to award 500 points purely because my question is so long even though for some people the answer might be obvious (I hope).


Question by:sneidig

Expert Comment

ID: 9730828
I had experience a similar problem and the solution was just to encode the html tags. It would be easy to check whether you experience the same problem (though i doubt u do) or not, by posting a large text with no html tags.
If it works then just encode the textbox.text when you post it.
Good Luck
LVL 26

Expert Comment

ID: 9731610

The ADO Connexion Object has a CommandTimeOut property
Setting it to 0 turns it to infinity, and you can set it to any value in secs
eg 90 stands for one minute and a half

I don't know ADO.NET very well, but maybe you'll find this property
for the SQLConnection Object if you use System.Data.SQLClient

Just my two cents ...


Author Comment

ID: 9732678
Thanks for your replies Orbital and Hilaire!

Orbital - Earlier we attempted to upload 100 characters at a time, no html tags, to the textbox to insert them into the database. We found that entering around 4000 characters causes the insert to fail and generate the errors I mentioned above. Less than 4000 characters is OK but more than that and we get errors. Also, we can include HTML tags in the text and it will work as long as the text is under 4000 characters.

Hilaire - The SQLCommand object does indeed have a CommandTimeOut property. I set the timeout value for the command object I'm using to 0 and tried again but no luck. The problem doesn't seem to be a timeout issue because the page reloads immediatly after I hit the submit button whether the insert succeeds or fails. The timeout problems I've run into in the past have always taken a long time before the page reloads. With this it's immediate.

Thanks you both for your replies. Let me know if you think of anything else.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 9732758
Hilaire - I just read your post again and realized you were talking about the connection object and not the command object.

With ADO.NET you create a SQLConnection object and pass a connection string to it. Then you create a SQLCommand object and pass the newly created SQLConnection object to it.

The SQLConnection object does have a readonly connectiontimeout property, but CommandTimeOut is a property of the SQLCommand object.

Basically the same thing, I just wanted to clarify because my comment above probably didn't make complete sense.

Thanks Again,

Expert Comment

ID: 9733092
Is it possible that your datafield length (on the second server) to be set to a number around 4000?
Why don't you try to run a simple alter query againts your table such as:



Author Comment

ID: 9733144
That's a good idea. I'm not sure that's the solution though. Note that if I execute the same stored procedure directly thru Query Analyzer, the stored procedure executes successfully. The exact same stored proc with the exact same data called by the ASP.NET application fails.

So if there was some issue with the datatype, wouldn't the stored proc executed thru Query Analyzer fail as well?

I noticed you suggested the datatype be a large varchar (varchar(7200)). I have the datatype set to text. Maybe there's something I don't know about the text datatype and I should be using a large varchar instead.

I'll expirement with these ideas and let you know if anything works.

Thanks for replying,


Author Comment

ID: 9733397
I just finished trying changing the datatype from text to varchar(7200) and then I tried it as ntext. Both changes yeilded the same result: error - A severe error occurred on the current command. The results, if any, should be discarded.

Thanks for trying though,

Author Comment

ID: 9751214
Allright we were able to solve this problem, or at least find a suitable workaround. Remember how I wrote that I could execute the stored procedure in Query Analyzer? Well the trick was not to add parameters to the command object, but instead set the value of the command objects 'commandtext' property to a sql statement that executes the stored procedure ( exec yourProc 'param1', 'param2', 3, 'param4' ). For some reason setting the command type to 'storedprocedure' and adding parameters to the command object was causing the unusual error I documented above.

Thanks again for you help. I guess I don't get to hand out any points this time, but thank you for your efforts. This is my first post on experts-exchange so if should have handed out points and didn't please let me know. Be honest ;o)


Author Comment

ID: 11506860
I would have "closed" this question on 11/14/03 on the day of my last post, but I don't see any functionality in my Admin page or on this page that allows me to do so. No one answered my question, I ended solving the problem but I can't award points to myself. So I just left it open because I don't see any other options. Am I missing something? Maybe when no answers are provided you just wait three weeks and have admins close the topic.

I have deleted questions in the past if I solved the problem before anyone posted a response. But after the first response is posted it seem our only other choice to get some closure is to award points. TheLearnedOne, please let me know what you think.

Accepted Solution

modulo earned 0 total points
ID: 11533207
PAQed, with points refunded (500)

Community Support Moderator

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question