C#使用命名实例连接到SQL服务器?

时间:2021-01-19 10:20:35

A client of mine has told me the program I made for them won't connect to a SQL server named instance, I have a standard SQL server with no named instance so I'm wondering how I can test this. A named instance connection string look like the one below, could the backslash be were my code fails?

我的一个客户告诉我,我为他们制作的程序不会连接到SQL服务器命名实例,我有一个没有命名实例的标准SQL服务器,所以我想知道如何测试它。命名实例连接字符串如下所示,反斜杠是否是我的代码失败?

Driver={SQL Native Client};Server=myServerName\theInstanceName;Database=myDataBase;

Driver = {SQL Native Client}; Server = myServerName \ theInstanceName; Database = myDataBase;

My code is as follows:

我的代码如下:

sqlServer=s.Substring(keyword.Length,s.Length-keyword.Length);
FormODBC formODBC=new FormODBC(this);
formODBC.SetSqlServer(sqlServer,dbUsername,dbPassword,database,table);
formODBC.ReadData();

How should I handle the backslash as I suspect this may be the problem?

我应该如何处理反斜杠,因为我怀疑这可能是问题所在?

Thanks

6 个解决方案

#1


3  

We have SQL servers with named instances. Examples: myservername\sql2005. Backslash is fine, in the conection string server name will be "myservername\sql2005", works 100% fine. You can have a "regular instance" on the same server, will be "myservername"

我们有带有命名实例的SQL服务器。示例:myservername \ sql2005。反斜杠很好,在连接字符串服务器名称将是“myservername \ sql2005”,工作100%罚款。你可以在同一台服务器上有一个“常规实例”,将是“myservername”

PS just unit test your function making connection string returns "myservername\sql2005".

PS只是单元测试你的功能使连接字符串返回“myservername \ sql2005”。

#2


3  

Also, remember that backslash is an escape character in C# strings. If your instance name is contained in a string variable, make sure you do either:

另外,请记住反斜杠是C#字符串中的转义字符。如果您的实例名称包含在字符串变量中,请确保执行以下任一操作:

string server = "myServer\\myInstance";

or

string server = @"myServer\myInstance";

#3


1  

The answer is to ensure your connection string is configurable, and set it to something like:

答案是确保您的连接字符串是可配置的,并将其设置为:

data source=localhost\msexpress;database=dbname;trusted_connection=true;

#4


0  

Scott, At the risk of stating the obvious, have you tried setting up a named instance in your own development environment? I don't actually know the answer to your question but I've never personally run into a solution where testing the scenario that is failing directly didn't help. At a minimum you ought to be able to get better debugging information as to precisely what is failing. Good luck getting this resolved.

Scott,冒着明显的风险,您是否尝试在自己的开发环境中设置命名实例?我实际上并不知道你的问题的答案,但我从未亲自遇到一个解决方案,直接测试失败的方案没有帮助。至少你应该能够获得更好的调试信息,以确切地知道什么是失败的。祝你好运得到解决。

Regards, Chris

#5


0  

There is also the occassional SQL Express edition case where the name is MyServerName\SQLEXPRESS. This can trip up some people because they wouldn't think to specify the server that way.

还有偶然的SQL Express版本案例,其名称为MyServerName \ SQLEXPRESS。这会让一些人绊倒,因为他们不会想到以这种方式指定服务器。

#6


0  

You can extract your connection string to a config file (note this isn't necessarily secure - that will depend how their SQL security server is setup and the account your application is running under) - then your client can just add the appropriate connection string for their server to your applications config when deployed.

您可以将连接字符串提取到配置文件(请注意,这不一定是安全的 - 这取决于他们的SQL安全服务器的设置方式以及运行应用程序的帐户) - 然后您的客户端只需添加适当的连接字符串即可部署时将服务器配置为应用程序配置。

Notes:

  • You can create a connection string by renaming a .txt file to a .udl file and running through until you can connect to your/their server.
  • 您可以通过将.txt文件重命名为.udl文件并运行直到可以连接到您/他们的服务器来创建连接字符串。

  • Your unamed instance can actually be accessed by name - the machine name on which the sql server is installed
  • 实际上可以通过名称访问您的unamed实例 - 安装sql server的机器名

#1


3  

We have SQL servers with named instances. Examples: myservername\sql2005. Backslash is fine, in the conection string server name will be "myservername\sql2005", works 100% fine. You can have a "regular instance" on the same server, will be "myservername"

我们有带有命名实例的SQL服务器。示例:myservername \ sql2005。反斜杠很好,在连接字符串服务器名称将是“myservername \ sql2005”,工作100%罚款。你可以在同一台服务器上有一个“常规实例”,将是“myservername”

PS just unit test your function making connection string returns "myservername\sql2005".

PS只是单元测试你的功能使连接字符串返回“myservername \ sql2005”。

#2


3  

Also, remember that backslash is an escape character in C# strings. If your instance name is contained in a string variable, make sure you do either:

另外,请记住反斜杠是C#字符串中的转义字符。如果您的实例名称包含在字符串变量中,请确保执行以下任一操作:

string server = "myServer\\myInstance";

or

string server = @"myServer\myInstance";

#3


1  

The answer is to ensure your connection string is configurable, and set it to something like:

答案是确保您的连接字符串是可配置的,并将其设置为:

data source=localhost\msexpress;database=dbname;trusted_connection=true;

#4


0  

Scott, At the risk of stating the obvious, have you tried setting up a named instance in your own development environment? I don't actually know the answer to your question but I've never personally run into a solution where testing the scenario that is failing directly didn't help. At a minimum you ought to be able to get better debugging information as to precisely what is failing. Good luck getting this resolved.

Scott,冒着明显的风险,您是否尝试在自己的开发环境中设置命名实例?我实际上并不知道你的问题的答案,但我从未亲自遇到一个解决方案,直接测试失败的方案没有帮助。至少你应该能够获得更好的调试信息,以确切地知道什么是失败的。祝你好运得到解决。

Regards, Chris

#5


0  

There is also the occassional SQL Express edition case where the name is MyServerName\SQLEXPRESS. This can trip up some people because they wouldn't think to specify the server that way.

还有偶然的SQL Express版本案例,其名称为MyServerName \ SQLEXPRESS。这会让一些人绊倒,因为他们不会想到以这种方式指定服务器。

#6


0  

You can extract your connection string to a config file (note this isn't necessarily secure - that will depend how their SQL security server is setup and the account your application is running under) - then your client can just add the appropriate connection string for their server to your applications config when deployed.

您可以将连接字符串提取到配置文件(请注意,这不一定是安全的 - 这取决于他们的SQL安全服务器的设置方式以及运行应用程序的帐户) - 然后您的客户端只需添加适当的连接字符串即可部署时将服务器配置为应用程序配置。

Notes:

  • You can create a connection string by renaming a .txt file to a .udl file and running through until you can connect to your/their server.
  • 您可以通过将.txt文件重命名为.udl文件并运行直到可以连接到您/他们的服务器来创建连接字符串。

  • Your unamed instance can actually be accessed by name - the machine name on which the sql server is installed
  • 实际上可以通过名称访问您的unamed实例 - 安装sql server的机器名