Connecting to a pre-SQL 2012 SSIS Instance with SQL 2012 SSMS

If you are using SQL Server Management Studio in SQL 2012 to try to connect to an older SSIS instance (SQL 2008 R2 and earlier) called “SSISServerName”, you will get an error: Connecting to the Integration Services service on the computer “SSISServerName” failed with the following error: “Class not registered”. This error can occur when […]

Read more
Which SQL Client versions are connecting to my instance?

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 […]

Read more
Quick Tip: Error: 18456, Severity: 14, State: 38.

When trying to investigate the SQL error, “Error: 18456, Severity: 14, State: 38.” it can be frustrating when not a single log on the system will tell you what the actual problem is. In the case of state 38 (16 or 27 prior to SQL 2008) this error means the database specified in the client […]

Read more
SSIS 2008 – Cannot Show Visual Studio 2008 Tools for Applications error

Should you come across this error message, “cannot show visual studio 2008 tools for applications”, you may have to clean up your VSTA environment. You may also have the following additional error message: The System Cannot Find The File Specified Here’s how to fix it, thanks to Silviu Guea, Microsoft. Create a batch file with […]

Read more
Be careful with sp_configure

For those of you that work extensively with sys.dm_exec_query_stats and associated plan cache DMVs, be aware that altering the server’s ‘max degree of parallelism’ setting will flush the query plan cache. You may see this message in your server’s SQL Log file: “SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘SQL Plans’ […]

Read more
SSIS 2008 – Beware of incorrect version stamp in SSMS

There is a bug where even if you have applied a patch or service pack to SSIS 2008, the version listed next to the “Integration Services” service in Management Studio continues to show 10.0.1600, which is the version number for RTM. As always, if you are unsure, it is best to determine the actual version […]

Read more