Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
CtrlAltDel
Starting Member
17 Posts |
Posted - 2003-10-30 : 09:51:18
|
I've finally gotten round to getting all my SQL stored in SourceSafe, but I've run into an annoying problem when attempting to deploy code from dev to test servers (and presumably from test to production).As part of the deployment, I script all of the stored procedures on the test server (one file per object), and run a file/directory diff against what is held in SourceSafe - that way, I can tell which stored procedures have been changed between releases. So far so good.The problem occurs when a stored procedure calls different servers using a heterogenous query. No problem, you say, just add SET ANSI_WARNINGS ON at the head of your query, and the query will run just fine; so I go and do that, and the query does run just fine. So far, still so good.The problem occurs when I run the diff between dev and test. When you run the Generate SQL Script function in Enterprise Manager, it will happily add SET QUOTED_IDENTIFIER ON and SET ANSI_NULLS ON, but it won't add SET ANSI_WARNINGS ON when they have been explicitly added; this means that no matter how many times I run my upgrade script, next time I run Generate SQL Script, it will have forgotten that SET ANSI_WARNINGS ON needs to be set, so the script fails when run.Other than forcing all queries to be run with SET ANSI_WARNINGS ON by editing the server properties (not a desireable option), can anyone think of another solution?--David Keaveny |
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2003-10-30 : 21:29:45
|
One option might be to create a DTS ActiveX script which will append the scripts before moving them to sourcesafe.Perhaps something like this:Private Sub SET_ANSI_WARNINGS_ON() 'Exported Scripts as ANSI Dim objFSO As New FileSystemObject Dim objFolder As Folder Set objFolder = objFSO.GetFolder("C:\temp") 'This could be DTSGlobalVariable Dim objFile As File For Each objFile In objFolder.Files 'Look for the sproc files If Right(objFile.Name, 3) = "PRC" Then Dim ThisFile As String ThisFile = CStr(objFile.Path) Dim objTextStream As TextStream 'Since we need to add content to beginning of sproc we need to 'read all the text into a variable, then delete all the text, then rewrite it with 'the new line first, then append everything from the variable after that. Set objTextStream = objFSO.OpenTextFile(ThisFile, ForReading) Dim ThisFilesContent As String 'read all the text into a variable ThisFilesContent = objTextStream.ReadAll objTextStream.Close Set objTextStream = Nothing Set objTextStream = objFSO.OpenTextFile(ThisFile, ForWriting) 'rewrite it with the new line first objTextStream.WriteLine "SET ANSI_WARNINGS ON " objTextStream.WriteLine "GO" 'append everything from the variable objTextStream.WriteLine ThisFilesContent 'Close the file and clean up objTextStream.Close Set objTextStream = Nothing End If Next Set objFSO = NothingEnd SubActiveX and/or sp_oa methods can also be used to automate your scripting choresSQL DMOhttp://www.sqlteam.com/item.asp?ItemID=11123http://www.databasejournal.com/features/mssql/article.php/2205291You can also automate your sourcesafe activity.VSS Automationhttp://www.michaelis.net/SourceSafe/Faq.htm#_Toc33748946 |
|
|
|
|
|
|
|