get_usable_table_names returning no tables, at all when scurity is confirmed and MSSql Database has tables. See screenshot

[AlbertLotito]

Checked other resources

• I added a very descriptive title to this question.
• I searched the LangChain documentation with the integrated search.
• I used the GitHub search to find a similar question and didn't find it.

Commit to Help

• I commit to help with one of those options 👆

Example Code

from langchain_ollama import OllamaLLM
from langchain.chains import ConversationChain
from langchain_community.utilities import SQLDatabase

# Connection string for MS SQL Server
connection_string = (
    "DRIVER={ODBC Driver 18 for SQL Server};"
    "SERVER=WOZ;"
    "DATABASE=WideWorldImportersDW;"
    "UID=DB_Dudskie;"
    "PWD=PitterPatter!;"
    "TrustServerCertificate=yes"
)
llm = OllamaLLM(model="llama3.2:3b")  # Or any other Ollama model you have

db = SQLDatabase.from_uri(f"mssql+pyodbc:///?odbc_connect={connection_string}")
print(db.get_usable_table_names())

Description

I am using get_usable_table_names and it returns an empty list.
See screenshot showing the database connected in SSMS, under the same security context; with the tables listed.
I have a fresh install of all relevant libraries, and python (fresh install today.)
All installed modules/libs in modules.txt.
Screenshot attached.

Python Version: 3.13.0

Modules.txt
Modules.txt

System Info

Microsoft SQL Server Developer (64-bit) (16.0.1135.2)
Testing against the WideWorldImportersDW sample DB from microsoft.

System Information

OS: Windows
OS Version: 10.0.22631
Python Version: 3.13.0 (tags/v3.13.0:60403a5, Oct 7 2024, 09:38:07) [MSC v.1941 64 bit (AMD64)]

Package Information

langchain_core: 0.3.21
langchain: 0.3.8
langchain_community: 0.3.8
langsmith: 0.1.145
langchain_cli: 0.0.33
langchain_ollama: 0.2.0
langchain_text_splitters: 0.3.2
langserve: 0.3.0

Other Dependencies

aiohttp: 3.11.7
async-timeout: Installed. No version info available.
dataclasses-json: 0.6.7
fastapi: 0.115.5
gitpython: 3.1.43
gritql: 0.1.5
httpx: 0.27.2
httpx-sse: 0.4.0
jsonpatch: 1.33
langserve[all]: Installed. No version info available.
numpy: 1.26.4
ollama: 0.4.0
orjson: 3.10.11
packaging: 24.2
pydantic: 2.10.1
pydantic-settings: 2.6.1
PyYAML: 6.0.2
requests: 2.32.3
requests-toolbelt: 1.0.0
SQLAlchemy: 2.0.35
sse-starlette: 1.8.2
tenacity: 9.0.0
tomlkit: 0.13.2
typer[all]: Installed. No version info available.
typing-extensions: 4.12.2
uvicorn: 0.32.1

[AlbertLotito]

I even tried as 'sa'. same result.

[AlbertLotito]

Also, to test the database and odbc I created an odbc (ver 17) connection and tested it in excel. See screenshot attached. Same Creds.

attached.

[feijoes]

@AlbertLotito I have a few theories. First, confirm if the URL is correct by trying this:

from sqlalchemy import create_engine
from sqlalchemy.exc import OperationalError

dburl = "mssql+pyodbc:///?odbc_connect=..." 

try:
    engine = create_engine(dburl)
    connection = engine.connect()
    print("Connection successful!")
    connection.close()
except OperationalError as e:
    print("Connection failed!")
    print(e)

Or perhaps the issue is the schema where the tables are located? Try passing it with schema='<your_db_schema>' in the from_uri method.

[AlbertLotito]

Adding the "Schema=woz.dbo.WideWorldImportersDW" is strange.
it returns:
['MSreplication_options', 'spt_fallback_db', 'spt_fallback_dev', 'spt_fallback_usg', 'spt_monitor']

these are all system tables in the master db.

Going down the sql rabbithole.

Thanks for some direction.

[AlbertLotito]

Even running the test with 'sa' returns the same thing.

Very strange indeed.

[AlbertLotito]

so, result: The Result:
[]
Connection successful!

the Code:
from langchain_ollama import OllamaLLM
from langchain.chains import ConversationChain
from langchain_community.utilities import SQLDatabase

Connection string for MS SQL Server

connection_string = (
"DRIVER={ODBC Driver 17 for SQL Server};"
"SERVER=WOZ;"
"DATABASE=WideWorldImportersDW;"
"UID=DB_Dudskie;"
"PWD=PitterPatter!;"
"TrustServerCertificate=yes"
)
llm = OllamaLLM(model="llama3.2:3b") # Or any other Ollama model you have

db = SQLDatabase.from_uri(f"mssql+pyodbc:///?odbc_connect={connection_string}")
print(db.get_usable_table_names())

from sqlalchemy import create_engine
from sqlalchemy.exc import OperationalError

dburl = "mssql+pyodbc://DB_Dudskie:PitterPatter!@dawoz"

try:
engine = create_engine(dburl)
connection = engine.connect()
print("Connection successful!")
connection.close()
except OperationalError as e:
print("Connection failed!")
print(e)

[AlbertLotito]

Additionally, I tried:

with engine.connect() as connection:
query = "SELECT * FROM information_schema.tables" #.format(db_name)
result = connection.execute(text(query))

#result = connection.execute("SELECT * FROM information_schema.tables")
for row in result:
    print(row)

And it returned all tables

[feijoes]

@AlbertLotito try use the schema information_schema in the from_uri method

[AlbertLotito]

FML.
This morning, no changes to the server or the test code.. and freaking magic the sqlalchemy version works.
the .get_usable_table_names still fails.

I am glad I can confirm the the database and permissions are correct.

Now to dive down the .get_usable_table_names rabbit hole.

@feijoes, Thank you. I appreciate you taking the time to guide me. I have been coding for decades but am new to python and LLMs... quite the journey.