Add table to view: only showing (All Columns)

Hi all,
I have a database created a few years ago by another team.It has gone
through upgrades and is currently SQL Server 2000 SP3 on Windows 2000 SP4.
It is performing normally, with one annoying exception:
When I use Enterprise Manager's view designer to create a new view or look
at an existing view, the tables always show only 1 line: * (All Columns).
The individual column names are not showing.This is true for all tables and
views, including a new test table I just created.
The problem only occurs with this database. It also occurs on other servers.
DBCC CHECKDB did not report anything unusual.
What can I do to make the column names appear again? Please let me know if
I should report any other information to diagnose this issue.
Thanks,
-Tom.
P.S.:
I *can* force columns to show with a syntax like this:
SELECT *
FROM (SELECT Col1, Col2
FROM tblTest) DERIVEDTBL
but that's hardly what I should have to do.Is it only the view/query designer which has this problem? Perhaps there's
some strange database setting in play here... What is the compatibility
level of the database?
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
news:1027hviq64k8e35@.corp.supernews.com...
> Hi all,
> I have a database created a few years ago by another team.It has gone
> through upgrades and is currently SQL Server 2000 SP3 on Windows 2000 SP4.
> It is performing normally, with one annoying exception:
> When I use Enterprise Manager's view designer to create a new view or look
> at an existing view, the tables always show only 1 line: * (All Columns).
> The individual column names are not showing.This is true for all tables
and
> views, including a new test table I just created.
> The problem only occurs with this database. It also occurs on other
servers.
> DBCC CHECKDB did not report anything unusual.
> What can I do to make the column names appear again? Please let me know
if
> I should report any other information to diagnose this issue.
> Thanks,
> -Tom.
> P.S.:
> I *can* force columns to show with a syntax like this:
> SELECT *
> FROM (SELECT Col1, Col2
> FROM tblTest) DERIVEDTBL
> but that's hardly what I should have to do.
>|||Hi Tibor,
"only"? What other viewer are you referring to? The Diagrams are normal.
Compatibility level is 80 for all databases, as reported by sp_helpdb.
-Tom.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ODekOPN7DHA.2392@.TK2MSFTNGP11.phx.gbl...
> Is it only the view/query designer which has this problem? Perhaps there's
> some strange database setting in play here... What is the compatibility
> level of the database?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
> news:1027hviq64k8e35@.corp.supernews.com...
SP4.
look
Columns).
> and
> servers.
> if
>|||No viewer in particular, just other GUI tools in general. I'm trying to
pinpoint what might be the problem. So, you are saying that you run EM on a
machine. From that machine, the query builder doesn't show the column names
for the tables in one of your databases, even if you create new tables. But
of you create another database and create tables in that database, you will
see the column in that. Is this a correct description?
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
news:1027j8k4od20t23@.corp.supernews.com...
> Hi Tibor,
> "only"? What other viewer are you referring to? The Diagrams are normal.
> Compatibility level is 80 for all databases, as reported by sp_helpdb.
> -Tom.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:ODekOPN7DHA.2392@.TK2MSFTNGP11.phx.gbl...
there's
>
http://groups.google.com/groups?oi=...ublic.sqlserver
message
> SP4.
> look
> Columns).
tables
know
>|||Yes, that's correct. But note that I created this db from a backup from my
ISP's SQL Server. I'm assuming that the imperfections came with the backup
and are not native to my Server. At the ISP they see the same behavior.
Additionally, if I use an Access 2000 ADP to create a view or inspect an
existing one, I get the same behavior.
-Tom.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23eqDG$N7DHA.712@.tk2msftngp13.phx.gbl...
> No viewer in particular, just other GUI tools in general. I'm trying to
> pinpoint what might be the problem. So, you are saying that you run EM on
a
> machine. From that machine, the query builder doesn't show the column
names
> for the tables in one of your databases, even if you create new tables.
But
> of you create another database and create tables in that database, you
will
> see the column in that. Is this a correct description?
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
> news:1027j8k4od20t23@.corp.supernews.com...
normal.
> in
> there's
compatibility
>
http://groups.google.com/groups?oi=...ublic.sqlserver
> message
gone
2000
or
> tables
> know
>|||OK, the next thing I'd do is to check all the database options and see if
there are any differences between a "working" database and this one.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
news:1027pld4sts7g4e@.corp.supernews.com...
> Yes, that's correct. But note that I created this db from a backup from my
> ISP's SQL Server. I'm assuming that the imperfections came with the backup
> and are not native to my Server. At the ISP they see the same behavior.
> Additionally, if I use an Access 2000 ADP to create a view or inspect an
> existing one, I get the same behavior.
> -Tom.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:%23eqDG$N7DHA.712@.tk2msftngp13.phx.gbl...
on
> a
> names
> But
> will
>
http://groups.google.com/groups?oi=...ublic.sqlserver
message
> normal.
wrote
> compatibility
>
http://groups.google.com/groups?oi=...ublic.sqlserver
> gone
> 2000
> or
other
me
>|||Hi Tibor,
Thanks for hanging in there with me. You are making some good suggestions.
Not much difference in database options. I ran this:
sp_dboption 'arizonavacationvalues.com'
go
sp_dboption 'pubs'
results:
torn page detection
auto create statistics
auto update statistics
trunc. log on chkpt.
torn page detection
auto create statistics
auto update statistics
-Tom.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O6A2oYO7DHA.1040@.TK2MSFTNGP10.phx.gbl...
> OK, the next thing I'd do is to check all the database options and see if
> there are any differences between a "working" database and this one.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
>
> "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
> news:1027pld4sts7g4e@.corp.supernews.com...
my
backup
> in
to
> on
tables.
>
http://groups.google.com/groups?oi=...ublic.sqlserver
> message
sp_helpdb.
> wrote
>
http://groups.google.com/groups?oi=...ublic.sqlserver
view
> other
> me
>|||I had this happen several years ago. It turned out that someone had put a
stored procedure in the database that was the same name as the stored
procedure that Enterprise Manager uses to get the Field Names. Since SQL
will pull the stored proc from the current database before it tries the
master, it was messing up Enterprise Manager. I'm sorry, I don't recall the
name of the stored proc, but maybe this will give you a starting place.
"Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
news:1027hviq64k8e35@.corp.supernews.com...
> Hi all,
> I have a database created a few years ago by another team.It has gone
> through upgrades and is currently SQL Server 2000 SP3 on Windows 2000 SP4.
> It is performing normally, with one annoying exception:
> When I use Enterprise Manager's view designer to create a new view or look
> at an existing view, the tables always show only 1 line: * (All Columns).
> The individual column names are not showing.This is true for all tables
and
> views, including a new test table I just created.
> The problem only occurs with this database. It also occurs on other
servers.
> DBCC CHECKDB did not report anything unusual.
> What can I do to make the column names appear again? Please let me know
if
> I should report any other information to diagnose this issue.
> Thanks,
> -Tom.
> P.S.:
> I *can* force columns to show with a syntax like this:
> SELECT *
> FROM (SELECT Col1, Col2
> FROM tblTest) DERIVEDTBL
> but that's hardly what I should have to do.
>|||Hmm, I think I'm out of ideas, then (or I need to get some sleep). However,
Joe has a very intriguing theory!
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
news:1027u48lpb6iac4@.corp.supernews.com...
> Hi Tibor,
> Thanks for hanging in there with me. You are making some good suggestions.
> Not much difference in database options. I ran this:
> sp_dboption 'arizonavacationvalues.com'
> go
> sp_dboption 'pubs'
> results:
> torn page detection
> auto create statistics
> auto update statistics
> trunc. log on chkpt.
> torn page detection
> auto create statistics
> auto update statistics
> -Tom.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:O6A2oYO7DHA.1040@.TK2MSFTNGP10.phx.gbl...
if
>
http://groups.google.com/groups?oi=...ublic.sqlserver
message
from
> my
> backup
behavior.
an
wrote
> to
EM
column
> tables.
you
>
http://groups.google.com/groups?oi=...ublic.sqlserver
> sp_helpdb.
Perhaps
>
http://groups.google.com/groups?oi=...ublic.sqlserver
in
has
Windows
> view
(All
all
let
>|||Hi Joe,
Thanks for this neat idea.
Alas:
select * from sysobjects
where [name] in (select [name] from master..sysobjects)
only results in 21 sysxxxx tables.
-Tom.
"Joe Jackson" <jj@.microsoft.com> wrote in message
news:uNoPTyP7DHA.1636@.TK2MSFTNGP12.phx.gbl...
> I had this happen several years ago. It turned out that someone had put a
> stored procedure in the database that was the same name as the stored
> procedure that Enterprise Manager uses to get the Field Names. Since SQL
> will pull the stored proc from the current database before it tries the
> master, it was messing up Enterprise Manager. I'm sorry, I don't recall
the
> name of the stored proc, but maybe this will give you a starting place.
>
> "Tom van Stiphout" <no.spam.tvanstiphout@.kinetik-it.com> wrote in message
> news:1027hviq64k8e35@.corp.supernews.com...
SP4.
look
Columns).
> and
> servers.
> if
>