SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Generating SQL Scripts
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CtrlAltDel
Starting Member

Australia
17 Posts

Posted - 10/30/2003 :  09:51:18  Show Profile  Visit CtrlAltDel's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1631 Posts

Posted - 10/30/2003 :  21:29:45  Show Profile  Reply with Quote
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 = Nothing

End Sub


ActiveX and/or sp_oa methods can also be used to automate your scripting chores

SQL DMO
http://www.sqlteam.com/item.asp?ItemID=11123
http://www.databasejournal.com/features/mssql/article.php/2205291

You can also automate your sourcesafe activity.

VSS Automation
http://www.michaelis.net/SourceSafe/Faq.htm#_Toc33748946

Edited by - ehorn on 10/30/2003 22:45:59
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000