Wednesday, March 28, 2012

Invalid stored procedures are getting created which have errors

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..
MannyDeferred name resolution is applied to stored procedures, which basically
means that the referenced objects aren't resolved until the SP is compiled
on first execution. There isn't an option to turn this feature off. Run the
SP to test it.
David Portas
SQL Server MVP
--|||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

No comments:

Post a Comment