Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Text Insert Thru VB.NET Stored Proc Causes Severe Error

Posted on 2003-11-11
12
Medium Priority
?
404 Views
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).

Thanks,
Scott




0
Comment
Question by:sneidig
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 

Expert Comment

by:Orbitalgr
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
Orbital.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9731610
Hi,

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

Hilaire
0
 

Author Comment

by:sneidig
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.

Scott
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:sneidig
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,
Scott
0
 

Expert Comment

by:Orbitalgr
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:

ALTER TABLE  TableName
ALTER COLUMN ColumnName
varchar(7200)


0
 

Author Comment

by:sneidig
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,
Scott

0
 

Author Comment

by:sneidig
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,
Scott
0
 

Author Comment

by:sneidig
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)

Thanks,
Scott
0
 

Author Comment

by:sneidig
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.
0
 

Accepted Solution

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

modulo
Community Support Moderator
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

618 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