Monthly Archives: December 2013

SSRS 2012 mails missing when using data driven subscriptions and bulk mail

Data driven subscriptions (DDS) are a powerful mechanism which can be used to generate mailings. When migrating from SSRS 2008 to 2012 I noticed mails were missing with a DDS containing about 2.000 mails.

This problem occurred in SP1 and Microsoft’s advice was to download the hotfix pack.
http://support.microsoft.com/kb/2894115

After some serious testing the issue seems to be a SMTP server issue.

emailextension!WindowsService_0!1ce0!12/05/2013-15:39:03:: e ERROR: Error sending email. Exception: System.Net.Mail.SmtpException: Failure sending mail. —> System.IO.IOException: Unable to write data to the transport connection: An established connection was aborted by the software in your host machine. —> System.Net.Sockets.SocketException: An established connection was aborted by the software in your host machine
at System.Net.Sockets.NetworkStream.Write(Byte[] buffer, Int32 offset, Int32 size)
— End of inner exception stack trace —
at System.Net.Sockets.NetworkStream.Write(Byte[] buffer, Int32 offset, Int32 size)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.DelegatedStream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.Base64Stream.FlushInternal()
at System.Net.Base64Stream.Write(Byte[] buffer, Int32 offset, Int32 count)
at System.Net.Mime.MimePart.Send(BaseWriter writer)
at System.Net.Mime.MimeMultiPart.Send(BaseWriter writer)
at System.Net.Mime.MimeMultiPart.Send(BaseWriter writer)
at System.Net.Mail.SmtpClient.Send(MailMessage message)
— End of inner exception stack trace —
at System.Net.Mail.SmtpClient.Send(MailMessage message)

The solution was to get rid of the limits “session size” and “messages per connection”

smtp_settings

Why the issue didn’t occur in SSRS 2008 using the same SMTP server puzzled me. Maybe SSRS 2012 speeds up the processing using multiple threads and overloads the mail server. However I am glad that the update and the new SMTP settings solved my problem.

Which DTEXEC version is used when running multiple SSIS versions on 1 server?

Today I faced some problems running SSIS 2008 packages on a server where SSIS 2008 and 2012 was installed.
The SSIS 2008 binaries are placed in the 100\DTS\Binn folder and the 2012 binaries in the 110\DTS\Binn folder.

When clicking a DTSX file it is hard to see which DTEXEC version will be used.
When running dtexec.exe from the commandline you can check which version is actually used by default.

Microsoft (R) SQL Server Execute Package Utility
Version 10.50.4000.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Version 10.5 is the SSIS 2008-R2 version. 11.0 is SSIS 2012.

Solution for running the SSIS 2008 version by default
Remove  the PATH environment variable contaning the following string: \110\DTS\Binn\
Be sure the PATH variable contains the 2008 SSIS binary path : C:\Program Files\Microsoft SQL Server\100\DTS\Binn\

Of course you can do this trick the other way around if you want to use SSIS 2012 by default.

Troubleshoot login errors with SQL Server login failed states

Whenever you receive a Login failed error from a SQL instance your faced with the problem  of context. In the SQL Server log you can find a more detailed description. You should see the client generating the error and the state. The table below helps to find the cause of the failed login attempt.

Error 18456, Severity 14: Login Failed States 
Login Failed states summary
State Description
1 Only state returned in SQL 2000. Server is in Single-User Mode (2005).
2 and 5 Invalid User Id
6 Attempt to use a windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid Password
10 Read Ref Link #2
11 and 12 Valid login but server access failure; Default Database access failure. Or Initial database connection failure in connection string.
13 SQL Server service paused
16 User doesn’t have permission to target database
18 Change password required
23 Server in process of shutting down, and user attempted to login.
27 Initial database could not be determined for session
38 Initial database could not be determined for session (SQL 2008)
40 Default database could not be accessed (SQL 2008)

Checking the progress of a backup or restore

With SQL Server’s powerful DMV’s it’s easy to monitor the progress of a SQL Backup or restore operation, but you can also use it to see the current running queries on your SQL instance. In this case you should delete the last WHERE command in the query. Note that the percent_complete column is not updated for all T-SQL commands.
Continue reading

Welcome to my SQL Server blog

On this blog I am planning to write especially SQL Server articles . I started the blog to share my knowledge on several SQL Server subjects. I hope you will find the articles useful and will provide me with feedback. We have a big SQL Server community around the world and sharing the knowledge will benefit all of us.