2006/06/27

Querying a view takes ages - really weird SQL Server 2005 behaviour

There is a view called VIEW_TITLES. It is quite complicated. Executing a SELECT query against it returns 189 rows.

A query:
  SELECT TOP 200 *
  FROM dbo.VIEW_TITLES
takes 13 seconds to execute.

However, executing an identical, when it comes to the returned rows, query:
  SELECT *
  FROM dbo.VIEW_TITLES
takes an indefinite amount of time - I cancelled the execution after 5 minutes.

I have no idea how to explain this behaviour. It looks like a bug in the optimizer to me.

The only workaround I found looks like this:
  SELECT TOP (SELECT COUNT(*) FROM dbo.VIEW_TITLES) *
  FROM dbo.VIEW_TITLES

I know it's a pretty dirty hack, but it works. And it is still better than hard-coding some fixed number in the TOP expression.

2006/06/21

Debugging a Windows service

OnPause, OnContinue and OnStop methods can be debugged by simply attaching to the [WindowsServiceName].exe process and setting breakpoints in the appropriate places.

The problem gets a little bit harder when it comes to debugging OnStart method, since there is no process that can be attached to, because the service is not running. There are many ways to solve this problem, but, in my opinion, the easiest one is to
call a System.Diagnostics.Debugger.Launch() method within the OnStart method in order to start a debugger programmatically.

A detailed description of this problem can be found here.

2006/06/08

How to format USB flash drive on Linux

You can use mkdosfs command.

For example:
  sudo mkdosfs /dev/sdb1

2006/06/05

SSIS package execution fails when you run it from a SQL Server Agent job

I created a SQL Server Integration Services (SSIS) package. It was pretty simple and it worked fine. After testing it, I created a SQL Server job with a single step that was supposed to execute the package.

And it took me two days to make it work. Unfortunately, documentation dealing with this topic is pretty poor.

I was getting different errors - for example:

Executed as user: SOME_DOMAIN\sqlagent2005. The package could not be found.
The step failed.

Unable to start execution of step 1 (reason: Could not get proxy data for
proxy_id = 2). The step failed.

This is a method I found (I am positive that there are other ways to get the same result, too).

1. Create a credential (make sure that the chosen account can execute jobs).

2. Create an SSIS proxy using this credential.

3. Set the proxy in the 'Run as' combo in a job step definition.

4. Grant access to C:\Program Files\Microsoft SQL Server\90\DTS\Packages (or wherever you deployed your SSIS package) to the domain account that you used to create the credential.

UPDATE:

Later I found a good article about this problem - An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step.