Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Reverse Engineer: Scaffolding fails on Azure Sql Server v11 and SQL 2008 because sequences are not supported #5553

Closed
mhenderson442 opened this issue May 27, 2016 · 22 comments · Fixed by #15438
Assignees
Milestone

Comments

@mhenderson442
Copy link

@mhenderson442 mhenderson442 commented May 27, 2016

Steps to reproduce
Scaffold-DbContext -Provider "Microsoft.EntityFrameworkCore.SqlServer" -Context "DbMercurySchool" -OutputDir "DataModel" -Connection "Server=[Sql Azure Database Server];Data Source=[Sql Azure Database];Initial Catalog=MercurySchool;Persist Security Info=False;User ID=[User ID ];Password=[My Password];Pooling=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"

The issue: SQL error thrown

Error Number:208,State:1,Class:16 One or more errors occurred. (Invalid object name 'sys.sequences'.)

See attached file.

scaffold_error.txt

EF Core

  • version: 1.0.0-rc2-final
  • Operating system: Win 10
  • Visual Studio version: VS 2015 Community

Other details about my project setup:

So far database has one table. The schema for the table is academy not dbo.

@smitpatel

This comment has been minimized.

Copy link
Contributor

@smitpatel smitpatel commented May 27, 2016

Ah Sequence issue. We are trying to get metadata about sequences but not all version of Sql Azure supports sequences so database error.
@mhenderson442 - Can you also tell us details about the server like version of Sql Azure?

@mhenderson442

This comment has been minimized.

Copy link
Author

@mhenderson442 mhenderson442 commented May 27, 2016

The server hosting my database describes the following features:

  • Server Version: V2
  • Extended Transact SQL
  • API Version 12
@ErikEJ

This comment has been minimized.

Copy link
Contributor

@ErikEJ ErikEJ commented May 28, 2016

Do a SELECT @@Version

@ErikEJ

This comment has been minimized.

Copy link
Contributor

@ErikEJ ErikEJ commented May 28, 2016

@lajones - this check is not correct for Azure SQL database:

 private bool SupportsSequences => _serverVersion?.Major >= 11;

 private bool SupportsSequences => _serverVersion?.Major >= 11 || (IsAzureSqlDatabase && _serverVersion?.Major >= 12;

Probably need IsAzureSqlDatabase (SERVERPROPERTY('EngineEdition') = 5) in the class as well

 DECLARE @sqlVersion numeric(4,2)
 SELECT @sqlVersion = LEFT(CAST(SERVERPROPERTY('productversion') as VARCHAR), 4)
 IF (SERVERPROPERTY('EngineEdition') = 5 AND @sqlVersion < 12) OR (@sqlVersion < 11)
 -- No sys.sequences   
@mhenderson442

This comment has been minimized.

Copy link
Author

@mhenderson442 mhenderson442 commented May 28, 2016

SELECT @@VERSION outputs the following:

Microsoft SQL Azure (RTM) - 11.0.9231.349
May 20 2016 14:01:37
Copyright (c) Microsoft Corporation

SERVERPROPERTY('EngineEdition') = 5

SERVERPROPERTY('productversion') = 11.0

Off in the weeds:
Interesting that what I have does not support sequences. It was my understanding that SQL Azure supports sequences (reference). I don't have a need for them, but thought there was feature parity with 2012.

@ErikEJ

This comment has been minimized.

Copy link
Contributor

@ErikEJ ErikEJ commented May 28, 2016

@mhenderson442 Azure SQL database has version 11 and 12, it is possible to update from 11 to 12: https://azure.microsoft.com/da-dk/documentation/articles/sql-database-upgrade-server-portal/

@mhenderson442

This comment has been minimized.

Copy link
Author

@mhenderson442 mhenderson442 commented May 28, 2016

@ErikEJ Just saw that I can upgrade and am doing so.

@ErikEJ

This comment has been minimized.

Copy link
Contributor

@ErikEJ ErikEJ commented May 28, 2016

@mhenderson442 Great idea, that should unblock you!

@mhenderson442

This comment has been minimized.

Copy link
Author

@mhenderson442 mhenderson442 commented May 28, 2016

Upgrading to to version 12 unblocked me and I successfully executed Scaffold-DbContext.

@smitpatel @ErikEJ Should this issue be closed, or leave it open till the version 11 scenario is addressed?

@ErikEJ

This comment has been minimized.

Copy link
Contributor

@ErikEJ ErikEJ commented May 28, 2016

As long as Azure supports v11 databases, I vote for a bug fix

@smitpatel

This comment has been minimized.

Copy link
Contributor

@smitpatel smitpatel commented May 28, 2016

As as @ErikEJ said, since there are Azure database v11 out there, EF should not fail like that and have better check for server version. Leave this issue open please.

@smitpatel smitpatel changed the title SQL Error thrown executing Scaffold-DbContext (Invalid object name 'sys.sequences'.). RevEng: Scaffolding fails on Azure Sql Server v11 because sequences are not supported May 28, 2016
@mhenderson442

This comment has been minimized.

Copy link
Author

@mhenderson442 mhenderson442 commented May 28, 2016

Agreed. I will leave open.

@rowanmiller rowanmiller added this to the 1.0.1 milestone May 31, 2016
@rowanmiller rowanmiller modified the milestones: 1.0.1, 1.1.0 Jul 1, 2016
@rowanmiller rowanmiller removed the pri0 label Jul 6, 2016
@rowanmiller rowanmiller changed the title RevEng: Scaffolding fails on Azure Sql Server v11 because sequences are not supported Reverse Engineer: Scaffolding fails on Azure Sql Server v11 because sequences are not supported Jul 21, 2016
@rowanmiller rowanmiller modified the milestones: 1.1.0, 1.0.1 Jul 29, 2016
@rowanmiller rowanmiller changed the title Reverse Engineer: Scaffolding fails on Azure Sql Server v11 because sequences are not supported Reverse Engineer: Scaffolding fails on Azure Sql Server v11 and SQL 2008 because sequences are not supported Oct 6, 2016
@rowanmiller rowanmiller modified the milestones: 1.2.0, 1.1.0-preview1 Oct 6, 2016
@markti

This comment has been minimized.

Copy link

@markti markti commented Feb 28, 2017

@mhenderson442 how did you get the scaffolding output log? all I get is "build failed."

@markti

This comment has been minimized.

Copy link

@markti markti commented Feb 28, 2017

Guys I get this issue and have verified my Azure SQL DB is version 12. Anybody know how to turn on verbose logging for Scaffolding-DbContext?

@mhenderson442

This comment has been minimized.

Copy link
Author

@mhenderson442 mhenderson442 commented Feb 28, 2017

@markti I don't recall as it was more than a year ago I wrestled with this issue.

@smitpatel

This comment has been minimized.

Copy link
Contributor

@smitpatel smitpatel commented Feb 28, 2017

@markti - Use -Verbose flag in PMC.

@smitpatel

This comment has been minimized.

Copy link
Contributor

@smitpatel smitpatel commented Aug 30, 2017

We already do sequences conditionally, Does this still fail?

@ErikEJ

This comment has been minimized.

Copy link
Contributor

@ErikEJ ErikEJ commented Aug 30, 2017

Yes, this is still an issue, if you wish to continue to support Azure SQL Database v11, which has been retired: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-faq - othewise close

See my comment above: #5553 (comment)

@smitpatel

This comment has been minimized.

Copy link
Contributor

@smitpatel smitpatel commented Aug 31, 2017

Thanks @ErikEJ - I missed that condition was incorrect. Probably should just fix this if we plan to support.

@ajcvickers ajcvickers closed this Apr 19, 2018
@ajcvickers ajcvickers removed this from the Backlog milestone Apr 19, 2018
@symbiogenesis

This comment has been minimized.

Copy link

@symbiogenesis symbiogenesis commented Aug 7, 2018

Azure Data Warehouse uses a different version numbering scheme. The latest version at this date is 10.0.9999.0.

Maybe it should check the compatibility level, rather than the version number. The compatibility level is 130, which is probably fine.

Edit: looks like Azure Data Warehouse doesn't currently support sequences. Thus, the compatibility level would not actually detect this. Ideally, a real feature detection would be attempted, such as seeing if a query on sys.sequences returns anything.

Here's my attempt

symbiogenesis@b8d8147

@smitpatel smitpatel reopened this Aug 8, 2018
@ajcvickers

This comment has been minimized.

Copy link
Member

@ajcvickers ajcvickers commented Aug 28, 2018

Triage: we should try to not need to check for whether a feature is available, or if we do we should find the most appropriate way to use version number, compatibility level, or whatever else to determine what is supported.

@ajcvickers ajcvickers added this to the Backlog milestone Aug 28, 2018
ErikEJ added a commit to ErikEJ/EntityFramework that referenced this issue Apr 22, 2019
Use compatibility_level for feature detection
Unblock scaffolding on Azure SQL DW

fixes aspnet#14566
fixes aspnet#5553
@bricelam bricelam added closed-fixed and removed propose-close labels Apr 22, 2019
@bricelam bricelam modified the milestones: Backlog, 3.0.0 Apr 22, 2019
@bricelam bricelam self-assigned this Apr 22, 2019
bricelam added a commit that referenced this issue Apr 22, 2019
Use compatibility_level for feature detection
Unblock scaffolding on Azure SQL DW

fixes #14566
fixes #5553
@ajcvickers ajcvickers modified the milestones: 3.0.0, 3.0.0-preview5 Apr 22, 2019
@ajcvickers ajcvickers modified the milestones: 3.0.0-preview5, 3.0.0 Nov 11, 2019
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
9 participants
You can’t perform that action at this time.