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

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 < 14)

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

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

/****** Object: StoredProcedure [dbo].[usp_GetWeekDaysRateChart] Script Date: 09/06/2012 15:30:32 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

--**********************************************************************

-- 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 < 14)

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

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

/****** Object: Table [dbo].[TravelSubTravelAgentId] Script Date: 09/06/2012 15:30:36 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

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]

SET ANSI_PADDING OFF

/****** Object: Table [dbo].[TaxTypes] Script Date: 09/06/2012 15:30:36 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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]

/****** 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());

}

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Abhimanyu K Vatsa14y ago
Accepted 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 Ahlawat14y ago
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 Ahlawat14y ago
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 Ahlawat14y ago
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 < 14)
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 < 14)
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 More14y ago
Can you please remove the Go from that query and then try to run from cmd