unsupported T-SQL statements in 2.70 upgrade script (SQL Azure)..see last post

I had a running 2.65 version of nopcommerce running on a Windows 2003 server. I obtained a new Windows 2008 server and installed a new version 2.80 of nopcommerce, and ran the upgrade scripts for 2.70 and 2.80 on my database. They both executed successfully..no errors. I copied my settings.txt file from the 2.65 installation and pasted it into the App_Data folder of the new 2.80 installation.

When I view it in a browser, this is what I get:

-------------------------------------------------------------------------------------------------
Invalid object name 'dbo.UrlRecord'.

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: System.Data.SqlClient.SqlException: Invalid object name 'dbo.UrlRecord'.

Source Error:


Line 8:          <div class="clear">
Line 9:          </div>
Line 10:         @Html.Action("HomepageCategories", "Catalog")
Line 11:         <div class="clear">
Line 12:         </div>

Source File: c:\inetpub\wwwroot\MFSdev\Views\Home\Index.cshtml    Line: 10
-------------------------------------------------------------------------------------------------

What went wrong and what should I do to fix it?

Thanks,
Steve

The UrlRecord is used when the category model is built to get the active slug for the category.

The UrlRecord table is new in nopCommerce 2.7, so your upgrade script that you run for nopCommerce 2.7 should have created this table.

Check your database to see if you have an UrlRecord table.

7Spikes wrote:

I just looked at the tables, and UrlRecord was not created when I ran the 2.70 upgrade script...

I ran the 2.80 database upgrade script right after I ran the 2.70 upgrade script, so I'm guessing that it all went haywire and has to be redone? Or can I trust that all the other tables/fields were created correctly and just manually create the missing table/fields??

What should I do now?

Thanks,
Steve

embryo wrote:

You should definitely revert your database and run the scripts again. Make sure that you have no errors and that queries run successfully one by one.

7Spikes wrote:

This really concerns me, because as I said, both scripts executed successfully.. without a single error being reported. Reverting my database and running the 2.70 and 2.80 database upgrade scripts is a time-consuming task just to have the same outcome again.

My database is on Windows SQL Azure...might that be an issue? It hasn't been thus far...

When executing the upgrade scripts, I should be logged onto the actual database, not the master database, correct??

Thanks,
Steve

I ran the 2.70 upgrade script again and below are the errors returned..several appeared a few times each, and they were all in a group near the end of the report, after the majority of them completed successfuly.


Msg 208, Level 16, State 1, Line 587
Invalid object name 'sysindexes'.


Msg 40514, Level 16, State 1, Line 610
'Filegroup reference and partitioning scheme' is not supported in this version of SQL Server.

Msg 208, Level 16, State 1, Line 743
Invalid object name 'UrlRecord'.

Msg 16915, Level 16, State 1, Line 849
A cursor with the name 'cur_sename_existing_entity' already exists.

Msg 16905, Level 16, State 1, Line 851
The cursor is already open.


Does this give you any clue as to what could be going wrong and what I might do to get past it?

Regarding the second error I reported above ... (Filegroup reference and partitioning scheme not supported...)
I found this at MSDN...looks like you've included a T-SQL statement that is not supported in SQL Azure. Not sure wich one, but here is the list of unsupported statements at MSDN:  http://msdn.microsoft.com/en-us/library/ee336253.aspx

Maybe you could re-write the stored procedures in the 2.7+ versions of the upgrade script to also work with SQL Azure? Pretty please??



Thanks,
Steve

I edited my last post to include information that the developers might be interested in knowing...

embryo wrote:
Hi Steve,

Thanks for reporting. In SQL Azure, we don't have sysindexes or sysindexkeys. Instead, we should use sys.indexes and sys.index_columms. I've just created a work item for this issue

a.m. wrote:

OK, good. From what I read, this is not just in SQL Azure, but also in future releases of MS SQL Server.

I really need to move forward with setting up my site on my new server...is there any way that I could obtain the modified upgrade scripts prior to the next full nopcommerce release?

Thanks,
Steve

a.m. wrote:

OK, good. From what I read, this is not just in SQL Azure, but also in future releases of MS SQL Server.

I really need to move forward with setting up my site on my new server...is there any way that I could obtain the modified upgrade scripts prior to the next full nopcommerce release?

Thanks,
Steve