Protocol error in TDS stream

Yesterday I struggled with the following issue in SSIS: I specified a stored proc as the SQL command text in an OLE DB Source in a data flow. When I clicked the Preview button the data I expected was returned. The Columns were also picked up successfully in the source editor.

But everytime I ran the package it failed with the following errors:

SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Protocol error in TDS stream"...

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1135) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure...

A component has returned from its PrimeOutput call. : 1135 : OLE DB Source
SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited...

SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled...

SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited...

After searching the whole day for a solution I finally stumbled accross this post by Adam Machanic. At first sight it did not fit my problem, but out of desperation I decided to give it a bash; and it worked.

I am not going to repeat what he said in his post because he did a brilliant job of it. But in short: tables, views and table-valued UDFs have explicitly defined output columns; and stored procs don't. So SSIS will have guaranteed success in retrieving the correct output columns from tables, views and table-valued UDFs, while a stored proc can return different resultsets based on the input parameters.

As I said before, this does not fit my problem since SSIS did pick up the correct output columns, but I suspect that it missed some crutial metadata which wasn't available in the stored procedure.

LINQ to SQL - Implementing the Repository pattern

I've been playing with LINQ to SQL for the past month. It really impresses me with its ease of use.

But I am getting some strange errors where EntitySets don't want to populate no matter what. I can do whatever I like with the relationships and the loading options, it does not want to work. It appears to be objects which wrap views in the database.

Then I read something about how tightly coupled objects can be bad in that they make unit testing a nightmare by complicating the data access layer.

So, I thought of using LINQ to SQL in a loosely coupled fashion. It will still increase the speed at which I develop my data access layer. And then I found someone who had a similar idea. Here it is: LINQ to SQL - Implementing the Repository pattern

Get all the dates in a range

Use this T-SQL script to get all the dates in a specified date range.

Please note that it will only work in SQL Server 2005 or later.

SQL Server 2005 Performance Dashboard Reports

Last week our dba resolved serious SQL Server performance issues very effectively using SQL Server Performance Dashboard Reports.
Our server's four dual core processors were averaging 99.73% for a couple of days. After monitoring the situation with SQL Server 2005 Performance Dashboard Reports and implementing the suggested performance enhancements, our server's CPUs are now working at between 12% and 60%.

More about Microsoft SQL Server 2005 Performance Dashboard Reports:

"The Microsoft SQL Server 2005 Performance Dashboard Reports are used to monitor and resolve performance problems on your SQL Server 2005 database server."

"The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server."

Download it here.

Update all index statistics after upgrading a db from SQL Server 2000 to 2005

After upgrading a database from SQL Server 2000 to SQL Server 2005, our application's performance degraded significantly. It was eventually resolved by creating two new indexes in the database.

However, I could not understand how database performance could be impacted negatively by an upgrade. All the indexes which were in the SQL 2000 db exist in the upgraded database.

After googling the topic for a while I came across the following article: Interview with Greg Linwood. Below is an important tip from the article.

Most upgrades I have been involved with have been fairly smooth but some degree of tuning has been required in most cases. One tip worth remembering is to update all index statistics after an upgrade (or simply rebuild all indexes) so that SQL Server’s cost optimizer has the most recent information possible by which to make the best query execution decisions. SQL Server 2005’s cost optimizer is significantly more sophisticated and will generally make the same or better decisions in most cases than it’s predecessor. These improved decisions rarely get noticed (as no-one complains when things improve!) but those which degrade performance sometimes cause the upgrade process to come under un-warranted criticism. Hence, I recommend closely monitoring performance during and after upgrades, just in case the query optimizer makes decisions which “appear” better to it but end up not working out.

My Development Motto

SET FMTONLY

When you want to retrieve the metadata for a table, view, stored procedure, etc without having any records returned, use SET FMTONLY ON. Only the column data will be returned.

Multiple SSIS instances on the same machine

You can only have one instance of SSIS (SQL Server Integration Services) installed on a machine at a time.

But you can have multiple SSIS database instances on one machine at the same time. See this post on how to do that.

Get the SQL Server Instance name from T-SQL

To get the SQL Server instance name with T-SQL use one of the following two statements:

• select @@SERVERNAME
• select SERVERPROPERTY('SERVERNAME')

Important: The SERVERNAME property of the SERVERPROPERTY function uses the computer's current network name. So, if the computer (on which the SQL Server instance is installed) had its network name changed after the SQL Server instance was installed, the SERVERPROPERTY function will pick up the new name - @@SERVERNAME will not! @@SERVERNAME will use the network name of the computer at the time when the instance of SQL Server was installed.

Click here to see more properties of the SERVERPROPERTY function.

Get the names of all tables used in a SQL Server view

I want to be able to quickly identify the tables which make up a SQL Server view. I know I can just open the script and look at it, but I have the following problems:

1. All the scripts are one liners for some strange reason.
2. And some of the views contain large numbers of tables in the joins.

So I came up with this script. It will work in SQL Server 2000 and 2005.

Take note: This script is by no means foolproof, and I haven't tested it very extensively. I am even ignoring the tables' schemas altogether. And if a view and a table would have the same name my script wouldn't know the difference.
But hey, it returns the information I want, so I am happy.

I didn't use the system view INFORMATION_SCHEMA.VIEWS because it truncates a view's T-SQL script in the VIEW_DEFINITION column when the script is too long.

I rather used syscomments because it spreads a view's script over several records when the script is too long for the text column. This enabled me to search the whole script for tables names.
I am not sure if syscomments will split a script in the middle of a word when the script is too long for the text column. If that would be the case I could miss some table names because they can be split.
(syscomments is a system view in the sys schema in SQL Server 2005. In SQL Server 7.0 and 2000 it is a system table in the dbo schema.)

Undocumented Stored Procedures

Apparently there are extended stored procedures which are not documented in the Books Online. In this post I want to create a list of those stored procs with links to articles about how to use them. So far I have only one proc, but I will update this post as soon as I come across more.

• xp_ReadErrorLog (SQL Server 2005)

I don't think this one is an extended proc, but also undocumented:

• EXEC sp_MSForEachTable 'EXEC sp_spaceused [?]' - It allows one to execute a T-SQL statement against each table in a database.

KILLERSITES.COM

Most web developers (including myself) are guilty of abusing HTML to some extent. With abuse I mean using the wrong tools (tags) for the job.

A common "mistake" is to use tables to format the layout of your web page.

KILLERSITES.COM teaches you how to build web pages using the correct tags in the correct places.

Enjoy!

Best Practices for Troubleshooting Slow Running Queries

A colleague forwarded this article to me. Although the article contains very useful and valid tips, he pointed out that it does not discuss poorly written T-SQL as a possible cause for slow running queries.

Poorly written T-SQL is probably such a huge topic that it deserves an article of its own.

Anyhow, I hope you can use some of the troubleshooting tips.

A Software Developer Explaining His Job to an Engineer

I found this while reading a thread in The Code Project Lounge. I think it's brilliant!

A software developer explaining his job to an engineer:

Imagine that you are hired to build a bridge over a river. The width of the river increases slightly every day, except when it shrinks. Your budget does not allow for you to use concrete or steel - you can only afford timber and cut stone. Gravity changes from hour to hour, as does the viscosity of air. Your only tools are a hacksaw, a chainsaw, a rubber mallet, and a length of rope.

Welcome to my world.

SQL Server 2005 ROW_NUMBER() function

Here's an example of how to use the row_number() function which became available in SQL Server 2005.

Click here for a detailed description of the syntax.
Click here for a Google search on this topic.

Run this script against the AdventureWorks sample database in SQL Server 2005. See how the row_number() function numbers the line items per sales order:

select
SalesOrderNumber,
LineItemNo = row_number() over(partition by SalesOrderNumber order by SalesOrderDetailID),
Product = p.Name,
OrderQty,
UnitPrice,
UnitPriceDiscount,
LineTotal
from
Sales.SalesOrderHeader h
inner join
Sales.SalesOrderDetail d
on
d.SalesOrderID = h.SalesOrderID
inner join
Production.Product p
on
p.ProductID = d.ProductID

Using LINQ to SQL

Scott Guthrie posted a series of excellent articles about LINQ to SQL on his blog. Here they are:

• Part 1: Introduction to LINQ to SQL
• Part 2: Defining our Data Model Classes
• Part 3: Querying our Database
• Part 4: Updating our Database
• Part 5: Binding UI using the ASP:LinqDataSource Control
• Part 6: Retrieving Data Using Stored Procedures
• Part 7: Updating our Database using Stored Procedures
• Part 8: Executing Custom SQL Expressions
• Part 9: Using a Custom LINQ Expression with the <asp:linqdatasource> control

Choosing Between SQL Server Compact 3.5 and SQL Server 2005 Express Edition

SQL Server Compact 3.5 and SQL Server 2005 Express Edition are basically the same thing - or are they?!

See the following article and white paper from Microsoft which will guide you when having to choose between these two editions of SQL Server.

Download SQL Server Compact 3.5
Download SQL Server 2005 Express Edition

Articles about .NET Exception Handling

This morning I added two Code Project articles (which have helped me a lot so far) to my blog:

• Exception Handling Best Practices in .NET
• Error Handling in ASP.NET

You will find the links to these articles under the .NET Framework and ASP.NET link lists respectively.

IIS Authentication Explained

Here is a link to an article which explains How IIS authenticates browser clients.

It gives an overview of the different authentication methods, as well as the sequence in which these methods get processed when more than one are selected.

Visual Studio 2008 DVD Freezes PC

I've been spending the past day trying to install Visual Studio 2008 on my work pc (with Windows XP SP 2). Everytime I put in the VS2008 DVD, Windows Explorer freezes up and I cannot kill the process through the Task Manager. Eventually the whole OS freezes and I have to do a hard reboot to get it going again.

I uninstalled and reinstalled the drivers for the DVD writer - it did not work.

Technical support came and installed a new DVD writer in my pc. We tested it with a DVD (not the VS2008 one) and it worked fine. After the guy left I put in the VS2008 DVD and I had the same trouble over again.

So, I found this thread on a forum and it had the solution to my problem. In short:

1. Right-click on the DVD drive
2. Select Properties
3. Goto the DLA tab
4. Uncheck "Enable DLA on your drives"
   

I have Sonic installed on my pc and with that you get the DLA (Drive Letter Access) feature.

When I disabled it, my troubles were over.

Change a T-SQL variable in an 'update' statement

declare @message varchar(1024)
set @message = 'New message'

update MyTable
set Column1 = 'New Value',
Column2 = @message,
@message = 'Message updated successfully'
where [Some condition]

select @message

If one or more records have been updated with the update statement, the @message variable will contain "Message updated successfully".
If no record was updated the variable will contain the original text "New message".