Connecting to a pre-SQL 2012 SSIS Instance with SQL 2012 SSMS
Which SQL Client versions are connecting to my instance?
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 […]
Be careful with sp_configure
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 […]
SQL Server 2005 – How to introduce data corruption without trying
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’ […]
SSIS – Avoiding the Sort Components
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) […]
SQL Server – COUNT(expression) bug
When using a Merge or Merge Join component in a data flow, your incoming data is required to be sorted. While it may be easy to drop a Sort component into your data flow, it may make more sense to perform the sort in your source query (if you are using a source such as […]
SSIS – Using a Script Component as a Source
Starting with SQL Server 2005 RTM and through the latest SQL Server 2008 build at the time of this writing, there is a pretty nasty bug with respect to the COUNT(expression) function and its rule that when using an expression, it filters out NULLs in its count results.
Just a quick tutorial on using a script component as a source… Follow the link for screenshots.