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
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
SQL Server 2005 – How to introduce data corruption without trying

It is quite easy in SQL Server 2005 to accidentally (or intentionally, I suppose) create data corruption. It’s best to just show via a demo, so here you go: (Ref: http://www.sqlservercentral.com/Forums/Topic915058-2669-1.aspx. Thanks, Paul White, for doing all of the work for this post. 😉 ) — In SQL Server 2005 only — fails select round(0.5,0) […]

Read more