OdbDataReader.GetSchemaTable and SQL Server 2005 XML Data Type

[Howard Hoffman]

Does the System.Data.Common.OdbDataReader type shipping with VS.NET 2005
support the XML column type on SQL Server 2005?

I've a stored procedure that has an input parameter of the XML data type.
If I create an OdbcDataReader via OdbcCommand.ExecuteReader( .. ) and try to
call GetSchemaTable I get an OdbcException with a 4200 Error:

ERROR [4200][Microsoft][SQL Native Client][SQL Server]Incorrrect syntax near
the keyword 'convert'

Here's the partial ODBC Provider stack trace:

StackTrace " at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle
hrHandle, RetCode retcode)\r\n at
System.Data.Odbc.OdbcDataReader.get_FieldCount()\r\n at
System.Data.Odbc.OdbcDataReader.NewSchemaTable()\r\n at
System.Data.Odbc.OdbcDataReader.GetSchemaTable()\r\n at
....mycalltoGetSchemaTableHERE"

My CommandText is "{? = CALL dbo.MySproc(?)}"
My CommandBehavior input to OdbcCommand.ExecuteReader is SchemaOnly |
KeyInfo | CloseConnection. (I've also tried SchemaOnly | CloseConnection).

Does the Win32 API SQLNumResultCols barf when there's an XML column type?
Is the advice...don't use XML columns or stored procedure parameters with
ODBC?

If this isn't the right newsgroup for this query, please accept apologies
and direct me to the correct newsgroup.

Thanks in advance,

Howard Hoffman

 

[Howard Hoffman]

Correction: The XML question appears to be a red herring.

The issue has to do with calling OdbcDataReader.GetSchemaTable on a stored
procedure that does *not* perform a SELECT. For other DbDataReader leaves
I've tried( OleDb, SQL Server, MS OracleClient), this is not an issue -- the
returned DataTable is null for stored procedures of this ilk.

OdbcDataReader.GetSchemaTable throws an exception for this case, however.

Why is Odbc behaving differently from the other ADO.NET providers for this
API?

Howard

 

[Kevin Yu [MSFT]]

Hi Howard,

I tried it on my machine, and I can reproduce this. It seems that the
CommandBehavior.SchemaOnly is the point that makes your code not working
well. I made a stored procedure that does not SELECT anything, and when I
add the SchemaOnly enum, the problem occurs. So in this case, I suggest you
remove SchemaOnly enum from the code, since your sp doesn't return
anything. There is no schema, so it throughs exception.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Howard Hoffman]

Kevin,

I'm working on a code generator that generates stored procedure command
wrappers, targeting all the ADO.NET providers. The code generator does not
(cannot, really) know anything about the stored procedure(s) it needs to
wrap.
So far, the Odbc provider is the only one that throws an exceptoin on
xxxDataReader.GetSchemaTable.

Feels like a defect to me.

Thanks for your answer and all future help,

Howard

 

[Kevin Yu [MSFT]]

Hi Howard,

It seems that the underlying ODBC driver doesn't support a null return with
SchemaOnly enum. You can try to

1. Remove the SchemaOnly.
2. Add additional code to check if there is a SELECT in the stored
procedure.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."