Bas files - 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

Import/Export (DTS) and Replication (2000)

Bas files

Author

Topic

DB King
Starting Member

21 Posts

Posted - 2006-04-05 : 04:26:44

hi

I am trying to use DTS to copy databases using the transfer database object and after I select the locations tab EM freezes.

I have 2 instances on the same machine named: RIZVIS and RIZVIS\B

I am copying a DB with one table from RIZVIS to RIZVIS\B to test the process which I will then deploy onto our production box to a backup machine (for those wondering: this procedure is not meant to cover High Availabity).

I just want to copy the databases from one server to the next. Because DTS works I intend to use a bas file to run the same process for all databases. I will write a vbs file which will use the code below. The only thing, I am running the vbs using the following command:

cscript cdw.vbs

but its throwing the following error:

U:\cdw.vbs(2, 21) Microsoft
VBScript compilation error: Expected end of statement

Any ideas ???

DTS package VBS CODE:

Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2

Private Sub Main()
set goPackage = goPackageOld

goPackage.Name = "New Package"
goPackage.Description = "DTS package description"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0

Dim oConnProperty As DTS.OleDBProperty

'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------

Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceConstraint

'------------- a new step defined below

Set oStep = goPackage.Steps.New

oStep.Name = "Copy SQL Server Objects"
oStep.Description = "Copy SQL Server Objects"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy SQL Server Objects"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing

'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------

'------------- call Task_Sub1 for task Copy SQL Server Objects (Copy SQL Server Objects)
Call Task_Sub1( goPackage )

'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
tracePackageError goPackage
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
set goPackage = Nothing

set goPackageOld = Nothing

End Sub

'-----------------------------------------------------------------------------
' error reporting using step.GetExecutionErrorInfo after execution
'-----------------------------------------------------------------------------
Public Sub tracePackageError(oPackage As DTS.Package)
Dim ErrorCode As Long
Dim ErrorSource As String
Dim ErrorDescription As String
Dim ErrorHelpFile As String
Dim ErrorHelpContext As Long
Dim ErrorIDofInterfaceWithError As String
Dim i As Integer

For i = 1 To oPackage.Steps.Count
If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
oPackage.Steps(i).GetExecutionErrorInfo ErrorCode, ErrorSource, ErrorDescription, _
ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError
MsgBox oPackage.Steps(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription
End If
Next i

End Sub

'------------- define Task_Sub1 for task Copy SQL Server Objects (Copy SQL Server Objects)
Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.TransferObjectsTask2
Set oTask = goPackage.Tasks.New("DTSTransferObjectsTask")
oTask.Name = "Copy SQL Server Objects"
Set oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "Copy SQL Server Objects"
oCustomTask1.Description = "Copy SQL Server Objects"
oCustomTask1.SourceServer = "(LOCAL)"
oCustomTask1.SourceUseTrustedConnection = True
oCustomTask1.SourceDatabase = "saj01"
oCustomTask1.DestinationServer = "rizvis\b"
oCustomTask1.DestinationUseTrustedConnection = True
oCustomTask1.DestinationDatabase = "saj01"
oCustomTask1.ScriptFileDirectory = "C:\Program Files\Microsoft SQL Server\80\Tools"
oCustomTask1.CopyAllObjects = True
oCustomTask1.IncludeDependencies = True
oCustomTask1.IncludeLogins = True
oCustomTask1.IncludeUsers = True
oCustomTask1.DropDestinationObjectsFirst = True
oCustomTask1.CopySchema = True
oCustomTask1.CopyData = 1
oCustomTask1.ScriptOption = -2146995969
oCustomTask1.ScriptOptionEx = 4722704
oCustomTask1.SourceTranslateChar = True
oCustomTask1.DestTranslateChar = True
oCustomTask1.DestUseTransaction = False
oCustomTask1.UseCollation = True

goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing

End Sub

Help ???!?!?!?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-05 : 06:51:33

If you can use another solution you'll probably find dmo easier.
see
http://www.nigelrivett.net/DMO/SQL-DMOTransfer.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DB King
Starting Member

21 Posts

Posted - 2006-04-05 : 06:59:41

what is sql-dmo ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources