Changing columns name in a temp table

Dear All,

I'm trying to alter the name of several columns' in a table which gets created in a stored procedure.

trying to use:

exec sp_rename '#tblBd.week1', '2007_18', 'COLUMN'

I get:

Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163
Either the parameter @.objname is ambiguous or the claimed @.objtype (COLUMN) is wrong.

There is no mistype, the table name and column name are correct.

Can temp table's column names not be altered?

If yes, how?

Thanks in advance!Hi gorgenyi

Let's cover the obvious stuff first of all to get an idea of what you are doing and why.
1) Why not define your temp table with the correct column names in the first place?
2) Why not alias your column names in the result set you create from the temp table?|||ok, here it goes.

I created a report with a stored procedure which displays resource allocation to projects in the BD pipeline week by week for the next 10 weeks ahead.

the end reult looks like this:

Project name, w1, w2, w3, w4, w5, w6 etc...
Prj A 1 2 2 1
Prj B 1 2 2 2.5 2 2
Prj C .5 1 1 1.5 1
...

I'm using w1, w2, w3 for the weeks ahead during the stored procedure for multiple joins, however I'd like to change them to the always relevant week numbers programatically in the follwoing format '2007-17', '2007-18', '2007-19' ... etc at the end so I can publish these as the datagrid's header.

Did I explain clearly? What would you recomend? Thanks for your help on this.|||You are making the all too common mistake of trying to use SQL and/or Query Analyzer as a reporting environment. It is not designed for this.
What you are trying to do is modify the way the data is displayed, and this is a job for Crystal Reports/Active Reports/Reporting Services/MS Access/Excel, or whatever reporting/interface platform you are using.
Dynamic column names are BAD, because then whatever reporting tool you use will never know what output schema to expect.|||this is a job for Crystal Reports/Active Reports/Reporting Services/MS Access/Excel, or whatever reporting/interface platform you are using.Somehow you conspired to miss with all those I think. I suspect this is just some .NET front end.

You can (I think) dynamically set up your column headers in a datagrid but I am afraid I cannot remember how.

blindman is right though - this is best handled in your front end rather than SQL.|||...or .Net or ASP.|||It just seemed very simple, and must say I do not have experience of the reporting tools. Here is what I did:

temp table to get next 10 weeks ids
temp table to get active BD items and the 10 columns with 0 values
then a double loop where I update each cell with a resource number to the appropriate BD item and the appropriate week number.

Dispay the data in datagrid, with headings for the weeks as Week 1, Week 2, etc. while adding the sums up / week in ASP.NET and do various other things like colouring background etc.

But I'd like to get the actually internal week reference displayed on the grid, which of course chnage weekly, so want to do it dynamicaly. But yes, point taken on ASP.NET won't know what col name they will be.

And yes, this is ASP.NET front end.|||Some ideas from the web. Maybe consider posting in an ASP.NET site if you can't follow them. You defo do not want to do this at the database.
http://www.thescripts.com/forum/thread320779.html
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_20976549.html

Changing collation for all columns

I need to change the collation for my database from
SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
Is the statement "ALTER DATABASE MyDatabase COLLATE
SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
fields (how to do it)?
Any ideas? Any precautions to take?
Thanks!
Changing the collation on the database level affects everything beneath it -
fields included
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>I need to change the collation for my database from
>SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
> Is the statement "ALTER DATABASE MyDatabase COLLATE
> SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
> fields (how to do it)?
> Any ideas? Any precautions to take?
> Thanks!
|||> Changing the collation on the database level affects everything beneath it - fields included
That is *not* correct, I'm afraid. Here's a simple repro which demonstrates that changing db
collation *does not* modify your existing data:
USE master
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
GO
CREATE DATABASE x_coll_test COLLATE Albanian_CI_AS
GO
USE x_coll_test
GO
CREATE TABLE t(c1 varchar(5))
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
USE master
GO
ALTER DATABASE x_coll_test COLLATE Croatian_CI_AS
USE x_coll_test
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
GO
USE master
GO
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:O6ZvAL$0HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Changing the collation on the database level affects everything beneath it - fields included
> "Gaspar" <gaspar@.no-reply.com> wrote in message news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>
sql

Changing collation for all columns

I need to change the collation for my database from
SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
Is the statement "ALTER DATABASE MyDatabase COLLATE
SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
fields (how to do it)?
Any ideas? Any precautions to take?
Thanks!Changing the collation on the database level affects everything beneath it -
fields included
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>I need to change the collation for my database from
>SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
> Is the statement "ALTER DATABASE MyDatabase COLLATE
> SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
> fields (how to do it)?
> Any ideas? Any precautions to take?
> Thanks!|||> Changing the collation on the database level affects everything beneath it
- fields included
That is *not* correct, I'm afraid. Here's a simple repro which demonstrates
that changing db
collation *does not* modify your existing data:
USE master
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
GO
CREATE DATABASE x_coll_test COLLATE Albanian_CI_AS
GO
USE x_coll_test
GO
CREATE TABLE t(c1 varchar(5))
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
USE master
GO
ALTER DATABASE x_coll_test COLLATE Croatian_CI_AS
USE x_coll_test
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
GO
USE master
GO
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:O6ZvAL$0HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Changing the collation on the database level affects everything beneath it
- fields included
> "Gaspar" <gaspar@.no-reply.com> wrote in message news:OJRh5B40HHA.6072@.TK2M
SFTNGP03.phx.gbl...
>

Changing collation for all columns

I need to change the collation for my database from
SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
Is the statement "ALTER DATABASE MyDatabase COLLATE
SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
fields (how to do it)?
Any ideas? Any precautions to take?
Thanks!Changing the collation on the database level affects everything beneath it -
fields included
"Gaspar" <gaspar@.no-reply.com> wrote in message
news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>I need to change the collation for my database from
>SQL_Latin1_General_CP1_CI_ASto SQL_Latin1_General_CP1_CI_AI
> Is the statement "ALTER DATABASE MyDatabase COLLATE
> SQL_Latin1_General_CP1_CI_AI" enough or should I change every existing
> fields (how to do it)?
> Any ideas? Any precautions to take?
> Thanks!|||> Changing the collation on the database level affects everything beneath it - fields included
That is *not* correct, I'm afraid. Here's a simple repro which demonstrates that changing db
collation *does not* modify your existing data:
USE master
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
GO
CREATE DATABASE x_coll_test COLLATE Albanian_CI_AS
GO
USE x_coll_test
GO
CREATE TABLE t(c1 varchar(5))
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
USE master
GO
ALTER DATABASE x_coll_test COLLATE Croatian_CI_AS
USE x_coll_test
GO
SELECT
DATABASEPROPERTYEX('x_coll_test', 'Collation') AS DatabaseCollation
,collation_name AS DatabaseCollation
FROM sys.columns WHERE objecT_id = OBJECT_ID('t') AND name = 'c1'
GO
USE master
GO
IF DB_ID('x_coll_test') IS NOT NULL DROP DATABASE x_coll_test
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bass_player [SBS-MVP]" <bass_player@.mvps.org> wrote in message
news:O6ZvAL$0HHA.5884@.TK2MSFTNGP02.phx.gbl...
> Changing the collation on the database level affects everything beneath it - fields included
> "Gaspar" <gaspar@.no-reply.com> wrote in message news:OJRh5B40HHA.6072@.TK2MSFTNGP03.phx.gbl...
>>I need to change the collation for my database from SQL_Latin1_General_CP1_CI_ASto
>>SQL_Latin1_General_CP1_CI_AI
>> Is the statement "ALTER DATABASE MyDatabase COLLATE SQL_Latin1_General_CP1_CI_AI" enough or
>> should I change every existing fields (how to do it)?
>> Any ideas? Any precautions to take?
>> Thanks!
>

Changing all existing tables collations in one step

I can change table collation first table column by column
then second table and so on (Alter table table_name
alter column column_name COLLATE < collation_name> ).
How can I change all existing tables collations in one step ?
Where can I find some program in Transact-SQL or some procedure to solve
this problem ?Am Tue, 11 Apr 2006 20:31:01 -0700 schrieb Alur:

> I can change table collation first table column by column
> then second table and so on (Alter table table_name
> alter column column_name COLLATE < collation_name> ).
> How can I change all existing tables collations in one step ?
> Where can I find some program in Transact-SQL or some procedure to solve
> this problem ?
You can change the collation only column by column, not in one step.
So you can read the info you need:
--
select so.name as TableName, sc.name as ColName, sc.collation from
syscolumns sc left join sysobjects so on so.id = sc.id
where IsNull(sc.name,'') > '' and so.xtype = 'U'
and sc.collation is not NULL
--
create a cursor, loop through this query and do the ALTER TABLE where the
collation is not like you need.
bye, Helmut|||Thank you.
"helmut woess" wrote:

> Am Tue, 11 Apr 2006 20:31:01 -0700 schrieb Alur:
>
> You can change the collation only column by column, not in one step.
> So you can read the info you need:
> --
> select so.name as TableName, sc.name as ColName, sc.collation from
> syscolumns sc left join sysobjects so on so.id = sc.id
> where IsNull(sc.name,'') > '' and so.xtype = 'U'
> and sc.collation is not NULL
> --
> create a cursor, loop through this query and do the ALTER TABLE where the
> collation is not like you need.
> bye, Helmut
>

changing a datatype

Hi - I want to change the precision/scale on a decimal datatype on a field
which is in a replicated table (merge). I tried to alter the field on the
publisher but I receive a message saying that I can't do this due to the
table being a replicated table.
Anyone know tips on how to do this (without removing replication). Thanks.
Hi Dave,
unfortunately you can't do this in merge.
Have a look at part (b) in this article for a workaround :
http://www.replicationanswers.com/AddColumn.asp
Alternatively you'll have to reinitialize in the case of merge.
Incidentally, this is no longer an issue in SQL Server 2005.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul.
I'll consider re-initialising as I don't have that much data yet and just
conducting tests now anyway. Do I have to pretty much drop replication, do
the changes and start again with merge replication set up?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e5dKgDhrFHA.1132@.TK2MSFTNGP10.phx.gbl...
> Hi Dave,
> unfortunately you can't do this in merge.
> Have a look at part (b) in this article for a workaround :
> http://www.replicationanswers.com/AddColumn.asp
> Alternatively you'll have to reinitialize in the case of merge.
> Incidentally, this is no longer an issue in SQL Server 2005.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Dave - you don't need to drop replication as such - just the merge
publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Changing a column name

How can you change a Column name for MS SQL 2000 ?
something like :
ALTER TABLE [dbo].[MyTable]
OldName RENAME NewName
GO
thank youFrom BOL,

--This example renames the contact title column in the customers table to title.

EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'|||thanks a lot|||So why does EM do this?

EXECUTE sp_rename N'dbo.myTable00.AccID', N'Tmp_AccID_new', 'COLUMN'
GO
EXECUTE sp_rename N'dbo.myTable00.Tmp_AccID_new', N'AccID_new', 'COLUMN'
GO|||Mainly for backward compatibility, I would guess. SQL 7.0 might not have supported renaming columns.|||EM is really a strange application
SELECT CASE doesn't work for example|||EM is really a strange application
SELECT CASE doesn't work for example

You mean in the View window...

ALL of that stuff should be done in Query Analyzer.

I like to see what EM does when you change a table..they usually try to provide the best practices approach.

For example, go change the order of a column, or drop a column in EM, and then save the script...

Know what it does? It doesn't use ALTER, and there's a very good reasonm for that.

Changes in Table

How we can stop alter statement of Table when the Table is referred by Stored Procedures and other Objects?

Quote:

Originally Posted by sajithamol

How we can stop alter statement of Table when the Table is referred by Stored Procedures and other Objects?

can you explain in detail

change timestamp from null to not null

Hi,
I have a table with a timestamp column which allows nulls.
I wanted to
ALTER TABLE [dbo].[MyTbl] ALTER COLUMN [MyClmn] [timestamp]
NOT NULL
but it gives me msg 4927 - Cannot alter column 'MyClmn' to
be data type timestamp.
Is there any elegant/easy way around this?
--
Many thanks,
OskHi,
Would dropping the column and recreating it with the 'not
null' clause be the advisable way to do it?
--
Many thanks,
Osk
>--Original Message--
>Hi,
>I have a table with a timestamp column which allows nulls.
>I wanted to
>ALTER TABLE [dbo].[MyTbl] ALTER COLUMN [MyClmn] [timestamp]
>NOT NULL
>but it gives me msg 4927 - Cannot alter column 'MyClmn' to
>be data type timestamp.
>Is there any elegant/easy way around this?
>--
>Many thanks,
>Osk
>.
>|||Osk wrote:
> Hi,
> Would dropping the column and recreating it with the 'not
> null' clause be the advisable way to do it?
>
>> --Original Message--
>> Hi,
>> I have a table with a timestamp column which allows nulls.
>> I wanted to
>> ALTER TABLE [dbo].[MyTbl] ALTER COLUMN [MyClmn] [timestamp]
>> NOT NULL
>> but it gives me msg 4927 - Cannot alter column 'MyClmn' to
>> be data type timestamp.
>> Is there any elegant/easy way around this?
>> --
>> Many thanks,
>> Osk
>> .
If you need to preserve the data in the table, create a second table,
copy the data, change the first table DDL and then migrate the data
back.
--
David Gugick
Imceda Software
www.imceda.com|||Hi David,
Thanks for your answer. Can you please tell me why would I
lose any data except the old timestamp values (which seems
to be inevitable anyway) by dropping and
redefining/recreating the timestamp column?
--
Many thanks,
Osk
>--Original Message--
>Osk wrote:
>> Hi,
>> Would dropping the column and recreating it with the 'not
>> null' clause be the advisable way to do it?
>>
>> --Original Message--
>> Hi,
>> I have a table with a timestamp column which allows nulls.
>> I wanted to
>> ALTER TABLE [dbo].[MyTbl] ALTER COLUMN [MyClmn] [timestamp]
>> NOT NULL
>> but it gives me msg 4927 - Cannot alter column 'MyClmn' to
>> be data type timestamp.
>> Is there any elegant/easy way around this?
>> --
>> Many thanks,
>> Osk
>> .
>If you need to preserve the data in the table, create a
second table,
>copy the data, change the first table DDL and then migrate
the data
>back.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>|||anonymous@.discussions.microsoft.com wrote:
> Hi David,
> Thanks for your answer. Can you please tell me why would I
> lose any data except the old timestamp values (which seems
> to be inevitable anyway) by dropping and
> redefining/recreating the timestamp column?
>
I guess it really doesn't matter if you lose the data in the column.
--
David Gugick
Imceda Software
www.imceda.com|||Yes, I lose the timestamp data in the column, but the rest
of the table data should be remaining intact. And from this
perspective the result of using either the drop-
redefine/recreate method or the export/import method would
be the same, except that the latter takes more effort to
implement.
--
Thanks,
Osk
>--Original Message--
>anonymous@.discussions.microsoft.com wrote:
>> Hi David,
>> Thanks for your answer. Can you please tell me why would I
>> lose any data except the old timestamp values (which seems
>> to be inevitable anyway) by dropping and
>> redefining/recreating the timestamp column?
>I guess it really doesn't matter if you lose the data in
the column.
>--
>David Gugick
>Imceda Software
>www.imceda.com
>.
>

change table ownership 2005

I have never been able to get this to work for 2005. Can anyone help
me troubleshoot my problem? I run this:
ALTER AUTHORIZATION ON db.table TO dbo;
GO
And get incorrect syntax near the keyword 'authorization'
I am supposed to run it as a query right?That's the correct syntax, yes. Does the following script run without error
for you:
--
use tempdb
go
create user auser without login
go
create table xyz (xxyyzz int)
go
alter authorization on xyz to auser
go
drop table xyz
go
drop user auser
go
--
--
Adam Machanic
SQL Server MVP
Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220
<glayla@.gmail.com> wrote in message
news:1182458122.375047.227480@.k79g2000hse.googlegroups.com...
>I have never been able to get this to work for 2005. Can anyone help
> me troubleshoot my problem? I run this:
> ALTER AUTHORIZATION ON db.table TO dbo;
> GO
> And get incorrect syntax near the keyword 'authorization'
> I am supposed to run it as a query right?
>