MS SQL JOIN query not returning all results

[David J Smith]

Hi,

I am using the Microsoft SQL Server JDBC Driver 2.0.
I have the following query

SELECT tbl1.DATA_TYPE AS DATA_TYPE, tbl1.TABLE_NAME AS TABLE_NAME, tbl1.COLUMN_NAME AS COLUMN_NAME, 
tbl1.ORDINAL_POSITION AS ORDINAL_POSITION, tbl1.IS_NULLABLE AS IS_NULLABLE, tbl2.COLUMN_NAME AS COLUMN_ID 
FROM INFORMATION_SCHEMA.COLUMNS AS tbl1 JOIN INFORMATION_SCHEMA.COLUMNS AS tbl2 
ON tbl1.ORDINAL_POSITION = tbl2.ORDINAL_POSITION 
WHERE tbl1.TABLE_NAME = 'Table1' 
AND tbl2.TABLE_NAME = 'Table2'  

When I run this in SQL Server Management Studio I get 176 rows returned, however when I run it using the JDBC Driver with the following command

  ResultSet rs = stmt.executeQuery(query.toString());  

I only get 24 rows.

I am completely stuck, any help would be greatly appreciated.

David.

[David J Smith]

By the way, Table1 is a view, not sure if this makes any difference?

[Scott Selikoff]

Try putting the username in front of the table name, sometimes SQL server filters by user.

[David J Smith]

I'm logging into the server using the sa user so it should show everything no?