Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
daniel_oslo
Starting Member
2 Posts |
 Posted - 2011-01-04 : 04:14:35
|
Hello everyone, i wish you an happy new year ! First at all: excuse my poor english  I have a stored procedure that works perfectly when i run it manually in the query analyserI tried to schedule it via a job and it crashes because of an error related to "null values inserting"This procedure (that i did not create) is very complex and there is a lot of transactions calling other spu, etc...As i am not very enthusiast to debug it, i would like "just" to understand what happens.Some details:1- I use the sa login with Enterprise Manager and i have tried, with no more success, to run the job via the user login i use in query analyser 2- Yes it really works when i run it manually with the query analyser (i have checked the expected results in the database)So i have 2 questions:1- Why such a important and blocking error is raised via the job and not via the query analyser ?  2- What can i do ? or test ? or look after ? |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-04 : 05:08:12
|
Are you sure the Scheduled Task is running with the User Login that you expect it to?Is the SProc working with any external resources? (such as files on disk). Permissions at a System Level will be different for a scheduled task (than when you are logged on in QA)To check what is actually happening (i.e. the parameters passed, and the User that the SProc is running as) you may get some help from SQL Profiler (i.e. without having to modify the Sproc itself).Also worth trying a simple SProc that just logs what the current user is - so you can test that your scheduled task is indeed running AS the anticipated user"excuse my poor english"There was nothing wrong with that part  |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-01-04 : 07:01:59
|
| daniel_oslo as in Oslo, Norway? In that case: Velkommen til SQLTeam! Vi trenger flere nordmenn her :)Are you sure that the correct parameters are passed when you have this procedure scheduled? Do you have other job steps in this job? Conceptually there is no difference between running a procedure from QA and running it via a scheduled job. The only difference is basically the user running the procedure and the parameters that are passed to it.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
daniel_oslo
Starting Member
2 Posts |
Posted - 2011-01-04 : 07:52:14
|
| Hei Lumbago og Kristen! Takk for svarene og velkommen dine :)Først : Beklager for min norsk, jeg er fransk :DJeg skal snakke engelsk hvis det passer bra for dere ...I have tried to change the user running the job but still the same errorBut here is some new clues:I have decided to debug the spu and focus on this error of inserting null values, and it seems that the problem come from the definition of the id column (which raise the error)The id column is a non null column but has a default value that calls a function db_fn_getsequencenumber that calls a extended stored procedure master..xp_seq, and xp_seq is a function coming from a external file afcsq.dll locally stored into the dataserverThe spu insert values into the table without specifying this id column because it is filled automatically with an incremental value but i still don't understand why it works from QA and not from EntMgtWhat do you think ? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-01-04 : 08:07:42
|
| English is the language for this site so I think we'll stick to that :)But this really sounds like a permission issue. Can you check and see if the user running the SQL Server Agent service has permissions to run the extended store procedure?- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-04 : 08:12:33
|
| "I have tried to change the user running the job but still the same error"Are you sure you have succeeded? (Hence my earlier suggestion to try scheduling a simple procedure that just "logs" the current user so you can test, and then discount, that as being an issue. (I think SQL Profiler would also tell you, but it is less than 100% certain IMHO)"xp_seq is a function coming from a external file afcsq.dll locally stored into the dataserver"Might be a differences in permissions between your SProc running from QA and the SYSTEM USER which will be used for a Scheduled Task (this TTBOMK will be IRRESPECTIVE of what user you run the Sproc as in your scheduled tasks, and will instead be the User that is configured for the SQL Agent Service)Still seems a bit unlikely that a change of user will prevent running a DLL stored locally on the machine - but maybe that DLL then tries to talk to another machine to compare ID numbers, or somesuch, and the SQL Agent Service's user does not have enough permissions for that task ...(I don't like the sound of a DLL being used to assign the next available ID number, but that's another story! lets hope there is a good reason why that is the solution you have on your system) |
|
|
|
|
|
|
|
|
stored procedure runs manually but not scheduled