I haven't been able to resolve this problem for days now. It's kind of complicated but I am hoping I can describe the problem clear and one of you guys could help me dig out the root cause.
I have a stored procedure located in a SQL Server database running on a server, let's call it MYSQLSERVER that does something like:
bulk insert #mytable from ‘\\MYFILESERVER\data\myfile.csv’
The "data" folder of MYFILESERVER is a shared network drive that I and everyone in the office has read/write/exeute access right to it.
The stored procedure works fine if I execute it from MYSQLSERVER through Management Studio but gives me error message if:
1. I execute it from my desktop either through Management Studio or through a VBA program located on the ''MYFILESERVER\data" network drive. The error message:
"Cannot bulk load because the file "\\MYFILESERVER\data\myfile.csv" could not be opened. Operating system error code 5(Access is denied.)."
2. if I execute it from MYSQLSERVER through the VBA program located on MYFILESERVER/data. Error message is thrown at the code trying to access to resulting recordset:
"Operation is not allowed when the object is closed."
I use a domain account to log in my desktop. I am also able to log on to MYSQLSERVER using the same account. I have no problem read/write a file on the shared network drive on MYFILESERVER either from my desktop or from MYSQLSERVER using this same account.
Please help. Thank you.