November Happy Hour will be moved to Thursday December 5th.

Creating a site using existing database fails - help!

Vote:
 

I'm trying to create a new site with database, using an empty database i hvae created beforehand. The reason why i want to use this pre-existing database is, that i want to use another collation that the server default.

When i try to install the new site, the process terminated with the error shown below. My database is not case sensitive, and i get the same error if i try using a pre-existing database with the server default collation. 

Help anyone? :-)

Adding/Updating assembly redirect for Microsoft.Web.Services3
System.InvalidOperationException: The installation failed, and the rollback has been performed. ---> System.Data.SqlClient.SqlException: Invalid object name 'tblPageType'.
   at EPiServer.Install.SqlServer.Installers.SqlServerItemInstaller.HandleSqlException(SqlCommand cmd, SqlException ex)
   at EPiServer.Install.SqlServer.Installers.SqlQueryInstaller.ExecuteSqlNonQuery(String sqlQuery, IDictionary stateSaver)
   at EPiServer.Install.SqlServer.Installers.SqlQueryInstaller.InstallInternal(IDictionary stateSaver)
   at EPiServer.Install.SqlServer.Installers.SqlQueryInstaller.Install(IDictionary stateSaver)
   at System.Configuration.Install.Installer.Install(IDictionary stateSaver)
   at System.Configuration.Install.Installer.Install(IDictionary stateSaver)
   at System.Configuration.Install.TransactedInstaller.Install(IDictionary savedState)
   --- End of inner exception stack trace ---
   at System.Configuration.Install.TransactedInstaller.Install(IDictionary savedState)
   at EPiServer.Install.InstallationManager.DoInstall()
Error - System.Management.Automation.CmdletInvocationException: Invalid object name 'tblPageType'. ---> System.Data.SqlClient.SqlException: Invalid object name 'tblPageType'.
   at EPiServer.Install.InstallationManager.DoInstall()
   at EPiServer.Install.InstallationManager.CommitBulkInstall()
   at EPiServer.Install.Common.Cmdlets.CommitBulkInstallCmdlet.ProcessRecord()
   at System.Management.Automation.Cmdlet.DoProcessRecord()
   at System.Management.Automation.CommandProcessor.ProcessRecord()
   --- End of inner exception stack trace ---
   at System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(Object input, Hashtable errorResults, Boolean enumerate)
   at System.Management.Automation.PipelineNode.Execute(Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)
   at System.Management.Automation.StatementListNode.ExecuteStatement(ParseTreeNode statement, Array input, Pipe outputPipe, ArrayList& resultList, ExecutionContext context)

#50380
Apr 29, 2011 12:58
Vote:
 

Could there be a policy in the database/sql server which doesn't accept the name of the table?

#50396
Edited, Apr 29, 2011 14:01
Vote:
 

I'm not sure, but i don't think so. When i create a site with a new database (one that is created via the episerver deployment center) i have no problem at all.

#50398
Apr 29, 2011 14:30
Vote:
 

I now realize that the problem isn't necessarily that it can't create the tblPageType table, it is more likely that it is doing something that requires the tblPageType table but it doesn't exist. Why that is I do not know...

What SQL Server version are you using? Does the user you are using to populate the database from deployment center have sufficient access rights?

You could probably take the database script from the correct version folder under program files and run it yourself in the database to get more information on what is going wrong.

#50399
Apr 29, 2011 15:27
Vote:
 

Hi Magnus!

Well i tried to take the DB script (EPiServerRelease52.sql) and run it through my Management Studio; it went through without problems. But this script doesn't create all the tables, that the development center wizard does.

My SQL server version is 9.0.4053. The user used when trying to create the site was owner of my pre-existing database, so i take it there are sufficient acces rights.

I appreciante the help!

#50422
May 02, 2011 11:06
Vote:
 

Some of the tables are not episerver specific but are part of the asp.net provider model. So you'll be missing some aspnet_membership tables etc. You can create those too, using the aspnet_regsql utility found under %windir%\Microsoft.NET\v2.....

#50423
May 02, 2011 11:20
Vote:
 

Ok, i tried that and now I have most of my tables. I'm still missing 'CompletedScope' and 'InstanceState' as far as i can tell.

#50459
May 03, 2011 9:53
Vote:
 

Those are related to Windows Workflow Foundation. There are database scripts for those as well under %windir%\Microsoft.NET somewhere. But I suspect there are different versions, I'll try to find out which one is used.

Edit: v3.0\Windows Workflow Foundation\SQL\en etc. There are several scripts, and I don't really know which ones are needed or not.

Maybe you could create the database correctly on some other server (local sql server for example) which has the desired collation and then backup-restore it to the target server?

#50460
Edited, May 03, 2011 9:56
Vote:
 

Hi Magnus,

I imported these tables from a database that was created using the Deployment Center, and made sure that these tables have the correct collation. Then i tried creating a new site without a databse in the deployment center, and changed the connection string fro the new site to point to this new database. I tried to browse the new site (default.htm) and i worked fine.

I now tried the migration tool, using this new site'sd web.config as destination, and i get the following error:

Validation of configuration files failed.

Target errors:

Target site failed to respond, reason: the remoteserver returned and error: (500) Internal Server Error.

Did i mess up my config file?

I'm not sure i'm following the right strategy here, but i need a new site and a database with a different collation than the server default, and I cant create such a database through the deployment center.

#50463
May 03, 2011 11:36
Vote:
 

Which EPiServer and SQL Server versions are you using?

#50467
May 03, 2011 12:38
Vote:
 

I'm trying to upgrade from 4.62 to 5.2.375.7, and my SQL server version is 9.0.4053

#50469
May 03, 2011 13:07
Vote:
 

Oh, I had totally missed that you were going to migrate. In that case the collation of the 5.2 database don't matter at all since the database migration will be done in-place, meaning your 4.62 database will remain in the same collation but have its schema and data converted.

I suggest you install a 5.2 site using deployment center and ignore that it uses the wrong collation. You just need to get the 5.2 site running correctly. It won't be used for anything else than to provide a web context to run the migration in.

Edit: Correction: The target site WILL, in a way, be used afterwards too, as a placeholder for the converted site. The copied files will be in its VPP folder. It's config files will be rewritten though and new temporary page templates, custom properties etc will be created, and the connection string will be changed to point to the 4.62-turned-5.2 database.

#50470
Edited, May 03, 2011 13:13
Vote:
 

Mette, your approach with creating a new site and attaching your pre-existing database should work for getting a database with your desired collation.

The 500-error from Migration Tool - could it be that your CMS 5 site is not started in IIS? (in WinXP dev environments, IIS5 only allowed one site to run at a time) ..

#50472
May 03, 2011 13:20
Vote:
 

:-)

Well i actually did try that, and that is the reason i started this whole DB collation experiment.

When i did as you suggests, i got a collation error during the migration process. It also says here

http://world.episerver.com/Documentation/Items/Tech-Notes/EPiServer-CMS-5/EPiServer-CMS-5-R2/EPiServer-CMS-Migration-Tool-11/

under the trouble shooting section, that the db's need to have the same collation.

#50473
May 03, 2011 13:21
Vote:
 

Oh, my bad :)

#50474
May 03, 2011 13:22
Vote:
 

Then you could create an empty CMS 5 database with the "right" collation on another server, and attach it to the empty CMS 5 site you are using as the migration target? That way both dbs will have same collation before migration.

Alternatively try this tip from Mari Jørgensen on Twitter (https://twitter.com/#!/marijorg/status/45462455375233024):

"Ever needed to change collation on a database and its objects? Create a new db and use Red Gate SQL Data Compare to move the data"

 

#50476
May 03, 2011 13:30
Vote:
 

Hey Arild,

I checked my IIS and the site is runing, and i stopped all the other websites. I can even browse the default.htm without problems. I'm runing IIS6 could that be a problem?

I also checked my event viewer and noticed that alle the users that i created through the deployment center (I have created a few sites withe databases trying to make this work) they create a 'Failure Audit' entry in the Application event log saying that

'Login failed for user 'dbUserEpiServerR2'. [CLIENT: <local machine>]'

But when i tried using the migration tool on a site created WITH database in the deployment tool, i got to start the migration process, and i ran all the way until i got the collation error. When i use my site with the pre-existion database i can't even pass the first step in the wizard.

And on top of this the error message changed to:

Target errors:

Target site failed to respond, reason: Unable to connect to the remote server.

#50477
May 03, 2011 13:37
Vote:
 

Hi Arild

"Then you could create an empty CMS 5 database with the "right" collation on another server, and attach it to the empty CMS 5 site you are using as the migration target? That way both dbs will have same collation before migration."

I did try to connect an empty DB to a new site, and that's when the original error occured :-)

#50478
May 03, 2011 13:54
Vote:
 

The CMS5 database does not have to have the same collation as the 4.62 site as Magnus pointed out.

What the tech note says (could probably have been more clearly stated) is that all table columns in the 4.62 database must have the same collation as the 4.62 database it self (yes, we have seen cases where the collation has differed between table columns in the same database).

 

#50597
May 05, 2011 7:55
This thread is locked and should be used for reference only. Please use the Episerver CMS 7 and earlier versions forum to open new discussions.
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.