CODEWITHSUNDEEP
sql-serverpython-3.xstored-procedurespyodbc
Taylor Francis
Taylor Francis

Reputation: 168

How to fix pyodbc error with stored procedure execution

I'm setting up a new VM on a server to offload SQL Server database loading from my laptop. In doing so, I'd like to be able to execute stored procedures (no params, just 'exec storedprocedure') in my database via Python, but it's not working.

Stored procedure call worked when using sqlcmd via a batch file and in SSMS, but i'd like to make it all python based.

The stored procedure is appending fact tables follows the below general format:

--staging tbl drop and creation
if object_id(stagingtbl) is not null drop tabl stagingtbl
create table stagingtbl
(fields datatypes nullable
)

--staging tbl load
bulk insert stagingtbl
from 'c:\\filepath\\filename.csv'
with (
   firstrow = 2
   , rowterminator = '\n'
   ,fieldterminator = ','
   , tablock /*don't know what tablock does but it works...*/
)

--staging table transformation
; with cte as (
    /*ETL process to transform csv file into my tbl structure*/
)

--final table load
insert final_tbl
select * from cte


/*
T-SQL update the final table's effect to date, based on subsequent effect from date. 
eg:
id, effectfromdate, effecttodate
1,1/1/19, 1/1/3000
1,1/10/19, 1/1/3000
   becomes
id, effectfromdate, effecttodate
1,1/1/19, 1/10/19
1,1/10/19, 1/1/3000
*/

The stored procedure works fine with sqlcmd and in ssms but in python (pyodbc) executing the query 'exec storedprocedure', I get the error message:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]
Cannot bulk load because the file "c:\filepath\filename.csv" could not be opened.
Operating system error code 3(The system cannot find the path specified.). (4861) (SQLExecDirectW)')

When the csv file is there, no misspellings in path or filename, and I can open the csv when double clicking on it, and no one has the csv open.

Upvotes: 0

Views: 904

Answers (1)

Taylor Francis
Taylor Francis

Reputation: 168

With continued experimentation I've established the problem is not with python or pyodbc. In SSMS on my laptop (host machine of db) the stored procedures work just fine, but in SSMS on the VM the stored procedures cause the same error. This tells me that my question isn't the root problem and I had more digging to do. The error (in SSMS) is below.

Msg 4861, Level 16, State 1, Procedure Append_People, Line 71 [Batch Start Line 0]
Cannot bulk load because the file "N:\path\filename.csv" could not be opened. Operating system error code 3(The system cannot find the path specified.).

Once I established the problem is in SSMS, I broadened my search and discovered the issue is the path for the bulk insert command has to be relative to the machine hosting the database. So in the VM (client machine until I migrate the db) when I use the path c:\ thinking it's the VM's c:\ drive the stored procedure is looking at the c:\ of my laptop since it's the host machine. With that I also learned that on a shared drive (N:\) access is delegated and that is its own issue (https://dba.stackexchange.com/questions/44524/bulk-insert-through-network).

So I'm going to focus on migrating the database first, then that'll solve my problem. Thanks to all who tried to help

Upvotes: 1

Related Questions