InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 13:30:18 GMT20http://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxAh, OK. Now I understand. Thanks,Tue, 01 Jul 2008 06:06:42 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspx[quote][b]rbarryyoung (6/20/2008)[/b][hr][quote][b]jezemine (6/20/2008)[/b][hr]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:[url=http://www.codeplex.com/scriptdb]http://www.codeplex.com/scriptdb[/url][/quote]jezemine: I did take a quick look at this, but I could not figure out you could use it from a SQL command line?[/quote]when I say cmd line, I meant a DOS cmd line. it's a console app, like sqlcmd or osql.scriptdb is not meant to be used from t-sql.Mon, 30 Jun 2008 22:28:34 GMTjezeminehttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxThanks for the feedback, sherjon.Mon, 30 Jun 2008 16:37:54 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxRBarryYoung, The script works as advertised. It is exactly what we were looking for. Thank you very much:)Mon, 30 Jun 2008 16:13:39 GMTSherjonhttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspx[quote][b]jezemine (6/20/2008)[/b][hr]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:[url=http://www.codeplex.com/scriptdb]http://www.codeplex.com/scriptdb[/url][/quote]jezemine: I did take a quick look at this, but I could not figure out you could use it from a SQL command line?Fri, 20 Jun 2008 22:45:04 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxif 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:[url=http://www.codeplex.com/scriptdb]http://www.codeplex.com/scriptdb[/url]Fri, 20 Jun 2008 20:05:23 GMTjezeminehttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspx[quote][b]TheSQLGuru (6/20/2008)[/b][hr][quote][b]rbarryyoung (6/20/2008)[/b][hr][quote][b]TheSQLGuru (6/20/2008)[/b][hr]Maybe take a look at ApexSQL's Script product, or search the web for some SMO code that will script out objects for you.[/quote]Not necessary. As I demonstrated above, this can easily be done from SQL script alone.[/quote]Script does a weeeeeeeee bit more than your script does. ;) So does a number of SMO implementations I have seen.[/quote]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.Fri, 20 Jun 2008 18:43:17 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspx[quote][b]rbarryyoung (6/20/2008)[/b][hr][quote][b]TheSQLGuru (6/20/2008)[/b][hr]Maybe take a look at ApexSQL's Script product, or search the web for some SMO code that will script out objects for you.[/quote]Not necessary. As I demonstrated above, this can easily be done from SQL script alone.[/quote]Script does a weeeeeeeee bit more than your script does. ;) So does a number of SMO implementations I have seen.Fri, 20 Jun 2008 17:32:18 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspx[quote][b]TheSQLGuru (6/20/2008)[/b][hr]Maybe take a look at ApexSQL's Script product, or search the web for some SMO code that will script out objects for you.[/quote]Not necessary. As I demonstrated above, this can easily be done from SQL script alone.Fri, 20 Jun 2008 17:04:39 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxAs promised here is the stored procedure that will list a procedure that you name, including the "DROP.. IF.." prefix:[code]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 algorithimto deal with splitting an arbitrary string of almost any size.Test command: EXEC spScriptProc_DropIf [spScriptProc_DropIf]2008-06-20 RBarryYoung*/SET NoCount ON--====== Get the string that contains the proc's listing, and other infoDeclare @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+']GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO' + @listing--======prep for the SplitDeclare @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 = 1SET @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*/;WITHcteTally 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[/code]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.Fri, 20 Jun 2008 17:02:45 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxMaybe take a look at ApexSQL's Script product, or search the web for some SMO code that will script out objects for you.Fri, 20 Jun 2008 13:25:14 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspx[quote][b]Sherjon (6/20/2008)[/b][hr]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.[/quote]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.Fri, 20 Jun 2008 11:31:56 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxYes - You are absolutely correct - Scripting to a New Query window and then [b]saving as [/b]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 [b]Show Differences[/b] 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 [b]Include IF NOT EXISTS[/b] clause but no IF EXISTS clause. If anyone knows of a way to to create a compatible script, we would be much obliged.[i]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]GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GOCREATE PROCEDURE RPQ_EarningsDeductions_Main...[/i]Fri, 20 Jun 2008 09:37:03 GMTSherjonhttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxGood 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:[img]http://www.sqlservercentral.com/Forums/Attachment886.aspx[/img]And this is the Save Dialog that you should see, with the Save Options:[img]http://www.sqlservercentral.com/Forums/Attachment887.aspx[/img]Fri, 20 Jun 2008 08:03:30 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxOK, 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. [i]That[/i] Save dialog does have the Save Options on the Save button.Fri, 20 Jun 2008 07:56:08 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxA screenshot or two could be helpful here too. A picture is worth ...Fri, 20 Jun 2008 07:36:43 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspx[quote][b]Sherjon (6/19/2008)[/b][hr]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.[/quote]*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.Fri, 20 Jun 2008 06:18:46 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxHi 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.Thu, 19 Jun 2008 23:03:49 GMTSherjonhttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspx[quote][b]Sherjon (6/19/2008)[/b][hr]The winner is grasshopper, because, although it is a little cumbersome, the Script Wizard does indeed produce ANSI text output. The SQL Server 2000 script would generate an 'IF exists' clause that would DROP the SPROC and then CREATE a new one. IF we discover a way to append a drop Script and a Create script, we will have it made.[/quote]I can show you how to do that, but you'll have to get your save button to work because it won't work with the Wizard.Thu, 19 Jun 2008 17:24:07 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxBy the way, his name isn't Grasshopper, that's his posting rank. His name is [b]asdfgh[/b].Thu, 19 Jun 2008 17:21:21 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxDid you try what I suggested?Thu, 19 Jun 2008 17:19:13 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxTo all those who responded, Thank you very much. The winner is grasshopper, because, although it is a little cumbersome, the Script Wizard does indeed produce ANSI text output. The SQL Server 2000 script would generate an 'IF exists' clause that would DROP the SPROC and then CREATE a new one. IF we discover a way to append a drop Script and a Create script, we will have it made. Thanks again to all who respondedThu, 19 Jun 2008 16:47:09 GMTSherjonhttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspx[quote][b]Sherjon (6/19/2008)[/b][hr]Hey Grasshopper, We installed the x86 version of SP2 because we have 32 bit machines. We have the 'script' entry for Tools which is nice because we can output the 'IF exists..' line in the CREATE script. The only output Save options we have are 'SQL Server Files .SQL' and All Files(*.*). I will re-install SP2(Maybe there is a later version) to see if the [b]Save As [/b]option displays. If this works, I'll buy any book that you have published at retail price. Much Appreciated !:D[/quote]The Save options are NOT on the File Type drop down! They are on the SAVE button itself!Look at the SAVE button on the Save dialog: The should be a tiny down-arrow on the right-hand side. Click that arrow. Then select "Save with encoding.." Bingo!Thu, 19 Jun 2008 15:50:08 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxHey Grasshopper, We installed the x86 version of SP2 because we have 32 bit machines. We have the 'script' entry for Tools which is nice because we can output the 'IF exists..' line in the CREATE script. The only output Save options we have are 'SQL Server Files .SQL' and All Files(*.*). I will re-install SP2(Maybe there is a later version) to see if the [b]Save As [/b]option displays. If this works, I'll buy any book that you have published at retail price. Much Appreciated !:DThu, 19 Jun 2008 14:43:27 GMTSherjonhttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxSQl 2005 management (SP2) studio has 'ANSI text' option..in object explorer.. right-click a database -> Tasks->Generate Scripts->select database->choose options->choose object type->choose object->'Script to File'Save as: 'unicode text' 'ansi text'(choose ansi) hereThu, 19 Jun 2008 13:30:25 GMTwaevahttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxAmen to ApexSQL Edit. Not sure that will help them with their current procedures though. But it does make a few steps un-necessary.The easiest solution might just be to do a one time refresh of VSS with all SQL objects scripted from SQL 2005. At least that is what I did. Major pain, but a one time update.Thu, 19 Jun 2008 09:46:51 GMTAnders Pedersenhttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxTake a look at ApexSQL Edit. Among its many incredible features is very nice source code control integration. Tell them TheSQLGuru sent you and you can pick up a nice discount (and my daughter can get a few coins added to her college fund). :)Thu, 19 Jun 2008 08:39:15 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxAh. I get it now. Thought you were having trouble getting the files, and couldn't understand why.Not sure how to solve that one. Maybe a file converter?Wed, 18 Jun 2008 14:57:42 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxHi GSquared, Thanks for the input. Although one can save the File as either .SQl or .Txt the character encoding remains the same(Unicode). If you Right-Click any object in VSS and select Properties, the General tab will display the File Type. In our system, all the objects are stored as [b]ANSI/MBCS[/b]. The output from the script is [b]Unicode[/b]. This is what causes an error when doing a DIFF. In SQL 2000 the scripting prompt allowed you to choose the character encoding and we always chose ANSIWed, 18 Jun 2008 14:15:57 GMTSherjonhttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxI'm not sure what method you are using to save the scripts, but if you right-click a proc in Management Studio, select Script Stored Procedure as -> CREATE to -> File, you can select to save it as a .txt file. Is that what you're trying to do?Wed, 18 Jun 2008 10:37:08 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxHi Grasshopper, Thanks for all the info. Our issue is that we started tracking changes in SQL Server 2000. When we would alter a stored procedure, we could 'script' it out to a file, do a 'DIFFERENCES' and then check it back into VSS. We used [b]ANSI Text [/b]output format for the script file . In SQL Server 2005, the only output option is [b]Unicode[/b]. As a result, we are unable to do a Difference using the latest VSS file and the output file from SQL Server 2005. VSS gives us an error that it cannot compare files that are different types Until we discover a solution we 1. Extract latest from VSS 2. Make the changes in SQL Server 2005 3. Copy the altered Stored Procedure and Paste it into the extracted .SQL file 4. Do A Diff in VSS 5. Check it back in If you find a way to diff a text file and a SQL Server 2005 script file, please let us know Thanks again for your responseWed, 18 Jun 2008 10:06:21 GMTSherjonhttp://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxHi FerSher,We also use VSS 2005 to track version history of our stored procedures. We use Visual Studio 2005 to manage the whole process.If you don't have Visual Studio 2005, then I'm sorry, but thought I'd write anyway. If you do, read on!Click File -> New -> Project. In the project dialog that appears, select 'Other project types', then 'Database', then 'Database project'. This opens up a new project. If you right-click the project and click 'add new item', you'll be offered different templates. Select 'Stored procedure script' which contains script to remove the stored proc if it already exists and then recreates it.Set up a new database reference in the project by right-clicking 'Database references' in the project and then clicking 'New database reference'. You can then run the stored procedure on your database by right-clicking the script in the project window and then clicking 'Run'. You'll see the results in the output window.Use File -> Source Control options to add the project/solution to sourcesafe. You'll then get check-in/out context menu options when right-clicking the stored procedure scripts in the project.Another nice thing about the database project is the ability to add sub-folders. So even though the stored procs are all at the same level in the database, you can use folders to further organise the scripts.Let me know if this helps.Cheers,JamesTue, 17 Jun 2008 21:23:17 GMTJames D-764131http://www.sqlservercentral.com/Forums/Topic510133-149-1.aspxWe have just upgraded from SQL Server 2000 to 2005 and we use VSS 2005 to track modifications. The exporting/Scripting of stored procedures in SQL Server 2005 is quite different than it's 2000 equivalent. In Sql Server 2000, one could specify the File type to save the Export and we used Windows(Text) Ansi format for all of our exports. With 2005, you just get a .SQL file with code to either Create or Alter a Stored procedure. So even doing a file difference fails in VSS with a Character encoding error. Is there any way to synch up the format ? Thanks FerSherMon, 02 Jun 2008 12:16:46 GMTSherjon