How to connect MS-SQL with LANG-CHAIN with SQLDatabase.from_uri(conn_str)

[surabhi-cb]

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

conn_str = 'mssql+pyodbc://@SSC10205F/SkillDB?trusted_connection=yes&driver=SQL+Server'
engine =  SQLDatabase.from_uri(conn_str)

Description

I want to connect my mssql db directly with langchain without using username and password. Getting this error:

sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY104', '[HY104] [Microsoft][ODBC SQL Server Driver]Invalid precision value (0) (SQLBindParameter)')
[SQL: SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME]
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max)) ORDER BY [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME]]
[parameters: ('dbo', 'BASE TABLE')]
(Background on this error at: https://sqlalche.me/e/20/dbapi)

System Info

aiofiles==23.2.1
aiohttp==3.9.3
aiosignal==1.3.1
annotated-types==0.6.0
anyio==3.7.1
asgiref==3.7.2
async-timeout==4.0.3
asyncer==0.0.2
attrs==23.2.0
backoff==2.2.1
bidict==0.23.0
certifi==2024.2.2
chainlit==1.0.200
charset-normalizer==3.3.2
click==8.1.7
colorama==0.4.6
dataclasses-json==0.5.14
Deprecated==1.2.14
distro==1.9.0
Django==2.1.15
django-pyodbc-azure==2.1.0.0
djangorestframework==3.14.0
exceptiongroup==1.2.0
fastapi==0.108.0
fastapi-socketio==0.0.10
filetype==1.2.0
frozenlist==1.4.1
googleapis-common-protos==1.62.0
greenlet==3.0.3
grpcio==1.60.1
h11==0.14.0
httpcore==0.17.3
httpx==0.24.1
idna==3.6
importlib-metadata==6.11.0
jsonpatch==1.33
jsonpointer==2.4
langchain==0.1.8
langchain-community==0.0.21
langchain-core==0.1.24
langchain-openai==0.0.5
langsmith==0.1.3
Lazify==0.4.0
literalai==0.0.103
marshmallow==3.20.2
multidict==6.0.5
mypy-extensions==1.0.0
mysqlclient==2.2.4
nest-asyncio==1.6.0
numexpr==2.9.0
numpy==1.26.4
openai==0.27.8
openapi-schema-pydantic==1.2.4
opentelemetry-api==1.22.0
opentelemetry-exporter-otlp==1.22.0
opentelemetry-exporter-otlp-proto-common==1.22.0
opentelemetry-exporter-otlp-proto-grpc==1.22.0
opentelemetry-exporter-otlp-proto-http==1.22.0
opentelemetry-instrumentation==0.43b0
opentelemetry-proto==1.22.0
opentelemetry-sdk==1.22.0
opentelemetry-semantic-conventions==0.43b0
packaging==23.2
pandas==2.0.3
protobuf==4.25.2
pydantic==1.10.14
pydantic_core==2.16.2
PyJWT==2.8.0
pymssql==2.2.11
pyodbc==5.1.0
python-dateutil==2.8.2
python-dotenv==1.0.1
python-engineio==4.9.0
python-graphql-client==0.4.3
python-multipart==0.0.6
python-socketio==5.11.1
pytz==2024.1
PyYAML==6.0.1
regex==2023.12.25
requests==2.31.0
simple-websocket==1.0.0
six==1.16.0
sniffio==1.3.0
SQLAlchemy==2.0.27
sqlparse==0.4.4
starlette==0.32.0.post1
syncer==2.0.3
tabulate==0.9.0
tenacity==8.2.3
tiktoken==0.5.2
tomli==2.0.1
tqdm==4.66.2
typing-inspect==0.9.0
typing_extensions==4.9.0
tzdata==2024.1
uptrace==1.22.0
urllib3==2.2.0
uvicorn==0.25.0
watchfiles==0.20.0
websockets==12.0
wrapt==1.16.0
wsproto==1.2.0
yarl==1.9.4
zipp==3.17.0

[dosubot[bot]]

🤖

Hey @surabhi-cb, nice to see you back here! Hope you're doing well.

Based on the error message you provided, it seems like there might be an issue with your ODBC driver or SQL Server configuration. The error message sqlalchemy.exc.DBAPIError: (pyodbc.Error) ('HY104', '[HY104] [Microsoft][ODBC SQL Server Driver]Invalid precision value (0) (SQLBindParameter)') is typically associated with these types of issues.

To connect to an MS-SQL database without a username and password, you can use the SQLDatabase.from_uri() method with a connection string that uses Windows Authentication (also known as Integrated Security).

Here is how you can use it:

from libs.community.langchain_community.utilities.sql_database import SQLDatabase

conn_str = "mssql+pyodbc:///?odbc_connect=DRIVER={SQL Server};SERVER=your_server;DATABASE=your_database;Trusted_Connection=yes;"
db = SQLDatabase.from_uri(conn_str)

Please replace your_server and your_database with your actual server name and database name.

This method uses Windows Authentication, so it only works if your Python script is running on a Windows machine that's authenticated against the SQL Server. If you're running on a different OS, or if your SQL Server doesn't support Windows Authentication, you'll need to use a different method.

If you're still encountering a sqlalchemy.exc.DBAPIError with pyodbc, the error might be due to a problem with your ODBC driver or your SQL Server configuration. Please check that you have the correct ODBC driver installed, that your SQL Server is running and accessible, and that it's configured to allow connections from your machine.

In the past, similar issues have been resolved by changing the driver name in the connection string. For example, in this issue, the user was able to resolve the problem by replacing SQL+Server with ODBC+Driver+17+for+SQL+Server in the connection string.

I hope this helps! If you have any other questions or need further clarification, feel free to ask.

Sources

• How to connect MS-SQL with LANG-CHAIN
• SQLDatabase chain having issue running queries on the database after connecting
• libs/community/langchain_community/utilities/sql_database.py

About Dosu

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

[surabhi-cb]

Hey @dosubot

I have used the same

conn_str = "mssql+pyodbc:///?odbc_connect=DRIVER={SQL Server};SERVER=SSC10205F;DATABASE=SkillDB;Trusted_Connection=yes;"
db = SQLDatabase.from_uri(conn_str)

again getting same error.

Can I connect with odbc driver?

[surabhi-cb]

I have used the same

conn_str = "mssql+pyodbc:///?odbc_connect=DRIVER={SQL Server};SERVER=SSC10205F;DATABASE=SkillDB;Trusted_Connection=yes;"
db = SQLDatabase.from_uri(conn_str)

again getting same error.

Can I connect with odbc driver?

[dosubot[bot]]

🤖

Based on the information you've provided and the similar issue I found in the LangChain repository, it seems like you're on the right track with using the SQLDatabase.from_uri() method to connect to your MS-SQL database. However, the error you're encountering is likely due to a mismatch between the data types in your SQL Server database and the data types that SQLAlchemy/pyodbc are trying to use when interacting with your database.

To resolve this issue, you might need to adjust the data types in your database or in your SQLAlchemy models to ensure they match. Without more specific information about your database schema and the queries you're running, it's difficult to provide a more precise solution.

As for using the ODBC driver, you can modify your connection string as follows:

database_uri = "mssql+pyodbc://<username>:<password>@<dsn_name>"
engine_args = {"echo": True}  # Optional engine arguments
db = SQLDatabase.from_uri(database_uri, engine_args)

Replace <username>, <password>, and <dsn_name> with your actual username, password, and DSN name. The DSN should be configured to use the ODBC driver and Windows Authentication.

Please note that this is a general guide and might need to be adjusted based on your specific setup. If you continue to encounter issues, please provide more details about your database schema and the queries you're running, and I'll do my best to assist you further.

For more information, you can refer to the SQLDatabase utility in the LangChain repository and the similar issue I found.

Sources

• How to connect MS-SQL with LANG-CHAIN
• libs/community/langchain_community/utilities/sql_database.py

About Dosu

This response is meant to be useful and save you time. It is not meant to be a precise solution, but rather a starting point for your own research.

Help me be more useful! Please leave a 👍 if this is helpful and 👎 if it is irrelevant.

If you want to continue the conversation, start your reply with @dosu-bot.

[surabhi-cb]

I have told you previously that I don't have username and password , tell me the things that I can aonnect without username and password.
conn_str = 'mssql+pyodbc://@SSC10205F/SkillDb?trusted_connection=yes&driver=SQL+Server'
engine_args = {"echo": True}
engine = SQLDatabase.from_uri(conn_str,engine_args)

[surabhi-cb]

I am trying to directly use this without using SQLDatabase but i also throwing error:
By using line:
engine = sqlalchemy.create_engine('mssql://@SSC10205F/SkillDb?trusted_connection=yes')
it is connecting with my db but whenever I am trying to pass this with chain getting internal error.
engine = sqlalchemy.create_engine('mssql://@SSC10205F/SkillDb?trusted_connection=yes')
llm = ChatOpenAI(model="gpt-3.5-turbo",openai_api_key="sk-cp...hfhg", temperature=0)
chain = create_sql_query_chain(llm, engine)

Can you please help me with this

[BerkadiaBonal]

SQLDatabase object from Langchaing does not have the from_uri property in my code. It only has fromDataSourceParams and fromOptionsParams. Did this change?

Here is my code

const datasource = new DataSource({
   type: 'mssql',
   host: 'localhost', 
   database: 'database',
   options: {
       encrypt: true,
       trustServerCertificate: true
   },
   driver: 'ODBC Driver 17 for SQL Server'
 });

 const db = await SqlDatabase.fromDataSourceParams({ appDataSource: datasource });

[green999]

Did anyone solve this problem?

conn_str = "mssql+pyodbc:///?odbc_connect=DRIVER={ODBC Driver 18 for SQL Server};SERVER=my-db-name50;DATABASE=Data_base_name;Trusted_Connection=yes;"

db_engine = SQLDatabase.from_uri(conn_str)

db = SQLDatabase(db_engine, schema="dbo")

I get the following error

sqlalchemy.exc.NoInspectionAvailable: No inspection system is available for object of type <class 'langchain_community.utilities.sql_database.SQLDatabase'>

Any help is really appreciated.