2

I am trying to set up my SQL Server Express database on my hosting site (GoDaddy/Plesk) as a full-fledged SQL Server DB, but have run into a series of error messages running the script.sql file.

I am following the steps here to set up my DB, first by running my script.sql file inside MSSMS while connected to the remote server.

I had been having trouble connecting, but was finally able to by leaving out the port number, as recommended by seanb here:

enter image description here

...but I got these err msgs on running the script file:

enter image description here

Is this because my DB is SQL Server Express, and it's trying to create a SQL Server (standard/regular) DB, and 'OPTIMIZE_FOR_SEQUENTIAL_KEY' is foreing to "regular" SQL Server/TSQL?

If so (or in any case), what do I need to do to get the sql script to run?

For a little more detail, all of the complaints are "'OPTIMIZE_FOR_SEQUENTIAL_KEY' is not a recognized CREATE TABLE option." except for one, which is:

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

As for the "OPTIMIZE_FOR_SEQUENTIAL_KEY" problem, here is the first place where that is complained about:

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],

...and as for "Incorrect syntax near the keyword 'with'", here is where it occurs:

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

In other words, it's the same exact TSQL, but being applied to different columns, and causing different err msgs - the first one complaining about "OPTIMIZE" and the second one simply vaguely complaining about something near "WITH"

Here they are in context:

[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [CK_ACTORS_Column] UNIQUE NONCLUSTERED 
(
    [ActorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I'm tempted to simply remove all of the "OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF" statements and try it again; would it be safe to do that?

B. Clay Shannon-B. Crow Raven
  • 8,547
  • 144
  • 472
  • 862

1 Answers1

2

Your target SQL Server is simply an older version. It's SQL Server 2014 SP3 CU4 to be exact, and OPTIMIZE_FOR_SEQUENTIAL_KEY is a new feature in SQL Server 2019. SSMS scripting always recites all the options, even when set to the default value to ensure full fidelity scripting, which sometimes a pain.

You can simply remove that option and the script should be compatible with SQL 2014. In fact you can remove all those options if you haven't set any to a non-default value.

Or see if your hoster has SQL Server 2019 available. Or upgrade your hoster :)

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67