INF: How to Use the SQL Server DMO Objects from VBScript (214820)

SUMMARY

This article contains a basic sample of how you can use SQL Server Distributed Management Objects (SQL-DMO) from within VBScript.

VBScript has some basic differences from Visual Basic, which must be understood in order to successfully construct error-free VBScript code.

Any type library constant that you want to use must be explicitly declared in the script. If you do not use Option Explicit, then all references to constants that are not explicitly declared are initialized as new variables. This may cause unexpected behavior as all variables are initialized to zero(0).
All objects must be created using Late Binding (i.e. CreateObject). Therefore, Events from the created objects are not supported.
Control Flow calls and Error Handling are limited.

MORE INFORMATION

Install a VBScript runtime environment before executing the sample below. You can also use the following links to obtain more information about VBScript or to download the runtime environment necessary to run this VBScript sample.

For more information about differences between Visual Basic and VBScript, refer to the following:
http://msdn.microsoft.com/scripting/default.htm?/scripting/vbscript/default.htm

For more information about the Windows Script Host, refer to the following:
http://msdn.microsoft.com/scripting/default.htm?/scripting/windowshost/default.htm

NOTE: The following code does a simple export from the "authors" table, then creates and imports the data into the "authorsnew" table, using the bulk copy program (BCP) object. Also, the sample assumes that the code is executing on the same computer that SQL Server is installed on.

' turn on this to trap any syntax or declaration errors
Option Explicit

' any used constants from the type library must be explicitly declared
Const SQLDMODataFile_TabDelimitedChar = 2
Const SQLDMOBCPDataFile_Char = 1

' beginning of routine
Dim oServer ' the SQL Server object
Dim oDatabase ' the target database to use
Dim oBCP ' the BCP object
Dim nRows ' the number of rows returned from bcp
Dim strTableSQL 

strTableSQL = "SELECT * INTO authorsnew FROM authors WHERE 1=0"

Set oServer = CreateObject("SQLDMO.SQLServer")
Set oBCP = CreateObject("SQLDMO.BulkCopy")

oServer.EnableBcp = True
oServer.Connect ".", "sa" ' login to the local server

Set oDatabase = oServer.Databases("pubs")

oBCP.ColumnDelimiter = vbTab
oBCP.DataFilePath = "C:\temp\authors.bcp" 'Modify as necessary
oBCP.DataFileType = SQLDMODataFile_TabDelimitedChar
oBCP.ImportRowsPerBatch = 1000
oBCP.MaximumErrorsBeforeAbort = 1
oBCP.RowDelimiter = vbCrLf
oBCP.ServerBCPDataFileType = SQLDMOBCPDataFile_Char
oBCP.UseExistingConnection = True

nRows = oDatabase.Tables("authors").ExportData(oBCP)

If (nRows > 0) Then
        If Not oDatabase.DBOption.SelectIntoBulkCopy Then
		oDatabase.DBOption.SelectIntoBulkCopy = True
        End If

        ' Just create the table using T-SQL syntax
        oDatabase.ExecuteImmediate(strTableSQL)

        ' since we created the table outside DMO, we must refresh 
        ' the tables collection
	oDatabase.Tables.Refresh

        ' start the importing process
        oDatabase.Tables("authorsnew").ImportData(oBCP)
End If