ASPNETDB.MDF when no SQLExpress, only SQL Server 2005?

[Guest]

I have only SQL Server 2005 installed on my PC. And I tried to add the
following rows in web.config to use SQL Server 2005 instead of Express:

<connectionStrings>
<clear />
<add name="LocalSqlServer" connectionString="Data
Source=.\SQL2005;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;user
instance=true;Integrated Security=True;Initial Catalog=ASPNETDB;"
providerName="System.Data.SqlClient" />
</connectionStrings>

However, I still get error:

The user instance login flag is not supported on this version of SQL Server.
The connection will be closed.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information about
the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The user instance
login flag is not supported on this version of SQL Server. The connection
will be closed.

And double click the ASPNETDB.MDF on solution explorer will get error
message: Connections to SQL Server files (*.mdf) require SQL Server Express
2005 to function properly. Please verify the installation of the component
.....

I need to deploy the application to my web host server and neither do they
have Sql Server express.
How to fix the problem.
TIA.

 

[Guest]

nick,
The exception message is exactly what it says - SQL Server 2005 does not
support the "user instance" directive in the connection string as does SQL
Express. The database has to be a a real instance and it has to be attached.
Here's a working example:

<connectionStrings>
<remove name="LocalSqlServer" />
<add name="LocalSqlServer"
connectionString="server=localhost;database=Articles;uid=sa;pwd="
providerName="System.Data.SqlClient" />
</connectionStrings>


Also, this article may be helpful with more info:

http://www.eggheadcafe.com/articles/20060529.asp

Peter

 

[Jon Paal]

SQL server is a real pain for webhost environments.

You will need to upload the mdf file and have your webhost attach it to the sql server.

Most won't do this.

In which case you will need to recreate the database into the database which is available to the hosted domain if you have signed up
for that service. Typically expensive, and a very clumsy process.


good luck.

 

[pblse2]

SQL server is a real pain for webhost environments.

You will need to upload the mdf file and have your webhost attach it to the sql server.

Most won't do this.

In which case you will need to recreate the database into the database which is available to the hosted domain if you have signed up
for that service. Typically expensive, and a very clumsy process.


good luck.

Well, since SQL Express and the full version can run side by side it
shouldn't really be an issue, the problem here is that most hosting
companies havent really understood what SQL Server 2005 Express Edition
is all about and wont install it.

I have my personal website hosted at godaddy and although they offer
20M SQL Server 2000 space they completely refused my request to install
SQL Server 2005 Express Edition.

They just don't get it and they have no interest in learning about it.

PL.

 

[Jon Paal]

I agree. It is a huge problem.

There really needs to be an acceptable, file-based database solution.

Something that can be managed remotely and deployed independently like any other file.

 

[Guest]

Jon,
You do not need to "upload the MDF file" and attach it. YOu can enable any
database with this code:

Management.SqlServices.Install("server", "USERNAME", "PASSWORD",
"databasename", SqlFeatures.All)


That's in the System.Web namespace, and it can be run from a web page.

I agree that it's too bad MS hasn't really promoted this fact, given that so
many people run ASP.NET 2.0 web sites on hosted environments where they have
no access to ASPNET_REGSQL.
Peter

--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com

 

[Jon Paal]

you lost me on this one.

Assuming someone has procured some SQL server space from their webhost, are you saying this code will transfer all local tables,
stored procedures, security etc. over to the webhost sql server without their participation ?

 

[Guest]

For enabling a Sql Server 2000 or 2005 database for Membership, Roles and
Profiles, that's what this method call will do. It creates all the tables,
views, and stored procedures to begin using the Membership, Roles and Profile
providers with the target database. If you then have "stuff" in a local
database that's already set up, you may be able to do insert -selects to
bring it over.

Unfortunately, MS decided not to document the feature very well.
Peter
--
Co-founder, Eggheadcafe.com developer portal:
http://www.eggheadcafe.com
UnBlog:
http://petesbloggerama.blogspot.com

 

[Jon Paal]

hmmm....

unfortuantely, that still doesn't address the deployment problem .