so I tried using ExecuteNonequery method. it still failed: Here is my test
code
try
{
if(con.State !=ConnectionState.Open)
{
con.Open();
}
int i=cmd.ExecuteNonQuery();
return true;
}
catch(SqlException sql)
{
MessageBox.Show(sql.Message);
return false;
}
Here is a snippit of one of the SQL script:
DECLARE @CORE_CORE3572301500_release_exists INT
EXEC @CORE_CORE3572301500_release_exists = sp_COMXVerifyPatch
'3',
'5',
'723',
'CORE',
NULL,
0,
'15',
'00'
IF @CORE_CORE3572301500_release_exists > 0
BEGIN
PRINT 'Maintenance Release 3.5.723.01500 has already been applied.'
RETURN
END
PRINT 'BEGIN: Maintenance Release - Schema'
PRINT ''
PRINT 'X:\Db\Schema\Tables\Change\bill_wrk_attr_prc_plan_chg.sql
15716(myurkus)'
; <--------------GO REPLACE HERE
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.bill_wrk_attr_prc_plan_chg ADD
is_recalculate bit NOT NULL CONSTRAINT
DF_bill_wrk_attr_prc_plan_chg_is_recalculate DEFAULT 0
;<--------------GO REPLACE HERE
COMMIT
PRINT 'X:\Db\Schema\Tables\Change\bill_wrk_invoice_portfolio.sql
15716(myurkus)'
;<--------------GO REPLACE HERE
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.bill_wrk_invoice_portfolio ADD
override_price money NULL
;<--------------GO REPLACE HERE
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[acport_upd_portfolioChangeHistory]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[acport_upd_portfolioChangeHistory]
;<--------------GO REPLACE HERE
CREATE Procedure acport_upd_portfolioChangeHistory ------CODE BOMBS OUT WHEN
IT GETS TO CREATE PROCEDURE
SO someone tell me what am i doing wrong, I'm talking directly to SQL Server
using ADO .NET
Sorry for being such a pain but you guys seem so convince that it works...
and I fee like a dumb ass. Show me Light.
Cliff
Joe Fallon said:
You can't do that in Query Analyzer.
1. QA expects the GO keyword. It is a feature in *QA*!!!!
QA uses it to parse the batch.
2. SQL Server takes the parsed batches and processes them.
So when you are NOT using QA - and you ARE talking directly to SQL Server
use the ; as the batch separator.
--
Joe Fallon
CliffNews said:
I forgot to mention one other thing, I actually replace 'GO' and tried to
run the script in Query Analyzer and got the same error as well.
Joe Fallon said:
I think the suggestion was to replace GO with ; everywhere in the file.
This will send multiple SQL statements to SQL Server which has the ability
to run them consecutively.
So it should work.
Some other DBs don't have this ability.
--
Joe Fallon
Thanks for the quick reply Val. I'm not sure if I quite follow you. Are
you
saying for each statement in my batch I should terminate it with a (
.
e.g
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response];
create procedure [dbo].[delete_datafeed_response]
@day_delete_interval
int
as
begin
set nocount on
declare @error int, @message varchar(200), @rows_deleted int;
select @error = 0;
if @day_delete_interval > 0
begin
DELETE FROM [delete_datafeed_response];
WHERE [status_code] = 'CLOSED';
AND (DATEDIFF(d, [modify_date], getdate()) >
@day_delete_interval);
end;
I'm not sure if this is what you mean (that is putting semi-colon at the
end
of each statement). If so that will be a pain considering the procs that
i'm
executing can be rather huge in upwards of 4 megs. Streaming through these
files and putting (
at the end of each line will be pain.
Hi,
Since GO is not a valid SQL statement, but batch statement, you cannot
use
it in your batch. What you could do in this case is to separate your
statements inside of the batch, suing semicolon (
. In this case OLEDB
provider for SQL Server will recognize each statement properly and will
execute them separately. But it works with SQL Server provider. Some
other
providers do not support this feature
--
Val Mazur
Microsoft MVP
Hi all,
I'm trying to a automate the execution of a stored procedure
from
a
file.
The problem is when I remove the 'GO' command, I get "'CREATE
PROCEDURE'
must be the first statement in a query batch." Is there a way around
this??
I would hate to have to parse my "if exist" statement and run that
separately. And I really don't want to shell out and use sol server
command
line tool.
code snippet:
if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[delete_datafeed_response]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[delete_datafeed_response]
GO
create procedure [dbo].[delete_datafeed_response] @day_delete_interval
int
as
begin
Thanks
