如何判断SQL Server是本地还是远程？

In my app the users gets to pick from a list of SQL Server in the network. The thing is I need to know if the chosen instance is a local or remote computer.

在我的应用程序中，用户可以从网络中的SQL Server列表中进行选择。问题是我需要知道所选实例是本地计算机还是远程计算机。

Is there a way I can ask that SQL instance what computer is she on? Is there a way a can figure that out?

有没有办法我可以问SQL实例她在哪台电脑上？有没有办法可以解决这个问题？

Edit1: I want to know the host name where the SQL Server is hosted so I can then compare that to System.Environment.MachineName and know for sure is that Sql Server is running on the same machine where my app is running

Edit1：我想知道托管SQL Server的主机名，这样我就可以将它与System.Environment.MachineName进行比较，并确定Sql Server是在运行我的应用程序的同一台机器上运行的

Edit2: @@servername returned my computername\sqlinstance while SELECT SERVERPROPERTY('MachineName') returns just the computername, which is exactly what I want

Edit2：@@ servername返回我的computername \ sqlinstance，而SELECT SERVERPROPERTY（'MachineName'）只返回computername，这正是我想要的

3 个解决方案

#1

Use @@Servername, for example:

使用@@ Servername，例如：

SELECT @@servername

Alternately you could do this

或者你可以做到这一点

SELECT SERVERPROPERTY('MachineName')

From MSDN on the differences between these approaches:

从MSDN上看这些方法之间的差异：

The ServerName property of the SERVERPROPERTY function and @@SERVERNAME return similar information. The ServerName property provides the Windows server and instance name that together make up the unique server instance. @@SERVERNAME provides the currently configured local server name.

SERVERPROPERTY函数的ServerName属性和@@ SERVERNAME返回类似的信息。 ServerName属性提供组成唯一服务器实例的Windows服务器和实例名称。 @@ SERVERNAME提供当前配置的本地服务器名称。

The ServerName property and @@SERVERNAME return the same information if the default server name at the time of installation has not been changed.

如果尚未更改安装时的默认服务器名称，则ServerName属性和@@ SERVERNAME将返回相同的信息。

If the local server name has been changed from the default server name at installation time, @@SERVERNAME returns the new name.

如果在安装时已从默认服务器名称更改了本地服务器名称，则@@ SERVERNAME将返回新名称。

#2

Do you actually have login permissions on all the instance(s) of SQL Server? If so you could execute sp_helpserver or @@servername and compare the name returned with Environment.MachineName.

你真的对SQL Server的所有实例都有登录权限吗？如果是这样，您可以执行sp_helpserver或@@ servername并将返回的名称与Environment.MachineName进行比较。

If you don't have login access, you can write a small C# console program to return the server name of every SQL Server instance on the local network:

如果您没有登录访问权限，则可以编写一个小型C＃控制台程序，以返回本地网络上每个SQL Server实例的服务器名称：

using System;
using System.Data.Sql;

class Program
{
    static void Main()
    {
        // Retrieve the enumerator instance and then the data.
        SqlDataSourceEnumerator instance =
        SqlDataSourceEnumerator.Instance;
        System.Data.DataTable table = instance.GetDataSources();

        // Display the contents of the table.
        // The first column is the server name.
        DisplayData(table);
        Console.WriteLine("Press any key to continue.");
        Console.ReadKey();
    }

    private static void DisplayData(System.Data.DataTable table)
    {
        foreach ( System.Data.DataRow row in table.Rows )
        {
            foreach ( System.Data.DataColumn col in table.Columns )
            {
                    Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
            }
        Console.WriteLine("============================");
        }
    }
}

#3

-1

sp_who2 returns the hostname

sp_who2返回主机名

#1