Monday, March 26, 2012

Migrated from SQL 2k5 Express to SQL Server - Profile doesn't work

I recently created an application that stores 3 variables in the web.config in the "Profile"configuration:

<add name="Department" defaultValue="" />
<add name="UserName" defaultValue="Anonymous" allowAnonymous="true"/>
<add name="AL" defaultValue="" />

By doing this my web.config automatically configured itself to create a database in my app_data folder and also filled in all the table rows as necessary. Then I moved the code to my sql server running SQL Server 2005 Standard and found out that it won't generate that database in either the app_data folder or the SQL Server Data directory. I've gotten so desperate to get this database created that I've (temporarily) given "Everyone" full permissions to both directories. I then double checked that my named pipes to that server were enabled, and then also tried moving my database from my working sql express app from a different computer to the server. I still cannot get it to work.

If I have no database in the folders that worked on sql express i get this error:

"SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express ..."

If I have a database in the folder it says it can't communicate with it or can't find it.

My web.config shows:

<add name="LocalServer" connectionString="server=SERVERNAME\West;database=ASPNETDB.MDF;uid=UserName;pwd=Password;" PoviderName="System.Data.SqlClient" />

Is there a way to successfully generate this database with sql server? Please Help!!!

I figured the problem out...

For those of you interested, here is the solution. I found on google groups early on when i had this problem, but it didn't work and I never payed attention to the error I got, I just assumed it couldn't work. Then I figured i'd give it another shot. The trick is to make sure you don't include the line breaks in your web.config when replaceing the suggested code. Anyway, below is the link (solution provided by Brad Hammond):

My machine.config solution:
<add name="LocalSqlServer" connectionString="data source=.\West;Initial Catalog=ASPNETDB;User ID=UserName;password=Password" />

Thank you Brad, Hope this helps other people out!

No comments:

Post a Comment