VSS For Stored Procedures
-
Hi RBarryYoung,
Please do. That Save button would be awesome.
BTW, I did not mean to offend you in any way with the 'winner' comment. It just happened to be the one that worked for us. If you could tell u how to get the Save button with the arrows, that would be wonderful. We would greatly appreciate it.
-
Sherjon (6/19/2008)
Hi RBarryYoung,Please do. That Save button would be awesome.
BTW, I did not mean to offend you in any way with the 'winner' comment. It just happened to be the one that worked for us. If you could tell u how to get the Save button with the arrows, that would be wonderful. We would greatly appreciate it.
*sigh* Please talk straight. Did you try what I suggested? Are you saying that you do NOT have the arrow on your save button. If so, then please provide your server AND client info: SSMS version and OS version & SP's.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
A screenshot or two could be helpful here too. A picture is worth ...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
OK, I think that I know what the problem is here: When you script the procedure, do NOT script it directly to a file. That option does not have the Save Option.
Instead, script it to a "New Query window..." and then save it from there. That Save dialog does have the Save Options on the Save button.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Good point Kevin.
I have attached two screen shots that show how to get the Save Options that the OP had in SQL2000.
This is how you select the Script to New Query Windo for the procedure:
And this is the Save Dialog that you should see, with the Save Options:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Yes - You are absolutely correct - Scripting to a New Query window and then saving as with encoding does save as an ANSI encoded file. It doesn't ,however, solve our real issue which is synching up SQL Server 2005 scripts with our previously save stored procedues from SQL Server 2000.
Below is what is stored in our VSS stored procedure folder. All of our files have the same format where the script DROPs the Stored procedure if it exists and then CREATEs a new one.
In order to make the new 2005 files compatible, we do the following
- Check out a Stored Procedure from VSS
- Run the checked out file script in SQl Server, to make sure it's the
latest
- Make our code changes
- COPY and PASTE the code from SQL Server 2005 back into the file
we Checked out
- DO a Show Differences in VSS to make sure that only our
changes are recorded in VSS and we don't overlay some one elses
changes
- Check the file back into VSS
We have not found an easy way to format a 2005 script that is compatible with it's 2000 counterpart. It's quite cumbersome to Create a DROP script, then a Create script and then append the two scripts into one file.
I found it interesting to note that in Tools/Options/Scripting, there was a Include IF NOT EXISTS clause but no IF EXISTS clause.
If anyone knows of a way to to create a compatible script, we would be much obliged.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RPQ_EarningsDeductions_Main]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[RPQ_EarningsDeductions_Main]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE RPQ_EarningsDeductions_Main
.
.
.
-
Sherjon (6/20/2008)
We have not found an easy way to format a 2005 script that is compatible with it's 2000 counterpart. It's quite cumbersome to Create a DROP script, then a Create script and then append the two scripts into one file.If anyone knows of a way to to create a compatible script, we would be much obliged.
As I said before, I can do this for you, IF you are using the SAVE from a query window method. However, I can not get to it until this evening.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Maybe take a look at ApexSQL's Script product, or search the web for some SMO code that will script out objects for you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
As promised here is the stored procedure that will list a procedure that you name, including the "DROP.. IF.." prefix:
ALTER Proc spScriptProc_DropIf( @proc as SYSNAME, @schemaName SYSNAME = N'dbo' ) AS
/*
Script a stored procedure in way that is compatible with SQL Server 2000
(that is, it incldues the "DROP IF..." prefix.)
This procedures shamelessly copies Jeff Moden's Tally table Split algorithim
to deal with splitting an arbitrary string of almost any size.
Test command:
EXEC spScriptProc_DropIf [spScriptProc_DropIf]
2008-06-20RBarryYoung
*/
SET NoCount ON
--====== Get the string that contains the proc's listing, and other info
Declare @listing Varchar(MAX)
, @Name SYSNAME
, @Schema SYSNAME
, @TypeName Varchar(12)
Select @listing = m.definition
, @Name = o.name
, @Schema = s.name
, @TypeName = Case o.type
When 'P' Then 'Procedure'
When 'TR' Then 'Trigger'
When 'FN' Then 'Function'
When 'IF' Then 'Function'
When 'V' Then 'View'
When 'TF' Then 'Function'
END
From sys.sql_modules m
Join sys.objects o On o.object_id = m.object_id
Join sys.schemas s On s.schema_id = o.schema_id
Where o.name = @proc
And s.name = @schemaName
--====== Add the DROP prefix, extra parsing lines, etc.
Set @listing = 'if exists (select * from dbo.sysobjects where id = object_id(N''['+@Schema+'].['+@Name+']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)
drop procedure ['+@Schema+'].['+@Name+']
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
' + @listing
--======prep for the Split
Declare @Seperator Varchar(2) -- the string to use as a seperator
, @Sep char(1)
Select @Seperator = char(13)+char(10)-- CR+LF
, @Sep = char(10) --our seperator character
--NOTE: we make the @Sep character LF so that we will automatically
-- parse out rogue LF-only line breaks.
-- Add start and end lines/seperators to the listing so we can handle
-- all the elements the same way
-- and change the seperator expressions to our seperator
-- character to keep all offsets = 1
SET @Listing = @Sep+ Replace(@listing, @Seperator, @Sep) +@Sep
--====== Do the big split, and display the results
/*
"Monster" Split in SQL Server 2005
From Jeff Moden, 2008/05/22
*/
;WITH
cteTally AS
(--==== Create a Tally CTE from 1 to whatever the length
-- of the parameter is
SELECT TOP (LEN(@listing))
ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
SELECT --ROW_NUMBER() OVER (ORDER BY N) AS Number,
SUBSTRING(@listing,N+1,CHARINDEX(@Sep,@listing,N+1)-N-1) AS [--Lines--]
FROM cteTally
WHERE N < LEN(@listing)
AND SUBSTRING(@listing,N,1) = @Sep
To use this for your situation, first make sure that your query results text output setting is as high as possible (8192), or at least 256. Then set you query result output format to "Text".
Now execute it, passing the name of the stored procedure that you want to list, and optionally the schema name as well.
After it displays the listing in the text output window, switch to it and delete the first 2 rows (these are the SELECT column header rows). Then select "Save Results as.." from the right-click menu or the FILE menu. Now you should be able to change the encoding using the Save Options from the SAVE button.
Let us know how it works out for you.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
TheSQLGuru (6/20/2008)
Maybe take a look at ApexSQL's Script product, or search the web for some SMO code that will script out objects for you.Not necessary. As I demonstrated above, this can easily be done from SQL script alone.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
rbarryyoung (6/20/2008)
TheSQLGuru (6/20/2008)
Maybe take a look at ApexSQL's Script product, or search the web for some SMO code that will script out objects for you.Not necessary. As I demonstrated above, this can easily be done from SQL script alone.
Script does a weeeeeeeee bit more than your script does. 😉 So does a number of SMO implementations I have seen.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
TheSQLGuru (6/20/2008)
rbarryyoung (6/20/2008)
TheSQLGuru (6/20/2008)
Maybe take a look at ApexSQL's Script product, or search the web for some SMO code that will script out objects for you.Not necessary. As I demonstrated above, this can easily be done from SQL script alone.
Script does a weeeeeeeee bit more than your script does. 😉 So does a number of SMO implementations I have seen.
So does a number of the SMO implementations that I have written, but none of those things is what the OP asked for. My script does exactly what the OP asked for, so, as I said, those other things are not necessary.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
if you want to automate the generation of scripts from the cmd line, I wrote a tool to do it. it's open source on codeplex:
http://www.codeplex.com/scriptdb
---------------------------------------
elsasoft.org -
jezemine (6/20/2008)
if you want to automate the generation of scripts from the cmd line, I wrote a tool to do it. it's open source on codeplex:jezemine: I did take a quick look at this, but I could not figure out you could use it from a SQL command line?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
RBarryYoung,
The script works as advertised. It is exactly what we were looking for.
Thank you very much:)
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply