Execute SQL script from asp.net application

Question

I have a web service application that generates script to create stored procedures.  Each time the script runs, it checks to see if the stored procedure is already there and renames is with a "__FLAG_DELETE_ME" suffix.  This way I can temporarily archive the stored procedure incase it had custom code in it that needed to be saved.  Then the script creates a new version of the stored procedure.

This works fine when I execute it from SSMS.  but now i want to run it from the asp.net application and I get exceptions such as:

"Incorrect syntax near 'GO'."

or 

"'CREATE/ALTER PROCEDURE' must be the first statement in a query batch."

the way I try to execute this from wpf is I pass the script into a stored procedure which then attempts to execute it like this:

EXEC(@SqlScript)

Can some one please tell me how this can be accomplished?

Thank You.

Answer 1

OK I found part of the problem.  I can't execute the script from within another stored procedure.  So from c# I can create a command of type Text and assign the script to the commandtext.  then execute the command:

cmd.ExecuteNonQuery();

But I still get exceptions in sql server when the script executes.  the error message is:

Additional information: Incorrect syntax near 'GO'.

Must declare the scalar variable "@AstSys_Prj_Id".

and here's the script:

-- ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   
-- ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   

--==========================================================================================================================================
--==========================================================================================================================================
-- spAssetSystem_lst
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[spAssetSystem_lst]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	BEGIN --1
	 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[spAssetSystem_lst__FLAG_DELETE_ME]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
		 BEGIN --2
			 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[spAssetSystem_lst__FLAG_DELETE_ME__FLAG_DELETE_ME]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
				 BEGIN --3
					 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[spAssetSystem_lst__FLAG_DELETE_ME__FLAG_DELETE_ME__FLAG_DELETE_ME]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
						 BEGIN --4
							 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[spAssetSystem_lst__FLAG_DELETE_ME__FLAG_DELETE_ME__FLAG_DELETE_ME__FLAG_DELETE_ME]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
								 BEGIN --5
									 DROP PROCEDURE [spAssetSystem_lst__FLAG_DELETE_ME]
									 EXEC sp_rename 'spAssetSystem_lst', 'spAssetSystem_lst__FLAG_DELETE_ME'
								 END
							 ELSE
								 BEGIN
									 EXEC sp_rename 'spAssetSystem_lst', 'spAssetSystem_lst__FLAG_DELETE_ME__FLAG_DELETE_ME__FLAG_DELETE_ME__FLAG_DELETE_ME'
								 END --5
						 END
					 ELSE
						 BEGIN
							 EXEC sp_rename 'spAssetSystem_lst', 'spAssetSystem_lst__FLAG_DELETE_ME__FLAG_DELETE_ME__FLAG_DELETE_ME'
						 END --4
				 END
			 ELSE
				 BEGIN
					 EXEC sp_rename 'spAssetSystem_lst', 'spAssetSystem_lst__FLAG_DELETE_ME__FLAG_DELETE_ME'
				 END --3
		 END
	 ELSE
		 BEGIN
			 EXEC sp_rename 'spAssetSystem_lst', 'spAssetSystem_lst__FLAG_DELETE_ME'
		 END --2
	 END
GO
CREATE PROCEDURE [spAssetSystem_lst]
-- Script for this SP was codegenned on: 1/19/2018 10:26:53 PM
(
@AstSys_Prj_Id uniqueidentifier
)
AS
SET NOCOUNT ON

SELECT 	
AstSys_Id,
AstSys_Prj_Id,
AttachmentCount,
AttachmentFileNames,
DiscussionCount,
DiscussionTitles,
AstSys_SysNo,
AstSys_Name,
AstSys_Desc,
AstSys_AstSysTp_Id,
AstSysTp_Name AS AstSys_AstSysTp_Id,

AstSys_WlInfDgn_Id,
WlInfDgn_Name AS AstSys_WlInfDgn_Id,

AstSys_Un_Id,
Un_Name AS AstSys_Un_Id,

AstSys_AstSysPdTp_Id,
AstSysPdTp_Name AS AstSys_AstSysPdTp_Id,

AstSys_AstSysPsr_Id,
AstSysPsr_Name AS AstSys_AstSysPsr_Id,

AstSys_AstSysOpSt_Id,
AstSysOpSt_Name AS AstSys_AstSysOpSt_Id,

AstSys_Ent_Id,
Ent_Name AS AstSys_Ent_Id,

AstSys_Remarks1,
AstSys_Remarks2,
AstSys_IsActiveRow,
AstSys_IsDeleted,
AstSys_CreatedUserId,
AstSys_CreatedDate,
AstSys_UserId,
dbo.udf_FormatPersonName_FL(Pn_FName, Pn_LName) AS UserName,

AstSys_LastModifiedDate,
AstSys_Stamp

FROM 		tbAssetSystem LEFT OUTER JOIN
                tbPerson ON tbAssetSystem.AstSys_UserId = tbPerson.Pn_SecurityUserId LEFT OUTER JOIN
                tbAssetSystemType ON tbAssetSystem.AstSys_AstSysTp_Id = tbAssetSystemType.AstSysTp_Id LEFT OUTER JOIN
                tbWellInfrastructureDesignation ON tbAssetSystem.AstSys_WlInfDgn_Id = tbWellInfrastructureDesignation.WlInfDgn_Id LEFT OUTER JOIN
                tbUnit ON tbAssetSystem.AstSys_Un_Id = tbUnit.Un_Id LEFT OUTER JOIN
                tbAssetSystemProductType ON tbAssetSystem.AstSys_AstSysPdTp_Id = tbAssetSystemProductType.AstSysPdTp_Id LEFT OUTER JOIN
                tbAssetSystemPressure ON tbAssetSystem.AstSys_AstSysPsr_Id = tbAssetSystemPressure.AstSysPsr_Id LEFT OUTER JOIN
                tbAssetSystemOperatingStatus ON tbAssetSystem.AstSys_AstSysOpSt_Id = tbAssetSystemOperatingStatus.AstSysOpSt_Id LEFT OUTER JOIN
                tbEntity ON tbAssetSystem.AstSys_Ent_Id = tbEntity.Ent_Id

WHERE   (AstSys_Prj_Id = @AstSys_Prj_Id) AND (AstSys_IsDeleted = 0)
ORDER BY  AstSys_Name

GO

Thank you for any help.

Answer 2

"Incorrect syntax near 'GO'."

Hello,

GO isn't a valid SQL command, it's a batch separator interpreted by tools like SSMS and SqlCmd; see SQL Server Utilities Statements - GO

You either have to parse your script for the GO, split the script into parts, remove the GO and execute each SQL part one by one; or split the script as it.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 3

OK I found part of the problem.  I can't execute the script from within another stored procedure.  So from c# I can create a command of type Text and assign the script to the commandtext.  then execute the command:

cmd.ExecuteNonQuery();

But I still get exceptions in sql server when the script executes.  the error message is:

Additional information: Incorrect syntax near 'GO'.

Must declare the scalar variable "@AstSys_Prj_Id".

and here's the script:

-- ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   
-- ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   ***   X   

--==========================================================================================================================================
--==========================================================================================================================================
-- spAssetSystem_lst
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[spAssetSystem_lst]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	BEGIN --1
	 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[spAssetSystem_lst__FLAG_DELETE_ME]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
		 BEGIN --2
			 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[spAssetSystem_lst__FLAG_DELETE_ME__FLAG_DELETE_ME]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
				 BEGIN --3
					 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[spAssetSystem_lst__FLAG_DELETE_ME__FLAG_DELETE_ME__FLAG_DELETE_ME]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
						 BEGIN --4
							 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'[spAssetSystem_lst__FLAG_DELETE_ME__FLAG_DELETE_ME__FLAG_DELETE_ME__FLAG_DELETE_ME]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
								 BEGIN --5
									 DROP PROCEDURE [spAssetSystem_lst__FLAG_DELETE_ME]
									 EXEC sp_rename 'spAssetSystem_lst', 'spAssetSystem_lst__FLAG_DELETE_ME'
								 END
							 ELSE
								 BEGIN
									 EXEC sp_rename 'spAssetSystem_lst', 'spAssetSystem_lst__FLAG_DELETE_ME__FLAG_DELETE_ME__FLAG_DELETE_ME__FLAG_DELETE_ME'
								 END --5
						 END
					 ELSE
						 BEGIN
							 EXEC sp_rename 'spAssetSystem_lst', 'spAssetSystem_lst__FLAG_DELETE_ME__FLAG_DELETE_ME__FLAG_DELETE_ME'
						 END --4
				 END
			 ELSE
				 BEGIN
					 EXEC sp_rename 'spAssetSystem_lst', 'spAssetSystem_lst__FLAG_DELETE_ME__FLAG_DELETE_ME'
				 END --3
		 END
	 ELSE
		 BEGIN
			 EXEC sp_rename 'spAssetSystem_lst', 'spAssetSystem_lst__FLAG_DELETE_ME'
		 END --2
	 END
GO
CREATE PROCEDURE [spAssetSystem_lst]
-- Script for this SP was codegenned on: 1/19/2018 10:26:53 PM
(
@AstSys_Prj_Id uniqueidentifier
)
AS
SET NOCOUNT ON

SELECT 	
AstSys_Id,
AstSys_Prj_Id,
AttachmentCount,
AttachmentFileNames,
DiscussionCount,
DiscussionTitles,
AstSys_SysNo,
AstSys_Name,
AstSys_Desc,
AstSys_AstSysTp_Id,
AstSysTp_Name AS AstSys_AstSysTp_Id,

AstSys_WlInfDgn_Id,
WlInfDgn_Name AS AstSys_WlInfDgn_Id,

AstSys_Un_Id,
Un_Name AS AstSys_Un_Id,

AstSys_AstSysPdTp_Id,
AstSysPdTp_Name AS AstSys_AstSysPdTp_Id,

AstSys_AstSysPsr_Id,
AstSysPsr_Name AS AstSys_AstSysPsr_Id,

AstSys_AstSysOpSt_Id,
AstSysOpSt_Name AS AstSys_AstSysOpSt_Id,

AstSys_Ent_Id,
Ent_Name AS AstSys_Ent_Id,

AstSys_Remarks1,
AstSys_Remarks2,
AstSys_IsActiveRow,
AstSys_IsDeleted,
AstSys_CreatedUserId,
AstSys_CreatedDate,
AstSys_UserId,
dbo.udf_FormatPersonName_FL(Pn_FName, Pn_LName) AS UserName,

AstSys_LastModifiedDate,
AstSys_Stamp

FROM 		tbAssetSystem LEFT OUTER JOIN
                tbPerson ON tbAssetSystem.AstSys_UserId = tbPerson.Pn_SecurityUserId LEFT OUTER JOIN
                tbAssetSystemType ON tbAssetSystem.AstSys_AstSysTp_Id = tbAssetSystemType.AstSysTp_Id LEFT OUTER JOIN
                tbWellInfrastructureDesignation ON tbAssetSystem.AstSys_WlInfDgn_Id = tbWellInfrastructureDesignation.WlInfDgn_Id LEFT OUTER JOIN
                tbUnit ON tbAssetSystem.AstSys_Un_Id = tbUnit.Un_Id LEFT OUTER JOIN
                tbAssetSystemProductType ON tbAssetSystem.AstSys_AstSysPdTp_Id = tbAssetSystemProductType.AstSysPdTp_Id LEFT OUTER JOIN
                tbAssetSystemPressure ON tbAssetSystem.AstSys_AstSysPsr_Id = tbAssetSystemPressure.AstSysPsr_Id LEFT OUTER JOIN
                tbAssetSystemOperatingStatus ON tbAssetSystem.AstSys_AstSysOpSt_Id = tbAssetSystemOperatingStatus.AstSysOpSt_Id LEFT OUTER JOIN
                tbEntity ON tbAssetSystem.AstSys_Ent_Id = tbEntity.Ent_Id

WHERE   (AstSys_Prj_Id = @AstSys_Prj_Id) AND (AstSys_IsDeleted = 0)
ORDER BY  AstSys_Name

GO

Thank you for any help.

Can you wrap all under a single procedure and then try executing?

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

Answer 4

OK, parsing it into separate batches works.

Thanks.

Answer 5

OK, parsing it into separate batches works.

Thanks.

Hi moondaddy,

Since your problem has been resolved, please close the thread by marking the useful reply as answer. Your contribution is highly appreciated.

Best Regards,

Will

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].