Documents

Can I fake views also?

@Prasenjit Das Yes. You can use FakeTable for that.

Is it possible to disable constraints on a faketable ? 

By default, a fake table doesn't have any constraints.

@@sqlity Yes but the problem is , this test case has been unique. I have had to use identity inserts and then process and then fetch from another table during which I need to disable the identity and then re-enable it.

Also, just curious can the identity columns be set to a seed value?

I don't think the comments here are the most effective way to solve your issue. Can you post your test case on stackoverflow using the tSQLt tag. When you do, describe exactly what you are trying to do and what the error message is that you are getting.

Hello,

I am writing an ExpectException test for a stored procedure that performs an ALTER TABLE SWITCH (which is not allowed on a table which has a foreign key referencing it).  So I want the test to succeed and identify the correct exception.  I found that when the test runs it fails because an extra error is raised from tSQLt.Private_RunTest containing the text ' (There was also a ROLLBACK ERROR --> '.  Looking closely at this, I found this is caused when tSQL.Private_RunTest attempts to rollback to a savepoint (but in this case that fails because the transaction cannot be committed and cannot rollback to a save point ie XACT_STATE = -1).  This happens in the final try block of tSQLt.Private_RunTest.  I have therefore temporarily changed the final try block to rollback the entire transaction if XACT_STATE = -1 (since this is the only action that can be taken) otherwise rolling back to the savepoint as at present.  The test succeeds (and all my other tests) with this change in place.  I wonder if anyone can suggest a better solution as I would ideally prefer not to tamper with the framework as I do not have a full understanding of it yet.

Thanks

Ian Allchin 

Hi,

There is a procedure which contains "ALTER DATABASE"   and I want to test that procedure using tSQLt.

But its giving an error saying that "ALTER DATABASE statement not allowed within multi-statement transaction.". Is there a way that i can test this procedure.

@Navya Phutane , currently it is not possible to run tests without the tSQLt transaction. However, you could have one procedure generate the statement(s) to be executed and another execute a statement provided in a parameter. then you can write a test that the correct statement was generated by the one, and any passed-in statements is executed by the other. Those tests would have the additional advantage of being a lot faster than your original approach.

An alternative approach is to use tSQLt.NewConnection. That requires you to have provided external access permission to the tSQLt CLR, however.

@@sqlity @Navya Phutane

Hi,

Is there any tool to test the code coverage for tSQLt?

 I found a tool in "DLM  Automation Suite" site. There are some procedures which are encrypted in the database, so it throws an exception. Is it the limitation of that tool or is there any alternative way to test the code coverage of tSQLt test cases.

Is there a way to verify/test the Print statement of a stored proc? I have a stored procedure that prints a message when the stored proc cannot proceed with certain inputs. I want to test that the message is present given bad input

@bitbitz Yes. Take a look at  tSQLt.CaptureOutput  . It takes a command as parameter and logs all output generated by that command into the tSQLt.CaptureOutputLog table. (Sorry, there seems to be no manual page for it yet.)

However, I would suggest that you use a dedicated "print" stored procedure instead of the build in print statement. That gives you more flexibility later on, and also makes testing easier.

In case your procedure is not just printing but raising an error, use the ExpectException method: http://tsqlt.org/user-guide/expectations/expectexception/

Another tSQLt beginner here.  I created and ran a test with no problem.  I created another case (proc) within the same test (schema).  tSQLt.Run 'MyTest' does not run the new proc.  I drop the original case(proc) and tSQLt runs no test cases.  Any ideas on why it does not run the new proc?

@darwinfisk , sorry for the late reply. If you still have this issue, please send an email to tSQLt@googlegroups.com and include your tests.

I've just started to work with tSQLt, I wanted to keep the test database and my development database on the same instance but separated so I do not 'clutter' up the development environment. So I've created synonyms to all my dev objects for the unit test database. All works wonderful until I start using FakeTable. I get the following error 

[Test Negative Debit and Credits] failed: (Error) An invalid parameter or option was specified for procedure 'sp_addextendedproperty'.[16,2]{sp_addextendedproperty,37}

Msg 266, Level 16, State 2, Procedure Private_RunTest, Line 0

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

I did this on the most simplistic table we have (exact line copied from code), once I comment it out all runs fine.

EXEC tSQLt.FakeTable '[company]'

Any information on how to proceed using FakeTable?

@Jehies , the different fake... and spy.. procedures do not yet reliably handle synonyms. If you create a view instead, you should have more success.

@Jehies I've created following stored procedure. It is not ideal, some functionality will not work, but at least it helps me

CREATE PROCEDURE [tSQLt].[FakeSynonymTable]

@SynonymTable VARCHAR(MAX)

AS

BEGIN

DECLARE @NewName VARCHAR(MAX) = 'tmp' + @SynonymTable;

   DECLARE @RenameCmd VARCHAR(MAX) = 'EXEC sp_rename ''' + 

                          @SynonymTable + ''', ''' + 

                          @NewName + ''';';

EXEC tSQLt.SuppressOutput @RenameCmd;

DECLARE @sql VARCHAR(MAX) = 'SELECT * INTO '+@SynonymTable+' FROM ' + @NewName + ';';

EXEC (@sql);

EXEC tSQLt.FakeTable @TableName = @SynonymTable;

END; 

GO

@DmitrijKultasev, thanks for posting this. Did you by chance write any tests for it?

@@sqlity @DmitrijKultasev do you ask me if I've tried to use it? Then yes, it is working. However support of different schemas are supposed to be added. Also it is working with tables, I do not know would it work with views. It definetely will not work with stored procedures and so on, so some catchs must be added there

@DmitrijKultasev, tSQLt is from ground up developed following a test-driven approach. Therefore, for me to add any code to it, I need test cases. My question was, if you had written any for the code you posted above. No problem if you didn't.

I am getting some error like below when i am trying to use newconnection

[Purge_LocationEventDetails_Prg_Proc_Tests].[test_012 READPAST option working correctly] failed: (Error) A .NET Framework error occurred during execution of user-defined routine or aggregate "NewConnection":

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

System.Data.SqlClient.SqlException:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)

at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)

at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

at tSQLtCLR.CommandExecutor.Execute(SqlString command)

at tSQLtCLR.StoredProcedures.NewConnection(SqlString command)

we have been using tSQLt with TeamCity for awhile now...all has been good. Recently, on some of our build servers, we are having trouble with TC parsing the file and getting this error: DocumentstSQLt_testing.xml: Attribute name "classnam"

when i look at the xml output file, it looks like a few lines of the resulting xml has cr/lf in them and that's where the parsing breaks. Any ideas anyone?

Is there any way to use tSQLt with Visual Studio?

@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.

@abhit , yes it is.

1. Create a new DB project in VS or open existing one.

2. Add existing tSQLt.class.sql into project.

3. Remove from tSQLt.class.sql any DROP statements. I also moved CREATE ASSEMBLY [tSQLtCLR] into a separate file in the project.

4. Add a file for your test classes (schemas):

CREATE SCHEMA tSQLt;

go

-- Test class names must start with 'test'

CREATE SCHEMA testFunction;

go

CREATE SCHEMA testSales;

go

CREATE SCHEMA testHumanResources;

go

....

5. Add into a post-deployment file the following:

-- tSQLt set up

exec sp_configure 'clr enabled', 1;

reconfigure;

go

insert into tSQLt.Private_NullCellTable (I)

values (null);

go

-- Install tests

declare@sqlnvarchar(max)= N'';

select@sql+= concat(N'exec tSQLt.Private_MarkSchemaAsTestClass @QuotedClassName = N''', quotename(s.name), ''';', char(10))

from sys.schemas s

where s.name like 'test%';

exec sys.sp_executesql@sql;

-- Run All Tests

exec tSQLt.RunAll;

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?

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.

@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.

Thanks Sebastian

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

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

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.

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.

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?

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

There is currently no way to change the layout of the default output. however, there is an xml format generator that returns the test results in a JUnit compatible format. That can be used to run tSQLt tests inside a CI environment. See https://www.simple-talk.com/sql/sql-tools/using-sql-test-database-unit-testing-with-teamcity-continuous-integration/ for an example implementation.

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.

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/

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.

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

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

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

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?

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;

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

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

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) .

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