Run sqlScript (complete database) giving error with GO statement.

Question

I am executing script for create database.
this script is running fine in SSMS..
and when I execute it..

cmd.ExecuteNonQuery();

it gives error
like
incorrect syntax near GO .......
How can is solve it..?

Abhimanyu K Vatsa · Answer

Have a look at Data Center thread here http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/43e8bc3a-1132-453b-b950-09427e970f31

Vikas Ahlawat · Answer

Yes!! My problem is already solved by using....... using Microsoft.SqlServer.Management.Common; using Microsoft.SqlServer.Management.Smo; namspaces.. Anyway thanx to Abhimanyu and Vikrant for your replies.......

Vikas Ahlawat · Answer

After removing go I am getting ... Incorrect syntax near the keyword 'PROC'. The variable name '@Days' has already been declared. Variable names must be unique within a query batch or stored procedure. The variable name '@DayPart' has already been declared. Variable names must be unique within a query batch or stored procedure. The variable name '@Counter' has already been declared. Variable names must be unique within a query batch or stored procedure. The variable name '@SCRIPT' has already been declared. Variable names must be unique within a query batch or stored procedure.

Vikas Ahlawat · Answer

Hi Vikrant I am posting my code and script(not full) here.. I am storing the script in .txt file the script is I have tried (removed Go) but problem not solved here in two procedure i am using @Day variable it showing that u have already declared it... means it treat whole script as a single query... so what can i do now.. CREATE PROC [dbo].[usp_GetWeekendRateChart] @HotelID AS INT AS BEGIN SET NOCOUNT ON DECLARE @Days AS VARCHAR(400) DECLARE @DayPart AS VARCHAR(400) DECLARE @Counter AS INT -- Counter SET @Counter = 0 -- Days Part SET @DayPart = '' SET @Days = '' -- Making Loop to extract days WHILE (@Counter BEGIN SET @Days = ',[' + (CONVERT(VARCHAR(9), DATEADD(dd, @Counter ,GETDATE()-15), 6)) + ']' SET @DayPart = @DayPart + @Days -- Counter SET @Counter = @Counter + 1 -- Check for last and remove the ' IF (@Counter = 14) BEGIN SET @DayPart = SUBSTRING(@DayPart, 2, LEN(@DayPart)) END END DECLARE @SCRIPT AS VARCHAR(MAX) SET @SCRIPT = 'WITH RESULTSET AS ( SELECT RT.vchRoomType ,SUM (RI.lngWeekendOneAdult) AS [Rate], CONVERT(VARCHAR(12), RI.dtmEntryDate, 6) AS DAY1 FROM RateInformations RI LEFT OUTER JOIN RoomTypes RT ON RI.iRoomTypeId = RT.iRoomTypeID GROUP BY RT.vchRoomType, CONVERT(VARCHAR(12), RI.dtmEntryDate, 6) ) SELECT * FROM (SELECT vchRoomType, vchRoomType AS RoomType,Rate, DAY1 FROM RESULTSET ) AS TPARENT PIVOT (COUNT (Rate) FOR DAY1 IN (' + @DayPart + ')) AS CHILD' EXECUTE (@SCRIPT) SET NOCOUNT OFF END GO /****** Object: StoredProcedure [dbo].[usp_GetWeekDaysRateChart] Script Date: 09/06/2012 15:30:32 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --********************************************************************** -- Author:- Purushottam Kumar -- Date:- 1st FEB 2012 -- Version:- 0.1 -- Last Modified Date:- NONE -- Last Modified By:- NONE -- Description:- To Insert guest details data into the database -- EXEC [usp_GetWeekDaysRateChart] --********************************************************************** CREATE PROC [dbo].[usp_GetWeekDaysRateChart] @HotelID AS INT AS BEGIN SET NOCOUNT ON DECLARE @Days AS VARCHAR(400) DECLARE @DayPart AS VARCHAR(400) DECLARE @Counter AS INT -- Counter SET @Counter = 0 -- Days Part SET @DayPart = '' SET @Days = '' -- Making Loop to extract days WHILE (@Counter BEGIN SET @Days = ',[' + (CONVERT(VARCHAR(9), DATEADD(dd, @Counter ,GETDATE()), 6)) + ']' SET @DayPart = @DayPart + @Days -- Counter SET @Counter = @Counter + 1 -- Check for last and remove the ' IF (@Counter = 14) BEGIN SET @DayPart = SUBSTRING(@DayPart, 2, LEN(@DayPart)) END END DECLARE @SCRIPT AS VARCHAR(MAX) SET @SCRIPT = 'WITH RESULTSET AS ( SELECT RT.vchRoomType ,SUM (RI.lngWeekDayOneAdult) AS [Rate], CONVERT(VARCHAR(9), RI.dtmEntryDate) AS DAY1 FROM RateInformations RI LEFT OUTER JOIN RoomTypes RT ON RI.iRoomTypeId = RT.iRoomTypeID GROUP BY RT.vchRoomType, CONVERT(VARCHAR(9), RI.dtmEntryDate) ) SELECT * FROM (SELECT vchRoomType, vchRoomType AS RoomType,Rate, DAY1 FROM RESULTSET ) AS TPARENT PIVOT (SUM (Rate) FOR DAY1 IN (' + @DayPart + ')) AS CHILD' EXECUTE (@SCRIPT) SET NOCOUNT OFF END GO /****** Object: Table [dbo].[TravelSubTravelAgentId] Script Date: 09/06/2012 15:30:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[TravelSubTravelAgentId]( [iTravelSubTravelAgentId] [bigint] IDENTITY(1,1) NOT NULL, [vchFirstName] [varchar](50) NULL, [vchLastName] [varchar](50) NULL, [vchEmail] [varchar](50) NOT NULL, [vchPassword] [varchar](50) NULL, [vchCountryCode] [varchar](50) NULL, [vchState] [varchar](50) NULL, [vchCity] [varchar](50) NULL, [vchZipCode] [varchar](50) NULL, [vchMobileNo] [varchar](50) NULL, [vchPhone] [varchar](50) NULL, [iPhoneType] [int] NULL, [iParentTravelAgentId] [bigint] NULL, [bActive] [bit] NOT NULL, [dtmEntryDate] [datetime] NULL, [dtmModifyDate] [datetime] NULL, [iUserType] [int] NULL, [vchUnique] [varchar](100) NULL, [vchGlobalID] [varchar](100) NULL, CONSTRAINT [PK_TravelSubTravelAgentId] PRIMARY KEY CLUSTERED ( [iTravelSubTravelAgentId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_TravelSubTravelAgentId_1] UNIQUE NONCLUSTERED ( [vchEmail] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[TaxTypes] Script Date: 09/06/2012 15:30:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[TaxTypes]( [iTaxTypeID] [int] IDENTITY(1,1) NOT NULL, [iHotelID] [int] NOT NULL, [vchTaxType] [nvarchar](50) NULL, [lngTaxPercent] [decimal](5, 2) NULL, [bActive] [bit] NULL, [bDelete] [bit] NULL, [dtmEntryDate] [datetime] NULL, [dtmUpdateDate] [datetime] NULL, CONSTRAINT [PK_TaxTypes] PRIMARY KEY CLUSTERED ( [iTaxTypeID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[TaxType] Script Date: 09/06/2012 15:30:36 ******/ code for that is ... string database = "IXSTAYDBTEST3"; string server, userid, password, auth = string.Empty; bool WindowAuthentication = false; string connString = string.Empty; //Console.WriteLine("Please enter database server information:"); //Console.WriteLine("Server: "); //server = Console.ReadLine(); //while (!(auth == "0" || auth == "1")) //{ // Console.WriteLine("Authentication: 0 = SQL Serever Authentication; 1 = Window Authentication"); // auth = Console.ReadLine(); // if (!(auth == "0" || auth == "1")) // Console.WriteLine("Please put correct value 0 or 1"); //} server = @"127.0.0.1\SQLEXPRESS"; auth = "1"; WindowAuthentication = (auth == "1") ? true : false; if (WindowAuthentication) { connString = "Server=" + server + "; Database = master; Integrated Security=SSPI;"; using (SqlConnection conn = new SqlConnection(connString)) { try { Console.WriteLine("Opening connection ..."); conn.Open(); //ToDo: execute script Console.WriteLine("Reading query ..."); string cmdText = Properties.Resources.IXSTAYDBTEST3.ToString(); SqlCommand cmd = new SqlCommand(cmdText, conn); Console.WriteLine("Please wait! executing query .."); cmd.ExecuteNonQuery(); //server.connection Console.WriteLine("Query executed"); cmd.Dispose(); Console.WriteLine("Closing connection"); conn.Close(); conn.Dispose(); Console.WriteLine("Database created successfully"); } catch (Exception ex) { Console.WriteLine(ex.ToString()); } } }

Vikrant More · Answer

Can you please remove the Go from that query and then try to run from cmd