locked
Can't Attach a new DB. AdventureWorks . . . doesn't 'work' RRS feed

  • Question

  • I’m trying to attach the AdventureWorks DB.  Following the sample here:
    http://blog.sqlauthority.com/2007/08/24/sql-server-2005-t-sql-script-to-attach-and-detach-database/


    USE [master]
    GO
    CREATE DATABASE [AdventureWorks] ON
    ( FILENAME = N'C:\Users\Excel\Desktop\Coding\SQL Server\Sample Databases\AdventureWorks2000_Data.mdf'),
    ( FILENAME = N'C:\Users\Excel\Desktop\Coding\SQL Server\Sample Databases\AdventureWorks2000_Data.ldf')
    FOR ATTACH
    GO



    Whether or not I open the DB as ‘administrator’, I get the following message:
    Msg 5133, Level 16, State 1, Line 1
    Directory lookup for the file "C:\Users\Excel\Desktop\Coding\SQL Server\Sample Databases\AdventureWorks2000_Data.mdf" failed with the operating system error 5(Access is denied.).


    If I right-click 'Databases' and select 'Attach', I get the following error:


    I think part of the proiblem is that SQL Server is trying to attach something named 'WorkOrders.ndf'.  Well, where really is NO SUCH THING, so I have no idea what this thing is talking about.

    I really like SQL Server, but I really hate all the false errors.  I'd say, 90% of the time, or more, the error I get is completely nonsensical, or just flat out, completely wrong.  that's what maket is so hard to debug.

     
    Sunday, March 25, 2012 11:20 PM

Answers

  • OMG!!  i finally found a solution!! 

    Download AW for MS Access:

    http://adventureworksaccess.codeplex.com/

    I just used the 'upsize wizard' to get all the tables into SQL Server in a couple minutes.  Beautiful!!  Love it!!

    MS Access will nevre go away.  It helps users to do tasks in mere minutes, that would otherwise take hours, or days, or who knows how long.  I love SQL Server, but sometimes I think it is too powerful and it is subject to too many restrictions.  Can't attach a DB, ok, fine.  Let me override the process; let me FORCE it to work. Sometimes, stuff needs to get done.  Period.

    • Proposed as answer by Iric Wen Tuesday, March 27, 2012 8:56 AM
    • Marked as answer by Iric Wen Tuesday, April 3, 2012 5:49 AM
    Monday, March 26, 2012 9:27 PM

All replies

  • Under what login you have connected and running those statements? Does that login have an appropriate permissions to read/write from that directory?

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, March 26, 2012 8:02 AM
    Answerer
  • Good question.  I don't have a Login.  I have S-Server saved as a shortcut under my 'button' (using Windows 7).  I left-click on the button, right-click on SQL Server, select 'Run As Administrator', and click 'Connect'.  The server name is '(local)'.  That is the default.  That's all I have.  There are a coupld DB's already attached (i.e., Northwind, Pubs, and Apress Financial).

    I have no Logins, or prompts of any kind, on Windows 7.

    Any other ideas?

    Thanks!!


    • Edited by ryguy72 Monday, March 26, 2012 12:23 PM
    Monday, March 26, 2012 12:21 PM
  • Hello,

    You should locate the database files in your user profile (on desktop), the SQL Server service account don't have the permission to access your (private) user profile. Move the file to the DATA folder of the SQL Server instance.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Monday, March 26, 2012 12:35 PM
  • Probably UAC  issue in W7

    http://www.optimizingpc.com/windows7/windows_7_useraccountcontrol.html  

    Connect to SQL Server with a login gthat is a memeber of sysadmin role and try attach

    http://sqlblog.com/blogs/uri_dimant/archive/2010/09/02/be-careful-to-grant-dbcreator-server-role-to-the-user.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Monday, March 26, 2012 12:38 PM
    Answerer
  • Thanks, Olaf.  I created a new folder, and put my DBs into that folder.  Now, SQL Server can't find that folder; can't navigate to it.  I thought, ok, I'll force it to work.  I put the path right into the 'Attach' box; it keeps saying the Dbs are NOT there...I can assure you they are. 

    SQL Server LOVES this location!!  It ALWAYS takes me here:

    Even there, it chokes.  I will find the DBs, but it won't attach the DBs.  That seems ot be the ONLY thing it knows, and it doesn't even know how to handle the DBs there.  Ugh!!

    Thanks, also, Uri.  I downed the warnings to nothing; NO warnings.  Nevertheless, I still get messages popping up about NOT being able to attach the DBs.

    I say DON'T NOTIFY me.  So what does it do, NOTIFY me. 

    • Edited by ryguy72 Monday, March 26, 2012 1:48 PM
    Monday, March 26, 2012 1:45 PM
  • OMG!!  i finally found a solution!! 

    Download AW for MS Access:

    http://adventureworksaccess.codeplex.com/

    I just used the 'upsize wizard' to get all the tables into SQL Server in a couple minutes.  Beautiful!!  Love it!!

    MS Access will nevre go away.  It helps users to do tasks in mere minutes, that would otherwise take hours, or days, or who knows how long.  I love SQL Server, but sometimes I think it is too powerful and it is subject to too many restrictions.  Can't attach a DB, ok, fine.  Let me override the process; let me FORCE it to work. Sometimes, stuff needs to get done.  Period.

    • Proposed as answer by Iric Wen Tuesday, March 27, 2012 8:56 AM
    • Marked as answer by Iric Wen Tuesday, April 3, 2012 5:49 AM
    Monday, March 26, 2012 9:27 PM