[问题求助] VBS连接 mssql 数据库为什么会报错“下列工作站在这台服务器上有会话并打开了文件”

CrLf

想用 vbs 连接 mssql 数据库
但运行如下 vbs 报错 “下列工作站在这台服务器上有会话并打开了文件”，可是我没找到本机上有此类会话，请教下各位大牛知道这是肿么回事？

Set DB = CreateObject("ADODB.Connection")  
DB.Open "Provider=SQLOLEDB.1;Data Source=source;uid=name;pwd=password;database=master;" 
复制代码

yu2n

回复 1# CrLf
最好贴全代码，暂时看不出来有什么问题。

举个栗子：使用 VBS 在 SQL2000、SQL2005 中查询 Users 表中的 SID 字段

Function GET_SID(ByVal strUserName)

  On Error Resume Next
  strDBServer = "sql01.abc.com"
  strDBName = "admin_db"
  strDBUserName = "admin_db"
  strDBPassword = "P@ssW0rd"
  strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & strDBUserName & ";Password=" & strDBPassword & ";Initial Catalog=" & strDBName & ";Data Source=" & strDBServer
  sSQL = "SELECT  TOP 1  [SID]  FROM  [Users]  WHERE  [UserName]='" & strUserName & "'"

  Set conn  = CreateObject("ADODB.Connection")
  Set rs = CreateObject("ADODB.RecordSet")

  conn.ConnectionString = strConn

  If Not Err.Number = 0 Then Exit Function
  conn.Open
  rs.Open sSQL, conn, 3, 3

  If Not Err.Number = 0 Then Exit Function
  If rs.Recordcount = 1 Then
    strSID = rs("SID")
  Else
    strSID = ""
  End If

  rs.Close
  conn.Close
  Set rs=Nothing
  Set conn = Nothing

Exit Function
复制代码

USERS 表如下

SID   USERNAME  PASSWORD
uuid001  Yu2n  P@ssW0rd
...
复制代码

CrLf

回复 2# yu2n


    谢谢指点！回头到单位试试

yu2n

回复 3# CrLf
如果要容错做的比较好的话，可考虑RS记录集与ADO数据库连接的验证：

If (Not objRS Is Nothing) Then If (Not objRS.State = 1) Then ''Do Something
If (Not objADOConn Is Nothing) Then If (Not objADOConn.State = 1) Then ''Do Something
复制代码

再贴一个正在用的，自行修改Conn连接、SQL语句、：

Function GET_Password(ByVal strPCName)
  On Error Resume Next
  Dim strConn, strSQL, objADOConn, objRS, str
  strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=DBUSER1;Password=Abcd1234;Initial Catalog=ADMIN_DB;Data Source=SQL01.ABC.COM"
  strSQL = "SELECT TOP 1 [pwd1] FROM [password] WHERE [name]='" & strPCName & "'"
  Set objRS = CreateObject("ADODB.RecordSet")
  Set objADOConn  = CreateObject("ADODB.Connection")
  objADOConn.ConnectionString = strConn
  objADOConn.Open
  If (Not objADOConn Is Nothing) Then If (Not objADOConn.State = 1) Then Exit Function
  objRS.Open strSQL, objADOConn, 3, 3
  If (Not objRS Is Nothing) Then If Not objRS.State = 1 Then objADOConn.Close : Exit Function
  If objRS.Recordcount = 1 Then str = objRS("pwd")
  If (Not objRS Is Nothing) Then If (Not objRS.State = 1) Then objRS.Close
  If (Not objADOConn Is Nothing) Then If (Not objADOConn.State = 1) Then objADOConn.Close
  Set objRS = Nothing
  Set objADOConn = Nothing
  GET_Password = str
End Function
复制代码