stored procedure does not work overnight

Question

Post reply

stored procedure does not work overnight

T Tran

Grasshopper

Points: 13
More actions
April 5, 2004 at 12:41 am

#161788

Hi all
I've got a stored procedured in SQLServer 2000, it has a TINYINT parameter and all it does is select from mutilple tables and return a result set, it has been working fine till last week(I've made a minor
change to it). After I recreated it, it works (does return something), but does not work the next morning.
I ran the SQL of that stored procedure in query analyzer it did return something.
So again I have to recreated it and it works fine. But I have to do that every morning ?
Why ? what is the cause of that ? Other stored procedures are ok, just only this ?
Any help would be appreciated.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

- if you modify a sp, use the alter procedure syntax, so security keeps in place.

- What's the error, how can you tell it is not working ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

T Tran Grasshopper Points: 13 More actions · Answer 2

Thanks for your reply.

There is no error when I run the stored procedure. It returned empty result set. But if I run the sql in the stored procedure, it does return something. The funny thing is that when I recreated it (by drop the proc and create proc) it works, but not the morning after. Something must have happened to the server at night ????

mccork SSC-Insane Points: 22288 More actions · Answer 3

What happens if you run the stored proc (not the sql therein) manually a couple of times?

What happens if you invoke the scheduled job manually a couple of times?

Cheers,
- Mark

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

- IMO you may have to check the ansi-settings you are using.

- script the sp and take a look at

"SET QUOTED_IDENTIFIER ...

GO

SET ANSI_NULLS ....

GO"

and work from there on.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

T Tran Grasshopper Points: 13 More actions · Answer 5

I recreate the stored procedure yesterday from my machine it works, the previous days my boss created the stored procedure from his machine it did not work.

The script I ran yesterday did not have Set quoted identifier OFF., Set Ansi OFF or so.

But it is working now, I still don't know why yet and I don't want to play with it since I don't have time.

I will look at those "Set Quoted identifier OFF , Set Ansi NULL OFF." if it play up next time when I make changes.

Thank you all

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

I think there even are some articles regarding SET and ANSI at this site.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

WHug Ten Centuries Points: 1248 More actions · Answer 7

I'm betting its an ownership or permissions issue, especially if you can run it but your boss and the sql agent can not.

SQLBill SSC Guru Points: 51440 More actions · Answer 8

I agree with whug. Look into the permissions/access rights.

When you run it - it works. When your boss runs it - it fails. That indicates your boss doesn't have the same permissions/access rights as you have.

When it's run as a job, it is running under the permissions that SQLSERVERAGENT service is running under. Does that LOGIN have the same permissions/access rights as you do?

To further check out the possibility of a permissions error, review the SQL Server Error Logs and the Windows Event Viewer Logs (application, security, system).

-SQLBill