Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

By submitting your information, you agree that databasejournal.com may send you databasejournal offers via email, phone and text message, as well as email offers about other products and services that databasejournal believes may be of interest to you. databasejournal will process your information in accordance with the Quinstreet Privacy Policy.

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

SQL Scripts & Samples

Posted Oct 11, 2002

Replication Script

By DatabaseJournal.com Staff

This VBscript scripts publications on all SQL Servers within your network. It's useful for organizations the have implemented large amounts of replication.

Set up is as follows:

  1. A "Servers" table containing all the SQL Servers in the organization should be created.

    ServerName varcha(XX)
    ScriptDDL. Tinyint (values 0 or 1) 1 = Script, 0 = Do not Script

  2. The connection string to the server where you created the Servers table should be modified.

  3. The location for the directory structure should be modified to reflect a valid location.

  4. Copy and paste the script into a DTS package's ActiveX task and schedule it.

The following directory structure and scripts will be created (where servername is read from the servers table and PublicationName is the name of the Publication):

Replication\ServerName\Create PublicationName (1 for each publication per database)
Replication\ServerName\Delete PublicationName (1 for each publication per database)
Replication\ServerName\Create All Publications (1 file per server to recreate all publications)
Replication\ServerName\Drop All Publications (1 file per server to drop all publications)

Replication\ServerName2\Create PublicationName (1 for each publication per database)
Replication\ServerName2\Delete PublicationName (1 for each publication per database)
Replication\ServerName2\Create All Publications (1 file per server to recreate all publications)
Replication\ServerName2\Drop All Publications (1 file per server to drop all publications)

Replication\MMDDYYYY_Log.txt (log file stating what was created)

Author: Janet Keith

'  Visual Basic ActiveX Script
Option Explicit

'Cursor Type Enum
CONST adOpenForwardOnly = 0
CONST adOpenKeyset = 1
CONST adOpenDynamic = 2
CONST adOpenStatic = 3

'Lock Type Enum
CONST adLockReadOnly = 1
CONST adLockPessimistic = 2
CONST adLockOptimistic = 3
CONST adLockBatchOptimistic = 4

CONST forappending = 8

Dim oServer 
Dim oDatabase 
Dim TransPub 
Dim rs 
Dim fld 
Dim strFolder 
Dim oFSTR 
Dim tfLog
Dim strToday
Dim oFSO
Dim strPath

'On Error Resume Next

'Create File System object
Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oServer = CreateObject("SQLDmo.SqlServer")
oServer.LoginSecure = True

'Create a table named servers with 2 columns.  ServerName and ScriptDDL. 

SQL = "select servername from servers where scriptddl = 1"

Set rs = CreateObject("ADODB.Recordset")
rs.Open sql, "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security 
    Info=False;Initial Catalog=SQLServers;Data Source=MyServer", adOpenForwardOnly, 

Do While (Not rs.EOF)
     oServer.Connect rs.Fields(0).Value
     strFolder = "c:\dba\scripts\replication\" & rs.Fields(0).Value
     strToday = Month(Now()) & Day(Now()) & Year(Now()) 
     strPath = "c:\dba\scripts\replication\" & strToday & "_log.txt"

     If Not oFSO.FileExists(strPath) Then
     	 Set tfLog = oFSO.CreateTextFile(strPath, True)

     End If

	 Set tfLog = oFSO.OpenTextFile(strPath, ForAppending, True)

	 If Not oFSO.FolderExists(strFolder) Then
   	      oFSO.CreateFolder (strFolder)
   	      If oFSO.FileExists(strFolder & "\Create All Publications.sql") Then
     	           oFSO.DeleteFile (strFolder & "\Create All Publications.sql")
   	      End If
   	      If oFSO.FileExists(strFolder & "\Drop All Publications.sql") Then
     		   oFSO.DeleteFile (strFolder & "\Drop All Publications.sql")
   	      End If
	 End If

	For Each oDatabase In oServer.Databases
		strToday = FormatDateTime(Now(),vbShortDate) & " " & FormatDateTime(Now(),vbLongTime)
		tfLog.WriteLine (strToday & " " & strFolder)

		'SQLDMORepScript_Creation = 16384
		'SQLDMORepScript_Deletion = 32768
		If Not oDatabase.SystemObject Then
		     If Not oDatabase.DBOption.ReadOnly = True Then
		          For Each TransPub In oServer.Replication.ReplicationDatabases(oDatabase.Name).TransPublications
			  strToday = FormatDateTime(Now(),vbShortDate) & " " & FormatDateTime(Now(),vbLongTime)
			  tfLog.WriteLine (strToday & " " & strFolder & "\Create " & TransPub.Name & ".sql")
			  TransPub.Script 16384, strFolder & "\Create " & TransPub.Name & ".sql"
			  TransPub.Script 16384 + 8192, strFolder & "\Create All Publications.sql"            
			  TransPub.Script 32768, strFolder & "\Delete " & TransPub.Name & ".sql"
			  TransPub.Script 32768 + 8192, strFolder & "\Drop All Publications.sql"   
		     End If
		End If

'Record Errors
If Err.Number > 0 Then
	strToday = FormatDateTime(Now(),vbShortDate) & " " & FormatDateTime(Now(),vbLongTime)
	'MsgBox(strToday & " " & Err.Description & vbCrLf & Err.Number)
    tfLog.WriteLine (strToday & " " & Err.Description & vbCrLf & Err.Number)
End If

'Clean up
Set RS = Nothing
Set oServer = Nothing
Set oFSO = Nothing

Back to Database Journal Home

SQL Scripts & Samples Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM