[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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):

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

649 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