Text Insert Thru VB.NET Stored Proc Causes Severe Error

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).


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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

sneidigAuthor Commented:
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.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sneidigAuthor Commented:
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,
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:


sneidigAuthor Commented:
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,

sneidigAuthor Commented:
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,
sneidigAuthor Commented:
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)

sneidigAuthor Commented:
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.
PAQed, with points refunded (500)

Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.