Hello community, I tried to connect Prefecta with my SQL server database, but I got this error. who has already had this error, or has an idea about this error?

Let’s see if someone in the community knows more about this

it's a driver issue - either it's not installed in your execution environment, or you don't point at the proper driver in your pyodbc connection. check those docs for more info

I have already installed ODBC 17

can you share your flow code?

normally in the code I work with in DRIVE I use: SQL Server Native Client 11.0

You can try to automatically infer driver using this:

driver=[x for x in pyodbc.drivers() if ' for SQL Server' in x][-1]

Not working! the same error Task 'define_Connection': Exception encountered during task execution! Traceback (most recent call last): File "/home/zineb/.local/lib/python3.8/site-packages/prefect/engine/task_runner.py", line 880, in get_task_run_state value = prefect.utilities.executors.run_task_with_timeout( File "/home/zineb/.local/lib/python3.8/site-packages/prefect/utilities/executors.py", line 468, in run_task_with_timeout return task.run(*args, **kwargs) # type: ignore File "sql_connection", line 10, in define_Connection cnxn = pyodbc.connect( pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

can you share your updated flow? (in text not image)

from prefect import Flow, task from prefect.tasks.sql_server import SqlServerExecute from prefect.tasks.secrets import PrefectSecret from prefect.client import Secret import pyodbc @ Task def define_Connection(query,pwd): cnxn = pyodbc.connect( "Driver={ODBC Driver 17 for SQL Server};" "Server=EQ-EQ6288793\SQLEXPRESS;" "Database=OTI_Djoliba;" "UID=sa;" "Trusted_Connection=no;") cursor = cnxn.cursor() #insert dataframe cursor.execute(query) for row in cursor: print('row = %r' % (row,)) cnxn.commit() @ Task def define_query(): return """ SELECT * FROM {Trafic_OTI_Djoliba}; """ @ task def my_task(credentials): """A task that requires credentials to access something. Passing the credentials in as an argument allows you to change how/where the credentials are loaded (though we recommend using

PrefectSecret

tasks to load them.""" pass with Flow("postgres_example") as flow: postgres_pwd = PrefectSecret("POSTGRES_PASSWORD") query = define_query() define_Connection(query=query,pwd=postgres_pwd) my_secret = PrefectSecret("MYSECRET") res = my_task(credentials=my_secret) # Register the flow under the "tutorial" project flow.register(project_name="Step1") if name == "__main__": flow.run()

so you didn't implement my suggestion then. I was suggesting:

@task
def define_connection(query,pwd):
    cnxn = pyodbc.connect(driver=[x for x in pyodbc.drivers() if ' for SQL Server' in x][-1], server="EQ-EQ6288793\SQLEXPRESS", database="OTI_Djoliba", uid="sa", pwd="xxx")
    cursor = cnxn.cursor()
    #insert dataframe
    cursor.execute(query)
    for row in cursor:
         print('row = %r' % (row,))
    cnxn.commit()

I made the changes requested, but I have an error related to the Secret variable that I created with the value MYSECRET: "ValueError: Local Secret "MYSECRET" was not found." Normally it is already created and I added it to the code

Did you add MYSECRET as an env var to the agent?

yes

you still didn't implement the same syntax I suggested. I suggested using normal keyword arguments such as driver, uid etc, not the single string syntax. Can you look more closely at the syntax I sent you and compare it to your current code? Can you see the difference in how the pyodbc connection is defined in each of those?

Yes I did the same as you, the only change in the Driver so I used what you suggested: driver=[x for x in pyodbc.drivers() if ' for SQL Server' in x][-1] here are my connection parameters: cnxn = pyodbc.connect(driver=[x for x in pyodbc.drivers() if ' for SQL Server' in x][-1], server="EQ-EQ6288793\SQLEXPRESS", database= "OTI_Djoliba", uid="sa", pwd="xxx")

exactly, this syntax should work. perhaps you can run in iPython

pyodbc.drivers()

to see what drivers do you have in your environment and to confirm that

pyodbc

can find those?

with pyodbc.drivers() command, return this ['ODBC Driver 17 for SQL Server']

discussion moved to Discourse: https://discourse.prefect.io/t/issues-setting-secrets-valueerror-local-secret-mysecret-was-not-found/773