Search results matching tag 'SQL Server Data Tools'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=SQL+Server+Data+Tools&orTags=0Search results matching tag 'SQL Server Data Tools'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Learn from me about SSDT in London, February 2015http://sqlblog.com/blogs/jamie_thomson/archive/2014/12/02/learn-from-me-about-ssdt-in-london-february-2015.aspxTue, 02 Dec 2014 13:24:22 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:56886jamiet<p>Microsoft released SQL Server Data Tools (SSDT) along with SQL Server 2012 in the Spring of 2012. Since then I’ve noticed an upward tick in both the number of organisations that are using SSDT and the number of questions that are getting asked about it on forums. There is some confusion about what SSDT actually is (<a href="http://sqlblog.com/blogs/jamie_thomson/archive/2013/04/03/ssdt-naming-confusion-cleared-up-somewhat.aspx">Microsoft hasn’t helped there</a>), why people should be using SSDT and, most importantly, how to make best use of it. If you want to know more then a good place to start is my blog series <a href="http://sqlblog.com/blogs/jamie_thomson/archive/tags/10+days+of+SSDT/default.aspx" target="_blank">10 days of SSDT</a> or <a href="http://devproconnections.com/database-development/get-know-sql-server-2012s-sql-server-data-tools" target="_blank">Get to Know SQL Server 2012's SQL Server Data Tools</a> on devproconnections.com.</p> <p>Its clear that people want to learn more about SSDT so if those articles don’t satiate you know that I have joined forces with <a href="http://www.technitrain.com/">Technitrain</a> to offer a 2–day training course, in London, in February 2015 called <a href="http://www.technitrain.com/coursedetail.php?c=44&amp;trackingcode=JT1" target="_blank">Introduction to SQL Server Data Tools</a></p> <p><a href="http://www.technitrain.com/coursedetail.php?c=44&amp;trackingcode=JT1" target="_blank"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_690BD57C.png" width="556" height="234" /></a>&#160;</p> <p><a href="http://www.technitrain.com/coursedetail.php?c=28&amp;trackingcode=JT1"><img title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_24D3984E.png" width="380" height="84" /></a></p> <p>The course will cover:</p> <p><i>Day 1</i></p> <ul> <li>SSDT Positioning </li> <li>IDE Tour </li> <li>Connected Database Development </li> <li>Declarative, offline, database development </li> <li>Publishing </li> <li>Continuous Integration (CI) and Continuous Deployment </li> </ul> <p><i>Day 2</i></p> <ul> <li>Data Publishing </li> <li>Refactoring </li> <li>Database unit testing </li> <li>References and composite projects </li> <li>Database Drift </li> <li>Code analysis </li> </ul> <p>If this sounds like your bag then please <a href="http://www.technitrain.com/coursedetail.php?c=28&amp;trackingcode=JT1">sign up on the Technitrain website</a>. I ran this course for Technitrain in March 2014 after which 100% rated the trainer and the course content as outstanding. 100% also rated the course overall as outstanding.</p> <p><a href="http://twitter.com/jamiet">@Jamiet</a></p>Want a headless build server for SSDT without installing Visual Studio? You’re out of luck!http://sqlblog.com/blogs/jamie_thomson/archive/2014/08/08/want-a-headless-build-server-for-ssdt-without-installing-visual-studio-you-re-out-of-luck.aspxFri, 08 Aug 2014 10:22:13 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:54734jamiet<p>An issue that regularly seems to rear its head on my travels is that of headless build servers for SSDT. What does that mean exactly? Let me give you my interpretation of it.</p> <hr /> <p>A SQL Server Data Tools (SSDT) project incorporates a build process that will basically parse all of the files within the project and spit out a .<a href="http://sqlblog.com/blogs/jamie_thomson/archive/2014/01/18/dacpac-braindump.aspx" target="_blank">dacpac file</a>. Where an organisation employs a <a href="http://en.wikipedia.org/wiki/Continuous_integration" target="_blank">Continuous Integration</a> process they will likely want to automate the building of that dacpac whenever someone commits a change to the source control repository. In order to do that the organisation will use a build server (e.g. <a href="http://msdn.microsoft.com/en-us/library/ms181712.aspx" target="_blank">TFS</a>, <a href="http://www.jetbrains.com/teamcity/" target="_blank">TeamCity</a>, <a href="http://jenkins-ci.org/" target="_blank">Jenkins</a>) and hence that build server requires all the pre-requisite software that understands how to build an SSDT project.</p> <p>The simplest way to install all of those pre-requisites is to <a href="http://msdn.microsoft.com/en-us/data/hh297027" target="_blank">install SSDT</a> itself however a lot of folks don’t like that approach because it installs a lot unnecessary components on there, not least Visual Studio itself. Those folks (of which i am one) are of the opinion that it should be unnecessary to install a heavyweight GUI in order to simply get a few software components required to do something that inherently doesn’t even need a GUI. The phrase “headless build server” is often used to describe a build server that doesn’t contain any heavyweight GUI tools such as Visual Studio and is a desirable state for a build server.</p> <hr /> <p>In his blog post <a title="http://sqlproj.com/index.php/2012/03/headless-msbuild-support-for-ssdt-sqlproj-projects/" href="http://sqlproj.com/index.php/2012/03/headless-msbuild-support-for-ssdt-sqlproj-projects/" target="_blank">Headless MSBuild Support for SSDT (*.sqlproj) Projects</a> Gert Drapers outlines the steps necessary to obtain a headless build server for SSDT:</p> <blockquote> <p><em>This article describes how to install the required components to build and publish SQL Server Data Tools projects (*.sqlproj) using MSBuild without installing the full SQL Server Data Tool hosted inside the Visual Studio IDE. <br /><a title="http://sqlproj.com/index.php/2012/03/headless-msbuild-support-for-ssdt-sqlproj-projects/" href="http://sqlproj.com/index.php/2012/03/headless-msbuild-support-for-ssdt-sqlproj-projects/">http://sqlproj.com/index.php/2012/03/headless-msbuild-support-for-ssdt-sqlproj-projects/</a></em></p> </blockquote> <p>Frankly however going through these steps is a royal <a href="http://www.urbandictionary.com/define.php?term=pita" target="_blank">PITA</a> and folks like myself have longed for Microsoft to support headless build support for SSDT by providing a distributable installer that installs only the pre-requisites for building SSDT projects. Yesterday in MSDN forum thread <a href="http://social.msdn.microsoft.com/Forums/sqlserver/en-US/a3ee4fb4-8b58-49df-bbd8-d8755b97f311/building-a-vs2013-headless-build-server-its-sooo-hard?forum=ssdt" target="_blank">Building a VS2013 headless build server - it's sooo hard</a> Mike Hingley complained about this very thing and it prompted a response from Kevin Cunnane from the SSDT product team:</p> <blockquote> <p><em>The official recommendation from the TFS / Visual Studio team is to install the version of Visual Studio you use on the build machine.</em></p> </blockquote> <p>I, like many others, would rather not have to install full blown Visual Studio and so I asked:</p> <blockquote> <p><em>Is there any chance you'll ever support any of these scenarios:</em></p> <ul> <li><em>Installation of all build/deploy pre-requisites without installing the VS shell? </em></li> <li><em>TFS shipping with all of the pre-requisites for doing SSDT project build/deploys </em></li> <li><em>3rd party build servers (e.g. TeamCity) shipping with all of the requisites for doing SSDT project build/deploys</em></li> </ul> <p><em>I have to say that the lack of a single installer containing all the pre-requisites for SSDT build/deploy puzzles me. Surely the DacFX installer would be a perfect vehicle for that?</em></p> </blockquote> <p>Kevin replied again:</p> <blockquote> <p><em>The answer is no for all 3 scenarios. We looked into this issue, discussed it with the Visual Studio / TFS team, and in the end agreed to go with their latest guidance which is to install Visual Studio (e.g. VS2013 Express for Web) on the build machine. This is how Visual Studio Online is doing it and it's the approach recommended for customers setting up their own TFS build servers. I would hope this is compatible with 3rd party build servers but have not verified whether this works with TeamCity etc.</em></p> <p><em>Note that DacFx MSI isn't a suitable release vehicle for this as we don't want to include Visual Studio/MSBuild dependencies in that package. It's meant to just include the core DacFx DLLs used by SSMS, SqlPackage.exe on the command line, etc.</em></p> <p><em>What this means is we won't be providing a separate MSI installer or nuget package with just the necessary build DLLs you need to run your build and tests. If someone wanted to create a script that generated a nuget package based on our DLLs and targets files, then release that somewhere on the web for easier integration with 3rd party build servers we've no problem with that. </em></p> </blockquote> <p>Again, <a href="http://social.msdn.microsoft.com/Forums/sqlserver/en-US/a3ee4fb4-8b58-49df-bbd8-d8755b97f311/building-a-vs2013-headless-build-server-its-sooo-hard?forum=ssdt" target="_blank">here’s the link</a> to the thread and its worth reading in its entirety if this is something that interests you.</p> <p>So there you have it. Microsoft will not be be providing support for headless build servers for SSDT but if someone in the community wants to go ahead and roll their own, go right ahead.</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p>SSDT gotcha – Moving a file erases code analysis suppressionshttp://sqlblog.com/blogs/jamie_thomson/archive/2013/10/10/ssdt-gotcha-moving-a-file-erases-code-analysis-suppressions.aspxThu, 10 Oct 2013 16:42:09 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:51306jamiet<p><font size="3">I discovered a little wrinkle in SSDT today that is worth knowing about if you are managing your database schemas using SSDT. In short, if a file is moved to a different folder in the project then any code analysis suppressions that reference that file will disappear from the suppression file. This makes sense if you think about it because the paths stored in the suppression file are no longer valid, but you probably won’t be aware of it until it happens to you. If you don’t know what code analysis is or you don’t know what the suppression file is then you can probably stop reading now, otherwise read on for a simple short demo.</font></p> <p><font size="3"></font></p> <p><font size="3">Let’s create a new project and add a stored procedure to it called sp_dummy. </font></p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_6631C6B1.png"><font size="3"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_01FAFDFD.png" width="778" height="323" /></font></a></p> </blockquote> <p><font size="3">Naming stored procedures with a sp_ prefix is generally frowned upon and hence SSDT static code analysis will look for occurrences of this and flag them. So, the next thing we need to do is turn on static code analysis in the project properties:</font></p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_5CFFCE34.png"><font size="3"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_13708037.png" width="859" height="346" /></font></a></p> </blockquote> <p><font size="3">A subsequent build causes a code analysis warning as we might expect:</font></p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_2B967B43.png"><font size="3"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_744FF407.png" width="657" height="159" /></font></a></p> </blockquote> <p><font size="3">Let’s suppose we actually don’t mind stored procedures with sp_ prefixes, we can just right-click on the message to suppress and get rid of it:</font></p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_543777FB.png"><font size="3"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_74E0D251.png" width="879" height="266" /></font></a></p> </blockquote> <p><font size="3">That causes a suppression file to get created in our project:</font></p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_11EC11CB.png"><font size="3"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_19999BDC.png" width="956" height="222" /></font></a></p> </blockquote> <p><font size="3">Notice that the suppression file contains a relative path to the file that has had the suppression placed upon it. Now if we simply move the file within our project to a new folder notice that the suppression that we just created gets removed from the suppression file:</font></p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_0C58342B.png"><font size="3"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_3735B99F.png" width="981" height="265" /></font></a></p> </blockquote> <p><font size="3">As I alluded above this behaviour is intuitive because the path originally stored in the suppression file is no longer relevant but you’re probably not going to be aware of it until it happens to you and messages that you thought you had suppressed start appearing again. Definitely one to be aware of.</font></p> <p><a href="http://twitter.com/jamiet" target="_blank"><font size="3">@Jamiet</font></a><font size="3">&#160;</font></p> <p><font size="3">&#160;</font></p>Learn from me about SSDT in London, March 2014http://sqlblog.com/blogs/jamie_thomson/archive/2013/09/05/learn-from-me-about-ssdt-in-london-march-2014.aspxThu, 05 Sep 2013 07:01:38 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50841jamiet<p>Microsoft released SQL Server Data Tools (SSDT) along with SQL Server 2012 in the Spring of 2012. Since then I’ve noticed an upward tick in both the number of organisations that are using SSDT and the number of questions that are getting asked about it on forums. There is some confusion about what SSDT actually is (<a href="http://sqlblog.com/blogs/jamie_thomson/archive/2013/04/03/ssdt-naming-confusion-cleared-up-somewhat.aspx" target="_blank">Microsoft hasn’t helped there</a>), why people should be using SSDT and, most importantly, how to make best use of it. </p> <p>Its clear that people want to learn more about SSDT so I have joined forces with <a href="http://www.technitrain.com/" target="_blank">Technitrain</a> to offer a 2–day training course, in London, in March 2014 called <a href="http://www.technitrain.com/coursedetail.php?c=28&amp;trackingcode=JT1" target="_blank">Introduction to SQL Server Data Tools</a></p> <p><a href="http://www.technitrain.com/coursedetail.php?c=28&amp;trackingcode=JT1" target="_blank"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_53942F8E.png" width="553" height="226" /></a></p> <p><a href="http://www.technitrain.com/coursedetail.php?c=28&amp;trackingcode=JT1" target="_blank"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_24D3984E.png" width="380" height="84" /></a></p> <p>The course will cover:</p> <table cellspacing="0" cellpadding="2"> <tr> <td> <p><i>Day 1</i> </p> <ul> <li>SSDT Positioning</li> <li>IDE Tour</li> <li>Connected Database Development</li> <li>Declarative, offline, database development</li> <li>Publishing</li> <li>Continuous Integration (CI) and Continuous Deployment</li> </ul> </td> <td> <p><i>Day 2</i> </p> <ul> <li>Data Publishing</li> <li>Refactoring</li> <li>Database unit testing</li> <li>References and composite projects</li> <li>Database Drift</li> <li>Code analysis</li> </ul> </td> </tr> </table> <p>If this sounds like your bag then please <a href="http://www.technitrain.com/coursedetail.php?c=28&amp;trackingcode=JT1" target="_blank">sign up on the Technitrain website</a>.</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p>June 2013 release of SSDT contains a minor bug that you should be aware ofhttp://sqlblog.com/blogs/jamie_thomson/archive/2013/07/01/june-2013-release-of-ssdt-contains-a-minor-bug-that-you-should-be-aware-of.aspxMon, 01 Jul 2013 06:54:15 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:49892jamiet<p>I have discovered what seems, to me, like a bug in <a href="http://blogs.msdn.com/b/ssdt/archive/2013/06/24/announcing-sql-server-data-tools-june-2013.aspx" target="_blank">the June 2013 release of SSDT</a> and given the problems that it created yesterday on my current gig I thought it prudent to write this blog post to inform people of it.</p> <p>I’ve built a very simple SSDT project to reproduce the problem that has just two tables, [Table1] and [Table2], and also a procedure [Procedure1]:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_00C1DD2F.png"><img title="image" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_692CC06C.png" width="244" height="161" /></a></p> <p>The two tables have exactly the same definition, both a have a single column called [Id] of type integer.</p> <blockquote> <p><font color="#0000ff">CREATE TABLE </font>[dbo].[Table1] <br />( <br />&#160;&#160;&#160; [Id] <font color="#0000ff">INT </font><font color="#646b86">NOT NULL </font><font color="#0000ff">PRIMARY KEY</font> <br />) <br /></p> </blockquote> <p>My stored procedure simply joins the two together, orders them by the column used in the join predicate, and returns the results:</p> <blockquote> <p><font color="#0000ff">CREATE PROCEDURE </font>[dbo].[Procedure1] <br /><font color="#0000ff">AS <br />&#160;&#160;&#160; SELECT </font>t1.* <br />&#160;&#160;&#160; <font color="#0000ff">FROM&#160;&#160;&#160; </font>Table1 t1 <br />&#160;&#160;&#160; <font color="#646b86">INNER JOIN </font>Table2 t2 <br />&#160;&#160;&#160; <font color="#0000ff">&#160;&#160;&#160; ON&#160;&#160;&#160; </font>t1.Id = t2.Id <br />&#160;&#160;&#160; <font color="#0000ff">ORDER BY</font> Id</p> </blockquote> <p>Now if I create those three objects manually and then execute the stored procedure, it works fine:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_1F9D726F.png"><img title="image" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;margin:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_33B68EF8.png" width="244" height="145" /></a></p> </blockquote> <p>So we know that the code works. Unfortunately, SSDT thinks that there is an error here:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_7C7007BC.png"><img title="image" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_3993C342.png" width="386" height="207" /></a></p> </blockquote> <p>The text of that error is:</p> <blockquote> <p>Procedure: [dbo].[Procedure1] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[Table1].[Id] or [dbo].[Table2].[Id].</p> </blockquote> <p>Its complaining that the [Id] field in the ORDER BY clause is ambiguous. Now you may well be thinking at this point “OK, just stick a table alias into the ORDER BY predicate and everything will be fine!” Well that’s true, but there’s a bigger problem here. One of the developers at my current client installed this drop of SSDT and all of a sudden all the builds started failing on his machine – he had errors left right and centre because, as it transpires, we have a fair bit of code that exhibits this scenario.&#160; Worse, <strong><em><u>previous installations of SSDT do not flag this code as erroneous</u></em></strong> and therein lies the rub. We immediately had a mass panic where we had to run around the department to our developers (of which there are many) ensuring that none of them should upgrade their SSDT installation if they wanted to carry on being productive for the rest of the day. </p> <p>Also bear in mind that as soon as a new drop of SSDT comes out then the previous version is instantly unavailable so rolling back is going to be impossible unless you have <a href="http://sqlproj.com/index.php/2011/11/creating-an-administrative-install-for-ssdt/" target="_blank">created an administrative install of SSDT</a> for that previous version.</p> <p>Just thought you should know! In the grand schema of things this isn’t a big deal as the bug can be worked around with a simple code modification but forewarned is forearmed so they say!</p> <p>Last thing to say, if you want to know which version of SSDT you are running check my blog post <a href="http://sqlblog.com/blogs/jamie_thomson/archive/2013/02/08/which-version-of-ssdt-database-projects-do-i-have-installed.aspx" target="_blank">Which version of SSDT Database Projects do I have installed?</a> </p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p>Creating your own SQL snippets in SSDThttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/22/creating-your-own-sql-snippets-in-ssdt.aspxMon, 22 Apr 2013 21:33:35 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48822jamiet<p>SQL Server Data Tools (SSDT) has a neat feature where you can add snippets into your scripts via the right-click context menu:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_32A460AD.png"><img title="image" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_238D81D3.png" width="597" height="125" /></a></p> <p>I’m finding it very useful indeed. The same feature exists in SQL Server Management Studio (SSMS) as well by the way:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_2968256C.png"><img title="image" style="border-left-width:0px;border-right-width:0px;background-image:none;border-bottom-width:0px;padding-top:0px;padding-left:0px;display:inline;padding-right:0px;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_6F78E27F.png" width="601" height="108" /></a></p> <p>One thing I really wanted to be able to do was create my own snippets for SSDT and I ventured to the <a href="http://social.msdn.microsoft.com/Forums/en-US/ssdt/threads" target="_blank">SSDT forum</a> to ask if it was possible. Turns out the answer is “yes” and Gert Drapers <a href="http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/a351955d-c33b-4ff1-afca-94cabdeb2aa5" target="_blank">replied to my thread by providing an excellent run through of how to do it</a>. What Gert’s post didn’t quite clarify is that if you follow his instructions then you have to manually edit the .snippet file that you created so that the language is set to SQL_SSDT:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_47663360.png"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_78856E00.png" width="620" height="155" /></a></p> <p>(well, I had to do that anyway)</p> <p>Once you do that you’ll be able to import the snippet into Visual Studio (Gert’s post shows you how) and thereafter your snippets will show up in the snippets menu:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_5072BEE1.png"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_04A6E828.png" width="504" height="108" /></a></p> <p>Very handy indeed.</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p>Detecting Database Drift using SSDThttp://sqlblog.com/blogs/jamie_thomson/archive/2013/04/04/detecting-database-drift-using-ssdt.aspxThu, 04 Apr 2013 09:31:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48526jamiet<p>One of the nice things about doing user group presentations is that when you’re putting the presentation together you invariably learn about features that were previously unbeknown to you; so it proved as I stumbled upon SSDT’s database drift detection features while researching material for my forthcoming pre-conference seminar <a target="_blank" href="http://sqlbits.com/information/Event11/SSDT_Database_projects_from_the_ground-up1/TrainingDetails.aspx">SSDT from the ground up</a>.</p> <h3>What is database drift?</h3> <p>You have probably experienced database drift, you just didn’t happen to refer to it as that. More likely you might have spluttered the following, perhaps sprinkled with a few expletives:</p> <ul> <li>“Who put these tables in my database?” </li> <li>“Who changed this view definition?” </li> <li>“Why is this guy in db_owner?” </li> <li>“Where has my stored procedure gone?”</li> </ul> <p>In other words database drift can loosely be described as</p> <blockquote> <p><i>stuff that appears, gets removed, or gets modified in your production databases that perhaps shouldn’t be</i></p> </blockquote> <h3>Detecting database drift using SSDT</h3> <p>If you’re using SSDT to manage your database schema then you probably consider the source code in your SSDT projects to be “the truth” and hence anything that appears in your databases that is not in your source code would be considered database drift.</p> <p>In order to detect database drift using SSDT you must ensure that your database is registered as a Data-Tier Application. This can be done when you publish your database project (i.e. dacpac) by selecting “Register as a Data-tier Application”:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML450c41_2A02E493.png"><img title="SNAGHTML450c41" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;display:inline;" border="0" alt="SNAGHTML450c41" width="495" height="254" src="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML450c41_thumb_4800F287.png"></a></p> <p>Thereafter you can check for database drift on subsequent publishes by selecting “Block publish when database has drifted from registered version”:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML46b2db_7BC8E8D8.png"><img title="SNAGHTML46b2db" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;display:inline;" border="0" alt="SNAGHTML46b2db" width="496" height="254" src="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML46b2db_thumb_7A844FF9.png"></a></p> <p>If you check that box and database drift has occurred then the publish operation will fail and you see an appropriate message in the Data Tools Operations pane, “Publish stopped. The target database has drifted from the registered version.”:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML4bd20f_5271A0DA.png"><img title="SNAGHTML4bd20f" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;display:inline;" border="0" alt="SNAGHTML4bd20f" width="461" height="267" src="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML4bd20f_thumb_78675130.png"></a></p> <p>Clicking the <font color="#0080c0">View Report</font> hyperlink displays the Drift Report which is represented in an XML file:</p> <blockquote> <pre style="font-size:13px;font-family:consolas;background:white;color:black;"><span style="color:blue;">&lt;?</span><span style="color:#a31515;">xml</span><span style="color:blue;">&nbsp;</span><span style="color:red;">version</span><span style="color:blue;">=</span>"<span style="color:blue;">1.0</span>"<span style="color:blue;">&nbsp;</span><span style="color:red;">encoding</span><span style="color:blue;">=</span>"<span style="color:blue;">utf-8</span>"<span style="color:blue;">?&gt;</span>
<span style="color:blue;">&lt;</span><span style="color:#a31515;">DriftReport</span><span style="color:blue;">&nbsp;</span><span style="color:red;">xmlns</span><span style="color:blue;">=</span>"<span style="color:blue;">http://schemas.microsoft.com/sqlserver/dac/DriftReport/2012/02</span>"<span style="color:blue;">&gt;</span>
<span style="color:blue;">&nbsp; &lt;</span><span style="color:#a31515;">Additions</span><span style="color:blue;">&gt;</span>
<span style="color:blue;">&nbsp;&nbsp;&nbsp; &lt;</span><span style="color:#a31515;">Object</span><span style="color:blue;">&nbsp;</span><span style="color:red;">Name</span><span style="color:blue;">=</span>"<span style="color:blue;">[View_1]</span>"<span style="color:blue;">&nbsp;</span><span style="color:red;">Parent</span><span style="color:blue;">=</span>"<span style="color:blue;">[dbo]</span>"<span style="color:blue;">&nbsp;</span><span style="color:red;">Type</span><span style="color:blue;">=</span>"<span style="color:blue;">SqlView</span>"<span style="color:blue;"> /&gt;</span>
<span style="color:blue;">&nbsp;&nbsp;&nbsp; &lt;</span><span style="color:#a31515;">ExtendedProperty</span><span style="color:blue;">&nbsp;</span><span style="color:red;">HostName</span><span style="color:blue;">=</span>"<span style="color:blue;">[View_1]</span>"<span style="color:blue;">&nbsp;</span><span style="color:red;">HostParent</span><span style="color:blue;">=</span>"<span style="color:blue;">[dbo]</span>"<span style="color:blue;">&nbsp;</span><span style="color:red;">HostType</span><span style="color:blue;">=</span>"<span style="color:blue;">SqlView</span>"<span style="color:blue;">&nbsp;</span><span style="color:red;">Count</span><span style="color:blue;">=</span>"<span style="color:blue;">2</span>"<span style="color:blue;"> /&gt;</span>
<span style="color:blue;">&nbsp; &lt;/</span><span style="color:#a31515;">Additions</span><span style="color:blue;">&gt;</span>
<span style="color:blue;">&nbsp; &lt;</span><span style="color:#a31515;">Removals</span><span style="color:blue;"> /&gt;</span>
<span style="color:blue;">&nbsp; &lt;</span><span style="color:#a31515;">Modifications</span><span style="color:blue;"> /&gt;</span>
<span style="color:blue;">&lt;/</span><span style="color:#a31515;">DriftReport</span><span style="color:blue;">&gt;</span></pre>
</blockquote>
<p>In this case a view called [dbo].[View_1] has been added to the target database. That view did not exist in the dacpac that was most recently deployed against the database thus the publish operation fails. Keeping one’s deployed databases as “clean” as possible is something that I am all in favour of so personally I think this is a pretty cool feature.</p>
<h3>Generating a drift report from the command-line</h3>
<p>The drift report can be generated by the command-line tool <a target="_blank" href="http://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx">sqlpackage.exe</a>. To do so you need to define:</p>
<ul>
<li>the action to be <font face="cons">DriftReport</font></li>
<li>a target server &amp; database</li>
<li>an output file</li>
</ul>
<p>&gt;SqlPackage.exe /A:<b>DriftReport</b> /tsn:"<b>(localdb)\Projects</b>" /tdn:"<b>Database1</b>" /op:<b>DriftReport.xml</b></p>
<p><a href="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML57a506_024C429C.png"><img title="SNAGHTML57a506" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;display:inline;" border="0" alt="SNAGHTML57a506" width="521" height="127" src="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML57a506_thumb_361438ED.png"></a></p>
<p>As far as I know there is no support for generating a drift report from SQL Server Management Studio (SSMS). I’m hoping that changes so that this feature gets more visibility.</p>
<p>&nbsp;</p>
<p>If you have any comments stick them in the comments section below!</p>
<p><a target="_blank" href="http://twitter.com/jamiet">@Jamiet</a></p>SSDT naming confusion cleared up. Somewhat.http://sqlblog.com/blogs/jamie_thomson/archive/2013/04/03/ssdt-naming-confusion-cleared-up-somewhat.aspxWed, 03 Apr 2013 07:49:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48507jamiet
<p>In March 2012 I published <a mce_href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/04/03/ssdt-what-s-in-a-name.aspx" target="_blank" href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/04/03/ssdt-what-s-in-a-name.aspx">SSDT - What's in a name?</a> where I lamented the mistakes that Microsoft made in the naming of SQL Server Data Tools.</p>
<blockquote>
<p><i>…official documentation stating that SSDT includes all the stuff for building SSIS/SSAS/SSRS solutions (this is confirmed in the installer, remember) yet someone from Microsoft tells him "SSDT doesn't include any BIDs components".</i></p>
<p><i>I have been close to this for a long time (all the way through the CTPs) so I can kind of understand where the confusion stems from. To my understanding SSDT was originally the name of the database dev stuff but eventually that got expanded to include all of the dev tools - I guess not everyone in Microsoft got the memo.</i></p>
</blockquote>
<p>Since then I’ve seen lots of questions pertaining to SSIS/SSAS/SSRS being posted on the <a mce_href="http://social.msdn.microsoft.com/Forums/en-US/ssdt/threads" target="_blank" href="http://social.msdn.microsoft.com/Forums/en-US/ssdt/threads">SSDT forum on MSDN</a> which, frankly, is the wrong place for them.</p>
<p>With the release of the <a mce_href="http://www.microsoft.com/en-us/download/details.aspx?id=36843" target="_blank" href="http://www.microsoft.com/en-us/download/details.aspx?id=36843">SSIS/SSAS/SSRS project templates for Visual Studio 2012</a> Microsoft have attempted to clear up the confusion. Matt Masson from the SSIS product team attempts to explain in his usual jovial way:</p>
<blockquote>
<p><i>note that the component was renamed – we added “Business Intelligence” to the end to distinguish it from the </i><a mce_href="http://msdn.microsoft.com/en-us/data/tools.aspx" href="http://msdn.microsoft.com/en-us/data/tools.aspx"><i>SQL Server Data Tools</i></a><i> (Juneau). We now refer to it as SSDTBI, rather than “SSDT, no, not that one, the other one – you know, the one that comes with the SQL installation media, not the one you download”. <br><a title="http://www.mattmasson.com/2013/04/installing-ssis-for-visual-studio-2012/" mce_href="http://www.mattmasson.com/2013/04/installing-ssis-for-visual-studio-2012/" href="http://www.mattmasson.com/2013/04/installing-ssis-for-visual-studio-2012/">http://www.mattmasson.com/2013/04/installing-ssis-for-visual-studio-2012/</a></i></p>
</blockquote>
<p>So to clarify, its now:</p>
<ul>
<li>SSDT – for building databases</li>
<li>SSDTBI – for building SSIS/SSAS/SSRS solutions</li>
</ul>
<p>Got it? Good!</p>
<p>That may all seem slightly confusing but its a darn sight clearer than it was SQL Server 2012 was released over a year ago. And if nothing else you have to be amused with Microsoft’s penchant for ever-lengthening acronyms, the last six-letter-acronym I can remember seeing was <a href="http://www.mathsisfun.com/operation-order-bodmas.html">BODMAS</a>!!!</p>
<p><a mce_href="http://twitter.com/jamiet" target="_blank" href="http://twitter.com/jamiet">@Jamiet</a></p>Considerations when starting a new SSDT database projecthttp://sqlblog.com/blogs/jamie_thomson/archive/2013/03/21/considerations-when-starting-a-new-ssdt-database-project.aspxThu, 21 Mar 2013 10:58:03 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48327jamiet<p>As you may have realised from much of my blogging over the past year or so I’m an advocate of using SSDT database projects for building database solutions on SQL Server. I have been using SSDT database projects a lot in that time and have come up with a checklist of things to consider when starting a new SSDT database project and I’ll be detailing that checklist below. I strongly advise you to consider making decisions about these items before you even write a scrap of code as invariably it will be more difficult to change later, especially if you have already deployed your database.</p> <hr /> <p>In no particular order here is my checklist:</p> <h3>Folder Structure</h3> <p>By default SSDT will not provide a folder structure for new projects in which to store all your script files so you might want to consider setting one up yourself. I recommend not trying to create a large hierarchy of folders to start with as this will evolve as you go about building your database. Here’s a simple starter for ten:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_2ADBA6CA.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_174E4729.png" width="212" height="138" /></a></p> </blockquote> <h3>Filegroups</h3> <p>If you don’t understand the importance of filegroups then read Thomas Larock’s recent blog post <a href="http://thomaslarock.com/2013/01/database-filegroups-just-like-seatbelts-but-with-less-chafing/">DATABASE FILEGROUPS: JUST LIKE SEATBELTS BUT WITH LESS CHAFING</a>. Quite often your organisation will stipulate policies for what filegroups you should be using and I highly recommend that you set up filegroups in your database projects to match those policies sooner rather than later. If your organisation does not have any such policies then perhaps think about defining them yourself, Thomas lists a few considerations that may influence your decisions:</p> <ul> <li>separation of system data from user data </li> <li>larger indexes may benefit from their own filegroup </li> <li>archival of data can benefit from using dedicated filegroups as this will reduce backup maintenance tasks </li> </ul> <p>If you follow my suggested folder structure above then put your filegroups into the “Storage” folder:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_2852750C.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_75826E97.png" width="183" height="173" /></a> </p> </blockquote> <p>Above all, specify your default filegroup especially as many DBAs won’t be happy about you putting objects into the PRIMARY filegroup (which is the default). This is done by right-clicking on the project and selecting “Properties..” When there hit “Database Settings…” and set your default filegroup on the “Operational” tab:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML1ebb40b8_7FD392F7.png"><img title="SNAGHTML1ebb40b8" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="SNAGHTML1ebb40b8" src="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML1ebb40b8_thumb_25C9434E.png" width="405" height="146" /></a></p> </blockquote> <p>If you intend to use the filestream feature of SQL Server then you can set the default filestream filegroup here too.</p> <h3>Collation</h3> <p>If you take only one piece of advice from this blog post make it this: <strong>set your collation before you write a single line of code</strong> (just trust me on this, OK). You’ll find this in Project Properties-&gt;Project Settings-&gt;Database Settings-&gt;Common</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML1ebc6e3e_4BBEF3A4.png"><img title="SNAGHTML1ebc6e3e" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="SNAGHTML1ebc6e3e" src="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML1ebc6e3e_thumb_71B4A3FA.png" width="415" height="160" /></a></p> </blockquote> <p>Again, find out if your organisation has any guidance on database collation.</p> <h3>Specify Target platform</h3> <p>SSDT allows you to deploy a project to SQL Server 2005, 2008, 2012 or Azure so it stands to reason that you should specify which you are intending to deploy to. You’ll find this in Project Properties-&gt;Project Settings:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_778F4793.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_5AA622BE.png" width="399" height="128" /></a> </p> </blockquote> <p></p> <h3>Default Schema</h3> <p>If you follow the premise that “anything that SQL Server picks as the default is probably wrong” then you should probably think about changing the default schema which, by default, will be [dbo]. SSDT will create all new objects in the default schema (unless otherwise specified).</p> <p>Generally I’m of the opinion that not accepting SQL Server’s defaults is a good idea if only because it forces you to think about these things and be aware of them; in the case of schemas it forces you to think about security and who should have GRANT or DENY permissions and on what.</p> <p>Specify the default schema in Project properties-&gt;Project Settings:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_158D5588.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_791063A7.png" width="428" height="71" /></a> </p> </blockquote> <h3>Review other database settings</h3> <p>I’ve mentioned the most important database settings that you should look to change however you should also glance over the rest of the defaults that SSDT chooses for you to verify that they are valid for your project. Again, these are in Project Properties-&gt;Project Settings-&gt;Database Settings. a few I’d call out as being especially worthy of your attention are:</p> <ul> <li>Auto update stats (on by default) </li> <li>Recovery model (FULL by default) </li> <li>Transaction Isolation (RCSI off by default) </li> <li>Service broker (disabled by default) </li> </ul> <p></p> <p></p> <p></p> <p></p> <p></p> <p></p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_18530A7B.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_6C360D89.png" width="244" height="240" /></a> <br /><a href="http://sqlblog.com/blogs/jamie_thomson/image_5D1F2EAF.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_42DEC58B.png" width="395" height="404" /></a> <br /><a href="http://sqlblog.com/blogs/jamie_thomson/image_289E5C67.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_12684115.png" width="396" height="403" /></a> </p> </blockquote> <h3>Code Analysis</h3> <p>SSDT always checks the syntax of your database code, that’s one of the main justifications for using SSDT database projects. However what it does not do (not by default anyway) is check whether the code you write might be considered <em>good </em>code. That’s what Code Analysis is for, your code gets checked to see if it adheres to well-understood good practices for SQL Server database development. For example, Code Analysis will check for use of “SELECT*” which is generally regarded as a bad thing.</p> <p>Code analysis is turned off by default. I recommend that you head into Project Properties-&gt;Code Analysis and turn it on. Moreover I recommend that you check all the boxes under “Treat Warning as Error” – this forces developers to address issues that get raised by Code Analysis.</p> <p></p> <p></p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_0D195A64.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_2B839B4D.png" width="437" height="255" /></a> </p> </blockquote> <h3>Big Labels</h3> <p>Add a banner to your Pre-Deployment script as I describe in <a href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/09/26/big-label-generator.aspx" target="_blank">Big label generator</a>. This may seem rather unnecessary but it takes about 30 seconds and believe me, at some point you’ll be glad you did!</p> <blockquote> <p><img src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_2A99351D.png" width="619" height="334" /></p> </blockquote> <p>&#160;</p> <p>The demo project that I took some of the above screenshots from is downloadable from <a title="http://sdrv.ms/ZW0gNt" href="http://sdrv.ms/ZW0gNt">http://sdrv.ms/ZW0gNt</a> and might provide a useful template for your own SSDT database projects.</p> <hr /> <p>If you have any more suggestions for my checklist please add them to the comments below!</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p>Connected development in SSDT versus SSMShttp://sqlblog.com/blogs/jamie_thomson/archive/2013/03/19/connected-development-in-ssdt-versus-ssms.aspxTue, 19 Mar 2013 16:28:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48314jamiet<p>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.</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML15dc3f62_18DB391E.png"><img title="SNAGHTML15dc3f62" style="border-top:0px;border-right:0px;border-bottom:0px;border-left:0px;display:inline;" border="0" alt="SNAGHTML15dc3f62" width="335" height="118" src="http://sqlblog.com/blogs/jamie_thomson/SNAGHTML15dc3f62_thumb_0C6D15F5.png"></a></p> <p>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).</p> <p>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.</p> <p>&nbsp;</p> <hr> <h3>Server groups</h3> <p>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</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_568820DA.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" width="244" height="97" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_4E907E78.png"></a>&nbsp;<a href="http://sqlblog.com/blogs/jamie_thomson/image_0977B142.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" width="244" height="230" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_6C8E8C6C.png"></a> </p> <h3>F6</h3> <p>In SSMS I regularly use the F6 keyboard shortcut to jump between the query, results &amp; messages panes of a query window. No such keyboard shortcut exists in SSDT and they’ve already canned <a target="_blank" href="https://connect.microsoft.com/sqlserver/feedback/details/780990/ssdt-f6-to-move-between-panes-in-a-query-window#tabs">my request on Connect to get this fixed</a> (even though it laughably has status “closed as fixed”).</p> <p>&nbsp;<i>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.</i></p> <h3>Change Connection</h3> <p>The context menu in SSMS provides the ability to change a connection as well as connect and disconnect:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_72693005.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" width="546" height="115" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_11ABD6D9.png"></a> </p> <p>SSDT doesn’t have change connection and believe me, you don’t know how much you use a feature until its not there:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_09B43477.png"><img title="image" style="border-left-width:0px;border-right-width:0px;border-bottom-width:0px;display:inline;border-top-width:0px;" border="0" alt="image" width="438" height="58" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_28F6DB4A.png"></a> </p> <p>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.</p> <p>&nbsp;</p><hr>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.<p>&nbsp;</p> <p><a target="_blank" href="http://twitter.com/jamiet">@Jamiet</a></p>