change sql compact

hi there, i initially installed nopcommerce using the sql compact option and integrated authentication, however i am trying to use nopadmin which will not let me access the database and apparently this is because it does not support those authorizations or the compact version.

Q does anyone know how i can change the database so that i can manage using nopadmin as it seems like its a lot quicker than the current way of doing things, or alternatively if anyone has any other ideas how to achieve this using a different method or software it would be great to here about it.

thnaks in advance

use sqlserver express edition.

hi thanks for your response, i understand that i should sql express, the problem is how do i do that now that i have already used sql compact.

how do i change the database on a site that already has a database ?

is this possible ?

thanks in advance.

right. i have found a way to migrate the database using webmatrix however everytime i do so it throws me an error as follows


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

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

---------------------------
Microsoft.Web.Deployment.DeploymentDetailedClientServerException: An error occurred during execution of the database script. The error occurred between the following lines of the script: "29133" and "29137". The verbose log might have more information about the error. The command started with the following:
"ALTER TABLE [dbo].[Product_SpecificationAttribute_"
The identifier that starts with 'Product_SpecificationAttribute_Mapping_FK_Product_SpecificationAttribute_Mapping_SpecificationAttributeOption_SpecificationAttri' is too long. Maximum length is 128. http://go.microsoft.com/fwlink/?LinkId=178587  Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_SQL_EXECUTION_FAILURE. ---> System.Data.SqlClient.SqlException: The identifier that starts with 'Product_SpecificationAttribute_Mapping_FK_Product_SpecificationAttribute_Mapping_SpecificationAttributeOption_SpecificationAttri' 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, Nullable`1 syncPassId)
   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__DisplayClass8.<StartMigrate>b__7(Object sender, DoWorkEventArgs eventArgs)
   at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
---------------------------

does anyone have any idea how i can fix this, i have online for and nothing seems to work ?

thnaks in advance

Seems like a bug in WebMatrix

NopC does not have identifier

'Product_SpecificationAttribute_Mapping_FK_Product_SpecificationAttribute_Mapping_SpecificationAttributeOption_SpecificationAttri' is too long. Maximum length is 128

It seems to be concatenating the table name
   Product_SpecificationAttribute_Mapping
with the key name
   ProductSpecificationAttribute_SpecificationAttributeOption

This is what I see if I script that FK in SSMS in my db

ALTER TABLE [dbo].[Product_SpecificationAttribute_Mapping]  WITH CHECK ADD  CONSTRAINT [ProductSpecificationAttribute_SpecificationAttributeOption] FOREIGN KEY([SpecificationAttributeOptionId])
REFERENCES [dbo].[SpecificationAttributeOption] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[Product_SpecificationAttribute_Mapping] CHECK CONSTRAINT [ProductSpecificationAttribute_SpecificationAttributeOption]
GO


Sorry, I 've got no ideas on a workaround.  You may want to post this issue in a WebMatrix forum. (Or find another migration method)

hi thanks for your response, i did some more research and apparently you are right, this is a bug in webmatrix, i have found a workaround and will post it when i can in case anyone else has the same problem, i was wandering why everytime i tried to install using sql express i was having problems, i think this would be the cause.

regards

kev

here is what i did to change my database, just in case anyone else needs to do it.

i created a new sql server r2 database and named it using server r2 management studio

i then downloaded PRIMEWORKS Data Port Wizard (link below) and installed

http://www.primeworks-mobile.com/Downloads-2.html

the program is easy to follow and once the source and target details had been filled in the old compact database was migrated to the new 2008 r2 database that i had created.

at the beginning of all this, a new connection string had been added in the web.config which needed to be removed as this was the wrong place for it, Thanks to new york who explained that it should have been added in app_data / settings.txt and the old compact string should be removed (and copy and pasted in case something goes wrong) i then entered my new string which worked for me as i was using integrated authentication this was

DataProvider: sqlserver
DataConnectionString:  Data Source=(My PC);Initial Catalog=(My New Database);Integrated Security=True

my new database now worked however my products wouldn't load and the error i kept getting was

Could not find stored procedure 'ProductLoadAllPaged'

i solved this by opening SqlServer.StoredProcedures.sql  (located in app_data folder)  in mangement stuidio, located

CREATE PROCEDURE [ProductLoadAllPaged]

i then added a comma at the end of the data within that procedure which was

@TotalRecords, i then saved the file at which point i cleared the cache and restarted webmatrix and everything loaded

i then removed the comma after

@TotalRecords and again saved the file and it worked as it did before.

not sure why that happened and cannot understand why that fixed it but there you go.

regards