Endless running package using a proxy

I have a package which runs fine, when I execute it with my account (e.g. double-click on the .dtsx file and run it).

Now I would like to establish a job, which starts the package. I created first a credential for my Account (which is a domain administrator account also for the box, where SQL Server is running on), then I defined a proxy to this credential.

In the job definition I changed the Run as... to this Proxy (it is a SSIS Proxy) and then I started the job.

Th job does NOT abend, it runs forever! So I have to stop it manually.

In the log I can see as last entry : "operation complete". It stops at a "Execute process task" where I call the bcp utility.

Does anyone has an idea, why a package can run forever?!?!

Regards

Norbert

Sorry, it is not the package running forever (it logged "operation complete) but the job does not stop.

In addition I would like to add another information. I tested the package also as CmdExec Call (dtexec ....) but the result is still the same.

Regards

Norbert Bender

Enable Service Broker for DB Mail on SQL 2005 Cluster

I'm having problems enabling service broker for DB Mail on a SQL 2005 cluster, when I try to execute this sql it just hangs. Any ideas?

USE master ;
GO

ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
GO

Make sure there are no other people in the database before you run that command. It requires exclusive access to the database to change the mode.

|||

See here: http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

This statement completes imeadetly, but the problem is that is requires exclusive access to the database! Any connection that is using this database has a shared lock on it, even when idle, thus blocking the ALTER DATABASE from completing.

There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT

- ROLLBACK
will close all existing sessions, rolling back any pending transaction.
- NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.

HTH,
~ Remus

|||

This did help me get past this part. Now I'm receiving this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This got me to the next step. Now I'm getting this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This error means you have performed some operations on the msdb database without respecting proper procedures. Something like replacing the mdf and ldf files with MSDB file from another instance. Always follow the proper procedures to move/copy databases, always use attach/detach or backup/restore.

You cannot enable the existing broker in MSDB, you have to create a new one using ALTER DATABASE ... SET NEW_BROKER.

HTH,
~ Remus

|||This worked thanks. Do you think the the mismatched GUID will cause any other problems?|||

You shouldn't have any problem from now on. You have now created new GUIDs, and the one in the database matches the one in the sys.databases.

HTH,
~ Remus

|||Thanks Remus.|||

This as happened to me when using a disaster recovery procedure

- reinstall sql_engine with start/wait setup.exe ...

and restoring master from a previous backup

So it seems that when using "proper procedures". the guid is not recovered correctly.

Xavier

Enable Service Broker for DB Mail on SQL 2005 Cluster

I'm having problems enabling service broker for DB Mail on a SQL 2005 cluster, when I try to execute this sql it just hangs. Any ideas?

USE master ;
GO

ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
GO

Make sure there are no other people in the database before you run that command. It requires exclusive access to the database to change the mode.

|||

See here: http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

This statement completes imeadetly, but the problem is that is requires exclusive access to the database! Any connection that is using this database has a shared lock on it, even when idle, thus blocking the ALTER DATABASE from completing.

There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT

- ROLLBACK
will close all existing sessions, rolling back any pending transaction.
- NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.

HTH,
~ Remus

|||

This did help me get past this part. Now I'm receiving this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This got me to the next step. Now I'm getting this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This error means you have performed some operations on the msdb database without respecting proper procedures. Something like replacing the mdf and ldf files with MSDB file from another instance. Always follow the proper procedures to move/copy databases, always use attach/detach or backup/restore.

You cannot enable the existing broker in MSDB, you have to create a new one using ALTER DATABASE ... SET NEW_BROKER.

HTH,
~ Remus

|||This worked thanks. Do you think the the mismatched GUID will cause any other problems?|||

You shouldn't have any problem from now on. You have now created new GUIDs, and the one in the database matches the one in the sys.databases.

HTH,
~ Remus

|||Thanks Remus.|||

This as happened to me when using a disaster recovery procedure

- reinstall sql_engine with start/wait setup.exe ...

and restoring master from a previous backup

So it seems that when using "proper procedures". the guid is not recovered correctly.

Xavier

Enable Service Broker for DB Mail on SQL 2005 Cluster

I'm having problems enabling service broker for DB Mail on a SQL 2005 cluster, when I try to execute this sql it just hangs. Any ideas?

USE master ;
GO

ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
GO

Make sure there are no other people in the database before you run that command. It requires exclusive access to the database to change the mode.

|||

See here: http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

This statement completes imeadetly, but the problem is that is requires exclusive access to the database! Any connection that is using this database has a shared lock on it, even when idle, thus blocking the ALTER DATABASE from completing.

There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT

- ROLLBACK
will close all existing sessions, rolling back any pending transaction.
- NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.

HTH,
~ Remus

|||

This did help me get past this part. Now I'm receiving this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This got me to the next step. Now I'm getting this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This error means you have performed some operations on the msdb database without respecting proper procedures. Something like replacing the mdf and ldf files with MSDB file from another instance. Always follow the proper procedures to move/copy databases, always use attach/detach or backup/restore.

You cannot enable the existing broker in MSDB, you have to create a new one using ALTER DATABASE ... SET NEW_BROKER.

HTH,
~ Remus

|||This worked thanks. Do you think the the mismatched GUID will cause any other problems?|||

You shouldn't have any problem from now on. You have now created new GUIDs, and the one in the database matches the one in the sys.databases.

HTH,
~ Remus

|||Thanks Remus.|||

This as happened to me when using a disaster recovery procedure

- reinstall sql_engine with start/wait setup.exe ...

and restoring master from a previous backup

So it seems that when using "proper procedures". the guid is not recovered correctly.

Xavier

Enable Service Broker for DB Mail on SQL 2005 Cluster

I'm having problems enabling service broker for DB Mail on a SQL 2005 cluster, when I try to execute this sql it just hangs. Any ideas?

USE master ;
GO

ALTER DATABASE AdventureWorks SET ENABLE_BROKER ;
GO

Make sure there are no other people in the database before you run that command. It requires exclusive access to the database to change the mode.

|||

See here: http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx

This statement completes imeadetly, but the problem is that is requires exclusive access to the database! Any connection that is using this database has a shared lock on it, even when idle, thus blocking the ALTER DATABASE from completing.

There is an easy trick for fix the problem: use the termination options of ALTER DATABASE:
ROLLBACK AFTER integer [ SECONDS ]
| ROLLBACK IMMEDIATE
| NO_WAIT

- ROLLBACK
will close all existing sessions, rolling back any pending transaction.
- NO_WAIT will terminate the ALTER DATABASE statement with error if other connections are blocking it.

HTH,
~ Remus

|||

This did help me get past this part. Now I'm receiving this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This got me to the next step. Now I'm getting this error; Any ideas? Thanks for your help, Julie

Msg 9776, Level 16, State 1, Line 2

Cannot enable the Service Broker in database "msdb" because the Service Broker GUID in the database (B4201B09-6358-4C65-8457-D6F50004A4D9) does not match the one in sys.databases (2527A339-BFB3-45C6-978D-412C4FA557CB).

Msg 5069, Level 16, State 1, Line 2

ALTER DATABASE statement failed.

|||

This error means you have performed some operations on the msdb database without respecting proper procedures. Something like replacing the mdf and ldf files with MSDB file from another instance. Always follow the proper procedures to move/copy databases, always use attach/detach or backup/restore.

You cannot enable the existing broker in MSDB, you have to create a new one using ALTER DATABASE ... SET NEW_BROKER.

HTH,
~ Remus

|||This worked thanks. Do you think the the mismatched GUID will cause any other problems?|||

You shouldn't have any problem from now on. You have now created new GUIDs, and the one in the database matches the one in the sys.databases.

HTH,
~ Remus

|||Thanks Remus.|||

This as happened to me when using a disaster recovery procedure

- reinstall sql_engine with start/wait setup.exe ...

and restoring master from a previous backup

So it seems that when using "proper procedures". the guid is not recovered correctly.

Xavier