How can we help you today? How can we help you today?
simonjmartin
Activity overview Badges (0) Posts (4) Comments (7)

Activity overview

Latest activity by simonjmartin

I've just figured out how to disable SQL Prompt in Visual Studio 2012. Run VS as Administrator; hold down Ctrl and right click it, then select "Run as Administrator..." this should let you unselect the Start up check box. Restart as normal and SQL Prompt is not enabled. Works great for me. I Love SQL Prompt in SSMS (and do all my database work in there), Visual Studio (for me) is for .NET development and I don't really do any SQL work in the IDE so having it running just gets in my way at times. Hope that helps / comments
  1. Community
  2. SQL Prompt Previous Versions
  3. cannot remove SQLprompt from VS2010
I've just figured out how to disable SQL Prompt in Visual Studio 2012. Run VS as Administrator; hold down Ctrl and right click it, then select "Run as Administrator..." this should let you unselect...
0 votes
Window corruption
  1. Community
  2. SQL Test
When I start SSMS the SQL Test window / tab is corrupted very much as in which is referenced in http://sqltest.uservoice.com/forums/140716-sql-test-forum/suggestions/3576855-tsqlt-window-popup-fai...
1 follower 1 comment 0 votes
Are you saying that if I fake the tables that TVF_PROMsGetUploadedScorecardsFilteredByHeaders references then when it runs it would query the data I've loaded as part of the test. So I could set up those tables to return data that matches what's being expected? / comments
  1. Community
  2. SQL Test
  3. Can TVFs be faked
Are you saying that if I fake the tables that TVF_PROMsGetUploadedScorecardsFilteredByHeaders references then when it runs it would query the data I've loaded as part of the test. So I could set up...
0 votes
Can TVFs be faked
  1. Community
  2. SQL Test
I am trying to isolate dependencies in a stored procedure I want to bring under test, one of which is a TVF. I have tried to use SpyProcedure on the TVF so I can return a known result but the messa...
2 followers 3 comments 0 votes
datacentricity wrote: I think I've just spotted your issue. It's difficult to see on the web page without syntax highlighting and I've only just seen it on a another review of the code. When you call AssertEqualsTable, if your expected and actual tables are in a schema other than dbo (which they are), you have to specify the schema name too. EXEC tSQLt.AssertEquals @Expected = 'BoardSummary.Expected', @Actual = 'BoardSummary.Actual' That was it! The assertion wasn't using the right schema :oops: / comments
  1. Community
  2. SQL Test
  3. Setup method?
datacentricity wrote: I think I've just spotted your issue. It's difficult to see on the web page without syntax highlighting and I've only just seen it on a another review of the code. When yo...
0 votes
There is a lot of set up code at the moment, I was hoping to refactor that out as in your tutorials, I had a go at fake tables but ran into issues so went with the full verbose approach to get things started. The PROMsBoardSummaryReport procedure is aggregating data from the tables I'm adding to above. There are constraints on those tables (foreign key etc) so faking the tables and only loading the minimum required data for the test is definitely where I'm aiming for. Then I was hoping to look into the Test Data Builder to further clean up the setup for these tests, but its small baby steps for me at the moment. Which is all good because once I've stumbled through things I can give a good presentation to my team. I definitely appreciate the help and depth of the reply, when trying to learn something new all the information comes in handy. It's getting over the gap between hello world and real world that's the big hurdle and having someone who knows what's what help out is invaluable / comments
  1. Community
  2. SQL Test
  3. Setup method?
There is a lot of set up code at the moment, I was hoping to refactor that out as in your tutorials, I had a go at fake tables but ran into issues so went with the full verbose approach to get thin...
0 votes
I'm getting closer, now when I run my test I get: Setup called! Warning: Null value is eliminated by an aggregate or other SET operation. [BoardSummary].[test GetBoardSummary_2ScorecardsAdded_MaxMinUsedForRanges] failed: 'Actual' does not exist I modified the SetUp so it should be using the same schema: CREATE TABLE [BoardSummary].Actual and in the test now: INSERT INTO [BoardSummary].Actual The version is returned as: 1.0.4504.21220 I like OCD coding, fewer things can bite you in the rear [image] / comments
  1. Community
  2. SQL Test
  3. Setup method?
I'm getting closer, now when I run my test I get: Setup called! Warning: Null value is eliminated by an aggregate or other SET operation. [BoardSummary].[test GetBoardSummary_2ScorecardsAdded_Max...
0 votes
This is the code I had: -- Comments here are associated with the test. -- For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/ ALTER PROCEDURE [BoardSummary].[test GetBoardSummary_2ScorecardsAdded_MaxMinUsedForRanges] AS BEGIN -- Arrange IF OBJECT_ID('Actual') IS NOT NULL DROP TABLE Actual IF OBJECT_ID('Expected') IS NOT NULL DROP TABLE Expected DECLARE @AreaID INT DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME DECLARE @ProviderList VARCHAR(150) DECLARE @RootReportLevelID INT SET @AreaID = 374 -- Leicester City PCT SET @StartDate = '2012-01-01 00:00:00' SET @EndDate = '2012-12-31 23:59:59' SET @ProviderList = N'TEST|' SET @RootReportLevelID = 380 -- The SHA to constrain results to just this area INSERT INTO tblPROMsPreferredProviders ( ShortName , FullName , FKHeaderFilterID , SUSCode , Preferred , Report , FKHeaderID ) VALUES ( 'TEST' , -- ShortName - varchar(50) 'TEST PROVIDER' , -- FullName - varchar(200) 513 , -- FKHeaderFilterID - int 'TEST' , -- SUSCode - varchar(5) 1 , -- Preferred - bit 2 , -- Report - int 71 -- FKHeaderID - int ) DECLARE @prmID INT -- Add the Highest scorecard EXEC apSaveUploadedScoreCardDetailsNew 'Leicester City PCT', -- varchar(100) '2012-07-09 08:24:46', -- datetime 'TEST', -- varchar(150) 'TEST', -- varchar(150) 185, -- int 'TEST', -- varchar(150) 1, -- int 'TEST', -- text '', -- text '', -- text '', -- text 1, -- bit 1, -- bit 1, -- bit @prmID OUT -- int EXEC apSaveUploadedScoreCardResults @prmUploadedID = @prmID, -- int @prmRating = 'T', -- char(3) @prmComments = '', -- text @prmQuestionID = 3448, -- int @prmMaxValue = 100, -- int @prmContribution = 90, -- int @prmGroupID = 217, -- int @prmResponseID = 527 -- int EXEC apAddUploadedScorecardHeader @prmScorecardHeaderID = 71, -- int @prmUploadedScorecardID = @prmID, -- int @prmHeaderText = 'TEST PROVIDER' -- text -- Add the Lowest scorecard EXEC apSaveUploadedScoreCardDetailsNew 'Leicester City PCT', -- varchar(100) '2012-07-09 08:24:46', -- datetime 'TEST', -- varchar(150) 'TEST', -- varchar(150) 185, -- int 'TEST', -- varchar(150) 1, -- int 'TEST', -- text '', -- text '', -- text '', -- text 1, -- bit 1, -- bit 1, -- bit @prmID OUT -- int EXEC apSaveUploadedScoreCardResults @prmUploadedID = @prmID, -- int @prmRating = 'T', -- char(3) @prmComments = '', -- text @prmQuestionID = 3448, -- int @prmMaxValue = 100, -- int @prmContribution = 35, -- int @prmGroupID = 217, -- int @prmResponseID = 527 -- int EXEC apAddUploadedScorecardHeader @prmScorecardHeaderID = 71, -- int @prmUploadedScorecardID = @prmID, -- int @prmHeaderText = 'TEST PROVIDER' -- text CREATE TABLE Expected ( GreenUpper DECIMAL(5,2) , GreenLower DECIMAL(5,2) , AmberUpper DECIMAL(5,2) , AmberLower DECIMAL(5,2) , RedUpper DECIMAL(5,2) , RedLower DECIMAL(5,2) ) INSERT INTO Expected VALUES ( 90, 80, 80, 60, 60, 35 ) --Act DECLARE @Exec TABLE ( GroupedBy VARCHAR(150) , PCTMax DECIMAL(5,2) , PCTAvg DECIMAL(5,2) , PCTMin DECIMAL(5,2) , AllAvg DECIMAL(5,2) , GreenUpper DECIMAL(5,2) , GreenLower DECIMAL(5,2) , AmberUpper DECIMAL(5,2) , AmberLower DECIMAL(5,2) , RedUpper DECIMAL(5,2) , RedLower DECIMAL(5,2) ) INSERT INTO @Exec ( GroupedBy , PCTMax , PCTAvg , PCTMin , AllAvg , GreenUpper , GreenLower , AmberUpper , AmberLower , RedUpper , RedLower ) EXEC apPROMsBoardSummaryReport @AreaID, @StartDate, @EndDate, @ProviderList, @RootReportLevelID CREATE TABLE Actual ( GreenUpper DECIMAL(5,2) , GreenLower DECIMAL(5,2) , AmberUpper DECIMAL(5,2) , AmberLower DECIMAL(5,2) , RedUpper DECIMAL(5,2) , RedLower DECIMAL(5,2) ) INSERT INTO Actual ( GreenUpper , GreenLower , AmberUpper , AmberLower , RedUpper , RedLower ) SELECT GreenUpper , GreenLower , AmberUpper , AmberLower , RedUpper , RedLower FROM @Exec AS E WHERE GroupedBy = 'TEST' --Assert -- For a complete list, see: http://tsqlt.org/user-guide/assertions/ EXEC tSQLt.AssertEqualsTable @Expected = N'Expected', -- nvarchar(max) @Actual = N'Actual', -- nvarchar(max) @FailMsg = N'Ranges for the values' -- nvarchar(max) END; any advice on how to keep it DRY would be appreciated. As I said the first thing I was trying to do was refactor out the Actual and Expected tables as I use them in all the other tests in this class I Refactored to this (with and without a 'test' prefix to SetUp: CREATE PROCEDURE [BoardSummary].[SetUp] AS BEGIN IF OBJECT_ID('Actual') IS NOT NULL DROP TABLE Actual; IF OBJECT_ID('Expected') IS NOT NULL DROP TABLE Expected; CREATE TABLE Expected ( GreenUpper DECIMAL(5,2) , GreenLower DECIMAL(5,2) , AmberUpper DECIMAL(5,2) , AmberLower DECIMAL(5,2) , RedUpper DECIMAL(5,2) , RedLower DECIMAL(5,2) ); CREATE TABLE Actual ( GreenUpper DECIMAL(5,2) , GreenLower DECIMAL(5,2) , AmberUpper DECIMAL(5,2) , AmberLower DECIMAL(5,2) , RedUpper DECIMAL(5,2) , RedLower DECIMAL(5,2) ); END; and for the test: -- Comments here are associated with the test. -- For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/ ALTER PROCEDURE [BoardSummary].[test GetBoardSummary_2ScorecardsAdded_MaxMinUsedForRanges] AS BEGIN -- Arrange DECLARE @AreaID INT DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME DECLARE @ProviderList VARCHAR(150) DECLARE @RootReportLevelID INT SET @AreaID = 374 -- Leicester City PCT SET @StartDate = '2012-01-01 00:00:00' SET @EndDate = '2012-12-31 23:59:59' SET @ProviderList = N'TEST|' SET @RootReportLevelID = 380 -- The SHA to constrain results to just this area INSERT INTO tblPROMsPreferredProviders ( ShortName , FullName , FKHeaderFilterID , SUSCode , Preferred , Report , FKHeaderID ) VALUES ( 'TEST' , -- ShortName - varchar(50) 'TEST PROVIDER' , -- FullName - varchar(200) 513 , -- FKHeaderFilterID - int 'TEST' , -- SUSCode - varchar(5) 1 , -- Preferred - bit 2 , -- Report - int 71 -- FKHeaderID - int ) DECLARE @prmID INT -- Add the Highest scorecard EXEC apSaveUploadedScoreCardDetailsNew 'Leicester City PCT', -- varchar(100) '2012-07-09 08:24:46', -- datetime 'TEST', -- varchar(150) 'TEST', -- varchar(150) 185, -- int 'TEST', -- varchar(150) 1, -- int 'TEST', -- text '', -- text '', -- text '', -- text 1, -- bit 1, -- bit 1, -- bit @prmID OUT -- int EXEC apSaveUploadedScoreCardResults @prmUploadedID = @prmID, -- int @prmRating = 'T', -- char(3) @prmComments = '', -- text @prmQuestionID = 3448, -- int @prmMaxValue = 100, -- int @prmContribution = 90, -- int @prmGroupID = 217, -- int @prmResponseID = 527 -- int EXEC apAddUploadedScorecardHeader @prmScorecardHeaderID = 71, -- int @prmUploadedScorecardID = @prmID, -- int @prmHeaderText = 'TEST PROVIDER' -- text -- Add the Lowest scorecard EXEC apSaveUploadedScoreCardDetailsNew 'Leicester City PCT', -- varchar(100) '2012-07-09 08:24:46', -- datetime 'TEST', -- varchar(150) 'TEST', -- varchar(150) 185, -- int 'TEST', -- varchar(150) 1, -- int 'TEST', -- text '', -- text '', -- text '', -- text 1, -- bit 1, -- bit 1, -- bit @prmID OUT -- int EXEC apSaveUploadedScoreCardResults @prmUploadedID = @prmID, -- int @prmRating = 'T', -- char(3) @prmComments = '', -- text @prmQuestionID = 3448, -- int @prmMaxValue = 100, -- int @prmContribution = 35, -- int @prmGroupID = 217, -- int @prmResponseID = 527 -- int EXEC apAddUploadedScorecardHeader @prmScorecardHeaderID = 71, -- int @prmUploadedScorecardID = @prmID, -- int @prmHeaderText = 'TEST PROVIDER' -- text INSERT INTO [BoardSummary].Expected VALUES ( 90, 80, 80, 60, 60, 35 ) --Act DECLARE @Exec TABLE ( GroupedBy VARCHAR(150) , PCTMax DECIMAL(5,2) , PCTAvg DECIMAL(5,2) , PCTMin DECIMAL(5,2) , AllAvg DECIMAL(5,2) , GreenUpper DECIMAL(5,2) , GreenLower DECIMAL(5,2) , AmberUpper DECIMAL(5,2) , AmberLower DECIMAL(5,2) , RedUpper DECIMAL(5,2) , RedLower DECIMAL(5,2) ) INSERT INTO @Exec ( GroupedBy , PCTMax , PCTAvg , PCTMin , AllAvg , GreenUpper , GreenLower , AmberUpper , AmberLower , RedUpper , RedLower ) EXEC apPROMsBoardSummaryReport @AreaID, @StartDate, @EndDate, @ProviderList, @RootReportLevelID INSERT INTO [BoardSummary].Actual ( GreenUpper , GreenLower , AmberUpper , AmberLower , RedUpper , RedLower ) SELECT GreenUpper , GreenLower , AmberUpper , AmberLower , RedUpper , RedLower FROM @Exec AS E WHERE GroupedBy = 'TEST' --Assert -- For a complete list, see: http://tsqlt.org/user-guide/assertions/ EXEC tSQLt.AssertEqualsTable @Expected = N'Expected', -- nvarchar(max) @Actual = N'Actual', -- nvarchar(max) @FailMsg = N'Ranges for the values' -- nvarchar(max) END; and this returns me the following: [BoardSummary].[test GetBoardSummary_2ScorecardsAdded_MaxMinUsedForRanges] failed: Invalid object name 'BoardSummary.Expected'.{test GetBoardSummary_2ScorecardsAdded_MaxMinUsedForRanges,100} [/quote] / comments
  1. Community
  2. SQL Test
  3. Setup method?
This is the code I had:-- Comments here are associated with the test. -- For test case examples, see: http://tsqlt.org/user-guide/tsqlt-tutorial/ ALTER PROCEDURE [BoardSummary].[test ...
0 votes
I have been following your series and it was actually the inspiration for finally taking the plunge with SQL Test. I was trying to refactor my Expected and Actual table creation to the SetUp method but after doing so all my tests are broken and the output shows that the [BoardSummary].[Actual] table does not exist... there are other similar failures on all my tests. It looked to me like the method simply wasn't being called. The class I've got for all my tests is BoardSummary, I created a new test in the class then renamed it to SetUp so I'm confident it is in the right class - but either I've massively mucked up the setup method or it isn't being called before the tests... I'm not sure. What would you recommend I try? / comments
  1. Community
  2. SQL Test
  3. Setup method?
I have been following your series and it was actually the inspiration for finally taking the plunge with SQL Test. I was trying to refactor my Expected and Actual table creation to the SetUp method...
0 votes
Setup method?
  1. Community
  2. SQL Test
Is SetUp deprecated? I thought tSQLt allowed a [Schema].[SetUp] sproc which would run before all tests in the schema, but I have been unable to get this working. How do I run common set up code, fo...
3 followers 14 comments 0 votes
Total activity
14
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
3