Post reply

STORED PROCEDURE PROBLEMS

  • April 29, 2004 at 2:50 am

    #85072

    Ok here's the deal...

    We recently deleted and re-created a Stored Procedure through Enterprise Manager on our SQL Server 2000 SP3 installation.

    When we did this, we discovered that although the execution of that stored procedure would show as successful, the stored procedure itself did not actually function.

    Following investigation, we dropped and recreated the Stored Procedure using scripting via the Query Analyzer and it then started to function normally. The same thing happened with a brand new stored procedure that we created on the same installation.

    Permissions were checked and were fine, and no error message was generated - (that was visible) - in either case.

    Has anybody else had this problem? Or does anybody have any ideas as to why this might have happened?

    Thanks and Regards,

    Mat

  • April 29, 2004 at 3:45 am

    #504627

    I don't know

  • April 30, 2004 at 12:37 am

    #504772

    I experienced a similar problem a few times: some lines in a Stored Procedure didn't work. I dropped those lines and wrote it anew from scratch (in Enterprise Manager), and it worked.

    I suppose there were some characters in those lines that Enterprise Manager doesn't show ...

     

  • April 30, 2004 at 5:17 am

    #504795

    How did you delete it and recreate it? The method you used may be important to understanding why it didn't work.

    I usually will use the option to script an SP or UFN to a .SQL file, then run that script in QA. That enables me to control how the script file is created, edit it to ensure it everything's OK (or make changes if I want to... easy way to clone an SP), and then run the script in QA and see that it worked successfully. This also has the advantage that you're already in QA so you can test the SP or UFN.

     

  • April 30, 2004 at 7:43 am

    #504824

    Hope this helps!

    I ran into a similar problem when porting sprocs and udfs.  I was using the Copy SQL Server Objects Task in DTS to send tables, views, sprocs, etc to our production server.  The sprocs seemed to transfer properly, but when I opened up my web reporting pages, they could no longer find the sprocs to even exist.

    Turns out to have been a schema issue.  The web pages expected the sprocs, functions, etc. to be dbo.sp_rpt, dbo.fn_get_entity, etc. but the Copy SQL Server Objects Task sent them as userid.sp_rpt, userid.fn_get, etc.

    More annoying is the fact that the Copy SQL Server Objects Task illogically drops the existing dbo versions before recreating the userid versions.

    Echoing the sentiments of a previous posting, sometimes there's no substitute for the fine level of control you get from a script run in Query Analyzer...



    But boss, why must the urgent always take precedence over the important?

  • April 30, 2004 at 8:03 am

    #504831

    Thanks guys, glad it's not just me.

    I think that I'll stick to the QA and just scripts from now on, at least with them I've got more control.

    That will teach me to be lazy!!!

    By the way, full marks to Churleigh's last comment - why IS that???!?!?!?

Viewing 6 posts - 1 through 5 (of 5 total)

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