SQL Server File Location: Points to Consider

In SQL Server, file locations can be specified in some commands like during bulk insert, listing directory information, using OPENROWSET function, etc. When you specify file location, you should note the following points:

1. By default SQL Server searches for the file location in Server's directory. For example, in a Bulk insert command, if you specify D:\emp.txt, it is by default searched in the Server's
directory. If your server name is myServer, SQL Server will try to find out the file path in that server only.

2. SQL Server will not recognize client's file location until specified using the UNC path. For example say your Server name is myServer and the SQL Server client is installed in your local system named myClient that connects to Server. Say you have a file named emp.txt in your computer’s D drive and you want to specify that file location in the BULK INSERT command. In this case, the command should be

3. When UNC path is specified, the file should be given atleast a read access to the server, in which

the Query is executed. In the above example, D:\emp.txt file should be given atleast read only access on the server myServer. Otherwise an error "File not found error" will be thrown

The same applies to other functions as well which accept file locations - such as xp_cmdshell, OPENROWSET etc.