I can not run "SQL execute Procedure" by VBA: Query timeout expire, help me

Question

Dim cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
'Login vao SQL Server
Server_Name = "xxxxxx" ' Enter your server name here
Database_Name = "INPUT" ' Enter your database name here
User_ID = "sa" ' enter your user ID here
Password = "xxx" ' Enter your password here

Set cn = New ADODB.Connection
cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"
'---------------- lenh lay SQL data
Sheets("HT1").Select
Sheets("HT1").Cells.Delete
'Lay du lieu tu Procedure
SQLStr = "exec [INPUT].[dbo].[Giaodichkhachhang]"
rs.Open SQLStr, cn, adOpenStatic

=>>>>>>>>>>>>>>> Query timeout expire !!!!

'Xuat ra excel
With Sheets("HT1") ' Enter your sheet name and range here
    .Cells.Delete
    .Cells(2, 1).CopyFromRecordset rs
End With

'Lay them ca Header
For iCols = 0 To rs.Fields.Count - 1
    Sheets("HT1").Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
Sheets("HT1").Cells(1, 1).CopyFromRecordset rs
Columns.AutoFit
'Reset ve 0
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

If it's the proc code that's slow, you should add the source for [INPUT].[dbo].[Giaodichkhachhang] to your question. — Dan Guzman
– Dan Guzman, Commented Jan 23, 2020 at 16:04
Consider using a proper ADODB.Command. Set the CommandTimeout to 0. — Mathieu Guindon
– Mathieu Guindon, Commented Jan 23, 2020 at 16:04
That said any SQL procedure that runs for more than 10 seconds is suspicious. Consider making the stored procedure more efficient (review execution plan, missing indexes, etc.) instead of shrugging it off with an infinite (0) command timeout. — Mathieu Guindon
– Mathieu Guindon, Commented Jan 23, 2020 at 16:06
CommandTimeout. Setting the ConnectionTimeout to 0 won't change anything. — Mathieu Guindon
– Mathieu Guindon, Commented Jan 23, 2020 at 16:53

Bak · Accepted Answer · 2020-01-23 16:04:42Z

1

standard timeout is 30 secs You can extend this period by applying this line, which then gives you 120 secs cn.CommandTimeout 120

answered Jan 23, 2020 at 16:04

Bak

111 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

I can not run "SQL execute Procedure" by VBA: Query timeout expire, help me

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related