SQL Pass-Through Mystery

[stanl]

This is not a Winbatch problem, but there are some good SQL members on this board and I need some advice. For years, whenever I have needed a report or some data manipulation involving multiple data sources, temp tables, INSERTS, UPDATES I would place all of the SQL steps into a table [memo field] and use a generic WB udf to step through the SQL and generate what I needed.

The other day, my udf failed (in Access) executing SQL that represented a basic SELECT INTO

FROM [ODBC Source].  The exact WB error was "ODBC Call Failed". And when I tried to execute directly from Access, it went into "Not responding..."  Bottom line: the data the query was selecting had grown exponentially. To the rescue: redesign the SELECT portion as a Pass-Through Query, then create an additional query to reference the pass-through and execute as a Make Table. Worked perfectly.  So: replace the original SQL in the memo with the second query. A little more on the logic of how the rows in the lookup table are arranged DROP SELECT INTO   <---- was failing, now replaced with Pass-through UPDATE Now here is the kicker.  The table is dropped. The pass-through is executed, but instead of moving to the UPDATE I get an SQL error. That Already Exists.  It is almost like the pass-through SQL creates the table then tries to create it again in the same statement. Since the WB udf is using basic ADO Connection.Execute(SQL) where the connection and command timeouts are set to 0, I'm wondering if the Pass-Through (which has it's own ODBC connection and Timeout properties set to avoid a pop-up asking for a DSN) is somehow conflicting. In other words, never use a pass-through in framework using a table of SQL statements.

[Deana]

Is it possible that the problem is that SQL Pass-Through queries are read-only?
SQL pass-through queries are read-only. The recordset returned by an SQL pass-through query is a snapshot, or read-only recordset. This behavior is by design.

Reference:
http://stackoverflow.com/questions/18898032/how-to-make-a-passthrough-passthru-query-editable
http://technet.microsoft.com/en-us/library/bb188204%28v=sql.90%29.aspx

[stanl]

Is it possible that the problem is that SQL Pass-Through queries are read-only?

I don't really know; but that is why the 2nd query uses the pass-through as a source then creates the local table. So assume the PT query is named "My Pass Thru Qry". The SQL for the 2nd query (the one that goes into the lookup table)  is   SELECT [My Pass Thru Qry].* INTO [Local Table].  And it works, the table is created and can be deleted or updated.  Just can't be part of a series of SQL Statements from my udf.

Like I said, not a WB problem.  I simply create the table prior to running the WB code and remove the entry from the lookup table that created it. And all works fine. No use throwing out baby with bathwater.

Guess I'll file it under Nice To Know....