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.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan