I have copied a DNN site + database from a windows Server 2003 machine to a windows XP Pro SP2 machine.

The database is not the original database.mdf but a custom db, which I'll allude to as "custom" for the purpose of this post.

"Custom" used to run under SQL Server 2000 on the original server. I have backed it up and restored the bak file to a "custom" db created on my SQL Server Express 2005. I have modified the connectionstrings in web.config accrodingly, commenting out the SQL server syntax and using the SQL Express syntax instead.

I cannot access the database. After looking into rights and ensuring I have set them correctly, I have realised that, on the original server, the database files were not in the default SQL Server directory. Google searches have revealed that the |DataDirectory| variable used in connectionString will keep the value set on the original machine. I have attempted to replace |DataDirectory| with the full explicit db path, but the connectionString value is then too long (over 128chars).

I therefore need to change the value of |DataDirectory| and ensure that it corresponds to my SQL Server Default data directory.

I know I need to call the method AppDomain.SetData("Data directory", <newpath>), but which file should the method be included in?Do I need to override a DNN class?If so, which one?

Many thanks in advance for any help you can lend.

many thanks for your quick reply Chris.

I tried what you said, but am still unsuccesful.

This is the error I get

Could not connect to database specified in connectionString for SqlDataProvider

Many thanks again Chris.

I used this connection string format in web.config...in case it helps other SQL Express users. This elimintes the need for |DataDirectory|

<add name="SiteSqlServer"
           connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=YOURDATABASENAME;Integrated Security=True;uid=YOURUSER;pwd=YOURUSERPASSWORD;"
           providerName="System.Data.SqlClient" />

However, one last thing needs doing....add the ASP.net process account to your machine's administrator group or access to the db will never work. in windows XP the ASP.net process account will be ASPNET, in Server 2003 it will be NETWORK SERVICE.