SQL to Sourcesafe Scripter v2.0 (SQLScripter) for Windows
|
Table Of Contents
Overview, what is SQLScripter?
SQLScripter is a 32 bit windows application designed as a tool that automatically generates SQL scripts for Microsoft SQL Server databases, and stores them in file system or under Microsoft SourceSafe version control. Although recent versions of MS SQL Server support version control on stored procedures through SourceSafe integration, the procedure requires modifications to be done exclusively from within a MS Visual Studio interface, and does not apply to other schema information such as table or view scripts. SQLScripter proposes a solution where all development work is done as before on the real server, and all schema objects are scripted into SourceSafe at predefined intervals. Interested parties are notified of changes by automatic e-mails where created, altered or dropped objects are reported. The individual database programmer may benefit from SQLScripter in keeping a personal history of versions. However the real value of SQLScripter comes out in collaboration, where a possibly multi-site group of programmers are working on related databases. If the volume of shared development is very high, a project may indeed call for a disciplined old-fashioned check-out-check-in mode of database operations to avoid collisions. Otherwise, which is the case for most mature projects in maintenance, this may be overkill. A daily run of SQLScripter may be enough to make everyone aware of any changes, and still be able roll back if necessary. Change History
Installation,
how to setup?
The latest release setup package of SQLScripter can be downloaded from
here. Operation,
how it works?
The program processes the configuration file in sequence. For each each
matching object in SQL Server, it first generates the object's SQL script into
its temporary files directory specified in the configuration. It then compares
this file to the version in SourceSafe, internally noting the the file as
changed, unchanged or new. Then the SourceSafe version is checked-out (without
getting a local copy) and the scripted version is checked in. When all objects
are processed like this, the program recurses in SourceSafe to find files that
match the configuration but have not been scripted in this run. These files are
assumed to have been dropped, and are renamed with the prefix For the first run, since no objects yet exist in SourceSafe, all objects will
be noted as new and according to database e-mail recipients may receive
huge reports. To prevent this, see the
The log file contains entries for the program start, termination, summary
info and any error messages. Usage,
how to use?
SQLScripter is a batch tool, primarily intended for scheduled use. Based on a configuration file, it starts scripting as soon as it is run. The configuration file is in INI format and needs to be edited manually. See the next section for details. The program can be run without parameters:
In this case it looks for the configuration file Otherwise a configuration file can be specified manually:
If there is an error in loading the configuration file, an error log will be created in the executable directory. Provided that the configuration is normally loaded and it specifies a valid log file location, any other errors are logged to that file without visual prompting. Note that the notification e-mails also contain the error entries written to the log file. Typical usage is scheduling SQLScripter as a nightly job. If scripting multiple servers in sequence takes too long, the batch job can be divided into multiple configuration files (e.g. one for each SQL Server) and scheduled as independent jobs to run in parallel. In that case, be sure to specify different log files and temporary directories to avoid concurrency conflicts. Configuration,
how to configure?
Configuration INI file section and key details:
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Sample configuration file is as follows:
Sample.INI:
[GLOBAL]
CharSet=iso-8859-9
[SQL2SS_Scripter]
tempfolder="%AppPath%"
logfile="%AppPath%\Log\Log_%DateTime%.txt"
DefaultExt=SQL
permdatadir="%AppPath%\Data"
permdatakeep="1"
AfterDbSrvTrg=""
AfterCatTrg=""
[AfterTrigger]
;Run1=test.bat
[scc]
scccheck=0
sccget=0
sccsave=0
sccname=LocalVSS
sccpath="\\server1\vssdb\srcsafe.ini"
sccroot="$/SQL"
sccuser="SQLScripter1_vss"
sccpass=""
[SQL_01]
ignore=0
dbsrvname="TESTSQL1"
Login="SQLScripter"
dbsrvpass=""
objects="user"
Noreport=0
[SQL_02]
ignore=0
dbsrvname="TESTSQL2"
Login="SQLScripter"
dbsrvpass=""
objects="user"
Noreport=0
DbName_01=Pubs
[Mail_01]
maildsb=0
smtpsrv="smtp.abc.com"
smtpuser=""
smtppass=""
From="admin@abc.com"
ReplyTo=""
Tag="PREFIXTAG"
To1=admin@abc.com
To2=sqloperator@abc.com
SkipOthersIfOk=1
SendMethod=0
ItemFormat=%ObjType% <A href="http://www.abc.com/ss2web/ss2web.asp?p=%Spec%">%DB%.%OwnerExc%.%Name2Show%
| SQL2SS.zip (3.5 MB) | Windows (MSI) Installer package, just MSI file |
| SQL2SSwSetup.zip (6.5 MB) | Full Setup with Installer |
Some prerequisites:
Windows Update, Do
not forget to update your Operating System
Installation of this product requires the Windows Installer version 2.0+. If
you are running Windows 95, 98, or Windows NT, you may need to download the
proper installer for your system, and run it before you can install this
product.
For Windows 98:
http://www.microsoft.com/downloads/release.asp?ReleaseID=32831
For Windows NT:
http://www.microsoft.com/downloads/release.asp?ReleaseID=32832
For Windows 2000/XP/2003:
Windows Installer 3.0 Redistributable
If you have problems, suggestions, do not hesitate to contact me from ssubasi@sbscon.com