直接执行指定SQL文件（参考整理）

参考转载 http://blog.yesky.com/Blog/hunter/archive/2005/03/25/95357.html

1)      如何执行 sql文件

exec master.dbo.xp_cmdshell ' osql -U @username -P @password -i @sqlname'

@username 数据库用户

@password 数据库用户口令

@sqlname    sql文件目录名称，例如：’f://gps_insert.sql’

例如：

exec master.dbo.xp_cmdshell ' osql -U sa -P sa -i f://gps_insert.sql'

 

2)       VB如何执行sql文件 ,读取文件，执行     

Public Sub exesqlfile(cn as ADODB.Connection,sqlFile As String)
Dim strSql As String, strTmp As String

Open sqlFile For Input As #1

strSql = ""

Do While Not EOF(1)

          Line Input #1, strTmp

          If UCase$(strTmp) = "GO" Then

              cn.Execute strSql

              strSql = ""

          Else

              strSql = strSql & strTmp & vbCrLf

          End If

Loop

      If strSql <> "" Then cn.Execute strSql
      Close #1

End Sub

3)      VB如何执行sql文件 ，直接执行存储过程xp_cmdshell

Public Sub exesqlfile (cn as ADODB.Connection,sqlFile As String)
Dim sql as string

sql="master.dbo.xp_cmdshell '

osql -U username -P  password -i " & sqlFile  &  " ' "

cn.execute sql

End Sub