CODEWITHSUNDEEP
vbaexcel
sat
sat

Reputation: 87

Query Timeout Expired (Run time error - '2147217871'), when I am trying to execute a SQL view in VBA?

The below VBA is the one I am working on and I am getting a

Query Timeout Expired error (Run time error - '2147217871')

when I tried to execute the below code:

Sub ConnectSqlServer2()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String

    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=adhoc23;" & _
                  "Initial Catalog=database1;" & _
                  "Integrated Security=SSPI;"


    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    ' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute("SELECT * FROM SQLViewName;")

    ' Check we have data.
    If Not rs.EOF Then
        ' Transfer result.
        Sheets(CC).Range("E5:G34").CopyFromRecordset rs
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

    End Sub

Please let me know how I can resolve the time out expired error.

Upvotes: 2

Views: 8018

Answers (1)

Dejan Dozet
Dejan Dozet

Reputation: 1009

It depends how many records you have in the related tables and also what is in the where clause (if you have any in the view). It can be that you are missing some indexes then.

if you are missing indexes use:

CREATE NONCLUSTERED INDEX [Ix_name] ON [dbo].[related table] 
(
    [column1] ASC, [column2] ASC....
)

This way you can extend timeout:

....

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim objCmd As New ADODB.Command   

' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=adhoc23;" & _
              "Initial Catalog=database1;" & _
              "Integrated Security=SSPI;"


' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
' Open the connection and execute.
conn.Open sConnString

objCmd.CommandText = "SELECT * FROM SQLViewName"  
objCmd.CommandType = adCmdText 
objCmd.CommandTimeout = 120 'seconds

' Connect to the data source.  
objCmd.ActiveConnection = conn

' Execute once and display...  
Set rs = objCmd.Execute  

' Check we have data.
If Not rs.EOF Then

....

Upvotes: 2

Related Questions