怎样使用字符串来连接到SQL服务器？ [英] What string do I use to connect to an SQL Server?

问题描述

我想通过自定义活动来执行使用CRM工作流程的SQL作业。在我的代码，一个在线教程，让我建造像这样（在C＃）：​​

I'm trying to execute an SQL Job using a CRM Workflow by making a custom activity. In my code, an online tutorial had me construct it like this (in c#):

[CrmWorkflowActivity("A test activity to run SQL Jobs")]
public sealed class ExecuteSQLJob : System.Activities.CodeActivity
{

    #region Inputs
    [Input("Job Name")]
    [Default("BMS_ExtractTransformLoad")]
    public InArgument<String> JobName { get; set; }

    [Input("Server Connection")]
    [Default("HBSSQL2008/MSSQLSERVER")]                  //<--This String
    public InArgument<String> ServerName { get; set; }

    [Input("User Name")]
    [Default("-----")]
    public InArgument<String> UserName { get; set; }

    [Input("Password")]
    [Default("-----")]
    public InArgument<String> Password { get; set; }
    #endregion

    protected override void Execute(CodeActivityContext context)
    {
        Server server = new Server(ServerName.Get(context));   //<--Is used here
        try
        {
            server.ConnectionContext.LoginSecure = false;
            server.ConnectionContext.Login = UserName.Get(context);
            server.ConnectionContext.Password = Password.Get(context);
            server.ConnectionContext.Connect();
            Job job = server.JobServer.Jobs[JobName.Get(context)];
            job.Start();
        }
        finally
        {
            if (server.ConnectionContext.IsOpen)
            {
                server.ConnectionContext.Disconnect();
            }
        }
    }
}

不过当我尝试运行此工作流程，它抛出一个错误，说无法连接到服务器。 （具体而言，则会引发错误的server.ConnectionContext.Connect（）方法调用）。

However, when I try to run this workflow, it throws an error saying it couldn't connect to the server. (Specifically, the error is thrown on the server.ConnectionContext.Connect() method call).

是问题中的ServerName字符串？如果是这样，我怎么能找到正确的服务器名称使用？

Is the problem the serverName String? If so, how can I find out the correct serverName to use?

感谢您的帮助！

< STRONG>编辑：
这里的一些额外的信息...

Here's some additional info...

这是我得到的服务器和实例名第一次：

This is how I got the server and instance name the first time:

和这里的错误在CRM工作流程：

And here's the error on the CRM Workflow:

Workflow suspended temporarily due to error: Unhandled Exception: Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server HBSSQL2008.
   at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
   at ExecuteSQLJob.ExecuteSQLJob.Execute(CodeActivityContext context)
   at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
   at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)
Inner Exception: System.Data.SqlClient.SqlException: Login failed for user 'rnkelch'.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
   at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

对不起，我没有包含这个原本，感谢您的帮助迄今为止

推荐答案

好吧，我想我理解了它吧。

Ok, I think I figured it out now.

由于有我们的服务器上运行的SQL只有一个实例，我实际上并不需要使用的 ServerName\InstanceName 的格式。无论hbssql2008和HBSSQL2008允许我连接。此外，IP工作也是如此。

Because there is only one instance of SQL running on our server, I don't actually need to use the ServerName\InstanceName format. Both hbssql2008 and HBSSQL2008 allow me to connect. In addition, the IP works as well.

另外，我来到了另一个问题对面是我使用来自Windows集成安全性，而不是SQL的用户名和密码服务器的安全性。因此，我创建了适当的权限和SQL安全信息在服务器上的一个新的帐户，并允许我连接。

Also, another issue I came across was the the user name and password I was using was from Windows Integrated Security, not the sql server's security. Therefore, I created a new account on the server with appropriate permissions and sql security info and it allowed me to connect.

感谢大家的帮助！

这篇关于怎样使用字符串来连接到SQL服务器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！