SQLOLE.SQLServer 是 SQL Server 專屬的自動化物件模型,不同於通用的 ADO,它能直接操作 SQL Server 的伺服器層級物件:資料庫、資料表、檢視表、預存程序等結構描述資訊,適用於管理工具開發、結構同步、自動化部署等場景。
SQLOLE.SQLServer (Server)
└─ Databases (Database Collection)
├─ Tables (Table Collection)
├─ Views (View Collection)
├─ StoredProcedures (StoredProcedure Collection)
└─ ... 其他物件集合
<%@ LANGUAGE = VBScript %>
<%
On Error Resume Next
Dim oSQLServer, strServer, strLogin, strPwd
Set oSQLServer = CreateObject("SQLOLE.SQLServer")
strServer = "YourServerName" ' 或 "(local)"
strLogin = "sa"
strPwd = "your_password"
oSQLServer.Connect strServer, strLogin, strPwd
' 列出所有使用者資料庫
For Each SQLDB In oSQLServer.Databases
If Not SQLDB.SystemObject Then
Response.Write SQLDB.Name & "<br>"
End If
Next
' 列出 PUBS 資料庫的所有資料表
Dim pubDatabase
Set pubDatabase = oSQLServer.Databases("pubs")
For Each DBTable In pubDatabase.Tables
Response.Write "Table: " & DBTable.Name & "<br>"
Next
' 列出檢視表
For Each DBView In pubDatabase.Views
Response.Write "View: " & DBView.Name & " - " & Left(DBView.Text, 200) & "<br>"
Next
' 列出預存程序
For Each DBSP In pubDatabase.StoredProcedures
Response.Write "SP: " & DBSP.Name & "<br>"
Next
Set oSQLServer = Nothing
%>
<%@ LANGUAGE = VBScript %>
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=big5">
<TITLE>SQL Server 資料庫列表</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF">
<H3>可用資料庫</H3>
<SELECT NAME="Database">
<%
Dim oSQLServer
Set oSQLServer = CreateObject("SQLOLE.SQLServer")
oSQLServer.Connect "YourServer", "sa", "password"
For Each SQLDB In oSQLServer.Databases
If Not SQLDB.SystemObject Then
Response.Write "<OPTION VALUE=""" & SQLDB.Name & """>" & SQLDB.Name & "</OPTION>"
End If
Next
Set oSQLServer = Nothing
%>
</SELECT>
</BODY>
</HTML>
sys.objects、sys.columns 等,純 SQL 方案、跨平台相容| 錯誤現象 | 可能原因 | 解決方向 |
|---|---|---|
| CreateObject 失敗 (80040154) | 未安裝 SQL-DMO/SQLOLE 元件 | 安裝 SQL Server 用戶端工具或 Shared Management Objects |
| Connect 失敗 | 伺服器名稱、認證、網路、防火牆 | 驗證 SQL Server Browser 服務、TCP/IP 通訊協定啟用 |
| 權限不足 | 登入帳號缺乏 VIEW ANY DEFINITION 或對應資料庫權限 | 授予必要權限或使用高權限帳號 |