Difficulty Executing "Execute" Query From C#

Question

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();
                 }
        }
    }

You're getting -1, but did the command run? – Tipx Nov 15 '18 at 20:35 — Tipx, Nov 15 '18 at 20:35

score 2 · Accepted Answer · answered Nov 15 '18 at 20:33

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.

Difficulty Executing "Execute" Query From C#

1 Answers1