Problem with sqlexpress

hi:

I set up a DSN via Adim tools, then I specify the connection string as "DSN=UserAppSample;Trusted_Connection=True".

When I run my aspx page, it says:

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQLServer]Cannot open database "UserAppSample" requested by the login. Thelogin failed.

It asks for login, but I use windows authentication. So what is wrong with this? :(

This is a SQL permission issue. You need to check the SQL Server logins, make sure the account which runs the application has proper permission on the UserAppSample database.

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 statement

I'm trying to use the following statement:

IF (IDS2_TXT.FIRST_MODIFIER="HQ" THEN
(IDS2_TXT.UNITS/240))

But an error appears whn i try to run it. Basically i want sql to only divide the amount of unit of a record if it finds a value on one of the fields (an leave the rest like they are) can someone please help me on what is wrong with the statement. Thank you.

using the derived column transformation:

FIRST_MODIFIER=="HQ"?(UNITS/240):UNITS

Frank

Problem with SQL Service Pack 4

You can review the setup log for any errors (file called sqlsp.log in the
windows directory). It is perfectly safe to run SP4 again to try and make it
stick (assuming you have the system database backups just in case you need
to regress)
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Leon Parker" <u23479@.uwe> wrote in message news:625d255c1f101@.uwe...
>I upgraded from SQL 2000 Service Pack 3 to SQL 2000 Service Pack 4. The
> install says it completed but after I rebooted and did a select @.@.version
> it
> still shows SP3. I only have a default instance so I know that it
> installed
> on the right instance. Could anyone help me out with this?I tried it several times but got the same result.
I've attached a copy of my sqlsp.log file. I looked at the sqlsp.log file
and it only listed one non-fatal error...I can't see this being the problem
considering it was nonfatal.
Jasper Smith wrote:[vbcol=seagreen]
>You can review the setup log for any errors (file called sqlsp.log in the
>windows directory). It is perfectly safe to run SP4 again to try and make i
t
>stick (assuming you have the system database backups just in case you need
>to regress)
>|||I upgraded from SQL 2000 Service Pack 3 to SQL 2000 Service Pack 4. The
install says it completed but after I rebooted and did a select @.@.version it
still shows SP3. I only have a default instance so I know that it installed
on the right instance. Could anyone help me out with this?|||You can review the setup log for any errors (file called sqlsp.log in the
windows directory). It is perfectly safe to run SP4 again to try and make it
stick (assuming you have the system database backups just in case you need
to regress)
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Leon Parker" <u23479@.uwe> wrote in message news:625d255c1f101@.uwe...
>I upgraded from SQL 2000 Service Pack 3 to SQL 2000 Service Pack 4. The
> install says it completed but after I rebooted and did a select @.@.version
> it
> still shows SP3. I only have a default instance so I know that it
> installed
> on the right instance. Could anyone help me out with this?|||I tried it several times but got the same result.
I've attached a copy of my sqlsp.log file. I looked at the sqlsp.log file
and it only listed one non-fatal error...I can't see this being the problem
considering it was nonfatal.
Jasper Smith wrote:[vbcol=seagreen]
>You can review the setup log for any errors (file called sqlsp.log in the
>windows directory). It is perfectly safe to run SP4 again to try and make i
t
>stick (assuming you have the system database backups just in case you need
>to regress)
>|||What does the following query return?
SELECT SERVERPROPERTY('ProductLevel')
Hope this helps.
Dan Guzman
SQL Server MVP
"Leon Parker" <u23479@.uwe> wrote in message news:625d255c1f101@.uwe...
>I upgraded from SQL 2000 Service Pack 3 to SQL 2000 Service Pack 4. The
> install says it completed but after I rebooted and did a select @.@.version
> it
> still shows SP3. I only have a default instance so I know that it
> installed
> on the right instance. Could anyone help me out with this?|||What does the following query return?
SELECT SERVERPROPERTY('ProductLevel')
Hope this helps.
Dan Guzman
SQL Server MVP
"Leon Parker" <u23479@.uwe> wrote in message news:625d255c1f101@.uwe...
>I upgraded from SQL 2000 Service Pack 3 to SQL 2000 Service Pack 4. The
> install says it completed but after I rebooted and did a select @.@.version
> it
> still shows SP3. I only have a default instance so I know that it
> installed
> on the right instance. Could anyone help me out with this?|||SP3....
Dan Guzman wrote:[vbcol=seagreen]
>What does the following query return?
> SELECT SERVERPROPERTY('ProductLevel')
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1|||SP3....
Dan Guzman wrote:[vbcol=seagreen]
>What does the following query return?
> SELECT SERVERPROPERTY('ProductLevel')
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1

Problem with SQL Server Agent.

SQL Server 2000 sp3 on Windows 2003. I am getting the
following error in the sql server agent log and I am not
able to run any of my jobs. sql server lightweight pooling
setting is set to 0.
[298] SQLServer Error: 18452, Login failed for
user '(null)'. Reason: Not associated with a trusted SQL
Server connection. [SQLSTATE 28000]
Thanks in Advance.
I have gotten this sometime when I use windows logins as the owner of a job.
I think it has something to do with our domain controller being unavailable
sometimes. When error occurs I have changed the owner to a sql server login
and the problems goes away.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Obaid" <anonymous@.discussions.microsoft.com> wrote in message
news:5fb401c42dff$58db8b70$a601280a@.phx.gbl...
> SQL Server 2000 sp3 on Windows 2003. I am getting the
> following error in the sql server agent log and I am not
> able to run any of my jobs. sql server lightweight pooling
> setting is set to 0.
> [298] SQLServer Error: 18452, Login failed for
> user '(null)'. Reason: Not associated with a trusted SQL
> Server connection. [SQLSTATE 28000]
> Thanks in Advance.
|||If you server is in Mixed Mode set the owner of the job to be SA instead of the creater. Otherwise set the owner to be the same Network ID that SQLServer is running as.
Jeff Duncan
MCDBA, MCSE+I
sql

Problem with SQL Server Agent.

SQL Server 2000 sp3 on Windows 2003. I am getting the
following error in the sql server agent log and I am not
able to run any of my jobs. sql server lightweight pooling
setting is set to 0.
[298] SQLServer Error: 18452, Login failed for
user '(null)'. Reason: Not associated with a trusted SQL
Server connection. [SQLSTATE 28000]
Thanks in Advance.I have gotten this sometime when I use windows logins as the owner of a job.
I think it has something to do with our domain controller being unavailable
sometimes. When error occurs I have changed the owner to a sql server login
and the problems goes away.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Obaid" <anonymous@.discussions.microsoft.com> wrote in message
news:5fb401c42dff$58db8b70$a601280a@.phx.gbl...
> SQL Server 2000 sp3 on Windows 2003. I am getting the
> following error in the sql server agent log and I am not
> able to run any of my jobs. sql server lightweight pooling
> setting is set to 0.
> [298] SQLServer Error: 18452, Login failed for
> user '(null)'. Reason: Not associated with a trusted SQL
> Server connection. [SQLSTATE 28000]
> Thanks in Advance.|||If you server is in Mixed Mode set the owner of the job to be SA instead of the creater. Otherwise set the owner to be the same Network ID that SQLServer is running as
Jeff Dunca
MCDBA, MCSE+I

Problem with SQL Server Agent.

SQL Server 2000 sp3 on Windows 2003. I am getting the
following error in the sql server agent log and I am not
able to run any of my jobs. sql server lightweight pooling
setting is set to 0.
[298] SQLServer Error: 18452, Login failed for
user '(null)'. Reason: Not associated with a trusted SQL
Server connection. [SQLSTATE 28000]
Thanks in Advance.I have gotten this sometime when I use windows logins as the owner of a job.
I think it has something to do with our domain controller being unavailable
sometimes. When error occurs I have changed the owner to a sql server login
and the problems goes away.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Obaid" <anonymous@.discussions.microsoft.com> wrote in message
news:5fb401c42dff$58db8b70$a601280a@.phx.gbl...
> SQL Server 2000 sp3 on Windows 2003. I am getting the
> following error in the sql server agent log and I am not
> able to run any of my jobs. sql server lightweight pooling
> setting is set to 0.
> [298] SQLServer Error: 18452, Login failed for
> user '(null)'. Reason: Not associated with a trusted SQL
> Server connection. [SQLSTATE 28000]
> Thanks in Advance.|||If you server is in Mixed Mode set the owner of the job to be SA instead of
the creater. Otherwise set the owner to be the same Network ID that SQLSer
ver is running as.
Jeff Duncan
MCDBA, MCSE+I

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 select with duplicate field names

I am converting a large application so that it can run with SQL server and
Access via ADO into Delphi.
There are many places where it does "select table1.*, table2.* etc"
With Access if there are duplicate field names (e.g. "description") they are
returned fully qualified and you can reference them as "table1.description"
and "table2.description".
I have just discovered to my horror that SQL server will not qualify the
names for you and returns these field names as "description" and
"description1".
Is there any way of altering this behaviour to return the fully qualified
field names as with Access ?
I know I should rewrite the queries to give aliases to the fields but there
maybe 1000's of places in the code and I want to avoid this.
Thanks for any helpHi Andrew,
I am not sure I understood your issue.
Could you place DDL + Sample Data?
What I did not understand is where are the queries writen? In Access Queries
? In Delphi code? In Stored Procedures?
I hope I can help you if I get a better idea of the issue.
Bye,|||you must do it manually (and you always should in SQL)
Access protects the users from themselves. SQL doesnt protect you as much.
You can alias any field name with the "AS" key word:
SELECT MyField AS ThisName
Hope this helps
Greg Jackson
PDX, Oregonsql

Problem with SQL select with duplicate field names

I am converting a large application so that it can run with SQL server and
Access via ADO into Delphi.
There are many places where it does "select table1.*, table2.* etc"
With Access if there are duplicate field names (e.g. "description") they are
returned fully qualified and you can reference them as "table1.description"
and "table2.description".
I have just discovered to my horror that SQL server will not qualify the
names for you and returns these field names as "description" and
"description1".
Is there any way of altering this behaviour to return the fully qualified
field names as with Access ?
I know I should rewrite the queries to give aliases to the fields but there
maybe 1000's of places in the code and I want to avoid this.
Thanks for any helpyou must do it manually (and you always should in SQL)
Access protects the users from themselves. SQL doesnt protect you as much.
You can alias any field name with the "AS" key word:
SELECT MyField AS ThisName
Hope this helps
Greg Jackson
PDX, Oregon

Problem with SQL Script with Clarion Date format

I need to run a sql script to update records in a table that were created
that day. I have a huge database and it would take a while to run a script
on all.
Here's what i have so far: (this is a select stmt version not update stmt
i'm using for testing)
SELECT tm5user.matter.c_date as CreateDate,
((tm5user.matter.c_date)-(1800-12-28))as CalcDate, *
FROM tm5user.matter
INNER JOIN tm5user.billopt ON tm5user.matter.sysid = tm5user.billopt.owner_id
WHERE tm5user.matter.c_date>=((tm5user.matter.c_date)-(1800-12-28))
I got the script to work but it doesn't calculate the date correctly. The
date field "c_date" is based on a clarion base date of 12/28/1800.
I have my select statement return the "c_date" field, the calculated date
field, and full table columns for testing. The CreateDate and CalcDate
should be equal and they are not.
Does anyone have any ideas ... I tried CastDate that also did not work.
Any help or tips would be great ..... thanks.
rob bartley
msce> ((tm5user.matter.c_date)-(1800-12-28))as CalcDate
The above expression (1800-12-28) is doing integer arithmetic. I suggest you use the DATEDIFF or
DATEADD function (depending on what you want to achieve). Also, I suggest you format the date in
language neutral format ('yyyymmdd') so it doesn't break in a nationalized environment.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Rob" <temp@.dstek.com> wrote in message news:us1cOsGtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> I need to run a sql script to update records in a table that were created
> that day. I have a huge database and it would take a while to run a script
> on all.
> Here's what i have so far: (this is a select stmt version not update stmt
> i'm using for testing)
> SELECT tm5user.matter.c_date as CreateDate,
> ((tm5user.matter.c_date)-(1800-12-28))as CalcDate, *
> FROM tm5user.matter
> INNER JOIN tm5user.billopt ON tm5user.matter.sysid => tm5user.billopt.owner_id
> WHERE tm5user.matter.c_date>=((tm5user.matter.c_date)-(1800-12-28))
>
> I got the script to work but it doesn't calculate the date correctly. The
> date field "c_date" is based on a clarion base date of 12/28/1800.
> I have my select statement return the "c_date" field, the calculated date
> field, and full table columns for testing. The CreateDate and CalcDate
> should be equal and they are not.
> Does anyone have any ideas ... I tried CastDate that also did not work.
> Any help or tips would be great ..... thanks.
> rob bartley
> msce
>

problem with sql query.

Hi ya,

I have two tables. One is the temporary table called tbl_temp and the other is called tbl_empDetails.
I want to run a query that would bring me the records of tbl_temp where there is no corresponding ID present in tbl_empDetails, meaning it shud bring only records where a corresponding records is not available in tbl_empDetails. I tried it but this query doesn't work, it is bringing me the records which are there in tbl_empdetails. How can i do it?

SELECTDISTINCT tbl_temp.SID, tbl_temp.fname, tbl_temp.lname, tbl_temp.mail, tbl_temp.tel, tbl_temp.mobile, tbl_temp.office

FROM tbl_temp, tbl_empDetails where tbl_empDetails.adSID != tbl_temp.SID

ORDERBY tbl_temp.lname

Try this

SELECTDISTINCT *

FROMtbl_empDetails

where not tbl_empDetails.adSID in (select SID from tbl_temp)

|||

thank you very very much, you saved me lot of work.

Can you care to explain what you have done with the query and what I was doing wrong?

Just in case if i got any similar problem again.

Cheers

|||

Table 1

KeyValue

1

2

3

Table 2

KeyValue

1

2

If you use "where tbl_empDetails.adSID = tbl_temp.SID" you will get 2 record. (1,1) (2,2)

if you use "where tbl_empDetails.adSID != tbl_temp.SID" you will get 4 records. (1,2) (2,1) (3,1) (3,2)

So. I use sub-query to solve it !

|||

SELECTDISTINCT a.*

FROMtbl_temp a

LEFT JOIN tbl_empDetails b

ON a.SID = b.adSID

Where b.asSID IS NULL

This will run much faster than a sub-query if your tables are large...

Brad

Problem with SQL (Job cannot run, Job status not updated)

Hi All,

I would need your help please.

I just installed a new fresh SQL server under Windows 2000 Advanced server SP 4

1. Firstly i tried to run normal database backup to a folder by select backup database then run manually and it ran very well. Then i tried to run it with schedule as sql backup job to the same folder. After the job was showed in the sql jobs list, i tried to run it manually by clicking start job. When i refresh the screen under the status field it says "executing job step '0 (unknown)" then in about 5 seconds the status turned into "not running". And i cannot find any error in the log and in the last run job status. The SQLSERVERAGENT is running, the strange thing is another job which is only to update the database can run with the scheduler very well.

2. Whenever i run job then after it finished the last run job status is always unknown.

What will be the solution for the conditions above, please help (

Thank you very much in advance

Best Regards

Felix

Just restart the sql server agent and see......may be the agent might have been in hanged state.........but i am not sure about it.....|||

Hi deepak,

Thank you very much

I can only try the solution tomorrow morning, just to make sure.

I have tried to run another jobs which is not backup job by using sqlserveragent as well and its working well.

So with this information perhaps you have other opinition please let me know.

I will let you know how it goes tomorrow morning, wish me luck

many thanks

Felix

sql