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-BPC******;" & "Database = CMP;" & "User ID = ******;" & "Password = ******")
        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()
        cmdB.ExecuteNonQuery()

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

End Module

[TCarter]

(Guess it helps if I actually read the queries huh?)

You can't delete tblFactSales from tblFactSales.

You could DELETE * from tblFactSales WHERE something = somethingelse.

[dunfiddlin]

How does "DELETE tblFactSales FROM tblFactSales" make any sense at all, especially given that you're then trying to extract values from this table in the same command? The mystery isn't that this doesn't work on the server but that it 'works' on SQL Express. I suspect that in fact it doesn't, it's just that there's no error generated.

[S37N]

maybe I'm mistaken, but I thought "delete tblFactSales from tblFactSales" just means the same as DELETE * FROM tblFactSales. basically stating to DELETE all columns in the table.

(Guess it helps if I actually read the queries huh?)

You can't delete tblFactSales from tblFactSales.

You could DELETE * from tblFactSales WHERE something = somethingelse.

[S37N]

the first query finds the unique values in tblFactSales, inserts them to #tmp, and the second query selects all the related records to #tmp that reside in tblFactSales.

[dunfiddlin]

No 'maybe' about it! DELETE * means delete all values in all columns of the defined datarows. DELETE table means delete the whole table, schema and all so it clearly couldn't support a FROM and certainly not a selective one.

[dunfiddlin]

the first query finds the unique values in tblFactSales, inserts them to #tmp, and the second query selects all the related records to #tmp that reside in tblFactSales.

No. It doesn't!

[S37N]

changing it to "DELETE * FROM tblFactSales RIGHT JOIN #tmp" gives me a syntax error near "*"
changing it to "DELETE tblFactSales RIGHT JOIN #tmp" gives me a syntax error near RIGHT.

any suggestions?

not that it matters, but the only reason I put the name in there is because I kept getting the syntax errors. also; i've ran the test locally several times, and verified results, so it's hard for me to believe that you're right.

No 'maybe' about it! DELETE * means delete all values in all columns of the defined datarows. DELETE table means delete the whole table, schema and all so it clearly couldn't support a FROM and certainly not a selective one.

[dunfiddlin]

changing it to "DELETE * FROM tblFactSales RIGHT JOIN #tmp" gives me a syntax error near "*"

Yes it will. I allowed myself to be distracted by TCarter's reply. DELETE in SQL is a row based command so it automatically deletes *. It's just ... DELETE FROM tblFactSales etc.

[S37N]

Yes it will. I allowed myself to be distracted by TCarter's reply. DELETE in SQL is a row based command so it automatically deletes *. It's just ... DELETE FROM tblFactSales etc.

Yes, what will? I'm confused. I've tried that format, and it gives me a syntax error.

[S37N]

I was sure the delete needed to specify which table to delete from when a join was present. How would it know which table to delete?

[S37N]

Related...
http://stackoverflow.com/questions/4...lete-via-joins

[dunfiddlin]

Well that's probably down to the multiple join attempt!

DELETE FROM table RIGHT JOIN table2 ON table.col = table2.col WHERE something = somethingelse

You join on one primary key which identifies a row in one table as 'belonging to' a row in the other table. You delete based on values defined in the where clause. But please note that the join means you will be deleting the row from both tables which, I assume is not what you intend.

If you simply want to select a number of rows into a temporary table and then delete them from the source table then you simply have to use exactly the same FROM and WHERE statements with which you selected with a DELETE command. I see no point in the tangled web your second query has become!

[S37N]

let me backup a little, and explain why it looks strange...

this is a table from a cubed database (OLAP, I believe it's called/ see SAP BPC for more info), not a standard relational database. without getting into too much detail, there are records in the table, that relate to other records in the same table. There are base records (data sent by users), and logic records (records produced through code).

Here's how it works "in my head"...
my first query finds all of the "base" records that have short_tons >= -.01 and <=.01, and inserts them into #tmp table (unique identifiers are product, shipto, time, and datatype (these are columns in the tblFactSales table).

my second query uses the unique ID list from #tmp, and is joined back to tblFactSales so it can grab ALL of the related records.

I need to grab all of the related records, not just the short_ton records.

[dunfiddlin]

So where does DELETE come into this? Time can't be a unique identifier because there is always a possibility (no matter how remote) that there may be duplicate values and unless they've invented an infinite number of types I really can't see how datatype can possibly be. In fact, if you have described the database correctly, it looks like there are no unique identifiers at all. As far as I can see any attempt to use such a database purely with standard SQL is doomed to failure from the get go.

I mean how do related records in the same table make any sense anyway? Do values get repeated? Is there a subset of columns filled in one record and a completely different subset in others? SQL is designed for plain and simple tables in relation to each other which can be joined on a single identifying common denominator. I just don't know whether it can begin to cope with concepts such as inner relations and joining tables to subsets of themselves.

[S37N]

turns out, I just needed to extend the CommandTimeout setting. running over the network (on the larger db) was taking longer than 30 seconds, which is the default.