SQL: Operating system error code 3(The system cannot find the path specified.)

please refer lionk:

http://www.dbforums.com/microsoft-sql-server/1615912-what-alternatives-bulk-insert.html

please refer link:

http://www.sql-server-performance.com/forum/threads/could-not-bulk-insert-because-file-c-csv-ame-txt-could-not-be-opened-operating-system-error-code-3-the-system-cannot-find-the-path-specified.29681/

hi,

you can mention the file path as \\server\directory\filename

ensure you have set appropriate permissions to access the file remotely

Thanks for prompt response.

I tried to execute following query on SQL

Bulk insert [Order]
FROM '\\192.168.101.178\abc\orders.csv'
WITH (FIELDTERMINATOR = '@#$#$#%@')

but i am getting following error now.
Cannot bulk load because the file "\\192.168.101.178\tsm\orders.csv" could not be opened. Operating system error code 5(Access is denied.).


Any suggestion?

Do you have access inthat file?

Access to the file from SQL server?

if you are asking the above then i think i have as if i try this "\\192.168.101.178\tsm\orders.csv" on SQL server it opens the file.

Also make sure that that file is not used somewhere else  by you or by some other user/procedure

Check the below link ,please confirm it
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68356
http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/137d2f16-bed3-4b98-a16f-f6db1a961c46/

I think em getting a bit closer now. I have loggedIn to SQL server with SA account and it has following roles public and sysadmin. while a role name as  'bulkadmin' is uncheck. Do it has anything to do with bulk insertion script?  

@angelIII: No i am logged in as SA which is a localsystem account on SQL server.

Yes it is running with the localsystem account i have checked it from SQLServer Service properties.
Now what to do?

you change that to a (best a dedicated) domain account (which has permissions to the relevant folder), restart the sql server service, and try your bulk operation again.

@angelIII:: Can you please tell me if my local machine and SQL server machine are on diffrent domains then will it be possible to run bulk insert script on SQLServer (on Domain DEF) from a file from my machine which is on Domain ABC?

if there is a trust relationship between the 2 domains, this can be possible.

I did as you directed.
1.SQL Service Logging as most privileged user who has access to the shared folders of my machine.
2. I ran the script on SQL and got this error now :(

Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

FYI: both the machine are on the same domain.

I ran it again and got these errors instead.

Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

you need to check the sql server errorlog file, you shall find more information there ...
this however seems also to indicate that you have named pipes enabled on the server ... any concrete reason to keep that one? possibly disable that one .

Thank you so much for being with me Mr. angelIII, really appreciated.

I ranthe script again and now i am getting these errors.

Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

From my experience the BULK Load error you are getting can be caused if there is a blank line at the end of the file.  Go to the last line of data and delete everything after that. If on the last line you can hit the arrow key and the cursor doesn't go to the line below, you're done. That's bitten me a couple of times.

The second error could be related to a timeout.  Set the timeout to 0 and see if it fixes the problem.  If you're unsure how to do that you can do it two ways.  

First Way:
In SSMS, right click on the name of the server and click properties.  Select the "Connections" option on the left side. Change the "Remote Server Timeout" to "0"

Second way is do it inline at the beginning of your script:
exec sp_configure 'remote query timeout', 0
GO
reconfigure with override
GO

I'm thinking the third error might be related to the second in some way.

Hope that helps you,
Joe J

Phew!!!!!!

Yes!!! i have done it. Thanks a lot for all the help you gave me Mr. Angelll :). Full marks to you. :)

Thanks to you too Mr. anandarajpandian:your comments were eqully good.

Oppps how to accept multiple solutions :(? i wanted to accept angelll solution as well!!! :(

I presume you wanted to split the points?