database vanishes like a David Blaine prop

[BobLewiston]

I've been working with SQL2008 AdventureWorks; specifically database AdventureWorks, table Person.Contact.

I experimented with using a DataGrid control's DataSource property to add a new project data source to the DataGrid, although my code was written to access the database without using a DataGrid. My code was unable to do so because, naturally enough, the database was already in use by the DataGrid.

However, when I removed the DataGrid, to my surprise the AdventureWorks database was just plain gone. That's right, not corrupted, but gone from the disk without a trace. The other databases (AdventureWorksDW, AdventureWorksDW2008, AdventureWorksLT, AdventureWorksLT2008, ReportServer$SQLEXPRESS, ReportServer$SQLEXPRESSTempDB) were still there, but not the database named simply "AdventureWorks".

So I reinstalled AdventureWorks. Now here's the part where you all conclude I've lost my mind: AdventureWorks installed without any problem - but no database named simply "AdventureWorks". No "Person.Contact" table, even in any of the other databases. No sign of them, no explanation.

But you can look up the AdventureWorks schema online and see that they DO exist. So I'm NOT insane after all - honest, fellas, I'm not dangerous, it's all right to talk to me.

So wha'd'ya think is going on here?

 

[Daeva]

Thats an interesting problem you have there. I'm not sure what is causing it (too many variables at this point as to what the prob is) however, I do know of a way to make it less of a headache in the future. When (if) you can get adventureworks installed again, open it up in SQL Server Management Studio and right click the database. From here, you can select "script database as". This will generate a T-SQL script that is equivilent to it's create statement. Then, you can save this and if the database gives you grief again, just run this script instead of downloading, re-installing, re-attaching, etc...

honest, fellas, I'm not dangerous, it's all right to talk to me.

LOL
You do have some interesting questions, I have to admit. It does seem like these tutorials are making you do things the hard way.

 

[BobLewiston]

Daeva:

Is it possible that database "AdventureWorks” IS there and SQL Server 2008 Express simply refuses to show it because it now "belongs to" another fictitious, non-logged-in user that was somehow conjured into being by DataGrid control's DataSource?

(I can't just look for the files in Windows Explorer. These databases are somehow hidden in other files that, although I know they reside in subfolders of C:\Program Files\Microsoft SQL Server\100\Tools\Samples, I've never been able to locate precisely.)

 

[Daeva]

It is possible, but not likely. Are you working on an application or a web-site? In any event, do a search for *.mdf* on your pc (try and locate it that way). It is possible that it was installed in another directory under SQL Server. If you can locate it using the search feature, try re-attaching it to your sql server. It is not possible for the database to be re-assigned exclusive permissions to a user like that, especially through your application (this would be a big security risk).

It's possible if you didn't close the connection to the database that it is unavailable or "locked". In which case, exit Visual Studio, and SQL Server management studio. In the run box type in "services.msc" without the quotes. Look for Sql Server in the list and re-start it. Then try your VS again and see if it can locate the database. Still a very weird thing.

 

[BobLewiston]

Daeva:

Are you working on an application or a web-site?

An application.

It's possible if you didn't close the connection to the database that it is unavailable or "locked". In which case, exit Visual Studio, and SQL Server management studio. In the run box type in "services.msc" without the quotes. Look for Sql Server in the list and re-start it.

It's already started.

…do a search for *.mdf* on your pc (try and locate it that way).

In C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA I see:

AdventureWorks2008_Data.mdf
AdventureWorksDW2008_Data.mdf
AdventureWorksDW_Data.mdf
AdventureWorksLT2008_Data.mdf
AdventureWorksLT_Data.mdf
ReportServer$SQLEXPRESS.mdf
ReportServer$SQLEXPRESSTempDB.mdf

I'm guessing that the first one, AdventureWorks2008_Data.mdf, is the one I want.

If you can locate it using the search feature, try re-attaching it to your sql server.

I think you're on to something here. How do I do that in SQL Server Management Studio? It doesn't show up there, so I can't select it.

Then try your VS again and see if it can locate the database.

Will do, after I re-attach the database to SQL Server.

Still a very weird thing.

Yeah, I had a guy tell me that VC#'s Data Source Configuration Wizard (which was invoked when I experimented with using a DataGrid control's DataSource property to add a new project data source to the DataGrid) had a tendency to “lose databases”. Do you ever use it?

 

[Daeva]

I have used it, but it never dropped things on me like this. To re-attach it to the database:
Open up SQL Management Studio.
Expand databases.
Right click databases.
Click attach database.
Here, locate the physical file you found
click ok.

This should complete the attachment process.

It's already started.

It's important to "re-start" it, because this will close out any open connections to the database server.

What I do, is I add a SQLDataSource control to the application, I set up the connection and tell it to select from a random table in the database and finish.
Once this is completed, it adds a ConnectionString property in your Application.Config

If, after you confirm this entry in your application.config, you delete the SqlDatasource control, you can then reference the connection string already established in your application.config like this:

String connStr = My.MySettings.Default.ConnectionStringNameHere;

Where ConnectionStringNameHere should be there automatically based on what you named the Connection when you created it using the SqlDatasource.

 

[BobLewiston]

Daeva:

I don't know why this happened, and it's certainly not your fault, but when I (unsuccessfully) tried to attach the AdventureWorks2008 database, it crashed the system. I uninstalled AdventureWorks (which succeeded but crashed the system) and reinstalled it (which also succeeded but crashed the system). The AdventureWorks2008 database was not attached (although the other six databases were), so I tried to reattach it (which failed and crashed the system). Now I can't uninstall or reinstall AdventureWorks, or attempt to attach the AdventureWorks2008 database, without crashing the system.

Since AdventureWorks is a collection of databases rather than an application, I have no way to try to “thoroughly” uninstall it with one of those special uninstallers that rigorously inspects the registry, so I know of no alternative to using the AdventureWorks setup program to do this.

A bit of a conundrum. I'm confident this will eventually get straightened out, but at the moment things aren't working. If you have any ideas or suggestions, they would certainly be much appreciated.

BTW, for what it's worth, I suspect that AdventureWorks2008_Data.mdf is NOT the "AdventureWorks" database I was using before (because I don't remember "2008" being in the database name). When I uninstall AdventureWorks, it is NOT removed from the directory where all the databases get installed to.

 

[Daeva]

You have a very curious situation here. Just as an aside, is there any reason other than you are following the tutorials, that you have to use adventureworks as opposed to creating your own and using that?

Also, be sure to let us know what the resolution is, this may be an issue to submit to Microsoft support *cringe*.

 

[BobLewiston]

Daeva:

You're right. I've had other problems with AdventureWorks that I haven't even mentioned. Time to get rid of it and just create my own database. Pertaining to that, please see my new thread.

 

[Olmossoft]

You have a very curious situation here.



Olmos Software LLС
2817248673
www.olmossoft.com