Error "Unable to connect to SQL Server '(local)'. The step failed."

If you are using SQL Server 2000 and you have a job with a step that fails with the following error message:

Unable to connect to SQL Server '(local)'. The step failed.

it may mean that this step is configured to use a database that existed when the step was created but has later been dropped.

Moreover, if you don't remember what database this step should use, checking the step definition in Enterprise Manager won't reveal the cause of the problem, because there will be some database selected in the Database combo box (it cannot be empty) but it won't be the same database that the step is really configured to use (this database does not exist so it is not in the combo box).

To fix the problem select a new database for each failing step and save the changes.

Disabling or enabling multiple jobs with a single click

If you navigate to <SQL Server instance>\SQL Server Agent\Jobs in SQL Server Management Studio and then right-click on some job, you'll see a context menu with Enable and Disable commands.

However, when you select more than one job and then right-click on the selection, a different menu shows and, of course [sic], it does not contain these commands.



Luckily there is another way to do it - you can use Job Activity Monitor which uses the same context menu for a single job as well as multiple jobs.

A job step seems to hang when executing a console application

An application executed in an Operating System (CmdExec) job step has to be a console application and it cannot ask for any user input.

Even a simple Press any key to continue..., used (when the application is executed by a user, not SQL Server Agent) to prevent the command prompt window from closing before the user can see the results, will keep such a job running forever.

Calling an SSIS package from a SQL Server Agent job step

If possible, always use Operating system (CmdExec) SQL Server Agent job step and DTExec.exe utility to call an SSIS package from a SQL Server Agent job step. The gives you much more verbose output information than SSIS Package step and makes troubleshooting much easier.

Just compare this:

Executed as user: DOMAIN1\sqlagent2005.
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 64-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 10:12:30 AM
Error: 2006-10-27 10:12:30.83
Code: 0xC0014060
Source: {9D739ECB-C57C-4A4D-9DFE-80183F7B7002}
Description: Failed to decrypt a package that is encrypted
with a user key. You may not be the user who encrypted this
package, or you are not using the same machine that was
used to save the package.
End Error
Could not load package "C:\Program Files\Microsoft SQL Server
\90\DTS\Packages\Package1\Package1.dtsx" because of error
0x8009000B.
Description: Failed to remove package protection with error
0x8009000B "Key not valid for use in specified state.". This
occurs in the CPackage::LoadFromXML method.
Source: {9D739ECB-C57C-4A4D-9DFE-80183F7B7002}
Started: 10:12:30 AM
Finished: 10:12:30 AM
Elapsed: 0.438 seconds.
Process Exit Code 5.
The step failed.

and this:

Executed as user: DOMAIN1\Administrator.
The package could not be loaded.
The step failed.

UPDATE:

Also, check the comment below for yet another reason that proves the point I am making here.

SQL Server Agent - multiserver administration (MSX/TSX configuration)

The sp_msx_enlist stored procedure adds the current server to the list of target servers available for multiserver operations. Only a SQL Server 2005 server can be enlisted against another SQL Server 2005 server.

That means that you cannot have a configuration with a SQL Server 2005 instance acting as a master server (MSX) and a SQL Server 2000 as a target server (TSX).

If you try to enlist a SQL Server 2000 instance into a SQL Server 2005 master server, you will receive an error like this:

The master server '<SRV_2005>' version 9.00.2047.00 is not compatible with the target server '<SRV_2000>' versions 8.00.2039.

(By the way, there is something wrong with this sentence, isn't it? I mean the word 'versions'.)

Is this job still executing?

It is easy to use Enterprise Manager to check whether a job is still executing or not.

But I needed to get this information programmatically.

This is a small stored procedure that tells whether a job is running or not.

CREATE PROCEDURE dbo.IsJobRunning
(
  @job_name varchar(255),
  @is_job_running int OUTPUT
)
AS
  BEGIN
    DECLARE @job_id uniqueidentifier 
    
    CREATE TABLE #xp_results 
    (
      job_id uniqueidentifier NOT NULL,
      last_run_date int NOT NULL,
      last_run_time int NOT NULL,
      next_run_date int NOT NULL,
      next_run_time int NOT NULL,
      next_run_schedule_id int NOT NULL,
      requested_to_run int NOT NULL, -- BOOL
      request_source int NOT NULL,
      request_source_id sysname COLLATE database_default NULL,
      running int NOT NULL, -- BOOL
      current_step int NOT NULL,
      current_retry_attempt int NOT NULL,
      job_state int NOT NULL
    )
    
    SELECT @job_id = sj.job_id 
    FROM msdb.dbo.sysjobs sj
    WHERE sj.name = @job_name
    
    INSERT INTO #xp_results
    EXEC master.dbo.xp_sqlagent_enum_jobs 1, ''

    SELECT @is_job_running = running
    FROM #xp_results
    WHERE job_id = @job_id
  END

After a few days of struggling I think this is the best way to do it.

DTS packages & jobs

I created a simple DTS package, I executed and it worked just fine.

Then I scheduled it and tried to execute the job. The job failed. It consisted only of one step which executed the package, so I was pretty speechless.

I opened job's history. It looked pretty awful (many lines of text without newline characters), but I managed to find a few occurrences of 'Access denied' phrase.

Then I remembered that there was something like 'Script file directory' textbox in 'Copy SQL Server Objects Task' window. I also realized that the domain account that I use to run SQL Server Agent service has very limited permissions.

I changed the path in 'Script file directory' textbox to one that SQL Server Agent could access and this time the job executed.