ADO.NET SP throws ArgumentException after migrating to SQL 2005 Standard - UPDATE 2 - FIXED

I just migrated a database from SQL 2000 Developer Edition on W2k Server to a production machine running SQL 2005 Standard on W2k3 Server Standard. A Web Service was also migrated from .NET 1.1 on the development machine to .NET 2.0 on the production machine.

Since the migration, a stored procedure that worked on SQL 2000/.NET 1.1 now throws a System.ArgumentException: "No mapping exists from object type System.RuntimeType to a known managed proider native type."

This occurs with a date value - the stored procedure is filled from XML data the Web Service receives from the client program by a generic subroutine that recognizes the applicable table names and primary key column names, selecting the appropriate stored procedure to use, but may or may not have data to update for each field. I have tried explicitly setting the SQLDBType for the parameter as the parametercollection is filled, but still get the ArgumentException. I also tried changing the parameter type to varchar(20), and having the stored procedure convert it to datetime, but still got the exception.

I know the ultimate solution will probably be to build the data Web Service within SQL 2005, but what can I do to get this working until I have time for that conversion?

Thanks for any help.

Darrell Escola

UPDATE - I was able to use the stored procedures to manually update the database using data that should have passed through the web service, so the problem is within the web service on .NET 2.0 - I think 2.0 is much stricter on data types than was 1.1. The SPs create dynamic SQL that is then executed - I know this is not the best security, hence my eventual interest in creating an end-point within SQL 2005 to handle the data in a more secure manner.

Darrell

|||

I finally got the web service to update through the stored procedure. I was able to access the stored procedure by specifically casting the data types for each parameter using a subroutine similar to that shown below, but the web service was still throwing an exception on the output value type, which I use to get the actual primary key value for the new record, so the off-line client can properly map its dependent records to the proper key value before inserting those dependent records:

Dim newID As SQLTypes.SQLInt32

I added a subroutine to determine the data type for each column used in the update:

Private Sub AddSPParameter(ByRef cmd as SQLClient.SQLCommand, ByVal paramName as String, ByVal fieldValue as Object)

If paramName = "<stringColName1>" Or paramName = "<stringColName...>" Then

cmd.Parameters.AddWithValue(Ctype(Cstr(fieldValue), SQLTypes.SQLString).DbType = DbType.String

ElseIf paramName = "<otherTypeColName1>" Or paramName.IndexOf("...") > 0 Or paramName.EndsWith("...") Then

cmd.Parameters.AddWithValue(Ctype(Cstr(fieldValue), SQLTypes.SQL<otherType>).DbType = DbType.<otherType>

ElseIf ...

End If

This subroutine determines the column data type by matches on the column name, and then explicitly casting the parameter value to the applicable database data type.

I also had to explicitly cast the Return Value:

cmd.Parameters.AddWithValue("@.ReturnValue", Ctype(0, SQLTypes.SQLInt32)).DbType = SqlDbType.Int

cmd.Parameters("@.ReturnValue").Direction = ParameterDirection.ReturnValue

Lesson learned: Explicitly type all data values used.

Darrell