Create table with CATALOG_COLLATION fails with syntax error (Copy Azure database to local development SQL Server)

2
votes

SQL Server Management Studio generated a script to create database with schema only. Original database is an Azure SQL database. 

USE [master]
GO

CREATE DATABASE [library-production]
  WITH CATALOG_COLLATION = SQL_Latin1_General_CP1_CI_AS
GO

-- Other commands

When generated script is executed in the local database (Microsoft SQL Server 2017 Developer (64-bit)), execution fails with an error:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '='.

I am trying to copy azure database to the local, how I can create database on local instance with script generated from Azure SQL Database?

1
sql-serverazure-sql-databasessms

1 Answers

2
votes

WITH CATALOG_COLLATION = SQL_Latin1_General_CP1_CI_AS is an option of Azure Database.

For SQL Server you can use this statement:

USE [master]
GO

CREATE DATABASE [library-production]
  COLLATE SQL_Latin1_General_CP1_CI_AS
GO