|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SqlDataReader.GetSchemaTable returns too many columnsSqlDataReader.GetSchemaTable() on a database view (SQL Server 2005). It is returning rows not only for the columns in the view, but also for another column that is not ouput in the view. For example, I execute the following code on the Northwind database view named [Alphabetical list of products]: command.CommandText = "SELECT * FROM [Alphabetical list of products]"; reader = cmd.ExecuteReader(CommandBehavior.KeyInfo); schemaTable = reader.GetSchemaTable(); This view selects all 10 columns from Products and also CategoryName from Categories (11 columns total). The tables are joined on CategoryID. When I run the above code, it returns a schema table that contains 12 rows--all 10 from Products, Categories.CategoryName and also Categories.CategoryID. It is this last one that is causing problems for me. CategoryID is appearing twice in my list (once for each table) even though only one of them is in the SELECT. So my questions are: 1. Why is Categories.CategoryID getting into the schema table even though it is not selected? 2. Is there any way to prevent it from getting in there and having the schema table only show the selected columns? For those who do not have the Northwind database handy, here's the view's sql: SELECT dbo.Products.ProductID, dbo.Products.ProductName, dbo.Products.SupplierID, dbo.Products.CategoryID, dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice, dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder, dbo.Products.ReorderLevel, dbo.Products.Discontinued, dbo.Categories.CategoryName FROM dbo.Categories INNER JOIN dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID WHERE (dbo.Products.Discontinued = 0) Thanks, John When you specify the CommandBehavior.KeyInfo option, Sql Server will
automatically return you the columns in your original select along with the necessary additional columns needed to uniquely identify the rows returned. For example, if you had this table: CREATE TABLE Foo (ID INT PRIMARY KEY, MyValue VARCHAR(50)) If you called ExecuteReader/GetSchemaTable on this query: SELECT MyValue FROM FOO Without KeyInfo, you'd only get MyValue when you called GetSchemaTable(). However, if you specify KeyInfo, you'll get ID as well because ID is the key for the given select clause and you specifically asked for key info. Robert Show quote "John" <revelation@nospam.nospam> wrote in message news:eFEQwRE6GHA.2044@TK2MSFTNGP02.phx.gbl... > I've run into a problem I can't seem to explain or work around. I'm > calling SqlDataReader.GetSchemaTable() on a database view (SQL Server > 2005). It is returning rows not only for the columns in the view, but > also for another column that is not ouput in the view. For example, I > execute the following code on the Northwind database view named > [Alphabetical list of products]: > > command.CommandText = "SELECT * FROM [Alphabetical list of products]"; > reader = cmd.ExecuteReader(CommandBehavior.KeyInfo); > schemaTable = reader.GetSchemaTable(); > > This view selects all 10 columns from Products and also CategoryName from > Categories (11 columns total). The tables are joined on CategoryID. When > I run the above code, it returns a schema table that contains 12 rows--all > 10 from Products, Categories.CategoryName and also Categories.CategoryID. > It is this last one that is causing problems for me. CategoryID is > appearing twice in my list (once for each table) even though only one of > them is in the SELECT. > > So my questions are: > > 1. Why is Categories.CategoryID getting into the schema table even though > it is not selected? > 2. Is there any way to prevent it from getting in there and having the > schema table only show the selected columns? > > For those who do not have the Northwind database handy, here's the view's > sql: > SELECT dbo.Products.ProductID, dbo.Products.ProductName, > dbo.Products.SupplierID, dbo.Products.CategoryID, > dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice, > dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder, > dbo.Products.ReorderLevel, dbo.Products.Discontinued, > dbo.Categories.CategoryName > FROM dbo.Categories > INNER JOIN dbo.Products ON dbo.Categories.CategoryID = > dbo.Products.CategoryID > WHERE (dbo.Products.Discontinued = 0) > > Thanks, > John > Thank you, Robert. That makes sense. My problem is (was) that I need the
key info that is provided in the table, but only for the selected columns. Through the magic of the IntelliSense Visualizers I just found the IsHidden column in the table. This appears to tell me whether or not the column was included in the select. That is what I needed. I can now ignore the hidden columns, which is what I need to do. Funny, IsHidden is not in my VS documentation, but it is on the MSDN site. Thanks for your reply, as it got me thinking and looking in the right direction. John Show quote "Robert Simpson" <rmsimpson@nospam.nospam> wrote in message news:OlZAUmE6GHA.4232@TK2MSFTNGP02.phx.gbl... > When you specify the CommandBehavior.KeyInfo option, Sql Server will > automatically return you the columns in your original select along with > the necessary additional columns needed to uniquely identify the rows > returned. > > For example, if you had this table: > > CREATE TABLE Foo (ID INT PRIMARY KEY, MyValue VARCHAR(50)) > > If you called ExecuteReader/GetSchemaTable on this query: > > SELECT MyValue FROM FOO > > Without KeyInfo, you'd only get MyValue when you called GetSchemaTable(). > However, if you specify KeyInfo, you'll get ID as well because ID is the > key for the given select clause and you specifically asked for key info. > > Robert > > "John" <revelation@nospam.nospam> wrote in message > news:eFEQwRE6GHA.2044@TK2MSFTNGP02.phx.gbl... >> I've run into a problem I can't seem to explain or work around. I'm >> calling SqlDataReader.GetSchemaTable() on a database view (SQL Server >> 2005). It is returning rows not only for the columns in the view, but >> also for another column that is not ouput in the view. For example, I >> execute the following code on the Northwind database view named >> [Alphabetical list of products]: >> >> command.CommandText = "SELECT * FROM [Alphabetical list of products]"; >> reader = cmd.ExecuteReader(CommandBehavior.KeyInfo); >> schemaTable = reader.GetSchemaTable(); >> >> This view selects all 10 columns from Products and also CategoryName from >> Categories (11 columns total). The tables are joined on CategoryID. >> When I run the above code, it returns a schema table that contains 12 >> rows--all 10 from Products, Categories.CategoryName and also >> Categories.CategoryID. It is this last one that is causing problems for >> me. CategoryID is appearing twice in my list (once for each table) even >> though only one of them is in the SELECT. >> >> So my questions are: >> >> 1. Why is Categories.CategoryID getting into the schema table even though >> it is not selected? >> 2. Is there any way to prevent it from getting in there and having the >> schema table only show the selected columns? >> >> For those who do not have the Northwind database handy, here's the view's >> sql: >> SELECT dbo.Products.ProductID, dbo.Products.ProductName, >> dbo.Products.SupplierID, dbo.Products.CategoryID, >> dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice, >> dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder, >> dbo.Products.ReorderLevel, dbo.Products.Discontinued, >> dbo.Categories.CategoryName >> FROM dbo.Categories >> INNER JOIN dbo.Products ON dbo.Categories.CategoryID = >> dbo.Products.CategoryID >> WHERE (dbo.Products.Discontinued = 0) >> >> Thanks, >> John >> > > I guess I could've mentioned the IsHidden property and saved you some
additional time while I was at it ... oh well! :P Robert Show quote "John" <revelation@nospam.nospam> wrote in message news:%23z%23gypI6GHA.4644@TK2MSFTNGP04.phx.gbl... > Thank you, Robert. That makes sense. My problem is (was) that I need the > key info that is provided in the table, but only for the selected columns. > Through the magic of the IntelliSense Visualizers I just found the > IsHidden column in the table. This appears to tell me whether or not the > column was included in the select. That is what I needed. I can now > ignore the hidden columns, which is what I need to do. Funny, IsHidden is > not in my VS documentation, but it is on the MSDN site. > > Thanks for your reply, as it got me thinking and looking in the right > direction. > > John > > "Robert Simpson" <rmsimpson@nospam.nospam> wrote in message > news:OlZAUmE6GHA.4232@TK2MSFTNGP02.phx.gbl... >> When you specify the CommandBehavior.KeyInfo option, Sql Server will >> automatically return you the columns in your original select along with >> the necessary additional columns needed to uniquely identify the rows >> returned. >> >> For example, if you had this table: >> >> CREATE TABLE Foo (ID INT PRIMARY KEY, MyValue VARCHAR(50)) >> >> If you called ExecuteReader/GetSchemaTable on this query: >> >> SELECT MyValue FROM FOO >> >> Without KeyInfo, you'd only get MyValue when you called GetSchemaTable(). >> However, if you specify KeyInfo, you'll get ID as well because ID is the >> key for the given select clause and you specifically asked for key info. >> >> Robert >> >> "John" <revelation@nospam.nospam> wrote in message >> news:eFEQwRE6GHA.2044@TK2MSFTNGP02.phx.gbl... >>> I've run into a problem I can't seem to explain or work around. I'm >>> calling SqlDataReader.GetSchemaTable() on a database view (SQL Server >>> 2005). It is returning rows not only for the columns in the view, but >>> also for another column that is not ouput in the view. For example, I >>> execute the following code on the Northwind database view named >>> [Alphabetical list of products]: >>> >>> command.CommandText = "SELECT * FROM [Alphabetical list of products]"; >>> reader = cmd.ExecuteReader(CommandBehavior.KeyInfo); >>> schemaTable = reader.GetSchemaTable(); >>> >>> This view selects all 10 columns from Products and also CategoryName >>> from Categories (11 columns total). The tables are joined on >>> CategoryID. When I run the above code, it returns a schema table that >>> contains 12 rows--all 10 from Products, Categories.CategoryName and also >>> Categories.CategoryID. It is this last one that is causing problems for >>> me. CategoryID is appearing twice in my list (once for each table) even >>> though only one of them is in the SELECT. >>> >>> So my questions are: >>> >>> 1. Why is Categories.CategoryID getting into the schema table even >>> though it is not selected? >>> 2. Is there any way to prevent it from getting in there and having the >>> schema table only show the selected columns? >>> >>> For those who do not have the Northwind database handy, here's the >>> view's sql: >>> SELECT dbo.Products.ProductID, dbo.Products.ProductName, >>> dbo.Products.SupplierID, dbo.Products.CategoryID, >>> dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice, >>> dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder, >>> dbo.Products.ReorderLevel, dbo.Products.Discontinued, >>> dbo.Categories.CategoryName >>> FROM dbo.Categories >>> INNER JOIN dbo.Products ON dbo.Categories.CategoryID = >>> dbo.Products.CategoryID >>> WHERE (dbo.Products.Discontinued = 0) >>> >>> Thanks, >>> John >>> >> >> > > It's always better when I find things out on my own... It makes them stick
better in this weary old brain! :) Show quote "Robert Simpson" <rmsimpson@nospam.nospam> wrote in message news:%232hWWWJ6GHA.5108@TK2MSFTNGP03.phx.gbl... >I guess I could've mentioned the IsHidden property and saved you some >additional time while I was at it ... oh well! :P > > Robert > > "John" <revelation@nospam.nospam> wrote in message > news:%23z%23gypI6GHA.4644@TK2MSFTNGP04.phx.gbl... >> Thank you, Robert. That makes sense. My problem is (was) that I need >> the key info that is provided in the table, but only for the selected >> columns. Through the magic of the IntelliSense Visualizers I just found >> the IsHidden column in the table. This appears to tell me whether or not >> the column was included in the select. That is what I needed. I can now >> ignore the hidden columns, which is what I need to do. Funny, IsHidden >> is not in my VS documentation, but it is on the MSDN site. >> >> Thanks for your reply, as it got me thinking and looking in the right >> direction. >> >> John >> >> "Robert Simpson" <rmsimpson@nospam.nospam> wrote in message >> news:OlZAUmE6GHA.4232@TK2MSFTNGP02.phx.gbl... >>> When you specify the CommandBehavior.KeyInfo option, Sql Server will >>> automatically return you the columns in your original select along with >>> the necessary additional columns needed to uniquely identify the rows >>> returned. >>> >>> For example, if you had this table: >>> >>> CREATE TABLE Foo (ID INT PRIMARY KEY, MyValue VARCHAR(50)) >>> >>> If you called ExecuteReader/GetSchemaTable on this query: >>> >>> SELECT MyValue FROM FOO >>> >>> Without KeyInfo, you'd only get MyValue when you called >>> GetSchemaTable(). However, if you specify KeyInfo, you'll get ID as well >>> because ID is the key for the given select clause and you specifically >>> asked for key info. >>> >>> Robert >>> >>> "John" <revelation@nospam.nospam> wrote in message >>> news:eFEQwRE6GHA.2044@TK2MSFTNGP02.phx.gbl... >>>> I've run into a problem I can't seem to explain or work around. I'm >>>> calling SqlDataReader.GetSchemaTable() on a database view (SQL Server >>>> 2005). It is returning rows not only for the columns in the view, but >>>> also for another column that is not ouput in the view. For example, I >>>> execute the following code on the Northwind database view named >>>> [Alphabetical list of products]: >>>> >>>> command.CommandText = "SELECT * FROM [Alphabetical list of products]"; >>>> reader = cmd.ExecuteReader(CommandBehavior.KeyInfo); >>>> schemaTable = reader.GetSchemaTable(); >>>> >>>> This view selects all 10 columns from Products and also CategoryName >>>> from Categories (11 columns total). The tables are joined on >>>> CategoryID. When I run the above code, it returns a schema table that >>>> contains 12 rows--all 10 from Products, Categories.CategoryName and >>>> also Categories.CategoryID. It is this last one that is causing >>>> problems for me. CategoryID is appearing twice in my list (once for >>>> each table) even though only one of them is in the SELECT. >>>> >>>> So my questions are: >>>> >>>> 1. Why is Categories.CategoryID getting into the schema table even >>>> though it is not selected? >>>> 2. Is there any way to prevent it from getting in there and having the >>>> schema table only show the selected columns? >>>> >>>> For those who do not have the Northwind database handy, here's the >>>> view's sql: >>>> SELECT dbo.Products.ProductID, dbo.Products.ProductName, >>>> dbo.Products.SupplierID, dbo.Products.CategoryID, >>>> dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice, >>>> dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder, >>>> dbo.Products.ReorderLevel, dbo.Products.Discontinued, >>>> dbo.Categories.CategoryName >>>> FROM dbo.Categories >>>> INNER JOIN dbo.Products ON dbo.Categories.CategoryID = >>>> dbo.Products.CategoryID >>>> WHERE (dbo.Products.Discontinued = 0) >>>> >>>> Thanks, >>>> John >>>> >>> >>> >> >> > > If you modify the view in the following form:
ALTER VIEW TheView WITH VIEW_METADATA AS SELECT .... Then GetSchemaTable will return only the fields in the select list. No hidden fields will be returned. Regards: Jesús López Show quote "John" <revelation@nospam.nospam> escribió en el mensaje news:%23z%23gypI6GHA.4644@TK2MSFTNGP04.phx.gbl... > Thank you, Robert. That makes sense. My problem is (was) that I need the > key info that is provided in the table, but only for the selected columns. > Through the magic of the IntelliSense Visualizers I just found the > IsHidden column in the table. This appears to tell me whether or not the > column was included in the select. That is what I needed. I can now > ignore the hidden columns, which is what I need to do. Funny, IsHidden is > not in my VS documentation, but it is on the MSDN site. > > Thanks for your reply, as it got me thinking and looking in the right > direction. > > John > > "Robert Simpson" <rmsimpson@nospam.nospam> wrote in message > news:OlZAUmE6GHA.4232@TK2MSFTNGP02.phx.gbl... >> When you specify the CommandBehavior.KeyInfo option, Sql Server will >> automatically return you the columns in your original select along with >> the necessary additional columns needed to uniquely identify the rows >> returned. >> >> For example, if you had this table: >> >> CREATE TABLE Foo (ID INT PRIMARY KEY, MyValue VARCHAR(50)) >> >> If you called ExecuteReader/GetSchemaTable on this query: >> >> SELECT MyValue FROM FOO >> >> Without KeyInfo, you'd only get MyValue when you called GetSchemaTable(). >> However, if you specify KeyInfo, you'll get ID as well because ID is the >> key for the given select clause and you specifically asked for key info. >> >> Robert >> >> "John" <revelation@nospam.nospam> wrote in message >> news:eFEQwRE6GHA.2044@TK2MSFTNGP02.phx.gbl... >>> I've run into a problem I can't seem to explain or work around. I'm >>> calling SqlDataReader.GetSchemaTable() on a database view (SQL Server >>> 2005). It is returning rows not only for the columns in the view, but >>> also for another column that is not ouput in the view. For example, I >>> execute the following code on the Northwind database view named >>> [Alphabetical list of products]: >>> >>> command.CommandText = "SELECT * FROM [Alphabetical list of products]"; >>> reader = cmd.ExecuteReader(CommandBehavior.KeyInfo); >>> schemaTable = reader.GetSchemaTable(); >>> >>> This view selects all 10 columns from Products and also CategoryName >>> from Categories (11 columns total). The tables are joined on >>> CategoryID. When I run the above code, it returns a schema table that >>> contains 12 rows--all 10 from Products, Categories.CategoryName and also >>> Categories.CategoryID. It is this last one that is causing problems for >>> me. CategoryID is appearing twice in my list (once for each table) even >>> though only one of them is in the SELECT. >>> >>> So my questions are: >>> >>> 1. Why is Categories.CategoryID getting into the schema table even >>> though it is not selected? >>> 2. Is there any way to prevent it from getting in there and having the >>> schema table only show the selected columns? >>> >>> For those who do not have the Northwind database handy, here's the >>> view's sql: >>> SELECT dbo.Products.ProductID, dbo.Products.ProductName, >>> dbo.Products.SupplierID, dbo.Products.CategoryID, >>> dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice, >>> dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder, >>> dbo.Products.ReorderLevel, dbo.Products.Discontinued, >>> dbo.Categories.CategoryName >>> FROM dbo.Categories >>> INNER JOIN dbo.Products ON dbo.Categories.CategoryID = >>> dbo.Products.CategoryID >>> WHERE (dbo.Products.Discontinued = 0) >>> >>> Thanks, >>> John >>> >> >> > > Thanks, Jesús. That's good to know. I'll keep that for future reference.
In my case now, however, modifying the views is not an option. John Show quote "Jesús López" <sqlranger.***@mpvs.org> wrote in message news:Ooq6V1W6GHA.3620@TK2MSFTNGP04.phx.gbl... > If you modify the view in the following form: > > ALTER VIEW TheView > WITH VIEW_METADATA > AS > SELECT .... > > Then GetSchemaTable will return only the fields in the select list. No > hidden fields will be returned. > > Regards: > > Jesús López > > > > > "John" <revelation@nospam.nospam> escribió en el mensaje > news:%23z%23gypI6GHA.4644@TK2MSFTNGP04.phx.gbl... >> Thank you, Robert. That makes sense. My problem is (was) that I need >> the key info that is provided in the table, but only for the selected >> columns. Through the magic of the IntelliSense Visualizers I just found >> the IsHidden column in the table. This appears to tell me whether or not >> the column was included in the select. That is what I needed. I can now >> ignore the hidden columns, which is what I need to do. Funny, IsHidden >> is not in my VS documentation, but it is on the MSDN site. >> >> Thanks for your reply, as it got me thinking and looking in the right >> direction. >> >> John >> >> "Robert Simpson" <rmsimpson@nospam.nospam> wrote in message >> news:OlZAUmE6GHA.4232@TK2MSFTNGP02.phx.gbl... >>> When you specify the CommandBehavior.KeyInfo option, Sql Server will >>> automatically return you the columns in your original select along with >>> the necessary additional columns needed to uniquely identify the rows >>> returned. >>> >>> For example, if you had this table: >>> >>> CREATE TABLE Foo (ID INT PRIMARY KEY, MyValue VARCHAR(50)) >>> >>> If you called ExecuteReader/GetSchemaTable on this query: >>> >>> SELECT MyValue FROM FOO >>> >>> Without KeyInfo, you'd only get MyValue when you called >>> GetSchemaTable(). However, if you specify KeyInfo, you'll get ID as well >>> because ID is the key for the given select clause and you specifically >>> asked for key info. >>> >>> Robert >>> >>> "John" <revelation@nospam.nospam> wrote in message >>> news:eFEQwRE6GHA.2044@TK2MSFTNGP02.phx.gbl... >>>> I've run into a problem I can't seem to explain or work around. I'm >>>> calling SqlDataReader.GetSchemaTable() on a database view (SQL Server >>>> 2005). It is returning rows not only for the columns in the view, but >>>> also for another column that is not ouput in the view. For example, I >>>> execute the following code on the Northwind database view named >>>> [Alphabetical list of products]: >>>> >>>> command.CommandText = "SELECT * FROM [Alphabetical list of products]"; >>>> reader = cmd.ExecuteReader(CommandBehavior.KeyInfo); >>>> schemaTable = reader.GetSchemaTable(); >>>> >>>> This view selects all 10 columns from Products and also CategoryName >>>> from Categories (11 columns total). The tables are joined on >>>> CategoryID. When I run the above code, it returns a schema table that >>>> contains 12 rows--all 10 from Products, Categories.CategoryName and >>>> also Categories.CategoryID. It is this last one that is causing >>>> problems for me. CategoryID is appearing twice in my list (once for >>>> each table) even though only one of them is in the SELECT. >>>> >>>> So my questions are: >>>> >>>> 1. Why is Categories.CategoryID getting into the schema table even >>>> though it is not selected? >>>> 2. Is there any way to prevent it from getting in there and having the >>>> schema table only show the selected columns? >>>> >>>> For those who do not have the Northwind database handy, here's the >>>> view's sql: >>>> SELECT dbo.Products.ProductID, dbo.Products.ProductName, >>>> dbo.Products.SupplierID, dbo.Products.CategoryID, >>>> dbo.Products.QuantityPerUnit, dbo.Products.UnitPrice, >>>> dbo.Products.UnitsInStock, dbo.Products.UnitsOnOrder, >>>> dbo.Products.ReorderLevel, dbo.Products.Discontinued, >>>> dbo.Categories.CategoryName >>>> FROM dbo.Categories >>>> INNER JOIN dbo.Products ON dbo.Categories.CategoryID = >>>> dbo.Products.CategoryID >>>> WHERE (dbo.Products.Discontinued = 0) >>>> >>>> Thanks, >>>> John >>>> >>> >>> >> >> > >  |
|||||||||||||||||||||||
Other interesting topics