Omaha SQL Saturday and Friday Pre-Cons

 SQL Server User Group  Comments Off on Omaha SQL Saturday and Friday Pre-Cons
Jul 212015

In less than one month, the Omaha SQL/BI User Group will be hosting three all-day pre-con sessions on Friday, August 14th, with an all-day SQL Saturday conference to follow on Saturday, August 15th.

Please join us on Friday in one of three sessions:

  • Ed Leighton-Dick: SQL Server Security Master Class
  • Mike Fal: Introduction to Powershell
  • Dandy Weyn: Power BI

For more information on the pre-cons, please visit

On Saturday, August 15th, we will be hosting several speakers across five tracks with topics ranging from SQL Server 2016, AlwaysOn, social networking, Microsoft APS, and much more!

For more information on the SQL Saturday event, please visit

SSIS vNext: Survey Opportunity to Shape the Future

 SSIS  Comments Off on SSIS vNext: Survey Opportunity to Shape the Future
Jul 252014

If you’ve wanted to influence the future direction, now is the best chance to get your thoughts heard. Microsoft has a survey available for you to fill out, with which your feedback will be used to help shape the future of the product. Please fill it out, and use the comment boxes to fully capture your thoughts. The more meaningful the comments are (i.e. with business justification), the more valuable your submission will be to the product team.

Here’s the link to the survey:

Mar 032014

NOTE: SCOM 2012 R2 Update Rollup 5 addresses this issue. See for more details.

It turns out that in System Center Operations Manager 2012 R2 (SCOM 2012 R2), there is a pretty nasty, but well documented bug that causes corruption and the following error:

SCOM Specified cast is not valid

After clicking OK a few times, you can attempt to view the Distribution tab, but will find it is now empty:

SCOM Empty Distribution Tab

The root cause of this is likely due to the uninstall of a previously managed agent. If you are in a large shop with multiple admins, which agent was it that is currently causing the problem? Since I’m a SQL guy, I took to Transact-SQL to find the answer. Log into your OperationsManager database instance, and run the following SQL to find the bad agent:

SELECT BME.DisplayName,
  FROM [OperationsManager].[dbo].[CredentialManagerSecureStorage] AS CMSS
  JOIN [OperationsManager].[dbo].[CredentialHealthService] AS CHS
    ON CMSS.SecureStorageElementId = CHS.SecureStorageElementId
  JOIN [OperationsManager].[dbo].[BaseManagedEntity] AS BME
    ON CHS.HealthServiceId = BME.BaseManagedEntityId
 WHERE CMSS.UserName = N'scom_sqlm.service'
   AND CMSS.Domain = N'MYDOMAIN' -- e.g. MYDOMAIN\scom_sqlm.service

Objects with the IsDeleted column set to 1 are likely your problem agents.

So, how to clean this up? Well, there are a few options. One, if you are lucky you can re-approve the agent and after which you should be back to normal. Two, you can delete the offending row from the dbo.CredentialHealthService table. Lastly, and the option I’ll use is to call the same procedure that the SCOM Console uses in the GUI. To do that, run the following SQL:

DECLARE @SecureStorageElementId NVARCHAR(36);

SELECT @SecureStorageElementId = CMSS.SecureStorageElementId
  FROM [OperationsManager].[dbo].[CredentialManagerSecureStorage] AS CMSS
 WHERE CMSS.UserName = N'scom_sqlm.service'
   AND CMSS.Domain = N'MYDOMAIN' -- e.g. MYDOMAIN\scom_sqlm.service
   /* or use CMSS.DisplayName, which is what the SCOM console shows */;

SELECT @HealthServiceIdList = COALESCE(@HealthServiceIdList + N',', N'') + CAST(CHS.HealthServiceId AS NVARCHAR(MAX))
  FROM [OperationsManager].[dbo].[CredentialHealthService] AS CHS
  JOIN [OperationsManager].[dbo].[BaseManagedEntity] AS BME
    ON CHS.HealthServiceId = BME.BaseManagedEntityId
   AND BME.IsDeleted = 0
 WHERE CHS.SecureStorageElementId = @SecureStorageElementId;

PRINT N'exec dbo.p_SetApprovedHealthServicesForDistribution 
            @SecureStorageElementId=''' + @SecureStorageElementId + N''', 
            @HealthServiceIdList=N''' + @HealthServiceIdList + N''', 

The output of which, you are free to inspect – no changes are made – until you are comfortable with running it. When you are, simply copy and paste the output and execute against your OperationsManager database. The output is exactly what the SCOM console would do if you were making changes to the distribution list. Running the output of the above script is done at your own risk. Changing data in the SCOM OperationsManager database is not officially supported. Contact Microsoft support if you are in an emergency. Feel free to mention the above solution to your support engineer, however.

Apr 232013

When installing the Microsoft Assessment and Planning (MAP) Toolkit (commonly used for licensing renewal discussions and migration planning), one of the options is to have the tool use the new SQL Server Express LocalDB database container. Because this isn’t a normal SQL service, you may find it a bit challenging to connect to this database outside of the MAP toolkit.

If you want to connect to this database instance with SQL Server Management Studio (SSMS), use the following as your connection string: (LocalDB)\MAPToolkit. As long as you are the owner of the LocalDB instance, you will be able to connect just fine. If you are not the owner of the instance, you will get the following error:

LocalDB Connection Failure

If you run into this, verify who the owner of the LocalDB instance is, by excecuting SqlLocalDb.exe from a command line as follows:

SqlLocalDB.exe info MAPToolkit

Which should return details about who owns the instance, when it was last started, and its state (running, for instance):

LocalDB instance "MAPToolkit" started.

C:\Users\Phil.Brammer>SqlLocalDb.exe info MapToolkit
Name:               MAPToolkit
Version:            11.0.2100.60
Shared name:        
Owner:              MyDomain\Phil.Brammer
Auto-create:        No
State:              Running
Last start time:    4/23/2013 9:04:31 AM
Instance pipe name: np:\\.\pipe\LOCALDB#SH2C50D1\tsql\query

Here we can see that “MyDomain\Phil.Brammer” owns this instance. Any other user will not be able to connect to it. In order to allow other users to connect, follow these steps:

  • Connect to the instance in SSMS (logged in as the owner shown above)
  • For the users/groups you want to be able to connect, add them with the SQL “sysadmin” server role.
  • Share the LocalDB instance. The following command will do the trick: SqlLocalDb.exe share MAPToolkit MAPToolkitShare
  • Stop the MAPToolkit LocalDB instance if it is running. SqlLocalDb.exe stop MAPToolkit
  • Start the MAPToolkit LocalDB instance. SqlLocalDb.exe start MAPToolkit
  • As the other user, open up SSMS and connect to the shared LocalDB instance using the following connection string: (localdb)\.\MAPToolkitShare

To see details about the shared LocalDB instance, you can execute SqlLocalDb.exe info MAPToolkit -or- SqlLocalDb.exe info .\MAPToolkitShare

Either command will show the same results:

C:\Users\Phil.Brammer>SqlLocalDb.exe info .\MapToolkitShare
Name:               MAPToolkit
Version:            11.0.2100.60
Shared name:        MAPToolkitShare
Owner:              MyDomain\Phil.Brammer
Auto-create:        No
State:              Running
Last start time:    4/23/2013 9:17:35 AM
Instance pipe name: np:\\.\pipe\LOCALDB#SH80EAD1\tsql\query

You can unshare the database with SqlLocalDb.exe unshare .\MapToolkitShare

Note: LocalDB does not allow remote connections. You will need to connect from the machine where the LocalDB instance is running.

SQL Server Data Tools (SSDT) announced for Visual Studio 2012

 SQL Server, SQL Server 2012  Comments Off on SQL Server Data Tools (SSDT) announced for Visual Studio 2012
Mar 072013

Fresh off of the press, Microsoft has announced the availability of SSDT for Visual Studio 2012. This means you can now build SSRS, SSAS, and SSIS BI projects within Visual Studio 2012, allowing you to abandon VS 2010 for SQL 2012 BI work.

For more details see the official announcement here. You can download the bits here.

When installing, please follow the below steps:
– Double-click the downloaded file from the above link to start the install.
– When you see “SQL Server 2012 Setup” don’t panic. You’re at the correct place.
– Accept the license terms, click Next.
– After the setup files are installed and you are at the “Installation Type” screen, click Next. (Do not “add features to an existing instance of SQL Server 2012”)
– On the “Feature Selection” screen, select “SQL Server Data Tools…” from the Shared Features section. Click Next.
– In the “Error Reporting” screen, select the error reporting option if you desire, otherwise just click Next.
– After the install complete, you may be presented with a “computer restart required” dialog box. Click OK.
– At this point, the install should be complete. Click close and restart if necessary.
– Open Visual Studio 2012 and start a new BI project.

When VS 2012 is open, you should see the familiar BI templates as below:
SSDT & VS2012


Jan 312013

2013-11-26 – UPDATE – SQL Server 2012 CU 7 -or- SQL Server 2012 SP1 CU4 addresses the indexing recommendations. See KB 2829948 for more details.

I’ve been sitting on this content for about a year now, and due to external pressure (Thanks Matthew!) I’ve decided to put this into writing. One of the reasons I’ve been sitting on this content is that I didn’t want to share it publicly because the recommendations (after the break) can put you into a state of unsupportability with Microsoft. So with that, you take on the sole risk by implementing my recommendations. After you review them, you’ll see they are pretty basic, much needed, and well vetted. Jamie Thomson has written about these indexes (or rather mentions that they are available) as they have helped with his reporting pack Codeplex project. All-in-all, I now feel pretty safe documenting these, as others are starting to use them and talk about them publicly. With that, here goes.

Continue reading »