• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

Attache DB with single file

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
edwinson
Asked:
edwinson
  • 4
  • 4
1 Solution
 
Valliappan ANSenior Tech ConsultantCommented:
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
 
edwinsonAuthor Commented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
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
Technology Partners: 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!

 
Valliappan ANSenior Tech ConsultantCommented:
I could not think you could be able to register a remote server's file, to a server, unless you copy it locally.
0
 
edwinsonAuthor Commented:
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
 
Valliappan ANSenior Tech ConsultantCommented:
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
 
edwinsonAuthor Commented:
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
 
edwinsonAuthor Commented:
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
 
MoondancerCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now