User Guide

 

This reference contains an explanation of each of the public tables, views, stored procedures and functions provided by tSQLt.

 Posted by at 12:20 am
25 comments
abhit
abhit

Is there any way to use tSQLt with Visual Studio?

franzR
franzR

Calling exec tSQLt.FakeTable on a table with a user defined data type that is based on a char type aborts with error message "Msg 452, Level 16, State 1, Line 1 COLLATE clause cannot be used on user-defined data types."


Changing the invokation of tSQLt.Private_GetDataTypeOrComputedColumnDefinition in sproc [tSQLt].[Private_CreateFakeOfTable] from

"CROSS APPLY tSQLt.Private_GetDataTypeOrComputedColumnDefinition(c.user_type_id, c.max_length, c.precision, c.scale, c.collation_name, c.object_id, c.column_id, @ComputedColumns) cc"

to "CROSS APPLY tSQLt.Private_GetDataTypeOrComputedColumnDefinition( c.user_type_id, c.max_length, c.precision, c.scale, case when c.system_type_id=c.user_type_id then c.collation_name end,c.object_id, c.column_id, ComputedColumns) cc" helped.


Steps to reproduce:

create database TestDb;

go

alter database TestDb set trustworthy on;

-- install tSQLt

go

use TestDb;

go

create type dbo.mytype from varchar(20) not null;

go

create table dbo.MyTable(c dbo.MyType);

go

begin transaction;

exec tSQLt.FakeTable @TableName = N'dbo.MyTable';

rollback transaction;


Could you update the source of [tSQLt].[Private_CreateFakeOfTable] accordingly?









Debdutta
Debdutta

I need to execute set of code to set the environment to run tSQLt test cases. After execute all test cases, I need to run set of code to bring back the environment. I can do this by writing two procedures Start and End and call them from the First Test Case and Last Test Case. If I do this, then I have to do some trick to run specific/single test case. In that case, I have to call Start and End sp from each for the test cases. However, I don’t want that. Rather, I am looking for something like, two tSQLt interfaces “main” and “exit” which should be implement for every test class. The tSQLt.Run will execute the “main” at the beginning of the execution and “exit” at the end of the execution. If I run an individual tSQLt test case instead of tSQLt.Run, in that case, the framework will call the “main” and “exit”. I should not allow calling the “main” and “exit” from individual tSQlt Test Cases. Please let me know if this facility already implemented in tSQLt otherwise, let me know is there any way to do this.

Ganesh
Ganesh

Hi, Can we get documentation of TSQL framework the in PDF format? Ganesh

Jeff Schanhals
Jeff Schanhals

Can an individual test take a parameter? For example, we have login/logout functionality where we store the user in a table. I write a unit test to verify this. Then, I need to go to another unit - which also requires that the user is logged in. Rather than rewriting (or copying/pasting, faking data), I would like to call the previous unit test to login the user(variable), then perform the rest of the tests. Is this possible? There also may be different levels of accessiblity depending on the user name, but really only want to write a single unit test (again to avoid copy/pasting) that can verify this based on the user name passed.

Wondering
Wondering

Is there any way to change the format that tSQLt displays after completing a test. For example, making each failure message to have a blank line beneath it?

G. Vlad
G. Vlad

I recently started using tSQLt and I really like it. I have a little problem when I am trying to test a stored procedure containing "ALTER TABLE tablename ADD CONSTAINT ...". I got the error: [testclass].[testcase] failed: (Error) Could not create constraint. See previous errors.{[name of the stored proc called in the testcase],304} If I removed this line of code from the stored proc, the test case is running sucessfully, Is it supposed to be supported by the framework? Is there any workaround? Thanks for your help.

Hamidreza
Hamidreza

Hi. How can i integrating tsqlt with Mstest runner on Visual studio 2012 ?

Alin Hanghiuc
Alin Hanghiuc

I think ResultSetFilter should be included on this page, maybe under "Test Creation and Execution"? Thanks.

Mikah
Mikah

I'm looking for a way to run tests and persist results in my own custom fashion. tSQLt smartly rolls back every transaction (being a test suite, this makes sense). I've tried BEGIN TRAN/COMMIT TRAN inside my procedures for commands I don't want rolled back - like additional logging, but I realize those transactions begin as a child of the tSQLt parent transaction. Any suggestions?

Guru
Guru

Method "AssertResultSetsHaveSameMetaData" Doesnt seem to work on SQL 2008 R2 / .NET framework 4. I get below error: Msg 6522, Level 16, State 1, Procedure AssertResultSetsHaveSameMetaData, Line 0 A .NET Framework error occurred during execution of user-defined routine or aggregate "AssertResultSetsHaveSameMetaData": System.Data.SqlClient.SqlException: tSQLt.Failure System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at tSQLtCLR.TestDatabaseFacade.assertEquals(String expectedString, String actualString) at tSQLtCLR.MetaDataEqualityAsserter.AssertResultSetsHaveSameMetaData(SqlString expectedCommand, SqlString actualCommand) at tSQLtCLR.StoredProcedures.AssertResultSetsHaveSameMetaData(SqlString expectedCommand, SqlString actualCommand) .

@sqlity
@sqlity moderator

@abhit I am not currently aware of a good visual studio project integration. For SSMS you can take a look at Red-Gate's SQLTest.

@sqlity
@sqlity moderator

@Debdutta  tSQLt offers a SetUp procedure capability. If a test class contains a procedure called SetUp it will automatically be called at the beginning of each test.


Each test case is wrapped in a transaction that is rolled back at the end of the test. Therefore there should be no need for a teardown procedure as all changes that happen during the test (including the SetUp procedure) are undone automatically.


That being said, I recommend not using the SetUp capability either. There are two issues with it. First, it obfuscates what is going on in the test cases, as there is no indication within the test code that something else is happening. Second, there is usually at least one test case in a test class that needs a different "setup". I recommend instead to create one or more setup procedures in the test class and give them names that make their intent obvious, like [fake relevant tables]. Then you can call the appropriate "setup" procedure explicitly at the beginning of each test.

Sebastian
Sebastian

Hi Ganesh. We are currently working on a book about tSQLt. Stay tuned.

Sebastian
Sebastian

Currently tSQLt does not support parameterized tests. (It is on the backlog however.) Additionally, you do want all your tests to be independent of each other. That leads to only one (or very few) tests failing when a new defect is introduced. That in turn makes it easy to figure out what caused the problem. If you have dependent tests, one failure could lead to many failing tests of which the majority likely does not even touch the broken piece of code. That makes troubleshooting unnecessarily hard.

Sebastian
Sebastian

You are right. There are a few other ones missing too. I'll probably add a tools section for those.

Sebastian
Sebastian

You could use tSQLt.NewConnection to insert data into a table outside the current transaction. Be very careful however to not block yourself, as that will cause an undetectable deadlock. Here is a demo: CREATE TABLE dbo.tst(i INT) BEGIN TRAN EXEC tSQLt.NewConnection 'INSERT dbo.tst(i) VALUES(13);'; ROLLBACK SELECT * FROM dbo.tst; DROP TABLE dbo.tst;

Mark Wojciechowicz
Mark Wojciechowicz

Guru, I have not used this testing framework before, but I can validate that I am getting the same error when the metadata is not the same. This is executing on sql 2008 r2 as well. When the metadata is the same, no error is thrown. The guide does say that an error will be raised when the schema does not match, but I would expect the message to have information about what the differences were. I would be happy to contribute to this project to get it working. Mark

Wondering
Wondering

Then does anyone know what the procedure tSQLt.SetTestResultFormatter is used for, or more importantly how to use it correctly?

Wondering
Wondering

How do you make sure you don't block yourself?

Sebastian
Sebastian

This is an extension point that allows the output to be formatted in different ways. There is for example an XML formatter that you can use to generate JUnit compatible output instead of human readable. We have not stabilized that piece yet. that is, why it is not documented yet. The goal is to allow external formatters to be installed through this as extension to the existing ones. However, you can get the XML output through other means. See for example http://tsqlt.org/177/integrating-tsqlt-with-cruise-control/

Wondering
Wondering

I looked through the tSQLt code and saw some things that might interest some people. If you want to add a line in-between Error messages tSQLt sends as out put go to tSQLt.Private_RunTest.sql Line 77 and add either a CHAR(13) or CHAR(10) to @Msg2. And to change the Test Summary output go into tSQLt.DefaultResultFormatter.sql.

Sebastian
Sebastian

The best way to avoid self blocking is to not access the same tables within NewConnection and outside of it.