Valid SQL Server query returning empty in System.Data.SqlClient.SqlCommand

Question

Here is my query, it is on 2014 Adventure works.

DECLARE @l_RunSQL VARCHAR(MAX);

SELECT @l_RunSQL = COALESCE(@l_RunSQL + ' UNION ALL SELECT * FROM [', 'SELECT * FROM [') + TABLE_SCHEMA +'].['+ TABLE_NAME+']'
FROM INFORMATION_SCHEMA.TABLES
WHERE [TABLE_TYPE] = 'BASE TABLE'
  AND [TABLE_NAME] LIKE 'Address';

EXEC (@l_RunSQL)
    WITH RESULT SETS
         (
             (
                 [AddressID] [INT],
                 [AddressLine1] [NVARCHAR](60),
                 [AddressLine2] [NVARCHAR](60),
                 [City] [NVARCHAR](30),
                 [StateProvinceID] [INT],
                 [PostalCode] [NVARCHAR](15),
                 [SpatialLocation] [GEOGRAPHY],
                 [rowguid] [UNIQUEIDENTIFIER],
                 [ModifiedDate] [DATETIME]
             )
        );

I am able to run this in SSMS and I get the contents of the Person.Address table as desired.

In C#, I create a connection node and then a command node and try and execute query like so:

using (var reader = command.ExecuteReader(CommandBehavior.SchemaOnly))
{
     schemaTable = reader.GetSchemaTable();
}

And the reader results come back empty. Why is this command valid in SSMS but not using C# ?

The connection is of type System.Data.SqlClient.SqlConnection and the command is of type System.Data.SqlClient.SqlCommand.

Any help would be greatly appreciated

pfx · Accepted Answer · 2019-02-07 19:32:57Z

2

It's because you are executing with CommandBehavior.SchemaOnly, which returns column information only, without data, see documentation.

The query returns column information only. When using SchemaOnly, the .NET Framework Data Provider for SQL Server precedes the statement being executed with SET FMTONLY ON.

To access the returned data from the query, just execute as below.

using (var reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        // Access your data here
        Console.WriteLine(String.Format("{0}", reader[0])); // Column AddressID
        // ...
    }
}

edited Feb 7, 2019 at 19:32

answered Feb 7, 2019 at 19:20

pfx

23.9k52 gold badges105 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jacob Alley Over a year ago

But shouldn't I at least be seeing my schema info, like what is in the result set?

pfx Over a year ago

Might have to do with the WITH RESULT SETS feature; with this in the query GetSchemaTable returns null. For a 'regular' query (eg. select * from sometable) CommandBehavior.SchemaOnly is OK to return the schematable. At least, without CommandBehavior.SchemaOnly you can access the schematable.

Collectives™ on Stack Overflow

Valid SQL Server query returning empty in System.Data.SqlClient.SqlCommand

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related