Changing column data type increase transaction log

I am using SQL Server 2000. My database is about 8.5 gig in size.
I have a table with a column with datatype smallint, and I would like to
change it to datatype int.
When I did that, it is taking forever and the transaction log just keep
growing and growing until I almost ran out of disk space before I killed
Enterprise Manager.
How can I change a column datatype without making the transaction log keep
growing and growing ?
Thank you.
On Mar 20, 12:56Xpm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.
|||Thank you.
My database model is already simple. If I do the ALTER TABLE will the
transaction log keep growing and growing ?
I also posted another question on this forum regarding moving data from 1
column to another. I am thinking to do it that way.
Here is the real table:
CREATE TABLE [dbo].[Packet] (
[PACKET_TIME] [datetime] NOT NULL ,
[PACKET_CONTRACT] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[PACKET_DATA] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PACKET_VOL] [smallint] NULL ,
[PACKET_VOL2] [int] NULL ,
[PACKET_TRADE] [float] NULL
) ON [PRIMARY]
GO
I create PACKET_VOL2 as datatype INT, and I will move data from PACKET_VOL
to PACKET_VOL2 with the following query:
set ROWCOUNT 50000
declare @.LastCount smallint
set @.LastCount = 1
while (@.LastCount > 0)
begin
begin tran
UPDATE PACKET SET PACKET_VOL2 = PACKET_VOL WHERE PACKET_VOL2 is null
set @.LastCount = @.@.ROWCOUNT
commit tran
end
set ROWCOUNT 0
Then I will rename PACKET_VOL to PACKET_VOLX and PACKET_VOL2 to PACKET_VOL.
What do you think ?
"rpresser" <rpresser@.gmail.com> wrote in message
news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.
|||If after renaming PACKET_VOL2 to PACKET_VOL, if I want to delete column
PACKET_VOL2 by doing the following:
ALTER TABLE packet DROP COLUMN PACKET_VOLX
Will it eat up the transaction log (will the log keep growing and growing
while I do the ALTER TABLE above) ?
Thank you
"rpresser" <rpresser@.gmail.com> wrote in message
news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.
|||When I do
ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
it executes very fast, and the transaction log did not grow.
But when I do
ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
the transaction log keeps growing and growing, so I stopped it.
Why when converting to smallint it went fast and the log did not grow but
when converting to INT the log keep growing?
Thanks.
"rpresser" <rpresser@.gmail.com> wrote in message
news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
> I am using SQL Server 2000. My database is about 8.5 gig in size.
> I have a table with a column with datatype smallint, and I would like to
> change it to datatype int.
> When I did that, it is taking forever and the transaction log just keep
> growing and growing until I almost ran out of disk space before I killed
> Enterprise Manager.
> How can I change a column datatype without making the transaction log keep
> growing and growing ?
> Thank you.
Don't use enterprise manager. EMGR's approach is to duplicate the
entire table with the new schema and then rename it back.
Use an ALTER statement:
ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
You will have to drop any indexes, references or constraints that use
the column, and recreate them afterwards.
It is still going to eat a lot of trans log space. You might need to
change the model from full to simple first.
|||In addition to Tibor's reply, there is also a third type of change. For
some datatype changes SQL Server has to inspect every row to see if the
existing values 'fit' into the new datatype, and gives you an error if even
one row won't be convertible. But it doesn't actually make any changes. So
there can be lots of reads with no writes.
So ALTER TABLE operations can be one of the following:
1. Metadata only
2. Read the whole table
3. Read and WRITE the whole table
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://DVD.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:7BAEF159-5820-4749-8BA8-3920E0658CBF@.microsoft.com...
>
> Some operations are meta-data only operations where for other operations
> SQL Server need to actually modify each row. I would guess that there is
> some information in Books Online about this, but they can't document every
> possible change (from type - to type) and whether such change is meta-data
> only or not.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
>
|||If your column is a small int already, and you issue a command to change it
to a small int, since nothing actually needs to change I would expect this
to be a very fast operation.
However, if the data type actually changes, I would expect it to take some
time, depending on the size of the table. I'm not sure how much work SQL
Server needs to do to change the smallint to an int, maybe it needs to
allocate more space in every row?
"fniles" <fniles@.pfmail.com> wrote in message
news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
> When I do
> ALTER TABLE packet ALTER COLUMN packet_vol smallint NOT null
> it executes very fast, and the transaction log did not grow.
> But when I do
> ALTER TABLE packet ALTER COLUMN packet_vol int NOT null
> the transaction log keeps growing and growing, so I stopped it.
> Why when converting to smallint it went fast and the log did not grow but
> when converting to INT the log keep growing?
> Thanks.
>
> "rpresser" <rpresser@.gmail.com> wrote in message
> news:f239b870-1cc2-48e5-8496-3b692854bfea@.n77g2000hse.googlegroups.com...
> On Mar 20, 12:56 pm, "fniles" <fni...@.pfmail.com> wrote:
> Don't use enterprise manager. EMGR's approach is to duplicate the
> entire table with the new schema and then rename it back.
> Use an ALTER statement:
> ALTER TABLE tbl ALTER COLUMN col1 INT NOT NULL
> You will have to drop any indexes, references or constraints that use
> the column, and recreate them afterwards.
> It is still going to eat a lot of trans log space. You might need to
> change the model from full to simple first.
>
|||> However, if the data type actually changes, I would expect it to take some
> time, depending on the size of the table. I'm not sure how much work SQL
> Server needs to do to change the smallint to an int, maybe it needs to
> allocate more space in every row?
Page density will also likely come into play... if the pages are full then,
even though it's only a 2 byte change, there may need to be a large
re-allocation of data...
|||Thank you, all.
1. Metadata only -> is this the fast one ?
2. Read the whole table --> is this slow ?
3. Read and WRITE the whole table --> is this the slowest one ?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23RhZg80iIHA.5088@.TK2MSFTNGP02.phx.gbl...
> In addition to Tibor's reply, there is also a third type of change. For
> some datatype changes SQL Server has to inspect every row to see if the
> existing values 'fit' into the new datatype, and gives you an error if
> even one row won't be convertible. But it doesn't actually make any
> changes. So there can be lots of reads with no writes.
> So ALTER TABLE operations can be one of the following:
> 1. Metadata only
> 2. Read the whole table
> 3. Read and WRITE the whole table
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://DVD.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:7BAEF159-5820-4749-8BA8-3920E0658CBF@.microsoft.com...
>
|||Thank you, all.
I was doing some testing.
When I change the column to smallint, it was of type int originally. This
went fast.
Then I changed back from smallint to int, that's when it took a long time.
"Jim Underwood" <james.underwood_nospam@.fallonclinic.org> wrote in message
news:%235NGxW1iIHA.5504@.TK2MSFTNGP05.phx.gbl...
> If your column is a small int already, and you issue a command to change
> it to a small int, since nothing actually needs to change I would expect
> this to be a very fast operation.
> However, if the data type actually changes, I would expect it to take some
> time, depending on the size of the table. I'm not sure how much work SQL
> Server needs to do to change the smallint to an int, maybe it needs to
> allocate more space in every row?
> "fniles" <fniles@.pfmail.com> wrote in message
> news:u06CJYviIHA.4396@.TK2MSFTNGP04.phx.gbl...
>

Changing backup model before running reindex

Our txn log is ballooning after we re-index. I've see a recommendataion to
change to Bulk-logged before re-indexing and I'm wandering, will this break
my backup chain? Currently we are running full backups every Sun morning
and re-indexing Mon and Thurs.
Thanks.Are you doing log backups? If not, go to simple recovery model.
If you are doing log backup, then changing to bulk-logged will produce less
records in the ldf file,
but the following log backup can potentially be larger than if in full recov
ery. It will not
adversely affect your backup strategy, but two things to remember are:
If you have performed a bulk-logged operation, then you can't restore a log
backup to point-in time.
If db is in bulk-logged mode, you won't be able to backup log if a data file
crashes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kevin" <none@.none.com> wrote in message news:6D4D3721-2B84-4AC7-B19C-7307EC729604@.microsoft
.com...
> Our txn log is ballooning after we re-index. I've see a recommendataion t
o change to Bulk-logged
> before re-indexing and I'm wandering, will this break my backup chain? Cu
rrently we are running
> full backups every Sun morning and re-indexing Mon and Thurs.
> Thanks.|||Addition to Tibor's comment: Take backup before and after changing your
Recovery Model to SIMPLE\BULK LOGGED for indexing operations.
Ekrem nsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:8BF808EA-D953-4467-83BD-3CDC59691BC7@.microsoft.com...
> Are you doing log backups? If not, go to simple recovery model.
> If you are doing log backup, then changing to bulk-logged will produce
> less records in the ldf file, but the following log backup can potentially
> be larger than if in full recovery. It will not adversely affect your
> backup strategy, but two things to remember are:
> If you have performed a bulk-logged operation, then you can't restore a
> log backup to point-in time.
> If db is in bulk-logged mode, you won't be able to backup log if a data
> file crashes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kevin" <none@.none.com> wrote in message
> news:6D4D3721-2B84-4AC7-B19C-7307EC729604@.microsoft.com...
>

change user mode

Hi group,
I have a db that I changed from multiple user to single user, and want to
change it back now. However, it keeps giving me the message below. I have
checked with EM and sysprocesses that there is no connection to it. Any
suggestion what I can do?
Thanks.
Quentin
message:
Server: Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'mydb' cannot be made at this
time. The database is in single-user mode, and a user is currently connected
to it.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Try running the following script from Query Analyzer:
ALTER DATABASE mydb
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE mydb
SET MULTI_USER
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> I have a db that I changed from multiple user to single user, and want to
> change it back now. However, it keeps giving me the message below. I
have
> checked with EM and sysprocesses that there is no connection to it. Any
> suggestion what I can do?
> Thanks.
> Quentin
>
> message:
> Server: Msg 5064, Level 16, State 1, Line 1
> Changes to the state or options of database 'mydb' cannot be made at this
> time. The database is in single-user mode, and a user is currently
connected
> to it.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
>
|||Thanks Dan. The error message war the result of the T-sql statement.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e0yhhFTPEHA.632@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Try running the following script from Query Analyzer:
> ALTER DATABASE mydb
> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> ALTER DATABASE mydb
> SET MULTI_USER
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
> have
this
> connected
>

change user mode

Hi group,
I have a db that I changed from multiple user to single user, and want to
change it back now. However, it keeps giving me the message below. I have
checked with EM and sysprocesses that there is no connection to it. Any
suggestion what I can do?
Thanks.
Quentin
message:
Server: Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'mydb' cannot be made at this
time. The database is in single-user mode, and a user is currently connected
to it.
Server: Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.Try running the following script from Query Analyzer:
ALTER DATABASE mydb
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE mydb
SET MULTI_USER
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> I have a db that I changed from multiple user to single user, and want to
> change it back now. However, it keeps giving me the message below. I
have
> checked with EM and sysprocesses that there is no connection to it. Any
> suggestion what I can do?
> Thanks.
> Quentin
>
> message:
> Server: Msg 5064, Level 16, State 1, Line 1
> Changes to the state or options of database 'mydb' cannot be made at this
> time. The database is in single-user mode, and a user is currently
connected
> to it.
> Server: Msg 5069, Level 16, State 1, Line 1
> ALTER DATABASE statement failed.
>|||Thanks Dan. The error message war the result of the T-sql statement.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:e0yhhFTPEHA.632@.TK2MSFTNGP12.phx.gbl...
> Try running the following script from Query Analyzer:
> ALTER DATABASE mydb
> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> ALTER DATABASE mydb
> SET MULTI_USER
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uiK0UuSPEHA.1340@.TK2MSFTNGP12.phx.gbl...
to[vbcol=seagreen]
> have
this[vbcol=seagreen]
> connected
>

Change TinyInt identity into SmallInt

Hi,
I have a table BehaviorProp with an TinyInt identity BP_ID, I need to
change the column to a SmallInt.
I tried this:
drop index behaviorprop.PK_BehaviorProp
alter table behaviorprop alter column BP_ID smallint
but I get
An explicit DROP INDEX is not allowed on index
'behaviorprop.PK_BehaviorProp'. It is being used for PRIMARY KEY
constraint enforcement.
How can I remove the primary key constraint and add it again after the
change?
Thanks in advance,
Stijn Verrept.You need to drop the constraint instead...
begin tran
alter table behaviorprop drop constraint PK_Behaviourprop
alter table behaviorprop alter column BP_ID smallint
alter table behaviourprop add constraint PK_behaviourprop primary key (
bp_id )
commit tran
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:UcSdnQiSTJvOC_TZRVnytw@.scarlet.biz...
> Hi,
> I have a table BehaviorProp with an TinyInt identity BP_ID, I need to
> change the column to a SmallInt.
> I tried this:
> drop index behaviorprop.PK_BehaviorProp
> alter table behaviorprop alter column BP_ID smallint
> but I get
> An explicit DROP INDEX is not allowed on index
> 'behaviorprop.PK_BehaviorProp'. It is being used for PRIMARY KEY
> constraint enforcement.
> How can I remove the primary key constraint and add it again after the
> change?
> --
> Thanks in advance,
> Stijn Verrept.|||Hi
create table test (c1 tinyint identity(1,1) not null)
insert into test default values
alter table test alter column c1 int
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:UcSdnQiSTJvOC_TZRVnytw@.scarlet.biz...
> Hi,
> I have a table BehaviorProp with an TinyInt identity BP_ID, I need to
> change the column to a SmallInt.
> I tried this:
> drop index behaviorprop.PK_BehaviorProp
> alter table behaviorprop alter column BP_ID smallint
> but I get
> An explicit DROP INDEX is not allowed on index
> 'behaviorprop.PK_BehaviorProp'. It is being used for PRIMARY KEY
> constraint enforcement.
> How can I remove the primary key constraint and add it again after the
> change?
> --
> Thanks in advance,
> Stijn Verrept.|||Tony Rogerson wrote:

> You need to drop the constraint instead...
> begin tran
> alter table behaviorprop drop constraint PK_Behaviourprop
> alter table behaviorprop alter column BP_ID smallint
> alter table behaviourprop add constraint PK_behaviourprop primary key
> ( bp_id )
> commit tran
Thanks for your reply, but then I get:
'PK_Behaviourprop' is not a constraint.
and when I look in enterprise manager at constraints there aren't any
there, only the primary key (in the indexes tab).
Kind regards,
Stijn Verrept.|||Oh, I did not care about PRIMARY KEY constraint , see Tony's answer
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ucjy$rMeGHA.4304@.TK2MSFTNGP05.phx.gbl...
> Hi
> create table test (c1 tinyint identity(1,1) not null)
> insert into test default values
> alter table test alter column c1 int
>
> "Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
> news:UcSdnQiSTJvOC_TZRVnytw@.scarlet.biz...
>|||Need your version, use PRINT @.@.VERSION, the CREATE TABLE script including
indexes and any constraints.
And, the exact error message (cut and paste).
many thanks
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:SI-dnbH15pMVBvTZRVnyvQ@.scarlet.biz...
> Tony Rogerson wrote:
>
> Thanks for your reply, but then I get:
> 'PK_Behaviourprop' is not a constraint.
> and when I look in enterprise manager at constraints there aren't any
> there, only the primary key (in the indexes tab).
> --
> Kind regards,
> Stijn Verrept.|||Tony Rogerson wrote:

> Need your version, use PRINT @.@.VERSION, the CREATE TABLE script
> including indexes and any constraints.
> And, the exact error message (cut and paste).
> many thanks
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Error message:
Server: Msg 3728, Level 16, State 1, Line 2
'PK_Behaviourprop' is not a constraint.
Server: Msg 3727, Level 16, State 1, Line 2
Could not drop constraint. See previous errors.
The create table script is at: http://www.entrysoft.com/script.sql it
will create temptest database and will display the error when you try
to run
begin tran
alter table behaviorprop drop constraint PK_Behaviourprop
alter table behaviorprop alter column BP_ID smallint
alter table behaviourprop add constraint PK_behaviourprop primary key (
bp_id )
commit tran
It must be something simple.
Thanks for taking the time!
Kind regards,
Stijn Verrept.|||Not really answering the question but this script will work.... Good (new)
sql 2005 management studio and the create change script option on modify
table ;)
/* To prevent any potential data loss issues, you should review this script
in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.BehaviorProp
DROP CONSTRAINT FK_BehaviorProp_Users
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.BehaviorProp
DROP CONSTRAINT FK_BehaviorProp_Seniors
GO
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_BehaviorProp
(
BP_ID smallint NOT NULL IDENTITY (1, 1),
BP_SNID int NOT NULL,
BP_Stamp smalldatetime NOT NULL,
BP_Score1 tinyint NOT NULL,
BP_Score2 tinyint NOT NULL,
BP_Score3 tinyint NOT NULL,
BP_Score4 tinyint NOT NULL,
BP_Score5 tinyint NOT NULL,
BP_Score6 tinyint NOT NULL,
BP_Desc1 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc2 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc3 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc4 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc5 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_Desc6 varchar(200) COLLATE Latin1_General_CI_AS NULL,
BP_USID smallint NOT NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_BehaviorProp ON
GO
IF EXISTS(SELECT * FROM dbo.BehaviorProp)
EXEC('INSERT INTO dbo.Tmp_BehaviorProp (BP_ID, BP_SNID, BP_Stamp,
BP_Score1, BP_Score2, BP_Score3, BP_Score4, BP_Score5, BP_Score6, BP_Desc1,
BP_Desc2, BP_Desc3, BP_Desc4, BP_Desc5, BP_Desc6, BP_USID)
SELECT CONVERT(smallint, BP_ID), BP_SNID, BP_Stamp, BP_Score1, BP_Score2,
BP_Score3, BP_Score4, BP_Score5, BP_Score6, BP_Desc1, BP_Desc2, BP_Desc3,
BP_Desc4, BP_Desc5, BP_Desc6, BP_USID FROM dbo.BehaviorProp WITH (HOLDLOCK
TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_BehaviorProp OFF
GO
DROP TABLE dbo.BehaviorProp
GO
EXECUTE sp_rename N'dbo.Tmp_BehaviorProp', N'BehaviorProp', 'OBJECT'
GO
ALTER TABLE dbo.BehaviorProp ADD CONSTRAINT
PK_BehaviorProp PRIMARY KEY CLUSTERED
(
BP_ID
) ON [PRIMARY]
GO
ALTER TABLE dbo.BehaviorProp WITH NOCHECK ADD CONSTRAINT
FK_BehaviorProp_Seniors FOREIGN KEY
(
BP_SNID
) REFERENCES dbo.Seniors
(
SN_ID
) ON DELETE CASCADE
GO
ALTER TABLE dbo.BehaviorProp WITH NOCHECK ADD CONSTRAINT
FK_BehaviorProp_Users FOREIGN KEY
(
BP_USID
) REFERENCES dbo.Users
(
US_ID
) ON DELETE CASCADE
GO
COMMIT
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Stijn Verrept" <TURN_moc.tfosyrtne@.njits_AROUND> wrote in message
news:Ke-dnSqDzbrrO_TZRVny2w@.scarlet.biz...
> Tony Rogerson wrote:
>
> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005
> 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer
> Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> Error message:
> Server: Msg 3728, Level 16, State 1, Line 2
> 'PK_Behaviourprop' is not a constraint.
> Server: Msg 3727, Level 16, State 1, Line 2
> Could not drop constraint. See previous errors.
> The create table script is at: http://www.entrysoft.com/script.sql it
> will create temptest database and will display the error when you try
> to run
> begin tran
> alter table behaviorprop drop constraint PK_Behaviourprop
> alter table behaviorprop alter column BP_ID smallint
> alter table behaviourprop add constraint PK_behaviourprop primary key (
> bp_id )
> commit tran
>
> It must be something simple.
> Thanks for taking the time!
>
> --
> Kind regards,
> Stijn Verrept.|||Tony Rogerson wrote:

> Not really answering the question but this script will work.... Good
> (new) sql 2005 management studio and the create change script option
> on modify table ;)
This works indeed! Thanks!
Kind regards,
Stijn Verrept.

Change SQL Server account password

I am looking for an *easy* way to allow *non-technical* end users to
change their SQL Server account password. Does anyone have any
suggestions? I am not a programmer by trade, but I can write scripts.
It would be great if I could do this for my Oracle databases, too.
Thanks,
Aaron
How do your users connect? Through some sort of application?
It would be pretty simple to have an application wrapper around sp_password.
Of course, if they are using Query Analyzer and know how to submit thier own
queries... I don't think it's much of a burden to show people the syntax for
sp_password and let them run it on thier own.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Vols Fan" <volsfan1998@.hotmail.com> wrote in message
news:2489efb1.0408181426.14242c24@.posting.google.c om...
> I am looking for an *easy* way to allow *non-technical* end users to
> change their SQL Server account password. Does anyone have any
> suggestions? I am not a programmer by trade, but I can write scripts.
> It would be great if I could do this for my Oracle databases, too.
> Thanks,
> Aaron
|||Anyone have a little application that can do this?
I have the same exact question. The apps I am using are vendor written, so I don't have access to the code.|||I'm looking for this answer too. Did you come up with anything? I need an easy way for users to change their SQL account passwords.

Change ownership of all objects owned by

HI, I thought I read somewhere about a Stored Procedure that can be used to
change all objects owned by x to owned by y. Does anyone know of such SP,
tell me about it, please. OR Tell me a better way of changing ownership of
ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
another SQL login I've created with SA privileges, Then I can change the PW
of sa. OR can this not be done. ? PLEASE ADVISE!!This free tool may be a start...
http://searchsqlserver.techtarget.c...4&ad=524311USCA
It uses a parameter of 'IF ^$^ <> ^dbo^ EXECUTE sp_changeobjectowner
^$.*^,^dbo^' to change the owner of selected objects.
"WANNABE" wrote:

> HI, I thought I read somewhere about a Stored Procedure that can be used t
o
> change all objects owned by x to owned by y. Does anyone know of such SP,
> tell me about it, please. OR Tell me a better way of changing ownership
of
> ALL (DTS, Jobs, anything that has sa and the sa PW assigned to it) to
> another SQL login I've created with SA privileges, Then I can change the P
W
> of sa. OR can this not be done. ? PLEASE ADVISE!!
>
>