Database can't use SQL Server 2005.

Will · March 4th, 2009, 03:55 PM

I have SQL Server 2005 but for some reason it wont let me open the database file provided in the resources folder for chapter 11.

Pretty much having trouble with the first try it out it keeps telling me that to use sql server 2005 express it has to be functioning properly but I want to use the developer edition I have.

I know there isn't a problem with the developer editon as I have got a few databases and stored procedures working in it previously.

Imar · March 4th, 2009, 05:05 PM

Hi Will,

ASP.NET uses a SQL Server connection string called LocalSqlServer which, by default, points to (and thus insists on using) a SQL Server called .SqlExpress.

It's easy to use a different database server but you'll need to either override this connection string and point it to your server, or reconfigure services like Membership and Roles to use your database. Check out my reply here: Yellow screen of death when creating new user for some background and pointers to pages in the book.

Cheers,

Imar

Will · March 4th, 2009, 05:17 PM

Haven't looked through that link yet but I have gone in the server explorer and changed the datasource from sqlexpress to \NETSDK which is what i have used for other databases and using windows authentication as that is what it is setup with.

Should I not even just be able to open the mdf file in sql server 2005 anyway?

Imar · March 4th, 2009, 05:24 PM

It depends on your connection stirng.

You should be able to open a database which has a connection string pointing to your own server.
However, separate files in the App_Data folder are typically opened in "user instance mode" which by default uses SQL Express.

Once you set up a connection to your own SQL server, it should be accessible from the Database Explorer.

Cheers,

Imar

Will · March 4th, 2009, 05:43 PM

Ok I read tha post and looked in the machine config file and it does say sql express so I changed it to Will as when I open sql server this is the login I have to use to connect successfully and view all my databases in that I have other logins such as Will\will parker and Will\ASPNET. So I am assuming here that I only need to put WILL as this seems to be the main one.

so i modified it to this

Code:

<add name="LocalSqlServer" connectionString="data source=.\WILL;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>

In the server explorer when I double click the database connection I get the following error:

Quote:

Connections to SQL server files are require SQL Server express to function properly.

When I try to open the .mdf file from the resources folder directly using SQL Server 2005 I get this error:

There is no editor available for "filepathnamehere" make sure the application for the file type (.mdf) is installed.

Imar · March 4th, 2009, 05:51 PM

I think you're making too many changes at once which is always dangerous.

First of all, *don't* change Machine.config. As I explain in the book, simply <clear /> the connection strings and re-add the LocalSqlServer in your web site's *web.config* instead.

Also, is your server called Will? Then the instance name of SQL is not .\Will, but simply Will.

If I were (that's how I have it here as well) I'd do the following:

1. Install SQL Express die by side by the dev edition. Great for quick and dirty creation of new database driven web apps.

2. In sites running on a different database (either the Dev editon on (loca) or another one), override LocalSqlServer for the ASP.NET services and for your own connection stirngs.

Cheers,

Imar

Will · March 4th, 2009, 06:12 PM

Ok I put the machine config file back to say SQLEXPRESS.

If I wanted to connect to sql server 2005 dev edition then simply in the web config file i would have the following:

Code:

<connectionStrings>
<clear />
<add name="PlanetWroxConnectionString1" connectionString="Data Source=DatabaseServer;Initial Catalog="PlanetWrox;User Id=myusername; password=mypassword;" providerName="System.Data.SqlClient" />

where "PlanetWroxConnectionString1" is the name of my connection and could be called "PWCon" if I that is the name I had given it in the codebehind file?

"DatabaseServer" is the name of my database server in my case Will

"planetWrox" is the name of the database (.mdf) file

UserId and password are my username and password but I don't know what user name and password it is talking about?

Imar · March 4th, 2009, 06:17 PM

Did you read appendix B? It's all in there.

Your example uses SQL authentication where SQL manages the user name and password. Instead of that you could also use Integrated Secuirty.

Do yourself a favor, and read the full appendix B before you continue and seriously mess up your system. It provides you with a lot of detail and insights in how SQL Server authentication works.

Cheers,

Imar

Will · March 4th, 2009, 06:43 PM

Im an idiot. I hadn't attached the database...

Thanks again.

Imar · March 4th, 2009, 07:05 PM

Hahaha ;-) Glad it's working now..

Imar