Generating SQL Scripts - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Generating SQL Scripts

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 = 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources