Migration to SQL from CE

Topics: Installing Orchard, Troubleshooting
Jul 2, 2011 at 12:13 AM

I followed the video turtorial on how to migrate the original (out of the box ) CE db to the SQLExpress as, I would ultimately like to migrate completely to SQL however, keeping it simple for now...

The migration has completed successfully (after I deleted the Silverlight module, another topic) however, when I browse the blog I get the following error message (in brief)

Cannot open database "Orchard" requested by the login. The login failed.
Login failed for user 'IIS APPPOOL\

In a word, HELP....

Thanks

Coordinator
Jul 2, 2011 at 12:34 AM

The connection string in your app_data/sites/default/settings.txt file is probably not right, especially the db authentication.

Jul 2, 2011 at 1:00 AM

i just noticed that if I browse the site using webmatrix with localhost:7665 it works just fine.... when I "publish" via ftp (which is still on my server I was hoping the "localhost" is would be changed to the site url) the site crashes

Here is the connection string created when I ran the WebMatrix db migration

DataConnectionString: Data Source=.\SQLExpress;Initial Catalog=Orchard;Integrated Security=True
Here is what my Server is set for....

data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true

I Tried to change it to:
data source=.\SQLEXPRESS;;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|orchard.mdf;User ID=Administrator;Password=(password removed for posting0
and then
data source=.\SQLEXPRESS;;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|orchard.mdf;User Instance=true

All using the same :LocalSqlServer

Everything is failing....

I cannot see why this is failing when I use the domain name to view the site versus localhost.... I haven't a clue as to how to change the rights on SQLExpress let alone SQL as, I have been using MySQL using ports for the last several years...

I Hope this sheds somm light on the situation...
Jul 2, 2011 at 1:03 AM

This is somewhat still on topic however it regards the migration process which was failing... I simply dropped the silverlight table:

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

An error occurred during execution of the database script. The error occurred between the following lines of the script: "1898" and "1903". The verbose log might have more information about the error. The command started with the following:
"ALTER TABLE [dbo].[Szmyd_Orchard_Modules_Silverlig"
 The identifier that starts with 'Szmyd_Orchard_Modules_Silverlight_SilverlightPartRecord_PK__Szmyd_Orchard_Modules_Silverlight_SilverlightPartRecord__00000000000' is too long. Maximum length is 128. http://go.microsoft.com/fwlink/?LinkId=178587
---------------------------
OK
---------------------------

---------------------------
Microsoft.Web.Deployment.DeploymentDetailedClientServerException: An error occurred during execution of the database script. The error occurred between the following lines of the script: "1898" and "1903". The verbose log might have more information about the error. The command started with the following:
"ALTER TABLE [dbo].[Szmyd_Orchard_Modules_Silverlig"
 The identifier that starts with 'Szmyd_Orchard_Modules_Silverlight_SilverlightPartRecord_PK__Szmyd_Orchard_Modules_Silverlight_SilverlightPartRecord__00000000000' is too long. Maximum length is 128. http://go.microsoft.com/fwlink/?LinkId=178587 ---> System.Data.SqlClient.SqlException: The identifier that starts with 'Szmyd_Orchard_Modules_Silverlight_SilverlightPartRecord_PK__Szmyd_Orchard_Modules_Silverlight_SilverlightPartRecord__00000000000' is too long. Maximum length is 128.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.Web.Deployment.DBStatementInfo.Execute(DbConnection connection, DbTransaction transaction, DeploymentBaseContext baseContext, Int32 timeout)
   --- End of inner exception stack trace ---
   at Microsoft.Web.Deployment.DBStatementInfo.Execute(DbConnection connection, DbTransaction transaction, DeploymentBaseContext baseContext, Int32 timeout)
   at Microsoft.Web.Deployment.DBConnectionWrapper.ExecuteSql(DBStatementInfo sqlStatement, DeploymentBaseContext baseContext, Int32 timeout)
   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)

Coordinator
Jul 2, 2011 at 1:04 AM

Well, is there SqlExpress on the server?

Coordinator
Jul 2, 2011 at 1:06 AM

On the length error you got, I've seen that on some custom module tables. My workaround was to rename the private key in SqlCe before the upgrade.

Jul 2, 2011 at 1:06 AM

I just noticed two other issues one with the web.config and the other was:

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot open database "Orchard" requested by the login. The login failed.
Login failed for user 'IIS APPPOOL\ASP.NET v4.0'.

Coordinator
Jul 2, 2011 at 1:07 AM

How is that different?

Jul 2, 2011 at 1:10 AM

Regardng the SQLExpress, yes alonh wih SQL Server 2008 R2 MAnagement Studio Express as, WEB PI installed them for me when I did the Orchard install...

Jul 2, 2011 at 1:12 AM

How is that different?

When I removed the two duplicate lines of code in the Web.Config file I then noticed the additional information regarding the Exception Details: System.Data.SqlClient.SqlException: Cannot open database "Orchard" requested by the login. The login failed.
Login failed for user 'IIS APPPOOL\ASP.NET v4.0'.
Statement....

 

Coordinator
Jul 2, 2011 at 1:17 AM

Where is the mdb file?

(I still don't understand how that error is different from what you had before)

Jul 2, 2011 at 1:19 AM

I guess it is no different.... the bottom-line is that I can view the site using the SQLExpress via localhost but cannot via a domainname or IP Address.... I am I supposed to make an Adjustment to SQLExpress to allow domain names and IP's and if so, how?

I am simply trying to find an alternative hoping that will help with the memory usage issues....

Coordinator
Jul 2, 2011 at 1:23 AM

Oh, I did not understand that's what you were trying to do. I don't think Sql Express supports remote connections.

Jul 3, 2011 at 6:24 AM

Okay after HOURS of trying to get this set up to use the SQLExpress I have finally WON the battle...

For starters, the connection string located within the IIS ASP Controls must be removed.... along with this, named pipes has to be enabled on the SQLExpress server in addition to TCP/IP.... last but not least, when I ran the SQLExpress set-up via Web PI, I choose the default configuration and did not choose Integrated servces so, I need to login to the database via Super Admin....

Trial and error... I will let you know if this has improved the memory issues we where discussing or not... if I can keep a lid on that, it looks like this might be a go... I have been trying to get away from "WordPress" for months not saying they are really bad or anything it's just that I am running an IIS 7.5 server and anytime I go to the forums for help the only feedback I get is, "Well we are open source. open source is the greatest, microsoft sucks.... blah blah blah" gets really old after awhile and takes me hours to solve my own issues...