Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

|||

Hi! If you follow Abha's instructions from 03-01-2007 you can fix this problem! I finally managed to get it to work after months of trying to fix it. By selecting SQL Server Agent->Jobs->[the job] open it's properties and find the steps that do not work. Edit the step and change where it says "run as" to "JobProxy" or whatever else you've called in "ON EVERY PACKAGE THAT WILL NOT SUCCESSFULLY COMPLETE UNDER THE SYSTEM LOGIN". Save outta everything and run your Job. It should work! Note: I when i first tried it, i did not change all the packages to run under the new proxy and it failed.

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

|||

Hi! If you follow Abha's instructions from 03-01-2007 you can fix this problem! I finally managed to get it to work after months of trying to fix it. By selecting SQL Server Agent->Jobs->[the job] open it's properties and find the steps that do not work. Edit the step and change where it says "run as" to "JobProxy" or whatever else you've called in "ON EVERY PACKAGE THAT WILL NOT SUCCESSFULLY COMPLETE UNDER THE SYSTEM LOGIN". Save outta everything and run your Job. It should work! Note: I when i first tried it, i did not change all the packages to run under the new proxy and it failed.

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

sql

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

|||

Hi! If you follow Abha's instructions from 03-01-2007 you can fix this problem! I finally managed to get it to work after months of trying to fix it. By selecting SQL Server Agent->Jobs->[the job] open it's properties and find the steps that do not work. Edit the step and change where it says "run as" to "JobProxy" or whatever else you've called in "ON EVERY PACKAGE THAT WILL NOT SUCCESSFULLY COMPLETE UNDER THE SYSTEM LOGIN". Save outta everything and run your Job. It should work! Note: I when i first tried it, i did not change all the packages to run under the new proxy and it failed.

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

|||

Hi! If you follow Abha's instructions from 03-01-2007 you can fix this problem! I finally managed to get it to work after months of trying to fix it. By selecting SQL Server Agent->Jobs->[the job] open it's properties and find the steps that do not work. Edit the step and change where it says "run as" to "JobProxy" or whatever else you've called in "ON EVERY PACKAGE THAT WILL NOT SUCCESSFULLY COMPLETE UNDER THE SYSTEM LOGIN". Save outta everything and run your Job. It should work! Note: I when i first tried it, i did not change all the packages to run under the new proxy and it failed.

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

|||

Hi! If you follow Abha's instructions from 03-01-2007 you can fix this problem! I finally managed to get it to work after months of trying to fix it. By selecting SQL Server Agent->Jobs->[the job] open it's properties and find the steps that do not work. Edit the step and change where it says "run as" to "JobProxy" or whatever else you've called in "ON EVERY PACKAGE THAT WILL NOT SUCCESSFULLY COMPLETE UNDER THE SYSTEM LOGIN". Save outta everything and run your Job. It should work! Note: I when i first tried it, i did not change all the packages to run under the new proxy and it failed.

Problem with SQLAgent Running SSIS Package "...package execution failed..."

I am trying to run an SSIS package from the SQL Agent. I am able to execute the package manually from VS2005 and SQL Server. When I try to run the package from the agent, I get the error "The package execution failed. The step failed." VS2005, SQL Server, and the SQL Server Agent are all installed on my local machine and running from the same account. I only have one step in the job and it doesn't include any scripts. Are there any permissions I need to set for the agent? What am I missing?

-Stephen

Are you able to run the package successfully using DTExec ? If so , what is the context under which you are executing this . Please try with the same account for SQLAgent service and give a try, it should work. If not then you may want to use logging feature of SSIS package to get more appropriate error message, SQLAgent does not provide good error logging w.r.t to SSIS job steps.

Thanks,

Gops Dwarak

|||Delete the table you created with Import/Export utility and then run the job again. This worked for me.|||

Its seems like you haven't installed Integration services.

Install Integration services , restart the SQL agent and try to execute that package

Thanks,

Q_A

|||I am having the same problem. This did not work for me. the package runs for me manually but randomly fails when scheduled. Your solution did not work for me.|||

Hey Even I was facing the same problem but i could solve it .. Just with the few steps below:

1. Go to SQL server Management Studio. under Security ->Credentials->Create New Credential. Give any Credential Name e.g 'Job Account' . Fill your own Windows account in identity column. domain\account. Password Give ur own passowrd

2. After creating 'JobAccount' as Credential Goto SQLServerAgent->Proxies. Create a new proxy. Give any proxy name.e.g give 'JobProxy' . Credential should be the one which u created in the above step.Here in this case it is JobAccount'. In subsystems. Check Sql Server Integration Servive Package.

3. Now when you create a job it should run under 'Jobproxy' instead of 'SQL Agent Service account'.

Here U go... Your job is successful.

Any questions please let me know.

|||

I'm having the same problem, and I did what you suggesting above but it did not work. I still get the same error.

Please, help me.

|||

Does your window account have sufficient rights? If not try giving full rights to your account and try again.

|||

I added domain admin privilege to my account and still the same error. Oh the world of DTS was so much simpler...

|||

UPDATE... I did 2 things and my Packages magically started running properly... I set all of the SQL Server Services back to using the builtin Service accounts (they were set as a Domain User service account) and I installed SP2.

REM7600

|||I'm having the same issues as well, looks like we'll have to try SP2. We have one domain account that runs everything, Integration Services, SQL Agent, and SQL Server. We created the package with that user and the job is owned by that user as well and it still fails, I don't understand that at all. A great and simple product was unnecessarily complicated and now it's a mess.|||

I am getting the same error and cannot manage to get around it.

I have a job that simply imports data from an Informix Db using a ODBC connection string (I have also tried a simpler package, but am getting the same error).

The package runs fine when executed in SQL server BID and when executed manually.

I have created a proxy user and appropriate credentials however I still get the error >

The process could not be created for step 1 of job # (reason: A required privilege is not held by the client). The step failed.

The account in question is running as an uber-admin (literally every box is ticked), it is the dbo of the database I am trying to upload to and it has a role within the msdb database with appropriate permissions (it was also the creator of the package)

Looked through here > http://support.microsoft.com/kb/918760 without success.

EDIT - I have also tried executing the step with the job as the SQL agent service but I get the "The package execution failed. The step failed" error when you try execute a package as a different login to what the package was created under.

Any help would be much appreciated.

|||

I also have the same error and cannot find a solution. I have done a lot of searching, and have found that Microsoft has done as little as possible in the way of providing solid, step-by-step guides for creating identities, credentials, and proxies for use with SQLAgent.

My SQL services are running under the same active directory account. My credential uses a different ad account. I've even tried adding these accounts to the local admins group with no success.

Executed as user: ADS\hnetsql2. The process could not be created for step 1 of job 0x27F5B898EE348A4DB17838696B2E1CD0 (reason: A required privilege is not held by the client). The step failed.

Which privilege?

Could this have to do with Kerberos delegation? I do not manage the active directory. However, I can see that my server has "Do not trust this server for delegation" checked under the Properties | Delegation tab. I get an Access Denied error from AD if I try to change this setting.

-Tony

|||

Hi! If you follow Abha's instructions from 03-01-2007 you can fix this problem! I finally managed to get it to work after months of trying to fix it. By selecting SQL Server Agent->Jobs->[the job] open it's properties and find the steps that do not work. Edit the step and change where it says "run as" to "JobProxy" or whatever else you've called in "ON EVERY PACKAGE THAT WILL NOT SUCCESSFULLY COMPLETE UNDER THE SYSTEM LOGIN". Save outta everything and run your Job. It should work! Note: I when i first tried it, i did not change all the packages to run under the new proxy and it failed.

Problem With SQL Server Job

Hi,
We have jobs successfully running on the server. Now we moved the server to a different IP address(still the same machine).
I modified the DTS which has been scheduled as a job. In the DTS I modified the server IP address. Now when I ran the job I am getting the error message as " DTSRun: Loading... Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."
If I run the DTS itself it is working fine. But it is not working as a job.
But if I create a new job with the same DTS, the job runs fine without any error.
I have logged into the machine with my own user account.
Please some advise on this.

ThanksAre you trying to run the encrypted job that DTS created originally? If so, the GUID points to the previous version, not the one you modified. If you change the IP and let DTS create a new job, it should be OK. Or, manually create an uncrypted job with DTSRUN (details are in BOL).|||When you run it it runs under the context of your id, which has authority to right to the drives...

When it's scheduled it runs under the context of the sql server agents account, which does not have rights...

And because you made it an ip...it's leaving and coming back in to itself...and has to authenticate...

Grant the accounts the rights to the drive...|||Yes. I am running the encrypted job. But when I replaced this encrypted string with the new encrypted string (after I created the new job from the modified DTS), the job successfully ran.
If we use DTSRUN we need to provide the username and password in the code which will be seen by other users when they check the properties of the job.
So we are using the encrypted method.
If we want to user the encryption methodology, and if there are 100s of jobs, do we have to recreate each job from the DTS and replaced the encryption string or there is any easier method?
If we use DTSRUN method and if we provide username and password for sa in that string, will it be seen by otherusers when they try to open the job?

Thanks|||Is the SQLAgent a Windows user? If so, use the /E switch for a trusted connection.

Yes, you will have to create a new job for each encrypted string. Or script out one job, gather all the encrypted strings, and mass produce them.|||Does it have to be DTS?

Can't they be sprocs?

Or is it too late?sql

Problem with Sql Server 2000 error

I am using VWD with Sql Server 2000. My application was running smoothly till I rebooted both my local box and the Machine where sql Server 2000 resides and now I am getting this error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

like I said before I am using Sql Server 2000 and not 2005. I don't really understand the problem. The application was running smoothly before rebooting it, so I know there is nothing wrong with the code. Maybe I need to start some service within IIS or something.

If anyone has any idea please let me know.

Thanks in advance for your help

Hi,

I do not have an idea why you catch this error while working with a SQL2K database.

But to enable remote connections, open the SQL Server Surface Area Configuration tool. Then drill down the Services and Connections, open the Database Engine. Then enable remote connections from this configuration tool.

eralper

http://www.kodyaz.com

|||

Could be a number of issues.

First, I rarely let SQL Server use named pipes. I hate them, I force SQL Server to TCP/IP always, unless it's a local connection then shared memory.

A firewall blocking the connection could do it.

Putting in the wrong server name could do it.

Configuring SQL Server to deny remote connections could do it (That is what the error message is saying).

There was a problem with SP2 and SQL Server 2000. Check the SP readme.txt to make sure you've configured it correctly for use with XP SP2.

Problem with SQL Server 2000 developer

I'm also having the same problem with MSDE. I really just want a local SQL Server instance on my machine for developer purposes. I'm running Win XP Pro sp2. When I attempt to install either SQL Server OR the analysis services, the install sheild logo appears for a moment, then nothing. There is no setup, no error, no nothing. The sqlsetup says it's running, but after almost 10 minutes, nothing happens.

What can I do to get some kind of SQL running on my computer? The same thing happens when I try to install the analysis tools, too!

Thanks!

Have you tried 're-booting' your computer and then running the installer?

I highly recommend using SQL Express instead of MSDE. You can download a copy here.

|||Just as an update, I have attempted to reboot my computer a half dozen times. I actually require the 2000 framework because my company is currently using SQL Server 2000 ENT.|||As an update, I have attempted to reboot my computer several times when installing either SQL Server or the analysis tools. I need 2000 on my machine because our company is still using SQL Server 2000 ENT.|||

what version and edition of sql server is this ? If it is Enterprise Edition , then it need Server Version of OS like Windows 2000 server,2003 Server etc... What you can do is Download SQL Server Express downloadble version of SQL Server 2005 and install

Madhu

|||I have attempted to reboot and reinstall the software at least a half dozen times. I'm only trying to install the developer version of SQL 2000 and/or the analysis tools.|||I've even tried closing my anti-virus software. It still doesn't load. The CD spins for a few moments, then stops. I can live without the SQL Server on my machine, but I really need the tools.sql

Problem with SQL Server

A Stored Procedure has been running fine returning result in 2 secs but
surddenly started taking 2 mins to run. When I run the SQL codes in query
analyser it runs fine. Any idea what the problem may be?
Thanks
Egbon.Have a look at
INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224587
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/default.aspx?scid=kb;EN-US;243589
INF: Understanding and Resolving SQL Server 7.0
or 2000 Blocking Problems
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224453
As well as these articles themselves, they contain links in them to lots
of other performace troubleshooting type articles. Lots of good stuff !
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Egbon" <Vnjowusi@.gosps.com> wrote in message
news:uTma$F6WDHA.3248@.tk2msftngp13.phx.gbl...
A Stored Procedure has been running fine returning result in 2 secs but
surddenly started taking 2 mins to run. When I run the SQL codes in query
analyser it runs fine. Any idea what the problem may be?
Thanks
Egbon.|||Thanks for the links Jasper. My question was why will it run fine in Query
Analyzer and run slow in Stored Procedure. I can't understand.
Egbon.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OameON6WDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Have a look at
> INF: Troubleshooting Application Performance with SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;224587
> HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;243589
> INF: Understanding and Resolving SQL Server 7.0
> or 2000 Blocking Problems
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224453
> As well as these articles themselves, they contain links in them to lots
> of other performace troubleshooting type articles. Lots of good stuff !
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Egbon" <Vnjowusi@.gosps.com> wrote in message
> news:uTma$F6WDHA.3248@.tk2msftngp13.phx.gbl...
> A Stored Procedure has been running fine returning result in 2 secs but
> surddenly started taking 2 mins to run. When I run the SQL codes in query
> analyser it runs fine. Any idea what the problem may be?
> Thanks
> Egbon.
>
>|||Do you mean it runs slow as a stored procedure in Query Analyzer ? i.e. if
you run the contents of the procedure in Query Analyzer does it run quicker
than the exec procedurename ? Sorry if I misunderstood you question, I
thought you meant that in general use by your application the performace of
this procedure was slow which may be cause by blocking etc so profiler would
help to show up the problem. You can also capture the execution plan and
look for differences between the slow and fast executions.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Egbon" <Vnjowusi@.gosps.com> wrote in message
news:eAVQIV6WDHA.2424@.TK2MSFTNGP12.phx.gbl...
Thanks for the links Jasper. My question was why will it run fine in Query
Analyzer and run slow in Stored Procedure. I can't understand.
Egbon.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OameON6WDHA.2256@.TK2MSFTNGP10.phx.gbl...
> Have a look at
> INF: Troubleshooting Application Performance with SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;224587
> HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;243589
> INF: Understanding and Resolving SQL Server 7.0
> or 2000 Blocking Problems
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q224453
> As well as these articles themselves, they contain links in them to lots
> of other performace troubleshooting type articles. Lots of good stuff !
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Egbon" <Vnjowusi@.gosps.com> wrote in message
> news:uTma$F6WDHA.3248@.tk2msftngp13.phx.gbl...
> A Stored Procedure has been running fine returning result in 2 secs but
> surddenly started taking 2 mins to run. When I run the SQL codes in query
> analyser it runs fine. Any idea what the problem may be?
> Thanks
> Egbon.
>
>

Problem with Sql Query

By Running a query, I got the result set as shown below

Month Status Count
===== ====== =====
April I 129
April O 4689
April S 6
July I 131
July O 4838
July S 8
June I 131
June O 4837
June S 8
May I 131
May O 4761
May S 7

But, I need the same result set as below and no. of rows for Status is unknown. it may be more than three like (I, O, S, T, W and so on), so dynamically the there will be more columns.

Month I O S
===== = = =
April 129 4689 6
July 131 4838 8
June 131 4837 8
May 131 4761 7

Can anyone provide me the tips/solution.

Thanks in advance

RG

Reminds me of the Cross-Tab queries in MS-Access.

Check -

http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574

'Almost' dynamic column generation.

|||I believe the new pivot functionality in sql server 2005/ express will do that for ya.sql

Problem with SQL 2000 on Windows CLUSTERING

I am running SQL server 2000 on a two nodes clustering environment using Window 2003 on both machine.

SQL server went down on one node and it will not come backup... it takes forever...pending... and not started. I suspect there are some integrity data issue...

It then caused one of the node to be unavailable... on the resource... it sees the nic card is unavailable on that machine... however, when I ping the IP... I got reply.... I rebooted that machine, it still does not show available though the clustering adminstration console.

So any idea where I should start looking to fix this issue? should I evit the node? How can I check the NIC cards are available?

HELP? ANYWHERE TO START?

I believe clusters should all be supported by the supplier so you could raise a call with PSS and also with your supplier.

problem with split function to select multiple values

When running the split function from my data pane within ssrs, or when
running it from sql server directly everything works fine.
But..when running the report in preview I receive dbo.split too many
arguments specified. I assume reporting services in some way passes it
like a string value once running the report. Otherwise it's also
noticeable the join function works fine within
code i'm using is like this within my dataset
Select myfield
from table
where field in
(
select item
from dbo.split(@.par,',')
)
The parameter passed to the split function has an 'integer value'
specified for the 'parameter value' . The split value is the well
known generic split function.If @.Par is a multivalued parameter, then try joining the items together in a
string:
Select myfield
from table
where field in
(
select item
from dbo.split(join(@.par,','),',')
)
"koopmans.johan@.hccnet.nl" wrote:
> When running the split function from my data pane within ssrs, or when
> running it from sql server directly everything works fine.
> But..when running the report in preview I receive dbo.split too many
> arguments specified. I assume reporting services in some way passes it
> like a string value once running the report. Otherwise it's also
> noticeable the join function works fine within
> code i'm using is like this within my dataset
> Select myfield
> from table
> where field in
> (
> select item
> from dbo.split(@.par,',')
> )
> The parameter passed to the split function has an 'integer value'
> specified for the 'parameter value' . The split value is the well
> known generic split function.
>|||Update to my previous post, the parameter to the split function must be a
varchar parameter as this will be sent through as a single comma separated
string list to the function.
"koopmans.johan@.hccnet.nl" wrote:
> When running the split function from my data pane within ssrs, or when
> running it from sql server directly everything works fine.
> But..when running the report in preview I receive dbo.split too many
> arguments specified. I assume reporting services in some way passes it
> like a string value once running the report. Otherwise it's also
> noticeable the join function works fine within
> code i'm using is like this within my dataset
> Select myfield
> from table
> where field in
> (
> select item
> from dbo.split(@.par,',')
> )
> The parameter passed to the split function has an 'integer value'
> specified for the 'parameter value' . The split value is the well
> known generic split function.
>|||Branden, sorry for taking your time...I've been using a direct query
within SSRS so there was no need for the split (this explains the
error). I tend to use SP's or cubes regularly and confused the way
they handle parameters with a direct query i'm using now. So this is
the simple solution
Select myfield
from table
where field in
(
@.par
)

Problem with SP4 installation and REPLSYS.SQL

I have a SQLServer 2000 SP3 running on Windows Server 2000 SP4. My
installation has 5 named instances. I was able to install SQLServer SP4
onto 4 of the named instances with out any problem. When I installed the
SP4 update I was logged on as the local administrator and used Windows
authentication.
When I attempted the install on the last instance, it failed running the
REPLSYS.SQL script. After some digging I found the REPLSYS.OUT file and it
told me that the error was with procedure sp_addextendedproc in the master
db. The script tried to execute the following command:
sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
The error says that there is already an object named 'xp_mergexpusage' in
the database.
I have looked at the list of extended stored procedures listed in the Master
DB and I cannot see the xp_mergexpusage stored procedure anywhere. Is it
hidden somehow? I can see the stored procedure in the other instances that
were sucessfully updated but not in the instance that fails.
Has anyone else run into this problem when installing SP4?
Is there a way to attempt a delete of the xp_mergexpusage extended stored
procedure incase it is hidden somehow?
Any help would be greatly appreciated.
Greg Pagan
I found the answer to this issue myself.
Turns out the problem had nothing to do with SP4 at all. It just pointed
out the underlying issue.
I had corruption in the master data base that somehow messed with the
DBI_NextID. The value of that indicator was out of sync with the max(ID)
indicator. The DB_NextID as less than the Max(ID) which was telling the
system that the object already existed. The fix which came from
sqlforums.windowsitpro.com is to generate new DB_NextIDs by creating and
droping simple tables in master until the counter for DB_NextID grows larger
than max(ID). It took me over 700 table creates to get to that point.
After that the SP4 Update ran successfully.
Thanks anyway.
"Greg Pagan" <gpagan@.thehartford.com> wrote in message
news:O%23CHMX3VGHA.5692@.TK2MSFTNGP09.phx.gbl...
>I have a SQLServer 2000 SP3 running on Windows Server 2000 SP4. My
>installation has 5 named instances. I was able to install SQLServer SP4
>onto 4 of the named instances with out any problem. When I installed the
>SP4 update I was logged on as the local administrator and used Windows
>authentication.
> When I attempted the install on the last instance, it failed running the
> REPLSYS.SQL script. After some digging I found the REPLSYS.OUT file and
> it told me that the error was with procedure sp_addextendedproc in the
> master db. The script tried to execute the following command:
> sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
> The error says that there is already an object named 'xp_mergexpusage' in
> the database.
> I have looked at the list of extended stored procedures listed in the
> Master DB and I cannot see the xp_mergexpusage stored procedure anywhere.
> Is it hidden somehow? I can see the stored procedure in the other
> instances that were sucessfully updated but not in the instance that
> fails.
> Has anyone else run into this problem when installing SP4?
> Is there a way to attempt a delete of the xp_mergexpusage extended stored
> procedure incase it is hidden somehow?
> Any help would be greatly appreciated.
> Greg Pagan
>
>

Problem with SP4 installation and REPLSYS.SQL

I have a SQLServer 2000 SP3 running on Windows Server 2000 SP4. My
installation has 5 named instances. I was able to install SQLServer SP4
onto 4 of the named instances with out any problem. When I installed the
SP4 update I was logged on as the local administrator and used Windows
authentication.
When I attempted the install on the last instance, it failed running the
REPLSYS.SQL script. After some digging I found the REPLSYS.OUT file and it
told me that the error was with procedure sp_addextendedproc in the master
db. The script tried to execute the following command:
sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
The error says that there is already an object named 'xp_mergexpusage' in
the database.
I have looked at the list of extended stored procedures listed in the Master
DB and I cannot see the xp_mergexpusage stored procedure anywhere. Is it
hidden somehow? I can see the stored procedure in the other instances that
were sucessfully updated but not in the instance that fails.
Has anyone else run into this problem when installing SP4?
Is there a way to attempt a delete of the xp_mergexpusage extended stored
procedure incase it is hidden somehow?
Any help would be greatly appreciated.
Greg PaganI found the answer to this issue myself.
Turns out the problem had nothing to do with SP4 at all. It just pointed
out the underlying issue.
I had corruption in the master data base that somehow messed with the
DBI_NextID. The value of that indicator was out of sync with the max(ID)
indicator. The DB_NextID as less than the Max(ID) which was telling the
system that the object already existed. The fix which came from
sqlforums.windowsitpro.com is to generate new DB_NextIDs by creating and
droping simple tables in master until the counter for DB_NextID grows larger
than max(ID). It took me over 700 table creates to get to that point.
After that the SP4 Update ran successfully.
Thanks anyway.
"Greg Pagan" <gpagan@.thehartford.com> wrote in message
news:O%23CHMX3VGHA.5692@.TK2MSFTNGP09.phx.gbl...
>I have a SQLServer 2000 SP3 running on Windows Server 2000 SP4. My
>installation has 5 named instances. I was able to install SQLServer SP4
>onto 4 of the named instances with out any problem. When I installed the
>SP4 update I was logged on as the local administrator and used Windows
>authentication.
> When I attempted the install on the last instance, it failed running the
> REPLSYS.SQL script. After some digging I found the REPLSYS.OUT file and
> it told me that the error was with procedure sp_addextendedproc in the
> master db. The script tried to execute the following command:
> sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
> The error says that there is already an object named 'xp_mergexpusage' in
> the database.
> I have looked at the list of extended stored procedures listed in the
> Master DB and I cannot see the xp_mergexpusage stored procedure anywhere.
> Is it hidden somehow? I can see the stored procedure in the other
> instances that were sucessfully updated but not in the instance that
> fails.
> Has anyone else run into this problem when installing SP4?
> Is there a way to attempt a delete of the xp_mergexpusage extended stored
> procedure incase it is hidden somehow?
> Any help would be greatly appreciated.
> Greg Pagan
>
>

Problem with SP4 installation and REPLSYS.SQL

I have a SQLServer 2000 SP3 running on Windows Server 2000 SP4. My
installation has 5 named instances. I was able to install SQLServer SP4
onto 4 of the named instances with out any problem. When I installed the
SP4 update I was logged on as the local administrator and used Windows
authentication.
When I attempted the install on the last instance, it failed running the
REPLSYS.SQL script. After some digging I found the REPLSYS.OUT file and it
told me that the error was with procedure sp_addextendedproc in the master
db. The script tried to execute the following command:
sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
The error says that there is already an object named 'xp_mergexpusage' in
the database.
I have looked at the list of extended stored procedures listed in the Master
DB and I cannot see the xp_mergexpusage stored procedure anywhere. Is it
hidden somehow? I can see the stored procedure in the other instances that
were sucessfully updated but not in the instance that fails.
Has anyone else run into this problem when installing SP4?
Is there a way to attempt a delete of the xp_mergexpusage extended stored
procedure incase it is hidden somehow?
Any help would be greatly appreciated.
Greg PaganI found the answer to this issue myself.
Turns out the problem had nothing to do with SP4 at all. It just pointed
out the underlying issue.
I had corruption in the master data base that somehow messed with the
DBI_NextID. The value of that indicator was out of sync with the max(ID)
indicator. The DB_NextID as less than the Max(ID) which was telling the
system that the object already existed. The fix which came from
sqlforums.windowsitpro.com is to generate new DB_NextIDs by creating and
droping simple tables in master until the counter for DB_NextID grows larger
than max(ID). It took me over 700 table creates to get to that point.
After that the SP4 Update ran successfully.
Thanks anyway.
"Greg Pagan" <gpagan@.thehartford.com> wrote in message
news:O%23CHMX3VGHA.5692@.TK2MSFTNGP09.phx.gbl...
>I have a SQLServer 2000 SP3 running on Windows Server 2000 SP4. My
>installation has 5 named instances. I was able to install SQLServer SP4
>onto 4 of the named instances with out any problem. When I installed the
>SP4 update I was logged on as the local administrator and used Windows
>authentication.
> When I attempted the install on the last instance, it failed running the
> REPLSYS.SQL script. After some digging I found the REPLSYS.OUT file and
> it told me that the error was with procedure sp_addextendedproc in the
> master db. The script tried to execute the following command:
> sp_addextendedproc 'xp_mergexpusage', 'xprepl.dll'
> The error says that there is already an object named 'xp_mergexpusage' in
> the database.
> I have looked at the list of extended stored procedures listed in the
> Master DB and I cannot see the xp_mergexpusage stored procedure anywhere.
> Is it hidden somehow? I can see the stored procedure in the other
> instances that were sucessfully updated but not in the instance that
> fails.
> Has anyone else run into this problem when installing SP4?
> Is there a way to attempt a delete of the xp_mergexpusage extended stored
> procedure incase it is hidden somehow?
> Any help would be greatly appreciated.
> Greg Pagan
>
>

Problem with sorting in exported Excel document

Hello. I am using Reporting Services 2000 and Microsoft Excel 2003 running on
Windows XP Professional. I have a report that contains sales information that
I am exporting to Excel. The first two lines of the report contain title
information. They span all columns of the report. For example, the first line
(let's call it Food Sales Information) spans 15 columns. After the first two
lines of title information, there is a line containing the names of the
columns. After that, I have a lot of lines containing the sales data. I
export this to Excel. When I click in one of the numeric columns and click
the "A->Z" (sort ascending) button, I get the following error:
"This operation requires the merged cells to be identically sized."
Clciking Show Help gives me the following detail:
"This error only appears if you attempted to sort a range of previously
merged cells, but not all cells in the sort range were merged or are not of
the same size.
To avoid this behavior, unmerge all the merged cells in the range, or merge
all the cells in the range to be the same as the largest merged cell
grouping."
I believe this error to be caused the the two header rows. When I delete the
two header rows from the exported Excel document, I do not get the error.
Is there any way around this problem? Can I get the data to sort in Excel
without removing the header? Thanks in advance for the help.For those of you with this problem in the future who find this post
through google, Teo pointed me to a blog with this very issue
addressed. The bottom line is exporting to Excel from RS2000 leaves a
lot to be desired. Here is the blog with a work-around for this
problem:
http://blogs.msdn.com/chrisbal/archive/2006/07/08/659545.aspx

Problem with simple cursor

I have a cursor here that appears to never stop running even though the
record set that populates it is only 22 records

heres the code:

declare cursorfinal cursor for
select appointmenteffdate, appointmentDuration, provideroid from @.main

open cursorfinal

FETCH NEXT FROM cursorfinal
INTO @.aff, @.duration, @.poid

WHILE @.@.FETCH_STATUS = 0
BEGIN

delete from @.main_temp where (appointmenteffdate between @.aff and
dateadd(minute, @.duration , @.aff) or
dateadd(minute,appointmentduration,appointmenteffd ate ) between @.aff
and dateadd(minute, @.duration , @.aff))
and provideroid = @.poid
select @.poid

END

CLOSE cursorfinal
DEALLOCATE cursorfinalJimbo (jim.ferris@.motorola.com) writes:
> I have a cursor here that appears to never stop running even though the
> record set that populates it is only 22 records
> heres the code:
> declare cursorfinal cursor for
> select appointmenteffdate, appointmentDuration, provideroid from @.main
> open cursorfinal
> FETCH NEXT FROM cursorfinal
> INTO @.aff, @.duration, @.poid
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> delete from @.main_temp where (appointmenteffdate between @.aff and
> dateadd(minute, @.duration , @.aff) or
> dateadd(minute,appointmentduration,appointmenteffd ate ) between @.aff
> and dateadd(minute, @.duration , @.aff))
> and provideroid = @.poid
> select @.poid
> END
> CLOSE cursorfinal
> DEALLOCATE cursorfinal

You need to move the FETCH into the loop:

WHILE 1 = 1
BEGIN
FETCH ...
IF @.@.fetch_status <> 0
BREAK

-- Do stuff
END

However, there is no reason to write a cursor at all here:

delete @.main_temp
from @.main_temp t
where EXISTS
(SELECT *
FROM @.main m
WHERE m.provideroid = t.provideroid
AND (t.appointmenteffdate between
m.appointmenteffdate and
dateadd(minute, m.appointmentDuration,
m.appointmenteffdate)
or dateadd(minute, t.appointmentduration,
t.appointmenteffdate)
between m.appointmenteffdate AND
dateadd(minute, m.appointmentDuration,
m.appointmenteffdate)))

It's essential that you lear to operations like this in a set-based
fashion. Maybe your cursor over 22 rows runs quickly, but what if
real production data has 20000 rows? The difference beween the
cursor and a set-based statement like the above, can easily be a
factor of 1000 in such case.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Here's a guess. I'm assuming that (provideroid, appointmenteffdate) is
unique in @.main_temp. If you need more help then please post DDL /
DECLARE statements so that we don't have to guess at keys and
constraints - that's pretty important information for solving data
manipulation problems.

DELETE T
FROM @.main_temp AS T
WHERE EXISTS
(SELECT *
FROM @.main AS M
WHERE M.provideroid = T.provideroid
AND M.appointmenteffdate
< DATEADD(MINUTE,T.appointmentduration,T.appointment effdate)
AND DATEADD(MINUTE,M.appointmentduration,M.appointment effdate)
> T.appointmenteffdate
AND M.appointmenteffdate < T.appointmenteffdate) ;

(untested)

Alternatively, why bother deleting the rows at all from the table
variable? Just ignore the unwanted rows until you discard the variable.

--
David Portas
SQL Server MVP
--|||The reason why I did the cursor is really complicated...I tried it with
the delete before and it wasnt working on certain records...but the
cursor solved it..thanks for all your help and suggestions..got it
working