#sql #sql-server #vba #excel
#sql #sql-сервер #vba #excel
Вопрос:
Я хотел бы настроить код vba, который будет подключаться к sql Management studio 2012 и запускать запрос, который я бы указал в коде vba. Я прочитал здесь все похожие вопросы о переполнении стека, но когда я пытаюсь их воспроизвести, я всегда получаю сообщение об ошибке, обычно из-за сбоя входа пользователя в систему.
Я думаю, что я неправильно настраиваю строковое соединение. Кроме того, мне потребуется проверка подлинности пользователя с помощью проверки подлинности Windows.
Я знаю имя базы данных, имя сервера и свое имя пользователя.
Это код, который я использую, и который выдает мне ошибку.
Sub ConnectionExample6()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnn = New ADODB.Connection
' Open a connection by referencing the ODBC driver.
cnn.ConnectionString = "driver={SQL Server};" amp; _
"server=SERVER NAME;uid=USER ID;pwd=MyPassword;database=DATABASE NAME"
cnn.Open
' Create a Recordset by executing an SQL statement.
Set rs = cnn.Execute("Select top 100 * from "TABLE NAME" aac " amp; _
"where aac.EffectiveDate = '10/04/16'")
' Close the connection.
rs.Close
End Sub
Может кто-нибудь рассказать мне о строке подключения и о том, как настроить ее шаг за шагом? Спасибо.
Комментарии:
1. ошибка: ошибка времени выполнения ‘-2147217843 (80040e4d)’: [Microsoft] [Драйвер ODBC SQL Server] Не удалось войти в систему для пользователя ‘Идентификатор ПОЛЬЗОВАТЕЛЯ’
2. Если вы хотите использовать проверку подлинности Windows, не указывайте идентификатор пользователя / пароль в строке подключения.
3. Mat прибил это, просто убедитесь, что все пользователи, которые будут использовать этот макрос, имеют общедоступный доступ к серверу SQL и базе данных. Или добавьте их в группу в домене, у которого есть доступ.
4. Потрясающе! Большое вам спасибо, это работает!!
Ответ №1:
Проверка подлинности
Если вы подключаетесь к SQL Server, вам следует предпочесть проверку подлинности Windows, если она доступна: вы создаете логин на уровне сервера для группы пользователей Active Directory, а затем создаете пользователя, прошедшего проверку подлинности Windows, в своей базе данных, используя этот логин.
Таким образом, вы сохраняете пароли и имена пользователей из жестко запрограммированных строк и позволяете сети обрабатывать аутентификацию.
Предполагая, что вы не хотите хранить пароли в десятках копий рабочих книг с поддержкой макросов по всей сети, вы захотите использовать проверку подлинности Windows.
Integrated Security=SSPI; Persist Security Info=True;
Сервер
Строки подключения раздражают — кажется, есть разные форматы / формулировки для каждой отдельной вещи, которая может их анализировать!
Поскольку вы используете ADODB, вам потребуется указать поставщика, источник данных и, при необходимости, начальный каталог:
Provider=SQLOLEDB.1; Data Source=SQL Server instance name; Initial Catalog=Database name;
Кто?
Каждое соединение можно отслеживать на сервере; при создании строки подключения вы можете дополнительно указать идентификатор рабочей станции, чтобы идентифицировать компьютер, к которому подключено соединение.
Workstation ID=computer name;
Вы можете получить имя компьютера, выбрав значение переменной среды, используя Environ$ :
Private Function GetWorkstationId() As String
GetWorkstationId = Environ$("ComputerName")
End Function
Учитывая имя экземпляра SQL Server SomeSqlServer , имя базы SomeDatabase данных и использование проверки подлинности Windows, строка подключения ADODB будет выглядеть следующим образом:
Dim connString As String
connString = "Provider=SQLOLEDB.1; Data Source=SomeSqlServer; Initial Catalog=SomeDatabase; Integrated Security=SSPI; Persist Security Info=True;"
Задана проверка подлинности SQL (с жестко заданным именем пользователя и паролем) для SomeUser с SomePassword :
connString = "Provider=SQLOLEDB.1; Data Source=SomeSqlServer; Initial Catalog=SomeDatabase; UID=SomeUser; PWD=SomePassword;"
Команды
Вы не хотите объединять произвольный пользовательский ввод в предложение WHERE; избегайте выполнения строки SQL непосредственно из ADODB.Connection объекта.
Вместо этого создайте ADODB.Command и параметрируйте свой запрос.
Dim sql As String
sql = "SELECT Foo, Bar FROM dbo.FooBars WHERE Foo = ? AND DateInserted > ?"
Здесь у нас есть 2 параметра.
Сначала мы создаем команду:
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = sql
Затем его параметры, предполагая, что у нас есть их соответствующие значения в param1Value и param2Value локальных переменных:
Dim param1 As ADODB.Parameter ' a string parameter
Set param1 = New ADODB.Parameter
param1.Type = adVarWChar
param1.Direction = adParamInput
param1.Size = Len(param1Value)
param1.Value = param1Value
cmd.Parameters.Append param1
Dim param2 As ADODB.Parameter ' a date parameter
Set param2 = New ADODB.Parameter
param2.Type = adDate
param2.Direction = adParamInput
param2.Value = param2Value
cmd.Parameters.Append param2
Затем мы извлекаем набор записей, выполняя команду:
Dim results As ADODB.Recordset
Set results = cmd.Execute
Конечно, это выглядит очень многословно, но его можно легко преобразовать в функции, предназначенные для создания параметра со значением определенного типа.
В результате вы избегаете этой ситуации, поскольку вы больше не выполняете произвольный пользовательский ввод, объединенный в запрос:
Комментарии:
1. Отличный ответ, лучший мультфильм