MS SQL Server - Information

Author

Information_Schema Question

Jeff User

2005-12-22, 3:23 am

Hi
I am selecting some column info from information_schema like this:
SELECT COLUMN_NAME,DATA_TYP
E,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

Jeff User

2005-12-28, 3:23 am

Nobody has any idea what I might be missing here?

Jeff

On Thu, 22 Dec 2005 06:58:10 GMT, Jeff User <jeff31162@hotmail.com>
wrote:

>Hi
>I am selecting some column info from information_schema like this:
>SELECT COLUMN_NAME,DATA_TYP
E,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

Sponsored Links