Attache DB with single file

Posted on 2001-09-05
Medium Priority
Last Modified: 2008-02-01
I have an application that allows a user to perform extensive data mining functions on data warehouses.  Currently I am using MSDE to serve as the platform to house the data extracted for analysis.

Now MSDE gives the user a SQL server on the desktop and the application works great.

When a user starts a new project, a template database is copied from a template library and registered in the SQL server using


This works just fine since the user is storing the database mdf file on the local machine, thus local server.

I am now trying to modify the code to be a networked client server type of application where the SQL server no longer the MSDE on the local machine, but on some server on the network.

Now I can get the template MDF copied to the network SQL server, but I cannot get the AttachDBWithSingleFile method to work since the dbFilePath is now a network location.  SQL server hates this intensely.

I need to figure a way to attach this file to the SQL server using a file reference that is not a network reference.

Question by:edwinson
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4

Expert Comment

by:Valliappan AN
ID: 6459697
Copy the attaching DB file to the network, and register it local to that server. For example, copy to network's C drive MSSQL/DATA directory, and then register it as usual.

Hope it helps.

Author Comment

ID: 6460361
I already have done that part.  The trick is the client side has visiblity to the file based on the UNC.  This means the client can see \\SqlServer01\projects\mydata.mdf

SQL server will register the file only if the UNC is converted to an actual local reference on the server.  Thus the file name must be c:\projects\mydata.mdf. So I need to be able to convert
\\sqlserver01\projects\mydata.mdf to the local reference.  

Keep in mind this is just an example, a user could store the MDF file practically anywhere on the server.

Expert Comment

by:Valliappan AN
ID: 6462870
What I meant was copy the \\sqlserver01\projects\mydata.mdf to c:\projects\mydata.mdf.

Then you register it (in your way):


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Expert Comment

by:Valliappan AN
ID: 6462872
I could not think you could be able to register a remote server's file, to a server, unless you copy it locally.

Author Comment

ID: 6464262
I actually posted this question to the MS-SQL board at the same time.  Although the answers didn't get the solution right on the target, they did give me clues. Here is a solution:

Use an Query Results object of a SQLDMO server query execution method using this as the SQL query.

CREATE TABLE #Reg(Value nvarchar(255), Data nvarchar(255))
                  EXEC                                                        master..xp_regenumvalues
from #Reg  
where Data
   LIKE '%Path%'

This returns a table/view/recordset containing a complete listing of the shares on the server.
From there, checking the file path of the MDF file for any from right to left searching for the first
where a share name is found.  

At that point substitute the share name and everything in the path to the left of the share name with
the path as found in the query.

Expert Comment

by:Valliappan AN
ID: 6467091
What, if there is no shares for the MDF?

So, would you like to attach the database MDF file, from a client, and the MDF would not be located in the Server. Would it not cause problem, say like, if the client is not powered on, and the Server starts SQL server, then the respective database, will go to (dead) status, right?

Please correct me, if I am wrong.

Author Comment

ID: 6468019
You are correct on a couple of points.

If you are in a network environment and writing a file to a server, there MUST be a share or you will not see a directory location in which to save a file.  This is of course assuming a Windows environment.

So in order to write a file on the network, a network share must exist.  In fact if you have an opportunity you can check this out by looking at another computer on the network.  If you click the Network Neighborhood item, you will see a list of all the computers on the network.  If you pick one and cannot see any folders, then there have been no shares created on that computer.  Now go to the computer and make a shared directory.  Review the computer again on the network and you will see the share location.

With regard to the storage of a MDF on a network drive.  It is true that storing an MDF on a network drive will cause all kinds of grief when being registered.  In fact Microsoft EXTREMELY discourages this type of setup for the very reasons you mentioned.  

Microsoft does give a clue on how to still store an MDF file on a network location.  You can issue a command upon SQL startup to allow the save. There is a sever performance penalty to pay for doing this workaround, and it is HIGHLY HIGHLY discouraged.

Our website has a FAQ concerning this issue since we have had users who wanted to save database information on a network drive.  I don't want to get into trouble with shameless self promotion, but www.parametrica.com in the support area Project Allegheny.

Author Comment

ID: 6520815
This is deleted since most of the solution was obtained from other sources.  It was suggested by one of the posters to delete the question since I discovered the answer elsewhere.

Noteably I added comments to correct misconceptions of some posters related to the existence or non-existence of shared folders in a network environment.

Accepted Solution

Moondancer earned 0 total points
ID: 6772275
As I indicated in your Community Support question, rather than deleting this, I have refunded your 300 points to you and am moving this to our PAQ at zero points since you found your solution outside this question thread.  The question and solution may benefit others.

Any time you choose to grant points to experts for assistnce given, that may have helped guide you, you can do this by posting a new question in the same topic area, entitled Points for __expertname__ and in the comments include the primary question link.


Community Support Moderator @ Experts Exchange

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question