Scripting stored procs into a data project is useless for me now.

This is really frustrating. I used to ba able to script stored procs from the server explorer into a data project. It would create a single file for each proc and would look something like this:

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Stored_Procedure_Name')
BEGIN
   DROP Procedure Stored_Procedure_Name
END
GO

CREATE Procedure Stored_Procedure_Name

AS

...

GO

This was great. I could check the scripts into source control. I could use VS.NET to maintain the procs and just right click and select "run on" to deploy it to the sql instance. This seems to have taken a turn for the worse in 2005. All I can get it to create now is this:

/****** Object: StoredProcedure [dbo].[Stored_Procedure_Name] Script Date: 04/23/2006 10:55:50 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Stored_Procedure_Name]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Stored_Procedure_Name]
GO
/****** Object: StoredProcedure [dbo].[Stored_Procedure_Name] Script Date: 04/23/2006 10:55:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Stored_Procedure_Name]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE Procedure dbo.Stored_Procedure_Name
AS
/*
Code goes here
*/
'
END
GO

Wow. this is totally useless for my purposes. Wy does it generate a dynamic sql with an exec? What the heck? I guess your supposed to code all your procs on the db server and then go back at some point and rescript them out and put them into source control. Hopefully there is some way to customize the script template that its using.

I know this isnt a problem if you create a data project from the beginning and just add strored procedure scripts as you go. What about those people that already have a mass of procs in their db server that they want to migrate to a dataproject for source control purposes?

I know I'm just missing something obvious. I can't wait until I can just slap my forhead and move on.

7 Comments

  • You could always write a few lines of SMO code to script them yourself. Even though you shouldn't have to!

  • You can find all the code needed to create the stored procs etc. from the [syscomments] table.

  • Yeah yeah, I know I can do it manually. I know I was able to automate this in VS.NET 2003. I'm just wondering why it changed.

  • While I don't always put them in an data project, I do script all my SPs for deployment. When I tried this with Sql2005, it seemed that I had to do things in multiple steps. It was not at all obvious that I could generate the drop and create statements in the same script.



    Script generation worked very well in the previous version.



  • David,

    I am working on a project that requires me to break my objects down as well.. anywho, if you go down to the sproc level and right click.. you should have the option to SCRIPT TO>FILE/NEW WINDOW/CLIPBOARD and it will give you the code for the sproc only.

  • I was talking about the server exporer in VS.NET 2005 not SQL Managment Studio. Maybe I should ditch VS.NET and just use managment studio for all SQL related stuff. So much for it being an "itegrated development environment".

  • Totally. It's nice to have one env to work in, but the SQLMS is just far superior to the server explorer.

Comments have been disabled for this content.