我有一個 Excel 作業簿，它構建了一堆 SQL 更新腳本，然后在 SQL Server 中執行它們。
我得到了以下 VBA 腳本的幫助。如果我在以 Admin 用戶 Windows 登錄時運行它，下面的作業正常。但是，當從用戶作業站運行時，我遇到了問題。
主要問題似乎是用戶 ID 和密碼不正確。我不確定在下面的何處可以為 SQL Server 添加系統管理員 (sa) 用戶名??和密碼。請我得到一些幫助。
我的代碼：
Sub test()
Const SERVER = "SRV\ServerName"
Const DATABASE = "Test Database"
Dim fso As Object, ts As Object, ar
Dim ws As Worksheet
Dim iLastRow As Long, i As Long
Dim sql As String, timestamp As String
Dim Folder As String, SQLfile As String, LOGfile As String
Dim t0 As String: t0 = Timer
' query file and log filenames
timestamp = Format(Now, "YYYYMMDD_HHMMSS")
Folder = "\\SRV\Test Folder\"
SQLfile = Folder & timestamp & ".sql"
LOGfile = Folder & timestamp & ".log"
Set fso = CreateObject("Scripting.FileSystemObject")
' read data from sheet into array to build sql file
Set ws = ThisWorkbook.Sheets("UDF Update")
iLastRow = ws.Cells(Rows.Count, "N").End(xlUp).Row
If iLastRow = 1 Then
MsgBox "No data in Column N", vbCritical
Exit Sub
End If
ar = ws.Range("N2").Resize(iLastRow - 1).Value2
' connect to server and run query
Dim sConn As String, conn, cmd, n As Long
sConn = "Provider=SQLOLEDB;Server=" & SERVER & _
";Initial Catalog=" & DATABASE & _
";Trusted_Connection=yes;"
' open log file
Set ts = fso.CreateTextFile(LOGfile)
' make connection
Set conn = CreateObject("ADODB.Connection")
conn.Open sConn
' execute sql statements
Set cmd = CreateObject("ADODB.Command")
With cmd
.ActiveConnection = conn
For i = 1 To UBound(ar)
ts.writeLine ar(i, 1)
.CommandText = ar(i, 1)
.Execute
On Error Resume Next
Next
End With
ts.Close
conn.Close
MsgBox UBound(ar) & " SQL queries completed (ADODB)", vbInformation, Format(Timer - t0, "0.0 secs")
End Sub
uj5u.com熱心網友回復：
如果您使用Trusted_Connection=yes，SQL 服務器將通過 Windows 身份驗證接受/拒絕您。服務器似乎接受您的管理員帳戶，而其他帳戶則不接受。
資料庫管理員將其他帳戶添加到資料庫服務器，或者您需要提供憑據并進行設定Trusted_Connection=no（或省略它，因為這是默認設定）
sConn = "Provider=SQLOLEDB;Server=" & SERVER & _
";Initial Catalog=" & DATABASE & _
";Trusted_Connection=no" & _
";User ID=MyUserID;Password=MyPassword;"
請參閱https://docs.microsoft.com/en-us/sql/ado/guide/appendixes/microsoft-ole-db-provider-for-sql-server?view=sql-server-ver15
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/369106.html
標籤：sql-server vba 废话