Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


VSS For Stored Procedures


VSS For Stored Procedures

Author
Message
Sherjon
Sherjon
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 25
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
James D-764131
James D-764131
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 23
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
Sherjon
Sherjon
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 25
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
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
Sherjon
Sherjon
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 25
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
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
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
TheSQLGuru
TheSQLGuru
SSCertifiable
SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)SSCertifiable (6K reputation)

Group: General Forum Members
Points: 5950 Visits: 8304
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). Smile

Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Anders Pedersen
Anders Pedersen
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1430 Visits: 876
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.
sqld-_-ba
sqld-_-ba
SSC-Enthusiastic
SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)SSC-Enthusiastic (120 reputation)

Group: General Forum Members
Points: 120 Visits: 1095
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
Sherjon
Sherjon
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 25
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 !BigGrin
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search