SQL error upgrading from SC 5.0 to 5.17 BETA (Smith

Item		Qty	Total
	{{cartItem.ProductName}} SKU: {{cartItem.ProductSku}}	{{cartItem.Quantity \| number : 2}} x {{cartItem.UnitCost \| currency : '$' : 2}}	{{cartItem.UnitCost * cartItem.Quantity \| currency : '$' : 2}}
{{itemVariant.VariantHasText ? (itemVariant.VariantName + ': ' + itemVariant.VariantText) : (itemVariant.VariantGroup + ': ' + itemVariant.VariantName)}}
You have no items in your shopping cart.
Subtotal	{{minicart.cartTotals.SubTotal \| currency : '$' : 2}}

Image	Item	Qty	Total
	{{cartItem.ProductName}} SKU: {{cartItem.ProductSku}}	{{cartItem.Quantity \| number : 2}} x {{cartItem.UnitCost \| currency : '$' : 2}}	{{cartItem.UnitCost * cartItem.Quantity \| currency : '$' : 2}}
{{itemVariant.VariantHasText ? (itemVariant.VariantName + ': ' + itemVariant.VariantText) : (itemVariant.VariantGroup + ': ' + itemVariant.VariantName)}}
You have no items in your shopping cart.

Status	Order No.	Order Date	Ship To	Order Total	Pay Total	Balance
Bill Me Later Open Ready To Ship Shipped Picked Up Cancelled Incomplete On Hold Back Ordered Returned {{order.Status}}	{{order.OrderID}}	{{order.OrderDate \| date : 'M/d/yyyy'}}	{{order.ShipFirstName}} {{order.ShipLastName}}, {{order.ShipAddress1}} {{order.ShipAddress2}}, {{order.ShipCity}}, {{order.ShipState}} {{order.ShipZipcode}}	{{order.GrandTotal \| currency : '$' : 2}}	{{order.PayTotal \| currency : '$' : 2}}	{{order.GrandTotal - order.PayTotal \| currency : '$' : 2}}

Home

Product Discus...

SmithCart

SQL error upgrading from SC 5.0 to 5.17 BETA (Smith_ListVariantIDByVariantName)

10/6/2012 1:05 AM

Daniel Taft

Joined: 8/1/2010

Posts: 252

SQL error upgrading from SC 5.0 to 5.17 BETA (Smith_ListVariantIDByVariantName)

Hi SC

I have DNN 6.2.2 (upgraded from 6.0.0), and SC 5.0. I ran an upgrade on a test portal to SC 5.17 BETA and it gave the following error below.

It's a bad one because the Buy-Now module is stuck on 5.0, and all the other modules are at 5.17, which breaks everything catastrophically. Good to always do upgrades on test portals...

StartJob	Starting Installation
Info	Starting Installation - Smith.BuyNow
Info	Starting Installation - Script
Info	Begin Sql execution
Info	Created - 05.10.00.SqlDataProvider
Info	Executing 05.10.00.SqlDataProvider
Info	Start Sql execution: 05.10.00.SqlDataProvider file
Info	End Sql execution: 05.10.00.SqlDataProvider file
Info	Created - 05.12.00.SqlDataProvider
Info	Executing 05.12.00.SqlDataProvider
Info	Start Sql execution: 05.12.00.SqlDataProvider file
Failure	SQL Execution resulted in following Exceptions: System.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. 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 DotNetNuke.Data.SqlDataProvider.ExecuteADOScript(String SQL) at DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean UseTransactions) Create PROCEDURE dbo.Smith_ListVariantIDByVariantName @ProductID int, @VariantNameList nvarchar(1000) as SET NOCOUNT ON DECLARE @TempList table ( VariantName nvarchar(100) ) INSERT INTO @TempList (VariantName) select * from [dbo].[scSplit](@VariantNameList, '~') SELECT pv.VariantID FROM Smith_ProductVariant pv inner JOIN @TempList t ON pv.VariantName = t.VariantName where pv.ProductID = @ProductID
Info	End Sql execution: 05.12.00.SqlDataProvider file
Info	Finished Sql execution
Failure	Installation Failed - Script
Info	Installation Failed - Smith.BuyNow
Info	Starting Installation - Smith.AffiliateTracking
Info	Starting Installation - Module
Info	Module registered successfully - Smith.AffiliateTracking
Info	Component installed successfully - Module
Info	Starting Installation - Assembly
Info	Assembly registered - bin\SmithAffiliateTracking.dll
Info	Created - bin\SmithAffiliateTracking.dll
Info	Component installed successfully - Assembly
Failure	Package Installation aborted
Info	Installation Failed - Smith.AffiliateTracking
Info	Starting Installation - Smith.CategoryMenu
Info	Starting Installation - Module
Info	Module registered successfully - Smith.CategoryMenu
Info	Component installed successfully - Module
Info	Starting Installation - Assembly
Info	Assembly registered - bin\SmithCategoryMenu.dll
Info	Created - bin\SmithCategoryMenu.dll
Info	Component installed successfully - Assembly
Failure	Package Installation aborted
Info	Installation Failed - Smith.CategoryMenu
Info	Starting Installation - Smith.ManufacturerMenu
Info	Starting Installation - Module
Info	Module registered successfully - Smith.ManufacturerMenu
Info	Component installed successfully - Module
Info	Starting Installation - Assembly
Info	Assembly registered - bin\SmithManufacturerMenu.dll
Info	Created - bin\SmithManufacturerMenu.dll
Info	Component installed successfully - Assembly
Failure	Package Installation aborted
Info	Installation Failed - Smith.ManufacturerMenu
Info	Starting Installation - Smith.FeaturedProducts
Info	Starting Installation - Module
Info	Module registered successfully - Smith.FeaturedProducts
Info	Component installed successfully - Module
Info	Starting Installation - Assembly
Info	Assembly registered - bin\SmithFeaturedProducts.dll
Info	Created - bin\SmithFeaturedProducts.dll
Info	Component installed successfully - Assembly
Failure	Package Installation aborted
Info	Installation Failed - Smith.FeaturedProducts
Info	Starting Installation - Smith.MiniCart
Info	Starting Installation - Module
Info	Module registered successfully - Smith.MiniCart
Info	Component installed successfully - Module
Info	Starting Installation - Assembly
Info	Assembly registered - bin\SmithMiniCart.dll
Info	Created - bin\SmithMiniCart.dll
Info	Component installed successfully - Assembly
Failure	Package Installation aborted
Info	Installation Failed - Smith.MiniCart
Info	Starting Installation - Smith.MyAccount
Info	Starting Installation - Module
Info	Module registered successfully - Smith.MyAccount
Info	Component installed successfully - Module
Info	Starting Installation - Assembly
Info	Assembly registered - bin\SmithMyAccount.dll
Info	Created - bin\SmithMyAccount.dll
Info	Component installed successfully - Assembly
Failure	Package Installation aborted
Info	Installation Failed - Smith.MyAccount
Info	Starting Installation - Smith.QuickOrderEntry
Info	Starting Installation - Module
Info	Module registered successfully - Smith.QuickOrderEntry
Info	Component installed successfully - Module
Info	Starting Installation - Assembly
Info	Assembly registered - bin\SmithQuickOrderEntry.dll
Info	Created - bin\SmithQuickOrderEntry.dll
Info	Component installed successfully - Assembly
Failure	Package Installation aborted
Info	Installation Failed - Smith.QuickOrderEntry
Info	Starting Installation - Smith.ProductDetail
Info	Starting Installation - Module
Info	Module registered successfully - Smith.ProductDetail
Info	Component installed successfully - Module
Info	Starting Installation - Assembly
Info	Assembly registered - bin\SmithProductDetail.dll
Info	Created - bin\SmithProductDetail.dll
Info	Component installed successfully - Assembly
Failure	Package Installation aborted
Info	Installation Failed - Smith.ProductDetail
Info	Starting Installation - Smith.Vendor
Info	Starting Installation - Module
Info	Module registered successfully - Smith.Vendor
Info	Component installed successfully - Module
Info	Starting Installation - Assembly
Info	Assembly registered - bin\SmithVendor.dll
Info	Created - bin\SmithVendor.dll
Info	Component installed successfully - Assembly
Failure	Package Installation aborted
Info	Installation Failed - Smith.Vendor
Info	Starting Installation - Smith.Search
Info	Starting Installation - Module
Info	Module registered successfully - Smith.Search
Info	Component installed successfully - Module
Info	Starting Installation - Assembly
Info	Assembly registered - bin\SmithSearch.dll
Info	Created - bin\SmithSearch.dll
Info	Component installed successfully - Assembly
Failure	Package Installation aborted
Info	Installation Failed - Smith.Search
Info	Starting Installation - Smith.AddressBook
Info	Starting Installation - Module
Info	Module registered successfully - Smith.AddressBook
Info	Component installed successfully - Module
Info	Starting Installation - Assembly
Info	Assembly registered - bin\SmithAddressBook.dll
Info	Created - bin\SmithAddressBook.dll
Info	Component installed successfully - Assembly
Failure	Package Installation aborted
Info	Installation Failed - Smith.AddressBook
Info	Starting Installation - Smith.SalesRep
Info	Starting Installation - Module
Info	Module registered successfully - Smith.SalesRep
Info	Component installed successfully - Module
Info	Starting Installation - Assembly
Info	Assembly registered - bin\SmithSalesRep.dll
Info	Created - bin\SmithSalesRep.dll
Info	Component installed successfully - Assembly
Failure	Package Installation aborted
Info	Installation Failed - Smith.SalesRep
Info	Deleted temporary install folder
EndJob	Installation Failed

10/7/2012 5:27 PM

Dave Smith

Joined: 7/28/2009

Posts: 1452

Re: SQL error upgrading from SC 5.0 to 5.17 BETA (Smith_ListVariantIDByVariantName)

The cause of this error is that the ProductVariant table has a different colation then your sql server database installation. I looked and found that the "01.00.00.SqlDataProvider" has a few colation statements on the Product Variant table. It looks like the variantname column may be the culprit. I have removed the colation statements from the "01.00.00.SqlDataProvider" file that will be available in the next release. For more info see the following stackoverflow post:

http://stackoverflow.com/questions/16...

If you would like to fix this now you can open the "01.00.00.SqlDataProvider" file from the pa zip and search on "latin" and remove the colation statements and then save and add the "01.00.00.SqlDataProvider" back to the zip file.

At your service,
Dave Smith
DotNetNuke Consulting, DotNetNuke Store and DNN Ecommerce

10/7/2012 6:43 PM

Daniel Taft

Joined: 8/1/2010

Posts: 252

Re: SQL error upgrading from SC 5.0 to 5.17 BETA (Smith_ListVariantIDByVariantName) Modified By Daniel Taft on 10/7/2012 6:44:14 PM

Thanks Dave,

Strange that it happened now, since previous Smith Cart upgrades on my same SQL Server were all fine so far.

Do you have an ETA on the updated release with this statement fixed?

And for example this statement below in 01.00.00.SqlDataProvider, do I delete the bold text?

CREATE TABLE {databaseOwner}[{objectQualifier}Smith_ProductVariant](
[VariantID] [int] IDENTITY(1,1) NOT NULL,
[ProductID] [int] NULL,
[VariantGroup] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VariantName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VariantImage] [nvarchar](500) NULL,
[PriceAdjustment] [money] NULL,
[WeightAdjustment] [float] NULL,
[SortOrder] [int] NULL,
[PortalId] [int] NULL
) ON [PRIMARY]

10/7/2012 11:41 PM

Dave Smith

Joined: 7/28/2009

Posts: 1452

Re: SQL error upgrading from SC 5.0 to 5.17 BETA (Smith_ListVariantIDByVariantName)

The ETA for the updated beta release with sqldataprovider fix is tomorrow after testing.

You are correct, in your example just delete the bold text. Also search the 01.00.00.SqlDataProvider for any sprocs with colation statements and remove those as well.

At your service,
Dave Smith
DotNetNuke Consulting, DotNetNuke Store and DNN Ecommerce

Page 1 of 1

Home

Product Discus...

SmithCart

SQL error upgrading from SC 5.0 to 5.17 BETA (Smith_ListVariantIDByVariantName)

You have no items in your shopping cart.

You have 1 item in your shopping cart.

{{multiCartItemsStr.replace('[COUNT]', minicart.cartList.length)}}

Item(s): {{minicart.countCart().Number | number : 2}}

Questions? Contact us for answers!

Services

DNN Modules

Support

Contact Us