Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Null value passed in as job parameter Expand / Collapse
Author
Message
Posted Friday, May 2, 2014 7:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 1:58 PM
Points: 7, Visits: 157
Hello everyone,

We have had an interesting issue with one of our production servers lately and I cant really put my finger on why its happening.

Scenario is as follows: We have a job that has multiple job steps.

The step for the job reads like this: exec ssrsJob_spBillingSummaryCurrentPeriod NULL

When you execute this step, the job just hangs, no processing takes place. The job does not fail or go to the next step, just hangs there and racks up the run time.

When I execute the exact same code from managements studio in a query window, the code executes just fine. and the SP is done in a matter of seconds.

Any ideas as to why I cannot run this in a job?
Post #1567031
Posted Friday, May 2, 2014 8:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 1:41 PM
Points: 1,298, Visits: 1,891
Can you execute the exact same code from managements studio in a query window with the SQL Agent service account?

Alex S
Post #1567047
Posted Friday, May 2, 2014 8:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 1:58 PM
Points: 7, Visits: 157
Yes, job executing as SA. Running the SP standalone as SA as well. Job doesnt work, standalone does.
Post #1567052
Posted Friday, May 2, 2014 10:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
Have you uses sp_who/sp_who2/sp_whoisactive or queried sys.dm_exec_connections/sessions or run a trace to see what the SQLAgent session is doing?

Based on the fact that the sp starts with ssrs* I'd guess that this is doing something with reporting services, what is it and does the SQLAgent user have the rights on the SSRS server to do it?




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1567123
Posted Friday, May 2, 2014 12:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 1:58 PM
Points: 7, Visits: 157
It actually has nothing to do with reporting services. this job just populates tables that are then used by reporting services in another job.
Post #1567146
Posted Friday, May 2, 2014 12:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:26 AM
Points: 10,381, Visits: 13,436
Okay. Like I said, that was a guess based on the name. I'm not surprised I guessed wrong.

I just thought of something else. The connection settings can have an impact. Check out Erland Sommarskog's excellent article, "Slow in the Application, Fast in SSMS", to see how this works.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1567150
Posted Friday, May 2, 2014 12:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 1:58 PM
Points: 7, Visits: 157
I will check that out this weekend and report back....thanks for the article!
Post #1567154
Posted Friday, May 2, 2014 1:06 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 1:41 PM
Points: 1,298, Visits: 1,891
aaron.floyd (5/2/2014)
Yes, job executing as SA. Running the SP standalone as SA as well. Job doesnt work, standalone does.

Wait what's the service account for sql agent service?
Login with that account via ssms and see if u can execute the SP
It may be permissions issue.


Alex S
Post #1567158
Posted Friday, May 2, 2014 1:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 4, 2014 1:58 PM
Points: 7, Visits: 157
I have, it runs as SA in SSMS. The Job runs as SA as well. The job would fail if there were invalid permissions. That isnt the case here, it just hangs.
Post #1567159
Posted Friday, May 2, 2014 1:47 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 1:41 PM
Points: 1,298, Visits: 1,891
aaron.floyd (5/2/2014)
I have, it runs as SA in SSMS. The Job runs as SA as well. The job would fail if there were invalid permissions. That isnt the case here, it just hangs.


Does this SP interacting with a file system?

Just FYI:
Sa cannot be a service account.
Sql server configuration manager will tell you which account(localsystem or domain account) is assigned as a service account.


Alex S
Post #1567166
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse