将服务器添加到 SQL Management Studio [英] Adding Servers to SQL Management Studio

问题描述

我想将不同服务器上的一堆 SQL(2000-2005 的混合)服务器实例添加到我的 SSMS(SQL 管理工作室)注册服务器，我正在关注本教程 此处 但这一次只添加一台服务器不能同时进行.

I would like to add a bunch of SQL (mixture of 2000-2005) server instances on different servers to my SSMS (SQL Managment Studio) Registered servers, I was following this tutorial here but this only adds one server at a time not all at the same time.

该列表目前在 Excel 中，但可以导入到记事本或任何其他文档格式，只要它可以被 PowerShell 命令识别.另外，要注意的另一件事是我可以在哪里更改登录以使用 sql 身份验证?并指定用户名和密码?

The list is currently in Excel but can be imported to notepad or any other document format as long as it can be recognized by the PowerShell command. Also, another thing to note is where can i change the login to use sql authentication? and specify username and password?

New-Item $(Encode-Sqlname "SERVERNAME\INSTANCENAME") -itemtype registration -Value "server=MyServer;integrated security=true" 

tks

错误

New-Object : Cannot convert argument "1", with value: "", for "PSCredential" to
 type "System.Security.SecureString": "Cannot convert the "" value of type "Sys
tem.String" to type "System.Security.SecureString"."
At line:4 char:32
+         -Credential (New-Object <<<<  System.Management.Automation.PSCredenti
al("sa", "")) }
    + CategoryInfo          : InvalidOperation: (:) [New-Object], MethodExcept
   ion
    + FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.Power
   Shell.Commands.NewObjectCommand

推荐答案

如果将 Excel 电子表格保存为 CSV 文件，则可以使用 Import-Csv cmdlet 并按名称自动在列表中注册服务器.

If you save the Excel spreadsheet as a CSV file, you can easily import it in PowerShell using the Import-Csv cmdlet and automatically register the servers in the list by their names.

假设您的 CSV 文件如下所示:

Assuming your CSV file looks like this:

|Name    |
|Server1 |
|Server2 |
|Server3 |

以下命令将其内容导入为对象列表，CSV 文件中的每一行一个，所有内容都有一个 Name 属性，其中包含实际值.然后在传递给 New 的字符串中使用这些名称-Item cmdlet 来实际进行注册:

The following command will import its content as a list of objects, one for each row in the CSV file, all having a Name property, which contains the actual value. Those names are then used within the string passed to the New-Item cmdlet to actually do the registration:

Import-Csv ServersToRegister.csv | ForEach-Object { `
    New-Item $(Encode-Sqlname $_.Name) -ItemType Registration `
        -Value ("server=$($_.Name);integrated security=true") }

您可以通过传递一个 PSCredential 对象到 新项目 cmdlet.所以完整的命令是:

You can specify the username and password to use to connect to the SQL Server instance by passing a PSCredential object to the New-Item cmdlet. So the complete command would be:

Import-Csv ServersToRegister.csv | ForEach-Object { `
    New-Item $(Encode-Sqlname $_.Name) -ItemType Registration `
        -Value ("server=$($_.Name);integrated security=true") `
        -Credential (New-Object System.Management.Automation.PSCredential("username", "password")) }

这篇关于将服务器添加到 SQL Management Studio的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！