Re: Invalid stored procedures are getting created which have errors

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 10/29/04 

Date: Fri, 29 Oct 2004 08:18:21 -0500

To add to David's response, one method to validate procs is to execute with
FMTONLY ON, passing any needed parameters as NULL. This will catch deferred
name resolution errors. However, the only way to completely test the proc
is to actually execute it. This is especially true with dynamic SQL.

SET FMTONLY ON
GO
EXEC spAccountCustomerAdd
GO
SET FMTONLY OFF
GO 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Manny" <mneupane@gmail.com> wrote in message 
news:6162b3aa.0410281304.73741c5a@posting.google.com...
> Hello,
>
> i'm having a strange issue with SQL server 2000 (sp3).  i'm able to
> create stored procedures that have critical erros.  for example, i'm
> able to create the following stored procedure in the tempdb table even
> the the table, nor the columns exist any where. is there a setting
> i've changed on the database that is supressing the validation of the
> stored procedures.
>
>
> CREATE PROCEDURE spAccountCustomerAdd
> AS
>
> select asdfkljasdk,dkajfk,dkjf
> from blah11
>
>
> any help would be appreciated..
>
> Manny

Relevant Pages

  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.sqlserver.server)
  • Re: Securing Stored Procedures from being seen
    ... username, but in SQL Server, there are too many users to map an individuals ... granting the group execute permissions. ... but I would have to know what groups the user belongs ... see the names of any stored procedures to which he has no rights. ...
    (microsoft.public.sqlserver.security)
  • Re: parallelism
    ... SQL Server can not execute multiple stored procedures at ...
    (microsoft.public.sqlserver.programming)
  • Re: Extended stored procedures failues - only under sql server log
    ... I'm talking about "home grown" extended stored procedures. ... For example, 'sa' can execute xp_cmdshell, but CANNOT execute our ... regardless of the fact that its sql server login is ...
    (microsoft.public.sqlserver.programming)
  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... This process runs very quickly if run through Query ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.dotnet.framework.adonet)