INFORMATION_SCHEMA.COLUMNS query

Hi,
I'm trying to run the following query but keep getting an error
returned:
SELECT COUNT(*)FROM
[sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '[table_name]'
Error:
Server: Msg 7314, Level 16, State 1, Line 2
OLE DB provider '[sever_name]' does not contain table
'"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
does not exist or the current user does not have permissions on that
table.
OLE DB error trace [Non-interface error: OLE DB provider does not
contain the table: ProviderName='[sever_name]',
TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
The table does exist and the other server has been added to the
sysservers table and works as other queries can be run. I've run the
exact code on other sql servers and it works without any problems. Any
help to run this query would be much appreciated.
Thanks
SimonAFAIK, you can't use the information_schema views in SQL Server 2000 from a linked server as they
don't exists in each database, they only exist in the master database. Use syscolumns etc instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1129802634.977349.10700@.g14g2000cwa.googlegroups.com...
> Hi,
> I'm trying to run the following query but keep getting an error
> returned:
> SELECT COUNT(*)FROM
> [sever_name].[database_name].INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = '[table_name]'
> Error:
> Server: Msg 7314, Level 16, State 1, Line 2
> OLE DB provider '[sever_name]' does not contain table
> '"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'. The table either
> does not exist or the current user does not have permissions on that
> table.
> OLE DB error trace [Non-interface error: OLE DB provider does not
> contain the table: ProviderName='[sever_name]',
> TableName='"[database_name]"."INFORMATION_SCHEMA"."COLUMNS"'].
> The table does exist and the other server has been added to the
> sysservers table and works as other queries can be run. I've run the
> exact code on other sql servers and it works without any problems. Any
> help to run this query would be much appreciated.
> Thanks
> Simon
>

INFORMATION_SCHEMA.COLUMNS error

Hi All,

For some reason, whenever I execute the following query:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

I get a few rows returned then I get the following error:

Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.

Is my INFORMATION_SCHEMA.COLUMNS view corrupt somehow?

Querries of other INFORMATION_SCHEMA views are fine, just this one is returning an error. Anyone have any ideas about how to fix this?

John

Can you post the table structure..|||

Manivannan.D.Sekaran wrote:

Can you post the table structure..

The table structure of what? The INFORMATION_SCHEMA.COLUMNS view? This is the default INFORMATION_SCHEMA.COLUMNS view that is created by default in SQL Server. Its what should be there by default. Its not a view that I've created.

So what table structure are you referring to?

John

|||

Ohh yes.. You dont know which table cause the problem.. Simply forget..

Ok try to execute the following query..

At one time you will get an error.. So that is the table causing the issue... Then post the DDL of that table...

sp_msforeachtable'Print ''? is Executing..'';Select Count(Column_Name) TotalColumn, ''?'' TableName fromINFORMATION_SCHEMA.COLUMNS Where object_id(table_name) = object_id(''?'');Print ''? is Completed..'';'

|||

Hi Manivannan,

Ok I executed your query. I didn't get any errors.

I think the issue is with the view in general on my server. I get the error when I execute the query against INFORMATION_SCHEMA.COLUMNS regardless of the database I execute against.

When I execute your script against ALL of my databases (on my server) I get no errors.

|||

Here's a little background on what I think may have caused my server to be in such a state.

I have been tasked with changing all columns, in all tables, in our database that are of varchar type to be nvarchar. I immediately issued a query such as

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE='varchar'

That very simply returned ALL columns in my database that were of varchar type, I think there were hundreds of rows returned. So, I think this is what screwed things up for me, I executed an update statement against that view as a test to see if I could do such a thing. This is the update statement I executed:

UPDATE INFORMATION_SCHEMA.COLUMNS SET DATA_TYPE='nvarchar' WHERE DATA_TYPE='varchar'

I figured something was wrong when upon executing that statement it said that 1 row was updated. I got no error message, but now all querries of that view yields the error I documented.

|||

Its really worst idea changing the system tables directly. You should use the proper alter statement.

The update query you performed, is modified the data in System Table (spt_datatype_info) rather than your original table (on syscolumns).

If you only executed the above query then use the following query to revert back spt_datatype_info into original state..

UPDATE spt_datatype_info

SET local_type_name=type_name

WHERE local_type_name='varchar'

|||

WooHoo, it worked!

I changed your update statement slightly to:

UPDATE spt_datatype_info
SET local_type_name=type_name
WHERE local_type_name!=type_name

It restored all that were messed up (there were two rows actually, the row for "varchar" and the row for "text").

Thank you very much Manivannan!

John

sql

Info about Error 8623

Hi,

SQL Server returned me error 8623 "Internal Query Processor Error: The query processor could not produce a query plan." I've looked for info in the SQL books, but there's no info about this error. I would appreciate it a lot if somebody could tell me what this error means, how is it triggered, and if it's possible, how to avoid it.

Thanks a lot,

FedericoWhere are you doing this?

What's the query?

It's dynamic, isn't it...|||It would be better if you quote the query involved as per Brett's reply.
This KBA1 (http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;818729) and KBA2 (http://support.microsoft.com/default.aspx?scid=kb;EN-US;286255) refers about 863 error.

HTH|||I can't reproduce this error. So I think it's sporadyc. Anyway I'll check my SELECT clauses to see if there's something similar to what Satya's link mention. Again, thanks a lot|||PROFILER may help you to track the process of query against database, just in case if you don't know.