search

mkleehammer / pyodbc

Python ODBC bridge
https://github.com/mkleehammer/pyodbc/wiki
MIT No Attribution
2.92k stars 563 forks source link

Cross-server query on MS SQL Server with pyodbc #1009

Closed isaac-florence closed 2 years ago

isaac-florence commented 2 years ago

Please first make sure you have looked at:

Environment

Issue

When trying to execute a cross server query in MS SQL Server, one uses a four part object specification (server.database.schema.table). If the server name has a backslash in it (e.g. 'servername\server'), pyodbc escapes the backslash by adding another backslash. This means the query returns an error ('server: "servername\server" not found in sys.servers').

I am relatively new to Python, coming from R where the odbc package doesn't escape backslashes making this possible in the equivalent of cursor.execute(query).

Is there a way to prevent pyodbc taking SELECT * FROM [servername\server].[mydb].[dbo].[tablename] and then executing SELECT * FROM [servername\\server].[mydb].[dbo].[tablename] ? I hope I have missed something!

Many thanks for your help in advance

gordthompson commented 2 years ago

I'm not seeing the behaviour you describe. This works fine for me:

crsr.execute(r"""
SELECT COUNT(*) AS n FROM [GORD-HP\SQLEXPRESS].[mydb].[dbo].[million_rows]
""")
print(crsr.fetchval())  # 1000000
isaac-florence commented 2 years ago

Thank you for having a look @gordthompson. I will continue to investigate and report back what the issue was. I suspect it is an error on my side.