[dbo] schema issue

Jun 2, 2009 at 11:22 PM

I had this issue when installing the package on a clean umbraco install.

Package would install completely, however when clicking on the commerce admin area, a database error would appear stating an 'Invlaid Object Name dbo.CSK_store_order

To solve this problem, when creating a new database for the ne Umbaco install, you need to ensure that the [dbo] schema is set as the default schema, which in my case it wasn't - the username was the default.

Once changing this setting in the database prior to installing Umbraco - the entire setup for Commerce for Umbraco went through without a hitch.

 

Aug 10, 2009 at 8:51 PM
screamr wrote:

I had this issue when installing the package on a clean umbraco install.

Package would install completely, however when clicking on the commerce admin area, a database error would appear stating an 'Invlaid Object Name dbo.CSK_store_order

To solve this problem, when creating a new database for the ne Umbaco install, you need to ensure that the [dbo] schema is set as the default schema, which in my case it wasn't - the username was the default.

Once changing this setting in the database prior to installing Umbraco - the entire setup for Commerce for Umbraco went through without a hitch.

 

 Are you telling me that I need to have the database username to be "dbo"?

If not, how do I change the schema qualifier? This is all new to me, sorry.

Coordinator
Aug 10, 2009 at 9:01 PM

No, you can set the user's default schema to DBO in Management Studio from Login > Properties.  That's all you're trying to accomplish.

-Paul

Aug 11, 2009 at 3:43 AM

I found a query, and a tutorial of how to do it online.

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o.Name
    FROM sys.Objects o
    INNER JOIN sys.Schemas s on o.schema_id = s.schema_id
    WHERE s.Name = 'yourschema'
    And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')

Then copy and paste all of the results in another query, and execute (!) it.

All I need now is figure out why when I try to create a product, I obtain the following error.

 

Server Error in '/' Application.

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CMRC_Products_CMRC_ProductStatus". The conflict occurred in database "oishiadb", table "dbo.CSK_Store_ProductStatus", column 'statusID'.
The statement has been terminated.

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: SubSonic.SqlQueryException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CMRC_Products_CMRC_ProductStatus". The conflict occurred in database "oishiadb", table "dbo.CSK_Store_ProductStatus", column 'statusID'.
The statement has been terminated.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.


Stack Trace:

[SqlQueryException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CMRC_Products_CMRC_ProductStatus". The conflict occurred in database "oishiadb", table "dbo.CSK_Store_ProductStatus", column 'statusID'.
The statement has been terminated.]
   SubSonic.Insert.Execute() +350
   CommerceForUmbraco.adminSection.tasks.ProductTasks.InsertProduct() +1992
   CommerceForUmbraco.adminSection.tasks.ProductTasks.Save() +44
   umbraco.presentation.create.dialogHandler_temp.Create(String NodeType, Int32 TypeId, Int32 NodeId, String Text) +344
   CommerceForUmbraco.adminSection.commerce.CreateProduct.sbmt_Click(Object sender, EventArgs e) +168
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565



Version Information: Microsoft .NET Framework Version:2.0.50727.3082; ASP.NET Version:2.0.50727.3082

<!-- [SqlQueryException]: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CMRC_Products_CMRC_ProductStatus". The conflict occurred in database "oishiadb", table "dbo.CSK_Store_ProductStatus", column 'statusID'. The statement has been terminated. at SubSonic.Insert.Execute() at CommerceForUmbraco.adminSection.tasks.ProductTasks.InsertProduct() at CommerceForUmbraco.adminSection.tasks.ProductTasks.Save() at umbraco.presentation.create.dialogHandler_temp.Create(String NodeType, Int32 TypeId, Int32 NodeId, String Text) at CommerceForUmbraco.adminSection.commerce.CreateProduct.sbmt_Click(Object sender, EventArgs e) at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) [HttpUnhandledException]: Exception of type 'System.Web.HttpUnhandledException' was thrown. at System.Web.UI.Page.HandleError(Exception e) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) at System.Web.UI.Page.ProcessRequest() at System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) at System.Web.UI.Page.ProcessRequest(HttpContext context) at ASP.umbraco_create_aspx.ProcessRequest(HttpContext context) at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) --><!-- This error page might contain sensitive information because ASP.NET is configured to show verbose error messages using <customErrors mode="Off"/>. Consider using <customErrors mode="On"/> or <customErrors mode="RemoteOnly"/> in production environments.-->
Feb 10, 2010 at 3:05 PM

Just in case anybody is interested I had the same problem, and modified the database object schemas using a similar method to kevingul. I then also got the foreign key issues that kevin got - this was due to none of the base data, sample data etc being added to the database.

I extracted the commerce for umbraco package and executed the base data scripts against my database which seems to have resolved this immediate error, although I am getting some other problems - i.e. I dont seem to be able to delete a product in the interface.

 

Matt

Coordinator
Feb 10, 2010 at 3:36 PM

Matt -

FYI - there is no delete from the UI currently.  It's in the work item list though!

-Paul