• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

Prevent Time out error from happening?

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
Schuttend
Asked:
Schuttend
  • 2
  • 2
  • 2
  • +1
1 Solution
 
FER_GCommented:
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
 
3_SCommented:
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
 
SchuttendAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Anthony PerkinsCommented:
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
 
SchuttendAuthor Commented:
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
 
3_SCommented:
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
 
Anthony PerkinsCommented:
Yes, you can from code, but it is beyond the scope of this question.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now