How to validate stored procedures using a client application?

[Guest]

During the development of a database the columns of the tables may change along the way, such as adding new ones, dropping some, changing their data types, etc. If stored procedures are created as the project progresses, they can easily become out-of-sync with the database schema. I know I can use SQL Server Enterprise Manager to edit and save stored procedures with the native editor. If the stored procedure contains errors, a message will be displayed when I try to save it because SQL Server invokes a method that validates the stored procedure when I click the editor's OK/APPLY buttons.

Is there a way I can invoke that SQL Server method, from an application such as Visual Basic .Net ? I would like to perform batch-validation against all stored procedures in the database. I do not want to edit each single stored procedure to see if it contains an error. Thanks in advance for any suggestions.

 

[Frans Bouma [C# MVP]]

During the development of a database the columns of the tables may change
along the way, such as adding new ones, dropping some, changing their data
types, etc. If stored procedures are created as the project progresses,
they can easily become out-of-sync with the database schema. I know I can
use SQL Server Enterprise Manager to edit and save stored procedures with
the native editor. If the stored procedure contains errors, a message will
be displayed when I try to save it because SQL Server invokes a method that
validates the stored procedure when I click the editor's OK/APPLY buttons.

Is there a way I can invoke that SQL Server method, from an application
such as Visual Basic .Net ? I would like to perform batch-validation
against all stored procedures in the database. I do not want to edit each
single stored procedure to see if it contains an error. Thanks in advance
for any suggestions.

Enterprise Manager just does an ALTER PROCEDURE call.

What you're looking for is SET FTMONLY ON. After that statement, you can
execute a stored procedure like you're used to, however it is not
'effectively' executed, meaning that if you're calling a delete procedure, it
will not delete anything, however it will be interpreted/compiled by the
optimizer. When an error occurs, you'll get it as an exception.

So follow these steps:
- open a connection object
- execute this query: "SET FMTONLY ON"
- keep your connection open and call all your procs
- execute this query: "SET FMTONLY OFF"
- close your connection.

Execute your procs in a try/catch block. When an exception occurs, flag that
proc as 'suspect' by adding its name to an arraylist or something.

Then inspect which procs are errorous and check them. There is a problem:
temptable creations do throw an error with SET FTMONLY ON. if you're not
using temptables, you're fine.

Frans.

 

[Guest]

Frans
Thank you very much for your reply. This solution works for me.