SSIS Junkie : sql server

AdventureWorks on Azure donates GBP351.49 to War Child. THANK YOU!

jamiet — Sun, 07 Apr 2013 21:02:35 GMT

Just over 12 months ago I published a blog post entitled AdventureWorks2012 now available for all on SQL Azure. In it I explained that I had set up a Windows Azure SQL Database (then known as SQL Azure database) for the SQL Server community to use and hence familiarise themselves with the SQL Azure offering – I called this initiative “AdventureWorks on Azure”. Judging by the comments that were left in the table that I set up for that very purpose it seems that a number of people were in favour and made use of it:

The credentials to enable you to connect up and leave your own comment are in that same blog post!

Running a SQL Azure database is of course not free hence I asked for the community to support the initiative by contributing via PayPal to its upkeep. I hoped that there might be enough left over to make a charitable donation and as you have probably gathered from the title of this blog post I’m delighted to be able to say that that did indeed happen. A few minutes ago I made a contribution to War Child (the same charity that the SQL Server MVP Deep Dives book donated to) of £351.49

What do War Child do? In their own words:

We look forward to a world in which children's lives aren't torn apart by war.

There's 27 of us in an old false-teeth factory in north London.
We're trying to change the world.

You could say we're a small charity with big ambitions.

We're directly transforming the lives of tens of thousands of children. And we're campaigning to improve the lives of millions more.

Thank you to everyone that made a donation especially to the extremely generous person (who shall remain nameless – you know who you are) that contributed $105.

I would like to keep AdventureWorks on Azure going for another year at least but to do that I need more donations. If you would like to support this initiative for another year take a read of the instructions below that I have copied from last year’s blog post:

AdventureWorks on Azure is being provided for the SQL Server community to use and so I am hoping that that same community will rally around to support this effort by making a voluntary donation to support the upkeep which, going on current pricing, is going to be $119.88 per year. If you would like to contribute to keep AdventureWorks on Azure up and running for that full year please donate via PayPal to adventureworksazure@hotmail.co.uk:

Any amount, no matter how small, will help.

Thank you once again to everyone that donated!

@Jamiet

Connected development in SSDT versus SSMS

jamiet — Tue, 19 Mar 2013 16:28:00 GMT

When you install the database projects template of SSDT you get SQL Server Object Explorer (SSOX) installed as well. SSOX is a pane within Visual Studio and is the main enabler of the Connected Development experience that the SSDT team have attempted to provide.

SSOX provides some really cool capabilities that are not in SQL Server Management Studio (I hope to blog about them in the near future). In theory these capabilities make it possible for a database developer to spend all their time in SSDT (i.e. Visual Studio) thus making SSMS a pureplay DBA tool (this does of course depend on your definition of both a database developer and a DBA, but I’m not getting into that debate here).

With that in mind I have spent a few days trying to work without SSMS, preferring to live wholly inside Visual Studio instead. By and large I was able to do everything I needed to do from within Visual Studio however there were a few nuances about the experience that kept pushing me back to SSMS, I detail those nuances below.

Server groups

SSOX combines the functions of SSMS’s Object Explorer and Registered Servers pane. I don’t mind either way of working but it does mean that there is no ability to group servers in SSOX like you can in the Registered Servers pane

F6

In SSMS I regularly use the F6 keyboard shortcut to jump between the query, results & messages panes of a query window. No such keyboard shortcut exists in SSDT and they’ve already canned my request on Connect to get this fixed (even though it laughably has status “closed as fixed”).

UPDATE: See the comments below where Brett Gerhardi informed me of a different keyboard shortcut that does the same thing as F6. Actually its not quite the same, if you have multiple resultsets in your results pane then the behaviour is slightly different to F6 in SSMS - but that's not an issue you'll hot frequently.

Change Connection

The context menu in SSMS provides the ability to change a connection as well as connect and disconnect:

SSDT doesn’t have change connection and believe me, you don’t know how much you use a feature until its not there:

There’s also no hotkey to jump to “Connection” on the context menu like there is in SSMS (“C”) and I find that annoying too.

Those were the main annoyances that forced me back to SSMS. The lack of F6 was a major bugbear for me as I am a big keyboard shortcut junkie. If such things don’t bother you then you may be able to live in Visual Studio quite happily. If you have any similar experiences to share I’d be keen to read them.

@Jamiet

sp_ssiscatalog v1.0.2.0 now available for download [SSIS]

jamiet — Mon, 11 Mar 2013 22:51:20 GMT

v1.0.2.0 – what’s in it?

Things have been a bit quiet on the sp_ssiscatalog front since I last blogged about it three months ago in December 2012. Rest-assured development continues apace however and today I’m making available a minor update, v1.0.2.0 which is now available for download from Codeplex. For those that don’t know I describe sp_ssiscatalog as:

sp_ssiscatalog is a stored procedure that makes it easy to query for information that is strewn around the SSIS Catalog.

There aren’t too many functional changes in this release, it is more focused on making sp_ssiscatalog easier to use. Back in Documenting sp_ssiscatalog I explained how I was adding documentation to the messages tab of SSMS. Hence as of this new release when you execute sp_ssiscatalog you will see information such as this:

which I think should be very useful for anyone that wants to use sp_ssiscatalog to its fullest. Even I who wrote the thing and has been using it day-in, day-out for quite some time now can’t remember the names of all the parameters – now I no longer have to!

Note that you can turn off the display of the documentation using the @show_docs parameter:

exec sp_ssiscatalog @show_docs=0

If you want to display only the documentation and not actually have sp_ssiscatalog do any querying of the SSIS Catalog its @show_docs_only:

exec sp_ssiscatalog @show_docs_only=1

If you have any suggestions for future enhancements please put them in the comments below or submit them to the discussions page on the Codeplex site.

As a reminder, here is the sort of thing you can do with sp_ssiscatalog:

--Return all failed executions  
EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_status_desc='failed'


--Return all executions for a specified folder
EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_folder_name='My folder'


--Return all executions of a specified package in a specified project
EXEC [dbo].[sp_ssiscatalog] @operation_type='execs',@execs_project_name='My project', @execs_package_name='Pkg.dtsx'


--Return information about the most recent execution
EXEC [dbo].[sp_ssiscatalog]

One last thing, if sp_ssiscatalog is useful to you and you’d like to support future development feel free to donate to my personal beer fund at http://www.paypal.com/cgi-bin/webscr?cmd=_xclick&business=jamie@jamie-thomson.net&item_name=Supporting%20sp_ssiscatalog.

Installation Instructions

Download the zip file at DB v1.0.2.0. It contains two files, SsisReportingPack.dacpac & SSISDB.dacpac
Unzip to a folder of your choosing
Open a command prompt and change to the directory into which you unzipped the files
Execute:
- "%PROGRAMFILES(x86)%\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe" /a:Publish /tdn:SsisReportingPack /sf:SSISReportingPack.dacpac /v:SSISDB=SSISDB /tsn:(local)
  (/tsn specifies the target server, change as appropriate. /tdn specifies the database name, you can call it whatever you like.)

If everything works OK you’ll see something like the following:

This will (if it doesn’t already exist) create a database called [SsisReportingPack] (or whatever you chose to call it) which contains [dbo].[sp_ssiscatalog].

Deployment of client-specific database code using SSDT

jamiet — Sun, 10 Mar 2013 13:24:00 GMT

Yesterday I attended SQL Saturday 194 in Exeter for which many people deserve credit but especially the organisers Jonathan Allen & Annette Allen and the sponsors Fusion-IO, Confio Software, Nexus, Big Bang Data Company, Purple Frog Systems, Redgate, idera, Pluralsight, Jurys Inn Exeter & Wrox.

I gave a talk entitled Declarative Database Development with SSDT; both during the session and then afterwards on Twitter Leo Pasta asked me

would you have any extra references on how to handle client-specific changes to the DB schema

I promised Leo an answer, hence this blog post.

If I understand Leo’s question correctly he has an application that is used by multiple clients, he has a separate instance of the application for every client, and the database requires client-specific customisations; luckily for Leo there is a new feature in SSDT that is perfect for this situation. First let’s set up our solution in SSDT:

We have three database projects:

_core - This contains all the objects that are common to all clients. For demonstration purposes it simply contains a table called [Sales]
clientFoo – All the objects that are required only by client “Foo”. In this case there is view which aggregates the data in [Sales] by Customer

clientBar – All the objects that are required only by client “Bar”. In this case there is view which aggregates the data in [Sales] by Location

In order that the reference to table [Sales] in the two views can be resolved both of the client-specific projects have a database reference to _core:

It is those database references that allows objects in clientFoo/clientBar to refer to objects in _core. Now here’s the important bit. When we set up those two database references we must specify that the objects in the referenced project are intended to be in the same database:

Having objects from one database split over multiple projects is called composite projects. The effect of this is that (by default) whenever clientFoo or clientBar are deployed the objects in _core will get deployed as well. We can see this in the output when we deploy clientFoo:

Notice how even though we chose not to deploy _core two objects have been created; table [Sales] (from the _core project) and view [vSalesPerCustomer]. This is the new feature in SSDT that I spoke of above – a deployment of a project will (by default) also deploy all the objects in referenced projects where objects in the referenced database are intended to be in the same database (incidentally this functionality replaces the “partial projects” feature from previous incarnations of SSDT).

Note that you can change this default behaviour in the advanced publish settings by unchecking the “Include composite objects” box:

Hence, with the “Include Composite Objects” setting turned on, we can deploy both projects clientFoo & clientBar and both will contain table [Sales] plus their own client-specific view:

which (I hope) is exactly what Leo was after!

That’s all there is to it. A very very nice new feature of SSDT!

@Jamiet

New job, new computer, new software installs

jamiet — Thu, 07 Mar 2013 09:35:48 GMT

Three days ago I started a new job and put a shout out on Twitter looking for suggestions as to what software bits-n-pieces I as a SQL Server developer should install.

I got some great replies (click here to see them all) and here is the list of all “stuff” that I then installed (N.B. SQL Server and Visual Studio came pre-installed):

Redgate SQLPrompt
Pragmatic Works DBA Express (in order to get Data Surf)
SQL Sentry Plan Explorer
SQL Server Data Tools
BIDS Helper
Notepad++
Sublime Text
SSMS Tools Pack
Powershell 3.0
SQLPSX
SnagIt
PureText
Visual Studio Productivity Power Tools
Live Writer
SkyDrive desktop app
Chrome
Yammer Desktop App

A lot of those aren’t strictly speaking SQL Server related but, for me, they’re still invaluable.

Do you have a similar list? Don’t be afraid to share ‘em and put a link below! And let me know if you think I’ve missed anything important.

@Jamiet

Weekend reading: Improvements to ODBC Data Source Adminstrator & dacpac vs bacpacs

jamiet — Mon, 18 Feb 2013 13:16:07 GMT

I read a few articles over the weekend and learnt a few things that I think might be worth bringing to folks’ attention.

ODBC Administrator gets a lick of paint in Windows 8/Windows server 2012

The ODBC Administrator in Windows 8 has had some minor improvements. There’s now no longer a 32bit & a 64bit version of it, there’s only one and it has a new column to tell you whether the 32bit, 64bit drivers or both are installed.

There are also a few PowerShell cmdlets to help with administration of ODBC Data Sources which if you’re a fan of automating deployments (as I am) is great news. Read more at ODBC DSN Management in the Next Release of Windows (code-named Windows “8” and Windows Server “8”).

Bacpacs days may be numbered

Bacpacs are files that act as an easy portable backup of schema+data of a SQL Server database and are typically used to move data between an on-premise SQL Server instance & Windows Azure SQL Database (aka SQL Azure). Recently however Dacpacs gained the ability to deploy data also so I asked the question on the SSDT forum Given that a dacpac can now deploy data, why would I ever need bacpacs? Gert Drapers, who until recently ran the team that builds SSDT and the DAC Framework, replied with:

BACPACs are a left over of the past more then anything.The main reason will be that most of the existing tooling, SSMS 2012, the Azure management portal and teh Azure Import Export service, only support BACPACs.
Right now the data support between the two is identical, DACPAC are or were supposed to add incremental data deployment, as right now it expects an empty table to load the data in to. Overtime I expect BACPAC to fade away and its usage to be replaced by DACPAC with data.

In other words the only reason right now to use dacpacs is that that is what the tooling supports however you should expect that to change in the future. Note that Gert is no longer on the team that builds those bits so don’t quote this as being a formal roadmap.

@Jamiet

Which version of SSDT Database Projects do I have installed?

jamiet — Fri, 08 Feb 2013 21:15:52 GMT

SQL Server Data Tools (SSDT) is released on a very regular cadence (note that I’m talking about the database projects part of SSDT, this is nothing to do with SSIS/SSAS/SSRS). Since the first release in March 2012 there have been releases in September 2012, November 2012, & December 2012. I personally have found it difficult to keep track of not only which particular version I am using but also that which are my colleagues are using – quite simply I didn’t know how to discover the current version number. I assumed that the answer would lie within Visual Studio 2012 in the Help->About Microsoft Visual Studio menu option, when I checked there I saw this:

A version number – 11.1.21208.0. What does that mean though? To understand more we have to go to Add/Remove programs:

There are many different items listed there, let’s look at them in turn:

Microsoft SQL Server 2012 Data-Tier App Framework. This is fairly self-explanatory if you know what the Data-Tier App Framework (aka DACFx) is. If you don’t, see the notes at the end of this blog post*. The reason that you see two entries in the list above is due to my having the 32bit & 64bit versions installed – the fact that there is no differentiation between the two in the title is a bug.
Microsoft SQL Server 2012 Express LocalDB. LocalDB is the small server-less version of SQL Server that now gets deployed with SQL Server proper. LocalDB also gets installed with SSDT if you do not already have it, but you should consider it part of SQL Server itself.
Microsoft SQL Server Data Tools 2010. This was described to me as the “installation chainer” – it is the installation component that manages SSDT and its dependencies. I think of it as the SSDT shell within Visual Studio 2010.
Microsoft SQL Server Data Tools 2012. This was described to me as the “installation chainer” – it is the installation component that manages SSDT and its dependencies. I think of it as the SSDT shell within Visual Studio 2012. Note that the version number is the same as in the Help->About screenshot at the top of this article – that is because they are one and the same.
Microsoft SQL Server Data Tools – Database Projects – Web installer entry point. A stub project that gets installed when you install SQL Server. Its the SSDT database project entry point.
Microsoft SQL Server Data Tools – enu (10.3.21208.0) & Microsoft SQL Server Data Tools – enu (11.1.21208.0). These are the things we’re interested in. These *are* SSDT database projects, for Visual Studio 2010 & Visual Studio 2012 respectively. The version numbers are those for the December 2012 release; note that the build numbers are the same (X.X.21208.X), only the major and minor numbers are different.
Microsoft SQL Server Data Tools Build Utilities – enu (10.3.21208.0) & Microsoft SQL Server Data Tools Build Utilities – enu (11.1.21208.0). Everything you need to do a headless (i.e. without having Visual Studio installed) build/deploy of an SSDT database project.

So at the end of all that we have determined the following version numbers for SSDT database projects are:

Release	Visual Studio 2010 version number	Visual Studio 2012 version number
September 2012	10.3.20905.0	11.1.20905.0
November 2012	10.3.21101.1	11.1.21101.1
December 2012	10.3.21208.0	11.1.21208.0

I do not know the version numbers for the March 2012, September 2012 & November 2012 releases (if anyone out there does know please let me know in the comments) however I shall update this blog post for future releases. September 2012 and November 2012 releases now added!

I have requested that any blog post to announce future releases should contain the version numbers. Let’s hope that happens.

@Jamiet

* DACFx is described here as:

The Microsoft® SQL Server® 2012 Data-Tier Application Framework (DACFx) is a component which provides application lifecycle services for database development and management for Microsoft® SQL Server® and Windows Azure SQL Databases.
DacFX supports various database deployment and management scenarios for SQL Server and Windows Azure SQL Databases including extracting/exporting a live database to a DAC package, deploying a DAC package to a new or existing database, and migrating from on-premise SQL Server to Windows Azure. This functionality is exposed via the DACFx managed API. DACFx can target SQL Server 2005 SP4, 2008 SP1, 2008R2, 2012, and Windows Azure SQL Databases. DACFx also provides the command-line utility SqlPackage.exe for creating and deploying .dacpac and .bacpac packages. DACFx supports scenarios provided by SQL Server client tooling including SQL Server Data Tools and SQL Server Management Studio 2012.

Obtaining rowcounts when using Composable DML [T-SQL]

jamiet — Thu, 07 Feb 2013 13:59:57 GMT

In my August 2009 blog post Exploring Composable DML I introduced a new feature in SQL Server 2008 called Composable DML and also outlined one of its limitations; namely that data from the OUTPUT cannot be aggregated prior to insertion. Composable DML does have some useful scenarios however and one of those is in capturing and storing values that are replaced by an UPDATE (which I have talked about before in Using Composable DML to maintain entity history). Here’s the basic premise:

INSERT old_values( id, name ) --use Composable DML to store the values that were replaced by an UPDATE
SELECT	mrgout.deleted_id
,		mrgout.deleted_name
FROM	(
		MERGE	tgt
		USING	src
			ON	tgt.id = src.id
		WHEN MATCHED THEN  UPDATE	
			SET	tgt.NAME = src.NAME   WHEN NOT MATCHED THEN   INSERT(id,name)
			VALUES(src.id,src.name)
		OUTPUT $ACTION AS action_
		,		INSERTED.id AS inserted_id
		,		INSERTED.NAME AS inserted_name
		,		DELETED.id AS deleted_id
		,		DELETED.name AS deleted_name
		)mrgout
WHERE	mrgout.action_ = 'UPDATE' --Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table
;

This statement updates some rows in [tgt] and stores the old values in [old_values]. I think that’s rather useful, especially in a data warehousing scenario where one may wish to MERGE to a type 1 dimension table. Unfortunately this scenario gives rise to another limitation of Composable DML – the value returned by @@ROWCOUNT is the number of rows that were affected in [old_values], not in [tgt]. The following code (which you can simply copy/paste and execute and which is also available on pastebin) demonstrates this problem:

/******************************************************************************************************************************
A demonstration of capturing rowcounts when using composable DML. The problem I'm trying to demonstrate here is that 
I don't think there is a way to capture the number of rows affected by the MERGE
 
Jamie Thomson, 2013-02-07
******************************************************************************************************************************/
 
/*Setup table first and insert some data into [src]*/
USE tempdb
IF OBJECT_ID('src') IS NOT NULL		DROP TABLE src;
CREATE TABLE src (
	id		INT
,	name    NVARCHAR(MAX)
);
IF OBJECT_ID('tgt') IS NOT NULL		DROP TABLE tgt;
CREATE TABLE tgt (
	id		INT
,	name    NVARCHAR(MAX)
);
/*[updates] will be used as the target of the Composable DML insertion*/
IF OBJECT_ID('old_values') IS NOT NULL		DROP TABLE old_values;
CREATE TABLE old_values (
	id		INT
,	name    NVARCHAR(MAX)
);
INSERT src(id,name)VALUES(1,'don'),(2,'kaina');
GO
 
/*Everything after here gets run twice because the batch ends with GO 2*/
INSERT old_values( id, name ) --use Composable DML to store the values that were replaced by an UPDATE
SELECT	mrgout.deleted_id
,		mrgout.deleted_name
FROM	(
		MERGE	tgt
		USING	src
			ON	tgt.id = src.id
		WHEN MATCHED THEN  UPDATE	
			SET	tgt.NAME = src.NAME   WHEN NOT MATCHED THEN   INSERT(id,name)
			VALUES(src.id,src.name)
		OUTPUT $ACTION AS action_
		,		INSERTED.id AS inserted_id
		,		INSERTED.NAME AS inserted_name
		,		DELETED.id AS deleted_id
		,		DELETED.name AS deleted_name
		)mrgout
WHERE	mrgout.action_ = 'UPDATE' --Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table
;
SELECT	[@@ROWCOUNT]=@@ROWCOUNT,row_tally_in_tgt=(SELECT COUNT(*) FROM tgt)  -- <-Rowcount only provides tally of rows affected by the outer INSERT, not the MERGE
GO 2

Here is the output:

Notice that the Composable DML containing the MERGE statement is executed twice. The first execution inserts two rows into [tgt] yet @@ROWCOUNT returns zero because zero rows were inserted into [old_values] by the outer query. The second execution results in two rows in [tgt] being updated hence two rows are inserted into [old_values] and hence why @@ROWCOUNT returns two. It appears there is no way to discover the number of inserts or updates that were committed by the MERGE; if you’re a fan of logging rowcounts during ETL operations (which I think you should be) then this is a big problem. The only way I can think of getting around this problem is to break the statement into two like so (for brevity I haven’t included the full code listing so it is also available on pastebin):

/*Setup part is the same as before, we do need an extra table for capturing the output of our MERGE tho*/
IF OBJECT_ID('mrgout') IS NOT NULL		DROP TABLE mrgout;
CREATE TABLE mrgout (
	action_			NVARCHAR(MAX)
,	inserted_id		INT
,	inserted_name    NVARCHAR(MAX)
,	deleted_id		INT
,	deleted_name    NVARCHAR(MAX)
);
INSERT src(id,name)VALUES(1,'don'),(2,'kaina');
GO
 
/*Everything after here gets run twice because the batch ends with GO 2*/
TRUNCATE TABLE mrgout;
INSERT mrgout(action_, inserted_id, inserted_name,deleted_id, deleted_name )
SELECT	mrgout.action_
,		mrgout.inserted_id
,		mrgout.inserted_name
,		mrgout.deleted_id
,		mrgout.deleted_name
FROM	(
		MERGE	tgt
		USING	src
			ON	tgt.id = src.id
		WHEN MATCHED THEN  UPDATE	
			SET	tgt.NAME = src.NAME   WHEN NOT MATCHED THEN   INSERT(id,name)
			VALUES(src.id,src.name)
		OUTPUT $ACTION AS action_
		,		INSERTED.id AS inserted_id
		,		INSERTED.NAME AS inserted_name
		,		DELETED.id AS deleted_id
		,		DELETED.name AS deleted_name
		)mrgout
;
INSERT	dbo.old_values(id,name)
SELECT	deleted_id,deleted_name FROM mrgout
WHERE	mrgout.action_ = 'UPDATE' --Filtering on $action=UPDATE allows us to get the replaced values from DELETED virtual table
SELECT	[INSERT_@@ROWCOUNT]=(SELECT COUNT(*) FROM mrgout WHERE action_ = 'INSERT'),[UPDATE_@@ROWCOUNT]=(SELECT COUNT(*) FROM mrgout WHERE action_ = 'UPDATE'),row_tally_in_tgt=(SELECT COUNT(*) FROM tgt)   GO 2

Executing that returns:

This is much better. We now know the tally of insertions and updates committed by the MERGE, unfortunately we have had to do it in two separate statements which in a way defeats the point of using MERGE in the first place (and don’t forget some of the other current problems with MERGE). If you can think of a better way of doing it then I’m all ears – please reply in the comments below.

I’m not saying don’t use MERGE and I’m not saying don’t use Composable DML; just be aware of their limitations. Personally I think there should be built-in functions, similar to @@ROWCOUNT, that return the number of rows INSERTed/DELETEd/UPDATEd by a MERGE; Aaron Bertrand agreed and raised a Connect submission to that affect: Katmai : Merge does not distinguish rowcounts in triggers which has, unfortunately, “been closed as won’t fix”.

@Jamiet

NOEXPAND query hint returns wrong results – CU fix now available

jamiet — Mon, 04 Feb 2013 10:24:00 GMT

Three days ago at my current gig we stumbled across a problem where use of the NOEXPAND query hint was causing different results compared to the same query without NOEXPAND. For those that do not know (which, until three days ago, included me) NOEXPAND governs the use of indexed views:

The indexed view is not expanded only if the view is directly referenced in the SELECT part of the query and WITH (NOEXPAND) or WITH (NOEXPAND, INDEX(index_value [ ,...n ] ) ) is specified.
http://msdn.microsoft.com/en-gb/library/ms181714.aspx

This screenshot demonstrates the problem:

Clearly this isn’t good. I put a comment out on Twitter hoping someone knew something about it and thankfully Gonçalo Ferreira was reading. He pointed me toward a Knowledge Base article entitled FIX: Incorrect result is returned when you query an indexed view by using the NOEXPAND hint in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012 that confirmed this as being a bug in SQL Server. The article is dated 24th January 2013 (that is only 11 days ago folks) and describes the bug rearing its head under these circumstances:

You create an indexed view for two tables that have a foreign key reference in Microsoft SQL Server 2008, in Microsoft SQL Server 2012 or in Microsoft SQL Server 2008 R2.

You update the base tables of the indexed view.

You run a query against the indexed view that uses the NOEXPAND hint.

In this scenario, you receive an incorrect result.

I don’t know about you but I read that as “If you’re using Indexed Views with NOEXPAND, they’re most likely giving you the wrong answer”. The latest cumulative update (CU) for:

SQL Server 2012
SQL Server 2012 SP1
SQL Server 2008 R2 SP2
SQL Server 2008 R2 SP1
SQL Server 2008 SP3

fixes the problem. If you’re using indexed views you may want to install the CU, sharpish!

@Jamiet

UPDATE. Paul White has posted a fantastic blog post where he explains how to reproduce the problem: Incorrect Results with Indexed Views (N.B. It occurs if you are using the MERGE statement) and Aaron Bertrand has followed with the equally readable If you are using indexed views and MERGE, please read this! where he offers some considered opinions and advice surrounding the issue. I highly recommend reading both.

Responses to SSDT questions

jamiet — Fri, 11 Jan 2013 22:22:24 GMT

My recent article Get to Know SQL Server 2012's SQL Server Data Tools prompted two questions to come to me via email and in the interests of sharing knowledge via search engines I thought I would answer them here rather than by simply replying by email (I hate that so much useful information gets trapped inside closed inboxes).

Question: How would you handle cases below, our main database has many objects already, we are using redgate source control to keep things under sourcecontrol but ssdt would be awesome.
Our database has some invalid stored procs etc, unfortunately we do not want to drop them, underlying objects either don't exists or has changed etc
There are linked servers or code referencing other objects stored in the database
There are staging table named automatically in the database and we do not want to include them in the project and not drop them or touch them in a deploy

JT: The overall question here seems to be “how do we prevent SSDT from DROPping sprocs and staging tables that are in the target database but are not in the SSDT project?” Fortunately SSDT has an option within it to allow exactly what the questioner requires. When an SSDT project is published one has the option to set some properties relating to that publish. One of the settings is “DROP objects in target but not in project”:

If that option is left unchecked (which is the default) then no objects will get removed from the target database.

Question: At my previous shop, I done a proof of concept with one of our databases, but never got it implemented as VS2012 (with SSDT) had not been released yet. Well, fast forward 9 or so months, and at my new shop (which is extremely agile) I've been tasked with setting up our db projects with SSDT so for the immediate short term goals/objectives we can;

Allow new or existing developers to be able to easily get the latest databases from source control downloaded from TFS, built and deployed onto our local sandbox db environments. This ideally will be done via a powershell script or something of the sort.
Be able to employ C.I. so that every night a database deployment is done to our system test servers.

3 days into it, and I've wrestled with 3 of our databases, reverse engineering them into TFS and getting them built etc. My question is, what are the best practices or guidelines for;

Allowing only Windows authentication for the local deploys to the sandbox environment. i.e. is it best to have a script with all our team user logins in a script? How would the project recognise a team member which will create a login for them so once the project is deployed they can login to their local db?
Using a 1-touch/click action (preferably outside of VS2010) to get latest, build project & deploy to local sandbox
Same as above but to a system test server

JT: There are quite a few questions there. Firstly I’ll say that its great to hear of sandboxed environments and Continuous Integration (CI) being used – I openly advocate these techniques in my blog post Implementing SQL Server solutions using Visual Studio 2010 Database Projects – a compendium of project experiences.

Now, taking each question in turn:

Windows Auth for local deploys. I’m not sure if there’s any recognised best practise here but if developers are working on local sandboxes I would assume they would have sysadmin rights so I’m not sure there’s a need to have a script of team logins. Perhaps I have misunderstood the question in which case please clarify in the comments below. I will say this though – I think its better to have a Windows group that you can move all of your devs in and out of rather than having to modify a script each time a user joins your team.
1-click get latest, build & deploy. Scripting your build & deploy is something else I advocate in the aforementioned blog post:

Building and deploying your datadude projects within Visual Studio can become a real time hog; in my experience its not unusual for deployments to take many minutes and your Visual Studio environment will be unavailable for further development work during that time. For that reason I recommend investing some time in writing some msbuild scripts that will build and deploy your project(s) from the command-line.

I’ll go further here and say that the script with which you use to build and deploy to your sandbox should be the same script as which you use for your production environment and everything in between. The script should take a parameter value to name the environment and then deploy appropriately.
See 2.

Hope that is useful. Any questions? Please put them in the comments.

@Jamiet

Get to Know SQL Server 2012's SQL Server Data Tools [article published]

jamiet — Wed, 09 Jan 2013 23:12:03 GMT

I recently wrote an article on SQL Server Data Tools for SQL Server Magazine and it has now been published online at Get to Know SQL Server 2012's SQL Server Data Tools. In the article I offer my thoughts and opinions on SQL Server Data Tools as it exists in SQL Server 2012.

Note that the article refers to SSDT database projects only, there is nothing in there pertaining to SSIS, SSAS or SSRS. If you find that confusing then you should probably go and at least read the first few paragraphs of the article where I try to alleviate some of that confusion.

Constructive feedback is, as always, very welcome.

@Jamiet

“Add as object” or “Add as script”? [SSDT]

jamiet — Fri, 04 Jan 2013 14:15:46 GMT

In SQL Server Data Tools (SSDT) one way to create a new object is to right click on a folder in Solution Explorer and point to Add. Doing so will display this menu:

A number of options are offered for creating a brand new object (“function”, “table”, “stored proc” etc…) and one of them is “Script”. If your preference is to handcraft the DDL for every database object without resorting to a GUI then you may choose to use the “Script” option because, after all, every file is just a DDL script – why not write the thing from scratch? There is however one fundamental difference between the Script” option and all the other options that you should be aware of.

For the screenshot below I added a script and handcrafted a simple CREATE TABLE statement:

Notice that the table is defined in the dbo schema but it is not appearing in SQL Server Object Explorer under the dbo schema node. Why is that? The answer is actually pretty simple. If I select Script1.sql in Solution Explorer and hit F4 the properties window appears and we see this:

The Build Action property of the file is set to “None” which means that the file does not get included in a build and hence any objects defined within that file will not appear in our database. This is the default behaviour when you Add as script whereas the default behaviour when you explicitly add an object is to set Build Action=Build. That is the fundamental difference between adding a script and adding a specific object.

This is one of those nuances of SSDT that is an easy problem to spot when you know about it but can cause you to spend hours hunting around for a solution if you do not (believe me, I’ve been there). Be aware of it!

@Jamiet

Warning! Publish may fail on creation of a FOREIGN KEY constraint and then immediately succeed [SSDT]

jamiet — Thu, 03 Jan 2013 12:15:06 GMT

I have stumbled across a nuance of SSDT that other users should be cognizant of. I published an SSDT database project onto an existing database that already had data in it but that Publish operation failed:

The error message was:

(10036,1): SQL72014: .Net SqlClient Data Provider: Msg 547, Level 16, State 0, Line 1 
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint 
"ActualsDay_fct_ent_CustomerHierarchyLvl03_FK_PayTo". 
The conflict occurred in database "PM_EDW", table "ent.CustomerHierarchyLvl03", column 
'CstHierLvl03_EK'.

The command that caused the error was:

ALTER TABLE [fct].[ActualsDay] WITH CHECK CHECK CONSTRAINT [ActualsDay_fct_ent_CustomerHierarchyLvl03_FK_PayTo];

In other words SSDT created a FOREIGN KEY constraint in my database and then attempted to check that existing data satisfied that constraint (hence the WITH CHECK of the command above). There was some data that violated that constraint and the Publish operation failed.
This is good, I definitely want a Publish operation to fail under these circumstances.

If you want to understand more about WITH CHECK take a read of How to load related tables.

A problem occurred though when I next published because that Publish operation actually succeeded:

even though I hadn’t changed anything in the project! If I check out the metadata of that FOREIGN KEY:

SELECT	name,is_not_trusted
FROM	sys.foreign_keys  WHERE	name = 'ActualsDay_fct_ent_CustomerHierarchyLvl03_FK_PayTo'

I see that [is_not_trusted] equals 1.

What does that mean, exactly? Well, SSMS’s table designer provides more useful information:

[is_not_trusted] means that my new FOREIGN KEY constraint has been created but the existing data has not been checked for compliance. Ouch! Not good, not good at all!

What’s going on here? If we break down the individual steps it becomes evident:

First Publish operation starts
FOREIGN KEY constraint gets created but without the WITH CHECK option
FOREIGN KEY constraint is ALTERed to have the WITH CHECK option. This fails due to existing data violating the constraint and hence the entire Publish operation fails
Second Publish operation starts
Publish engine only checks to see that the FOREIGN KEY constraint exists, which it does. Hence, no action is taken
Second Publish operation succeeds and I’m left with data that violates a newly created FOREIGN KEY constraint

This sequence of events can leave your database in a dangerous state and hence is something that SSDT users should be aware of. In my opinion this is a bug and I have raised it as such on Microsoft Connect: [SSDT bug] Publish will fail due to FK constraint and then succeed immediately after.

Be aware, be very aware!

@Jamiet

Query for server DefaultData & DefaultLog folders

jamiet — Mon, 10 Dec 2012 12:10:35 GMT

Do you ever need to query for the DefaultData & DefaultLog folders for your SQL Server instance? Well, I just did and the following script enabled me to do that:

DECLARE @HkeyLocal NVARCHAR(18),@MSSqlServerRegPath NVARCHAR(31),@InstanceRegPath SYSNAME;

SELECT @HkeyLocal=N'HKEY_LOCAL_MACHINE'
SELECT @MSSqlServerRegPath=N'SOFTWARE\Microsoft\MSSQLServer'
SELECT @InstanceRegPath=@MSSqlServerRegPath + N'\MSSQLServer'

DECLARE @SmoDefaultFile NVARCHAR(512)
EXEC MASTER.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultData', @SmoDefaultFile OUTPUT
DECLARE @SmoDefaultLog NVARCHAR(512)
EXEC MASTER.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'DefaultLog', @SmoDefaultLog OUTPUT

SELECT ISNULL(@SmoDefaultFile,N'') AS [DefaultFile],ISNULL(@SmoDefaultLog,N'') AS [DefaultLog]'

I haven’t done any rigorous testing or anything like that, all I can say is…it worked for me (on SQL Server 2012). Use as you see fit.

Doubtless this information exists in a multitude of other places but nevertheless I’m putting it here so I know where to find it in the future.

Just for fun I thought I’d try this out against ~~SQL Azure~~ Windows Azure SQL Database. Unsurprisingly it didn’t work there:

Msg 40515, Level 15, State 1, Line 16
Reference to database and/or server name in 'MASTER.dbo.xp_instance_regread' is not supported in this version of SQL Server.

@Jamiet

The perils of double-dash comments [T-SQL]

jamiet — Thu, 06 Dec 2012 09:21:39 GMT

I was checking my Twitter feed on my way in to work this morning and was alerted to an interesting blog post by Valentino Vranken that highlights a problem regarding the OLE DB Source in SSIS. In short, using double-dash comments in SQL statements within the OLE DB Source can cause unexpected results. It really is quite an important read if you’re developing SSIS packages so head over to SSIS OLE DB Source, Parameters And Comments: A Dangerous Mix! and be educated. Note that the problem is solved in SSIS2012 and Valentino explains exactly why.

If reading Valentino’s post has switched your brain into “learn mode” perhaps also check out my post SSIS: SELECT *... or select from a dropdown in an OLE DB Source component? which highlights another issue to be aware of when using the OLE DB Source.

As I was reading Valentino’s post I was reminded of a slidedeck by Chris Adkin entitled T-SQL Coding Guidelines where he recommends never using double-dash comments:

That’s good advice!

@Jamiet