How can I execute DDL?

[goodmorningsky]

I want to execute DDL sql query.

I tried following query

 

strsql ="

CREATE PROCEDURE spTEST

@UserID varchar(6)

AS

DELETE tblPayData

WHERE UserID = @UserID

GO"

 

SqlCommand cmd= new SqlCommand(strSql, cnnection);

cmd.ExecuteNonQuery();

 

It gives error.

I wonder the ExecuteNonQuery method can execute DDL?

if not what API should I use?

 

And to execute batch queries such as

 

DROP PROCEDURE spTest

GO

Create procedure spTest

...

GO

..

GO

 

The app works like Query analyzer on Sql server2000.

 

Any comment will help!!

[PlausiblyDamp]

When you say it gaives an error could you be more specific? What error does it give? Does it give a line number? Have you properly initalised your command and connection objects (and remembered to open the connection - I always forget that one).

[goodmorningsky]

Thank your for reply.

Here are the methods.

 

// When Execute button is clicked, 
private void btExec_Click(object sender, System.EventArgs e)
{
ComponentImplementation.DBHandler dbH = new ComponentImplementation.DBHandler();
Cursor.Current = Cursors.WaitCursor;
DataTable depts = this.deptls.Department; //ipaddrs
query = this.txtQuery.Text.Trim();
strLog="";
string strSql=query.Replace(nl, "  ") ;

if(strSql.Length < 1) 
{
	MessageBox.Show ("No query found!");
	return;
}

int suc =0, fail = 0; 
this.progressBar1.Maximum = depts.Rows.Count;
foreach(DataRow row in depts.Rows)
{
	try
	{				
		this.lbProgress.Text  = "Excuting on Department : " + (string)row["DepartmentName"];
		string ip =(string) row["DBIPAddr"];
		dbH.ExecuteNonQuery_Local(strSql, ComponentImplementation.DBHandler.GetDBDeptConnectionString(ip));
		suc ++;
	}
	catch(Exception ex)
	{
		fail++;
		strLog = "-------------------------------------------------------------------------------------------------------------------" + nl +
						" Department: " + (string)row["DepartmentName"]  + nl +
						ex.Message  + nl;
	}
	finally
	{
		this.progressBar1.Value = suc + fail;
	}
}
this.lbProgress.Text  = "Done.  Sucess :" + suc + ",  fail : " + fail;
Cursor.Current = Cursors.Default;
}



// this method is called to execute query.
public void ExecuteNonQuery_Local(string strSql, SqlConnection cnn)
{
bool openhere=false;
SqlCommand cmd= new SqlCommand(strSql, cnn);
try
{				
	if(cmd.Connection.State == ConnectionState.Closed)
	{
		openhere=true;
		cmd.Connection.Open();
	}
	cmd.ExecuteNonQuery();
	if(openhere)
		cmd.Connection.Close();
}
catch(Exception ex)
{
	if(openhere && cmd.Connection.State == ConnectionState.Open)
		cmd.Connection.Close();
	
	throw ex;
	
}
}

and the log returned is

-------------------------------------------------------------------------------------------------------------------

Department: IT

Line 1: Incorrect syntax near 'GO'.

 

 

Any comment will help!

[PlausiblyDamp]

Can't tell what the SQL code looks like from your posting but the error message indicates a syntax error. Do you have the keyword 'GO' in the SQL you are trying to execute by any chance? If so that is the problem, GO is not a SQL keyword - it is really an instruction to the SQL tools (Query analyzer, isql and osql).

If you remove the word GO does that fix the problem? If not could you post the SQL that is being executed and any new error messages that may be returned.

[goodmorningsky]

Thank your for reply.

It's not issue of T-SQL!!!

The query works perfect on Query Analyze.

 

The problem is related to ADO.NET, SqlCommand.ExecuteNonquery, i think!.

 

ExecuteNonquery works fine with basic sql, select, update, insert and simple DDL DROP Procedure spTest GO.

[PlausiblyDamp]

Like I said above the word GO is supported by Query Analyzer but isn't a valid SQL keyword - it will not work if you try to use it in code being executed from a SqlCommand

[goodmorningsky]

Thank you for reply.

You r right.

I think Query analyzer parse all query by spliting with 'GO'

and execute each one by one.