Accessing more than: showing

Showing posts with label showing. Show all posts

Sunday, March 11, 2012

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=djq&as_ugroup=microsoft.public.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=djq&as_ugroup=microsoft.public.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.
> >
> >
>|||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=djq&as_ugroup=microsoft.public.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...
> > 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=djq&as_ugroup=microsoft.public.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.
> > >
> > >
> >
> >
>|||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=djq&as_ugroup=microsoft.public.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...
> > > 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=djq&as_ugroup=microsoft.public.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.
> > > >
> > > >
> > >
> > >
> >
> >
>|||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=djq&as_ugroup=microsoft.public.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...
> > 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=djq&as_ugroup=microsoft.public.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...
> > > > 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=djq&as_ugroup=microsoft.public.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,
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=djq&as_ugroup=microsoft.public.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...
> > > 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=djq&as_ugroup=microsoft.public.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...
> > > > > 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=djq&as_ugroup=microsoft.public.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.
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||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=djq&as_ugroup=microsoft.public.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...
> > 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=djq&as_ugroup=microsoft.public.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...
> > > > 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=djq&as_ugroup=microsoft.public.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...
> > > > > > 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=djq&as_ugroup=microsoft.public.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 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...
> > 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.
> >
> >
>

Add table to view: only showing (All Columns)

Thursday, February 9, 2012

Actual size of database

My database size is of 1500 mb and actual data is not of 1500 mb the data
space available is showing 100 mb , i have done dbcc checkbd on the database
but still the it isnot showing actual size there is one more commond to
resize the database can anbody tell me
Yousuf Khan
Programmer
Yousuf wrote:
> My database size is of 1500 mb and actual data is not of 1500 mb the
> data space available is showing 100 mb , i have done dbcc checkbd on
> the database but still the it isnot showing actual size there is one
> more commond to resize the database can anbody tell me
If you need to shrink the data of log files because they have grown much
too large for what your database requires, you can use DBCC SHRINKFILE.
Having extra space in the data and log files is a good idea as auto-grow
operations are very expesinsive.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Hi,
Use the below command to get the actual free space:-
For Data and Index
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
Based on the outcome you can shrink the MDF and LDF file seperately.
Steps:-
1. Backup the transaction log (Backup Log in books online)
2. Now shrink the files
dbcc shrinkfile('logical_mdf_name',Xsize)
3. Shrink the LDF file
dbcc shrinkfile('logical_ldf_name',Xsize)
4. After this check the size again
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
Thanks
Hari
SQL Server MVP
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:B062771E-F552-4EBE-8F6E-2E2F0BD359EA@.microsoft.com...
> My database size is of 1500 mb and actual data is not of 1500 mb the data
> space available is showing 100 mb , i have done dbcc checkbd on the
> database
> but still the it isnot showing actual size there is one more commond to
> resize the database can anbody tell me
> --
> Yousuf Khan
> Programmer
|||thanks hari
being in the current database
if use DBCC UPDATEUSAGE will it be ok
or shall i go as per your advise
Yousuf Khan
Programmer
"Hari Prasad" wrote:

> Hi,
> Use the below command to get the actual free space:-
> For Data and Index
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> Based on the outcome you can shrink the MDF and LDF file seperately.
>
> Steps:-
> 1. Backup the transaction log (Backup Log in books online)
> 2. Now shrink the files
> dbcc shrinkfile('logical_mdf_name',Xsize)
> 3. Shrink the LDF file
> dbcc shrinkfile('logical_ldf_name',Xsize)
> 4. After this check the size again
> use dbname
> go
> sp_spaceused @.updateusage='true'
>
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> --
> Thanks
> Hari
> SQL Server MVP
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:B062771E-F552-4EBE-8F6E-2E2F0BD359EA@.microsoft.com...
>
>

Actual size of database

My database size is of 1500 mb and actual data is not of 1500 mb the data
space available is showing 100 mb , i have done dbcc checkbd on the database
but still the it isnot showing actual size there is one more commond to
resize the database can anbody tell me
--
Yousuf Khan
ProgrammerYousuf wrote:
> My database size is of 1500 mb and actual data is not of 1500 mb the
> data space available is showing 100 mb , i have done dbcc checkbd on
> the database but still the it isnot showing actual size there is one
> more commond to resize the database can anbody tell me
If you need to shrink the data of log files because they have grown much
too large for what your database requires, you can use DBCC SHRINKFILE.
Having extra space in the data and log files is a good idea as auto-grow
operations are very expesinsive.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi,
Use the below command to get the actual free space:-
For Data and Index
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
Based on the outcome you can shrink the MDF and LDF file seperately.
Steps:-
1. Backup the transaction log (Backup Log in books online)
2. Now shrink the files
dbcc shrinkfile('logical_mdf_name',size)
3. Shrink the LDF file
dbcc shrinkfile('logical_ldf_name',size)
4. After this check the size again
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
--
Thanks
Hari
SQL Server MVP
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:B062771E-F552-4EBE-8F6E-2E2F0BD359EA@.microsoft.com...
> My database size is of 1500 mb and actual data is not of 1500 mb the data
> space available is showing 100 mb , i have done dbcc checkbd on the
> database
> but still the it isnot showing actual size there is one more commond to
> resize the database can anbody tell me
> --
> Yousuf Khan
> Programmer|||thanks hari
being in the current database
if use DBCC UPDATEUSAGE will it be ok
or shall i go as per your advise
Yousuf Khan
Programmer
"Hari Prasad" wrote:
> Hi,
> Use the below command to get the actual free space:-
> For Data and Index
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> Based on the outcome you can shrink the MDF and LDF file seperately.
>
> Steps:-
> 1. Backup the transaction log (Backup Log in books online)
> 2. Now shrink the files
> dbcc shrinkfile('logical_mdf_name',Âsize)
> 3. Shrink the LDF file
> dbcc shrinkfile('logical_ldf_name',Âsize)
> 4. After this check the size again
> use dbname
> go
> sp_spaceused @.updateusage='true'
>
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> --
> Thanks
> Hari
> SQL Server MVP
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:B062771E-F552-4EBE-8F6E-2E2F0BD359EA@.microsoft.com...
> > My database size is of 1500 mb and actual data is not of 1500 mb the data
> > space available is showing 100 mb , i have done dbcc checkbd on the
> > database
> > but still the it isnot showing actual size there is one more commond to
> > resize the database can anbody tell me
> > --
> > Yousuf Khan
> > Programmer
>
>

Actual size of database

My database size is of 1500 mb and actual data is not of 1500 mb the data
space available is showing 100 mb , i have done dbcc checkbd on the database
but still the it isnot showing actual size there is one more commond to
resize the database can anbody tell me
--
Yousuf Khan
ProgrammerYousuf wrote:
> My database size is of 1500 mb and actual data is not of 1500 mb the
> data space available is showing 100 mb , i have done dbcc checkbd on
> the database but still the it isnot showing actual size there is one
> more commond to resize the database can anbody tell me
If you need to shrink the data of log files because they have grown much
too large for what your database requires, you can use DBCC SHRINKFILE.
Having extra space in the data and log files is a good idea as auto-grow
operations are very expesinsive.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Hi,
Use the below command to get the actual free space:-
For Data and Index
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
Based on the outcome you can shrink the MDF and LDF file seperately.
Steps:-
1. Backup the transaction log (Backup Log in books online)
2. Now shrink the files
dbcc shrinkfile('logical_mdf_name',_size)
3. Shrink the LDF file
dbcc shrinkfile('logical_ldf_name',_size)
4. After this check the size again
use dbname
go
sp_spaceused @.updateusage='true'
For Transaction log
DBCC SQLPERF(LOGSPACE)
Thanks
Hari
SQL Server MVP
"Yousuf" <yousuf.yk@.gmail.com> wrote in message
news:B062771E-F552-4EBE-8F6E-2E2F0BD359EA@.microsoft.com...
> My database size is of 1500 mb and actual data is not of 1500 mb the data
> space available is showing 100 mb , i have done dbcc checkbd on the
> database
> but still the it isnot showing actual size there is one more commond to
> resize the database can anbody tell me
> --
> Yousuf Khan
> Programmer|||thanks hari
being in the current database
if use DBCC UPDATEUSAGE will it be ok
or shall i go as per your advise
Yousuf Khan
Programmer
"Hari Prasad" wrote:

> Hi,
> Use the below command to get the actual free space:-
> For Data and Index
> use dbname
> go
> sp_spaceused @.updateusage='true'
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> Based on the outcome you can shrink the MDF and LDF file seperately.
>
> Steps:-
> 1. Backup the transaction log (Backup Log in books online)
> 2. Now shrink the files
> dbcc shrinkfile('logical_mdf_name',_size)
> 3. Shrink the LDF file
> dbcc shrinkfile('logical_ldf_name',_size)
> 4. After this check the size again
> use dbname
> go
> sp_spaceused @.updateusage='true'
>
> For Transaction log
> DBCC SQLPERF(LOGSPACE)
> --
> Thanks
> Hari
> SQL Server MVP
> "Yousuf" <yousuf.yk@.gmail.com> wrote in message
> news:B062771E-F552-4EBE-8F6E-2E2F0BD359EA@.microsoft.com...
>
>

Activity Monitor - host entries blank for sql login ?

The activity monitor is not showing entries for hosts which connect via sql logins (odbc), is this normal? Where can I find a list of protocols in which the hostname/ip can be picked up. We're on SQL 2005 SP1/Build 2153

Thanks

The hostname should show up if the user is connecting from a Windows box using ODBC or OLE-DB. If you are using a JDBC driver or comming from a unix machine this field is typically blank. This can be adjusted by adding the "Workstation ID" paramater to the connection string and setting it to equal the client machines host name. This object probably doesn't exist on all drivers. The string name may also be slightly different so some trial and error may be required.|||

The connections in question are from Windows OS (2000/XP) machines, no JDBC or UNIX involved......

Sunday, March 11, 2012

Add table to view: only showing (All Columns)

Add table to view: only showing (All Columns)

Thursday, February 9, 2012

Actual size of database

Actual size of database

Actual size of database

Activity Monitor - host entries blank for sql login ?

Accessing more than

Blog Archive

About Me