Solved

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

Posted on 2008-06-26
7
509 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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 200 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 300 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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now