0

Over the weekend we migrated our Sybase ASE 12.5 instance to a 15 instance. A classic ASP application that is connected to the Sybase database is mostly working correctly except one page. On that page when one of the methods is called that sends a request to the database (returns multiple fields, some strings, some ints, and two dates), on the return it is failing with the following error "Conversion failed because of the DateTime data value overflowed the type specified for the DataTime value part in the consumer's buffer". We altered the stored proc to not return the two date fields and it now returns, but the data it is returning to the front end is invalid data for the stored proc. We have a string that is wrong (should return "D" and is returning "A") a string that should be populated but is null, and an int that should be 1 returning some crazy long int. We went down the path of possibly having a driver issue of the front end server when trying to connect to 15 instead of 12.5 but can't quiet figure out if that is the issue. We tried changing the connection string from using Sybase.ASEOLEDBProvider.2 to ASEOLEDB and that results in errors when the connection is trying to establish.

The connection is an OleDb connection

Before we altered the stored proc, it was failing when trying to execute "ExecuteNonQuery"

We did verify the values going to the database (even pulled to the logs to make sure what was being passed to the SP) and that is all valid and running the SP manually returns the expected results.

Is this a driver issue? Or difference in data types?

This the application code

    OleDbConnection planningconnection = new OleDbConnection(_planningconnectionString);
    planningconnection.Open();

    OleDbCommand command = new OleDbCommand("dbo.tour_last_arrive_home", planningconnection);
    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add("@start_datetime", OleDbType.DBTimeStamp);
    command.Parameters["@start_datetime"].Value = dNextAvailable;
    command.Parameters.Add("@tour_no", OleDbType.Integer);
    command.Parameters["@tour_no"].Value = DplingOldRun;
    command.Parameters.Add("@operator_code", OleDbType.Char, 1);
    command.Parameters["@operator_code"].Value = DplingOldJob;

    // Stored Procedure Output
    command.Parameters.Add("@parmoutSchedNbr", OleDbType.Integer);
    command.Parameters["@parmoutSchedNbr"].Direction = ParameterDirection.Output;

    command.Parameters.Add("@parmoutSection", OleDbType.Integer);
    command.Parameters["@parmoutSection"].Direction = ParameterDirection.Output;

    command.Parameters.Add("@parmoutJob", OleDbType.Char, 1);
    command.Parameters["@parmoutJob"].Direction = ParameterDirection.Output;

    command.Parameters.Add("@parmoutArriveCity", OleDbType.Char, 28);
    command.Parameters["@parmoutArriveCity"].Direction = ParameterDirection.Output;

    command.Parameters.Add("@parmoutArriveDatetime", OleDbType.DBTimeStamp);
    command.Parameters["@parmoutArriveDatetime"].Direction = ParameterDirection.Output;

    command.Parameters.Add("@parmoutSignOff", OleDbType.DBTimeStamp);
    command.Parameters["@parmoutSignOff"].Direction = ParameterDirection.Output;

    command.ExecuteNonQuery();
Kul-Tigin
  • 16,728
  • 1
  • 35
  • 64
  • Make sure that you're data in the database is correct. Also check if there is no change of field type identifiers for example a string type is changed to integer or int is not bigint etc. This can happen with database updates. – Plippie Jan 30 '19 at 12:49
  • If all you did was upgrade the ASE version then datatypes should be the same (ie, the upgrade process does not change datatypes); there were a lot of changes/differences between 12.5 and 15.x, and if I recall correctly this included the need for applications to upgrade drivers so, yeah, I'd suggest you look into upgrading your drivers; also, since the stored proc returns the correct values when executed manually, but you're getting (effectively) garbage in your application .. this is another sure sign there's an issue with the (older) drivers in use – markp-fuso Jan 31 '19 at 12:14

0 Answers0