Adding a new Column after a particular column

Hi,
I have a table, and i have say 3 columns, say c1,c2,c3 . Now i want to insert one more column say c2a after the column c2.How to do this, i tried with all possible ways, i cldn do, in mysql, the "AFTER" clause is ther, so i did with that, but in
MS Sql , i cdln make it...
Help me..\

ThanksStrange request, but if you absolutely have to put your new column between 2 others, then you will have to:

1. make a back up of your table
2. select * into a temp table
3. drop your table
4. create new table with cols in order you want
5. select * from temp table into your new table
6. drop temp table

happy SQLing

Adding a column to VLDB 200GB table

The column I'm adding needs to be part of the clustered PK (it will be the
last of three columns) so I need to recreate all the indexes.
My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've tried
two methods so far.
Method 1:
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_copyoftablewithnewfield
(
) ON PRIMARY
IF EXISTS(SELECT * FROM dbo.originaltable)
EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields>)
SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.originaltable
GO
EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
'OBJECT'
GO
<recreate PK constraint>
<rebuild indexes>
COMMIT
Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
Con's: Tons of wasted space and time. It took about 15 hours.
Method 2:
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
<drop PK constraint>
<drop indexes>
ALTER TABLE [dbo].[originaltable] ADD
[newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfield] DEFAULT
((1))
<recreate PK constraint>
<rebuild indexes>
COMMIT TRANSACTION
Pro's: No making a copy of the entire table taking up 200GB more space in
the db data file
Con's: My tempdb grew to accomodate the row versioning info for every row in
the 200GB table. It took over 30 hours.
A lot of time and disk space is wasted with both.
Since the db is going to be unavailable to users I have some flexibility
here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then trying
method 2 again which should stop the versioning in tempdb and then turning it
back on.
I was also curious if setting the database recovery mode to SIMPLE would cut
down on db log usage and then I could set it back to FULL when done.
Do these really need to be in a transaction? If there's some hardware
failure or something unexpected I can just restore from backup and do the
conversion again. If the presence of the transaction itself is causing more
disk usage for logging or any other slowdown, I think I'd rather do without.
Given the amount of time this conversion takes, I wanted to get some
feedback other than "just try it" before doing any new tests.
Thanks.Demi
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
It is always worth taking backup of the database or snapshot as you are in
SQL Server 2005.
I'd use ALTER TABLE... (set it up with SIMPLE and ALLOW_SNAPSHOT_ISOLATION
OFF ) approach and there is no need BEGIN TRAN ....
"Demi" <Demi@.discussions.microsoft.com> wrote in message
news:D0A356E6-BFB7-498F-B5FA-871B6D3AFD75@.microsoft.com...
> The column I'm adding needs to be part of the clustered PK (it will be the
> last of three columns) so I need to recreate all the indexes.
> My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've
> tried
> two methods so far.
> Method 1:
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_copyoftablewithnewfield
> (
> ) ON PRIMARY
> IF EXISTS(SELECT * FROM dbo.originaltable)
> EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields>)
> SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK
> TABLOCKX)')
> GO
> DROP TABLE dbo.originaltable
> GO
> EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
> 'OBJECT'
> GO
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT
> Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
> Con's: Tons of wasted space and time. It took about 15 hours.
> Method 2:
> SET XACT_ABORT ON
> GO
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> BEGIN TRANSACTION
> <drop PK constraint>
> <drop indexes>
> ALTER TABLE [dbo].[originaltable] ADD
> [newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfield]
> DEFAULT
> ((1))
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT TRANSACTION
> Pro's: No making a copy of the entire table taking up 200GB more space in
> the db data file
> Con's: My tempdb grew to accomodate the row versioning info for every row
> in
> the 200GB table. It took over 30 hours.
> A lot of time and disk space is wasted with both.
> Since the db is going to be unavailable to users I have some flexibility
> here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then
> trying
> method 2 again which should stop the versioning in tempdb and then turning
> it
> back on.
> I was also curious if setting the database recovery mode to SIMPLE would
> cut
> down on db log usage and then I could set it back to FULL when done.
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
> Given the amount of time this conversion takes, I wanted to get some
> feedback other than "just try it" before doing any new tests.
> Thanks.sql

Adding a column to VLDB 200GB table

The column I'm adding needs to be part of the clustered PK (it will be the
last of three columns) so I need to recreate all the indexes.
My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've tried
two methods so far.
Method 1:
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_copyoftablewithnewfield
(
) ON PRIMARY
IF EXISTS(SELECT * FROM dbo.originaltable)
EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields>)
SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.originaltable
GO
EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
'OBJECT'
GO
<recreate PK constraint>
<rebuild indexes>
COMMIT
Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
Con's: Tons of wasted space and time. It took about 15 hours.
Method 2:
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
<drop PK constraint>
<drop indexes>
ALTER TABLE [dbo].[originaltable] ADD
[newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfield] DEFAULT
((1))
<recreate PK constraint>
<rebuild indexes>
COMMIT TRANSACTION
Pro's: No making a copy of the entire table taking up 200GB more space in
the db data file
Con's: My tempdb grew to accomodate the row versioning info for every row in
the 200GB table. It took over 30 hours.
A lot of time and disk space is wasted with both.
Since the db is going to be unavailable to users I have some flexibility
here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then trying
method 2 again which should stop the versioning in tempdb and then turning it
back on.
I was also curious if setting the database recovery mode to SIMPLE would cut
down on db log usage and then I could set it back to FULL when done.
Do these really need to be in a transaction? If there's some hardware
failure or something unexpected I can just restore from backup and do the
conversion again. If the presence of the transaction itself is causing more
disk usage for logging or any other slowdown, I think I'd rather do without.
Given the amount of time this conversion takes, I wanted to get some
feedback other than "just try it" before doing any new tests.
Thanks.
Demi
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
It is always worth taking backup of the database or snapshot as you are in
SQL Server 2005.
I'd use ALTER TABLE... (set it up with SIMPLE and ALLOW_SNAPSHOT_ISOLATION
OFF ) approach and there is no need BEGIN TRAN ....
"Demi" <Demi@.discussions.microsoft.com> wrote in message
news:D0A356E6-BFB7-498F-B5FA-871B6D3AFD75@.microsoft.com...
> The column I'm adding needs to be part of the clustered PK (it will be the
> last of three columns) so I need to recreate all the indexes.
> My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've
> tried
> two methods so far.
> Method 1:
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_copyoftablewithnewfield
> (
> ) ON PRIMARY
> IF EXISTS(SELECT * FROM dbo.originaltable)
> EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields>)
> SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK
> TABLOCKX)')
> GO
> DROP TABLE dbo.originaltable
> GO
> EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
> 'OBJECT'
> GO
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT
> Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
> Con's: Tons of wasted space and time. It took about 15 hours.
> Method 2:
> SET XACT_ABORT ON
> GO
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> BEGIN TRANSACTION
> <drop PK constraint>
> <drop indexes>
> ALTER TABLE [dbo].[originaltable] ADD
> [newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfield]
> DEFAULT
> ((1))
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT TRANSACTION
> Pro's: No making a copy of the entire table taking up 200GB more space in
> the db data file
> Con's: My tempdb grew to accomodate the row versioning info for every row
> in
> the 200GB table. It took over 30 hours.
> A lot of time and disk space is wasted with both.
> Since the db is going to be unavailable to users I have some flexibility
> here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then
> trying
> method 2 again which should stop the versioning in tempdb and then turning
> it
> back on.
> I was also curious if setting the database recovery mode to SIMPLE would
> cut
> down on db log usage and then I could set it back to FULL when done.
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
> Given the amount of time this conversion takes, I wanted to get some
> feedback other than "just try it" before doing any new tests.
> Thanks.

Adding a column to VLDB 200GB table

The column I'm adding needs to be part of the clustered PK (it will be the
last of three columns) so I need to recreate all the indexes.
My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've tried
two methods so far.
Method 1:
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_copyoftablewithnewfield
(
) ON PRIMARY
IF EXISTS(SELECT * FROM dbo.originaltable)
EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields> )
SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.originaltable
GO
EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
'OBJECT'
GO
<recreate PK constraint>
<rebuild indexes>
COMMIT
Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
Con's: Tons of wasted space and time. It took about 15 hours.
Method 2:
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
<drop PK constraint>
<drop indexes>
ALTER TABLE [dbo].[originaltable] ADD
[newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_newfi
eld] DEFAULT
((1))
<recreate PK constraint>
<rebuild indexes>
COMMIT TRANSACTION
Pro's: No making a copy of the entire table taking up 200GB more space in
the db data file
Con's: My tempdb grew to accomodate the row versioning info for every row in
the 200GB table. It took over 30 hours.
A lot of time and disk space is wasted with both.
Since the db is going to be unavailable to users I have some flexibility
here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then tryin
g
method 2 again which should stop the versioning in tempdb and then turning i
t
back on.
I was also curious if setting the database recovery mode to SIMPLE would cut
down on db log usage and then I could set it back to FULL when done.
Do these really need to be in a transaction? If there's some hardware
failure or something unexpected I can just restore from backup and do the
conversion again. If the presence of the transaction itself is causing more
disk usage for logging or any other slowdown, I think I'd rather do without.
Given the amount of time this conversion takes, I wanted to get some
feedback other than "just try it" before doing any new tests.
Thanks.Demi
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
It is always worth taking backup of the database or snapshot as you are in
SQL Server 2005.
I'd use ALTER TABLE... (set it up with SIMPLE and ALLOW_SNAPSHOT_ISOLATION
OFF ) approach and there is no need BEGIN TRAN ....
"Demi" <Demi@.discussions.microsoft.com> wrote in message
news:D0A356E6-BFB7-498F-B5FA-871B6D3AFD75@.microsoft.com...
> The column I'm adding needs to be part of the clustered PK (it will be the
> last of three columns) so I need to recreate all the indexes.
> My DB is set for FULL recovery mode ALLOW_SNAPSHOT_ISOLATION ON. I've
> tried
> two methods so far.
> Method 1:
> BEGIN TRANSACTION
> CREATE TABLE dbo.Tmp_copyoftablewithnewfield
> (
> ) ON PRIMARY
> IF EXISTS(SELECT * FROM dbo.originaltable)
> EXEC('INSERT INTO dbo.Tmp_copyoftablewithnewfield (<original fields> )
> SELECT <original fields> FROM dbo.originaltable WITH (HOLDLOCK
> TABLOCKX)')
> GO
> DROP TABLE dbo.originaltable
> GO
> EXECUTE sp_rename N'dbo.Tmp_copyoftablewithnewfield', N'originaltable',
> 'OBJECT'
> GO
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT
> Pro's: Lets me add the new field in the spot I'd like it (not a big deal)
> Con's: Tons of wasted space and time. It took about 15 hours.
> Method 2:
> SET XACT_ABORT ON
> GO
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> GO
> BEGIN TRANSACTION
> <drop PK constraint>
> <drop indexes>
> ALTER TABLE [dbo].[originaltable] ADD
> [newfield] [tinyint] NOT NULL CONSTRAINT [DF_originaltable_new
field]
> DEFAULT
> ((1))
> <recreate PK constraint>
> <rebuild indexes>
> COMMIT TRANSACTION
> Pro's: No making a copy of the entire table taking up 200GB more space in
> the db data file
> Con's: My tempdb grew to accomodate the row versioning info for every row
> in
> the 200GB table. It took over 30 hours.
> A lot of time and disk space is wasted with both.
> Since the db is going to be unavailable to users I have some flexibility
> here. I was considering turning ALLOW_SNAPSHOT_ISOLATION OFF and then
> trying
> method 2 again which should stop the versioning in tempdb and then turning
> it
> back on.
> I was also curious if setting the database recovery mode to SIMPLE would
> cut
> down on db log usage and then I could set it back to FULL when done.
> Do these really need to be in a transaction? If there's some hardware
> failure or something unexpected I can just restore from backup and do the
> conversion again. If the presence of the transaction itself is causing
> more
> disk usage for logging or any other slowdown, I think I'd rather do
> without.
> Given the amount of time this conversion takes, I wanted to get some
> feedback other than "just try it" before doing any new tests.
> Thanks.

adding 2 columns in a dataset as a single column in datagrid

hi,

i am having 2 columns in a table in a dataset.

i want to add those two columns and bind the resultant total as a single column to the datagrid.

is it possible.

if yes, how o acheive this?

please help me.

thanks in advance.

muppidi.

A lot of ways. One is to use a computed column in the dataset, then just bind to that. Another way is to use something like this in your page:

<%= Eval("column1")+Eval("column2") %>

Add Titles to columns in a matrix report

I have a matrix report and it doesn't add titles to the different groupings. And when you try and add a text box it spans across all grouping columns. How do you add titles to these columns?

Cheers
Damien

When you use a matrix, the titles for the column groups are going to be the values specified in that group. For example, if you group the columns by State, then you will have each state listed at the top of each column.

I'm not sure of any way to add an extra row at the top of a matrix for a title. However, if nothing else, you could add a textbox right before the matrix to add a title. The only problem is this wouldn't be displayed on each page.

|||In a matrix report I need a way to provide titles for the groupings not the detail listings. There doesn't appear to be a way to do this in reporting services 2005?

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)

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
>

Add table and column descriptions/comments using script

Is it possible to add descriptions/comments to tables and columns using a
system stored procedure?Yes. See sp_addextendedproperty in the Books Online
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> Is it possible to add descriptions/comments to tables and columns using a
> system stored procedure?|||Just what I was looking for!
Thanks a lot Dan.
Rgds
Per Christian Paasche
Norway
"Dan Guzman" wrote:
> Yes. See sp_addextendedproperty in the Books Online
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> > Is it possible to add descriptions/comments to tables and columns using a
> > system stored procedure?
>
>

Add static calculated column AFTER dynamic columns in a matrix?

Is it possible to add a calculated static column to a matrix, after the
dynamic ones?
I have the following matrix to create:
Indicator Units 2001 2002 2003 2004 %Change
----
Volume M3 23 33 44 55 25%
Energy KJ 33 34 35 36 3%
etc.
The year values come from a dataset and may vary (sometimes only 1 year,
sometimes 5 or 6 years). The last column is not depending on the dataset and
takes the values of the last 2 dynamic columns to calculate the % change.
1) is it possible to add a static column after the dynamic ones?
2) can I refer to the last dynamic column (and the one before) in an
expression?
Thanks,
VincentI am not really sure if you can filter the totals at the
end of the dynamic columns or not , but you can place a
textbox after the matrix and specify the expression as
follows :
=First(Fields!FieldName1.Value) OR =Last(Fields!
FieldName2.Value)
OR you can have a table with just the footer and only one
column and specify the filter expression in this one place
holder in the table.
>--Original Message--
>Is it possible to add a calculated static column to a
matrix, after the
>dynamic ones?
>I have the following matrix to create:
>Indicator Units 2001 2002 2003 2004 %Change
>----
>Volume M3 23 33 44 55 25%
>Energy KJ 33 34 35 36 3%
>etc.
>The year values come from a dataset and may vary
(sometimes only 1 year,
>sometimes 5 or 6 years). The last column is not depending
on the dataset and
>takes the values of the last 2 dynamic columns to
calculate the % change.
>1) is it possible to add a static column after the
dynamic ones?
>2) can I refer to the last dynamic column (and the one
before) in an
>expression?
>Thanks,
>Vincent
>
>.
>

Add static and dynamic columns to a matrix

I

have received a request for a report that has a variable number of

columns that can be generated with a matrix, and then two columns that

are fixed, i.e. one per group. Here is what I mean:

Sect 1 Sect 2 Sect 3 Score Time
-- -
Person 1 X X 50% 8 Days
Person 2 X 12% 3 Days

Another

page-level group could have 2 Sections or 8 Sections. There should only

be one Score and Elapsed Time column. How can I add these fixed columns

to a matrix?

Thanks for any help.
Having dynamic and static columns side-by-side is not currently supported in a matrix. We are working on adding this for a future release. For now, as a workaround, you can add a table that contains two columns (for score and time) to the right side of the matrix, and add a table group on person so that the matrix and the table are grouped the same way on the row.

Add space between matrix row group

I have a 2 row groups in a matrix and I need to add some space between the top one so that all the columns don't run together. Is there a way to do this?

Click on the row that you want to add the space below, go to the properties window and increase your 'Height' value. Also, make sure that the 'VerticalAlign' field is set to 'Top'. This will add empty space between that row and the one directly below it.

Hope this helps.

Jarret

|||I guess I wasn't clear. I dont need space between the rows, but between the last column in a row group and the first column in the next row group. Does that make sense?|||

Click on the last column in the matrix, and in the properties window, increase the Padding-Right value. You might want to increase the Width of that column to handle the additional padding. Does this do what you are trying to accomplish?

Hope this helps.

Jarret

|||

No, padding won't help. Here's my layout. I need more space between the subject areas.

ELA

Math

Science

Social Studies

Below Basic

Basic

Proficient

Advanced

Below Basic

Basic

Proficient

Advanced

Below Basic

Basic

Proficient

Advanced

Below Basic

Basic

Proficient

Advanced

17%

46%

37%

1%

14%

36%

30%

20%

20%

32%

23%

24%

40%

37%

9%

15%

9%

57%

30%

4%

22%

49%

16%

13%

22%

50%

13%

15%

14%

44%

22%

21%

16%

36%

44%

4%

13%

39%

22%

26%

27%

33%

21%

18%

23%

40%

17%

20%

|||That's what it currently looks like. How do you want it to look?|||

So, you are wanting something like a 'spacer' column? What if you add another blank column to you report? Right click your textbox below the 'Advanced' column and select 'Add Column', then just leave it blank.

Jarret

|||

ELA					Math
Below Basic	Basic	Proficient	Advanced		Below Basic	Basic		Proficient	Advanced
17%	46%	37%	1%		14%	36%		30%	20%
9%	57%	30%	4%		22%	49%		16%	13%
16%	36%	44%	4%		13%	39%		22%	26%

See the space I've added between ELA and Math sections? Thats what I want.

|||That adds a second column of data that will repeat. So instead of one column under advanced I have two.|||Would you still want the tables to appear horizontally next to each other? Have you considered placing the matrix in a list control and adding a groupping by subject? This would effectively give you a matrix per subject but they would be layed out vertically.|||I have to lay them out horizontally bc of how many rows there are. If I do it vertically the report gets too long to read.|||Does anybody have a clue how to do this? It seems like it'd be so simple...

add new row to existing table

I have about 600 records that I want to add to an existing table in a DB. T
he table row contains about 48 columns. I have data for 4 of those columns
in an excel spreadsheet. All other columns except 3 can be null. If I put
a value in the non-null col
umns, then I assume I can use the DTS import wizard to import the data from
the spread sheet to the desired table in the DB. Am I correct?
Thanks
JoelJoel,
You should be able to do that. You can also try something like this:
insert into
yourTable(a,b,c,d,other_not_nullA,other_
not_nullB,other_not_nullC)
select A,B,C,D,'whatever', 'whatever', 'whatever'
from OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;
HDR=YES;
IMEX=1;
Database=c:\path_to\yourSpreadsheet.xls',
'select * from [Sheet1$]'
)
If the other not null columns are not strings, use constants of the
appropriate type. The DTS wizard might be easier - there are some
issues importing Excel with OpenRowset if the column types aren't
obvious from the data, and with blank values, but with only 600 rows,
you can probably just run the select from the insert above and look to
see if what you see is right.
A quick and dirty way to get data from Excel to SQL Server is to use
string functions and concatenation in Excel to create a column something
like
="insert into
yourTable(a,b,c,d,other_not_nullA,other_
not_nullB,other_not_nullC)
values('"&A1&"','"&A2 ... and so on,
then copy and paste the insert statements into Query Analyzer and run them.
Steve Kass
Drew University
joel wrote:

>I have about 600 records that I want to add to an existing table in a DB. The tabl
e row contains about 48 columns. I have data for 4 of those columns in an excel spr
eadsheet. All other columns except 3 can be null. If I put a value in the non-null
co
lumns, then I assume I can use the DTS import wizard to import the data from the spread she
et to the desired table in the DB. Am I correct?
>Thanks
>Joel
>

add new colums to existing table with Access

is it possible to add new columns to an existing MSDE table using
Access? If not what is the best practices modify a table schema without
loosing data?
Thanks for any suggestions.
Micheal,
You can register this msde server to another sql server's enterprise
manager. Or, you could use osql at the command prompt, then do an ALTER
TABLE and add the columns you need.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Micheal,
You can register this msde server to another sql server's enterprise
manager. Or, you could use osql at the command prompt, then do an ALTER
TABLE and add the columns you need.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Add new column?

How do you add a new column in a table between two existing columns (like when you add columns with enterprise manager) using alter table?
/y0d4Is this a slow forum or is the topic to trivial for people to open it?

/y0d4|||An ATLER TABLE statement only adds the column to the end and since this is a relational database there is no problem where the column appears. However if you wish to add the column in a partical position then:

Example

CREATE TblA
(
col1 int,
col3 int,
col4 int
)

exec sp_rename Tb1A, OldTb1A
go

CREATE TblA
(
col1 int,
col2 int,
col3 int,
col4 int
)
GO
INSERT Tb1A (col1,col2,col3 ,col4 )
SELECT col1, 0,col3 ,col4
FROM OldTb1A
GO

DROP TABLE OldTb1A
GO

Do get a better idea of handling IDENTITY and so forth columns turn Profiler on and go into Enterprise Manager under the design Table option and insert a column into the middle of table and Save. You will see all the transactions that Microsoft does to perform this in Profiler.