Microsoft KB Archive/214820

= INF: How to Use the SQL Server DMO Objects from VBScript =

Article ID: 214820

Article Last Modified on 8/8/2007

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q214820



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://msdn2.microsoft.com/en-us/library/ms950396.aspx

'''

For more information about the Windows Script Host, refer to the following:

'''http://msdn2.microsoft.com/en-us/library/ms950396.aspx

'''

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

Additional query words: script console administration vb wsh

Keywords: kbhowto kbinfo KB214820

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.