September 30, 2015 at 12:28 pm
Can someone look over this script and tell me why I'm getting an "Incorrect syntax near 'Go'" error right at the line above the dashes
(about line 18)?
This script works in SSMS, but when compiled using SQL Packager, it errors out.
Andy Evans
ScoreKeyper, Inc.
USE [master]
GO
/****** Object: Database [Sk1] Script Date: 9/29/2015 4:53:36 PM ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'Sk1')
DROP DATABASE Sk1
GO
CREATE DATABASE [Sk1]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Sk1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Sk1.mdf' , SIZE = 12160KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Sk1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Sk1_log.ldf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
ALTER DATABASE [Sk1] SET COMPATIBILITY_LEVEL = 90
GO
---------------
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
EXEC [Sk1].[dbo].[sp_fulltext_database] @action = 'enable'
END
ALTER DATABASE [Sk1] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Sk1] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Sk1] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Sk1] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Sk1] SET ARITHABORT OFF
GO
ALTER DATABASE [Sk1] SET AUTO_CLOSE ON
GO
ALTER DATABASE [Sk1] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [Sk1] SET AUTO_SHRINK OFF
GO
September 30, 2015 at 12:59 pm
andy 61637 (9/30/2015)
Can someone look over this script and tell me why I'm getting an "Incorrect syntax near 'Go'" error right at the line above the dashes(about line 18)?
This script works in SSMS, but when compiled using SQL Packager, it errors out.
Andy Evans
ScoreKeyper, Inc.
USE [master]
GO
/****** Object: Database [Sk1] Script Date: 9/29/2015 4:53:36 PM ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = 'Sk1')
DROP DATABASE Sk1
GO
CREATE DATABASE [Sk1]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'Sk1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Sk1.mdf' , SIZE = 12160KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Sk1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Sk1_log.ldf' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
ALTER DATABASE [Sk1] SET COMPATIBILITY_LEVEL = 90
GO
---------------
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
EXEC [Sk1].[dbo].[sp_fulltext_database] @action = 'enable'
END
ALTER DATABASE [Sk1] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Sk1] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Sk1] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Sk1] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Sk1] SET ARITHABORT OFF
GO
ALTER DATABASE [Sk1] SET AUTO_CLOSE ON
GO
ALTER DATABASE [Sk1] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [Sk1] SET AUTO_SHRINK OFF
GO
Quick suggestion, remove the SSMS specific batch delimiters "GO" and terminate each statement with a semicolon.
😎
This works
USE [master]
GO
/****** Object: Database [Sk1] Script Date: 9/29/2015 4:53:36 PM ******/
DECLARE @SQL_CREATE NVARCHAR(MAX) = N'
IF EXISTS (SELECT name FROM sys.databases WHERE name = ''Sk1'')
DROP DATABASE Sk1;
CREATE DATABASE [Sk1]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N''Sk1'', FILENAME = N''C:\SQLDATA\Sk1.mdf'' , SIZE = 12160KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N''Sk1_log'', FILENAME = N''C:\SQLDATA\Sk1_log.ldf'' , SIZE = 3136KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%);
ALTER DATABASE [Sk1] SET COMPATIBILITY_LEVEL = 100;
IF (1 = FULLTEXTSERVICEPROPERTY(''IsFullTextInstalled''))
BEGIN
EXEC [Sk1].[dbo].[sp_fulltext_database] @action = ''enable'';
END
ALTER DATABASE [Sk1] SET ANSI_NULL_DEFAULT OFF ;
ALTER DATABASE [Sk1] SET ANSI_NULLS OFF ;
ALTER DATABASE [Sk1] SET ANSI_PADDING OFF ;
ALTER DATABASE [Sk1] SET ANSI_WARNINGS OFF ;
ALTER DATABASE [Sk1] SET ARITHABORT OFF ;
ALTER DATABASE [Sk1] SET AUTO_CLOSE ON ;
ALTER DATABASE [Sk1] SET AUTO_CREATE_STATISTICS ON ;
ALTER DATABASE [Sk1] SET AUTO_SHRINK OFF ;
';
EXEC (@SQL_CREATE);
Edit: Typo
September 30, 2015 at 1:22 pm
GO is not a T-SQL command. It's an indicator for Management Studio as to where the batches of commands end. Hence it'll cause errors in anything other than Management Studio
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2015 at 8:05 am
Is there a way to have SSMS generate a create script using ; instead of go ? My create script is huge, quite a lot of editing otherwise. thx.
October 1, 2015 at 8:15 am
andy 61637 (10/1/2015)
Is there a way to have SSMS generate a create script using ; instead of go ? My create script is huge, quite a lot of editing otherwise. thx.
Generate the script, the "find and replace";-)
😎
October 1, 2015 at 8:53 am
You can change the BATCH separator in SSMS by going into options/query Execution/SQL server
Change the Batch separator from GO to ;.
October 1, 2015 at 9:08 am
When setting up client tools for the occasional imperious .net developer I have changed GO
to RESOLVE. it confuses the hell out of them 😀
October 1, 2015 at 9:20 am
Thx. I had thought of that after my post, but now, I'm getting a "Database xxx already exists error". You would probably
need to see my script in order to help, unless its a common kind of thing?
October 1, 2015 at 10:02 am
October 1, 2015 at 10:22 am
andy 61637 (10/1/2015)
Is there a way to have SSMS generate a create script using ; instead of go ? My create script is huge, quite a lot of editing otherwise. thx.
Be careful, those are not the same things.
You're getting the errors because you've got statements that must be the only statement in the batch (like CREATE PROCEDURE) in a batch with other statements, and you've got CREATE statements for objects which exist at the time the batch starts.
Changing the batch terminator to a ; (which is a row terminator in T-SQL, not an indication where to break batches of statements apart) is not a solution. You need to take your original code, with the GO, and break it up into multiple separate batches in whatever tool you're using. Each place SSMS puts a GO must indicate the end of one batch and the beginning of another separate batch of statements. Otherwise you're just going to get errors.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply