ADO.NET & SELECT ... UNION issue

Hi,

I have discovered something weird.

I prepared a dataset that consists of a table adapter which has a select command of type stored proc. My stored procedure performs a select on a table1 and then table2 using UNION. My stored proc is running perfectly in the SQL Management studio (SQL2005) - no questions.

In Visual Studio when I test my dataset querying my tableadapter I get a result that is not just a UNION but a join of empty columns (number of empty columns = number of columns from one of my tables) and then the result of select statement from my stored proc.

And then my asp.net code fails as well because my gridView is expecting only 3 columns but instead I am getting 6 (3 empty + 3 those I was expecting in the first place.)

I think ADO.NET converted you UNION to a JOIN which either means your table was UNION compatible implicitly or ADO.NET internally is using very old SET operation based JOIN syntax. So check your table to see if it is explicitly UNION compatible, I am assuming you know UNION performs implicit distinct to remove duplicates so its requirements are strict compared to UNION ALL which includes duplicate rows. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms180026.aspx

|||

Hey,

That is exactly what happened. ADO.net converted my resultset to some sort of join and I know why.

The thing that caused that abnormal behavior was I used a join on two tables that have different column names. Once I've changed my select statement in both parts of the union to return exactly the same names of columns it's started working properly.