I'm using VB6 and SQL Server 2000.
I've got a problem with an insert statement that seems to be randomly failing. Its job is to copy everything from one table into another, removing duplicates using WITH IGNORE_DUP_KEY ON [PRIMARY]. Then my code copies the records back to the original table, duplicate free.
This is my VB code:
strSQL = "INSERT CLEAN_MILE_DUPES (" & vbCrLf _ & " CASE_IDENTIFIER" & vbCrLf _ & " ,DOCUMENT_TYPE" & vbCrLf _ & " ,DOCUMENT_NUMBER" & vbCrLf _ & " ,MILESTONE" & vbCrLf _ & " ,MILESTONE_DATE_TYPE" & vbCrLf _ & " ,MILESTONE_DATE)" & vbCrLf _ & "(SELECT CASE_IDENTIFIER" & vbCrLf _ & " ,DOCUMENT_TYPE" & vbCrLf _ & " ,DOCUMENT_NUMBER" & vbCrLf _ & " ,MILESTONE" & vbCrLf _ & " ,MILESTONE_DATE_TYPE" & vbCrLf _ & " ,MILESTONE_DATE " & vbCrLf _ & " FROM CASE_MILESTONE_TEMP)"
The SQL works in TOAD and Query Analyzer, every time. The code works in my program, on the machine I'm using right now, also every time. I've got two other machines I'm using for testing, with their own databases, and this code fails in the program, but works in TOAD or Query Analyzer.
As I'm debugging this today, it is failing. I removed the second pair of parentheses from the code above (I saw an insert statement here today while trying to find the answer, and it didn't have the select statement in parentheses) and it worked. I am now ripping my hair out.
Either form works in TOAD or QA. It would seem that either works in VB, *unless it doesn't feel like working*. Does anyone have any idea about this?