none
Linked Server T-SQL RRS feed

  • Question

  • Dim strSDK As String = "DBCC TRACEON(8765) SELECT [CUST_NAME],[CUST_ADDR_LINE_01],[CUST_ADDR_LINE_02],[CUST_CITY],[CUST_STATE],[CUST_ZIP],[TEN_DIGIT_PHONE_NUM],[INS_CARRIER_NAME],[REFERRAL_NAME] FROM OPENQUERY(MESTALINK, 'SELECT [CUSTOMER].[CUST_NAME],[CUSTOMER].[CUST_ADDR_LINE_01],[CUSTOMER].[CUST_ADDR_LINE_02],[CUSTOMER].[CUST_CITY], [CUSTOMER].[CUST_STATE],[CUSTOMER].[CUST_ZIP],[CUSTOMER].[TEN_DIGIT_PHONE_NUM],[INSURANCE].[INS_CARRIER_NAME],[REFERRAL].[REFERRAL_NAME] FROM [CUSTOMER] LEFT JOIN [INSURANCE] ON [CUSTOMER].[PRIMARY_INS_CODE] = [INSURANCE].[INSURANCE_CODE] LEFT JOIN [REFERRAL] ON [CUSTOMER].[REFERRAL_CODE] = [REFERRAL].[REFERRAL_CODE] WHERE [CUSTOMER].[CUSTOMER_NUMBER] = ''" & Me.txtsearch.Text.ToUpper.Trim & "''')"

                Dim MyCommand1 As OleDbCommand = New OleDbCommand(strSDK, objConnection)

                If Me.BackgroundWorker1.CancellationPending = True Then
                    e.Cancel = True
                    Me.BackgroundWorker1.CancelAsync()
                    Return
                End If

                ''Handle the connection timeout
                Try
                    MyCommand1.CommandText = strSDK
                    MyCommand1.CommandType = CommandType.Text
                    MyCommand1.CommandTimeout = 60
                    DA.SelectCommand = MyCommand1
                    DA.Fill(DS)
                    ''Make asynchronous function call to Form's thread to populate the data from the dataset
                    Me.BeginInvoke(CallMestamedData)
                    ''Bind the remaining records from an actual SQL Server database
                    Me.BeginInvoke(CallBindRecords)
                Catch ex As Exception
                    ''A Timeout occurred!!
                    MsgBox("No Record(s) Found Based On Search Critiera!", MsgBoxStyle.Information)
                Finally
                    ''Dispose the MyCommand1 object
                    MyCommand1.Dispose()
                    MyCommand1 = Nothing
                End Try


    When I enter a value that doesn't return any records, the code simply runs for about 5 minutes until the default connection timeout for the ODBC driver. Is there any way to either force a timeout or quickly determine that there are no matching records based on the entered search pattern?


    Thanks,

    K. Lewis
    Wednesday, October 3, 2007 8:04 PM