Can I do an "alter table add column", with an existing named default?

Hello folks,
Shouldn't I be able to do this? I'm not able to get the syntax to work
Alter table TableName add ColumnName tinyint not null default
DefaultZero
This works:
Alter table TableName add ColumnName tinyint not null default (0)
...but I need to immediately drop the default programatically, and for
that I need the default name, so I'd like to name it myself.
I tried this:
EXEC sp_unbindefault 'TableName .ColumnName'
but got the error "Cannot unbind from 'TableName .ColumnName'. Use
ALTER TABLE DROP CONSTRAINT.
I guess I could query the system tables to figure out the name, but
would prefer not to if I can avoid it.
thanks for any ideas!
SylviaHere's an example with the proper syntax:
ALTER TABLE TableName
ADD ColumnName tinyint NOT NULL
CONSTRAINT DF_TableName_ColumnName DEFAULT 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Sylvia" <Puget4753@.yahoo.com> wrote in message
news:1116288687.747349.22520@.g47g2000cwa.googlegroups.com...
> Hello folks,
> Shouldn't I be able to do this? I'm not able to get the syntax to work
> Alter table TableName add ColumnName tinyint not null default
> DefaultZero
> This works:
> Alter table TableName add ColumnName tinyint not null default (0)
> ...but I need to immediately drop the default programatically, and for
> that I need the default name, so I'd like to name it myself.
> I tried this:
> EXEC sp_unbindefault 'TableName .ColumnName'
> but got the error "Cannot unbind from 'TableName .ColumnName'. Use
> ALTER TABLE DROP CONSTRAINT.
> I guess I could query the system tables to figure out the name, but
> would prefer not to if I can avoid it.
> thanks for any ideas!
> Sylvia
>|||thanks - this works perfectly!

Can an IDENTITY column be updated?

(SQL Server 2000, SP3)
Hello all!
I have a simple table:
create table Test (Id int identity(1, 1) not NULL, Name varchar(255) NULL)
insert into Test (Name) values ('Test')
And I'd like to potentially update the IDENTITY column Id:
update Test set Id = 100 where Id = 1
However, I get the following error:
Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'Id'.
Even if I try to "wrap" the UPDATE in a "set identity_insert", I still get the same error.
Is there any way to update a column with an IDENTITY property?
Thanks!
John PetersonThanks Sue! Ah, I see the blurb in BOL that says an IDENTITY can't be updated. Bummer.
:-(
It seems to me that in older versions of SQL Server, one could update a column with the
IDENTITY property. But, no longer (or my memory isn't what it once was ;-).
Thanks again!
John Peterson
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:dc1njvsscenku5aoklp93tl6rjptcibdcf@.4ax.com...
> Identity columns can't be updated - I think it's documented
> under the UPDATE topic in BOL T-SQL reference. One possible
> option would be to set identity_insert on, use the existing
> values for a new record and insert the new record with the
> identity value you need to use and then delete the old
> record.
> -Sue
> On Wed, 13 Aug 2003 20:10:46 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >(SQL Server 2000, SP3)
> >
> >Hello all!
> >
> >I have a simple table:
> >
> >create table Test (Id int identity(1, 1) not NULL, Name varchar(255) NULL)
> >insert into Test (Name) values ('Test')
> >
> >And I'd like to potentially update the IDENTITY column Id:
> >
> >update Test set Id = 100 where Id = 1
> >
> >However, I get the following error:
> >
> >Server: Msg 8102, Level 16, State 1, Line 1
> >Cannot update identity column 'Id'.
> >
> >Even if I try to "wrap" the UPDATE in a "set identity_insert", I still get the same
error.
> >
> >Is there any way to update a column with an IDENTITY property?
> >
> >Thanks!
> >
> >John Peterson
> >
>

can a query be written to do this...

I have the following table defined: Not my design/idea and I can't change
it)
CREATE TABLE [dbo].[Task] (
[TaskID] [ROWIDENTIFIER] NOT NULL ,
[Name] [SHORTNAME] NULL ,
[Description] [SHORTDESCRIPTION] NULL ,
[PreviousTaskID] [ROWIDENTIFIER] NULL ,
[NextTaskID] [ROWIDENTIFIER] NULL ,
[ProcedureID] [ROWIDENTIFIER] NOT NULL ,
[IsActive] [WFBOOL] NULL
) ON [PRIMARY]
PreviousTaskID and NextTaskID form, what amounts to a linked list where
PreviousTaskID points to the TaskID that comes before the current task and
NextTaskID points to the TaskID of the task that follows the current task.
A PreviousTaskID equal to null signifies the first task in a list and a
NextTaskID equal to null signifies it is the last task in the list.
With all of that in mind: Is there any way to write a query that will return
a single set of rows ordered from first to last?
TIA
Brian WBW -
It seems unnecessary to have a Next and Previous so long as the chain is
always 1 for 1 (i.e. Task 2 always comes after Task 1, etc). Anyways, here'
s
something that should get you started:
create table #Task (
TaskID int not null
, [Name] varchar(50) null
, PreviousTaskID int null
, NextTaskID int null
)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(1,
'T1', null, 2)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(2,
'T1', 1, 3)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(3,
'T1', 2, 4)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(4,
'T1', 3, 5)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(5,
'T1', 4, 6)
insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(6,
'T1', 5, null)
declare @.parent_level int
set @.parent_level = 0
declare @.hierarchy table (parent int, item int, [level] int)
insert into @.hierarchy (parent, item, [level])
select null, taskid, 0
from #Task
where previoustaskid is null
while 1 = 1
begin
insert into @.hierarchy(parent, item, [level])
select nexttaskid, taskid, @.parent_level + 1
from #Task
where previoustaskid in (select item from @.hierarchy where [level] =
@.parent_level)
if @.@.rowcount = 0
break
set @.parent_level = @.parent_level + 1
end
select t.TaskID, h.[level] as Ordering
from #Task t
join @.hierarchy h on t.TaskID = h.item
order by 2 asc|||Perfect!
muchos gracias!
"Cris_Benge" <CrisBenge@.discussions.microsoft.com> wrote in message
news:C3096324-4209-4868-80AB-FA4FA7DB9B97@.microsoft.com...
> BW -
> It seems unnecessary to have a Next and Previous so long as the chain is
> always 1 for 1 (i.e. Task 2 always comes after Task 1, etc). Anyways,
here's
> something that should get you started:
> create table #Task (
> TaskID int not null
> , [Name] varchar(50) null
> , PreviousTaskID int null
> , NextTaskID int null
> )
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(1,
> 'T1', null, 2)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(2,
> 'T1', 1, 3)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(3,
> 'T1', 2, 4)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(4,
> 'T1', 3, 5)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(5,
> 'T1', 4, 6)
> insert into #Task (TaskID, [Name], PreviousTaskId, NextTaskID) values(6,
> 'T1', 5, null)
> declare @.parent_level int
> set @.parent_level = 0
> declare @.hierarchy table (parent int, item int, [level] int)
> insert into @.hierarchy (parent, item, [level])
> select null, taskid, 0
> from #Task
> where previoustaskid is null
> while 1 = 1
> begin
> insert into @.hierarchy(parent, item, [level])
> select nexttaskid, taskid, @.parent_level + 1
> from #Task
> where previoustaskid in (select item from @.hierarchy where [level] =
> @.parent_level)
> if @.@.rowcount = 0
> break
> set @.parent_level = @.parent_level + 1
> end
> select t.TaskID, h.[level] as Ordering
> from #Task t
> join @.hierarchy h on t.TaskID = h.item
> order by 2 asc
>

Can @@ROWCOUNT return NULL?

SQL Server 2000 SP3.

Is it possible for the @.@.ROWCOUNT function to return NULL after a
statement? I am troubleshooting a relatively large stored procedure with
multiple SELECT statements and a couple of INSERTs into table variables.
Immediately after each statement I save the value returned by @.@.ROWCOUNT to
a local variable. That information eventually is passed back to the client
via one output parameter, for all statements in the procedure.
Occasionally, the value returned via that parameter is NULL. This cannot be
reproduced by re-running the SP with the same input parameters.

Before doing any further troubleshooting, I would like to rule out the
possibility that @.@.ROWCOUNT can actually return a NULL under some
circumstances. From searching the archives, it appears that in SQL Server
7.0 this could happen in the context of a DML query on a table with
triggers. This is not the case here - the only DML queries are INSERTs into
table variables, all other queries in the SP are SELECTs.

Any related information would be appreciated.

--
remove a 9 to reply by emailDimitri Furman (dfurman@.cloud99.net) writes:
> Is it possible for the @.@.ROWCOUNT function to return NULL after a
> statement? I am troubleshooting a relatively large stored procedure with
> multiple SELECT statements and a couple of INSERTs into table variables.
> Immediately after each statement I save the value returned by @.@.ROWCOUNT
> to a local variable. That information eventually is passed back to the
> client via one output parameter, for all statements in the procedure.
> Occasionally, the value returned via that parameter is NULL. This cannot
> be reproduced by re-running the SP with the same input parameters.
> Before doing any further troubleshooting, I would like to rule out the
> possibility that @.@.ROWCOUNT can actually return a NULL under some
> circumstances. From searching the archives, it appears that in SQL
> Server 7.0 this could happen in the context of a DML query on a table
> with triggers. This is not the case here - the only DML queries are
> INSERTs into table variables, all other queries in the SP are SELECTs.

I have never heard of a case where @.@.rowcount can return NULL. Books
Online gives one hint when @.@.rowcount is not good: when more than two
milliard rows can be affected. In this case, you should try
rowcount_big(). Could this apply to you?

If not, I would recommend that you start troubleshooting. If it is not
repeatable, it will certainly be difficult...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Try removing
SET NOCOUNT ON
from the stored procedure.

GeoSynch

"Dimitri Furman" <dfurman@.cloud99.net> wrote in message
news:Xns96C19F85E9F56dfurmancloud99@.127.0.0.1...
> SQL Server 2000 SP3.
> Is it possible for the @.@.ROWCOUNT function to return NULL after a
> statement? I am troubleshooting a relatively large stored procedure with
> multiple SELECT statements and a couple of INSERTs into table variables.
> Immediately after each statement I save the value returned by @.@.ROWCOUNT to
> a local variable. That information eventually is passed back to the client
> via one output parameter, for all statements in the procedure.
> Occasionally, the value returned via that parameter is NULL. This cannot be
> reproduced by re-running the SP with the same input parameters.
> Before doing any further troubleshooting, I would like to rule out the
> possibility that @.@.ROWCOUNT can actually return a NULL under some
> circumstances. From searching the archives, it appears that in SQL Server
> 7.0 this could happen in the context of a DML query on a table with
> triggers. This is not the case here - the only DML queries are INSERTs into
> table variables, all other queries in the SP are SELECTs.
> Any related information would be appreciated.
> --
> remove a 9 to reply by email|||GeoSynch (SpamSlayed@.Casablanca.com) writes:
> Try removing
> SET NOCOUNT ON
> from the stored procedure.

@.@.rowcount should always return a value even if NOCOUNT is on. This
option controls whether rowcount information is passed to the client.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp