VBS 连接异地MS SQL 数据库

SQLServer服务器配置

---

SQLServer服务器已启用TCP/IP

SQL端口号1433

防火墙添加端口号

实例

---

On Error Resume Next
'连接对象
Dim oCon
'数据库连接字符串
Dim strCon
'结果集对象
Dim oRs
'执行对象
Dim oCmd
'查询语句
Dim strSQL
'结果集行数
Dim RsCount,IP,Port,PWD,usr,DBName
Set oCon = CreateObject("ADODB.Connection")

strCon = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;PassWord=123456;Initial Catalog=TMP;Data Source=192.168.1.73,1433"

oCon.ConnectionString = strCon
oCon.CursorLocation = 3
oCon.Open
Set oRs = CreateObject("ADODB.Recordset")
Set oCmd = CreateObject("ADODB.Command")
oCmd.CommandType = 1 

strSQL = "select * from UserInfo"
With oCmd
 .ActiveConnection = oCon
 .CommandText = strSQL
End With
'结果集
Set oRs  = oCmd.Execute
'获取查询条件的结果集
RsCount = oRs.RecordCount
MsgBox RsCount

'断开结果集
oRs.Close
'清空结果集
Set oRs = Nothing
'断开数据库连接
oCon.Close
'清空连接对象
Set oCon = Nothing'断开结果集