June 2, 2008 at 12:16 pm
We 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
FerSher
June 17, 2008 at 9:23 pm
Hi 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,
James
June 18, 2008 at 10:06 am
Hi 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 ANSI Text output format for the script file . In SQL Server 2005, the only output option is Unicode. 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 response
June 18, 2008 at 10:37 am
I'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?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 18, 2008 at 2:15 pm
Hi 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 ANSI/MBCS. The output from the script is Unicode. 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 ANSI
June 18, 2008 at 2:57 pm
Ah. 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?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 19, 2008 at 8:39 am
Take 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). 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 19, 2008 at 9:46 am
Amen 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.
June 19, 2008 at 1:30 pm
SQl 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) here
June 19, 2008 at 2:43 pm
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 Save As option displays. If this works, I'll buy any book that you have published at retail price.
Much Appreciated !:D
June 19, 2008 at 3:50 pm
Sherjon (6/19/2008)
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 Save As option displays. If this works, I'll buy any book that you have published at retail price.
Much Appreciated !:D
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!
[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]
June 19, 2008 at 4:47 pm
To 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 responded
June 19, 2008 at 5:19 pm
Did you try what I suggested?
[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]
June 19, 2008 at 5:21 pm
By the way, his name isn't Grasshopper, that's his posting rank. His name is asdfgh.
[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]
June 19, 2008 at 5:24 pm
Sherjon (6/19/2008)
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.
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.
[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]
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply