CODEWITHSUNDEEP
c#sql-serverexecutenonquery
octopusgrabbus
octopusgrabbus

Reputation: 10685

Difficulty Executing "Execute" Query From C#

I have this query that runs on our SQLServer that hosts a vendor's application. 

exec sp_configure 'show advanced options',1
RECONFIGURE 
exec sp_configure 'Ole Automation Procedures', 1
RECONFIGURE

exec sp_configure 'show advanced options', 0
RECONFIGURE 

exec CreateMunisExportFile

exec sp_configure 'show advanced options',1
RECONFIGURE

exec sp_configure 'Ole Automation Procedures', 0
RECONFIGURE

exec sp_configure 'show advanced options', 0
RECONFIGURE

I want to execute it remotely using C#.

I am using Visual Studio 2015, but cannot find access to the SQLServer modules that allow you to create a Server object. I lost the link of that post, but have also been looking at the following and other posts in SO, this-post, and that-post.

I am getting -1 from ExecuteNonQuery in the following function, and want to know how I can get further error information.

   private void btRunQuery_Click(object sender, EventArgs e)
    {
        using (var conn = new SqlConnection("Data Source=SERVER\\TICKETTRAK;Initial Catalog=TTRAK9.4.20;User Id=sa;Password=xxxyyyyz;Pooling=false"))
        {
                 string script = File.ReadAllText(@".\SpecialTickeTrakExport_20181106.sql");
                 using (SqlCommand mySqlCmd = new SqlCommand(script))
                 {
                     conn.Open();
                     mySqlCmd.Connection = conn; 
                     var result = mySqlCmd.ExecuteNonQuery();
                     conn.Close();
                 }
        }
    }

Upvotes: 0

Views: 286

Answers (1)

Gabriel Luci
Gabriel Luci

Reputation: 40858

Nothing's wrong.

The documentation for ExecuteNonQuery says:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

Since the command you are executing is not an UPDATE, INSERT, or DELETE, then the return value will always be -1.

Upvotes: 2

Related Questions