Hi, there is not much documentation around SuppressOutput, so it's possible I'm not using it quite as intended. I am using SuppressOutput to mean "I don't care about the outcome of the execution, even exceptions", but a call to RAISERROR throws an exception that the implementation of SuppressOutput doesn't catch and handle:

[TestSchema].[test My RAISEERROR Test] failed: (Error) A .NET Framework error occurred during execution of user-defined routine or aggregate "SuppressOutput": 
System.Data.SqlClient.SqlException: This is the RAISERROR message
System.Data.SqlClient.SqlException: 
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages)
   at System.Data.SqlClient.SqlDataReaderSmi.InternalNextResult(Boolean ignoreNonFatalMessages)
   at System.Data.SqlClient.SqlDataReaderSmi.NextResult()
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderSmi(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at tSQLtCLR.TestDatabaseFacade.executeCommand(SqlString Command)
   at tSQLtCLR.StoredProcedures.SuppressOutput(SqlString command)
.[16,1]{SuppressOutput,0}

My expectation was that the exception would be caught and ignored, and execution would continue rather than failing the test as Error.

I've had a quick look through the code, and I think this would be handled by modifying the following to include a try/catch block:

I haven't tested it, but I suspect that CaptureOutput has a similar issue with exceptions - an alternative solution might be to modify the implementation of testDatabaseFacade.executeCommand to capture exceptions and log them to the InfoMessage (or similar) property.

Would be happy to give either of these a shot, but before I spend the time I'd like to understand what the preferred solution should be.

I have noticed that, when procedure and custom type is specified with the same name and schema, the SpyProcedure throws an error. Simply repro:

create schema tsqlt_collision_names_test

go

create type tsqlt_collision_names_test.test_type as table (id int)

go

create procedure tsqlt_collision_names_test.test_type(
    @param tsqlt_collision_names_test.test_type readonly
)
as
begin
    select 1
end

go

create procedure tsqlt_collision_names_test.test_type2(
    @param tsqlt_collision_names_test.test_type readonly
)
as
begin
    select 1
end

go

tSQLt.SpyProcedure 'tsqlt_collision_names_test.test_type2' --works

go

tSQLt.SpyProcedure 'tsqlt_collision_names_test.test_type' --exception

The tSQLt.Tests view defines a valid test case as one which starts with the name 'test'.
Add the ability for the sproc to have an associated extended property 'tsqlt.Test'.
Add a sproc to set this extended property 'tsqlt.SetTest @sprocname sysname'

Happy to do the work and send a pull request

Hello,
We're experimenting with tSQLt. So far we love it, except for one small issue. It's definitely a n00b mistake, but since it's so easily preventable we thought we'd suggest a solution to prevent it.

In one of our first experiments with tSQLt, one of our developers wanted to run a snippet of the test he was developing, so he outlined it in SQL Server Management Studio and hit F5. Unfortunately, there was a call to tSQLt.FakeTable in the outlined snippet.

Since we were new to the framework, all we knew was that all the data in our table was gone - we didn't even know what had caused it to disappear. It was a test system, and we had backups anyway, so no harm done - however our confidence in tSQLt was severely shaken until we were able to guess what had happened, and find our data by looking in the renamed objects table.

It would be nice if the faking/spying functions all had some protection code in them to detect if they were being run inside a test run, and raise an error before any changes were made. Something similar to
IF @@TRANCOUNT = 0 RAISERROR('tSLQt.FakeTable called outside of a test session', 16, 10) perhaps?

It's not that big of a deal, but I think it could help new users of the framework immensely.

Thanks for your consideration,
Travis Little

It would be nice if I could specify a list of columns inFakeTable call, and their nullabilty was preserved.
Some code does SELECT INTO temp table, which copies the nullability -- then adds a primary key to that temp table -- which fails under test, but not with a real table.

Due to legacy database design, there are several columns defined with float (ugh!)

I'm finding that this is messing up tests because when comparing a result of legacy view with a modified stored procedure that should output same results as the legacy view, I get failed tests even if they match up visually. I'm attributing this to the rounding issue that are not immediately seen in the output text. From the users' POV, the results will be same, but from the computer's POV, there's probably one bit off somewhere that is of no significance.

I would suggest that the AssertTableEquals should have an optional parameter (with default to off) to enable approximate comparison to accept a small margin of rounding errors, only for the data types that are floating precision to help simplify the workflow including those dastardly floating precision data types.

It looks like ApplyConstraint is not incorporating ignore_dup_key = on into account. When I run ApplyConstraint with a primary key constraint with ignore_dup_key = on, and try to insert a record with a value that already exists, I get an exception when I don't expect one.

I believe this is happening because ApplyConstraint for unique constraints and primary key constraints are being handled the same. I can try to provide code to reproduce if it's not clear what is happening from the description here.

In order to use the sqlcmd utility to export test results in xml format it is necessary to both use the :XML ON directive and pass the -y0 flag to sqlcmd in order to get the full output. However, the :XML ON directive is not supported in the Linux version of sqlcmd.

As an alternative, casting to nvarchar(max) and using the -y0 flag works, including on Linux. However, tSQLt.XmlResultFormatter does not allow any way to do this cast. It is not possible via INSERT .. EXEC .., since FOR XML cannot be used within INSERT .. EXEC ...

tSQLt doesn't support contained/partially contained databases. A number of collation issues (due to the changed in system catalogs - the sys* tables) means that tSQLt cannot be installed if the database collation is not Latin1_General_100_CI_AS_KS_WS_SC.

Msg 468, Level 16, State 9, Procedure Private_GetCursorForRunNew, Line 10 Cannot resolve the collation conflict between "Latin1_General_100_CI_AS_KS_WS_SC" and "Latin1_General_CI_AS" in the equal to operation. Msg 451, Level 16, State 1, Procedure Private_ScriptIndex, Line 32 Cannot resolve collation conflict between "Latin1_General_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in add operator occurring in SELECT statement column 6. Msg 451, Level 16, State 1, Procedure Private_GetForeignKeyDefinition, Line 17 Cannot resolve collation conflict between "Latin1_General_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in add operator occurring in SELECT statement column 1. Msg 451, Level 16, State 1, Procedure Private_GetForeignKeyDefinition, Line 17 Cannot resolve collation conflict between "Latin1_General_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in add operator occurring in SELECT statement column 2. Msg 451, Level 16, State 1, Procedure Private_GetDataTypeOrComputedColumnDefinition, Line 4 Cannot resolve collation conflict between "Latin1_General_100_CI_AS_KS_WS_SC" and "Latin1_General_CI_AS" in CASE operator occurring in SELECT statement column 2. Msg 208, Level 16, State 1, Procedure Private_GetUniqueConstraintDefinition, Line 47 Invalid object name 'tSQLt.Private_GetDataTypeOrComputedColumnDefinition'.

Hello,

I have a unique filtered index for which I would like to write a tSQLt test to verify the logic is correct. However, since there is no tSQLt.ApplyIndex method, I have to manually re-create the unique filtered index on the table after calling tSQLt.FakeTable.

I'm requesting the addition of an ApplyIndex procedure for this use.

Example use case:

-- The values of `name` and `value` may be duplicated, but only one of those records
-- may have a `parentIsDefault` value of 'Y' (all other records must be 'N')
create table ParentIdentifiers (
    id int identity(1, 1) not null,
    parentId int not null,             -- References some other table
    name varchar(128) not null,
    value varchar(128) not null,
    parentIsDefault char(1) not null
)

-- Filtered unique index
create unique index UX_ParentIdentifiers_parentIsDefault
on ParentIdentifiers (
    name,
    value
)
where parentIsDefault = 'Y'
GO

exec tSQLt.FakeTable 'dbo.ParentIdentifiers'
-- This doesn't exist, so I have to duplicate the create unique index code
exec tSQLt.ApplyIndex 'dbo.ParentIdentifiers', 'UX_ParentIdentifiers_parentIsDefault'

exec tSQLt.ExpectNoException
insert into ParentIdentifiers (name, value, parentIsDefault)
values('One', '1', 'Y'),
      ('One', '1', 'N'),
      ('Two', '2', 'Y')

exec tSQLt.ExpectException @ExpectedMessagePattern = 'Cannot insert duplicate key row in object%',
                           @ExpectedSeverity = 14,
                           @ExpectedState =1

insert into ParentIdentifiers (name, value, parentIsDefault)
values('Two', '2', 'Y'),

Thank you.

Hello, Ive looked at your documentation and it is little misleading where firstly there is @TableName and @FailMsg and actuall parameters are @TableName and @message.

I had trouble with using DropClass while trying to figure out some standardized naming conventions for Class (test schema) names. The issue is there when there is a dot (ASCII 46) in the name.

Looking at the code of the procedure I noticed that when it is looking for the schema id, it uses SCHEMA_ID(@classname) everywhere, expect where it looks up the schema itself from sys.schemas. There it is using SCHEMA_ID(PARSENAME(@classname,1)). Not sure what's the goal with this, but once there's a dot in the Class name, PARSENAME will keep only the part after it, and SCHEMA_ID will not find the schema.

The main use case for this is in order to "spy" system stored procedures.

For example, I am currently writing tests for a procedure which calls sp_send_dbmail. I don't want to actually send the email; in fact there isn't a dbmail profile setup. But I do want to assert that my procedure a) is calling the proc the correct number of times, and b) is calling it with the correct parameters.

Procedures which schedule SQL Agent jobs are another good example.

I think theoretically you could get around this by deploying tSQLt to the system database, and calling [msdb].[tSQLt].[SpyProcedure], but the idea of deploying code to a system database makes my skin crawl. It's ok to spy procedures in the system database, because this happens in a transaction, and gets rolled back nice and cleanly. But uninstalling tSQLt from the database might not be as clean.

Another scenario where this would help is when your proc calls out to another database. Currently, you would have to deploy tSQLt to that database as well, and call [OtherDb].[tSQLt].[SpyProcedure]. This isn't as much of a problem, but it's a little frustrating having to deploy tSQLt to another database just to spy a single proc.

I think I should be able to code these changes; I've worked around this in my tests. I'm just raising this a) to have something to relate the pull request to, and b) to make sure people agree that it is a desired feature, and I'm not contravening some part of tSQLt's design principle.

In a fresh installation of SQL Test I added one of my database and notice the built-in test case "Tables without a primary key" fails and in the output where I would expect to see the list of table(s) breaching the rule I see database roles. The output looks like this:

Test Procedure: [dbname].[SQLCop].[test Tables without a primary key] on servername
[SQLCop].[test Tables without a primary key] failed: (Failure)

myRoleHere.Private_AssertEqualsTableSchema_Actual
myRoleHere.Private_AssertEqualsTableSchema_Expected
myRoleHere.Private_ExpectException
myRoleHere.Private_NullCellTable
myRoleHere.Run_LastExecution
myRoleHere.TestMessage

Please note that there is no tables or any other objects in my database with the same name as my role.

The issue was reported to RedGate and their development team confirmed the issue lies in the tSQLt framework so they can't fix it.

I fixed the issue locally by updating the built-in sproc :
[SQLCop].[test Tables without a primary key]

to include this WHERE condition:
AND ISNULL(u.issqlrole, 0) = 0

Please note another test, "Procedures that call undocumented procedures" , fails with a similar problem where it confuses a role for a sproc... I fixed that one locally the same way, by tweaking filtering our sqlroles in the where clause in [SQLCop].[test Procedures that call undocumented procedures]

We were running tests where comparison is made between two tables which store data about date time in DATETIME2(7). It appeared, that then tSQLt.AssertEqualsTable stored procedure is used the output on the mismatches is not presenting all details about, so the "Unexpected/missing resultset rows!" table showing that there are some rows which don't match, but when looking in to output results it is not so obvious what is the difference because values are rounded to seconds while the purpose of the test was to check precision of nanoseconds.

The output example:

We use DbUp to handle our data base schema migrations and I am also using it to deploy my tSQLt tests to different environments. The $$Variable$$ convention used below is conflicting with DbUps variable convention of $Variable$. Could it be changed to something different here? This is the only place I saw it in the code.

Maybe ${variable} or variable or ZZvariableZZ could be used instead?

SET @cmd =
'CREATE TRIGGER $$SCHEMA_NAME$$.$$TRIGGER_NAME$$
ON $$SCHEMA_NAME$$.$$VIEW_NAME$$ INSTEAD OF INSERT AS
BEGIN
RAISERROR(''Test system is in an invalid state. SetFakeViewOff must be called if SetFakeViewOn was called. Call SetFakeViewOff after creating all test case procedures.'', 16, 10) WITH NOWAIT;
RETURN;
END;
';

SET @cmd = REPLACE(@cmd, '$$SCHEMA_NAME$$', QUOTENAME(@SchemaName));
SET @cmd = REPLACE(@cmd, '$$VIEW_NAME$$', QUOTENAME(@ViewName));
SET @cmd = REPLACE(@cmd, '$$TRIGGER_NAME$$', QUOTENAME(@TriggerName));

I tried the new version today and found out that to skip a test I need to use --[@tSQLt:SkipTest]('Some reason') to get it working. In the releasenotes it tells the annotation is --[@tSQLt:Skip]('Some reason') (so no Test after Skip)

It would be nice if this was updated in releasenotes and an example in the documentation would be greatly appreciated. Keep up the good work :)

Hi,

We have just noticed a bug with AssertEmptyTable, specifically when checking a table in another database. If you pass in [AnotherDatabase].dbo.MyTable, OBJECT_ID returns a value (assuming the table does exist!), so the code flows to:
SET @FullName = tSQLt.Private_GetQuotedFullName(OBJECT_ID(@TableName));

Unfortunately Private_GetQuotedFullName doesn't know you're providing an OBJECT_ID that is in a different database, so you get a different FullName back than expected (in our case tSQLt.LogCapturedOutput among others) which means the execution fails further down (it ended up trying to do a SELECT 1 FROM <stored procedure> which errored).

I'm not sure what the correct fix should be - potentially PARSENAME could be used to see if a database name is provided, and this could be checked against DB_NAME(), but I don't know how far back that is supported, and/or if Private_GetQuotedFullName would also need to be expanded to support a database name (or if that should just happen in AssertEmptyTable?).

the example doesn't work
the examples http://downloads.tsqlt.org/tSQLt_demo.zip are also buggy

Hi,

I have recently reinstalled tSQLt as part of a SQL 2019 migration project and have noticed that the above configuration is not affecting the output. I set it to True, but I still see full detailed text output when I run my test suite.

Can you advise as to how I can suppress the full output, as my tests have thousands of results in some cases?

This is using the latest version, 1.0.7597.5637.

Thanks,
Steve

At the bottom is a tSQLt test that replicates the "INSERT EXEC statement cannot be nested.” issue.

This has been reported a while ago the consensus is this requires a CLR procedure to work around SQL server limitations.

• https://groups.google.com/d/msg/tsqlt/o8XGKG8UyM4/qFVVeHiIMvMJ
• https://groups.google.com/d/msg/tsqlt/O7VbCz6cgWg/9x2Ia8YCI5MJ

Replication

EXEC tSQLt.NewTestClass 'ReplicateInsertExecIssue';
GO

-- This proc returns a couple of rows
CREATE PROC ReplicateInsertExecIssue.[_DataReturnTable]
AS
	SELECT CAST('TEST' AS VARCHAR(15)) as col1, CAST(123 AS INT) AS col2 
	UNION SELECT 'TEST', 456
GO

-- This proc returns two tables after using INSERT EXEC
CREATE PROC ReplicateInsertExecIssue.[_DataUsesInsertExec]
AS 
BEGIN
	DECLARE @cachedResults TABLE(col1 VARCHAR(15) NULL , col2 int NULL);
	INSERT INTO @cachedResults EXEC ReplicateInsertExecIssue.[_DataReturnTable];
	SELECT col1, SUM(col2) AS col2 FROM @cachedResults GROUP BY col1;
	SELECT col1, MIN(col2) AS col2 FROM @cachedResults GROUP BY col1;
END;
GO

-- This tries to test the contents returned by the stored proc
CREATE PROC ReplicateInsertExecIssue.[test Replicate INSERT EXEC cannot be nested]
AS BEGIN	
	CREATE TABLE #expected (col1 VARCHAR(15) NULL , col2 int NULL);		
	INSERT INTO #expected values ('TEST', 123+456);

	CREATE TABLE #actual (col1 VARCHAR(15) NULL, col2 int NULL);
	INSERT INTO #actual EXEC tSQLt.ResultSetFilter 1, 'EXEC ReplicateInsertExecIssue._DataUsesInsertExec';

	EXEC tSQLt.AssertEqualsTable '#expected', '#actual';
END;
GO

EXEC tSQLt.Run 'ReplicateInsertExecIssue';
go

If you use FakeTable and any column is datetime2, time or datetimeoffset with a fractional seconds precision, the precision is ignored. For example the table might define a column as datetime2(3), the table created using FakeTable will have the datatype as datetime2(7).

I noticed that your work does not contain a license file and was just wondering if you have any ideas about the license you would like to issue with your intellectual property? According to the Choose an open source website your property already contains an explicit license:

Opting out of open source licenses doesn’t mean you’re opting out of copyright law.

You’ll have to check with your own legal counsel regarding your particular project, but generally speaking, the absence of a license means that default copyright laws apply. This means that you retain all rights to your source code and that nobody else may reproduce, distribute, or create derivative works from your work.

You don’t have to do anything to not offer a license, though including a copyright notice is recommended. [...]

Currently this basically means that I don't have permissions to re-use your code without your consent (sort of), which somehow collides with the idea of GitHub being pretty much open.

Hi,

I am investigating the framework as a means to test some database code. Unless I have misunderstood things there seems to be a direct correlation between the number of stored procedures "test methods" that would need to be written when say:

1. You want to test a combination of parameters.

For instance there is no ability to pass parameters to tSQLt.Run(). So if I have a SP that has a single param that I want to test with 3 separate values I would need THREE stored procedure test methods. As opposed to what can be done with NUnit say. See this SO post for an example.

2. Test all the result sets returned by a stored procedure

This requires multiple calls to the underlying SP so that we can call http://tsqlt.org/201/using-tsqlt-resultsetfilter/ with the appropriate offset.

That all being said.... What options are there for extending the framework? While I see that we have a CLR available to us there is no clear indication of:

a) What the extension points are
b) Whether this project is actually still alive and improving with the last release being at the beginning of 2016

Any feedback greatly appreciated.

I am implementing tSQLt in our environment and found that I do not have to set my database to be TRUSTWORTHY so I started to dig into what would not work if I kept it that way.
I found the V1.0.5873.27393 release notes which indicate that this is no longer needed.

The tSQLt.class.sql and Example.sql still contain code to set this property on. And the quick start guide indicates that it MUST be turned on.

Hello,

I noticed that when the value of the parameter of tested procedure is equal to the sql keyword - procedure tSQLt.ResultSetFilter returns an error:

System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'AS'.

Test script:

USE tSQLt_Example
GO

IF OBJECT_ID('dbo.uspForTest') IS NOT NULL
DROP PROCEDURE dbo.uspForTest
GO

CREATE PROCEDURE dbo.uspForTest
(
@InParam01 VARCHAR(20)
)
AS
BEGIN
SELECT @InParam01 [InParam01]
ENd
GO

IF OBJECT_ID('dbo.test_uspForTest') IS NOT NULL
DROP PROCEDURE dbo.test_uspForTest
GO

-- ok test case
CREATE PROCEDURE dbo.test_uspForTest
AS
BEGIN

CREATE TABLE #uspForTest
(
InParam01 VARCHAR(20)
)

DECLARE @command VARCHAR(4000)
DECLARE @InParam01 VARCHAR(20)

SET @InParam01 = 'AT'

SET @command = 'EXEC dbo.uspForTest @InParam01 = ' + @InParam01

INSERT INTO #uspForTest
EXEC tSQLt.ResultSetFilter
@ResultsetNo = 1
, @command = @command

SELECT * FROM #uspForTest
END
GO

EXEC tSQLt.Run '[dbo].[test_uspForTest]'
GO

-- error test case
ALTER PROCEDURE dbo.test_uspForTest
AS
BEGIN

CREATE TABLE #uspForTest
(
InParam01 VARCHAR(20)
)

DECLARE @command VARCHAR(4000)
DECLARE @InParam01 VARCHAR(20)

SET @InParam01 = 'AS'

SET @command = 'EXEC dbo.uspForTest @InParam01 = ' + @InParam01

INSERT INTO #uspForTest
EXEC tSQLt.ResultSetFilter
@ResultsetNo = 1
, @command = @command

SELECT * FROM #uspForTest
END
GO

EXEC tSQLt.Run '[dbo].[test_uspForTest]'
GO

I executed the tSQLt.class.sql file on an Azure SQL database. It worked well and I was able to use it there.

But when I wanted to export the database (via "Generate Scripts" in SQL Server Management Studio or directly from the Azure portal), I got errors like:

Error SQL71501: Error validating element [tSQLt].[Private_GetFullTypeName]: Function: [tSQLt].[Private_GetFullTypeName] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [tSQLt].[Private_SysTypes].[schema_id] or [tSQLt].[Private_SysTypes].[T].

Error SQL71501: Error validating element [tSQLt].[Private_ScriptIndex]: Function: [tSQLt].[Private_ScriptIndex] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [tSQLt].[Private_SysIndexes].[I] or [tSQLt].[Private_SysIndexes].[type].

I tried on my local machine and did not encounter the problem there.

Note: in both cases (on Azure and locally) I didn't execute the SetClrEnabled.sql file, because it does not work on Azure.

Versions:
tSQLt: 1.0.5873.27393
Azure DB: Microsoft SQL Azure (RTM) - 12.0.2000.8 Jun 7 2017 01:09:35

I wanted to test if a Table had an Audit setup on it and some of the tables are now using temporal tables to do that auditing.

I did see that Michael Hotek had built his own version of it over in this google group https://groups.google.com/forum/#!topic/tsqlt/nsHpez08huA

He implemented it by creating a separate stored procedure tSQLt.FakeTableTemporal but would it make more sense to extend tsqlt.FakeTable to support it instead?

Not sure which is the best way to go but wanted to get the issue in

does this framework support MDX queries to test SSAS cubes

These would be used for testing whether or not, for example, some variable has a value. E.g.:

EXEC @insertedAddressId = [someschema].[SomeStoredProcedure] ...
EXEC tSQLt.AssertNotNull
    @Value = @insertedAddressId
    @Messsage = N'Should have returned a non-null ID for INSERTed address.'

tSQLt.AssertIsNull would have similar syntax and usage.

Could the tSQLt documentation from https://tsqlt.org/user-guide/ be moved to source control (e.g. as wiki, or README) to make contributions easier?

This would be some useful (and easy) data to append to the results chart after running so that external timestamps (from a build server, for example) wouldn't be required to timestamp a given result set.

I'd be happy to work on adding this feature.

We are using 0001-01-01 (the minimum value for the DATE data type in SQL Server 2017) in some tables. Asserting table equals fails with the following error when this (or any value less than 1753-01-01 is present:

(Error) A .NET Framework error occurred during execution of user-defined routine or aggregate "Private": 
System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
System.Data.SqlTypes.SqlTypeException: 
   at System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value)
   at System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value)
   at tSQLtCLR.tSQLtPrivate.getTableStringArray(SqlDataReader reader, SqlString PrintOnlyColumnNameAliasList)
   at tSQLtCLR.tSQLtPrivate.TableToString(SqlString TableName, SqlString OrderOption, SqlString ColumnList)
.[16,1]{tSQLt.TableToText,8}

It seems that the whole thing fails when the table is serialize in tSQLt.TableToText when the assertion fails.

How to reproduce:

CREATE TABLE #Table (DateColumn DATE);
CREATE TABLE #Table2 (DateColumn DATE);

-- Succeeds
TRUNCATE TABLE #Table;
TRUNCATE TABLE #Table2;
INSERT INTO #Table (DateColumn) VALUES ('1753-01-01');
INSERT INTO #Table2 (DateColumn) VALUES ('2000-01-01');
EXEC tSQLt.AssertEqualsTable N'#Table', N'#Table2';

-- Fails
TRUNCATE TABLE #Table;
TRUNCATE TABLE #Table2;
INSERT INTO #Table (DateColumn) VALUES ('1752-12-31');
INSERT INTO #Table2 (DateColumn) VALUES ('2000-01-01');
EXEC tSQLt.AssertEqualsTable N'#Table', N'#Table2';

-- Succeeds
TRUNCATE TABLE #Table;
TRUNCATE TABLE #Table2;
INSERT INTO #Table (DateColumn) VALUES ('0001-01-01');
INSERT INTO #Table2 (DateColumn) VALUES ('0001-01-01');
EXEC tSQLt.AssertEqualsTable N'#Table', N'#Table2';

Environment:

• SQL Server 2017 Developer
• .NET Framework 4.5.2
• Windows 10 Enterprise

Hi Team,

I am not able to install tSQLT on SQL Server 2019. The steps followed to install tSQLT are:

1. Downloaded the latest package from https://tsqlt.org/
2. Executed script SetClrEnabled
3. Trying to execute script tSQLt.class but faced error - "CREATE or ALTER ASSEMBLY for assembly 'tSQLtCLR' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly."

As per the release notes, tSQLT CLR is now a signed. Please let me know what I am missing here in the installation part.

I've encountered an error when trying to use FakeTable on a table where a user-defined table type of the same name and schema exist.

"Either the parameter @OBJName is ambiguous or the claimed @objtype ((null)) is wrong."

The issue seems to be in SP tSQLt.Private_RenameObjectToUniqueName, which runs sp_rename without specifying the @objtype parameter (and so defaults @objtype to NULL). This then gives rise to the ambiguity error, as it has not been told whether to look at the OBJECT or the USERDATATYPE.

I believe setting @objtype to OBJECT in this call would resolve the problem.

First off, super excited to see a recent release! Thanks for the hard work in keeping this thing up to date 🙏

There are a few issues with the newest version (V1.0.7597.5637) that don't work with Azure SQL when running tSQLt.class.sql:

Msg 40515, Level 15, State 1, Procedure RemoveAssemblyKey, Line 16 [Batch Start Line 192]
Reference to database and/or server name in 'master.sys.sp_executesql' is not supported in this version of SQL Server.

Switching these database-qualified system stored procs to simply sp_executesql seems to resolve the error. This also applies to sp_configure as well.

Msg 10341, Level 16, State 100, Line 1204
Assembly 'tSQLtCLR' cannot be loaded because Azure SQL Database does not support user-defined assemblies. Contact Azure Technical Support if you have questions.

It looks like the issue is probably because older versions had the CLR whitelisted (discussed in #36) and this one has not yet.

I think the remaining downstream errors would be fixed by the above two problems being resolved. I am happy to make a PR for the first set of problems 👍

my bad. line 1140 breaks the code formatter but it runs successfully

Hi,

is it possible to fake a table permanently in unittestDb and use that data to run all my test cases within a class.

eg: I have a summarized table with many metrics like, openquantity, remaining quantity, open price, endofdayprice, volume by eod etc.

my goal is to randomly pick few markets into #markettable and relavant transactions from orginalsourcetable Joined to #market and load to #transactions .

In actual test case proc for 'openQuanity' looks like below
select * into #expected from #transactions

select * into #Actual
from db.dailysnapshot S
Inner join #markettable m .....

EXEC tSQLt.AssertEqualsTable '#Expected', '#Actual';

I want to write another test for remaining quantity but reuse the #markettable and #transactions tables.

is this something i can achieve using tsqlt

Tables that are referenced by views which have schema binding turned on can't be faked without removing the schema binding. This makes it really hard to write single responsibility tests when working with databases that use schema binding heavily.

In 2015 there was rumor that this feature would be added but it still seems to be missing.

http://tsqlt.org/user-guide/isolating-dependencies/faketable/

If test or called procedures have 5 or more select statements then this exception is thrown.

An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

if OBJECT_ID('[Tests_XL].[test test]','P') is not NULL
    drop proc [Tests_XL].[test test];
go
create procedure [Tests_XL].[test test]
as 
select 1;
select 1;
select 1;
select 1;
select 1;
go

Hello!
I'm learning tSQLt and have one suggestion - it we be great to have Setup procedure for all tests of class. My tests requires heavy setup and this setup is one for all tests of class. I want to call it once per test class.
Thanks!
Best regards, Konstantin

https://tsqlt.org/user-guide/test-creation-and-execution/run/ does not mention the @TestResultFormatter parameter.

It looks like the valid values for @TestResultFormatter are tSQLt.DefaultResultFormatter, tSQLt.NullTestResultFormatter, and tSQLt.XmlResultFormatter.

Please could you include the following modification to the DropClass proc?
tSQLt.DropClass.ssp.sql.txt

I am wary about documenting a feature that encourages "out of design" use of the framework. However, I could see an option to change the default behavior by setting a global TableOutputFilter similar to the TestResultFormatter that already exists.

Each filter should be a procedure that will be called to "filter" the rows in the output based on its rules.

Something like:

tSQLt.TableOutputFilterAll @ResultTable NVARCHAR(MAX), @ResultColumn NVARCHAR(MAX), @ColumnList NVARCHAR(MAX)

tSQLt.TableOutputFilterDifferent @ResultTable NVARCHAR(MAX), @ResultColumn NVARCHAR(MAX), @ColumnList NVARCHAR(MAX)

tSQLt.TableOutputFilterTop @RowCount INT, @ResultTable NVARCHAR(MAX), @ResultColumn NVARCHAR(MAX), @ColumnList NVARCHAR(MAX)

@ColumnList might not be needed, but the interface should be the same across all of them, with additional required parameters (like @RowCount coming first.

If you take this on, we do need adequate test coverage to accept the PR.

Originally posted by @mbt1 in #35 (comment)

It would be helpful to have setup and tear-down mechanisms, as per NUnit and the like. These could be used at the test, class , or database level to initialise any pre-requisite data, and get rid of it afterwards.

SQL Server 2017 introduces a new server-level option,"CLR strict security", that prevents unsigned assemblies from being created, even SAFE assemblies. Because of this, certain adjustments need to be made to the deployment process.

This issue was initially noted on Stack Overflow: Unable to create the tSQLtCLR assembly in SQL Server 2017

Because there is an existing .snk file and (I assume) related .pfx file, the option with the least impact on the current signing key and deployment process would be to use a certificate to sign the empty assembly used to create the asymmetric key. The overall difference as of SQL Server 2017 is that you used to be able to load the tSQLtCLR assembly and then later, optionally load the tSQLtExternalAccessKey assembly so that you could set the tSQLtCLR assembly to EXTERNAL_ACCESS for those using the NewConnection. Now, you can't create any assemblies unless the asymmetric key is there first. The asymmetric key is no longer optional (well, unless the database is set to TRUSTWORTHY ON but that is highly discouraged.

1. Create a certificate. There are a few ways to do this. I prefer the MAKECERT utility, but you can also use PowerShell or even SQL Server (if you use SQL Server, you will need to BACKUP CERTIFICATE including the private key). In the end, the certificate will be in both places: file system and SQL Server. Once created, you shouldn't ever need to do this again. But it might be a good idea to add the .cer file to the repository.

2. Use the PVK2PFX utility to combine the .cer and .pvk files into a password-protected .pfx file. Perhaps call it "InstallationKey.pfx" to distinguish it from "SigningKey.pfx"? Once combined, you shouldn't ever need to do this again.

3. This new .pfx file will be used to sign the tSQLtExternalAccessKey DLL using the SignTool utility. This can be automated rather easily by adding it as a Post-Build Event (which will be stored in the tSQLtExternalAccessKey.csproj file). This only needs to be run if the tSQLtExternalAccessKey.dll assembly is ever re-compiled, and there generally is no need to ever do that. But since you can't control whether or not people using this project will re-compile it, it can't hurt to have this part automated.

4. Update the build process such that the deployment has the following being executed before any CREATE ASSEMBLY statements (even if the assemblies are being created as SAFE):

   • All 3 steps are executed in [master]
   • If you need the installation to be compatible for SQL Server versions prior to 2012, then wrap steps 1 - 3 in an IF block based on SQL Server "major" version. The two DROP statements, 8 and 9, should be wrapped in an IF EXISTS (or similar) check so those don't need to check for the version (if those objects weren't created due to running on SQL Server 2008, for example, then they just won't do anything).
   • A full explanation of this approach, along with a link to a working demo script, can be found at: SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1
     
     
   1. CREATE CERTIFICATE [InstallationKey] from a VARBINARY literal of the .cer file. You can use or update one of your helper utilities to do this, or you can use the free Binary Formatter utility that I wrote to do this (and to make sure that the output works well in scripts and when posted online).
   2. CREATE LOGIN [tmp] from that certificate.
   3. GRANT that cert-based login the UNSAFE ASSEMBLY permission.
   4. CREATE ASSEMBLY [tSQLtExternalAccessKey] ...;
   5. CREATE ASYMMETRIC KEY [tSQLtExternalAccessKey] ...;
   6. CREATE LOGIN from that asymmetric key.
   7. GRANT that key-based login the UNSAFE ASSEMBLY permission.
   8. DROP LOGIN [tmp];
   9. DROP CERTIFICATE [InstallationKey];
   10. DROP ASSEMBLY [tSQLtExternalAccessKey];

All of this might seem complicated at first, but this is all just a one-time setup.

Is it possible to update the QuickStart page https://tsqlt.org/user-guide/quick-start/ to mention that you do not need to enable CLR for sqlAzure databases, a few people were not aware that tSQLt works on Azure as they couldn't get past the first set of instructions.

I suggest adding amending

3. Make sure CLRs are enabled on your development server by running the following sql

EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;

TO something like

3. Make sure CLRs are enabled on your development server by running the following sql (not required on SQL Azure Databases)

Within an enterprise setting, where changes are going to new servers automatically as part of a CI flow, and databases are being built and unit tested from code, it doesn't make sense to have these test schema's dependant on a user.

We made a minor change to the NewTestClass proc as a quick fix;

EXEC ('CREATE SCHEMA ' + @QuotedClassName + 'AUTHORIZATION dbo');  

Happy to PR but there may be a good reason for not doing this for most people? Or maybe it could be configuration or parameter based (Or indeed check that dbo exists first)

But, either way, I thought it was worth a discussion!

With the last release at the beginning of 2016?