Hi, I've developed a sproc generator using SMO types and all works fine except when I want to generate sprocs prefaced by a drop statement. To do this I create an instance of the Scripter object and set it's options.ScriptDrops property to true ( instance.options.ScriptDrops = true).
When options.ScriptDrops = true the output omits the actual stored procedure from the scriopt. When I comment out scriptDrops = true it works correctly. How do i get it working correctly WITH drop statements?
Example:
private void CreateScripter() {
if( this._SprocSscripter == null ) {
this._SprocSscripter = new Scripter();
this._SprocSscripter.Server = this._tables.Parent.Parent;
this._SprocSscripter.Options.IncludeHeaders = true;
this._SprocSscripter.Options.IncludeIfNotExists = true;
this._SprocSscripter.Options.DdlBodyOnly = false;
this._SprocSscripter.Options.DdlHeaderOnly = false;
this._SprocSscripter.Options.ExtendedProperties = true;
this._SprocSscripter.Options.ScriptDrops = true;
}
}
OUTPUT:
/****** Object: StoredProcedure [dbo].[usp_selectCategories] Script Date: 01/18/2007 16:39:29 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_selectCategories]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_selectCategories]
OUPUT when i comment out last line ( this._SprocSscripter.Options.ScriptDrops = true;)
/****** Object: StoredProcedure [dbo].[usp_selectCategories] Script Date: 01/18/2007 16:37:04 ******/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_selectCategories]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @.statement = N'CREATE PROCEDURE [dbo].[usp_selectCategories]
@.CategoryID [int]
AS
SELECT [CategoryID],
[CategoryName],
[Description],
[Picture]
FROM [dbo].[Categories]
WHERE [CategoryID] = @.CategoryID
'
END
If I Comment out all lines in the CreateScripter method exect the 1st, 2nd and last ( this._SprocSscripter.Options.ScriptDrops = true;)
The output looks like this:
DROP PROCEDURE [dbo].[usp_selectCategories]
That's it-- no sproc body.. what gives?
Bill Graziano did a great job of documenting how to do this at his SQLTeam.com web site - here's the link to the article:
http://www.sqlteam.com/item.asp?ItemID=23185
|||Excellent. Thank you. Too bad it's a bug, but learning that it is has restored some of my sanity :-)|||
|||
