Re: Is there a way to syntax check all stored procedures (without deferred name resolution)
From: Jacco Schalkwijk (jacco.please.reply_at_to.newsgroups.mvps.org.invalid)
Date: 10/20/04
- Next message: CGW: "Re: table size and indexes"
- Previous message: IBI: "-b or -h in BCP Switch"
- In reply to: Bonj: "Re: Is there a way to syntax check all stored procedures (without deferred name resolution)"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 20 Oct 2004 16:46:13 +0100
Yes, but that was not the question. The question was for a way to check the
syntax and objects used in the stored procedures, not for a complete test
suite.
-- Jacco Schalkwijk SQL Server MVP "Bonj" <benjtaylor at hotpop d0t com> wrote in message news:egPKztmtEHA.3292@TK2MSFTNGP12.phx.gbl... > Sometimes there will be compile time errors, sometimes there will be > runtime errors. The only way to get rid of runtime errors is to check that > it runs through. > > "Jacco Schalkwijk" <jacco.please.reply@to.newsgroups.mvps.org.invalid> > wrote in message news:OgAQc3htEHA.3572@tk2msftngp13.phx.gbl... >> That doesn't do what the original poster asks. See the following: >> >> CREATE PROCEDURE Select_from_non_existing_table >> AS >> SET NOCOUNT ON >> SELECT non_existing_column >> FROM non_existing_table >> >> This procedure is created fine, due to Deferred Name Resolution. That is >> exactly the problem that the original poster has. >> >> -- >> Jacco Schalkwijk >> SQL Server MVP >> >> >> "Philippe [MS]" <ptrotin@online.microsoft.com> wrote in message >> news:%23Sri8OhtEHA.3860@TK2MSFTNGP09.phx.gbl... >>> The best way to do that is to scrip all your existing store procedure, >>> backup your database and apply your script. >>> >>> In the querry analyzer, you just have to check errors. >>> >>> Phil. >>> >>> >>> "Jacco Schalkwijk" <jacco.please.reply@to.newsgroups.mvps.org.invalid> >>> wrote >>> in message news:uKD4YbgtEHA.3788@TK2MSFTNGP09.phx.gbl... >>>> You can check the syntax of a stored procedure in Query Analyzer by >>>> generating an Estimated Execution plan (Ctrl-F5). You can also do this >>> with >>>> SET FMTONLY. >>>> The drawback is that this will not work if your stored procedure uses >>>> temporary tables. >>>> >>>> If you are somewhat skilled with Perl, you can use Erland Sommarkog's >>>> AbaPerls (http://www.abaris.se/abaperls/index.html). It is basically a >>>> pre-processor, and one of the features is that it pre-creates temporary >>>> tables and so gets around Deferred Name Resolution. >>>> >>>> You can't (unfortunately) disable Deferred Name Resolution. >>>> >>>> -- >>>> Jacco Schalkwijk >>>> SQL Server MVP >>>> >>>> >>>> "Mike Dixon" <mdixon@umpublishing.org> wrote in message >>>> news:13b5d8fc.0410190852.7193509d@posting.google.com... >>>> > We are in the process of making a big schema change. Removing many >>>> > tables, changing column names, removing columns, etc. I would like >>>> > to >>>> > be able to check the syntax on all the stored procedures to see the >>>> > ones in error. Also, is there anyway to disable Deferred Name >>>> > Resolution and Compilation? I would like the syntax check to tell me >>>> > a table was not found. >>>> >>>> >>> >>> >> >> > >
- Next message: CGW: "Re: table size and indexes"
- Previous message: IBI: "-b or -h in BCP Switch"
- In reply to: Bonj: "Re: Is there a way to syntax check all stored procedures (without deferred name resolution)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
