Hello.

I have a big problem with SQL Server Express 2005. I had installed SQL
Server Dev Edition 2005 and SQL Express 2005, both working fine and together
with Visual Studio 2008. Starting on saturday none of the two SQL Servers
are working. I tried a repair installation which didn't work. Then I
uninstalled them both and reinstalled completely. After that SQL Server Dev
was ok, but not the Express. I can connect to it using Management Studio or
Visual Studio but cannot attach a mdf file. I get some of these errors: OS 5
(5120 oder 5134), Network error 25 or 26. When trying to add a database file
to a website in visual studio - or double clicking an existing one - i get
an error saying "Connections to SQL Server files (*.mdf) require SQL Server
Express 2005 to function properly. Please verify the installation of the
component or download from the URL:
http://go.microsoft.com/fwlink/?LinkId=49251 [OK]".

I tried allowing full remote permissions to SQL Express, changed the service
isntance running under LOCAL SYSTEM instead of network service, gave both
accounts full control to relevant files and folders, nothing worked. I've
completely uninstalled everything concerning M;S SQL and reinstalled SQL
Express after reboot from the VS 2008 DVD, nothing.

I need SQL Express for three different projects and no idea what te problem
could be. My environment:
Vista Business x64 without SP1
VS 2008 Team Edition
SQL Server Development Edition 2005 SP2 (not installed at the moment)
SQL Express 2005 SP2 (at the moment without advanced services, management
studio express etc.)

If there is something different which should work (other editions), maybe
SQL Server Express 2008 CTP if it is stable enough. I have access to MSDN
Academic of our university, so that's no problem.

Thanks,

Marco

> with Visual Studio 2008. Starting on saturday none of the two SQL Servers
> are working.

What does not working mean? Well, largely irrelevant now, but an uninstall
and reinstall may have been a bit drastic.

> was ok, but not the Express. I can connect to it using Management Studio
> or Visual Studio but cannot attach a mdf file.

How is Visual Studio connecting to SQL Server Express? Are you using an
instance name? What about authentication type?

> SQL Express 2005 SP2 (at the moment without advanced services, management
> studio express etc.)

Then how can you be sure you can connect to it using Management Studio, if
that is not there?

> If there is something different which should work (other editions), maybe
> SQL Server Express 2008 CTP if it is stable enough.

Is SQL Server Express 2005 really the edition that ships on the VS 2008 DVD?
Have you verified that it is SP2 or better? Also I wasn't quite sure
exactly what support there is right now for VS2008 connectivity to SQL
Server 2005. It must be there but I think you may need some kind of patch?
My recollection is fuzzy, I remember hearing or reading about this, but as I
don't use Visual Studio for database development, it hasn't impacted me
directly.

A

Hi Marco,
I understand that you encountered the database connection error in your
Visual Studio 2008 when you tried to add a database file to a website or
double clicking the existing one.
If I have misunderstood, please let me know.

Regarding your description "I can connect to it using Management Studio or
Visual Studio but cannot attach a mdf file.", I could not understand very
well. Would you mind first confirming with me the following question:
Did you mean that you also could not run "sp_attach_db" or
"sp_attach_single_file_db" to attach a .mdf file in Management Studio?
(Note: To attach a database, the database must be detached before.)

From the error message, it seemed that this issue was not related to SQL
Server Management Studio, instead it was a connection error only happened
in Visual Studio 2008. If so, I recommend that you first check if the
default SQL Server Instance Name was correctly set in your IDE. You can
check it via selecting Tools -> Options -> Database Tools -> Data
Connections. If the value of the field "SQL Server Instance Name (blank
for default)" is not SQLEXPRESS, change it to SQLEXPRESS ( if your sql
express instance name is not SQLEXPRESS, change it to your instance name).
This issue often happens if you install SQL Server Express after you
install Visual Studio.

If you have any other questions or concerns, please feel free to let me
know. Have a nice day!


Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hello Aaron,

>> with Visual Studio 2008. Starting on saturday none of the two SQL Servers
>> are working.
>
> What does not working mean? Well, largely irrelevant now, but an
> uninstall and reinstall may have been a bit drastic.

I decided to try this after all what I found in the web (service account,
protocols, ...) didn't work. I tried that options after the reinstall, too.
>
>> was ok, but not the Express. I can connect to it using Management Studio
>> or Visual Studio but cannot attach a mdf file.
>
> How is Visual Studio connecting to SQL Server Express? Are you using an
> instance name? What about authentication type?

It is connection to ".\SQLEXPRESS" using Windows Authentication, which
should be the default. I don't know which protocol it uses, but all are
enabled (Shared Memory, Named Pipes, TCP, VIA) local and remote. Windows
user has Administrator rights and I tried to start Visual Studio normal and
elevated.

>> SQL Express 2005 SP2 (at the moment without advanced services, management
>> studio express etc.)
>
> Then how can you be sure you can connect to it using Management Studio, if
> that is not there?

Because I had (and now again have) installed the "big" Management Studio
coming with Developer Edition. And I guess I don't need both studios.

>> If there is something different which should work (other editions), maybe
>> SQL Server Express 2008 CTP if it is stable enough.
>
> Is SQL Server Express 2005 really the edition that ships on the VS 2008
> DVD?
Yes.
> Have you verified that it is SP2 or better?
When I install SQL Server or SQL Express from another disc (expected the
download "SQL Server Express 2005 SP2 - with or without Advanced Services")
Vista tells me that it has compatibility problems and SP2 is required. From
VS2008 DVD this message didn't appear.

> Also I wasn't quite sure exactly what support there is right now for
> VS2008 connectivity to SQL Server 2005. It must be there but I think you
> may need some kind of patch?
VS2008 fully supports SQL Server 2005 because it was developed and released
after SQL Server 2005, not so with SQL Server 2008 which currently is in
development. There you need a patch.

> My recollection is fuzzy, I remember hearing or reading about this, but as
> I don't use Visual Studio for database development, it hasn't impacted me
> directly.
It's not only Visual Studio, it's also .NET resp. the ADO.NET classes for
working with SQL Server (SqlConnection, SqlCommand, ...) throwing the
exceptions I wrote in my first post.

Marco

Hi Charles,

> Hi Marco,
> I understand that you encountered the database connection error in your
> Visual Studio 2008 when you tried to add a database file to a website or
> double clicking the existing one.
> If I have misunderstood, please let me know.
Exactly.

> Regarding your description "I can connect to it using Management Studio or
> Visual Studio but cannot attach a mdf file.", I could not understand very
> well. Would you mind first confirming with me the following question:
> Did you mean that you also could not run "sp_attach_db" or
> "sp_attach_single_file_db" to attach a .mdf file in Management Studio?
> (Note: To attach a database, the database must be detached before.)
The databases are detached, furthermore the instance to which they have been
attached does not exists any more (SQL Server Dev which is currently
uninstalled). Using sp_attach_single_file_db I get this error message:

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file "S:\SQL2005\Data - Copy
(2)\FeedbackSystem_01.mdf". Operating system error 5: "5(error not found)".

And in addition, I'm not able to copy that file to another location (cannot
read). It seems that it got corrupted in some way, maybe on detaching or so.
Another mdf file (which is the most important for me at the moment), can be
attached with Management Studio and attaching from Visual Studio also seem
to work now (it was the data connection setting still set to MSSQLSERVER).
>
> If you have any other questions or concerns, please feel free to let me
> know. Have a nice day!

Is there any way to repair the corrupted mdf files? It's actually one which
is important. That project is also just at the beginning and the database
doesn't contain any data (or just some few records which are not important.
The important part is the schema.)

Thanks and Best regards,
Marco von Frieling

> Msg 5120, Level 16, State 101, Line 1
> Unable to open the physical file "S:\SQL2005\Data - Copy
> (2)\FeedbackSystem_01.mdf". Operating system error 5: "5(error not found)".

What is S:\ ? Is it a mapped drive? What happens when you run:

EXEC master..xp_cmdshell 'dir S:\';

? In other words, are you sure SQL Server can even see S:\? Did you try a
UNC path instead?

> And in addition, I'm not able to copy that file to another location (cannot
> read). It seems that it got corrupted in some way, maybe on detaching or so.

Ah, ok. So the problem is with the file or the folder, not with SQL Server.

> Is there any way to repair the corrupted mdf files? It's actually one which
> is important.

Then you would think you would have a backup, and/or would have detached it
in a proper manner?

I'm afraid you're out of luck... If you can't attach the MDF file, there's
not much SQL Server is going to be able to do for you.

> That project is also just at the beginning and the database
> doesn't contain any data (or just some few records which are not important.
> The important part is the schema.)

In addition to regular backups, source control is another good ally that you
should get comfortable with.

Hi Aaron,

> What is S:\ ? Is it a mapped drive? What happens when you run:
> ? In other words, are you sure SQL Server can even see S:\? Did you try
> a
> UNC path instead?

S:\ is a NTFS partition on my primary (and only) hard disc, so SQL Server
must see it.

>> And in addition, I'm not able to copy that file to another location
>> (cannot
>> read). It seems that it got corrupted in some way, maybe on detaching or
>> so.
>
> Ah, ok. So the problem is with the file or the folder, not with SQL
> Server.
Yes, I guess so. It seems that a process has an exclusive reference to that
files because I can't copy or zip them. But I don't know which process that
could be. The MSSQLSERVER service' process not, because that service doesn't
exists anymore.
>
>> Is there any way to repair the corrupted mdf files? It's actually one
>> which
>> is important.
>
> Then you would think you would have a backup, and/or would have detached
> it
> in a proper manner?
No, I have no backup.
Yes I guess/hope so. I executed the dteach command in the Management Studio
which returned a success message, no error.
>
> I'm afraid you're out of luck... If you can't attach the MDF file, there's
> not much SQL Server is going to be able to do for you.
Maybe I can attach them if I can release the file lock described above.

>> That project is also just at the beginning and the database
>> doesn't contain any data (or just some few records which are not
>> important.
>> The important part is the schema.)
>
> In addition to regular backups, source control is another good ally that
> you
> should get comfortable with.

Source control is a very good idea and normally I use it extensively, but I
got access to the universities SVN server after that crash :-(

> Yes, I guess so. It seems that a process has an exclusive reference to
> that files because I can't copy or zip them.

Get FileMon from
http://technet.microsoft.com/en-us/s...s/default.aspx

> Maybe I can attach them if I can release the file lock described above.

It's possible, cross your fingers. Or get out your notes and start
re-tracing your steps. :-(

A

Hi Marco,
I agree with Aaron's suggestions.

Before we perform further research, please use FileMon or Process Monitor
(http://www.microsoft.com/technet/sys...cessmonitor.ms
px) to monitor all the processes on your computer. You can set the filter
as "[Path] [contains] [FeedbackSystem_01]". Then you can find which
process is using your file.

Also for the operating system error 5, if you run "net msg 5" from
command line, you will get the following error message "Access is denied",
so it also seems that you do not have permissions on accessing this file.
You may first check if your current windows logon account has read/write
permissions on the file.

If you are an administrator and could not copy the file to other place, I
would like to know if it is possible for you to restart your computer with
safe mode and then check if you can copy the file.


Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

You can attach database to SQL Server directly from SQL Server
Comparison Tool (SCT) - a program which can be downloaded from

http://www.sql-server-tool.com

In order to add your SQL Server Express to the list of servers
available for SCT please click menu item Tools -> Add Server (if you
don't have at least one server added to SCT, the program will display
"Add Server" dialog automatically). As a server name please enter
\SQLEXPRESS.

If SCT displays error message, please exit the application, restart it
and check if the server was added to the list; if it was, please try
to connect to it - the chances are that the connection will be
established properly.

Once you are connected to the server, please click menu item Tools ->
Attach Database, specify database name and the location of files, and
click button "Attach". SCT should attach the database and display
confirmation message.


Dariusz Dziewialtowski

> "Add Server" dialog automatically). As a server name please enter
> \SQLEXPRESS.

Unless you used a different instance name.

> Once you are connected to the server, please click menu item Tools ->
> Attach Database, specify database name and the location of files, and
> click button "Attach". SCT should attach the database and display
> confirmation message.

And if the file doesn't exist, or is corrupt, or is inaccessible due to
permissions... What does this tool do to get around that problem, that
CREATE DATABASE FOR ATTACH can't do?

On May 8, 9:37 am, "Aaron Bertrand [SQL Server MVP]"
<ten....@dnartreb.noraa> wrote:
> > "AddServer" dialog automatically). As aservername please enter
> > \SQLEXPRESS.
>
> Unless you used a different instance name.

I stand corrected - \SQLEXPRESS is default instance name but of course
if it was changed, the new name has to be entered.


> And if the file doesn't exist, or is corrupt, or is inaccessible due to
> permissions... What does thistooldo to get around that problem, that
> CREATE DATABASE FOR ATTACH can't do?

Well, nothing more than CREATE DATABASE FOR ATTACH, though many users
find it convenient to do "one-click" attaching from within the tool.
But since I'm the author of the tool, I might be biased...


Dariusz Dziewialtowski

>> And if the file doesn't exist, or is corrupt, or is inaccessible due to
>> permissions... What does thistooldo to get around that problem, that
>> CREATE DATABASE FOR ATTACH can't do?
>
> Well, nothing more than CREATE DATABASE FOR ATTACH, though many users
> find it convenient to do "one-click" attaching from within the tool.
> But since I'm the author of the tool, I might be biased...
>

Of course. I only asked because, in this case, the OP would not gain
anything from your tool, because a normal attach does not work.

Hi Charles, Aaron,

thanks for your answers.

> Before we perform further research, please use FileMon or Process Monitor
> (http://www.microsoft.com/technet/sys...cessmonitor.ms
> px) to monitor all the processes on your computer. You can set the filter
> as "[Path] [contains] [FeedbackSystem_01]". Then you can find which
> process is using your file.

OK, I've downloaded Process monitor and set up the filter. I tried to using
Windows Explorer and Total Commander and except these two processes it
listed only two other tools: TSVNCache.exe (disabling it doesn't change
anything) and "RarExtLoader.exe" which is part of WinRar. But I have no idea
why this process accessed the mdf file. I've scanned the whole WinRar folder
for viruses - safe is safe - but it is clean.

> Also for the operating system error 5, if you run "net msg 5" from
> command line, you will get the following error message "Access is denied",
I know
> so it also seems that you do not have permissions on accessing this file.
> You may first check if your current windows logon account has read/write
> permissions on the file.
The problem is that every acccount on my system has full control to the file
itself, the folder contaiining the file and the folder I tried to copy the
file into.

>
> If you are an administrator and could not copy the file to other place, I
> would like to know if it is possible for you to restart your computer with
> safe mode and then check if you can copy the file.
I tried and it worked. But Process monitor doesn't work in safe mode, it's
driver could not be loaded.

Then I rebootet in normal mode and tried to attach the mdf file to SQL
Express using Management Studio and got an error 15110 which seems to be
related to the database owner (what I found in the forums), a second try
(because I closed the window to fast to copy out details) resulted again in
the well-known 5123 error: Here the details from Process monitor for that
second try:

Process Name: sqlserver.exe
Operation: Create File
Result: ACCESS DENIED
Desired Access: Generic Read
Disposition: Open
Options: No Buffering, Non-Directory File, Open No Recall
Attributes: n/a
ShareMode: Read
AllocationSize: n/a
Impersonating: mfrieling-noteb\mfrieling



Again thanks for your .
Marco

Hi Marco,

Yes, ProcMon cannot run in Safe mode, I just wanted you to copy files in
safe mode.



The ACCESS DENIED error came from SQL Server process and seemed related to
impersonating. Now I would like to check with you what your SQL Express
service account is. Does the service account have enough permission to
access your .mdf file? You can find the SQL Express service account via the
following steps:

Click Start->Run..., type services.msc, click OK, double click SQL Server
(SQLEXPRESS), switch to the Log On tab.



You may first try changing the service account to a domain user account that
is also a local administrator to see if it s.



If this does not , I am afraid that either your .mdf file or your SQL
Server Express instance may have some problem. To check if this issue is
related to your MDF file, I recommend that you try attaching the .mdf file
to your SQL Server Developer Edition instance or other SQL Server instance
on other machine to see if this issue persists. If your MDF file could not
be attached to any SQL Server instance, I am afraid that the file is most
likely corrupted. You need to use your latest backup file to restore the
database. If you have no database backup file, I am afraid that you need to
contact some third party data recovery company to recover the data if it is
important to you; If the .mdf file can be attached to other SQL Server
instance, I recommend that you perform a database backup and then try
restoring it to your SQL Express instance to see if it s. If this
process fails, you may need to re-install SQL Server Express instance.



Hope this s. If you have any other questions or concerns, please feel
free to let me know.




Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======






"Marco von Frieling" <marco-klu@community.nospam> wrote in message
news:%23OrCENgsIHA.576@TK2MSFTNGP05.phx.gbl...
> Hi Charles, Aaron,
>
> thanks for your answers.
>
>> Before we perform further research, please use FileMon or Process Monitor
>> (http://www.microsoft.com/technet/sys...cessmonitor.ms
>> px) to monitor all the processes on your computer. You can set the filter
>> as "[Path] [contains] [FeedbackSystem_01]". Then you can find which
>> process is using your file.
>
> OK, I've downloaded Process monitor and set up the filter. I tried to
> using Windows Explorer and Total Commander and except these two processes
> it listed only two other tools: TSVNCache.exe (disabling it doesn't change
> anything) and "RarExtLoader.exe" which is part of WinRar. But I have no
> idea why this process accessed the mdf file. I've scanned the whole WinRar
> folder for viruses - safe is safe - but it is clean.
>
>> Also for the operating system error 5, if you run "net msg 5" from
>> command line, you will get the following error message "Access is
>> denied",
> I know
>> so it also seems that you do not have permissions on accessing this file.
>> You may first check if your current windows logon account has read/write
>> permissions on the file.
> The problem is that every acccount on my system has full control to the
> file itself, the folder contaiining the file and the folder I tried to
> copy the file into.
>
>>
>> If you are an administrator and could not copy the file to other place, I
>> would like to know if it is possible for you to restart your computer
>> with
>> safe mode and then check if you can copy the file.
> I tried and it worked. But Process monitor doesn't work in safe mode, it's
> driver could not be loaded.
>
> Then I rebootet in normal mode and tried to attach the mdf file to SQL
> Express using Management Studio and got an error 15110 which seems to be
> related to the database owner (what I found in the forums), a second try
> (because I closed the window to fast to copy out details) resulted again
> in the well-known 5123 error: Here the details from Process monitor for
> that second try:
>
> Process Name: sqlserver.exe
> Operation: Create File
> Result: ACCESS DENIED
> Desired Access: Generic Read
> Disposition: Open
> Options: No Buffering, Non-Directory File, Open No Recall
> Attributes: n/a
> ShareMode: Read
> AllocationSize: n/a
> Impersonating: mfrieling-noteb\mfrieling
>
>
>
> Again thanks for your .
> Marco

Hi Marco,
Just check with you to see what everything is going on regarding this
issue. If you have any questions or concerns, please feel free to let us
know.

Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Charles,

I had some troubles the last week and still have them. Because of them I
had/have no time till now to try your latest suggestions.

Best regards,
Marco


""Charles Wang [MSFT]"" <changliw@online.microsoft.com> schrieb im
Newsbeitrag news:U4h2WCntIHA.4408@TK2MSFTNGHUB02.phx.gbl...
> Hi Marco,
> Just check with you to see what everything is going on regarding this
> issue. If you have any questions or concerns, please feel free to let us
> know.
>
> Have a nice day!
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== =======
> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@microsoft.com.
> ================================================== =======
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== =======
>

Hi Marco,
Thank you for letting me know this. Please feel free to post back when you
have convenient time to try the suggestions. We will monitor this post
closely.

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Charles,

I've changed the SQL Express instance from LOCAL SYSTEM account to the only
user account which is local administrator and also the account under which
VS2008 is running. This works. But if I change the instance back to a system
account like LOCAL SYSTEM or so, it doens't work. Both accounts have exactly
the same permission on the SQL Express installation folder and the folder
containing the mdf files.

Best regards,
Marco


""Charles Wang [MSFT]"" <changliw@online.microsoft.com> schrieb im
Newsbeitrag news:AbkTpnYuIHA.1788@TK2MSFTNGHUB02.phx.gbl...
> Hi Marco,
> Thank you for letting me know this. Please feel free to post back when you
> have convenient time to try the suggestions. We will monitor this post
> closely.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ================================================== =======
> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@microsoft.com.
> ================================================== =======
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== =======
>

Hi Marco,
Thank you for your response.

I am glad to hear that this issue has been resolved by changing the service
account to another user account. I think that maybe something requires
NETWORK access during the process. You know that LOCAL SYSTEM has very
limited network access privileges.

Anyway congratulations! If you have any other questions or concerns, please
feel free to let me know. Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======