即使全部打进包,运行DTS包时仍需要SQL SERVER CLIENTS工具,如果你在没有该工具的情况实现数据库的部分功能,请用SQL-DMO对象(参考Books online),建议安装客户端工具.在有该工具的开发机上(如SQL2000SERVER),在VisualBasic打开该项目,选择Project,References,就会出现References对话框,选中Microsoft SQL DMO ObjectLibrary复选框再点击OK。在VisualBasic项目中增加对SQL-DMO的引用后,用户即可使用VisualBasic的语法访问SQL-DMO的可用对象,来创建对象、调用方法和设置属性。下例显示用户如何在SQLServer中使用BulkCopy对象导入或导出数据。FileImportExport例程提供一个通用接口,以导入或导出一个标签限定的文件到指定表。例程的形式以两个Label控件(lblNumRows和lblStatus)在BukCopy对象的RowsCopied事件中更新。
用户能通过点击cmdAbort命令按钮中止这一操作。在将它运用于读者自己的数据库之前,注意在示例中当指定一个导入操作时,表中现有数据会先被删除。从SQL Server 有中导入或导出数据示列,供参考Private WithEvents mobjBC As SQLDMO.BulkCopy
Public Enum IEX_ENUM
iexImport
iexExport
End Enum
Private Sub cmdAbort_Click()
' abort bulkcopy operation
m o b j B C . A b o r t
End Sub
Private Sub mobjBC_RowsCopied(ByVal Message As String, ByVal Rows As Long)
' update status as bulkcopy progresses.
lblNumRows.Caption = Rows
lblStatus.Caption = Message
End Sub
Public Sub FileImportExport(ByVal vstrDB As String, _
ByVal vstrTable As String, _
ByVal vstrFile As String, _
ByVal vImpExpFlag As IEX_ENUM)
Dim objSvr As New SQLDMO.SQLServer
' connect to server
objSvr.Connect"(local)","sa",""
' set bulkcopy object properties
Set mobjBC = New SQLDMO.BulkCopy
With mobjBC
.DataFileType=SQLDMODataFile_TabDelimitedChar
.ColumnDelimiter = vbTa b
.RowDelimiter = vbCrLf
.DataFilePath = vstrFile
End Wi t h
With objSvr.Databases(vstrDB).Tables(vstrTable)
If vImpExpFlag = iexExport Then
' if exporting then...
' delete existing file
Kill vstrFile
' export data
.ExportData mobjBC
E l s e
' if importing then...
' set BC options supporting non-logged operation
mobjBC.SuspendIndexing = Tr u e
mobjBC.UseBulkCopyOption = Tr u e
' delete existing data from table (non-logged)
. Tr u n c a t e D a t a
' import data
.ImportData mobjBC
End If
End Wi t h
objSvr.DisConnect
Set objSvr = Nothing
End Sub
用户能通过点击cmdAbort命令按钮中止这一操作。在将它运用于读者自己的数据库之前,注意在示例中当指定一个导入操作时,表中现有数据会先被删除。从SQL Server 有中导入或导出数据示列,供参考Private WithEvents mobjBC As SQLDMO.BulkCopy
Public Enum IEX_ENUM
iexImport
iexExport
End Enum
Private Sub cmdAbort_Click()
' abort bulkcopy operation
m o b j B C . A b o r t
End Sub
Private Sub mobjBC_RowsCopied(ByVal Message As String, ByVal Rows As Long)
' update status as bulkcopy progresses.
lblNumRows.Caption = Rows
lblStatus.Caption = Message
End Sub
Public Sub FileImportExport(ByVal vstrDB As String, _
ByVal vstrTable As String, _
ByVal vstrFile As String, _
ByVal vImpExpFlag As IEX_ENUM)
Dim objSvr As New SQLDMO.SQLServer
' connect to server
objSvr.Connect"(local)","sa",""
' set bulkcopy object properties
Set mobjBC = New SQLDMO.BulkCopy
With mobjBC
.DataFileType=SQLDMODataFile_TabDelimitedChar
.ColumnDelimiter = vbTa b
.RowDelimiter = vbCrLf
.DataFilePath = vstrFile
End Wi t h
With objSvr.Databases(vstrDB).Tables(vstrTable)
If vImpExpFlag = iexExport Then
' if exporting then...
' delete existing file
Kill vstrFile
' export data
.ExportData mobjBC
E l s e
' if importing then...
' set BC options supporting non-logged operation
mobjBC.SuspendIndexing = Tr u e
mobjBC.UseBulkCopyOption = Tr u e
' delete existing data from table (non-logged)
. Tr u n c a t e D a t a
' import data
.ImportData mobjBC
End If
End Wi t h
objSvr.DisConnect
Set objSvr = Nothing
End Sub
实时错误:'-2147221499(80040005)'
[SQL-DMO]代码执行异常错误:EXCEPTION_ACCESS_VIOLATION代码如下:
Option Explicit
Private WithEvents mobjBC As SQLDMO.BulkCopy
Public Enum IEX_ENUM
iexImport
iexExport
End Enum
Private Sub cmdAbort_Click()
' abort bulkcopy operation
mobjBC.Abort
End SubPrivate Sub Form_Load()
Call FileImportExport("JDPY", "CP", "C:\TEST2.XLS", iexExport)
End SubPrivate Sub mobjBC_RowsCopied(ByVal Message As String, ByVal Rows As Long)
' update status as bulkcopy progresses.
lblNumRows.Caption = Rows
lblStatus.Caption = Message
End Sub
Public Sub FileImportExport(ByVal vstrDB As String, _
ByVal vstrTable As String, _
ByVal vstrFile As String, _
ByVal vImpExpFlag As IEX_ENUM)
Dim objSvr As New SQLDMO.SQLServer' connect to server
objSvr.Connect "(local)", "sa", ""
' set bulkcopy object properties
Set mobjBC = New SQLDMO.BulkCopyWith mobjBC
.DataFileType = SQLDMODataFile_TabDelimitedChar
.ColumnDelimiter = vbTab
.RowDelimiter = vbCrLf
.DataFilePath = vstrFile
End WithWith objSvr.Databases(vstrDB).Tables(vstrTable) If vImpExpFlag = iexExport Then
' if exporting then...
' delete existing file
Kill vstrFile
' export data
.ExportData mobjBC
Else
' if importing then...
' set BC options supporting non-logged operation
mobjBC.SuspendIndexing = True
mobjBC.UseBulkCopyOption = True
' delete existing data from table (non-logged)
.TruncateData
' import data
.ImportData mobjBC
End If
End WithobjSvr.DisConnect
Set objSvr = NothingEnd Sub
try a simple sampleBacking Up A Database
Create a stored procedure mydbackup as BACKUP mydb to DISK='c:\mtdb.bak' In SQLDMO, an example:
Set srv=Server.CreateObject("SQLDMO.SQLServer")
srv.Connect "servername", "username", "password"
Set bak = Server.CreateObject("SQLDMO.Backup")
bak.Database="Northwind"
bak.Devices=Files
bak.Files="e:\nwind.bak"
bak.SQLBackup srv
Response.write "DONE!"
Restoring A Database
In a stored procedure: RESTORE DATABASE Northwind FROM DISK='c:\nwind.bak' [WITH ] In SQLDMO you use the restore object (check BOL for full syntax): Set srv=Server.CreateObject("SQLDMO.SQLServer")
srv.Connect "servername", "username", "password"
Set rest=Server.CreateObject("SQLDMO.Restore")
rest.Action=0 ' full db restore
rest.Database="Northwind"
rest.Devices=Files
rest.Files="c:\nwind.bak"
rest.ReplaceDatabase=True 'Force restore over existing database
rest.SQLRestore srvresponse.write "Done!"set rest=nothing
set srv=nothing
可参考
http://www.microsoft.com/china/msdn/library/techart/odc_modmsde1.txt