大家知道,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:AssetChecksqltextassetcheck.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.Closeend sub'-----------------------------------------------------------------------------------public sub ClearSqlDmo() Set fso = Nothing Set goSQLServer = Nothingend sub