Post reply

stored procedure does not work overnight

  • 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.

  • April 5, 2004 at 1:21 am

    #501719

    - 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

  • April 5, 2004 at 1:31 am

    #501720

    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 ????

  • April 5, 2004 at 2:28 am

    #501724

    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

  • April 5, 2004 at 3:56 am

    #501728

    - 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

  • April 5, 2004 at 4:56 pm

    #501812

    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

  • April 6, 2004 at 12:46 am

    #501825

    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

  • April 6, 2004 at 1:05 pm

    #501922

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

  • April 6, 2004 at 2:02 pm

    #501928

    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

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

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