使用VB将SQL SERVER 的脚本导出

'调用方法： TableExport "表名","服务器名","用户名","密码","数据库名"

Public Sub TableExport(strTableNameSrc As String, _

strSvrNameSrc As String, _

strUserNameSrc As String, _

strUserPwdSrc As String, _

strDbNameSrc As String)

Dim strSql As String, nSqlDmoOption

Dim oSqlDmo As New SQLDMO.Application

Dim oSvrSrc As New SQLDMO.SQLServer2

Dim oTbSrc As New SQLDMO.Table2

Dim oBulkCpy As New SQLDMO.BulkCopy2

Dim oDmo As SQLDMO.Application

Set oDmo = New SQLDMO.Application

Set oSvrSrc = New SQLDMO.SQLServer2

'参数：SQL Server Name,User ID,Password

oSvrSrc.Connect strSvrNameSrc, strUserNameSrc, strUserPwdSrc '源服务器

Set oTbSrc = oSvrSrc.Databases(strDbNameSrc).Tables(strTableNameSrc) '源服务器数据库db1中的表tb1

'在目的服务器的数据库中建新的表

nSqlDmoOption = SQLDMOScript_Default + _

SQLDMOScript_DRI_All + _

SQLDMOScript_Drops + _

SQLDMOScript_Indexes + _

SQLDMOScript_OwnerQualify + _

SQLDMOScript_Triggers

'strSQL生成结构脚本

strSql = oTbSrc.Script(nSqlDmoOption, , , SQLDMOScript2_Default)

Set oBulkCpy = New SQLDMO.BulkCopy2

oBulkCpy.UseBulkCopyOption = True

oBulkCpy.DataFilePath = "c:\temp.txt"

oTbSrc.ExportData oBulkCpy '导出数据

Set oTbSrc = Nothing

Set oBulkCpy = Nothing

oSvrSrc.Disconnect

Set oSvrSrc = Nothing

Set oDmo = Nothing

End Sub