Solved

Prevent Time out error from happening?

Posted on 2008-10-27
7
455 Views
Last Modified: 2011-10-19
I have a stored procedure which takes long to complete, because it does a bulk import on a very large file. If I call this stored procedure I get a time out error, but the procedure is still running correctly.
Is there any way to prevent this?

regards,
Dennsi
timeout.jpg
0
Comment
Question by:Schuttend
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 7

Expert Comment

by:FER_G
ID: 22816705
Do you prove to change the time out of the command/connection object?
If you set the property to zero, ADO will wait indefinitely until the execution is complete.
Make sure the provider and data source to which you are writing code support the CommandTimeout/ConnectionTimeout  functionality.

FerG
Saludos
--
Ing. Fernando D. Giletta
San Fco. Cba. Argentina.
0
 
LVL 10

Expert Comment

by:3_S
ID: 22816719
on your connection you can set the property CommandTimeout.
Can you give a piece of your code?  Or at least some more info on what you are using to make the connection? ADO, something else?

Do you know how long you stored procedure normally should take?  Can it be optimized? (perhaps turning of full logging on the database for the bulk import?, is this on time import?)
0
 

Author Comment

by:Schuttend
ID: 22817571
Please see attached code below. What would be the correct syntax??

Regards,
Dennis
Private Sub Command3_Click()
 

' for connection strings to sql express, see: http://www.connectionstrings.com/?carrier=sqlserver2005

'ado.Open "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=master;Uid=sa;Pwd=d27021973;"

Dim cn As ADODB.Connection

    Dim rsCheck As ADODB.Recordset

    Dim cmd As ADODB.Command

    

       Dim rsRecCount As Integer  ' For counting records in a record set

    

    Set cn = New ADODB.Connection

    cn.ConnectionString = "Driver={SQL Server Native Client 10.0};Server=SCHUTTENVISTA\SQLEXPRESS;Database=Regus1;Uid=sa;Pwd=d27021973;"

    cn.Open

    

    Set cmd = New ADODB.Command

    cmd.ActiveConnection = cn

    cmd.CommandType = adCmdStoredProc

    cmd.CommandText = "importeren5"

 cmd.Execute
 

cn.Close

'Call Create_DataBase

'Call Create_Table

'Call Create_Table_Trading

'Call Add_Record1
 

End Sub

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 22818349
If you have optimized your stored procedure as much as you are capable, than you may have to resort to modifying the default value of 30 seconds for the CommandTimeout property for the Command object.  You ahve a couple of options:
A.  Increase it to a reasonable amount of time or if you like living dangerously
B.  Change it to 0 and be prepared to wait an indefinite amount of time.

As in:

    Set cmd = New ADODB.Command
    cmd.CommandText = "importeren5"
    cmd.CommandTimeout = 120
    cmd.ActiveConnection = cn
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "importeren5"


But chaging the CommandTimeout should only be done as a last resort.  Unfortunately without seeing your code, it is impossible to make any further recommendations.
0
 

Author Comment

by:Schuttend
ID: 22819740
I doubt if the code can be optimized. It's a simple bulk import into sql server. It's about 1 GIG of data and this just takes a while ,about 2-4 minutes.
Is there any method to cancel a called procedure while it's running?
ALTER PROCEDURE [dbo].[importeren5] 

	

AS

    -- Insert statements for procedure here

	drop table CSVtest99

	create Table CSVtest99

	(	Datum  VARCHAR(30),

	Signal VARCHAR(30),

	Equity VARCHAR(30)

	

	)

	

	BULK insert csvtest99

	FROM 'c:\structure\test.txt'

	WITH

	(

	FIELDTERMINATOR =',',

	ROWTERMINATOR = '\n'

	)

Open in new window

0
 
LVL 10

Expert Comment

by:3_S
ID: 22824718
I don not know of a method to cancel from the code side.  But the process can be killed on the sql server to cancel.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22827585
Yes, you can from code, but it is beyond the scope of this question.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

863 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

20 Experts available now in Live!

Get 1:1 Help Now