Comments
Sort by recent activity
Okay, let’s try leaving SSC out of the mix for a moment.
In the “shared†model, all database developers write their code against the same database. Hopefully they connect to this database with their own dedicated login (whether Windows or SQL Server) rather than some global dev user account. There is a single dev database and everyone develops in that.
In the "dedicated" model, each developer makes their changes against their own dedicated database. This could be multiple databases on the same SQL Server instance: PubsTom, PubsDick, PubsHarry etc but it is more likely to be set up so that each developer has their own dedicated SQL Server instance. This might be a VM or named instance on a shared physical server or SQL Server Developer edition (or even SQL Express) installed on each developer’s desktop.
User is what you think it is – a Windows or SQL Server login.
“Sandbox†is just a term used to describe a dedicated development environment where a developer can “play†without impacting on anyone else. It is a physical thing and you can only have a sandbox if operating the “dedicated†model as a shared database cannot, by definition, be a sandbox - unless everyone else has gone home for the night maybe [image]
So the question in SQL Source Control when you bring a database under source control relates to whether all your database developers work in one shared database or whether they each develop in their own dedicated database. As far as I know, it is not possible to combine both approaches in SSC.
I am not sure how SSC would work if all your developers use a single global user account like “sa†to develop. / comments
Okay, let’s try leaving SSC out of the mix for a moment.
In the “shared†model, all database developers write their code against the same database. Hopefully they connect to this database wit...
Yes that is exactly what I'm saying. A function (of any type) is just like any other module - if you re-name and re-populate a referenced table (using FakeTable) - the function will use the data you supply from the renamed table(s).
simonjmartin wrote:
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
Yes that is exactly what I'm saying. A function (of any type) is just like any other module - if you re-name and re-populate a referenced table (using FakeTable) - the function will use the data y...
The closest you can get is faking any underlying tables which should at least reduce some of the setup requirements.
The only other alternative would be to code up an ALTER FUNCTION statement using dynamic SQL that just returns the data you want it to. This would get rolled back along with any other changes when the test completes.
I'm thinking something like this: DECLARE @sql varchar(MAX);
SET @sql = 'ALTER FUNCTION [dbo].[TVF_PROMsGetUploadedScorecardsFilteredByHeaders]
(
@PipedHeaderIDs varchar(50)
)
RETURNS @tblVariable table
(
UploadedID int
)
AS
BEGIN
INSERT @tblVariable
(
UploadedID
)
SELECT 99
UNION SELECT 52
UNION SELECT 51
RETURN
END'
BEGIN TRAN
EXEC (@sql)
SELECT * FROM TVF_PROMsGetUploadedScorecardsFilteredByHeaders('')
ROLLBACK TRAN
/ comments
The closest you can get is faking any underlying tables which should at least reduce some of the setup requirements.
The only other alternative would be to code up an ALTER FUNCTION statement using...
You can try quickly throwing a PRINT statement into the SetUp procedure and exceute the test(s) acheck the output in SSMS see if it is definitely being called.
As with other users, specifying the schema correctly in all references is a common typo/gotcha.
Are you running against a case-sensitive instance/database? There were a couple of historic bugs around casing for the Setup procedure name - fixed in different versions.
Otherwise, try posting your code and we'll see if we can help / comments
You can try quickly throwing a PRINT statement into the SetUp procedure and exceute the test(s) acheck the output in SSMS see if it is definitely being called.
As with other users, specifying the s...
Simon, just a thought - if you're having issues with FakeTable it might be worth starting a new thread.
FakeTable itself is pretty reliable in my experience, but schema-level tasts can introduce some interesting questions around how we do certain types of test. / comments
Simon, just a thought - if you're having issues with FakeTable it might be worth starting a new thread.
FakeTable itself is pretty reliable in my experience, but schema-level tasts can introduce so...
Simon,
Ok so we know that [SetUp] is being called. And, if you've corrected the schema references, we need to find out whether the table is actually created and if it is, at point it disappears again.
Try adding this to the end of your SetUp procedure:
IF OBJECT_ID(N'BoardSummary.Actual') > 0
RAISERROR('SetUp: [Actual] table exists in BoardSummary schema', 0, 1) WITH NOWAIT;
ELSE IF OBJECT_ID(N'dbo.Actual') > 0
RAISERROR('SetUp: [Actual] table not found in BoardSummary schema but deos exist in dbo schema', 0, 1) WITH NOWAIT;
ELSE
RAISERROR('SetUp: [Actual] table not found in BoardSummary schema', 0, 1) WITH NOWAIT;
Then add this at the top of the test procedure IF OBJECT_ID(N'BoardSummary.Actual') > 0
RAISERROR('In Test: [Actual] table exists in BoardSummary schema', 0, 1) WITH NOWAIT;
ELSE
RAISERROR('In Test: [Actual] table not found in BoardSummary schema', 0, 1) WITH NOWAIT;
and then add this just before you try to insert the Actual table:
IF OBJECT_ID(N'BoardSummary.Actual') > 0
RAISERROR('Before Assert: [Actual] table exists in BoardSummary schema', 0, 1) WITH NOWAIT;
ELSE
RAISERROR('Before Assert: [Actual] table not found in BoardSummary schema', 0, 1) WITH NOWAIT;
Then just run the one test with tSQLt.Run and see what the output is.
Also, you were correct in naming the set-up procedure [SetUp]. tSQLt looks for this before running each test. If you'd left it as [test SetUp], it would have got called once because it was treated as just a regular test.
Also, in case it wasn't clear earlier, the reason why I recommend putting the [actual] and [expected] tables in the test schema rather than dbo is because one day you might actually be testing a database that has a table named like that. / comments
Simon,
Ok so we know that [SetUp] is being called. And, if you've corrected the schema references, we need to find out whether the table is actually created and if it is, at point it disappears aga...
Simon,
I think I've just spotted your issue. It's difficult to see on the web page without sysntax 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'
/ comments
Simon,
I think I've just spotted your issue. It's difficult to see on the web page without sysntax highlighting and I've only just seen it on a another review of the code.
When you call AssertEqua...
Simon,
I'm glad my tutorials have inspired you. The difficulty is that once you start using TDD, you'll struggle to work without it.
NB: Odd, I didn't see the refactored code when I first looked
Looking at the example code you posted, I'm assuming that this is before refactoring creation of the actual and expected table out into the setup method. The most obvious thing to check is that you also remove the DROP TABLE statements at the beginning of [BoardSummary].[test GetBoardSummary_2ScorecardsAdded_MaxMinUsedForRanges]. In fact arguably the DROPS aren't really required since the CREATES get rolled back at the end of each test. It's my OCD that makes me do it in my code :-)
If that's not the problem, try creating a [BoardSummary].[SetUp] that just does RAISERROR('Setup called!', 0, 1) WITH NOWAIT; then run just one test tSQL.Run '[BoardSummary].[test GetBoardSummary_2ScorecardsAdded_MaxMinUsedForRanges]' in another window to see if SetUp does actually get called. Then build things up from there.
Another thing to check (although a longshot) if you happen to be running on a case-sensitive database is that you are running the latest version of the tSQLt framework SELECT * FROM tSQLt.Info() should yield 1.0.4504.21220. There was a small issue in an earlier version WRT case-sensitivity of the SetUp call. Mind you if you've installed it via SQL Test this shouldn't be a problem.
If these suggestions don't work, let me know and I'll see what else I can come up with / comments
Simon,
I'm glad my tutorials have inspired you. The difficulty is that once you start using TDD, you'll struggle to work without it.
NB: Odd, I didn't see the refactored code when I first looked
L...