|
大家知道,wsh(windows script host)在windows平台下是用来代替bat文件的,以其灵活,功能强大许多人都用来处理自己的日常事物,但在调用excel,sqlerver方面可能不是太清楚了,今天这个例子就是 用vbscript来调用Dcom来生成数据库或删除数据库: 可以先建一个以.vbs结尾的文件,靠下面的东西到里面进去,双击就可以执行了,只要把里面的sql脚步的路径和Sqlserver的密码和账户改下就能用了 Dim str,ff,i ,intt,strtmp ''''定义普通变量
Dim goSQLServer,oDatabase ''''定义Sql_Dmo对象变量
Dim fso ''''定义文件对象 call main()
''''==============================================================================
''''==============================================================================
public sub main()
InitSqlDmo "." DropDatabase "Assetcheck" CreateDatabase "assetcheck" ''''取得数据库
Set oDatabase = goSQLServer.Databases("assetCheck")
''''msgbox oDatabase.PrimaryFilePath ''''执行大量的Sql文本文件
ExecuteSqlFromFile "D:\AssetCheck\sqltext\assetcheck.sql" ClearSqlDmo If Err.Number <> 0 Then MsgBox "发生错误"
MsgBox "脚本生成完成"
end sub
''''-------------------------------------------------------------------------------
''''初始化连接对象
public sub InitSqlDmo(aServerName)
Set fso = CreateObject("Scripting.FileSystemObject")
Set goSQLServer = CreateObject("SQLDMO.SQLServer")
goSQLServer.Connect aServerName, "sa", ""
end sub
''''-----------------------------------------------------------------------------
''''创建数据库
public sub CreateDatabase(astrDataBaseName )
strtmp = " create DataBase " & astrDataBaseName
goSQLServer.ExecuteImmediate strtmp
end sub
''''删除数据库
public sub DropDatabase(astrDataBaseName )
strtmp = " Drop DataBase " & astrDataBaseName
goSQLServer.ExecuteImmediate strtmp
end sub
''''------------------------------------------------------------------------------
''''执行脚本
public sub ExecuteSqlFromFile(astrExecuteSqlFile )
Set ff = fso.OpenTextFile(astrExecuteSqlFile)
Do While ff.AtEndOfStream <> True
strtmp = ff.ReadLine
str = str & vbCrLf & strtmp
i = i + 1
If i >= 1500 And strtmp = "" Then
i = 1
oDatabase.ExecuteImmediate (str)
str = ""
End If
Loop If Trim(str) <> "" Then
oDatabase.ExecuteImmediate (str)
End If ff.Close
end sub
''''-----------------------------------------------------------------------------------
public sub ClearSqlDmo()
Set fso = Nothing
Set goSQLServer = Nothing
end sub
| 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 