SQL server Jobs

Question

SQL server Jobs

dvaru

SSC Enthusiast

Points: 192
More actions
December 6, 2002 at 8:43 am

#58548

One of my stored procedure takes 5 minutes to run in Query Analyser. If I schedule the same as a Job, it runs for 5 hours.
Also there is another stored procedure that runs fine when run form Query analyser.This procedure sends record to another database residing on the same server.The record has a text field in it. The same when run as a job truncates the text field.
Any idea why it is happening. I have very basic knowledge of how jobs run.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

zach_john SSCertifiable Points: 5922 More actions · Answer 1

My first question is what else is running when the job is schedule? Run the sp in QA, if takes 5 min, start the job. Now how long does it take?

Second part, not sure what you're saying.

In one case the sp works fine in QA and does not truncate the text field.

When the sp is ran as a job it truncates the text field.

Watch user creditials, are they the same in both cases?

Are you evaluating the text field the same way in both cases?

John Zacharkan

Scorpion_66 SSCertifiable Points: 7891 More actions · Answer 2

I've seen the text truncation deal. In the case I saw it, the server's settings for ansi_nulls was set one way and the QA's settings had been modified to treat them like previous versions and the query was concatenating fields having nulls in some of them

Such as

FirstName + "," + Lastname

In the case where nulls were actually treated as nulls, this would give a null value if either field contained a null

In case where nulls were treated like they were in 6.5, this would give an empty string for the particular field having the null, and still concatenate the rest of the values. The minimum value you could get, even with both fields null would be the comma.

Check and see if that's your case.

dvaru SSC Enthusiast Points: 192 More actions · Answer 3

In QA, I use a login that has 'public' permission to the database. And the both the job run under 'SysAcct_SQLExec' userid which has system administrative permissions. And no other job is running when these two jobs are scheduled to run.