一个用Wsh来控制SqlServer的Dcom的VBs

王朝c#·作者佚名 2006-12-17

大家知道,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