本文发表在 rolia.net 枫下论坛Public Function BCP(ByVal Server As String, ByVal UserName As _
String, ByVal Password As String, ByVal Database As String, _
ByVal Table As String, ByVal FileName As String, _
Optional Import As Boolean) As Boolean
'**************************************************************
'PURPOSE: Do a SQL Server BulkCopy
'ASSUMPTIONS: You understand what a bulk copy is; otherwise
' consult SQLServer books online
'PARAMETERS: Server: ServerName
' UserName: UserName for Server
' Password: Password for Server
' Database: Name of Database
' Table: Table Name or SQL Statement for BCP
' FileName: File Name to Import from or Export To
' Import: If set to true, works as an import
' ("in") Otherwise, defaults to export
' ("out")
'RETURNS: True if successful, false otherwise
'EXAMPLE:
'BCP "MySQLServer", "username", "pwd", "MyDataBase", "MyTable", _
"C:\MyTable.out"
'
'Bulk Copies all rows of MyTable in MyDatabase on MySQLServer
'to a file named C:\MyTable.out
'REQUIRES: A reference to Microsoft SQLDMO Object Library
'LIMITATIONS: -- Assumes SQL Server Authentication as opposed
' to NT integrated authentication.
'
' -- Only allows full tables to be
' imported/exported i.e., you cannot specify
' where criteria
'
' -- Uses mostly default options of bcp.
' If you want to set more options, the BulkCopy
' object permits this. Refer to documentation or
' object browser
'***********************************************************
Dim objServer As New SQLDMO.SQLServer
Dim objBCP As New SQLDMO.BulkCopy
Dim objDB As SQLDMO.Database
'if file doesn't exist and it's an import,
'don't waste time
If Import = True And Dir(FileName) = "" Then Exit Function
On Error GoTo ErrorHandler
objServer.Connect Server, UserName, Password
objServer.EnableBcp = True
Set objDB = objServer.Databases(Database)
With objBCP
.DataFilePath = FileName
.DataFileType = SQLDMODataFile_UseFormatFile
'Below speeds things up
'but does not log the bulk copy operation
'comment out if this is not what you
'desire
.UseBulkCopyOption = True
End With
If Import Then
objDB.Tables(Table).ImportData objBCP
Else
objDB.Tables(Table).ExportData objBCP
End If
BCP = True
ErrorHandler:
Set objBCP = Nothing
Set objServer = Nothing
End Function更多精彩文章及讨论，请光临枫下论坛 rolia.net
String, ByVal Password As String, ByVal Database As String, _
ByVal Table As String, ByVal FileName As String, _
Optional Import As Boolean) As Boolean
'**************************************************************
'PURPOSE: Do a SQL Server BulkCopy
'ASSUMPTIONS: You understand what a bulk copy is; otherwise
' consult SQLServer books online
'PARAMETERS: Server: ServerName
' UserName: UserName for Server
' Password: Password for Server
' Database: Name of Database
' Table: Table Name or SQL Statement for BCP
' FileName: File Name to Import from or Export To
' Import: If set to true, works as an import
' ("in") Otherwise, defaults to export
' ("out")
'RETURNS: True if successful, false otherwise
'EXAMPLE:
'BCP "MySQLServer", "username", "pwd", "MyDataBase", "MyTable", _
"C:\MyTable.out"
'
'Bulk Copies all rows of MyTable in MyDatabase on MySQLServer
'to a file named C:\MyTable.out
'REQUIRES: A reference to Microsoft SQLDMO Object Library
'LIMITATIONS: -- Assumes SQL Server Authentication as opposed
' to NT integrated authentication.
'
' -- Only allows full tables to be
' imported/exported i.e., you cannot specify
' where criteria
'
' -- Uses mostly default options of bcp.
' If you want to set more options, the BulkCopy
' object permits this. Refer to documentation or
' object browser
'***********************************************************
Dim objServer As New SQLDMO.SQLServer
Dim objBCP As New SQLDMO.BulkCopy
Dim objDB As SQLDMO.Database
'if file doesn't exist and it's an import,
'don't waste time
If Import = True And Dir(FileName) = "" Then Exit Function
On Error GoTo ErrorHandler
objServer.Connect Server, UserName, Password
objServer.EnableBcp = True
Set objDB = objServer.Databases(Database)
With objBCP
.DataFilePath = FileName
.DataFileType = SQLDMODataFile_UseFormatFile
'Below speeds things up
'but does not log the bulk copy operation
'comment out if this is not what you
'desire
.UseBulkCopyOption = True
End With
If Import Then
objDB.Tables(Table).ImportData objBCP
Else
objDB.Tables(Table).ExportData objBCP
End If
BCP = True
ErrorHandler:
Set objBCP = Nothing
Set objServer = Nothing
End Function更多精彩文章及讨论，请光临枫下论坛 rolia.net