Search results matching tags 'SQL Server 2012' and 'SQL Server 'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=SQL+Server+2012,SQL+Server+&quot;Denali&quot;&orTags=0Search results matching tags 'SQL Server 2012' and 'SQL Server 'en-USCommunityServer 2.1 SP2 (Build: 61129.1)StreamInsight: More Than Just an APIhttp://sqlblog.com/blogs/stream_insight/archive/2013/06/03/streaminsight-more-than-just-an-api.aspxMon, 03 Jun 2013 09:24:14 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:49322Roman Schindlauer<p>If you're evaluating StreamInsight you should know that it's more than just an API for writing streaming queries. Included with the StreamInsight installation are powerful tools that allow you to manage, monitor, tune, and troubleshoot your streaming data applications. These tools range from diagnostic methods and properties built into the API to a full graphical debugging application.</p>
<p>To read more, see the TechNet Wiki article, <a title="StreamInsight: More Than Just an API" href="http://aka.ms/yrmp2p">StreamInsight: More Than Just an API</a>.</p>
<p>Regards,</p>
<p>The StreamInsight Team</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>A dacpac limitation – Deploy dacpac wizard does not understand SqlCmd variableshttp://sqlblog.com/blogs/jamie_thomson/archive/2012/11/13/a-dacpac-limitation-deploy-dacpac-wizard-does-not-understand-sqlcmd-variables.aspxTue, 13 Nov 2012 21:53:04 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:46159jamiet<p>Since the release of SQL Server 2012 I have become a big fan of using dacpacs for deploying SQL Server databases (for reasons that I will explain some other day) and I chose to use a dacpac to distribute my recently announced utility sp_ssiscatalog (read: <a title="http://sqlblog.com/blogs/jamie_thomson/archive/2012/11/07/introducing-sp-ssiscatalog-v1-0-0-0.aspx" href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/11/07/introducing-sp-ssiscatalog-v1-0-0-0.aspx" target="_blank">Introducing sp_ssiscatalog (v1.0.0.0)</a>). Unfortunately if you read that blog post you may have taken note of the following:</p> <blockquote> <p><em>Ordinarily a dacpac can be deployed to a SQL Server from SSMS using the Deploy Dacpac wizard however in this case there is a limitation. Due to sp_ssiscatalog referring to objects in the SSIS Catalog (which it has to do of course) the dacpac contains a SqlCmd variable to store the name of the database that underpins the SSIS Catalog; unfortunately the Deploy Dacpac wizard in SSMS has a rather gaping limitation in that it cannot deploy dacpacs containing SqlCmd variables.</em></p> </blockquote> <p>I think it is worth calling out this limitation separately in this blog post because its a limitation that all dacpac users need to be aware of. If you try and deploy the dacpac containing sp_ssiscatalog using the wizard in SSMS then this is what you will see:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image001_4325C5F1.jpg"><img title="SSMS dacpac limitation" 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="Error deploying dacpac containing a SqlCmd variable when using SSMS" src="http://sqlblog.com/blogs/jamie_thomson/image001_thumb_6B74F845.jpg" width="619" height="578" /></a></p> </blockquote> <blockquote> <p>TITLE: Microsoft SQL Server Management Studio <br />------------------------------ <br />Could not deploy package. (Microsoft.SqlServer.Dac) <br />------------------------------ <br />ADDITIONAL INFORMATION: <br />Missing values for the following SqlCmd variables:SSISDB. (Microsoft.Data.Tools.Schema.Sql) <br />------------------------------ <br />BUTTONS: <br />OK <br />------------------------------</p> </blockquote> <p>The message is quite correct. The SSDT DB project that I used to build this dacpac *<b>does</b>* have a SqlCmd variable in it called SSISDB:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image002_51348F21.png"><img title="SqlCmdVariable" 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="SqlCmd variable in an SSDT project" src="http://sqlblog.com/blogs/jamie_thomson/image002_thumb_577B65AF.png" width="593" height="164" /></a></p> </blockquote> <p>Quite simply, the Dac Deployment wizard in SSMS is not capable of deploying such dacpacs. Your only option for deploying such dacpacs is to use the command-line tool sqlpackage.exe. </p> <p>Generally I use sqlpackage.exe anyway (which is why it has taken me months to encounter the aforementioned problem) and have found it preferable to using a GUI-based wizard. Your mileage may vary.</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p>SSIS Design Patterns, the Bookhttp://sqlblog.com/blogs/andy_leonard/archive/2012/08/06/ssis-design-patterns-the-book.aspxMon, 06 Aug 2012 16:37:43 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:44587andyleonard<p>For the past two years, I have had the honor and privilege or authoring <a href="http://www.amazon.com/SSIS-Design-Patterns-Matt-Masson/dp/1430237716" target="_blank">SSIS Design Patterns</a> alongside Jessica Moss, Michelle Ufford, Tim Mitchell, and Matt Masson. Publication of the book – like many projects of this scope – has been delayed. The current publication date is 27 Aug 2012 and I have high confidence in this date. </p> <p>I take responsibility for publication delays and apologize to those who pre-ordered the book. The reasons for the delays are not important. I have built a career as a software developer and architect based on the following maxim:</p> <blockquote> <p><em>Deliver quality late, no one remembers. <br />Deliver junk on time, no one forgets.</em></p> </blockquote> <p>The shared goal of everyone working on this project has been to deliver quality. Proofing the manuscripts, I believe we have achieved that goal. </p> <p>:{&gt;</p>SSIS Catalog, Windows updates and deployment failures due to System.Core mismatchhttp://sqlblog.com/blogs/jamie_thomson/archive/2012/07/11/ssis-catalog-windows-updates-and-deployment-failures-due-to-system-core-mismatch.aspxWed, 11 Jul 2012 14:36:23 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:44257jamiet<p><em>This is a heads-up for anyone doing development on SSIS. </em></p> <p>On my current project where we are implementing a SQL Server Integration Services (SSIS) 2012 solution we recently encountered a situation where we were unable to deploy any of our projects even though we had successfully deployed in the past. Any attempt to use the deployment wizard resulted in this error dialog:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/clip_image002_1AF90749.jpg"><img style="background-image:none;border-bottom:0px;border-left:0px;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="clip_image002" border="0" alt="clip_image002" src="http://sqlblog.com/blogs/jamie_thomson/clip_image002_thumb_3D6D9604.jpg" width="510" height="257" /></a></p> </blockquote> <p>The text of the error (for all you search engine crawlers out there) was:</p> <blockquote> <p><font color="#ff0000">A .NET Framework error occurred during execution of user-defined routine or aggregate &quot;create_key_information&quot;: <br />System.IO.FileLoadException: Could not load file or assembly 'System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) ---&gt; System.IO.FileLoadException: The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040) <br />System.IO.FileLoadException: <br />System.IO.FileLoadException:&#160; <br />&#160;&#160; at Microsoft.SqlServer.IntegrationServices.Server.Security.CryptoGraphy.CreateSymmetricKey(String algorithm) <br />&#160;&#160; at Microsoft.SqlServer.IntegrationServices.Server.Security.CryptoGraphy.CreateKeyInformation(SqlString algorithmName, SqlBytes&amp; key, SqlBytes&amp; IV) <br />. (Microsoft SQL Server, Error: 6522)</font></p> </blockquote> <p>After some investigation and a bit of back and forth with some very helpful members of the SSIS product team (hey Matt, Wee Hyong) it transpired that this was due to a .Net Framework fix that had been delivered via Windows Update. I took a look at the server update history and indeed there have been some recently applied .Net Framework updates:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_1C0DF068.png"><img style="background-image:none;border-bottom:0px;border-left:0px;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_3AE46446.png" width="654" height="235" /></a></p> </blockquote> <p>This fix had (in the words of <a href="https://twitter.com/mattmasson" target="_blank">Matt Masson</a>) “somehow caused a mismatch on System.Core for SQLCLR” and, as you may know, SQLCLR is used heavily within the SSIS Catalog. The fix was pretty simple – restart SQL Server. This causes the assemblies to be upgraded automatically. If you are using Data Quality Services (DQS) you may have experienced similar problems which are documented at <a href="http://http://msdn.microsoft.com/en-us/library/hh479773.aspx" target="_blank">Upgrade SQLCLR Assemblies After .NET Framework Update</a>. I am hoping the SSIS team will follow-up with a more thorough explanation on their <a href="http://blogs.msdn.com/b/mattm/" target="_blank">blog</a> soon.</p> <p>You DBAs out there may be questioning why Windows Update is set to automatically apply updates on our production servers. We’re checking that out with our hosting provider right now <img style="border-bottom-style:none;border-left-style:none;border-top-style:none;border-right-style:none;" class="wlEmoticon wlEmoticon-smile" alt="Smile" src="http://sqlblog.com/blogs/jamie_thomson/wlEmoticon-smile_0088EE65.png" /></p> <p>You have been warned!</p> <p><a href="http://twitter.com/jamiet" target="_blank">@Jamiet</a></p>Publish Profile Files in SQL Server Data Tools (SSDT)http://sqlblog.com/blogs/jamie_thomson/archive/2012/05/08/publish-profile-files-in-sql-server-data-tools-ssdt.aspxTue, 08 May 2012 22:07:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:43267jamiet<p>I have been using <a href="http://msdn.microsoft.com/en-us/data/gg427686" target="_blank">SQL Server Data Tools</a> (SSDT) both at work and on some hobby projects for quite a few weeks now and of all the new features I have to say the one that I am appreciating the most is Publish Profile files. I have been searching around on MSDN for an article that explains Publish Profile files but it seems no such article exists so I’ll attempt to surmise here.</p> <p>Publish Profile files are, essentially, a collection of all the property key-value pairs that are needed to deploy a database model (i.e. a <a href="http://www.fileinfo.com/extension/dacpac" target="_blank">.dacpac</a>) to some target database. Those properties include (but are not limited to):</p> <ul> <li>database name</li> <li>connection string</li> <li>whether or not to publish SSDT projects on which the current project has a dependency</li> <li>Recreate the database from scratch (or not)</li> <li>Backup before deploy</li> <li>Drop unknown objects in target</li> <li>SQLCMD Variables</li> </ul> <p>Those that have used the predecessor to SSDT, Visual Studio Database Projects, will be familiar with a lot of those properties however in that case the properties had to be specified on a case-by-case basis; typically in an msbuild script or similar. Often those scripts are not maintained by a developer – rather they are maintained by a <a href="http://en.wikipedia.org/wiki/DevOps" target="_blank">DevOps</a> team that are not familiar with the code being deployed and as a developer myself that’s not a situation that I’m at all comfortable with. On my most recent project where Visual Studio Database Projects were being used I faced the maddening situation where every new SQLCMD variable added to a project required an email to be sent to the DevOps team to ask them to update their scripts accordingly. As you can imagine human errors crept in (on our side more than the DevOps side) and we ended up deploying projects with the wrong SQLCMD values. Moreover, we had to deal with different DevOps folks and often they would store the values in different places; totally infuriating, believe me.</p> <p>Publish Profile files make this process much easier because we can define all those properties on a per-environment basis and keep them in a dedicated Publish Profile file. The obvious benefit then is that a Publish Profile file <em>abstracts</em> all of the environment-specific information into a single file so deploying an SSDT project now requires only two things; the build output (i.e. a .dacpac file) and a Publish Profile file:</p> <blockquote> <p><font face="Courier New">&gt;sqlpackage.exe /sf:MyDB.dacpac /pr:DEV.publish.xml</font></p> </blockquote> <p>The implied benefit (and this is what I really like about them) is that Publish Profile files are a source code artefact that a developer maintains the same as they would any other source code artefact, all that the DevOps people need are the build output (i.e. a .dacpac file) and an appropriately named Publish Profile file. Developers are now wholly in charge of how their code gets deployed which keeps them happy and the DevOps people have less work to do – so they’re a happy bunch too! I’m not saying that the wrong values won’t ever be supplied but at least now we know exactly where to go to fix those errors.</p> <p>SSDT also provides a friendly UI for maintaining these Publish Profile files. Double-click on one and this UI appears:</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_4D2EC0B8.png"><img width="517" height="510" title="image" style="border:0px currentColor;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_5F97116D.png" border="0"></a></p> </blockquote> <p>Its fairly self-explanatory to fill these things out. A connection string, a database name and values for each SQLCMD variable defined within the project and you’re pretty much there. (I have written previously about one other benefit of Publish Profile files - that they can be used to make SQLCMD variables mandatory. Read more at <a href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/02/12/sql-server-data-tools-does-support-required-variables.aspx" target="_blank">SQL Server Data Tools does support required variables</a>.)</p> <p>One minor downside of Publish Profile files is that they get created in the root of your SSDT project (I have griped about this previously at <a href="http://sqlblog.com/blogs/jamie_thomson/archive/2012/04/15/folders-in-sql-server-data-tools.aspx" target="_blank">Folders in SQL Server Data Tools</a>) so the convention that I have been using is to create a folder called Publish in each SSDT project and move all Publish Profile files into there.</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_5DE64599.png"><img width="300" height="185" title="image" style="border:0px currentColor;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_5D0DDFAF.png" border="0"></a></p> </blockquote> <p>Hopefully this has given you a small taster of what Publish Profile files are all about. I’ll probably share some msbuild scripts that we’re using to deploy these things in a later blog post.</p> <p><a href="http://twitter.com/jamiet">@Jamiet</a>&nbsp;</p> <p>P.S. As an aside, I have requested that the SSIS/SSAS/SSRS teams adopt the Publish Profile file approach in future iterations of their products. If you think that that is something you would like to see happen then <a href="https://connect.microsoft.com/sqlserver/feedback/details/740059/ssis-collect-all-deployment-specific-properties-into-a-single-file#details" target="_blank">click through, vote and leave a comment</a>.</p><p>UPDATE: If you want a bit more info on Publish Profile files and sqlpackage.exe check out Ben Day's blog post <a href="http://www.benday.com/2012/12/18/deploy-a-sql-server-database-projects-dacpac-with-sqlpackage-exe/" target="_blank">Deploy a SQL Server Database project’s *.dacpac with SqlPackage.exe</a></p>SQL Server 2012 content on Channel 9http://sqlblog.com/blogs/jamie_thomson/archive/2012/03/10/sql-server-2012-content-on-channel-9.aspxSat, 10 Mar 2012 06:37:41 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:42221jamiet<p>A mountain of <a href="http://channel9.msdn.com/Tags/sql+server+2012" target="_blank">SQL Server 2012 video content</a> featuring Greg Low, Jonathan Kehayias, Joe Sack and Roger Doherty has just been released on Channel 9. Channel 9 has great support for tags and RSS feeds so if you want to automatically download all of that content simply you can add the following RSS feed:</p> <blockquote> <p><a title="http://channel9.msdn.com/Tags/sql+server+2012/RSS" href="http://channel9.msdn.com/Tags/sql+server+2012/RSS">http://channel9.msdn.com/Tags/sql+server+2012/RSS</a></p> </blockquote> <p>to your podcast reader of choice and have fun learning about all the new features in SQL Server 2012 such as:</p> <ul> <li>AlwaysOn</li> <li>Power View</li> <li>SSDT</li> <li>SSRS Data Alerts</li> <li>SSAS Tabular Modelling</li> <li>DAX Improvements</li> <li>MDS improvements</li> <li>SSIS improvements</li> <li>DQS</li> <li>StreamInsight improvements</li> <li>Data-Tier Apps (DACs)</li> <li>LocalDB</li> <li>FileTable</li> <li>Spatial improvements</li> <li>T-SQL paging</li> <li>Distributed Replay</li> <li>XEvents improvements</li> <li>ADO.Net Code-first</li> <li>T-SQL improvements</li> <li>Server roles</li> <li>Partitioning improvements</li> <li>ColumnStore</li> </ul> <p>Whew, quite a list!</p> <p><a href="http://twitter.com/jamiet">@jamiet</a></p>Variable enhancements in RC0 [SSIS]http://sqlblog.com/blogs/jamie_thomson/archive/2012/02/22/variable-enhancements-in-rc0-ssis.aspxWed, 22 Feb 2012 22:56:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:41899jamiet<p>I have been poking around idly in the RC0 release of SQL Server Integration Services (SSIS) 2012 and noticed a few nice enhancements that hadn’t really hit the newsstands as yet (well, I didn’t know about them anyway). Here is a quick rundown:</p> <h3>Expression is viewable in the Variables window</h3> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_5BC5784F.png"><img style="background-image:none;border-bottom:0px;border-left:0px;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_281D0BF1.png" width="636" height="136" /></a></p> </blockquote> <p>Fairly self-explanatory this one. If a variable has an expression applied to it that expression can now be seen in the Variables window; also notice that the variable icon gets an adorner specifying that there is an expression upon it. What is not so obvious is that if you add an expression to a variable then the EvaluateAsExpression property of that variable is automatically set to <strong>True:</strong></p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_06BD6655.png"><img style="background-image:none;border-bottom:0px;border-left:0px;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_17C19438.png" width="416" height="212" /></a></p> </blockquote> <p>That’s a welcome enhancement.</p> <h3>New System Variables</h3> <p>There are some new System variables available to you, some of which might be useful (one of which is very useful indeed):</p> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_31FA5AD4.png"><img style="background-image:none;border-bottom:0px;border-left:0px;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_36FC9883.png" width="279" height="598" /></a></p> </blockquote> <p>I have not found any documentation on these as yet so I’ll take a guess at what are for:</p> <ul> <li>IgnoreConfigurationsOnLoad – This is a boolean variable so I can only assume it does what it says on the tin – ignores configurations. The default value is False. I would assume that this doesn’t have any affect if you are using the new project deployment model.</li> <li>ProductVersion and LastModifiedProductVersion – In the package I am looking at these are both set to 11.0.1750.32 which is the same build number as in Help-&gt;About</li> </ul> <blockquote> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_03542C25.png"><img style="background-image:none;border-bottom:0px;border-left:0px;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="image" border="0" alt="image" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_61F48688.png" width="432" height="123" /></a></p> <p>The only thing I don’t understand is that if this package was built in a prior version (CTP3) why they are set to the same value?</p> </blockquote> <ul> <li>ServerExecutionID – This one is very useful indeed. It provides the ExecutionID that the SSIS Catalog assigned to the current execution.</li> </ul> <p>&#160;</p> <p>Hope that helps!</p> <p><a href="http://twitter.com/jamiet">@jamiet</a></p>SQL Server Data Tools does support required variableshttp://sqlblog.com/blogs/jamie_thomson/archive/2012/02/12/sql-server-data-tools-does-support-required-variables.aspxSun, 12 Feb 2012 11:58:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:41704jamiet<p>Over the past few years of using datadude (aka DBPro aka Visual Studio Database Projects) I have fallen prey to a peculiar little nuance – if you forget to supply a value for a sqlcmd variable then it will simply use a default and often that is not the desired behaviour. Hence why yesterday I submitted the following suggestion to <a mce_href="http://connect.microsoft.com/sqlserver/feedback" href="http://connect.microsoft.com/sqlserver/feedback">http://connect.microsoft.com/sqlserver/feedback</a> :</p> <blockquote> <h3><i>Specify sqlcmdvars properties as "required to be overridden”</i></h3> <p><i>In my current place of work I am responsible for maintaining our datadude projects and we have another team that is in charge of deployments. Hence, when we place new properties into the sqlcmdvars file I need to tell the deployment team what values to supply for that property per environment (dev, systest, uat, prod). <br>Unfortunately lack of communication/human error occasionally creeps in and, for whatever reason, no value gets supplied for some property at deployment time. If this is the case the default value as specified in the sqlcmdvars file gets used instead - invariably this will be the wrong value. I would like a mechanism within SSDT of preventing this from ever happening.</i></p> <p><i>One simple way to prevent this would be to specify that a sqlcmdvars property is *required* to be overridden during the deployment. In other words, never use the default. if an override is not supplied at deployment time then the deployment should fail. <br>Note that this stipulation should only be in place when deployment occurs from the command-line - if deploying from Visual Studio the default should be allowed (simply because Visual Studio doesn't provide a way to specify anything other than the default value supplied in the sqlcmdvars file).</i></p> <p><a mce_href="https://connect.microsoft.com/sqlserver/feedback/details/724366/ssdt-specify-sqlcmdvars-properties-as-required-to-be-overridden#details" href="https://connect.microsoft.com/sqlserver/feedback/details/724366/ssdt-specify-sqlcmdvars-properties-as-required-to-be-overridden#details"><i>https://connect.microsoft.com/sqlserver/feedback/details/724366/ssdt-specify-sqlcmdvars-properties-as-required-to-be-overridden#details</i></a></p> </blockquote> <p>It transpires that this requested feature is already available in the forthcoming SQL Server Data Tools (SSDT) as I shall now demonstrate. This screenshot shows the project properties of a SSDT project where we define the sqlcmd variables, I have defined a variable called $(Id_value):</p> <blockquote> <p><a mce_href="http://sqlblog.com/blogs/jamie_thomson/image_49257C87.png" href="http://sqlblog.com/blogs/jamie_thomson/image_49257C87.png"><img style="background-image:none;border-bottom:0px;border-left:0px;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="image" border="0" alt="image" width="732" height="259" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_63856F9E.png"></a></p> </blockquote> <p>In SSDT the nomenclature for deploying a project is “Publish”, a function that can be found by right-clicking on a project in Solution Explorer:</p> <blockquote> <p><a mce_href="http://sqlblog.com/blogs/jamie_thomson/image_1438774A.png" href="http://sqlblog.com/blogs/jamie_thomson/image_1438774A.png"><img style="background-image:none;border-bottom:0px;border-left:0px;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="image" border="0" alt="image" width="368" height="248" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_05219870.png"></a></p> </blockquote> <p>Selecting that brings up the Publish dialog.</p> <blockquote> <p><a mce_href="http://sqlblog.com/blogs/jamie_thomson/image_4AC6228E.png" href="http://sqlblog.com/blogs/jamie_thomson/image_4AC6228E.png"><img style="background-image:none;border-bottom:0px;border-left:0px;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="image" border="0" alt="image" width="554" height="536" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_6FE36CFA.png"></a></p> </blockquote> <p>Notice how the “Publish” button is greyed out – that it because I have not supplied a value for $(Id_value); supplying a value enables the Publish button and I can go ahead and publish my project. In other words, SSDT <i>insists</i> that I supply a value for that variable – exactly as I requested in my Connect submission.</p> <p>The same is true if I use the command-line publishing tool sqlpackage.exe. The following command does essentially the same thing as the above depicted Publish dialog:</p> <blockquote> <p>&gt;sqlpackage.exe /TargetDatabaseName:MyDB /TargetServerName:".\RC0" /Action:Publish /SourceFile:"TestRequiredSqlCmdVars.dacpac"</p> </blockquote> <p>Executing that gives me an error:</p> <blockquote> <p>Publishing to database 'MyDB' on server '.\RC0'. <br><font color="#ff0000">*** Missing values for the following SqlCmd variables: <br>'Id_value'.</font></p> </blockquote> <p>Here’s a screenshot showing the same:</p> <blockquote> <p><a mce_href="http://sqlblog.com/blogs/jamie_thomson/image_40454E6E.png" href="http://sqlblog.com/blogs/jamie_thomson/image_40454E6E.png"><img style="background-image:none;border-bottom:0px;border-left:0px;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="image" border="0" alt="image" width="618" height="201" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_05E9D88D.png"></a></p> </blockquote> <p>In order to supply a value for the variable from the command-line you need to use the /v: switch like so:</p> <blockquote> <p>&gt;sqlpackage.exe /TargetDatabaseName:MyDB /TargetServerName:".\RC0" /Action:Publish /SourceFile:"TestRequiredSqlCmdVars.dacpac" <b>/v:Id_value=1</b></p> <p><a mce_href="http://sqlblog.com/blogs/jamie_thomson/image_4B8E62AB.png" href="http://sqlblog.com/blogs/jamie_thomson/image_4B8E62AB.png"><img style="background-image:none;border-bottom:0px;border-left:0px;padding-left:0px;padding-right:0px;display:inline;border-top:0px;border-right:0px;padding-top:0px;" title="image" border="0" alt="image" width="617" height="201" src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_5C265D99.png"></a></p> </blockquote> <p>As you can see, publish was successful!</p> <p>So there you go, using SSDT you’ll no longer be able to fall prey to the problem I highlighted at the top of this blog post.</p> <p><a mce_href="http://twitter.com/jamiet" href="http://twitter.com/jamiet">@jamiet</a></p>Confirmed: Juneau is in the next version of Visual Studiohttp://sqlblog.com/blogs/jamie_thomson/archive/2011/09/20/confirmed-juneau-is-in-the-next-version-of-visual-studio.aspxTue, 20 Sep 2011 21:32:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:38615jamiet<p>Its no great secret that Juneau, a collection of features in the forthcoming SQL Server codenamed Denali, will be in the next version of Visual Studio but its nice to have it confirmed all the same. I have installed the first publicly available drop of that next version (known as Visual Studio 11 Developer Preview, download from <a href="http://msdn.microsoft.com/en-us/windows/hardware/hh454873" target="_blank">here</a>) that was released at last week's <a href="http://www.buildwindows.com/" target="_blank">BUILD </a>event and sure enough the Juneau stuff is right there. Here’s some screenshots:</p> <p>Note the presence of Juneau in the About dialog:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_1526A594.png"><img src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_7DABF162.png" 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;" alt="image" border="0" width="542" height="382"></a></p> <p>Here’s the new Juneau table designer:</p> <p><a href="http://sqlblog.com/blogs/jamie_thomson/image_50B68E87.png"><img src="http://sqlblog.com/blogs/jamie_thomson/image_thumb_4FDE289D.png" 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;" alt="image" border="0" width="544" height="324"></a></p> <p>OK, so this isn’t really news (unless you didn’t already know about it) however what <i>is </i>of interest is that this is the Express Edition of Visual Studio. This bodes well for full release – Juneau needs to be available to as many folks as possible which means not making it exclusive to the Ultimate Edition.</p> <p><a href="http://twitter.com/jamiet" target="_blank">@jamiet</a></p>