Index of all SQL Server build numbers since 7.0 Beta

Stumbled across a nice SQL Server build number site a while back that has a comprehensive (I think?) list of every SQL Server build from the SQL 7 beta 3 up through SQL 2012 RC1 including every CU and hotfix along the way.  There is also a link to download the KB that corresponds to each build if appropriate.  If you happen to know of a build that isn’t listed, it looks like this site is somewhat crowd-sourced, so post the details in the comments and it will likely make it up to the main page.

(If you look in the comments, you can even find little “anonymous” nuggets, such as the following: “SQL2012 RTM is 11.0.2100.19”)

Post SR1 Rollup Package 1 for Data Dude available

If you are using Visual Studio 2005 Team Edition for Database Professionals (Data Dude), you might be surprised to know that there is a post-SR1 Rollup Package 1 update available.  It can be download here.  The list of fixes can be found here, and I have pasted it in below:

Inconsistent revalidation of database object definitions when you close and then reopen a Visual Studio database project

A database project cannot be deployed if the project contains interdependent inline functions

If you use a four-part name to reference a table, you may receive an incorrect syntax exception

Database Professional Interpreter does not handle the "sp_executesql" parameter correctly

In a Visual Studio 2005 database, you cannot import a schema that was created by using the SQL Server 2000 compatibility mode

Error message when you use Design-Time Extensibility (DTE) objects: "Do you want to reload"

Cross-database references cause slow performance when you load a project

Schema Compare crashes the Visual Studio IDE when databases contain different versions of assemblies

Schema Compare does not correctly generate the update script for a stored procedure

The schema script is not generated when triggers or views that have the same name exist

Error message when you build a deployment script: "MSB4018: The 'SqlBuildTask' task failed unexpectedly"

After Schema Compare obtains the latest project files from Source Control, Schema Compare indicates that databases are different

Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables

My buddy Wayne just brought to my attention a white paper that came out just a year ago from Microsoft that describes how to design SSAS 2005 cubes for use with Excel 2007.  I'm posting it here for my own future reference.  Here is the description:

Overview

Microsoft Office Excel 2007 takes advantage of most of the features in Microsoft SQL Server 2005 Analysis Services. To take full advantage of these features, it is important to keep in mind the end-user experience in Office Excel 2007 when you are designing cubes.
This document outlines how you can create a good end-user experience by optimizing the cube design for Office Excel 2007 PivotTable dynamic views.

SSIS Performance Counters don't work on x64 (out of the box)

If you want to spend some time tuning performance of your SSIS solution, it is likely that at some point you will leverage the SSIS performance counters found in the SQL Server:SSIS Pipeline object.  However, if you are using 64-bit SQL, you will not find the performance counters listed in PerfMon.  That is because here is a known bug in SQL Server 2005 x64, which you can read about in KB 941154.

What is the problem?  The short version is: When SQL is installed, it accidentally points to the \Program Files (x86)\ directory for the SSIS PerfMon counters, except that they are actually in the \Program Files\ directory.

How do I fix this?  Its easy, but requires a reboot.  Here are the instructions, straight from the KB linked above:

To work around this problem, follow these steps:

1. Click Start, click Run, type regedit, and then click OK.

2. In Registry Editor, locate the following registry entry:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\DTSPipeline\Performance\Library

3.Double-click the Library registry entry.

4.In the Edit String dialog box, type the following string value in the Value data box:

DriveLetter:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSPipelinePerf.dll

5.Restart the computer.

UPDATE: This fix does not work as described in the KB

Even though there is a DTSPipelinePerf.dll file at the path given in KB 941154, simply pointing to it in the registry setting and rebooting does not make it visible.  I was able to get it to work using Plan B: change the path back to \Program Files (x86)\ and use the 32-bit version of PerfMon.

There are two ways to use the 32-bit version of PerfMon:

1.) Start | Run | mmc.exe /32 perfmon.msc

2.) Execute c:\windows\sysWow64\Perfmon.exe

Programmatically executing SSIS packages

For a good summary of many different ways to run an SSIS package programmatically, as well as advantages and drawbacks of each, look here at Michael Entin's blog.  There are some good things in the comments as well.  This will come in handy at some point, I am sure.

Upgrading Existing SSIS Packages to TableDifference Component 2.0

I am an avid user of Alberto Ferrari's SQL Server Integration Services "Table Diff" component, which you can download from http://www.sqlbi.eu.  He recently released the new, improved version 2.0 of the component (the .dll still says that it is 1.0 though?) and I am upgrading a mid-size solution that makes heavy use of the prior version of TableDiff (1.x) to use the new component.  Here are my observations of both the upgrade and the new capabilities of the component.

Here is a typical Type 1 style update package (in this case an import table, not a dimension) that uses TableDifference at its core to compare the new and old data flows and direct output accordingly.  Behind the scenes, I overwrote the TableDifference.dll file with the new one in both the .NET Assembly Cache and the \PipelineComponents directory.  When I opened the package, everything is working except the Update and Insert components have red X's.  These are easily fixed by simply double-clicking on each to open properties, accepting the new automatically matched column references and saving the package.  That's it, there is nothing else required to update your pacakages from TableDifference 1.x to the newly released 2.0.

Team Foundation Server 2008 RTM and 64-bit

Bad news - Microsoft is pushing 64-bit (as they should), but one of their newest enterprise products does not yet support it.  Team Foundation Server 2008 only supports 32-bit.  They have said that 64-bit support will be in the next version of TFS, "Rosario".

At least they support 64-bit SQL and SSAS, but definitely not MOSS 64-bit.  We have deployed 64-bit MOSS 2007 and it turns out that it doesn't even support 64-bit SharePoint when SharePoint is running on a different server from TFS, it doesn't even recognize that MOSS is installed:

SQL Server Default Ports

It seems like it is always hard to track down the ports that SQL Server uses, so thought that I would post them here for when I need them.  These are the SQL Server default ports.

SQL Component	TCP Port
SQL Service	1433
SQL Browser Service	1434
SQL Monitoring	1444
SQL Server Analysis Services Redirector	2382
SQL Server Analysis Service	2383
SQL Server Reporting Services	80

New set of SSIS Tasks

These may have been around for a while, but I just discovered that there is a nice set of (affordable) SSIS tasks at CozyRoc.  There is a task for zipping files, managing partitions, SFTP and even using another dataflow as a source.  Here is the current info from their site:

 

Component
Key Features

Beta! Package SSIS Connection Manager

• Gain access to the current or different package at runtime.
• Support for all current types of package storage: SQL, File, SSIS Package Store.
• Support for package variables initialization before execution.
• Easy to use interface.

Beta! Data Flow Source SSIS Data Flow Component

• Based on Package Connection Manager.
• Very fast, non-blocking, in-memory data read from another data flow.
• Scalable alternative of the solution Microsoft is recommending with raw data file source.
• User interface is based on the standard data flow source component dialog.

Beta! Data Flow Destination SSIS Data Flow Component

• Exposes data from a data flow for in-memory read in another data flow.
• Scalable alternative of the solution Microsoft is recommending with raw data file destination.
• User interface is based on the standard data flow destination component dialog.

Database Partitions SSIS Control Flow Task

• Saves you time by handling the intricacies of the partitioning and letting you concentrate on your workflow instead.
• Create, insert and remove database partitions in Microsoft SQL Server 2005.
• Combine smaller partitions into a larger one, for easy backup later.
• Support for ADO.NET, OLEDB, ADO connection managers.
• Easy to use interface.

Script Task Plus SSIS Control Flow Task

• Extension of Microsoft SSIS Script Task, meaning you already have the skills to use it.
• Greater reusability and easier maintenance of script functionality.
• Customizable script user interface and setup.
• SSIS Script export, import or linking to.
• Keep your script code separate from your package and have easier time tracking changes in your source control system.
• Requires SQL Server 2005 SP2.
• New! Share your exported SSIS scripts with the community.

Zip SSIS Control Flow Task

• Compression and decompression of Zip, GZip, BZip2, Unix, Tar archives.
• Automatically switches to Zip64 format, if it is required.
• Streamlined user interface.

SSH SSIS Connection Manager

• Secure connections with SSH-enabled server.
• Authenticate with password or private key file.
• Support for Tunnel (HTTP), SOCKS4, SOCKS5 proxy.
• Streamlined user interface.

SFTP SSIS Control Flow Task

• Based on SSH Connection Manager.
• Secure-FTP (SFTP) communications and management.
• Support for SFTP commands send, receive and delete files, create and remove directory, rename file.
• The user interface covers most of the functionality of the standard Microsoft SSIS FTP task and it is very similar.

SSH Execute SSIS Control Flow Task

• Based on SSH Connection Manager.
• Secure execution of shell commands on a remote SSH server.
• Streamlined user interface.

More Interesting SSIS Components

I knew about the Konesans Trash Destination, but it turns out that they have a whole series of free, publicly available SSIS components.  The main product page is here, and below I have pasted in content for each of the transforms, with the link to the download page:

Checksum Transformation

 The Checksum Transformation computes a hash value, the checksum, across one or more columns, returning the result in the Checksum output column. The transformation provides functionality similar to the T-SQL CHECKSUM function, but is encapsulated within SQL Server Integration Services, for use within the pipeline without code or a SQL Server connection. Checksums can reduce network contention and increase process performance by allowing you to verify data through a single value rather than transferring all data values for comparison.

Data Generator Source

This source component literally generates data. Specify how many columns you want, and how many rows, then watch the data flow out. Build demonstration and research scenarios faster with this simple source.

File Watcher Task

The File Watcher Task does what it says really, it watches a folder waiting for files. When an available file is found the task completes, returning the name of the file for onward use within the package.

Regular Expression Transformation

The Regular Expression Transformation exposes the power of regular expression matching within the pipeline. One or more columns can be selected, and for each column an individual expression can be applied. If all columns selected pass their tests then rows are passed down the successful match output. Rows that fail to pass all tests are directed down the alternate output.

Row Count Plus Transformation

The Row Count Plus Transformation can replace the stock transformation. We have recreated and extended with more functionality and a user friendly interface for faster and easier package design.

Row Number Transformation

The Row Number Transformation calculates a row number for each row. It offers ROW_NUMBER or IDENTITY like behaviour within the Data Flow. Uses include surrogate key generation or data partitioning within the pipeline.

Trash Destination

The Trash Destination Adapter is a development aid. It allows you to quickly terminate a data flow path, and does not require any configuration. It will consume the rows without any side effects, and prevents warnings or errors you may otherwise receive when executing the data flow.

Additional SSIS Transforms that didn't ship with the product

There are a whole series of SSIS transforms that Microsoft has made available for download.  They have differing degrees of usefulness, and have been available for a while but I am putting them out here for future reference.  Off the top of my head, it looks like Regex would be useful, UnPackDecimal if you are deal with mainframe files, calendar transform (as I recall it has a dependency on a DLL from SSAS though).

 

Regex Transform
Regex is an SSIS dataflow component that applies a configured regular expression against an incoming column, matching, extracting, or splitting, as configured by the user.

ConfigureUnDouble Transform
ConfigureUnDouble takes a text column and removes bracketing quotes if present, plus replaces double quotes inside the text with sinqle quotes. Configure the quote character by, on the first tab of the advanced editor, setting the "Qualifier" custom property to the desired value.

RTrimPlus Transform
RTrimPlus takes a string or unicode column and removes trailing spaces, whether ASCII or Japanese.

UnpackDecimal Transform
UnpackDecimal takes an input column formatted in packed decimal (comp-3) and generates the corresponding Decimal value.

UnDoubleOut Transform
UnDoubleOut is an SSIS dataflow component that removes qualifiers from quoted text, either in place or via the creation of a new output column.

CodePageConvert Transform
CodePageConvert is an SSIS dataflow component that translates from and to any code page or unicode character representations.

SeeBuffer Transform
SeeBuffer is an SSIS dataflow component that sits in a data flow and is provided a look at each buffer that is presented to it.

UnDouble Transform
UnDouble takes a text column and removes bracketing quotes if present, plus replaces double quotes inside the text with sinqle quotes.

NullDetector Transform
NullDetector is an SSIS dataflow component that sits astride a data flow, and, depending on whether the value of a user-indicated column is null or not, routes rows to one or the other of its outputs.

Calendar Transform
CalendarTransform is an SSIS dataflow transform component that generates standard calendar attributes

Microsoft Performance Point Server 2007 finally RTM's

After years of hard work and a ProClarity acquisition later, Performance Point Server 2007 has finally been released.  More details here on the product home page.  Here are links to trial downloads and SDK.

PerformancePoint Server 2007 Evaluation Version (x86)

PerformancePoint Server 2007 Evaluation Version (x64)

Deployment Guide, Operations Guide, Whitepapers

Planning Server, Business rules development guide and Monitoring SDK

Slick New MDX Editor/Debugger

This is a very nice looking tool for anyone that works with MDX.  It is currently in "CTP 1, version 0.1 Alpha" release, which is another way to say "Very early in development cycle".  It works with SSAS in all of its various versions from 7.0 to 2008.  Details and download here.

Documenting a SQL Server 2005 Solution end to end

I had heard of this tool before, and it looks interesting if you ever need to document a full SQL Server 2005 solution.  It apparently documents SSIS, SQL and SSAS as well as SSRS.  The output shows data lineage, as well as the structure of your database.  Worth a look, pretty affordable too.  Too bad they couldn't make a version that worked as an add-on to Data Dude.  Here is the link to their homepage.

Load Testing SQL Server Analysis Services 2005

Have you ever had a need to load test SSAS and then report on the results?  Your life just got easier.  There is now a project on CodePlex for that very purpose.  Apparently MCS and other large internal projects have used it for a while, but it is now publicly available as a community project.  Download it here.

Taking the pulse of SQL Server

If you have ever been involved in a big SQL Server project, you might have wanted some easy way to figure out how things are performing on the server.  Fortunately, many other people have wanted to do the same.  There are several ways available now (and more to come in SQL 08, specifically for SSAS I believe).  You can use the SQL Server Health and History Tool (SQL H2) which tells you all kinds of interesting things about the internals of SQL and run reports against it.

The other new and cool thing that has come out is an actual data mart that is built on top of the man Dynamic Management Views (DMV) inside SQL Server.  The DMV's are what drive the reports inside SSMS, and you can use them to find out all kinds of things.  Now you no longer have to run them manually.  Thanks to the SQL Server Customer Advisory Best Practices Team, now you can just install SQL DMVStats Data Warehouse on your server and your good to go.

Here are the main features as listed on CodePlex:

• DMV data collection
• DMV data warehouse repository
• Analysis and reporting.

VSTE for DB Pros (Data Dude) Power Tools released

If you are using VSTE for DB Pros, you will probably want to pick up the newly released Power Tools (didn't they used to call these sorts of things "Power Toys"?).  It adds a lot to the product in the way of convenience with the dependency viewer (you don't have to pretend to rename something to see dependencies now), additional refactoring abilities, enhanced data generation and T-SQL code analysis among other things.  Download it here.

Read more about it at Gert Drapers' blog, including detailed explanations and screen shots.

Here's the list of new features, copied from Gert's blog:

• Dependency Viewer
• Refactoring
  • Move Schema
  • Expand Wildcard
  • Fully Quality Name
  • Refactor in to strongly typed DataSet definitions
  • Refactor Command Generator
• Data Generation
  • Sequential Data Bound Generator
  • Editors for the Data Bound Generator, Sequential Data Bound Generator and RegEx String Generator to make configuration easier
  • The RegEx editor also tries to interpret your CHECK CONSTRAINTs and create a matching RegEx expression that you can use to generate data values that match the constraint definition
  • The RegEx editor can also be used for interactively defining and testing RegEx expressions and evaluate the output visually, which makes it a lot easier to create the right RegEx expression for your value domain.
• MSBuild Tasks
  • SqlSchemaCompareTask; allows you to compare schemas between two database from the command line using MSBuild.
  • SqlDataCompareTask; allows you to compare the content of tables within two databases from the command line using MSBuild.
• T-SQL Static Code Analysis
•  Miscellaneous tools
  • SQL script pre-processor command-line utility, which will expand all SQLCMD includes and variable definitions (sqlspp.exe)
• Schema Manager API

SR1 for Visual Studio 2005 Team Edition for Database Professionals (DataDude)

This news is about a month old, but the RTM version of SR1 for VSTE for DB Pros has been been released and is available for download.  One of the most important things that it adds is the ability to have "database references", which is a 3 or 4 part reference to a database other than the one that you are currently working with.  Much-needed functionality.

SSIS Pivot Transform is harder than it needs to be

Don't get me wrong - I am glad that they included the pivot transform, and I recognize that the dev team likely ran out of time to implement any sort of UI, but Wow!  That thing is way more difficult to use than it needs to be.

For instance, and I don't doubt that it is somewhat user error on my part, but in my first attempt to get it working today with 3 columns (UserID, AttributeName, AttributeValue), the transform coughed, choked and wouldn't start.  It just gave me errors.  Due to lack of time, I had to set it aside for now.  I appreciate its usefulness, but it is painful to use, and due to the limited info on the Internet about it (there are roughly 2 useful articles/blog posts), I am guessing that a lot of others just work around it as well.  A shame, since the Unpivot transform has a UI and everything.

In summary - I am not saying it is broken, I am not saying it is not useful.  Just that it not only is difficult to use, but that documentation and samples are lacking as well.  Here are the few useful posts I found:

Ashvini Sharma's blog

MSDN

Database Journal

UPDATE: My friend Matt pointed me to a blog entry that he wrote while at our old company that is a still up in which he gives some tips on the pivot transform.

Interesting AJAX enabled SSAS client

I was checking my webmail today, and one of the ad-links caught my eye.  It was for an AJAX based SSAS client.  I clicked the link and it took me to IntraSight, by a company called Active Interface.  Screenshots are available at that link.  There are a lot of interesting SSAS web based interfaces floating around out there if you look hard enough.  Add this one to the list.  SharePoint integration is listed as one of the bullet points.  Pricing is pretty affordable: $1995/server.  It might be worth a look if you need a web-based OLAP tool.

Here is their feature list:

• IE browser-based SSAS client
• High-fidelity AJAX user interaction
• Interactive table and chart views
• Annotated public and private bookmarks
• View and edit generated MDX
• Sharepoint integration
• Excel, Word, and PowerPoint generation
• Publish static HTML site from bookmarks
• Web tier caching
• Search dimensions
• Filter member sets
• Sort and filter measures
• Specialized business-centric and industry-specific views