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.

Dec 032012
 

While I have not been active in the Omaha SQL Server User Group (SSUG) for all of its five years, I am proud to be part of an ever-growing SQL Community in the Omaha area. Please be sure to join me on Wednesday, December 5th, at Farm Credit Services America for our last 2012 SSUG meeting.

To celebrate the five year anniversary of the Omaha SSUG, the topic of the evening will be … YOURS! John & Naras are bringing back the “Choose Your Own Adventure” agenda, where you get to bring your own questions, problems, fodder, tips, and best practices to be discussed by all attendees. If you haven’t been to one of these sessions before, or are feeling as if you just can’t quite find the answer to your questions online, be sure to take part in this meeting. They are extremely informational and beneficial to the SQL Community.

I will be in attendance, and if there are any questions that need to be raised to Microsoft, I will do my best to ensure the appropriate people see it.

Oct 122012
 

If you attended my SQL Saturday 165 presentation in Lincoln, NE, on October 6th, I have uploaded the materials from that presentation. If you did not attend, please download, review the PowerPoint slides, and contact me with any feedback/questions you may have.

You can view the materials either through the SQL Saturday 165 Website (http://www.sqlsaturday.com/165/eventhome.aspx) or through this Website via the Speaking tab at the top of the page.

Oct 122012
 

New in the 2012 version of SSIS is a feature that lets us keep a user-configurable amount of deployed project versions in the catalog. The problem, and the point of this blog post, is that you cannot export an older version of a project without reverting back to that version. This may not be possible in your environment and this post is an effort to provide you with another option.

First, this is what I am referring to:
SSIS 2012 - Catalog Project Versions

When you right-click on a project under “Integration Services Catalogs” in SSMS, you can choose to view its versions. From this window you can revert back to an older version while still retaining the latest version deployed.

If you want to export projects, you can do so by right-clicking on the project and selecting “Export…” In this window, just point it to an appropriate location. In my case, I saved it to Documents\SQLSaturday165. The resulting file you produce is an .ispac (Integration Services deployment package). A .ispac file is just a .zip. Rename it to use .zip instead of .ispac if you want to extract a single package out of a project.

If you want to export an older version of the project, you have two options – set the older version of the project to be the current version via the Project Versions window, or you can use an SSIS package to explicitly export the desired project version. I will demonstrate the SSIS package solution. The full sample and download links are at the end of the post.

Step One
Create a global, temporary table (##) on the SQL instance that has the SSISDB catalog.

CREATE TABLE ##projectBLOB (val VARBINARY(MAX));

Step Two
Open up the ExportProject.dtsx package and modify the OLE DB Source in the data flow to use the values from the following query:

SELECT ov.object_version_lsn AS project_version_lsn,
       ov.object_id AS project_id,
       *
  FROM internal.object_versions AS ov
  JOIN catalog.projects AS p
    ON ov.object_id = p.project_id
   AND p.name = N'SQLSaturday165_SSISDBInternals' -- replace with your project name
  JOIN catalog.folders AS f
    ON p.folder_id = f.folder_id
   AND f.name = N'SQLSaturday165_SSISDBInternals' -- replace with the folder name that contains your project
 ORDER BY ov.created_time DESC;
GO

Step Three
Edit the derived column component to set the path that you want to put the exported project in. Remember that in the derived column component you need to escape slashes. (“C:\Temp” should be written as “C:\\Temp”)

SSIS 2012 Catalog Derived Column Changes

Step Four
Change the SSISDB connection manager object to point to your SQL instance containing the SSISDB catalog.

SSIS 2012 Catalog SSISDB Connection Manager Changes

Step Five
Execute the package.

Step Six
Confirm your project was exported to the path entered in the derived column component. You should have a .ispac file with the name of your project in that location. From this point, you can double-click on it to launch the deployment wizard, or you can rename the file so that it has a .zip extention. If you choose to rename it to .zip, you will be able to individually pull out the packages.

SSIS 2012 Catalog Project ispac file

The resulting .ispac output file from the export process.

SSIS 2012 Catalog .zip file

Renaming the .ispac file to .zip allows you to inspect its contents.

Just a word of warning – THIS SSIS APPROACH IS NOT A SUPPORTED METHOD OF EXPORTING PROJECTS FROM THE CATALOG. YOU NEED TO BE A MEMBER OF SYSADMIN OR THE SSIS_ADMIN DATABASE ROLE. YOU TAKE SOLE RESPONSIBILITY FOR RUNNING THIS PACKAGE IN YOUR ENVIRONMENT. I AM NOT RESPONSIBLE FOR ANY DAMAGE THAT IS A RESULT OF RUNNING THIS PACKAGE. THIS IS AN UNSUPPORTED TECHNIQUE THAT USES INTERNAL, NON-DOCUMENTED PROCEDURES FOR EXPORTING PROJECTS.

With that said, I am merely posting this technique for educational purposes. The supported method for exporting an older version of a project is to revert to the older version and exporting it using the GUI in SSMS and then reverting back to the correct, current project.

Oct 082012
 

When using package configurations in SSIS (pre-SQL 2012 or SQL 2012 with package deployment models), you may run into the following errors:
The configuration entry, "????????????????????????????", has an incorrect format because it does not begin with the package delimiter. Prepend "\package" to the package path.

The package path referenced an object that cannot be found: "????????????????????????????". This occurs when an attempt is made to resolve a package path to an object that cannot be found.

Since the characters will inevitably be munged when displaying this page, here is an image of the actual text:
SSIS Incorrect Configuration Error

If you encounter these errors and you are using SQL Server-based configurations (using the dbo.[SSIS Configurations] table), double-check that the data types of all columns in the config table are NVARCHAR, not VARCHAR. If they are not NVARCHAR, set them back accordingly. Occasionally you may need to modify this table to extend the max length of some of the columns and when that happens someone may inadvertently change the data type to VARCHAR.

Sep 282012
 

Don’t miss out on your opportunity for a day of free SQL Server training! Come join us in Lincoln, NE, on Saturday, October 6th starting at 8:30 AM. The event will be held in Avery Hall on the University of Nebraska-Lincoln campus. There are 6 tracks with each having 5 sessions. That’s 30 FREE sessions to choose from, with industry experts and emerging leaders speaking about their passion! Don’t miss out!

I will be doing a presentation on the internals of the SSIS Catalog introduced in SQL Server 2012. For those that haven’t been much involved in the new catalog subsystem of SSIS, please plan on attending as I will be giving an overview and then going into some of the procedures, processes, and logging that are possible with this new framework. See you there!

http://www.sqlsaturday.com/165/eventhome.aspx

Sep 282012
 

Fellow MVP Jonathan Kehayias has a post on scripting out the Database Mail configuration with PowerShell and SMO. While this solution looks perfect, there is a bug in the Mail.Script() output, not mentioned in Jonathan’s post except for one reader’s comment, that does not script out the SMTP server name and port number. (Note, that is not a bug in Jonathan’s code; it is a bug in how Microsoft coded the API.)

This post is to give an example of how I worked around that problem so that I could script out a SQL instance’s mail config and apply it on a new instance as needed. Quite simply, I added a ForEach loop over each account in the Mail object and printed its configured mail server name and port. I just plugged those in as parameters to msdb.dbo.sysmail_update_account_sp. I also added a few more enhancements to Jonathan’s script – resizing the width of the output and adding appropriate sp_configure statements to get things rolling on the new instance. Below is the code for SQL 2005+:

#Original code from Jonathan Kehayias, http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/08/23/scripting-database-mail-configuration-with-powershell-and-smo.aspx

[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");

$Host.UI.RawUI.BufferSize = New-Object Management.Automation.Host.Size (500, 25);

"sp_configure 'show advanced options',1
RECONFIGURE
GO
sp_configure 'Database Mail XPs',1
RECONFIGURE 
GO
"

#Set the server to script from 
$ServerName = "SQLServerMachine";

#Get a server object which corresponds to the default instance 
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $ServerName

#Script Database Mail configuration from the server 
$srv.Mail.Script();

ForEach ($account in $srv.Mail.Accounts) {
	$AccountName = $account.Name
	$MailServerName = $account.MailServers[0].Name;
	$MailServerPort = $account.MailServers[0].Port;
	"EXEC msdb.dbo.sysmail_update_account_sp @account_name = N'$AccountName',";
		"	@mailserver_name = N'$MailServerName',";
		"	@port = N'$MailServerPort'";
}

This will give you output such as the following:

sp_configure 'show advanced options',1
RECONFIGURE
GO
sp_configure 'Database Mail XPs',1
RECONFIGURE 
GO

EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'AccountRetryAttempts', @parameter_value=N'1', @description=N'Number of retry attempts for a mail server'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'AccountRetryDelay', @parameter_value=N'60', @description=N'Delay between each retry attempt to mail server'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'DatabaseMailExeMinimumLifeTime', @parameter_value=N'600', @description=N'Minimum process lifetime in seconds'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'DefaultAttachmentEncoding', @parameter_value=N'MIME', @description=N'Default attachment encoding'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'LoggingLevel', @parameter_value=N'2', @description=N'Database Mail logging level: normal - 1, extended - 2 (default), verbose - 3'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'MaxFileSize', @parameter_value=N'1000000', @description=N'Default maximum file size'
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'ProhibitedExtensions', @parameter_value=N'exe,dll,vbs,js', @description=N'Extensions not allowed in outgoing mails'
EXEC msdb.dbo.sysmail_add_account_sp @account_name=N'DBMailAccount', 
		@email_address=N'sqlops@ssistalk.com', 
		@display_name=N'SQL Operations'
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name=N'DataManagement'
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name=N'DataManagement', @account_name=N'DBMailAccount', @sequence_number=1
EXEC msdb.dbo.sysmail_update_account_sp @account_name = N'DBMailAccount',
	@mailserver_name = N'mail.ssistalk.com',
	@port = N'25'
css.php