?
Solved

CLR stored procedure stops randomly(time out?) when run from client application.

Posted on 2008-06-26
7
Medium Priority
?
542 Views
Last Modified: 2008-06-26
Hello,

I have a clr stored procedure that inserts rows in a table. When run from sql server itself, the sp inserts every row correctly(about 6500 rows). When it is run from the client application, the sp stops at around 1500 rows inserted(about 30 seconds), which leads me to think that there's a timeout somewhere.

Any idea why it does that?

Thanks alot.
0
Comment
Question by:PatricePaquette
  • 3
  • 2
  • 2
7 Comments
 
LVL 8

Expert Comment

by:Randy Peterson
ID: 21876903
The timeout is in your connection string.  Defaulted to 30 seconds.
0
 
LVL 7

Expert Comment

by:60MXG
ID: 21876926
Try to set the time longer in your config file so it would be something like this.  Hope that help!

<settings>
       <add key="connectionToMyDB" value="server=192.168.1.50; database=myDataBase; uid=myId; pwd=myPassWord; Connection Lifetime=6000;">
</settings>
0
 

Author Comment

by:PatricePaquette
ID: 21876935
From what I understand, the timeout for the connection is the time it waits until the connection is made. Since 1500 rows are are added, the connection has obviously been made and shouldn't timeout.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 8

Expert Comment

by:Randy Peterson
ID: 21876964
The timeout happens if the response does not come back after a certain time.  On your insert, you don't get a response back until all rows are inserted. That is why you get the timeout, it thinks it lost connection to the server because it sent the command, but after 30 seconds, it never got a response back so it timed out.  Add the following code to your connection string and try it (Notice timeout in seconds):  connection timeout=500

Make sure you give it enough time to complete.
0
 
LVL 7

Assisted Solution

by:60MXG
60MXG earned 800 total points
ID: 21876975
How about the setting on SQL 2005?  I meant Connections setting when you have "Allow remote connections to this server" checked.  Try to set the "Remote query timeout" to 0 see if that will fix your problem.
0
 

Author Comment

by:PatricePaquette
ID: 21877047
Hey,

First, thanks for answering.

I have tried both suggestions for the client side but the problem still persists. I will try your suggestion on the server side and will tell you if it works.

Thanks
0
 
LVL 8

Accepted Solution

by:
Randy Peterson earned 1200 total points
ID: 21878192
You might want to try this in your code (syntax is C#)-  It looks like this is what I had to do to get the sproc longer time before the timeout.  This is in seconds, so it sets the timeout to 30 minutes in this example.

myCommand.CommandTimeout = 1800;
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

589 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