System.Xml.Xsl.XslTransformException: An error occurred during a call to extension function 'GetTargetedCampaignItems'. See InnerException for a complete description of the error. ---> NHibernate.Exceptions.GenericADOException: could not execute query [ select campaignit0_.CampaignItemId as Campaign1_10_, campaignit0_.CampaignId as CampaignId10_, campaignit0_.Name as Name10_, campaignit0_.Enabled as Enabled10_, campaignit0_.Deleted as Deleted10_, campaignit0_.Priority as Priority10_, campaignit0_.AllowNextCampaignItems as AllowNex7_10_, campaignit0_.CreatedOn as CreatedOn10_, campaignit0_.ModifiedOn as ModifiedOn10_, campaignit0_.CreatedBy as CreatedBy10_, campaignit0_.ModifiedBy as ModifiedBy10_, campaignit0_.DefinitionId as Definit12_10_ from uCommerce_CampaignItem campaignit0_ left outer join uCommerce_Campaign campaign1_ on campaignit0_.CampaignId=campaign1_.CampaignId, uCommerce_Campaign campaign2_ where campaignit0_.CampaignId=campaign2_.CampaignId and campaignit0_.Enabled=1 and campaign2_.Enabled=1 and campaign2_.StartsOn<@p0 and campaign2_.EndsOn>@p1 and not (campaignit0_.Deleted=1) order by campaign1_.Priority asc, campaignit0_.Priority asc ] Name:p1 - Value:01/07/2011 10:35:05 Name:p2 - Value:01/07/2011 10:35:05 [SQL: select campaignit0_.CampaignItemId as Campaign1_10_, campaignit0_.CampaignId as CampaignId10_, campaignit0_.Name as Name10_, campaignit0_.Enabled as Enabled10_, campaignit0_.Deleted as Deleted10_, campaignit0_.Priority as Priority10_, campaignit0_.AllowNextCampaignItems as AllowNex7_10_, campaignit0_.CreatedOn as CreatedOn10_, campaignit0_.ModifiedOn as ModifiedOn10_, campaignit0_.CreatedBy as CreatedBy10_, campaignit0_.ModifiedBy as ModifiedBy10_, campaignit0_.DefinitionId as Definit12_10_ from uCommerce_CampaignItem campaignit0_ left outer join uCommerce_Campaign campaign1_ on campaignit0_.CampaignId=campaign1_.CampaignId, uCommerce_Campaign campaign2_ where campaignit0_.CampaignId=campaign2_.CampaignId and campaignit0_.Enabled=1 and campaign2_.Enabled=1 and campaign2_.StartsOn<@p0 and campaign2_.EndsOn>@p1 and not (campaignit0_.Deleted=1) order by campaign1_.Priority asc, campaignit0_.Priority asc] ---> System.Data.SqlClient.SqlException: Invalid object name 'uCommerce_CampaignItem'. 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.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) --- End of inner exception stack trace --- at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) at NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results) at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) at NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters) at NHibernate.Impl.ExpressionQueryImpl.List() at NHibernate.Linq.NhQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery) at NHibernate.Linq.NhQueryProvider.Execute[TResult](Expression expression) at Remotion.Data.Linq.QueryableBase`1.GetEnumerator() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at UCommerce.Marketing.MarketingService.GetActiveCampaignItems() at UCommerce.Marketing.MarketingService.GetTargetedCampaignItems() at UCommerce.Xslt.MarketingLibrary.GetTargetedCampaignItems() at UCommerce.Xslt.Library.b__5f() at UCommerce.Xslt.Library.HandledCall[T](Func`1 method) at UCommerce.Xslt.Library.GetTargetedCampaignItems() --- End of inner exception stack trace --- at System.Xml.Xsl.Runtime.XmlExtensionFunction.Invoke(Object extObj, Object[] args) at System.Xml.Xsl.Runtime.XmlQueryContext.InvokeXsltLateBoundFunction(String name, String namespaceUri, IList`1[] args) at campaignItems(XmlQueryRuntime {urn:schemas-microsoft-com:xslt-debug}runtime) at Execute(XmlQueryRuntime {urn:schemas-microsoft-com:xslt-debug}runtime) at System.Xml.Xsl.XmlILCommand.Execute(Object defaultDocument, XmlResolver dataSources, XsltArgumentList argumentList, XmlSequenceWriter results) at System.Xml.Xsl.XmlILCommand.Execute(Object defaultDocument, XmlResolver dataSources, XsltArgumentList argumentList, XmlWriter writer) at System.Xml.Xsl.XslCompiledTransform.Transform(IXPathNavigable input, XsltArgumentList arguments, XmlWriter results, XmlResolver documentResolver) at System.Xml.Xsl.XslCompiledTransform.Transform(IXPathNavigable input, XsltArgumentList arguments, TextWriter results) at umbraco.macro.GetXsltTransformResult(XmlDocument macroXML, XslCompiledTransform xslt, Dictionary`2 parameters) at umbraco.presentation.umbraco.developer.Xslt.xsltVisualize.visualizeDo_Click(Object sender, EventArgs e)
When i look in the Analytics section and try and run a report i get the following message and i'm not sure why they haven't been set and what i'd need to set them too:
The EXECUTE permission was denied on the object 'uCommerce_GetTotalSales', database 'mydatabase', schema 'dbo'.
the more i look at the settings, the more errors i get, i have followed the video on uCommerce TV and i now get errors in AllCatalogs.xslt and RootCategories.xlst.
when i try and open product definitions i get an .NET error screen....
Should i go to uCommerce 1.5 rather than 2 and the previous version of the shop?
Can anyone help with figuring out permissions as i am unsure what to look for?
I appear to be missing some database entries but only some, i'm not sure why a permissions error would cause some to be missing and not others, can someone suggest a reason why?
I really need to get this nailed so any help would be most appreciated.
Having a look in the umbracoLog table i can see that when i installed the uCommerce package it threw this error:
Error in uCommerce.DbInstaller.InstallDatabase() script:D:\Inetpub\wwwroot\cropwell2011\umbraco\ucommerce\install\uCommerceDB.043.sql, error: System.Data.SqlClient.SqlException (0x80131904): Cannot find the object 'uCommerce_GetTotalSales', because it does not exist or you do not have permission.
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.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 UCommerce.Installer.DbInstaller.InstallDatabase().
It doesn't seem to have run anymore of the install .sql files after this point but i'm not sure why it hasn't created this table (and others). I think it creates all the tables in uCommerceDB.001.sql but not sure why they aren't in my database!
Turns out script 043.sql was trying to change settings on stored procedures and the user didn't have permission to do that. gave it permission and it's all started working correctly.
uCommerce 2 lots of problems
Hi,
Can anyone help me please!?
just installed v2 and having lots of problems with the setup.
tried to delete the product definitions to add my own and got:
When i navigate to the shop i get
Error parsing XSLT file: \xslt\Category[XSLT].xsltwhich when i use the visualiser gets me:
System.Xml.Xsl.XslTransformException: An error occurred during a call to extension function 'GetTargetedCampaignItems'. See InnerException for a complete description of the error. ---> NHibernate.Exceptions.GenericADOException: could not execute query [ select campaignit0_.CampaignItemId as Campaign1_10_, campaignit0_.CampaignId as CampaignId10_, campaignit0_.Name as Name10_, campaignit0_.Enabled as Enabled10_, campaignit0_.Deleted as Deleted10_, campaignit0_.Priority as Priority10_, campaignit0_.AllowNextCampaignItems as AllowNex7_10_, campaignit0_.CreatedOn as CreatedOn10_, campaignit0_.ModifiedOn as ModifiedOn10_, campaignit0_.CreatedBy as CreatedBy10_, campaignit0_.ModifiedBy as ModifiedBy10_, campaignit0_.DefinitionId as Definit12_10_ from uCommerce_CampaignItem campaignit0_ left outer join uCommerce_Campaign campaign1_ on campaignit0_.CampaignId=campaign1_.CampaignId, uCommerce_Campaign campaign2_ where campaignit0_.CampaignId=campaign2_.CampaignId and campaignit0_.Enabled=1 and campaign2_.Enabled=1 and campaign2_.StartsOn<@p0 and campaign2_.EndsOn>@p1 and not (campaignit0_.Deleted=1) order by campaign1_.Priority asc, campaignit0_.Priority asc ] Name:p1 - Value:01/07/2011 10:35:05 Name:p2 - Value:01/07/2011 10:35:05 [SQL: select campaignit0_.CampaignItemId as Campaign1_10_, campaignit0_.CampaignId as CampaignId10_, campaignit0_.Name as Name10_, campaignit0_.Enabled as Enabled10_, campaignit0_.Deleted as Deleted10_, campaignit0_.Priority as Priority10_, campaignit0_.AllowNextCampaignItems as AllowNex7_10_, campaignit0_.CreatedOn as CreatedOn10_, campaignit0_.ModifiedOn as ModifiedOn10_, campaignit0_.CreatedBy as CreatedBy10_, campaignit0_.ModifiedBy as ModifiedBy10_, campaignit0_.DefinitionId as Definit12_10_ from uCommerce_CampaignItem campaignit0_ left outer join uCommerce_Campaign campaign1_ on campaignit0_.CampaignId=campaign1_.CampaignId, uCommerce_Campaign campaign2_ where campaignit0_.CampaignId=campaign2_.CampaignId and campaignit0_.Enabled=1 and campaign2_.Enabled=1 and campaign2_.StartsOn<@p0 and campaign2_.EndsOn>@p1 and not (campaignit0_.Deleted=1) order by campaign1_.Priority asc, campaignit0_.Priority asc] ---> System.Data.SqlClient.SqlException: Invalid object name 'uCommerce_CampaignItem'. 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.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) --- End of inner exception stack trace --- at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) at NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters) at NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results) at NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results) at NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters) at NHibernate.Impl.ExpressionQueryImpl.List() at NHibernate.Linq.NhQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery) at NHibernate.Linq.NhQueryProvider.Execute[TResult](Expression expression) at Remotion.Data.Linq.QueryableBase`1.GetEnumerator() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at UCommerce.Marketing.MarketingService.GetActiveCampaignItems() at UCommerce.Marketing.MarketingService.GetTargetedCampaignItems() at UCommerce.Xslt.MarketingLibrary.GetTargetedCampaignItems() at UCommerce.Xslt.Library.b__5f() at UCommerce.Xslt.Library.HandledCall[T](Func`1 method) at UCommerce.Xslt.Library.GetTargetedCampaignItems() --- End of inner exception stack trace --- at System.Xml.Xsl.Runtime.XmlExtensionFunction.Invoke(Object extObj, Object[] args) at System.Xml.Xsl.Runtime.XmlQueryContext.InvokeXsltLateBoundFunction(String name, String namespaceUri, IList`1[] args) at campaignItems(XmlQueryRuntime {urn:schemas-microsoft-com:xslt-debug}runtime) at Execute(XmlQueryRuntime {urn:schemas-microsoft-com:xslt-debug}runtime) at System.Xml.Xsl.XmlILCommand.Execute(Object defaultDocument, XmlResolver dataSources, XsltArgumentList argumentList, XmlSequenceWriter results) at System.Xml.Xsl.XmlILCommand.Execute(Object defaultDocument, XmlResolver dataSources, XsltArgumentList argumentList, XmlWriter writer) at System.Xml.Xsl.XslCompiledTransform.Transform(IXPathNavigable input, XsltArgumentList arguments, XmlWriter results, XmlResolver documentResolver) at System.Xml.Xsl.XslCompiledTransform.Transform(IXPathNavigable input, XsltArgumentList arguments, TextWriter results) at umbraco.macro.GetXsltTransformResult(XmlDocument macroXML, XslCompiledTransform xslt, Dictionary`2 parameters) at umbraco.presentation.umbraco.developer.Xslt.xsltVisualize.visualizeDo_Click(Object sender, EventArgs e)When i look in the Analytics section and try and run a report i get the following message and i'm not sure why they haven't been set and what i'd need to set them too:
The EXECUTE permission was denied on the object 'uCommerce_GetTotalSales', database 'mydatabase', schema 'dbo'.
Hi Chris,
Looks like the install was unsuccessful. Which database are you running on MS SQL Server (Express)?
Can you verify that you've got entries in the table uCommerce_AdminPage in your database?
Hi Soren,
Yes it's MS SQL 2005 and yes there are entries in that table. 27 entries in total.
Should i remove and re-install?
Thanks
Chris
Does the user running the apppool have access to write to the database as well as the file system?
Also is the connection string pointing to the right database? (There's a separate conn string for uCommerce in web.config).
Thanks for your help Soren but we have had to bin the whole site and start again after these problems.
Thanks anyway.
After rebuilding the site and re-installing the uCommerce, i still get the same error in the Category.xslt
can someone help me debug this issue as i need to get this site sorted asap.
Thanks
Hi Chris
Did you ever figure out if the app pool user had the proper permissions as Søren is mentioning above?
/Jan
How do i establish that?
in answer to Soren,
The user running the app pool is the network service, how would i tell if that has write access to the database?
I have checked the second connection string and it is correct.
Thanks
the more i look at the settings, the more errors i get, i have followed the video on uCommerce TV and i now get errors in AllCatalogs.xslt and RootCategories.xlst.
when i try and open product definitions i get an .NET error screen....
Should i go to uCommerce 1.5 rather than 2 and the previous version of the shop?
Thanks
Chris
Can anyone help with figuring out permissions as i am unsure what to look for?
I appear to be missing some database entries but only some, i'm not sure why a permissions error would cause some to be missing and not others, can someone suggest a reason why?
I really need to get this nailed so any help would be most appreciated.
Thanks
Chris
Having a look in the umbracoLog table i can see that when i installed the uCommerce package it threw this error:
Error in uCommerce.DbInstaller.InstallDatabase() script:D:\Inetpub\wwwroot\cropwell2011\umbraco\ucommerce\install\uCommerceDB.043.sql, error: System.Data.SqlClient.SqlException (0x80131904): Cannot find the object 'uCommerce_GetTotalSales', because it does not exist or you do not have permission.
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 UCommerce.Installer.DbInstaller.InstallDatabase().
It doesn't seem to have run anymore of the install .sql files after this point but i'm not sure why it hasn't created this table (and others). I think it creates all the tables in uCommerceDB.001.sql but not sure why they aren't in my database!
Thanks
Chris
ok, finally have it sorted.
Turns out script 043.sql was trying to change settings on stored procedures and the user didn't have permission to do that. gave it permission and it's all started working correctly.
Chris
Hi Chris,
Sorry for the inconvenience. I'm glad that you got it running in the end.
is working on a reply...