Error when trying to migratie to SQL

Topics: Troubleshooting
Jul 5, 2011 at 1:13 AM

When I try to convert from SQL CE to SQL file I get this error.

---------------------------
Migration failed
---------------------------
Migration failed

An error occurred during execution of the database script. The error occurred between the following lines of the script: "349" and "367". The verbose log might have more information about the error. The command started with the following:
"INSERT INTO [dbo].[Common_BodyPartRecord]([Id],[Co"

---------------------------
OK
---------------------------

---------------------------
Microsoft.Web.Deployment.DeploymentException: An error occurred during execution of the database script. The error occurred between the following lines of the script: "349" and "367". The verbose log might have more information about the error. The command started with the following:
"INSERT INTO [dbo].[Common_BodyPartRecord]([Id],[Co"
 ---> ManagedBatchParser.ParserException
   at ManagedBatchParser.Parser.Parse()
   at Microsoft.Web.Deployment.SqlBatchParser.ProcessSqlCmdScript(String sqlCmdScript)
   at Microsoft.Web.Deployment.SqlScriptToDBProvider.AddHelper(DeploymentObject source, Boolean whatIf)
   --- End of inner exception stack trace ---
   at Microsoft.Web.Deployment.SqlScriptToDBProvider.AddHelper(DeploymentObject source, Boolean whatIf)
   at Microsoft.Web.Deployment.SqlScriptToDBProvider.Add(DeploymentObject source, Boolean whatIf)
   at Microsoft.Web.Deployment.DeploymentObject.AddChild(DeploymentObject source, Int32 position, DeploymentSyncContext syncContext)
   at Microsoft.Web.Deployment.DeploymentSyncContext.HandleAddChild(DeploymentObject destParent, DeploymentObject sourceObject, Int32 position)
   at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(DeploymentObject dest, DeploymentObject source)
   at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildren(DeploymentObject dest, DeploymentObject source)
   at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildrenOrder(DeploymentObject dest, DeploymentObject source)
   at Microsoft.Web.Deployment.DeploymentSyncContext.SyncChildren(DeploymentObject dest, DeploymentObject source)
   at Microsoft.Web.Deployment.DeploymentSyncContext.ProcessSync(DeploymentObject destinationObject, DeploymentObject sourceObject)
   at Microsoft.Web.Deployment.DeploymentObject.SyncToInternal(DeploymentObject destObject, DeploymentSyncOptions syncOptions, PayloadTable payloadTable, ContentRootTable contentRootTable)
   at Microsoft.Web.Deployment.DeploymentObject.SyncTo(DeploymentProviderOptions providerOptions, DeploymentBaseOptions baseOptions, DeploymentSyncOptions syncOptions)
   at Microsoft.Web.Deployment.DeploymentObject.SyncTo(String provider, String path, DeploymentBaseOptions baseOptions, DeploymentSyncOptions syncOptions)
   at Microsoft.Web.Deployment.DeploymentObject.SyncTo(DeploymentWellKnownProvider provider, String path, DeploymentBaseOptions baseOptions, DeploymentSyncOptions syncOptions)
   at Microsoft.WebMatrix.DatabaseManager.Client.SqlCeHelper.<>c__DisplayClass6.<StartMigrate>b__5(Object sender, DoWorkEventArgs eventArgs)
   at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
---------------------------

Any thoughts here how this might be resolved?

Thx in advance

Wim

Coordinator
Jul 5, 2011 at 10:11 PM

Any success at finding that verbose log? Did you try asking on the WebMatrix forums? (you tried upgrading through WebMatrix, right?)

Jul 5, 2011 at 10:31 PM

Not yet but got new pc today so big job installing everything :) I did indeed try to convert througt webmatrix and will try to find that verbose log now.  But I tried a nice tool to make scripts from SQL CE to SQL server on http://exportsqlce.codeplex.com  No problem then to get the database over to SQL Server. But could not make Orchard to accept this new database by changing the Settings.txt in Orchard. Maybe you know a trick to do that.

Coordinator
Jul 5, 2011 at 10:34 PM

Pointing settings.txt to the new connection string should be all you need. Do you have an error message or something?

Jul 5, 2011 at 10:40 PM

Doing some searches on the internet. Did not find the solution yet but I dit find that I am not the only one having this problem.

Jul 5, 2011 at 10:43 PM

No I did not get an error when I did this but I did get an almost empty Orchard back. But I will do it again to be sure was quite late in the night yesterday.

Coordinator
Jul 5, 2011 at 11:22 PM

Did it make you go through setup again? (it shouldn't have).

Jul 5, 2011 at 11:23 PM

Same problem but I know now that there is some content in this [dbo].[Common_BodyPartRecord]([Id],[Co" that makes the script and the export go crazy. I see pieces of Javascript and other kinds of content that must be te cause of this problem but it will be difficult to find out what I can and shall take out without damaging the site. Will investigate some more.

Jul 5, 2011 at 11:31 PM

No thats correct no setup. But it did not export all of the records of the file. Seems to be a bug in SQL CE on some characters in textfields I think.

Jul 5, 2011 at 11:32 PM

Even webmatrix goes crazy when I try to do something in that table.

Coordinator
Jul 5, 2011 at 11:37 PM

Yes, you might want to take that to WebMatrix or SQL forums or support.

Jul 5, 2011 at 11:44 PM

You are there at Microsoft so go over to these guys and kick some ass! :)

Coordinator
Jul 5, 2011 at 11:53 PM

I can't present the symptoms, errors an specific information from the problems you're seeing on your behalf. I would need first to be able to reproduce your problem (I can't, or would need to spend more time understanding and getting more information from you), then I would have to take all that to them and play intermediary between you and them. I can't see the point in doing that. I would definitely spend the time necessary if that was an Orchard problem, but since it isn't, and it is on a product on which I have zero expertise, that would be counter-productive.

Jul 6, 2011 at 12:10 AM

Yes thats true I will continue investigating to find out what line is causing the problems. But I saw browsing a bit that there is allready some discussion on the internet with the same problem. And its a pitty that is always a combination of Orchard and SQL CE. Even if it is not the fault of Orchard its bad publicity and thats a pitty for all the people working hard on the project. . I saw problems linked to $ signs in text. Localisation problems with dots and comma's that create extra fields in export tables etc. But when I have my site back on SQL Server I will surely never again make a site using SQL CE till this is solved. But you can count on me that I will deep dive in the problem.

Coordinator
Jul 6, 2011 at 12:17 AM

Maybe more people hit that with Orchard because it's one of the most widely used applications that use SqlCe as the default. I'd like to understand what's going on here as well, but I simply don't have the dataset to even reproduce it. Feel free to send me your app_data at bleroy at microsoft.

Jul 6, 2011 at 12:43 AM

Data file send.

Jul 6, 2011 at 1:41 AM

Just finished testing on a clean machine. Conversion from WebMatrix still goes wrong with the same error but now the Conversion from the tool went ok. My database is now converted. Correction for the address of the tool it is on http://exportsqlce.codeplex.com/ Or you can find it here http://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1/