Solved

Attache DB with single file

Posted on 2001-09-05
9
273 Views
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

objSvr.attachdbwithsinglefile(dbName,dbFilepath)

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.

IDEAS?
0
Comment
Question by:edwinson
  • 4
  • 4
9 Comments
 
LVL 9

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.
0
 
LVL 1

Author Comment

by:edwinson
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.
0
 
LVL 9

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

objSvr.attachdbwithsinglefile(dbName,"c:\projects\mydata.mdb")
0
 
LVL 9

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.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 1

Author Comment

by:edwinson
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))
INSERT
  #Reg
                  EXEC                                                        master..xp_regenumvalues
   N'HKEY_LOCAL_MACHINE',
   N'SYSTEM\Controlset001\Services\lanmanserver\Shares'
SELECT *
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
instance
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.
0
 
LVL 9

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.
0
 
LVL 1

Author Comment

by:edwinson
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.
0
 
LVL 1

Author Comment

by:edwinson
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.
0
 
LVL 1

Accepted Solution

by:
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.

Thanks,

Moondancer
Community Support Moderator @ Experts Exchange
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now