RESOLVED RESOLVED An attempt to attach an auto-named database for file <file string> faile

RESOLVED RESOLVED RESOLVED

DOH! When I rebuilt the OS, I changed the data partition ID's, and didn't remember to change them in the connetion strings

Not great, but better than continuing to bang head against wall.

Would like to say to MS appreciators that the VS2005 reinstall actually went quite well, considering.

===========================================================================

I built an app, works fine. Had the development environment working fine (VS2005 (SP0) and SQL Server Express). It had been stable for months, no problem.

Then I had to reinstall XP Pro...(finally threw too many conflicting programs into it, I guess...)

I managed to get VS2005 reinstalled ok. However, when I opened and compiled my existing app in VS2005, using Cassini, I got the dreaded error:

An attempt to attach an auto-named database for file <file string> failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I also get this error for the compiled copy of the app on the same machine, running on IIS. (Where it also previously worked fine).

The Connection strings are all of the form

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=(specific file string).MDF;Integrated Security=True;User Instance=True"

I appreciate that there are zillions of posts on this issue. I'm hoping that someone might know why this should happen in this particular case.

The thing that's different about this case is that the app was working fine; I have the app deployed on a demo machine and also on a remote server as well as on the development machine.

The only thing that changed is that there was this fresh install of SQL Server Express.

I tried using >> sp_configure user instances enabled','1';RECONFIGURE <<< in SSMSEE, which took, but didn't help.

So something is different in SSE. Since the app is stable everywhere else, I REALLY don't want to go through and adjust the connection strings...MUCH better to figure out what SSE needs to be happy with the existing connection strings...as it was before.

Any suggestions would be appreciated.

Thanks!

EDIT-TRIED SOME THINGS:

Changed folder and file permissions to allow ASPNET full access to the .mdf's and log files (though never had to do this before on the development machine.) No change...

Can see the table contents within the .mdf through SSMSE, and also through Server Explorer in VS2005.

So because the problem happens with both IIS and Cassini, I'm assuming it's got to be an issue between ASPNET and SSE.

EDIT TRIED SOME MORE THINGS:

I dimly remember that when I had this problem before, it was that the error message was too "dumb". It wasn't my file that had the permissions problems, it was that the SSE System Databases had to have permissions with respect to ASPNET.

I tried to set that up but I'm a complete newb on this area, so I don't know if I did it right. In SSMSE I created a Login for MyComputer/ASPNET, and gave it all permissions and roles, and then made it an owner for the four system databases...but no joy.

Any pointers on this last piece would be helpful.

EDIT - MORE INFO

The error details

[SqlException (0x80131904): An attempt to attach an auto-named database for file <filestring>.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181

The most common cause of this problem is that you have a database already listed as attached in your User Instance that has the same name that is being auto generated by the AttachDbFilename path and database file. The best way to resolve this is to connect to the User Instance when it is running and then find and drop the offending database so that the name is removed from master.

I have instructons on how to connect SSMS to your User Instance in the FAQ listed at the top of this forum.

Mike

RESOLVED RESOLVED An attempt to attach an auto-named database for file <file string> faile

RESOLVED RESOLVED RESOLVED

DOH! When I rebuilt the OS, I changed the data partition ID's, and didn't remember to change them in the connetion strings

Not great, but better than continuing to bang head against wall.

Would like to say to MS appreciators that the VS2005 reinstall actually went quite well, considering.

===========================================================================

I built an app, works fine. Had the development environment working fine (VS2005 (SP0) and SQL Server Express). It had been stable for months, no problem.

Then I had to reinstall XP Pro...(finally threw too many conflicting programs into it, I guess...)

I managed to get VS2005 reinstalled ok. However, when I opened and compiled my existing app in VS2005, using Cassini, I got the dreaded error:

An attempt to attach an auto-named database for file <file string> failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I also get this error for the compiled copy of the app on the same machine, running on IIS. (Where it also previously worked fine).

The Connection strings are all of the form

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=(specific file string).MDF;Integrated Security=True;User Instance=True"

I appreciate that there are zillions of posts on this issue. I'm hoping that someone might know why this should happen in this particular case.

The thing that's different about this case is that the app was working fine; I have the app deployed on a demo machine and also on a remote server as well as on the development machine.

The only thing that changed is that there was this fresh install of SQL Server Express.

I tried using >> sp_configure user instances enabled','1';RECONFIGURE <<< in SSMSEE, which took, but didn't help.

So something is different in SSE. Since the app is stable everywhere else, I REALLY don't want to go through and adjust the connection strings...MUCH better to figure out what SSE needs to be happy with the existing connection strings...as it was before.

Any suggestions would be appreciated.

Thanks!

EDIT-TRIED SOME THINGS:

Changed folder and file permissions to allow ASPNET full access to the .mdf's and log files (though never had to do this before on the development machine.) No change...

Can see the table contents within the .mdf through SSMSE, and also through Server Explorer in VS2005.

So because the problem happens with both IIS and Cassini, I'm assuming it's got to be an issue between ASPNET and SSE.

EDIT TRIED SOME MORE THINGS:

I dimly remember that when I had this problem before, it was that the error message was too "dumb". It wasn't my file that had the permissions problems, it was that the SSE System Databases had to have permissions with respect to ASPNET.

I tried to set that up but I'm a complete newb on this area, so I don't know if I did it right. In SSMSE I created a Login for MyComputer/ASPNET, and gave it all permissions and roles, and then made it an owner for the four system databases...but no joy.

Any pointers on this last piece would be helpful.

EDIT - MORE INFO

The error details

[SqlException (0x80131904): An attempt to attach an auto-named database for file <filestring>.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181

The most common cause of this problem is that you have a database already listed as attached in your User Instance that has the same name that is being auto generated by the AttachDbFilename path and database file. The best way to resolve this is to connect to the User Instance when it is running and then find and drop the offending database so that the name is removed from master.

I have instructons on how to connect SSMS to your User Instance in the FAQ listed at the top of this forum.

Mike

Resize DB in SQL 7

I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
leaves just enough room on the partition for the nightly backup files, but I
keep getting a msg saying that the drive is almost full. I cannot resize the
partition, and shrinking the db does not change the allocation size. How can
I reduce the allocation size and free up some disk space? Thanks in advance
for any suggestions.
Back it up
remove it
recreate it, smaller
restore it
Jan
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in
advance
> for any suggestions.
|||That will not do anything, as restore will create database files with same size as when you took the
backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message news:%2386SdCEuEHA.2072@.tk2msftngp13.phx.gbl...
> Back it up
> remove it
> recreate it, smaller
> restore it
> Jan
> "Ron B." <RonB@.discussions.microsoft.com> wrote in message
> news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I
> the
> can
> advance
>
|||One thing to try is to rebuild your indexes. Read about DBCC DBREINDEX. And then try a shrink again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but I
> keep getting a msg saying that the drive is almost full. I cannot resize the
> partition, and shrinking the db does not change the allocation size. How can
> I reduce the allocation size and free up some disk space? Thanks in advance
> for any suggestions.
|||You can try to use DTS copy the whole database to
another. detach two DBs and reattach the new DB with old
DB name.
Good luck
Mike
>--Original Message--
>One thing to try is to rebuild your indexes. Read about
DBCC DBREINDEX. And then try a shrink again.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Ron B." <RonB@.discussions.microsoft.com> wrote in
message
>news:636A452B-109A-439C-8DA4-
B1401C97E44C@.microsoft.com...[vbcol=seagreen]
2.9 GB used. This[vbcol=seagreen]
nightly backup files, but I[vbcol=seagreen]
full. I cannot resize the[vbcol=seagreen]
allocation size. How can[vbcol=seagreen]
space? Thanks in advance
>
>.
>
|||In message <636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com>, Ron B.
<RonB@.discussions.microsoft.com> writes
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>leaves just enough room on the partition for the nightly backup files, but I
>keep getting a msg saying that the drive is almost full. I cannot resize the
>partition, and shrinking the db does not change the allocation size. How can
>I reduce the allocation size and free up some disk space? Thanks in advance
>for any suggestions.
Using Enterprise Manager and Query Analyser:
1) EM - Backup the database and log file in question.
2) QA - Use DBCC DBREINDEX on each table in your database.
(May be better using a cursor in sproc - saves typing).
3) EM - Backup the database and log file again.
4) EM - Use tools 'Truncate Transaction Log' if log file too big.
5) EM - Use tools 'Shrink Database'.
Obviously, the backup at step 1 should be kept for good measure (just in
case) however the backup at step 3 can be deleted.
In SQL Server 7, the Shrink Database does not work very well, if at all,
until a backup has been performed. The re-indexing is required to reduce
fragmentation before a shrink is tried. I have found from time to time
that the above process needs to be run twice to get the actual physical
files to shrink any, specially if it has not been done for a very long
time.
Kind Regards,
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com

Resize DB in SQL 7

I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
leaves just enough room on the partition for the nightly backup files, but I
keep getting a msg saying that the drive is almost full. I cannot resize th
e
partition, and shrinking the db does not change the allocation size. How ca
n
I reduce the allocation size and free up some disk space? Thanks in advance
for any suggestions.Back it up
remove it
recreate it, smaller
restore it
Jan
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in
advance
> for any suggestions.|||That will not do anything, as restore will create database files with same s
ize as when you took the
backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message news:%2386SdCEuEHA.2072@.tk2msftngp13.ph
x.gbl...
> Back it up
> remove it
> recreate it, smaller
> restore it
> Jan
> "Ron B." <RonB@.discussions.microsoft.com> wrote in message
> news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I
> the
> can
> advance
>|||One thing to try is to rebuild your indexes. Read about DBCC DBREINDEX. And
then try a shrink again.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in advan
ce
> for any suggestions.|||You can try to use DTS copy the whole database to
another. detach two DBs and reattach the new DB with old
DB name.
Good luck
Mike
>--Original Message--
>One thing to try is to rebuild your indexes. Read about
DBCC DBREINDEX. And then try a shrink again.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Ron B." <RonB@.discussions.microsoft.com> wrote in
message
>news:636A452B-109A-439C-8DA4-
B1401C97E44C@.microsoft.com...
2.9 GB used. This[vbcol=seagreen]
nightly backup files, but I[vbcol=seagreen]
full. I cannot resize the[vbcol=seagreen]
allocation size. How can[vbcol=seagreen]
space? Thanks in advance[vbcol=seagreen]
>
>.
>|||In message <636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com>, Ron B.
<RonB@.discussions.microsoft.com> writes
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>leaves just enough room on the partition for the nightly backup files, but
I
>keep getting a msg saying that the drive is almost full. I cannot resize t
he
>partition, and shrinking the db does not change the allocation size. How c
an
>I reduce the allocation size and free up some disk space? Thanks in advanc
e
>for any suggestions.
Using Enterprise Manager and Query Analyser:
1) EM - Backup the database and log file in question.
2) QA - Use DBCC DBREINDEX on each table in your database.
(May be better using a cursor in sproc - saves typing).
3) EM - Backup the database and log file again.
4) EM - Use tools 'Truncate Transaction Log' if log file too big.
5) EM - Use tools 'Shrink Database'.
Obviously, the backup at step 1 should be kept for good measure (just in
case) however the backup at step 3 can be deleted.
In SQL Server 7, the Shrink Database does not work very well, if at all,
until a backup has been performed. The re-indexing is required to reduce
fragmentation before a shrink is tried. I have found from time to time
that the above process needs to be run twice to get the actual physical
files to shrink any, specially if it has not been done for a very long
time.
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.comsql

Resize DB in SQL 7

I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
leaves just enough room on the partition for the nightly backup files, but I
keep getting a msg saying that the drive is almost full. I cannot resize the
partition, and shrinking the db does not change the allocation size. How can
I reduce the allocation size and free up some disk space? Thanks in advance
for any suggestions.Back it up
remove it
recreate it, smaller
restore it
Jan
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but
I
> keep getting a msg saying that the drive is almost full. I cannot resize
the
> partition, and shrinking the db does not change the allocation size. How
can
> I reduce the allocation size and free up some disk space? Thanks in
advance
> for any suggestions.|||That will not do anything, as restore will create database files with same size as when you took the
backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jan Doggen" <j.doggen@.BLOCKqsa.nl> wrote in message news:%2386SdCEuEHA.2072@.tk2msftngp13.phx.gbl...
> Back it up
> remove it
> recreate it, smaller
> restore it
> Jan
> "Ron B." <RonB@.discussions.microsoft.com> wrote in message
> news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>> I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>> leaves just enough room on the partition for the nightly backup files, but
> I
>> keep getting a msg saying that the drive is almost full. I cannot resize
> the
>> partition, and shrinking the db does not change the allocation size. How
> can
>> I reduce the allocation size and free up some disk space? Thanks in
> advance
>> for any suggestions.
>|||One thing to try is to rebuild your indexes. Read about DBCC DBREINDEX. And then try a shrink again.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ron B." <RonB@.discussions.microsoft.com> wrote in message
news:636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com...
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
> leaves just enough room on the partition for the nightly backup files, but I
> keep getting a msg saying that the drive is almost full. I cannot resize the
> partition, and shrinking the db does not change the allocation size. How can
> I reduce the allocation size and free up some disk space? Thanks in advance
> for any suggestions.|||You can try to use DTS copy the whole database to
another. detach two DBs and reattach the new DB with old
DB name.
Good luck
Mike
>--Original Message--
>One thing to try is to rebuild your indexes. Read about
DBCC DBREINDEX. And then try a shrink again.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Ron B." <RonB@.discussions.microsoft.com> wrote in
message
>news:636A452B-109A-439C-8DA4-
B1401C97E44C@.microsoft.com...
>>I have a db with an allocated size of 24.3 GB, but only
2.9 GB used. This
>> leaves just enough room on the partition for the
nightly backup files, but I
>> keep getting a msg saying that the drive is almost
full. I cannot resize the
>> partition, and shrinking the db does not change the
allocation size. How can
>> I reduce the allocation size and free up some disk
space? Thanks in advance
>> for any suggestions.
>
>.
>|||In message <636A452B-109A-439C-8DA4-B1401C97E44C@.microsoft.com>, Ron B.
<RonB@.discussions.microsoft.com> writes
>I have a db with an allocated size of 24.3 GB, but only 2.9 GB used. This
>leaves just enough room on the partition for the nightly backup files, but I
>keep getting a msg saying that the drive is almost full. I cannot resize the
>partition, and shrinking the db does not change the allocation size. How can
>I reduce the allocation size and free up some disk space? Thanks in advance
>for any suggestions.
Using Enterprise Manager and Query Analyser:
1) EM - Backup the database and log file in question.
2) QA - Use DBCC DBREINDEX on each table in your database.
(May be better using a cursor in sproc - saves typing).
3) EM - Backup the database and log file again.
4) EM - Use tools 'Truncate Transaction Log' if log file too big.
5) EM - Use tools 'Shrink Database'.
Obviously, the backup at step 1 should be kept for good measure (just in
case) however the backup at step 3 can be deleted.
In SQL Server 7, the Shrink Database does not work very well, if at all,
until a backup has been performed. The re-indexing is required to reduce
fragmentation before a shrink is tried. I have found from time to time
that the above process needs to be run twice to get the actual physical
files to shrink any, specially if it has not been done for a very long
time.
Kind Regards,
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com