SQL Server Database File vs SQL Server Database -VWD Express

VWD Express 2008, SQL Server Express 2005.

I have been using SQL Server Express with ASP and VB for some years - I just create a DB in Management Studio Express, have SQL Server set to 'SQL Server and Windows authentication', and use a connection string with 'sa' and a password (I never have managed to get it working with 'integrated security', but that is probably irrelevant to this post). So I have a list of DB's attached to SQL Server that I can see, attach, detach etc in MSE.

I am getting started with .NET. Working through a VWD Express book tutorial involves adding login/registration etc using the wizard(s). This works fine, the wizard creates an ASPNETDB.mdf database, this shows in Data Explorer, and I can add users etc which create entries in that DB. If I right click that DB, the menu shows a 'detach' option, which implies it is 'attached', though DB does not show in MSE. But it works, so presumably that is OK.

The next stage in the tutorial involves creating a simple DB and using a gridview. I right-click the website in Solution Explorer and use 'add item' to add a SQL DB, accepting the message box that suggests I put it in the App_Data folder. This then shows in Solution Explorer, and I can add tables and data etc. I then add a gridview to the page and try to configure a datasource. I choose 'new connection'. On the next dialog 'Add Connection', if I leave the Data Source as the default 'Microsoft SQL Server (SqlClient)', then I get stuck, if I change it to 'Microsoft SQL Server Database File (SqlClient)' then I can carry on. But the books all say that I should be using the first default option. Specifically the symptoms are:

1. With 'Microsoft SQL Server (SqlClient)' it makes no difference whether I have the server name as 'MACHINENAME', 'MACHINENAME\SQLEXPRESS' (which is what my server is called) or '(local)' (which is what the book tells me to use), or what authentication I use (I leave it on Windows authentication, as that is what the book says):

1a. If lower down the dialog I leave the default 'Select or enter a database name', the only DBs that the dialog recognises are the ones that show in MSE i.e. all my old DB's, or at least, the ones I currently have attached. Typing in the new DB name just results in an error message saying it cannot find it

1b. If instead I select 'Attach a database file' and browse to the .mdf file, then it accepts it, but 'Test Connection' gives a long error message, the first part of which is "Unable to open the physical file .C:\....\testDB.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process)".

2. If in the top Datasource field I choose  'Microsoft SQL Server Database File (SqlClient)', then the dialog changes to a much smaller one, I can browse to the .mdf file, and Test Connection works, and so does the gridview.

So although I have found a way to get things working, it is not what the tutorials tell me should be happening. And I am thoroughly confused. What is the difference between  'Microsoft SQL Server (SqlClient)' and 'Microsoft SQL Server Database File (SqlClient)'? Am I doing something wrong?

0
anynick
9/5/2008 10:25:47 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies
1005 Views

Similar Articles

[PageSpeed] 40

Hi,

When we create a new database in the folder “App_Data” with IDE (Visual Studio or VWD), and access it in Server Explorer, it’s attached with user-instance.

A SQL Server user instance is similar to a normal SQL Server instance but it is created on demand while normal instances are created during setup. The service account for a user instance is the Windows user who opened the SQL Client connection to the database.  When a connection with this connection string opens successfully, the user application is connected to a user instance of SQL Server Express running as the user who opened the connection.

That’s why you cannot see the newly created database in SQL Server Management Studio Express, because the database is not attached in it.

SQL Server manages the locking of the database data in its memory. Thus, if more than one SQL Server instance has the same file open, there is the potential for data corruption. If two different user instances use the same database file, one instance must close the file before the other instance can open it.

If the database is using by IDE, you cannot attached it in SQL Server Management Studio Express.

If you want to connect the database with Microsoft SQL Server (SqlClient), you need detach it in IDE and attach it in SQL Server Management Studio Express.
Otherwise, you have to use Microsoft SQL Server Database File (SqlClient).

Please refer to the document below, I think it’s quite helpful.

SQL Server 2005 Express Edition User Instances
http://msdn.microsoft.com/en-us/library/bb264564.aspx


Jian Kang
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Jian
9/8/2008 12:55:44 PM

Thank you very much. Very helpful. That article is also very good. I understand now.

It seems my book tutorial is 'wrong' - in that it shows the dialogs for creating a connection to a DB that is attached to the parent instance (using Microsoft SQL Server (SqlClient), but the steps it leads you through only create DB files via the IDE.

0
anynick
9/9/2008 11:14:22 AM
Reply:

Similar Artilces:

Migrating sql server 2005 express database to sql server 2005 database
Hi, I have an application developed using VWD and sqlserver express database. The express database is turning out to be small in size and we need to migrate to larger sqlserver 2005 database. What are the steps for this migration, please list in detail. Regards, Sandyhttp://www.thequinn.infohttp://www.sksdataservices.comhttp://www.infobasket.info Hi Sandy, To move a database from SQL Express to SQL 2005, you can use the following steps. 1. If your database file is attached to the server instance, you will need to detach it first using SQL management studio. If it is under App_Data folde...

SQL Attached mdf database files VS database imported into or created in SQL Server 2005
 Hi all (newbie @ asp.net)(oldie @ ASP 3)What is the purpose of using an attached MDF database files in the App_Data folder on a web site as to importing it into the SQL server directly or creating it on the SQL server. Does a mdf database attached file purely use the SQL server as a connection interface.Is it something similiar to DSN(ODBC) Connections for ms access databases. AFAIK it is only for deployment conveniance, I have always imported the database directly into SQL Server and set up the connection string accordingly.Don't forget to click "Mark as Answer" on the...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!),   I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine.  But I've had nothing but frustration trying to get it up and running on a webhosting site.  I picked hostmysite.com which uses SQL Server 2005.  I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database.  I tried using SQL Server Management Studio Express.  I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf files ...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi (and HELP!),   I downloaded the Personal WebSite Starter Kit and got it working no problem on my local machine.  But I've had nothing but frustration trying to get it up and running on a webhosting site.  I picked hostmysite.com which uses SQL Server 2005.  I was under the impression that it wouldn't be that big a deal to convert the .MDF files that Sql Server Express uses to a SQL Server 2005 database.  I tried using SQL Server Management Studio Express.  I was able to "see" my remote database on hostmysite.com, but I was not able to import the .mdf file...

Converting MDF files (SQL Server Express) to SQL Server 2005 WITHOUT having to buy SQL Server 2005 (I have Express). Personal WebSite Starter Kit
Hi,Basically i built a webpage with Login and it's working well from my local machine.Right now, i'm using Database Publishing Wizard. the problem is how do i convert.MDF files to .SQL 2005 to get my database publish in the internet? thanks  Place your .MDF file in the App_Data directory, change the connection string accordingly and then publish your web site. ----------------------------------------------------------Please click Mark As Answer if this helped in solving your problem.  Please click "Mark As Answer" if this hepled in solving your problem. ...

moving an sql server 2005 database to a sql server 2000 database
I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?.....   this article explains it. http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149585_tax301536,00.html hth,mcm pizzamaker74: I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?..... If both are in the same network or box just register the 2005 with the 2000 cr...

How to convert a SQL Server 2005 Database to SQL Server Express
Hi, I have a SQL Server 2005 Database that I would like to export to SQL Server Express. How do I go about this? I've tried backing it up, creating a new (blank) SQL Server Express Database and trying to restore it, but get the following message: TITLE: Microsoft SQL Server Management Studio------------------------------ Restore failed for Server 'PRODSOL-LAPTOP1\SQLEXPRESS'.  (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOp...

Moving SQL Server 2005 Database to SQL Server 2005 Express
Hi, I have SQL Server 2005 in my Development machine but the hosting server has only SQL server 2005 Express. I was wondering if I develop the DB in SQL server 2005 would be able to move/deploy it to SQL Server Express in the production server? If so how? Can I just generate the script and then run it on the productions server? Or there is a better/easier way? Thanks in advanceShuaib----------------------- I don't anticipate any problems creating database in SQL Server 2005 and moving it to Express. If you need to generate the script to create an empty shell (no d...

How migrate SQL server 2000 database to SQL Server 2005 Express
I  have a  SQL server 2000 database.I have to migrate it to SQL Server 2005 Express.But I don't know how to do. Is any one can help me ? Thanks You can detach the database using sp_detach_db and then re-attach it to your SSE instance using sp_attach_db.  This should work seamlessly, but back up first, just in case.Adam MachanicSQL Server MVP...

SQL Server Express file to Sql Server
I would like to copy the schema and data of a sql server express database file to a SQL Server Database. How can this be done and what steps would I need to take?   Thanks for any helpTinyPond TinyPond:I would like to copy the schema and data of a sql server express database file to a SQL Server Database. How can this be done and what steps would I need to take? If you have Express Management Studio, you back backup your database, and them import it to SQL Server (or attach and reattach).Justs open SQL Server Express Management Studio, connect to your Express instanc...

Microsoft SQL Server Database File (SqlClient) and Microsoft SQL Server (SqlClient)?
hello there is Microsoft SQL Server Database File (SqlClient)(i have created *.mdf Solution of Visual web developer.. righ click, add new item, SQL server database) and Microsoft SQL Server (SqlClient) at server explorer(i have created *.mdf at SQL server management studio) as Modify Connection.. when i create *.mdf at Microsoft SQL Server Database File (SqlClient), i cant see i*.mdf at SQL Server Management Studio. but when i create *.mdf at Microsoft SQL Server (SQLClient), i can see *.mdf SQL Server Management Studio. what is different between Microsoft SQL Server Database File (SqlCli...

How to create a copy of SQL Server 2000 database in SQL Server 2005 Express?
I have a database called 'DB1' in SQL Server 2000. I want to create the same database in SQL Server 2005 Express including the original data in tables. How would I do that? I cannot find any option to do this upgrade in SQL Server Management Studio.sun21170 Check out this:How to: Upgrade to SQL Server 2005 with the Copy Database Wizard Darrell Norton, MVPDarrell Norton's BlogPlease mark this post as answered if it helped you! The article you mentioned does not apply to the 2005 Express Edition. The Management Studio for the Express Edition contains no Copy database opti...

sql server 2005 do'nt attach sql server database file
I have sql server 2005 standard version. I want connect mu database file with microsoft sql server  database file.I receive ... provider: SQL Network Interface; error: 26 - error locating server/instance specified ... error.How to use local .mdf file with sql server 2005 (not express)  SQL Server (not express) doesn't support attaching on the fly of mdf files, so you need to do it manually. That is, open SQL Management Studio, right click the server, select attach, and that way you can permanently attach the database to the server. You'll have to use a normal connection s...

Restoring a SQL Server Express 2005 Database to SQL Server Standard 2005
Hello,If I backup and restore an express database to sql server 2005 standard, will there still be limitations in regards to the database size, cpu...etc.? Thanks,Jon  I suppose that no. Limitations are connected to SQL engine not to database itself. But probably if your database will grow up connected to SQL Standard and you will try to move it back to SQL express you will be in trouble.  ThanksJPazgier...

Can Sql Server Express 2005 Run a Sql Server 2000 database?
Should be a quick question:I've got a client with a Sql Server 2000 database.  He wants to hook it up to a server that is only running Sql Server Express 2005.  Can this work? Thanks!-Craig U-Sports.net - Taking Fantasy Football to School Tell him to Backup the database and send you the .bak you can restore it as 2005 database.  I have posted a FAQ about moving SQL Server databases and there is a free chapter about Backup and Restore in the link below.  Hope this helps.  http://forums.asp.net/thread/1454694.aspxKind regards,Gift Peddie Thanks!  I will rec...

Web resources about - SQL Server Database File vs SQL Server Database -VWD Express - asp.net.sql-datasource

Database - Wikipedia, the free encyclopedia
A database is an organized collection of data . The data are typically organized to model aspects of reality in a way that supports processes ...

Database - Wikipedia, the free encyclopedia
... requiring information. For example, modelling the availability of rooms in hotels in a way that supports finding a hotel with vacancies. Database ...

Ben Carson calls for a database on 'every foreigner'
Boston Herald Ben Carson calls for a database on 'every foreigner' Boston Herald Republican presidential candidate, Dr, Ben Carson, center, ...

Donald Trump wants "surveillance of certain mosques," database of refugees - Videos - CBS News
... rally in Birmingham, Alabama, Republican presidential candidate Donald Trump called for increased scrutiny over mosques, along with a database ...

Trump: ‘I Didn’t Suggest A Database’ For Registering Muslims
Trump: ‘I Didn’t Suggest A Database’ For Registering Muslims

Trump refuses to say how his Muslim database plan differs from Nazi Germany
... blurt out outrageous ideas like forcing American Muslims to register and carry special ID and then back down. No, he sticks with them. A database ...

US Republican rivals blast Donald Trump for Muslim database comments
US Republican rivals blast Donald Trump for Muslim database comments - Donald Trump said he would implement a database to keep track of Muslims ...

Trump calls for surveillance of some mosques, attempts to clarify remarks on Syrian database
Trump calls for surveillance of some mosques, attempts to clarify remarks on Syrian database

Donald Trump is Under Media Assault for Suggesting ‘Muslim Database,’ But Here’s What He Says About It ...
"Donald Trump's plan for a Muslim database draws comparison to Nazi Germany"...

Trump is getting pummeled by his political opponents over Muslim database
WASHINGTON — Republican presidential rivals rushed Friday to condemn Donald Trump's support for a government database to track Muslims in the ...

Resources last updated: 11/22/2015 12:01:30 PM