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

Enjoy!

Jan 312013
 

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 »

Jan 072013
 

Waaaaay back in 2012, I had the great pleasure of being a technical reviewer of another book, What’s New in SQL Server 2012, authored by Rachel Clements (@RachelClements) and Jon Reade (@JonReade) and published by Packt Publishing.

Together, these two authors put together a very nice desk reference of the new features introduced in SQL Server 2012, with topics ranging from an overview of the various SQL Server editions to new functions, such as EOMONTH (yep, there’s a new “End of Month” datetime function).

I highly recommend this book for any SQL Server user who is new to SQL Server 2012.

Obtain your copy here: http://www.packtpub.com/sql-server-2012-professional-tips-and-tricks/book

Jan 072013
 

If you are on SQL Server 2012 SP1 (11.0.3000) and are experiencing higher than normal CPU loads on your machine, please take a look at the Windows Application log for the following:

  • Warning / Event ID 1004 / MsiInstaller / Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_ANS’, component ‘{0CECE655-2A0F-4593-AF4B-EFC31D622982}’ failed. The resource ” does not exist.
  • Warning / Event ID 1001 / MsiInstaller / Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_ANS’ failed during request for component ‘{6E985C15-8B6D-413D-B456-4F624D9C11C2}’
  • Information / Event ID 1040 / MsiInstaller / Beginning a Windows Installer transaction: {A7037EB2-F953-4B12-B843-195F4D988DA1}. Client Process Id: 16708.
  • Information / Event ID 11724 / MsiInstaller / Product: SQL Server 2012 Management Studio — Install started.
  • Information / Event ID 10000 / RestartManager / Starting session 0 – 2013-01-07T12:40:46.549958800Z.
  • Information / Event ID 11728 / MsiInstaller / Product: SQL Server 2012 Management Studio — Configuration completed successfully.
  • Information / Event ID 1035 / MsiInstaller / Windows Installer reconfigured the product. Product Name: SQL Server 2012 Management Studio. Product Version: 11.1.3000.0. Product Language: 1033. Manufacturer: Microsoft Corporation. Reconfiguration success or error status: 0.
  • Information / Event ID 1042 / MsiInstaller / Ending a Windows Installer transaction: {A7037EB2-F953-4B12-B843-195F4D988DA1}. Client Process Id: 16708.
  • Information / Event ID 10001 / RestartManager / Ending session 0 started 2013-01-07T12:40:46.549958800Z.

If you have the above symptoms, please look at this non-security hotfix KB download: http://www.microsoft.com/en-us/download/details.aspx?id=36215. Do not install if you have already applied any post SP1 hotfixes, such as SP1 CU1. You can also view the SQL Server Connect bug here: http://connect.microsoft.com/SQLServer/feedback/details/770630.

Installing that fix will bring your SQL version to 11.0.3128, fix the problem with the MSI installer, and it should drop your CPU consumption a bit as well.

Jun 202012
 

Cumulative update #2 is now available for SQL Server 2012, and I’m happy to let you know that there is a very, very welcome change in database user rights.

If you have SELECT permissions on a given table, you can now execute DBCC SHOW_STATISTICS to view the statistics histogram of any stats object on that table. Yep, this also means you get to see the histogram in Management Studio as well. Very cool. You no longer need to be a member of db_ddladmin or higher.

To disable this new behavior, enable trace flag 9485. For more details, see KB 2683304.

Feb 172012
 

Without fully restating what’s already published, please read this MSDN Blog Post for more details on how to determine which SQL Client version is connecting to a SQL instance.

Basically,
SELECT CAST(protocol_version as BINARY(4))
FROM sys.dm_exec_connections
will tell you which version is connecting to your instance based on the TDS protocol appendix document, http://msdn.microsoft.com/en-us/library/dd339982(PROT.13).aspx.

SQL Server 2012 is almost complete, and we now know that the TDS protocol version for this will be 0×74000004.

Another option appears to be to use the client_version column in sys.dm_exec_sessions. A client_version = 4 is SQL Server 2000. 5 = SQL Server 2005. 6 = SQL Server 2008. 7 = SQL Server 2012.

Feb 012012
 

Tonight is the first 2012 SSUG meeting for the Omaha area. The agenda for tonight is simply to have an open discussion on any topics you want to bring up – a current SQL issue you’re working on, upgrade problems, architecture questions, etc… I will be in attendance helping with the discussion.

Please register if you plan on attending so that the hosts can arrange enough food as well as to ensure you get registered for SWAG!

http://omahamtg.com/Events.aspx?ID=142