Database Error

Author	« Topic »
cooper Ecommerce Template Guru USA 2807 Posts Pre-sales questions only (More Details...)	Posted - 01/14/2022 : 13:11:53 Site hosting company migrated my site to a new server. The SQL database field types didn't make the move well. https://summercedar.com/proddetail.asp?prod=PB48U My detail page is supposed to show tabs, reviews, social buttons and related products underneath the ADD TO CART, but now I'm seeing: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The data type text cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable. I used the database utility program to set all available fields to TEXT and it made the site work, however, it appears one of the fields needs to be something else. Any ideas? Bill
Vince Administrator 42466 Posts	Posted - 01/14/2022 : 15:31:05 Hi Bill When you converted the datatype to TEXT there is a 3rd option which is VARCHAR(8000). This is the longest datatype that SQL Server treats as normal text and not a BLOB type datatype. If your descriptions and long descriptions are not longer than 8000 characters then this could solve all your problems I believe. Vince Click Here for Shopping Cart Software Click Here to sign up for our newsletter Click Here for the latest updater
cooper Ecommerce Template Guru USA 2807 Posts Pre-sales questions only (More Details...)	Posted - 01/16/2022 : 06:06:55 Thanks, Vince! Ran it and the site works well now. Two fields threw errors: ALTER TABLE [emailmessages] ALTER COLUMN emailheaders VARCHAR(8000) NULL CONVERSION ERROR: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]String or binary data would be truncated. ALTER TABLE [emailmessages] ALTER COLUMN orderstatusemail VARCHAR(8000) NULL CONVERSION ERROR: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]String or binary data would be truncated. I've just checked and it looks like my email statuses are blank. Will try to manually recreate them now! Bill
Vince Administrator 42466 Posts	Posted - 01/17/2022 : 01:42:52 Hi Bill quote: I used the database utility program to set all available fields to TEXT and it made the site work Did you try "TEXT" with those two fields? Vince Click Here for Shopping Cart Software Click Here to sign up for our newsletter Click Here for the latest updater
cooper Ecommerce Template Guru USA 2807 Posts Pre-sales questions only (More Details...)	Posted - 01/20/2022 : 12:20:10 That did it, Vince! Thank you! Bill
	« Topic »