INFORMATION_SCHEMA question

microsoft.public.sqlserver.programming

22 Dec 2005 7:02 AM

Jeff User

Hi
I am selecting some column info from information_schema like this:
SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE
FROM test.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Test Table';

I am doing this via OleDb connection to Sql2000
I connect as the owner of the database/tables.
I connect to the database for which I am seeking the information
('test').

I get rows returned for some tables, but not others. At first, when I
test in query analyzer (connected as same user) I got the same
results. So then, I went to Enterprise Manager and looked at
permissions for the tables for which I was getting no data returned.
There were no permissions on the tables at all. If I added, just/only
SELECT permission for this user, then I get results in query analyzer.

Ah ha, I think, I have it! So now I go back to my application and try
again with the OleDb connection and, well, it works for some tables
now (for which it did not earlier) , but there are still other tables
that , although they now return column information in Query Analyser,
still do not return any column information from my OleDb connection
query.

Is this a permissions issue with the table itself?
What else should I be looking for in my database that could be
preventing this column information from being returned to the user via
the connection? Is it the connection?

Thanks
Jeff