This is one I used to use a lot, and had cause to use it this morning.  An ETL process to truncate a staging table was being blocked by another process on the server, and I needed to identify exactly what command the blocking process was attempting to execute.  I found the SPID by executing sp_who2, which showed me the SPID that was blocking the truncate command (in this case SPID 54).  I then executed the following command to find out exactly what command was causing the block to happen:

DBCC inputbuffer(54)

The result set returned shows the actual T-SQL that was last executed for that SPID, which was a very large insert from a remote PostgreSQL database. 

This is a quick and useful method for tracking down issues and investigating what is happening on your server.

I've had a couple of issues recently with using the tinyint datatype in SSIS.  In one case I have a Lookup Transform which joins to the incoming data flow path on a column defined as a tinyint in the database.  The column metadata in the data flow is DT_I4, a four-byte signed integer.  Dragging this across to the tinyint column in the Lookup gives me the following error:

The following columns cannot be mapped:
[DataFlowColumn, LookupColumn]
One or more columns do not have supported data types, or their data types do not match.

By process of elimination, I discovered the incoming column needs to be converted to a DT_UI1, a single-byte unsigned integer.  One to bear in mind for the future!

I travelled down to Manchester last weekend to go to the SQLBits conference, something that I'd heard a lot of good things about.  I'm very glad I went, as it was an extremely useful and informative event.  And free!  :)

With 28 sessions being given by 28 speakers, there was plenty of choice on what to go to...possibly too much as I missed out on a few that I would have really liked to have seen.  I decided to mainly stay on the Business Intelligence track, and went to the following:

• Data Warehouse Data Modelling Vincent Rainardi
• Instrumenting, Monitoring and Auditing of SSIS ETL Solutions Davide Mauri
• SSIS Nuggets Live Jamie Thomson
• Data Warehousing features in SQL 2008 James Rowland-Jones
• Designing Effective Aggregations in Analysis Services 2008 Chris Webb
• Data Mining and SSIS - makes sense Allan Mitchell

All of which were very well presented and pitched at just the right level.  Personally I found Davide Mauri's session the most interesting as I've been looking at instrumentation and auditing of SSIS packages a lot recently, and his innovative DTLoggedExec tool (which can be downloaded here) is something I'll be looking at implementing very soon.

I'll certainly be back next year, as I'm sure there will be a SQLBits V.  If you are a SQL Server professional then get yourself along, it is well worth it.

Following up from yesterday's post about SDS, there is now a Q&A about the announced changes on the SDS Team Blog:

SDS Q&A

Now this is a bit interesting!  On Tuesday, the SQL Data Services team announced that SDS would offer full relational database capabilities.  This has really perked my interest now, as I was never fully comfortable with the ACE (Authority, Container, Entity) data model.  Now that SDS is going to support Stored Procedures, Views, Indexes and even Triggers, I think this is a product to get excited about.

Of course, the announcement means I still have more questions than answers!  For starters; when will we get to start playing with the product?  How is it licenced?  Will it work out the box with SSMS?  How will it interact with SSIS and SSRS?

Read the announcement here: SDS Team Blog

This is a new one on me...when running SSIS in a 64-bit environment, Excel connections (and Access as well I presume) do not work, as the OLE DB Provider for Jet is not supported.

At a current client, they are moving the server platform from Windows 2003 Server to Windows 2008 Server 64-bit.  I had developed some ETL packages for them, pulling data from Excel and CSV sources.  The packages get executed when the files have been unzipped and downloaded to a specified folder.  This is done via simple batch files, which are along the lines of the following:

dtexec /f "F:\SSIS Packages\Reporting\ImportClientCSV.dtsx"

When I moved all the packages to Windows 2008, the CSV packages ran no problem.  But when I tried to execute one that imported from Excel, I received the following error:

Code: 0xC00F9304
   Source: ImportClientXLS Connection manager "Excel Connection Manager"
   Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

In order to use the OLE DB for Jet providers, the packages must be run in 32-bit mode.  This is done by using the x86 version of dtexec and specifying the "/X86" switch.  I amended the batch file in question to look similar to this:

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /f "F:\SSIS Packages\Reporting\ImportClientXLS.dtsx" /X86

Voila!  The package ran successfully.

This link from Microsoft details the limitations when running SSIS in 64-bit environments: 64-bit Considerations for Integration Services

I found an interesting forum post over at SQLTeam this morning, and decided to have a go at coming up with a solution.

Filters are quite commonly used in Tablix reports to show only the top n rows from a dataset.  This is easily done by using the Top N operator in the Filters tab of the Tablix, and choosing the column in the Expression drop down.

In the forum post however, the original poster was wanting to show the top n values in a chart.  The chart would by default sum up the values in the data field.  The trick would be to get the top n aggregated values, as opposed to taking the top n original values and then doing the aggregation.  To start with, I created the following very simple table and data.

create table EmployeeSales
(
EmployeeName varchar(20),
SalesAmount money
);
go

insert into EmployeeSales
(EmployeeName, SalesAmount)
values
('Jerry', 9.99),
('Jerry', 30.00),
('Jerry', 55.70),
('Jerry', 4.99),
('George', 3.99),
('George', 1.99),
('George', 1.99),
('George', 1.99),
('George', 15.00),
('Kramer', 17.00),
('Kramer', 152.00),
('Kramer', 99.99),
('Kramer', 75.00),
('Elaine', 9.99),
('Elaine', 15.99),
('Elaine', 3.99),
('Newman', 120.00),
('Newman', 5.99),
('Newman', 5.99),
('Newman', 0.99)
;

I then created a report in SSRS 2008 with a DataSet called Sales, which contained the following SQL query.

select EmployeeName, SalesAmount
from EmployeeSales

I dropped a Tablix onto the report, and set the source to be the Sales dataset, with two columns for the fields in the dataset.  At this point I also created two Parameters, @TopSalesN and @TopTotalSalesN, both integers with a default value of 5.  I then went to the Filters tab in the Tablix Properties and added a filter with the following values (see attached picture)...

Expression: [SalesAmount]
Operator: Top N
Value: [@TopSalesN]

Then in the Sorting tab, I sorted the data by [SalesAmount], order Z to A.

Running a preview confirmed this worked as expected and I could change the parameter to show as many values as I needed.  See attached picture.

Then I added a standard column chart next to the tablix.  I dropped the SalesAmount column on the data fields section, and EmployeeName onto the category fields section.  There is no filter option on the Series properties for the data, so I tried to add a filter on the Chart Properties.  I found two issues with this.  Firstly, if you try to use an aggregate value in the filter Expression then the report fails to process, as this is not allowed.  If you filter on the raw values, then obviously all you do is remove rows from the raw data and the results are not what you expect.

To produce the correct results, the filtering (and sorting, if required) needs to be added to the Category Group Properties on the Category Field.  I set it up as follows (see picture)...

Expression: [Sum(SalesAmount)]
Operator: Top N
Value: [@TopTotalSalesN]

This gave me exactly what I was looking for.  The attached image shows the preview of the report, with the top 10 rows from the raw dataset in the tablix, and the top 3 Sales by Employee in the chart.

The original poster also wanted the chart data to be sorted.  This is easily done by adding a sort on the Category Group Properties, sorting by [Sum(SalesAmount)] Z to A.  The final attached image shows how this looks.

I can see quite a few situations where using this method of having one "raw" dataset and more than one report item consuming the data in different ways would be very beneficial.  Particularly in cases where you have a data report and you need to show a summary chart relating to the data on the same report. 

Another method you can use for "Top N" reports is to pass a TopN parameter into a stored procedure, and use that value in the SQL statement.  For example...

create procedure [dbo].[TopSales]
@TopN tinyint = 20
as

set nocount on;

select top (@TopN)
    EmployeeName,
    SalesAmount
from EmployeeSales
order by SalesAmount desc

exec dbo.TopSales 10

Need to find out if a term is referenced in any stored procedures in your database?  Use the following T-SQL statement...

select name, definition
from sys.procedures p
    inner join sys.sql_modules m
        on p.object_id = m.object_id
where
    m.definition like '%search term%';

This will work in SQL Server 2005 and 2008.

SQL Server Management Studio 2008 has a new feature, the ability to run a script on multiple servers at once.  To set this up, the servers need to be registered to a Server Group in the Registered Servers window (see image).  Right-clicking on the Server Group and selecting New Query creates a query window which is connected to the servers in the group (as long as the connection information is correct and the passwords, if using SQL Server Authentication, are saved).  Running a simple query on the master database, in this case:

select top 5 * from sys.columns

returns the top 5 rows from that table from each server.  See the results in the attached image.

The option to Merge Results is True by default.  In a case where the schemas are not identical, this would need to be set to False otherwise an error will be thrown.