Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

VSS For Stored Procedures Expand / Collapse
Author
Message
Posted Monday, June 2, 2008 12:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 1, 2008 8:26 AM
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
Post #510133
Posted Tuesday, June 17, 2008 9:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 31, 2009 5:41 PM
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

Post #518722
Posted Wednesday, June 18, 2008 10:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 1, 2008 8:26 AM
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
Post #519229
Posted Wednesday, June 18, 2008 10:37 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #519254
Posted Wednesday, June 18, 2008 2:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 1, 2008 8:26 AM
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
Post #519418
Posted Wednesday, June 18, 2008 2:57 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #519458
Posted Thursday, June 19, 2008 8:39 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:36 AM
Points: 4,319, Visits: 6,112
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 at GMail
Post #519913
Posted Thursday, June 19, 2008 9:46 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:11 PM
Points: 1,295, Visits: 759
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.
Post #519979
Posted Thursday, June 19, 2008 1:30 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, February 20, 2014 4:24 PM
Points: 65, Visits: 709
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
Post #520178
Posted Thursday, June 19, 2008 2:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 1, 2008 8:26 AM
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 !:D
Post #520252
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse