Problem with Agent on 2000 server - jobs hanging

I have a server that had SQL agent working on it, and running scheduled
jobs on set interval forever. Windows Server 2003, SQL 2000, most
current service packs on both.
I installed an instance of SQL 2005 on this server.
Now, when I look at Enterprise Manager & try to see what's happening
with the jobs, I see that the jobs are sitting out there & will kick off
when they are supposed to run, or I can kick them off manually.
However, now, they will run to certain steps in a job, and it will just
"hang". The jobs that hang are the ones that are running DTS jobs:
DTSRun /~Z0xC9C........
These particular jobs haven't been changed in months, and were running
fine until the install of SQL 2005 on this server.
Any idea what's happening or how to fix it?
It may be a locking. You can really easy to detect in Current Activity
window, processes view, last two fields if you scroll to the right, 'blocked
by" and "blocking" columns.
"Blasting Cap" wrote:

> I have a server that had SQL agent working on it, and running scheduled
> jobs on set interval forever. Windows Server 2003, SQL 2000, most
> current service packs on both.
> I installed an instance of SQL 2005 on this server.
> Now, when I look at Enterprise Manager & try to see what's happening
> with the jobs, I see that the jobs are sitting out there & will kick off
> when they are supposed to run, or I can kick them off manually.
> However, now, they will run to certain steps in a job, and it will just
> "hang". The jobs that hang are the ones that are running DTS jobs:
> DTSRun /~Z0xC9C........
>
> These particular jobs haven't been changed in months, and were running
> fine until the install of SQL 2005 on this server.
> Any idea what's happening or how to fix it?
>
sql

Problem with Agent on 2000 server - jobs hanging

I have a server that had SQL agent working on it, and running scheduled
jobs on set interval forever. Windows Server 2003, SQL 2000, most
current service packs on both.
I installed an instance of SQL 2005 on this server.
Now, when I look at Enterprise Manager & try to see what's happening
with the jobs, I see that the jobs are sitting out there & will kick off
when they are supposed to run, or I can kick them off manually.
However, now, they will run to certain steps in a job, and it will just
"hang". The jobs that hang are the ones that are running DTS jobs:
DTSRun /~Z0xC9C........
These particular jobs haven't been changed in months, and were running
fine until the install of SQL 2005 on this server.
Any idea what's happening or how to fix it?It may be a locking. You can really easy to detect in Current Activity
window, processes view, last two fields if you scroll to the right, 'blocked
by" and "blocking" columns.
"Blasting Cap" wrote:
> I have a server that had SQL agent working on it, and running scheduled
> jobs on set interval forever. Windows Server 2003, SQL 2000, most
> current service packs on both.
> I installed an instance of SQL 2005 on this server.
> Now, when I look at Enterprise Manager & try to see what's happening
> with the jobs, I see that the jobs are sitting out there & will kick off
> when they are supposed to run, or I can kick them off manually.
> However, now, they will run to certain steps in a job, and it will just
> "hang". The jobs that hang are the ones that are running DTS jobs:
> DTSRun /~Z0xC9C........
>
> These particular jobs haven't been changed in months, and were running
> fine until the install of SQL 2005 on this server.
> Any idea what's happening or how to fix it?
>

Problem with Agent on 2000 server - jobs hanging

I have a server that had SQL agent working on it, and running scheduled
jobs on set interval forever. Windows Server 2003, SQL 2000, most
current service packs on both.
I installed an instance of SQL 2005 on this server.
Now, when I look at Enterprise Manager & try to see what's happening
with the jobs, I see that the jobs are sitting out there & will kick off
when they are supposed to run, or I can kick them off manually.
However, now, they will run to certain steps in a job, and it will just
"hang". The jobs that hang are the ones that are running DTS jobs:
DTSRun /~Z0xC9C........
These particular jobs haven't been changed in months, and were running
fine until the install of SQL 2005 on this server.
Any idea what's happening or how to fix it?It may be a locking. You can really easy to detect in Current Activity
window, processes view, last two fields if you scroll to the right, 'blocked
by" and "blocking" columns.
"Blasting Cap" wrote:

> I have a server that had SQL agent working on it, and running scheduled
> jobs on set interval forever. Windows Server 2003, SQL 2000, most
> current service packs on both.
> I installed an instance of SQL 2005 on this server.
> Now, when I look at Enterprise Manager & try to see what's happening
> with the jobs, I see that the jobs are sitting out there & will kick off
> when they are supposed to run, or I can kick them off manually.
> However, now, they will run to certain steps in a job, and it will just
> "hang". The jobs that hang are the ones that are running DTS jobs:
> DTSRun /~Z0xC9C........
>
> These particular jobs haven't been changed in months, and were running
> fine until the install of SQL 2005 on this server.
> Any idea what's happening or how to fix it?
>

Problem when Connection is not available

Hello!

I have problem when opening a package or running it through SQL Server Agent and the Server in the Connection is not available. (I have tried with (M) SSIS > Work Offline, it helps opening it, but not running it).

The package has a Foreach Loop Container that loop through several Companies. Each Company have different Connection.

It exist one ODBC and one OLEDB connection. They have the Expression “ConnectionString” set to an variable (@.[User::varODBCConnectionString]/ @.[User::varOLEDBConnectionString]).

The Foreach Loop Container property ForceExectionResult is set to Completion. This results in that if one company is not available when trying to access it, the loop will continue with the next Company.

But the variables have a value from the beginning. If the server in the variables is not available, it is not possible to open the package or execute it through SQL Server Agent.

I tried to set the Property DelayValidation to True on the Connections, but this did not help.

Dose anyone have any ide how get around this problem?

Best regards,

Tina

Hi Tina,

You need to set DelayValidation=TRUE on the tasks that use those connection managers, not on the connection managers themselves.

Regards

Jamie

|||

Hello Jamie!

Thank you very much, it works now, bouth SQL Server Agent and in SSIS. (I also need to set Delay Validation = TRUE for att least the ODBC connection.).

Best regards,

Tina