tSQLt in Azure SQL Database

I was excited to hear about the v12 Azure databases supporting CLR assemblies. Mainly because I’ve been doing testing work with tSQLt and wanted to run tests in an Azure database.

I upgraded a SQL Azure database to v12 and connected in SSMS. My first step was to open the tSQLt file.

2015-08-14 14_27_22-Start

I had the file open, connected to Azure. This was the easy part. Now I need to actually send the batch across and compile the code:

2015-08-14 14_27_33-Start

My next step was to execute it. However that didn’t work as well as I expected.

2015-08-14 14_27_58-New notification

There are multiple errors here, but it’s possible that one error causes others. I pinged Sebastian Meine, the creator of tSQLt about the External_Access and he noted there was only one method that needs it.

So I decided to make a change. First, a search.

2015-08-14 14_28_18-tSQLt.class.sql - dkranchapps.database.windows.net,1433.Predictions (sjones (54)

That got me the assembly installation.

2015-08-14 14_28_28-Start

I decided to try and change this to something that’s contained inside the database. Since Azure is a bit of a black box, I thought safe was the way to go.

2015-08-14 14_28_42-Start

With that change made, I compiled the entire file again. This was the only change I made.

2015-08-14 14_30_18-Movies & TV

That’s cool, but does it work? I connected in Object Explorer and then opened SQL Test. I saw my database, and I could create a test.

2015-08-14 14_31_36-Cortana

However I got an error on the connection and creation of a procedure.

2015-08-14 14_42_31-Movies & TV

Even from Object Explorer, I refreshed the procedures, but got this:

2015-08-14 14_42_44-Microsoft SQL Server Management Studio

This appears to be a SQL Server 2014 RTM error. Actually I was on a CU, but not SP1. It is supposed to be corrected in SP1.

However the procedure was created, and I could alter it.

2015-08-14 14_43_50-Movies & TV

More importantly, I can execute it.

2015-08-14 14_44_04-SQLQuery4.sql - dkranchapps.database.windows.net,1433.Predictions (sjones (52))_

If I change the code.

2015-08-14 14_44_23-Movies & TV

Now it fails.

2015-08-14 14_44_33-SQLQuery4.sql - dkranchapps.database.windows.net,1433.Predictions (sjones (52))_

It appears that tSQLt can now work in v12 databases in Azure, so start adding those unit tests to your database projects.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

2 Responses to tSQLt in Azure SQL Database

  1. ooo00ooo says:

    So what specifically will not work as a result of the change to safe from external_access? I’d quite like to use tsqlt but have been put off by the external_access requirement as this will be a sticking point with our dba’s on our test environments and I don’t really want the config of our live environment to be different to our test environment for any reason.


Comments are closed.