Cant set database options

Question

I'd like to try Moodle on Azure SQL, but it needs QUOTED_IDENTIFIER ON, ANSI_NULLS ON, and READ_COMMITTED_SNAPSHOT ON.

When i try to set the options i get the following message.

use master
ALTER DATABASE [TestDB01] SET QUOTED_IDENTIFIER ON

Msg 40510, Level 16, State 1, Line 2
Statement 'ALTER DATABASE' is not supported in this version of SQL Server.

Any workarounds or plans for this to be acheiveable in the future?

Answer 1

Hi TeeJaa,

According to the error ALTER DATABASE statement is not be supported. Both ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT options will be set to ON by default and users will not be able to change them.

But if you want to set  ANSI_NULLS ON, which is supported in SQL Azure. You can test it with sample code : CREATE TABLE [dbo].[t1](
[a] [int] NULL
)
And then right click the table -> Script Table as -> Create to… And then I can see the option SET ANSI_NULLS ON.

For more information, please refer to here and this blog about SET QUOTED_IDENTIFIER ON/OFF and SET ANSI_NULLS ON/OFF in SQL Azure

 

---

Regards, Amber zhang

Answer 2

Hi Amber

Thanks, I will give it a try as soon as I can get a database created, seem to not be working right now...

TeeJaa

Answer 3

Hi TeeJaa,
  Thank you for your update.
  Could you create database? Once you created databse, you can follow the steps as below to set options.

---

Regards, Amber zhang