通过 SQL Server pyodbc 连接连接到链接服务器? [英] Connect to Linked Server via SQL Server pyodbc connection?

问题描述

我目前可以连接到我的 SQL Server 并直接查询我想要的任何数据库.

I can currently connect to my SQL Server and query any database I want to directly.

问题是当我想查询链接服务器时.我无法在 connect() 方法中直接引用链接服务器名称，我必须先连接到本地数据库，然后对链接服务器运行 OPENQUERY().

The problem is when I want to query a linked server. I cannot directly reference the linked servers name in the connect() method and I have to connect to a local database first and then run an OPENQUERY() against the linked server.

这看起来像是一个奇怪的工作.有没有办法直接查询链接服务器(根据我的研究，你不能直接连接到链接服务器) 或者至少连接到服务器而不指定一个数据库，然后我可以在其中运行 OPENQUERY() 无需先连接到数据库即可进行任何操作?

This seams like a odd work around. Is there a way to query the linked server directly (from my research you cannot connect directly to a linked server) or at least connect to the server without specifying a database where I can then run the OPENQUERY() for anything without having to first connect to a database?

我目前必须做的事情的例子:

Example of what I have to do currently:

import pyodbc

ex_value = "SELECT * FROM OPENQUERY(LinkedServerName,'SELECT * FROM LinkedServerName.SomeTable')"
# I have to connect to some local database on the server and cannot connect to linked server initially.
odbc_driver, server, db = '{ODBC Driver 17 for SQL Server}', 'MyServerName', 'LocalDatabase'

with pyodbc.connect(driver=odbc_driver, host=server, database=db, trusted_connection='yes') as conn:
    conn.autocommit = False
    cursor = conn.cursor()
    cursor.execute(ex_value)
    tables = cursor.fetchall()

    for row in tables:
        print('Row: {}'.format(row))

    cursor.close()

推荐答案

正如 Sean 提到的，链接服务器只是对存储在本地服务器中的另一台服务器的引用.

As Sean mentioned, a linked server is just a reference to another server that's stored within the local server.

不过，您不需要管理 100 多个用户凭据.如果您的用户使用 Windows 身份验证，并且您在服务器之间使用 Kerberos，则链接服务器可以在通过链接服务器定义连接到其他服务器时冒充您.

You do not need to manage 100+ user credentials though. If you have the users using Windows auth, and you have Kerberos working between the servers, the linked server can just impersonate you when it connects to the other server via the linked server definition.

然后，您可以使用 4 个部分名称来引用另一台服务器上的对象，或者当您想要更多地控制在何处执行的内容时使用 OPENQUERY.

Then you can use either 4 part names to refer to objects on the other server, or use OPENQUERY when you want more control over what gets executed where.

最后，如果它们都是 SQL Server 并且都使用相同的排序规则，请确保将链接服务器选项设置为说明它们是排序规则兼容的.这会对链接服务器的性能产生重大影响.我经常看到没有设置但应该设置的系统.

Finally, if they're both SQL Servers and both use the same collation, make sure you set the linked server option to say they are collation compatible. That can make a major difference to your linked server performance. I regularly see systems where that isn't set and it should be.

这篇关于通过 SQL Server pyodbc 连接连接到链接服务器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！