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 RoomTypeRate 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 RoomTypeRate 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(11) 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(11) 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