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.Services3System.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)
Could there be a policy in the database/sql server which doesn't accept the name of the table?
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.
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.
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!
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.....
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.
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?
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 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.
Which EPiServer and SQL Server versions are you using?
I'm trying to upgrade from 4.62 to 5.2.375.7, and my SQL server version is 9.0.4053
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.
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) ..
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
under the trouble shooting section, that the db's need to have the same collation.
Oh, my bad :)
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"
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 site failed to respond, reason: Unable to connect to the remote server.
"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 :-)
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).