Different results when querying VIEW from QA and code

[Gary Varga]

I have a VIEW as follows:

CREATE VIEW dbo.System_Data_Dictionary_Tables
AS
SELECT INFORMATION_SCHEMA.TABLES.TABLE_NAME AS
Table_Name,
(SELECT REPLACE(CAST
(sysproperties.value AS NVARCHAR(255)), ',', ';')
FROM sysproperties
WHERE ((sysobjects.id =
sysproperties.id) AND (sysproperties.type = 3))) AS
Description
FROM dbo.sysobjects INNER JOIN
INFORMATION_SCHEMA.TABLES ON dbo.sysobjects.name =
INFORMATION_SCHEMA.TABLES.TABLE_NAME
WHERE ((TABLE_TYPE = 'BASE TABLE') AND
(INFORMATION_SCHEMA.TABLES.TABLE_NAME<>'dtproperties'))

This basically creates a rowset of the tables with the
description field from the properties.

When I "Return all rows" from Enterprise Manager or
run "SELECT * FROM System_Data_Dictionary_Tables" in
Query Analyser I get all the tables I expect including
those with NULL descriptions.

However, when I run "SELECT * FROM
System_Data_Dictionary_Tables" using ADO or ADO.NET I
only get one row returned. This row is not the first row
created nor the last. I cannot think of any way it is
particularly unique.

Many thanks,
Gary Varga

 

[Kevin Yu [MSFT]]

Hi Gary,

I'm not sure how this happens since there is not adequante information.
Would you please check if you have set any filters when retrieving data
using ADO or ADO .net? Can you post some of your codes here?

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

--------------------
| Content-Class: urn:content-classes:message
| From: "Gary Varga" <[email protected]>
| Sender: "Gary Varga" <[email protected]>
| Subject: Different results when querying VIEW from QA and code
| Date: Mon, 20 Oct 2003 04:06:46 -0700
| Lines: 34
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcOW+kBAzPcq5KebTpCHqCBkjCPOvg==
| Newsgroups: microsoft.public.dotnet.framework.adonet
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.framework.adonet:64046
| NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
| X-Tomcat-NG: microsoft.public.dotnet.framework.adonet
|
| I have a VIEW as follows:
|
| CREATE VIEW dbo.System_Data_Dictionary_Tables
| AS
| SELECT INFORMATION_SCHEMA.TABLES.TABLE_NAME AS
| Table_Name,
| (SELECT REPLACE(CAST
| (sysproperties.value AS NVARCHAR(255)), ',', ';')
| FROM sysproperties
| WHERE ((sysobjects.id =
| sysproperties.id) AND (sysproperties.type = 3))) AS
| Description
| FROM dbo.sysobjects INNER JOIN
| INFORMATION_SCHEMA.TABLES ON dbo.sysobjects.name =
| INFORMATION_SCHEMA.TABLES.TABLE_NAME
| WHERE ((TABLE_TYPE = 'BASE TABLE') AND
| (INFORMATION_SCHEMA.TABLES.TABLE_NAME<>'dtproperties'))
|
| This basically creates a rowset of the tables with the
| description field from the properties.
|
| When I "Return all rows" from Enterprise Manager or
| run "SELECT * FROM System_Data_Dictionary_Tables" in
| Query Analyser I get all the tables I expect including
| those with NULL descriptions.
|
| However, when I run "SELECT * FROM
| System_Data_Dictionary_Tables" using ADO or ADO.NET I
| only get one row returned. This row is not the first row
| created nor the last. I cannot think of any way it is
| particularly unique.
|
| Many thanks,
| Gary Varga
|