Agfdhyk

I don't know why, but when I'm executing that code on MSSQL 2012 and 2015 everything works fine (I'm receving the DataTable's schema into the declared DataSet), while when I'm doing it on MSSQL 2017 - FillSchema puts NOTHING to DataSet.

DataSet.Tables.Count is equal 0.

Here is a sample code below:

var da = new SqlDataAdapter();

var cn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Test"].ConnectionString);

try

{

    cn.Open();

    var sql = "SELECT * FROM MyTable T" +

        "WHERE T.MyTableId=123 AND " +

        "EXISTS" +

        "(" +

        "  SELECT * FROM dbo.MyFunction() CTX WHERE ISNULL(T.MyOtherId, -1) = CTX.MyOtherId" +

        ")";

    da.SelectCommand = new SqlCommand(sql, cn);



    var ds = new DataSet();

    da.FillSchema(ds, SchemaType.Source);

    //There no table in the DataSet!

}

catch (Exception ex)

{

    MessageBox.Show("Error " + ex.Message);

}

finally

{

    cn.Close();

}

Something has changed in MS SQL 2017? Or maybe I'm doing something wrong?

EDIT 1: Problem disappears when I remove the EXISTS condition from the query. I think calling the function might be the reason.

EDIT 2: When I simplify the query to:

var sql = "SELECT * FROM dbo.MyFunction()";

Still not working... You may wondering what MyFunction in SQL do. It's a Multi-Statement Table function:

CREATE FUNCTION dbo.MyFunction ()

RETURNS @RESULT TABLE 

(

    ID BIGINT NOT NULL,

    NAME VARCHAR(50),

    CODE VARCHAR(50)

)

AS

BEGIN

    INSERT INTO @RESULT VALUES(1, 'Name', 'NAME')

RETURN 

END

GO

When I rewrite Multi-Statement Table Function to Inline-Statement it works.
I know it's not the best way in .NET to receive schema... but any ideas why the multi-statement functions not working?

This happens because SQL Server 2017 does not return metadata when SELECT * FROM dbo.MyFunction() is executed under SET FMTONLY ON, which is still the method used by ADO.NET to retrieve metadata. It's been deprecated, and sp_describe_first_result_set does work correctly for this case, but that's still no reason for SQL Server 2017 to fail. This has been reported as a bug.

Speculation: this bug may have been introduced as a side effect of the new interleaved execution feature, which changes the way these functions are executed. This is reinforced by the observation that it disappears if the database compatibility level is dropped down to 130, which disables this (among other things). I'm not aware of a trace flag that disables only interleaved execution, which could be used to test this.

There is a workaround, of sorts: set fmtonly on; exec ('select * from dbo.MyFunction()'); set fmtonly off will return metadata even under compat level 140, so fetching metadata of a query using EXEC will still work. In the above,

var sql = "EXEC ('SELECT * FROM dbo.MyFunction()')";

should return results.