Strange Timeout Error

S37N

Member
Joined
Jul 12, 2012
Messages
8
Programming Experience
1-3
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.

Code:
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

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,171
Location
Sydney, Australia
Programming Experience
10+
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

Member
Joined
Jul 12, 2012
Messages
8
Programming Experience
1-3
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.
 

S37N

Member
Joined
Jul 12, 2012
Messages
8
Programming Experience
1-3
just wanted to say thank you jmcilhinney. just needed to increase the timeout. apparently takes a little longer across the network. :p
 
Top Bottom