ALTER temp table - unexpected behaviour

Funny little problem with a temp table created by an sp and then
altered within the scope of the same sp. It doesn't seem to work when I
try it.
Here's some sample code;
/**** start create code ***/
use northwind
create procedure dbo.uspTest as
select top 5 productname
into #myTemp
from products
order by productname
alter table #myTemp add RowID int identity(1,1)
select * from #myTemp
GO
/**** end code ***/
If this is executed you get;
ProductName RowID
--
Alice Mutton 1
Aniseed Syrup 2
Boston Crab Meat 3
Camembert Pierrot 4
Carnavon Tigers 5
Now try this;
/**** start alter code ***/
use northwind
alter procedure dbo.uspTest as
select top 5 productname
into #myTemp
from products
order by productname
alter table #myTemp add RowID int identity(1,1)
select * from #myTemp where RowID = 1 --added where clause
GO
/**** end code ***/
When executed you get;
Result:
Error "Invalid column name 'RowID'"
So RowID is returned as part of the result set but if you try to add a
where clause on it, it doesn't exist? The same is true for inserts (if
the column being added is not an identity column).
I worked around the problem by creating the temp table with the
identity column first and then doing an insert but I'm curious about
the error. What did I miss?
Thanks.This is by design. At compile time, the engine has to validate the existence
of the columns. At this time, the alter statement is not yet committed,
thus, the error on compilation for the select statement.
You should change your proc to include the identity column as part of your
select/into.
e.g.
create procedure dbo.uspTest as
select top 5 productname,RowID=identity(int,1,1)
into #myTemp
from products
order by productname
select * from #myTemp
where RowID = 1 --added where clause
go
-oj
"Wolf" <spamcatcher5050@.hotmail.com> wrote in message
news:1124249380.861639.272680@.g14g2000cwa.googlegroups.com...
> Funny little problem with a temp table created by an sp and then
> altered within the scope of the same sp. It doesn't seem to work when I
> try it.
> Here's some sample code;
>
> /**** start create code ***/
> use northwind
> create procedure dbo.uspTest as
> select top 5 productname
> into #myTemp
> from products
> order by productname
> alter table #myTemp add RowID int identity(1,1)
> select * from #myTemp
> GO
> /**** end code ***/
> If this is executed you get;
> ProductName RowID
> --
> Alice Mutton 1
> Aniseed Syrup 2
> Boston Crab Meat 3
> Camembert Pierrot 4
> Carnavon Tigers 5
> Now try this;
> /**** start alter code ***/
> use northwind
> alter procedure dbo.uspTest as
> select top 5 productname
> into #myTemp
> from products
> order by productname
> alter table #myTemp add RowID int identity(1,1)
> select * from #myTemp where RowID = 1 --added where clause
> GO
> /**** end code ***/
> When executed you get;
> Result:
> Error "Invalid column name 'RowID'"
> So RowID is returned as part of the result set but if you try to add a
> where clause on it, it doesn't exist? The same is true for inserts (if
> the column being added is not an identity column).
> I worked around the problem by creating the temp table with the
> identity column first and then doing an insert but I'm curious about
> the error. What did I miss?
> Thanks.
>|||Awesome. This is going to save me so much time.
Thanks oj.|||You should note that the ORDER BY clause may effectively be ignored by the
server. In particular there is no guarantee that the IDENTITY values will be
assigned in Productname order. Don't use ORDER BY on SELECT INTO or
INSERT... SELECT.
David Portas
SQL Server MVP
--

Alter Column - strange behaviour msg 5074

Hi,
I've a very strange behaviour when launching a
ALTER TABLE X ALTER COLUMN V VarChar(16)
an error 5074 is returned; previously the column was varchar(15). The column
is indexed. Sometimes it works without problems, other times it returns the
error 5074 as mentioned. I cannot see differences between the databases
where it works and where it doesn't work.
To reproduce the problem, follow this task:
- from query analyzer execute this:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[testdata]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table testdata
go
create table testdata (
id int,
aName varchar(10) null,
CONSTRAINT [pk_testdata] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
)
on [primary]
go
create index ix_testdata_aName on testdata (aName) on [primary]
go
insert into testdata (id, aName) values(1,'aaa')
go
insert into testdata (id, aName) values (2, 'bbb')
go
insert into testdata (id, aName) values (4, null)
go
alter table testdata alter column aName varchar(11)
go
- all works fine
- then copy this table to another database using the export-wizard of
enterprise manager
- then try to alter the table on the second database with
alter table testdata alter column aName varchar(12)
go
and you receive the error.
best regards
Anton Santa
What is the TEXT of the error message?
http://www.aspfaq.com/
(Reverse address to reply.)
"Anton Santa" <santa@.sabesoft.it> wrote in message
news:uQCjiGY#EHA.2032@.tk2msftngp13.phx.gbl...
> Hi,
> I've a very strange behaviour when launching a
> ALTER TABLE X ALTER COLUMN V VarChar(16)
> an error 5074 is returned; previously the column was varchar(15). The
column
> is indexed. Sometimes it works without problems, other times it returns
the
> error 5074 as mentioned. I cannot see differences between the databases
> where it works and where it doesn't work.
> To reproduce the problem, follow this task:
> - from query analyzer execute this:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[testdata]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table testdata
> go
> create table testdata (
> id int,
> aName varchar(10) null,
> CONSTRAINT [pk_testdata] PRIMARY KEY CLUSTERED
> (
> [id]
> ) ON [PRIMARY]
> )
> on [primary]
> go
> create index ix_testdata_aName on testdata (aName) on [primary]
> go
> insert into testdata (id, aName) values(1,'aaa')
> go
> insert into testdata (id, aName) values (2, 'bbb')
> go
> insert into testdata (id, aName) values (4, null)
> go
> alter table testdata alter column aName varchar(11)
> go
> - all works fine
> - then copy this table to another database using the export-wizard of
> enterprise manager
> - then try to alter the table on the second database with
> alter table testdata alter column aName varchar(12)
> go
> and you receive the error.
> best regards
> Anton Santa
>
|||the message is italian since I've an italian engine installed:
Server: Msg 5074, Level 16, State 8, Line 1
Il indice 'ix_testdata_aName' dipende da colonna 'aName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN aName non riuscita perch uno o pi oggetti
accedono a questa colonna.
meaning:
The index .. is dipendent on column ..
ALTER TABLE DROP COLUMN .. failed because one or more objects access this
column.
...
regards
Anton Santa
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> ha scritto nel messaggio
news:Oap9AhY%23EHA.2180@.TK2MSFTNGP10.phx.gbl...
> What is the TEXT of the error message?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Anton Santa" <santa@.sabesoft.it> wrote in message
> news:uQCjiGY#EHA.2032@.tk2msftngp13.phx.gbl...
> column
> the
>

Alter Column - strange behaviour msg 5074

Hi,
I've a very strange behaviour when launching a
ALTER TABLE X ALTER COLUMN V VarChar(16)
an error 5074 is returned; previously the column was varchar(15). The column
is indexed. Sometimes it works without problems, other times it returns the
error 5074 as mentioned. I cannot see differences between the databases
where it works and where it doesn't work.
To reproduce the problem, follow this task:
- from query analyzer execute this:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testdata]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table testdata
go
create table testdata (
id int,
aName varchar(10) null,
CONSTRAINT [pk_testdata] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
)
on [primary]
go
create index ix_testdata_aName on testdata (aName) on [primary]
go
insert into testdata (id, aName) values(1,'aaa')
go
insert into testdata (id, aName) values (2, 'bbb')
go
insert into testdata (id, aName) values (4, null)
go
alter table testdata alter column aName varchar(11)
go
- all works fine
- then copy this table to another database using the export-wizard of
enterprise manager
- then try to alter the table on the second database with
alter table testdata alter column aName varchar(12)
go
and you receive the error.
best regards
Anton SantaWhat is the TEXT of the error message?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Anton Santa" <santa@.sabesoft.it> wrote in message
news:uQCjiGY#EHA.2032@.tk2msftngp13.phx.gbl...
> Hi,
> I've a very strange behaviour when launching a
> ALTER TABLE X ALTER COLUMN V VarChar(16)
> an error 5074 is returned; previously the column was varchar(15). The
column
> is indexed. Sometimes it works without problems, other times it returns
the
> error 5074 as mentioned. I cannot see differences between the databases
> where it works and where it doesn't work.
> To reproduce the problem, follow this task:
> - from query analyzer execute this:
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[testdata]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table testdata
> go
> create table testdata (
> id int,
> aName varchar(10) null,
> CONSTRAINT [pk_testdata] PRIMARY KEY CLUSTERED
> (
> [id]
> ) ON [PRIMARY]
> )
> on [primary]
> go
> create index ix_testdata_aName on testdata (aName) on [primary]
> go
> insert into testdata (id, aName) values(1,'aaa')
> go
> insert into testdata (id, aName) values (2, 'bbb')
> go
> insert into testdata (id, aName) values (4, null)
> go
> alter table testdata alter column aName varchar(11)
> go
> - all works fine
> - then copy this table to another database using the export-wizard of
> enterprise manager
> - then try to alter the table on the second database with
> alter table testdata alter column aName varchar(12)
> go
> and you receive the error.
> best regards
> Anton Santa
>|||the message is italian since I've an italian engine installed:
Server: Msg 5074, Level 16, State 8, Line 1
Il indice 'ix_testdata_aName' dipende da colonna 'aName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN aName non è riuscita perché uno o più oggetti
accedono a questa colonna.
meaning:
The index .. is dipendent on column ..
ALTER TABLE DROP COLUMN .. failed because one or more objects access this
column.
..
regards
Anton Santa
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> ha scritto nel messaggio
news:Oap9AhY%23EHA.2180@.TK2MSFTNGP10.phx.gbl...
> What is the TEXT of the error message?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Anton Santa" <santa@.sabesoft.it> wrote in message
> news:uQCjiGY#EHA.2032@.tk2msftngp13.phx.gbl...
>> Hi,
>> I've a very strange behaviour when launching a
>> ALTER TABLE X ALTER COLUMN V VarChar(16)
>> an error 5074 is returned; previously the column was varchar(15). The
> column
>> is indexed. Sometimes it works without problems, other times it returns
> the
>> error 5074 as mentioned. I cannot see differences between the databases
>> where it works and where it doesn't work.
>> To reproduce the problem, follow this task:
>> - from query analyzer execute this:
>> if exists (select * from dbo.sysobjects where id =>> object_id(N'[dbo].[testdata]') and OBJECTPROPERTY(id, N'IsUserTable') =>> 1)
>> drop table testdata
>> go
>> create table testdata (
>> id int,
>> aName varchar(10) null,
>> CONSTRAINT [pk_testdata] PRIMARY KEY CLUSTERED
>> (
>> [id]
>> ) ON [PRIMARY]
>> )
>> on [primary]
>> go
>> create index ix_testdata_aName on testdata (aName) on [primary]
>> go
>> insert into testdata (id, aName) values(1,'aaa')
>> go
>> insert into testdata (id, aName) values (2, 'bbb')
>> go
>> insert into testdata (id, aName) values (4, null)
>> go
>> alter table testdata alter column aName varchar(11)
>> go
>> - all works fine
>> - then copy this table to another database using the export-wizard of
>> enterprise manager
>> - then try to alter the table on the second database with
>> alter table testdata alter column aName varchar(12)
>> go
>> and you receive the error.
>> best regards
>> Anton Santa
>>
>

Alter Column - strange behaviour msg 5074

Hi,
I've a very strange behaviour when launching a
ALTER TABLE X ALTER COLUMN V VarChar(16)
an error 5074 is returned; previously the column was varchar(15). The column
is indexed. Sometimes it works without problems, other times it returns the
error 5074 as mentioned. I cannot see differences between the databases
where it works and where it doesn't work.
To reproduce the problem, follow this task:
- from query analyzer execute this:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[testdata]') and OBJECTPROPERTY(id, N'IsUserTable'
) = 1)
drop table testdata
go
create table testdata (
id int,
aName varchar(10) null,
CONSTRAINT [pk_testdata] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
)
on [primary]
go
create index ix_testdata_aName on testdata (aName) on [primary]
go
insert into testdata (id, aName) values(1,'aaa')
go
insert into testdata (id, aName) values (2, 'bbb')
go
insert into testdata (id, aName) values (4, null)
go
alter table testdata alter column aName varchar(11)
go
- all works fine
- then copy this table to another database using the export-wizard of
enterprise manager
- then try to alter the table on the second database with
alter table testdata alter column aName varchar(12)
go
and you receive the error.
best regards
Anton SantaWhat is the TEXT of the error message?
http://www.aspfaq.com/
(Reverse address to reply.)
"Anton Santa" <santa@.sabesoft.it> wrote in message
news:uQCjiGY#EHA.2032@.tk2msftngp13.phx.gbl...
> Hi,
> I've a very strange behaviour when launching a
> ALTER TABLE X ALTER COLUMN V VarChar(16)
> an error 5074 is returned; previously the column was varchar(15). The
column
> is indexed. Sometimes it works without problems, other times it returns
the
> error 5074 as mentioned. I cannot see differences between the databases
> where it works and where it doesn't work.
> To reproduce the problem, follow this task:
> - from query analyzer execute this:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[testdata]') and OBJECTPROPERTY(id, N'IsUserTabl
e') = 1)
> drop table testdata
> go
> create table testdata (
> id int,
> aName varchar(10) null,
> CONSTRAINT [pk_testdata] PRIMARY KEY CLUSTERED
> (
> [id]
> ) ON [PRIMARY]
> )
> on [primary]
> go
> create index ix_testdata_aName on testdata (aName) on [primary]
> go
> insert into testdata (id, aName) values(1,'aaa')
> go
> insert into testdata (id, aName) values (2, 'bbb')
> go
> insert into testdata (id, aName) values (4, null)
> go
> alter table testdata alter column aName varchar(11)
> go
> - all works fine
> - then copy this table to another database using the export-wizard of
> enterprise manager
> - then try to alter the table on the second database with
> alter table testdata alter column aName varchar(12)
> go
> and you receive the error.
> best regards
> Anton Santa
>|||the message is italian since I've an italian engine installed:
Server: Msg 5074, Level 16, State 8, Line 1
Il indice 'ix_testdata_aName' dipende da colonna 'aName'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN aName non riuscita perch uno o pi oggetti
accedono a questa colonna.
meaning:
The index .. is dipendent on column ..
ALTER TABLE DROP COLUMN .. failed because one or more objects access this
column.
..
regards
Anton Santa
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> ha scritto nel messagg
io
news:Oap9AhY%23EHA.2180@.TK2MSFTNGP10.phx.gbl...
> What is the TEXT of the error message?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Anton Santa" <santa@.sabesoft.it> wrote in message
> news:uQCjiGY#EHA.2032@.tk2msftngp13.phx.gbl...
> column
> the
>