ExecuteSQL Fails from Variable
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.
