Strange Timeout Error

[S37N]

I've put together the following script, and it runs great on my local machine (w/ SQL Express), but when I change the SQL login information to our company server (SQL Server 2008), it kicks back a timeout error at the second .ExecuteNonQuery() line, which i've colered red.

The user credentials I'm using has the appropriate security to perform the delete, in fact, our tech services has gone as far as granting database owner rights to the account to troubleshoot the issue at hand. I've verified security by manually deleting records from within SSMS.

I'm still very new at this, so please excuse my ignorance if I'm missing something obvious.

Any assistance is greatly appreciated.

NOTE: the first query finds the unique records, and puts them in #tmp, the second query is joined to #tmp.

Module Delete

    Sub Main()
        Dim con As SqlConnection = New SqlConnection("Server = OVP-S-BPCDEVD;" & "Database = CMP;" & "User ID = bpcfactsales;" & "Password = factsales")
        Con.Open()


        Dim Yr, Mnth, fRng As String
        Yr = Year(Now)
        Mnth = Format(Month(Now), "00")
        fRng = Yr + Mnth + "00"


        Dim cmdA As SqlCommand = New SqlCommand("SELECT PRODUCT, SHIPTO, TIMEID, DATATYPE INTO #tmp " & _
                                                    "FROM tblFactSales " & _
                                                    "WHERE (BILLTO = 'INPUT_BILLTO') AND (BRANCHPLANT = 'INPUT_BRANCHPLANT') AND (FRTHANDLE = 'INPUT_FRTHANDLE') AND (DATATYPE = 'FORECAST') AND (RPTCURRENCY = 'USD') AND (TIMEID > '" & fRng & "') " & _
                                                    "AND (SIGNEDDATA >= - .01) AND (SIGNEDDATA <= .01) AND (SALESDATA = 'short_tons')", con)


        Dim cmdB As SqlCommand = New SqlCommand("DELETE tblFactSales " & _
                                                    "FROM tblFactSales RIGHT JOIN #tmp " & _
                                                    "ON tblFactSales.PRODUCT=#tmp.PRODUCT AND tblFactSales.SHIPTO=#tmp.SHIPTO AND tblFactSales.TIMEID=#tmp.TIMEID AND tblFactSales.DATATYPE=#tmp.DATATYPE", Con)


        cmdA.ExecuteNonQuery()
[COLOR=#ff0000]        cmdB.ExecuteNonQuery()[/COLOR]


        MessageBox.Show("Records Removed Successfully.", "Clear Complete", _
            MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
    End Sub


End Module

 

[jmcilhinney]

Have you tried increasing the CommandTimeout of the command? How long does the command take to execute against your local database? Have you checked the Errors of the SqlException to see if there's athing else going on? Have you used SQL Profiler to see what's going on on the database?

 

[S37N]

Have you tried increasing the CommandTimeout of the command? How long does the command take to execute against your local database? Have you checked the Errors of the SqlException to see if there's athing else going on? Have you used SQL Profiler to see what's going on on the database?

the timing on my local box is much quicker, as I'm only working with a subset (~250K vs ~130M). It times out after about 30-35 seconds against the server.

i'm still new at this, and I'm not familiar with the items you've listed.

 

[jmcilhinney]

Then the obvious next step is to search the web using the keywords I provided.

 

[S37N]

Then the obvious next step is to search the web using the keywords I provided.

yes, i know, and thank you.

will have to come back to it later. bigger fish to fry right now.

 

[S37N]

just wanted to say thank you jmcilhinney. just needed to increase the timeout. apparently takes a little longer across the network.