Batch Separating SQL Statements with DBContext APIs

0

we believe we recently ran into a race condition with some of the t-sql being executed from our application and believe we found what is causing the issue.

We have t-sql being called at various times to preserve temp data that we need in the application. Part of this also includes dropping the table under certain circumstances, then creating it again. We have recently seen issues with querying the table experiencing exceptions such as:

Exception: System.Data.SqlClient.SqlException (0x80131904): There is already an object named 'PK_ConsoleData' in the database.
Could not create constraint or index. See previous errors.

and

 Exception: RowDataGateway.ExecuteSQLStatementFailedException: SQL Statement Failed ---> System.Data.SqlClient.SqlException: Invalid column name 'OriginationSourceHasSubJobLevelErrors'.
Invalid column name 'OriginationSourceHasJobLevelErrors'.
Invalid column name 'OriginationSourceHasSubJobLevelErrors'.
Invalid column name 'DataSourceHasJobLevelErrors'.

I was looking over Microsoft documentation and realized that a lot of our t-sql does things like drop a table and create it within the same batch.. per Microsoft:

DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.

Example code:

            IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'ConsoleData' AND TABLE_SCHEMA = N'DBO')
            BEGIN
                DROP TABLE DBO.[ConsoleData]
            END

            BEGIN
            CREATE TABLE DBO.[ConsoleData]
            (
                Column names..              
                CONSTRAINT [PK_ConsoleData] PRIMARY KEY CLUSTERED 
                (
                    [UserId] ASC
                )
            )
            END

Currently, I just decided to split the queries up into separate methods and just call each individually. I was trying to read up on batch separators but the GO command is not something we can use (it causing a syntax error and per Microsoft):

Applications based on the ODBC or OLE DB APIs receive a syntax error if they try to execute a GO command. The SQL Server utilities never send a GO command to the server.

Is there another way to call a some SQL from the application all at once, but specific parts executed as separate batches or is it better practice to just separate the calls into different methods?

tsql
dbcontext
sqlclient

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0