IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'WTemplate')
BEGIN
ALTER DATABASE [WTemplate] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [WTemplate]
END
GO
CREATE DATABASE WTemplate ON PRIMARY
( NAME = N'WTemplate', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'WTemplate_log', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @.dbname=N'WTemplate', @.new_cmptlevel=90
GO
EXEC WTemplate.[dbo].[sp_fulltext_database] @.action = 'disable'
GO
ALTER DATABASE WTemplate SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE WTemplate SET ANSI_NULLS OFF
GO
ALTER DATABASE WTemplate SET ANSI_PADDING OFF
GO
ALTER DATABASE WTemplate SET ANSI_WARNINGS OFF
GO
ALTER DATABASE WTemplate SET ARITHABORT OFF
GO
ALTER DATABASE WTemplate SET AUTO_CLOSE OFF
GO
ALTER DATABASE WTemplate SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE WTemplate SET AUTO_SHRINK OFF
GO
ALTER DATABASE WTemplate SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE WTemplate SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE WTemplate SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE WTemplate SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE WTemplate SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE WTemplate SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE WTemplate SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE WTemplate SET RECOVERY FULL
GO
ALTER DATABASE WTemplate SET MULTI_USER
GO
ALTER DATABASE WTemplate SET PAGE_VERIFY CHECKSUM
GO
If this is formed inside a script task, assigned to a variable and then executed from the variable it fails. If I past it into the Execute SQL Task as a string it succeeds. Any ideas on where the difference may be? I have set a breakpoint and verified that the variable is being filled in correctly.
I get this error:
SSIS package "BuildTemplates.dtsx" starting.
Error: 0x0 at Create Database: Incorrect syntax near the keyword 'CREATE'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0x0 at Create Database: Incorrect syntax near 'GO'.
Error: 0xC002F210 at Create Database, Execute SQL Task: Executing the query "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'WTemplate')
BEGIN
ALTER DATABASE [WTemplate] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [WTemplate]
END
GO
CREATE DATABASE WTemplate ON PRIMARY
( NAME = N'WTemplate', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'WTemplate_log', FILENAME = N'D:\MSSQL\MSSQL.1\MSSQL\DATA\WTemplate_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @.dbname=N'WTemplate', @.new_cmptlevel=90
GO
EXEC WTemplate.[dbo].[sp_fulltext_database] @.action = 'disable'
GO
ALTER DATABASE WTemplate SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE WTemplate SET ANSI_NULLS OFF
GO
ALTER DATABASE WTemplate SET ANSI_PADDING OFF
GO
ALTER DATABASE WTemplate SET ANSI_WARNINGS OFF
GO
ALTER DATABASE WTemplate SET ARITHABORT OFF
GO
ALTER DATABASE WTemplate SET AUTO_CLOSE OFF
GO
ALTER DATABASE WTemplate SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE WTemplate SET AUTO_SHRINK OFF
GO
ALTER DATABASE WTemplate SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE WTemplate SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE WTemplate SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE WTemplate SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE WTemplate SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE WTemplate SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE WTemplate SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE WTemplate SET RECOVERY FULL
GO
ALTER DATABASE WTemplate SET MULTI_USER
GO
ALTER DATABASE WTemplate SET PAGE_VERIFY CHECKSUM
GO
" failed with the following error: "Incorrect syntax near 'GO'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I suspect what is going on is that the \ in the paths need to be doubled. I did that and the problem appeared to go away.Thanks,
