May 16, 2011 at 10:36 am
I have two instances of SQL Server 2005 SP3 'Test' and 'Prod' on same server. i scheduled SSIS packages and stored procs together in a single job as several steps in both the instances (everything is same including the code and order of steps). In that, executing a proc which delete the duplicates in a table, is one of the step.
Jobs are executed successfully in both the instances. In the 'test', duplicates are deleted where in 'prod' instance it was not. when i execute the procedure manually, instead of scheduling, in prod 'instance' then duplicates are deleted.
On more thing i have to mention is, in 'prod' instance sometimes duplicates are deleted and sometime they were not deleted. In 'Test' instance it is working fine, duplicates are deleted all the times. I am getting problem only with prod instance. do any one face this type of issue?
May 16, 2011 at 1:07 pm
Are you getting any errors?
Have you checked permissions? 😉
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 16, 2011 at 2:22 pm
Nope, each and every step ran successfully.
May 16, 2011 at 2:35 pm
rajeevmehr (5/16/2011)
Jobs are executed successfully in both the instances. In the 'test', duplicates are deleted where in 'prod' instance it was not. when i execute the procedure manually, instead of scheduling, in prod 'instance' then duplicates are deleted.
Welsh Corgi (5/16/2011)
Are you getting any errors?Have you checked permissions? 😉
Yes I have run into this and so have a number of other people on this forum just within the last few days.
You have an issue with permissions on the SQL Server Agent Account.
When you execute manually you are using the credentials of your user account.
When you run it as a job you as using the SQL Server Agent credentials.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 16, 2011 at 2:44 pm
In that case, how it would be possible that duplicates are deleted on one day and other day it will not.
May 16, 2011 at 2:53 pm
rajeevmehr (5/16/2011)
I have two instances of SQL Server 2005 SP3 'Test' and 'Prod' on same server.In the 'test', duplicates are deleted where in 'prod' instance it was not. when i execute the procedure manually, instead of scheduling, in prod 'instance' then duplicates are deleted.
On more thing i have to mention is, in 'prod' instance sometimes duplicates are deleted and sometime they were not deleted. In 'Test' instance it is working fine, duplicates are deleted all the times. I am getting problem only with prod instance. do any one face this type of issue?
You tell me... I don't believe that you are providing all of the necessary details to help us hel you resolve this issue.
I don't know I do not have a crystal ball but the way you described the problem you thought it was working with your account but not when running as a job using SQL Server Agent.
Why don't your refresh you DEV or QA Environment to mirror production.
Run some queries to determine if the data matches the criteria to be deleted.
Monitor your query as it executes, etc.
Do a little research...
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply