tSQLt seems to have missed an update to the database schema

38 views
Skip to first unread message

Jan van der Ven

unread,
Sep 30, 2020, 5:36:57 AM9/30/20
to tSQLt
Hi group,


I added a column to a table today, and after that one of the tests that uses that column errored out with: (Error) Column name or number of supplied values does not match table definition.[16,7]{dbo.EndOfShift,35} (There was also a ROLLBACK ERROR --> The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.{tSQLt.Private_RunTest,161})

The test tests EndOfShift and that procedure can be executed without errors.

So now I think the schema update did not make it into the innards of tSQLt. Is there something that needs to be done whenever a schema changes?


Jan

Dmitrij Kultasev

unread,
Sep 30, 2020, 5:42:17 AM9/30/20
to ts...@googlegroups.com
Try to isolate your error. You quite easily do that by wrapping the source of the test procedure into the transaction something like:

BEGIN TRANSACTION
-- put the source code of the procedure here (without CREATE PROCEDURE ... just the code itself)
ROLLBACK

Make sure it is still failing, then comment out the code line by line starting from the bottom of the code. In that case you'll be able to find the exact place where it is failing.

1 gotcha you can hit is that if it fails, it won't reach to the ROLLBACK block and transaction would remain open, so you'll need to execute ROLLBACK manually.

--
You received this message because you are subscribed to the Google Groups "tSQLt" group.
To unsubscribe from this group and stop receiving emails from it, send an email to tsqlt+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/tsqlt/37b010cd-0a2f-4de0-a0d2-6165089fc9d3n%40googlegroups.com.

Jan van der Ven

unread,
Sep 30, 2020, 7:24:12 AM9/30/20
to tSQLt
Hi,


Thanks for the quick reply.

I'll provide some more information.
In the stored procedure, I create a table var, insert some records, and finally call SELECT * FROM @tablevar to return the results.
It fails on the SELECT statement, but only when the procedure is run by tSQLt.
I then added all the column names, but it still failed.

Then I decided to ask for help.

Ed Elliott

unread,
Sep 30, 2020, 7:26:41 AM9/30/20
to ts...@googlegroups.com
When you call FakeTable the existing table is examined and then the new table created.

Are you doing something like changing the schema after calling FakeTable?


From: ts...@googlegroups.com <ts...@googlegroups.com> on behalf of Jan van der Ven <jhvan...@gmail.com>
Sent: Wednesday, September 30, 2020 12:24:12 PM
To: tSQLt <ts...@googlegroups.com>
Subject: Re: tSQLt seems to have missed an update to the database schema
 

Dmitrij Kultasev

unread,
Sep 30, 2020, 8:15:34 AM9/30/20
to ts...@googlegroups.com
Can you show the source code of your test?

Jan van der Ven

unread,
Oct 1, 2020, 7:48:45 AM10/1/20
to tSQLt
Found it.
I switched the order of 2 columns in the test table.
So when I did:
    INSERT INTO #result
    EXEC dbo.EndOfShift
the error came.

Thanks for helping out, and stupid me for looking at the real stored procedure to see where it went wrong instead of in the test itself.
Reply all
Reply to author
Forward
0 new messages