I have been trying to create a stored proc that will dynamically (1) drop a stage table, and then (2) recreate the same stage table. I would like to write both statements inside a BT/ET. I need to do this because if I get an error after the drop I want it to roll back. I can't seem to get around the requirement of having the ET after the first drop table ddl execution. This is one of the iterations I have gone through:
SET v_DDL = 'DROP TABLE ' || v_STG_DB || '.' || in_STG_TBL_NAME || ';';
SET v_TEMP = v_TEMP || '0a'XC || v_DDL;
SET v_TEMP = v_TEMP || '0a'XC || v_DDL_CREATE_TABLE;
Of course this is just a snippet. All of the variables are being populated correctly. I have also tried the following:
1. Creating a seperate SP to recreate the table and call it within the first proc.
2. Create a seperate SP for both the drop and recreate ddl and call from within this proc.
No matter what I do I can't get around the 3932:Only ET or null statement is legal after a DDL statement. I know I have limited info and code but just thought I would see if anyone has done this before and could lead me in the right direction.
it's impossible, each DDL must be submitted individually (and there's no REPLACE TABLE).
Assuming the definition changes (otherwise simply DELETE it) you might use following approach:
CREATE TABLE oldTableCopy AS oldTable WITH DATA AND STATS;
DROP TABLE oldTable;
CREATE TABLE newTable;
And define an exception handler, if there's an error:
DROP TABLE newTable;
RENAME oldTableCopy AS oldTable;
Or if only that new create might fail:
CREATE TABLE newTable_temp;
DROP TABLE oldTable;
RENAME newTable_temp AS oldTable;
Let me explain what I am trying to do in a little more detail. I have set up a STORED_PROCS database that requires a special password to create stored procs. I have designed this in order to allow certain ddl/dml statements to be executed without providing those rights to a master role. Whoever executes the stored procs will have an entry into an audit table tracking what is being done on a daily basis. This particular stored proc was developed to allow etl developers the ability to drop and recreate stage tables that have been locked due to a TPT Load failure. This way the batch can continue and they do not have the ability to drop all tables in the database. :)
I was faily certain I would not be able to put a BT/ET transaction statement around the code. Just wanted to see if something new exists that I do not know about. Thank you for your input! The current stored proc works well as long as the call statement does not contain bad characters as a result of a cut and paste out of a microsoft product. Under certain circumstances, the drop table statement would complete and then the bad character would cause the create table to fail. When the developer would execute it again it would tell them the table does not exist when trying to drop.
May just create a bteq script and call it from the etl tool.
We will figure something out...