Can't create temp table in SQL Server 2005

2008-03-12
2013-04-25
  • I am trying to create and populate a temp table within SQLUnit against SQL Server 2005.  However, the lifetime of the temp table appears to be the the duration of the <stmt> tag.  It does not appear that the the value of the transaction-support attribute within <connection> tag affects the outcome.  (I have tried "on", "off", and "implicit", which I thought the last two would work.)

    In the sample code below, both of the temp tables are created (I can observe this in the SQL Profiler).  However, when I try to access #tblTest within the second <test>, SQL Server supplies me with the error "Invalid object name #tblTest" (Actually, the second <sql> block in the <setup> also gives me the error, SQLUnit isn't reporting it; it does show up in the Profiler.)

    Is transaction support or connection lifetime (temp tables are limited by the lifetime of the connection) different under SQLUnit 5.0? 

    Sample Test Code:

    <?xml version="1.0"?>
    <!DOCTYPE sqlunit SYSTEM "file:docs/sqlunit.dtd" [
        <!ENTITY jdbc-url SYSTEM "file:test/mssqlserver/jdbc-url.txt">
    ]>
    <sqlunit>
        <!-- ******************************* //-->
        <!--  Connection set-up information  //-->
        <!-- ******************************* //-->
        <connection transaction-support="off" server-name="microsoft_sql_server">
            <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
            &jdbc-url;
            <user>sqlunit</user>
            <password>sqlunit</password>
        </connection>
        <!-- ******** //-->
        <!-- Set up   //-->
        <!-- ******** //-->
        <setup>
            <!-- Create a temp table for testing -->
            <sql>
                <stmt>
                    create table #tblTest
                    (
                        id              integer    identity(1,1),
                        intvalue        integer
                    )
                </stmt>
            </sql>
            <!-- Load temp table with some data -->
            <sql>
                <stmt>
                    insert into #tblTest
                        (intvalue)
                    values
                        (1)
                </stmt>
            </sql>
        </setup>
        <!-- *****************    //-->
        <!-- Tests to perform    //-->
        <!-- *****************    //-->
        <test name="Test SQLUnit: Temp table test" >
            <sql>
                <stmt>
                    create table #MyTempTable
                    (
                        id              integer    identity(1,1),
                        intvalue        integer
                    )
                    insert into #MyTempTable
                        (intvalue)
                    values
                        (1)
                </stmt>
            </sql>
            <result>
                <updatecount>1</updatecount>
            </result>
        </test>
        <test name="Test SQLUnit : Temp Table Test using #tblTest">
            <sql>
                <stmt>
                    select    *
                    from        dbo.#tblTest
                </stmt>
            </sql>
            <result id="1">
                <resultset id="1">
                    <row id="1">
                        <col id="1" type="INTEGER">1</col>
                    </row>
                </resultset>
            </result>
        </test>
        <!-- *******    //-->
        <!-- Cleanup    //-->
        <!-- *******    //-->
        <teardown>
            <!-- Drop the temporary table.  (Not required, since it will be done automatically.) -->
            <sql>
                <stmt>
                    drop table #tblTest
                </stmt>
            </sql>
        </teardown>
    </sqlunit>