SQL Server Data Tools in Visual Studio 2012

In August I’ll be giving a couple of presentations at devLink. One of them will be on the new SQL Server Data Tools that was released with SQL Server 2012. As you may be aware, I’ve been a proponent of Visual Studio Database Projects since their initial release with Visual Studio 2005.

With SQL Server 2012 the SQL team took ownership of the database projects. They completely retooled them so now they can release them as “out of band” add-ons for Visual Studio. The new version is called SQL Server Data Tools, or SSDT for short. It’s included with VS2012, or you can download a version compatible with Visual Studio 2010 at http://msdn.microsoft.com/en-us/data/tools.aspx.

I’ve been using it for a real world production project for some time now. While I like it, there are some major differences between the new SSDT and the former database projects. Over the next few blog posts I want to highlight some of those differences, culminating with the devLink presentation.

For this series of posts I’ll be using the Visual Studio 2012 Release Candidate, which from here on I’ll simply refer to as VS2012. As this is a Release Candidate there shouldn’t be any noticeable changes between now and the final release.

If you are still on VS 2010 don’t fret, what I’ll describe applies to it as well, assuming you have gone to the link above and downloaded the SSDT add in.

The first difference is with creating a new project. With VS 2012 the older database projects are gone. Only in 2010 can you still do both. Here’s the new project screen shot from VS2010:



Here is the screen shot from VS2012.


As you can see, the Database branch is gone and only the SQL Server Database Project exists.

Now for the next difference. With VSDB Projects, when you created a new project you were immediately walked through a wizard that helped you with various default choices, and allowed you to import a database. With SSDT, once you create a new project you are given a blank slate, an empty project to start from.


If you are creating a new database from scratch, it is left to you to create the entire folder structure, and to name your files correctly.

I’d highly suggest though that you import at least one database, to see how the wizard organizes things, so that you can follow suit. Importing a database is as easy as it was in VSDB Projects, but we’ll save that for the subject of the next blog post.

Preventing PowerShell from Running in the ISE

Once in a great while you will have the need to force your scripts to only run in the console. Mostly likely you have created special formatting commands that only work in the console, but would cause the script to crash in the ISE. It is prudent then to prevent that code from even executing. But how?

Turns out it’s pretty simple. The PowerShell ISE has a built in variable called $psise. Within the ISE you can use this variable to alter various settings in the environment, such as colors, or the position of the code window. When you run from the console however, $psise will be null.

So the solution is simple, just check to see if $psise is null. To make it even easier, I put the code inside a simple function I can import to my various scripts. It checks to see if we’re in the ISE, and if so presents the user with an error and breaks out.

Note, the break will only be effective if you call this from the outermost level script, that is the script you actually run from the console command line. Break will return control to the outer script, which in that case would be nothing.

I admit this is not something you’ll use all that often, if it were you might consider throwing an error as opposed to breaking so you can nest it at any level.

Here is my simple version of the script.

function Prevent-IseExecution ()
  Prevents a script from executing any further if it's in the ISE
  The function determines whether it's running in the ISE or a window, 
  if it's in the ISE it breaks, causing further execution of the script to halt. 

  # If we're in the PowerShell ISE, there will be a special variable
  # named $PsIse. If it's null, then we're in the command prompt
  if ($psise -ne $null)
    "You cannot run this script inside the PowerShell ISE. Please execute it from the PowerShell Command Window."
    break # The break will bubble back up to the parent


# Stop the user if we're in the ISE

# Code will only continue if we're in command prompt
"I must not be in the ISE"

Free Microsoft E-Books!

Yes,  you read that right, free! Microsoft has lost their minds and is now giving away a rather large collection of e-books, yours for the taking.

They cover quite a range of subjects too, including ASP.Net, Office, SharePoint, SQL Server, Visual Studio, Windows, Azure, Phone 7, and Server. As you would expect they come in PDF format, but it gets better! They  also have them in MOBI and EPUB formats, so if you have a device that supports them you get a full featured experience. (I know Kindle uses the MOBI format and Apple’s iBook EPUB, not sure what other readers use).

You can get these goodies from:


Looks like I have many more late nights of reading ahead of me. Oh well, I guess three hours of sleep a night ought to be fine for anyone. 

PowerShell Editors: PowerSE

I’ve seen interest really growing in PowerShell over the last year or two. The editor included with PowerShell v2 though, does leave much to be desired. I can say from experience the v3 editor is FAR better, but still has some room to grow.

There are many good editors on the market, but many people just getting into PowerShell need something cheap (i.e free) to learn on. I’ve found the PowerSE editor from PowerWF to be an excellent choice to fill this gap.

As I indicated it is indeed free. And for a free editor it’s remarkably full featured. It has a very good help system, intellisense, and the ability to see your call stack. It also has a variable pane where you can see all of the variables, and their values for the current session.

You can have multiple tabs open, each with a different script. It has some customizability, you can set the font for both the editor and the console. My favorite feature is the very easy to use debugger, complete with breakpoints and the ability to examine variables through the aforementioned variable pane.

I still think it’s important to know the PowerShell IDE, as it’s on all the latest builds of Windows. But once you have grasped it’s basics, go grab yourself a copy of PowerSE. Only then will you really be able to enjoy the happiness that is PowerShell.

Simulating SQL Server Work with PowerShell

No, I’m not talking about simulating your job so you can sit home in your PJs and play Call of Duty. (Although if you work out a way to do so, call me!) What I’m speaking of is emulating a workload on your server.

There’s many reasons for wanting to do this. For folks like me, who do a lot of demos, it can be a good way to get your server busy so you can demo things like DMVs or the SQL Provider. Another use would be stress or performance testing your applications. Ensuring you get good retrieval times when the server is loaded.

I have to give a little shout out here to my friend and fellow MVP Allen White (Blog | Twitter). I had attended one of his sessions back at a SQL Saturday, and when I downloaded his samples found a “SimulateWork” PowerShell script among the sample code. In Allen’s script he used the .Net SQLClient library to do his work. I decided to emulate the concept but to rewrite the entire thing using the SQL Provider. I borrowed a few of his queries so I could be sure my results were similar.

The script is pretty simple. I have a function that will load up the SQL Provider, if it’s not already loaded. Next is the SimulateWork function. It has one parameter, how many times you want to execute the code in the loop.

Within the function I load up a series of T-SQL queries against the AdventureWorks2012 database using “here” strings. I then execute them using the Invoke-SqlCmd cmdlet. Finally I have a little code which calls the functions.

My goal with this script was to get the server ram loaded up and simulate some I/O so I could demo a few simple things. Astute observers will notice that, because these are just executing the same T-SQL commands over and over, for the most part SQL Server will just hit its plan cache and memory each time. If those are important to you, I’d suggest altering the T-SQL commands to include a where clause of some sort then each time through the loop add your new where condition to the variable holding the T-SQL.

So without further ado, here’s the full script.

# Simulate Work
# This routine will simulate work being done against a SQL Server. Ideal
# for demoing things like the SQL Profiler or DMV Stats. 
# The T-SQL queries were tested against the AdventureWorks2012 database. You
# may have to alter some queries if you are on previous versions of
# AdventureWorks.
# Author......: Robert C. Cain
# Blog........: http://arcanecode.com
# Twitter.....: http://twitter.com/arcanecode
# Last Revised: July 17, 2012
# Credit...: This script is based on one by SQL MVP Allen White.
#   Blog...: http://sqlblog.com/blogs/allen_white/default.aspx
#   Twitter: http://twitter.com/sqlrunr
# In his original script, he used the System.Data.SqlClient .Net library to 
# simulate work being done on a SQL Server. I liked the idea, but rewrote 
# using the SQL Provider. A couple of the SQL queries I borrowed from 
# his routine. 
# Other uses: The techniques below might also be a good way to stress test
# a system. Alter the t-sql (and probably variable names) and away you go. 

# Loads the SQL Provider into memory so we can use it. If the provider is 
# already loaded, the function does nothing. This makes it safe to call
# multiple times. 
function Load-Provider
  # Get folder where SQL Server PS files should be
  $SqlPsRegistryPath = "HKLM:SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"
  $RegValue = Get-ItemProperty $SqlPsRegistryPath
  $SqlPsPath = [System.IO.Path]::GetDirectoryName($RegValue.Path) + "\"
  # Check to see if the SQL provider is loaded. If not, load it. 
  [String] $IsLoaded = Get-PSProvider | Select Name | Where { $_ -match "Sql*" }

  if ($IsLoaded.Length -eq 0)
    # In this case we're only using the SQL Provider, so the code to load the
    # SMO has been commented out. Leaving it though in case you copy and paste it from somewhere
    # and need it. 
  # ----------------------------------------------------------------------------------------
    # Load the assemblies so we can use the SMO objects if we want. 
    # Note if all you need is the basic SMO functionality like was in 2005, you can get away
    # with loading only the first three assemblies. 
  # ----------------------------------------------------------------------------------------
    $assemblylist = 
    "Microsoft.SqlServer.ConnectionInfo ", 
    "Microsoft.SqlServer.SmoExtended ", 
    "Microsoft.SqlServer.Dmf ", 
    "Microsoft.SqlServer.SqlWmiManagement ", 
    "Microsoft.SqlServer.Management.RegisteredServers ", 
    "Microsoft.SqlServer.Management.Sdk.Sfc ", 
    "Microsoft.SqlServer.SqlEnum ", 
    "Microsoft.SqlServer.RegSvrEnum ", 
    "Microsoft.SqlServer.WmiEnum ", 
    "Microsoft.SqlServer.ServiceBrokerEnum ", 
    "Microsoft.SqlServer.ConnectionInfoExtended ", 
    "Microsoft.SqlServer.Management.Collector ", 
    foreach ($asm in $assemblylist) {[void][Reflection.Assembly]::LoadWithPartialName($asm)}

    # Set the global variables required by the SQL Provider    
    Set-Variable -scope Global -name SqlServerMaximumChildItems -Value 0
    Set-Variable -scope Global -name SqlServerConnectionTimeout -Value 30
    Set-Variable -scope Global -name SqlServerIncludeSystemObjects -Value $false
    Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

    # Load the actual providers  
    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100
    # The Types file lets the SQL Provider recognize SQL specific type data.
    # The Format file tells the SQL Provider how to format output for the 
    # Format-* cmdlets.
    # First, set a path to the folder where the Type and format data should be
    $sqlpTypes = $SqlPsPath + "SQLProvider.Types.ps1xml"
    $sqlpFormat = $sqlpsPath + "SQLProvider.Format.ps1xml"
    # Now update the type and format data. 
    # Updating if its already loaded won't do any harm. 
    Update-TypeData -PrependPath $sqlpTypes
    Update-FormatData -prependpath $sqlpFormat
  # Normally I wouldn't print out a message, but since this is a demo
  # it will give us a nice 'warm fuzzy' the provider is ready
  Write-Host "SQL Server Libraries are Loaded"


# This function simply loads a series of SQL Commands into variables, then
# executes them. The idea is to simulate work being done on the server, so
# we can demo things like SQL Profiler. 
# Parameters: $iterations - The number of times to repeat the loop
function SimulateWork ($iterations) 

  $sqlSalesOrder = @"  
  SELECT d.SalesOrderID
       , d.OrderQty
       , h.OrderDate
       , o.Description
       , o.StartDate
       , o.EndDate
    FROM Sales.SalesOrderDetail d
   INNER JOIN Sales.SalesOrderHeader h ON d.SalesOrderID = h.SalesOrderID
   INNER JOIN Sales.SpecialOffer o ON d.SpecialOfferID = o.SpecialOfferID
   WHERE d.SpecialOfferID <> 1

  $sqlSalesTaxRate = @"  
       , st.TaxRate
    FROM Sales.SalesTaxRate st
    JOIN Person.StateProvince sp 
         ON st.StateProvinceID = sp.StateProvinceID
   WHERE sp.CountryRegionCode = 'US'
   ORDER BY st.TaxRate desc ;
  $sqlGetEmployeeManagers = @"
  EXECUTE dbo.uspGetEmployeeManagers 1

  $sqlSalesPeople = @"
  SELECT h.SalesOrderID
       , h.OrderDate
       , h.SubTotal
       , p.SalesQuota
    FROM Sales.SalesPerson p
   INNER JOIN Sales.SalesOrderHeader h 
         ON p.BusinessEntityID = h.SalesPersonID ;

  $sqlProductLine = @"
       , ProductNumber
       , ListPrice AS Price
    FROM Production.Product
   WHERE ProductLine = 'R'
     AND DaysToManufacture < 4
   ORDER BY Name ASC ;

  $sqlHiringTrend = @"
  WITH HiringTrendCTE(TheYear, TotalHired)
    (SELECT YEAR(e.HireDate), COUNT(e.BusinessEntityID) 
     FROM HumanResources.Employee AS e
     GROUP BY YEAR(e.HireDate)
   SELECT thisYear.*, prevYear.TotalHired AS HiredPrevYear, 
    (thisYear.TotalHired - prevYear.TotalHired) AS Diff,
    ((thisYear.TotalHired - prevYear.TotalHired) * 100) / 
                 prevYear.TotalHired AS DiffPerc
   FROM HiringTrendCTE AS thisYear 
        HiringTrendCTE AS prevYear
   ON thisYear.TheYear =  prevYear.TheYear + 1;

  $mi = $env:COMPUTERNAME + "\SQL2012"  
  Set-Location SQLSERVER:\sql\$mi\databases\AdventureWorks2012

  for ($i=1; $i -lt $iterations; $i++) 
    Write-Host "Loop $i"
    $outSalesOrder = Invoke-Sqlcmd -Query $sqlSalesOrder -ServerInstance $mi -SuppressProviderContextWarning
    $outSalesTaxRate = Invoke-Sqlcmd -Query $sqlSalesTaxRate -ServerInstance $mi -SuppressProviderContextWarning
    $outGetEmployeeManagers = Invoke-Sqlcmd -Query $sqlGetEmployeeManagers -ServerInstance $mi -SuppressProviderContextWarning
    $outSalesPeople = Invoke-Sqlcmd -Query $sqlSalesPeople -ServerInstance $mi -SuppressProviderContextWarning
    $outProductLine = Invoke-Sqlcmd -Query $sqlProductLine -ServerInstance $mi -SuppressProviderContextWarning
    $outHiringTrend = Invoke-Sqlcmd -Query $sqlHiringTrend -ServerInstance $mi -SuppressProviderContextWarning


# This is the code that executes the above functions. To change the workload
# simply change the number of iterations. 

# Number of times to repeat the work simulation. 
$iterations = 100 

Write-Host "Starting simulated work"
SimulateWork $iterations
Write-Host "Done Working"