Post reply

Query runs fine in master db but not in production db

  • July 4, 2011 at 7:50 am

    #243480

    Hi All,

    I got stuck with problem while I was trying to figure out while a SP 'PROC_IMAGE_UPLOADER' is running or not. Actually I need to rerun this SP after each 15 minutes but if it is not already running.

    I used below stuff to implement it but I am amazed it works when run in master database and shows syntax error if run in production database:

    Select * From

    (Select SPID, (Select Object_Name(ObjectID)

    from sys.dm_exec_sql_text(sp.sql_handle)) As OBJECTNAME

    From master.dbo.sysprocesses SP) SPO

    Where OBJECTNAME='PROC_IMAGE_UPLOADER'

    Does any body has any idea about it?

  • July 4, 2011 at 7:52 am

    #1347855

    Create a setting table.

    update set field = 1 at the top of the query.

    Then 0 at the end.

    Schedule to run the proc every 15 minutes. 1st statement is to check if the value is 0. If not, return.

  • July 4, 2011 at 9:21 am

    #1347925

    Would help if you post the error message you get in production.

  • July 4, 2011 at 9:56 pm

    #1348050

    SQL Server throw below error:

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '.'.

  • July 4, 2011 at 9:58 pm

    #1348051

    Hi Ninja's_RGR'us

    Thanks for your reply.

    This method does work when to remove a lock I require to kill SP process.

  • July 5, 2011 at 4:55 am

    #1348174

    Manish Mittal (7/4/2011)

    Hi Ninja's_RGR'us

    Thanks for your reply.

    This method does work when to remove a lock I require to kill SP process.

    I'm not sure I understand your train of thoughts.

  • July 5, 2011 at 5:15 am

    #1348185

    1) Is your production server sql2008 ?

    2) is your production DB dblevel 100 ?

    DMV may not work when running the db in a lower compatibility mode.

    btw avoid usage of the deprecated master.dbo.sysprocesses

    If you schedule the sproc in a sqlagent job, it will only launch the job once per interval.

    A job will not be launched again if that job is already running.

    e.g. if you try to start it using an alert.

    FYI : help to tighten use of cmdshell or sp_start_job

    [/url]

    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

Viewing 7 posts - 1 through 7 (of 7 total)

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