Thursday, May 19, 2016

Living in the NYC tri-state area? Attend the SQL 2016 Launch Event in NYC on June 2nd


Got this info from my co-worker Daryl earlier today.

On June 2nd Microsoft will hold a SQL 2016 Launch Event in New York City. The Microsoft Offices at 11 Times Square (at 8th and 42nd) in New York is where this event will be held.


WHEN
Thursday, June 2, 2016 from 8:00 AM to 2:00 PM (EDT)

WHERE
Microsoft Offices - 11 Times Square, New York, NY 10036

I might be there but I don't want to promise anything. You can sign up here for this event: https://www.eventbrite.com/e/sql-2016-launch-event-nyc-ny-tickets-25477835893

Wednesday, May 11, 2016

How to disable tests in tSQLt



The tSQLt unit test framework that we use to do our DB tests does not allow for disabled tests, this is because each test is just a stored proc. Right now the only way is to either rename the proc so it doesn’t start with test or to remove the test

I was thinking about this in the shower this morning….here is what I came up with

Here is a quick way how to prevent a test from running when the tSQLt tests run.
Put the code below into each unit test that you want disabled, you should put this code right after the CREATE PROCEDURE  ProcName AS part

This code will pick the correct name and schema, so you can use the same exact code in each test


DECLARE @IsTestDisabled bit = 1 -- set this to 1 if you don't want the test to run

IF @IsTestDisabled =1
BEGIN
 DECLARE @SchemaName varchar(1000)
 SELECT @SchemaName = QUOTENAME(SCHEMA_NAME(schema_id)) +'.'
 FROM sys.procedures WHERE object_id = @@procid

 PRINT 'Disabled Test: ' + @SchemaName +  QUOTENAME(OBJECT_NAME(@@procid))
 RETURN
END

So if we grab the example test from the tSQLt website

We would change it from this


CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate]
AS


BEGIN
    DECLARE @actual MONEY;
    DECLARE @rate DECIMAL(10,4); SET @rate = 1.2;
    DECLARE @amount MONEY; SET @amount = 2.00;

    SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);

    DECLARE @expected MONEY; SET @expected = 2.4;   --(rate * amount)
    EXEC tSQLt.AssertEquals @expected, @actual;

END;
GO


To  this


CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given conversion rate]
AS

DECLARE @IsTestDisabled bit = 1 -- set this to 1 if you don't want the test to run

IF @IsTestDisabled =1
BEGIN
 DECLARE @SchemaName varchar(1000)
 SELECT @SchemaName = QUOTENAME(SCHEMA_NAME(schema_id)) +'.'
 FROM sys.procedures WHERE object_id = @@procid

 PRINT 'Disabled Test: ' + @SchemaName +  QUOTENAME(OBJECT_NAME(@@procid))
 RETURN
END


BEGIN
    DECLARE @actual MONEY;
    DECLARE @rate DECIMAL(10,4); SET @rate = 1.2;
    DECLARE @amount MONEY; SET @amount = 2.00;

    SELECT @actual = FinancialApp.ConvertCurrency(@rate, @amount);

    DECLARE @expected MONEY; SET @expected = 2.4;   --(rate * amount)
    EXEC tSQLt.AssertEquals @expected, @actual;

END;
GO


Now you will get the following in your tSQLt test run output

Disabled Test: [testFinancialApp].[test that ConvertCurrency converts using given conversion rate]

This will be right above this text

+----------------------+
|Test Execution Summary|
+----------------------+



I want this printed so we know we have disabled tests when we get a email whenever Jenkins kicks off these tests…..


To enable the test again, all you have to do is change the value from 1 to 0 here. So instead of

DECLARE @IsTestDisabled bit = 1

You would make it

DECLARE @IsTestDisabled bit = 0


To see all my tSQLt posts, visit this link: http://sqlservercode.blogspot.com/search/label/tSQLt

Monday, May 02, 2016

SQL Server 2016 will be generally available on June 1, 2016


Mark your calendars, SQL Server will be generally available less than a month from now, June 1st 2016.

You don't have a reason not to play around with SQL Server 2016, in March it was announced that the SQL Server 2016 Developer edition will also be available as a free download.

Also today, Lenovo published a new #1 TPC-H 30 TB world record using SQL Server 2016 and Windows Server 2016 on Lenovo System x2950 X6

To find out about all the difference between the various editions, download this pdf: SQL Server 2016 editions –what’s new 

For the official announcement, see here: Get ready, SQL Server 2016 coming on June 1st