none
Multiple SQL Create Table queries in one SQLCommand object ? RRS feed

  • Question

  • Hi ,

    I want to execute multiple SQL Create table queries in one ADO.NET SQLCommand run. But its not working , If i run the same SQL queires in SQL Server Query editor , all the required tables are created successfully.
    So is there any workaround to run multiple SQL Queries in one SQLCommand object?

    I have tired the approach mentioned here (http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/29b1f5d1-9b9b-478e-8d09-78f712f52a03), but didn't work !!!

     

    Thanks

    Sunnyz

    Wednesday, October 6, 2010 3:06 PM

Answers

  • You can not, you will get another error states 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch when you debug your application.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    • Marked as answer by liurong luo Thursday, October 14, 2010 10:51 AM
    Tuesday, October 12, 2010 7:52 AM

All replies

  • > not working

    Please post the full error message.

    If the script includes GO statements, you need to take them out.  This is because Management Studio really uses GO to split into separate batches, basically corresponding to separate SqlCommands.  It is possible that after taking them out in will not work right, but you can post back if you encounter further problems.

    It might also help if you include the .NET code and the SQL code, such as a cut down version of it that reproduces the problem.

     

     

     

    Wednesday, October 6, 2010 11:21 PM
  • I have tired it with creation of multiple stored procedures , and its not working there too. it gives error like 'incorrect syntex near Procedure ' 

    string sql = "Create Procedure [dbo].[XYZ_Delete] @X_ID int as  delete from tbl where X_ID = @X_ID ; Create Procedure [dbo].[XYZ2_Delete] @X_ID int as  delete from tbl2 where X_ID = @X_ID ;Create Procedure [dbo].[XYZ3_Delete] @X_ID int as  delete from tbl3 where X_ID = @X_ID ;"

                    SqlComd = new SqlCommand();
                    SqlComd.Connection = SqlCon;
                    SqlComd.CommandType = CommandType.Text;

                      SqlComd.CommandText =  sql;
                      int r = SqlComd.ExecuteNonQuery();  // gets error here 'incorrect syntex near Procedure ' 

     

    So does this mean I cannot run multiple Create Procedure statements in one run ?

    Thanks.

    Thursday, October 7, 2010 6:54 AM
  • You can not, you will get another error states 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch when you debug your application.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    • Marked as answer by liurong luo Thursday, October 14, 2010 10:51 AM
    Tuesday, October 12, 2010 7:52 AM