Problems with running Sybase SQL Script from C# - too many parameters

Question

I'm having issues running a Sybase SQL Script from an AseCommand object in the Sybase API. Here are copies of my script, which is passed into the C# as the sql string parameter

SQL Script:

DECLARE @dt DATETIME
DECLARE @mx INT

SELECT @dt = getDate()


EXEC DATABASE_NAME..StoredProcedure1
    @id_own_grd  =  200,
    @id_dom_grd  =  'TEST VALUE',
    @value       =  @mx OUTPUT

EXEC DATABASE_NAME..StoredProcedure2
    @id_level = @mx, @id_level_pt=NULL, @id_obj1 = 58464819, 
    @id_typ_lvl='TEST', @am_test=20130916,  @id_obj1_sub = 12949, 
    @dt_start_lvl = '9/16/2013', @dt_end_lvl = '9/16/2013',@ct_blah=0,
    @id_abs=" ", @id_obj1_trans=0,@am_obj1_vol=NULL, @am_obj3=-0.311095,
    @id_obj_test = NULL, @id_obj5_test = NULL, @id_is_valid = '0',
    @nm_db_origin='ORIGIN_DB', @id_owner=200

C# code that calls above SQL Script:

public Dictionary<int, Dictionary<string, object>> ExecuteSqlQuery(string sql)
{
    lock (this)
    {
        try
        {
            using (AseCommand command = new AseCommand(sql, Connection))
            using (AseDataReader reader = command.ExecuteReader())
            {
                Log.Info("Executing Query: [" + sql + "] on " + DbSettings);
                command.CommandTimeout = 120;
                var results = new Dictionary<int, Dictionary<string, object>>();
                var columns = new List<string>();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    columns.Add(reader.GetName(i));
                }
                int row = 0;
                while (reader.Read())
                {
                    Dictionary<string, object> rowResults = new Dictionary<string, object>();
                    results.Add(row, rowResults);
                    foreach (string item in columns)
                    {
                        rowResults.Add(item, reader[item]);
                    }
                    row++;
                }
                Log.Info("Finished Executing Query: [" + sql + "] on " + DbSettings + " in " + timer.TimeTaken());
                return results;
            }
        }
        catch (Exception e)
        {
            Log.Error("An error occured executing query SQL: " + e.Message, e);
            throw;
        }
    }
}

Whenever I try running the above script, an Sybase.Data.AseClient.AseException is thrown and caught which says "Invalid amount of parameters". This C# code works for any other code that does not call stored procedures. I am aware that Sybase has specific steps for calling a stored procedure, especially with output parameters, detailed in this link:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc20066.0215/html/adonet2155/Procedures_adodotnet_development.htm

However, my users want to be able to call several stored procedures in one script - something I can't figure out how to do yet. Anyone got any ideas?

Thanks,

Answer 1

A colleague and I found the answer. You have to change a flag in the Sybase connection string. Add this to the connection string

;Named Parameters=false

This tells the Ase Client to not check each parameter passed to the stored procedure. If it blows up, its entirely on the caller. You also need to set NamedParameters = false for anytime you are using OUTPUT parameters as well.