Changing SA password to "blank" (null)

We are about to start a migration and one of the requirements from the scrip
t
is to have both sa passwords set to blank (no password) while the migration
takes place. Where do I go to change the SA password on a sql 2000 server an
d
a sql 7.0 server?
My expertise isn't in SQL so any steps would be very helpful!
Thanks!!!"Candie" <Candie@.discussions.microsoft.com> wrote in message
news:1A4D7D3E-2075-4D88-97C0-B192663943FB@.microsoft.com...
> We are about to start a migration and one of the requirements from the
> script
> is to have both sa passwords set to blank (no password) while the
> migration
> takes place. Where do I go to change the SA password on a sql 2000 server
> and
> a sql 7.0 server?
> My expertise isn't in SQL so any steps would be very helpful!
> Thanks!!!
SQL Enterprise Manager -> Security
Pick the sa login and change the password.
Rick Sawtell
MCT, MCSD, MCDBA|||Query Analyzer works.
exec sp_password
Read about it within Books Online (within the SQL Server program group).
Keith
"Candie" <Candie@.discussions.microsoft.com> wrote in message
news:1A4D7D3E-2075-4D88-97C0-B192663943FB@.microsoft.com...
> We are about to start a migration and one of the requirements from the
script
> is to have both sa passwords set to blank (no password) while the
migration
> takes place. Where do I go to change the SA password on a sql 2000 server
and
> a sql 7.0 server?
> My expertise isn't in SQL so any steps would be very helpful!
> Thanks!!!|||"Candie" <Candie@.discussions.microsoft.com> wrote in message
news:1A4D7D3E-2075-4D88-97C0-B192663943FB@.microsoft.com...
> We are about to start a migration and one of the requirements from the
script
> is to have both sa passwords set to blank (no password) while the
migration
> takes place.
Huh? I'd really question WHY?
In any case if you do this, make darn sure you have the machines isolated
from the Internet via a firewall or something. A number of exploits exist
that look for and exploit blank passwords.

> Where do I go to change the SA password on a sql 2000 server and
> a sql 7.0 server?
> My expertise isn't in SQL so any steps would be very helpful!
> Thanks!!!

Changing SA password to "blank" (null)

We are about to start a migration and one of the requirements from the script
is to have both sa passwords set to blank (no password) while the migration
takes place. Where do I go to change the SA password on a sql 2000 server and
a sql 7.0 server?
My expertise isn't in SQL so any steps would be very helpful!
Thanks!!!
"Candie" <Candie@.discussions.microsoft.com> wrote in message
news:1A4D7D3E-2075-4D88-97C0-B192663943FB@.microsoft.com...
> We are about to start a migration and one of the requirements from the
> script
> is to have both sa passwords set to blank (no password) while the
> migration
> takes place. Where do I go to change the SA password on a sql 2000 server
> and
> a sql 7.0 server?
> My expertise isn't in SQL so any steps would be very helpful!
> Thanks!!!
SQL Enterprise Manager -> Security
Pick the sa login and change the password.
Rick Sawtell
MCT, MCSD, MCDBA
|||Query Analyzer works.
exec sp_password
Read about it within Books Online (within the SQL Server program group).
Keith
"Candie" <Candie@.discussions.microsoft.com> wrote in message
news:1A4D7D3E-2075-4D88-97C0-B192663943FB@.microsoft.com...
> We are about to start a migration and one of the requirements from the
script
> is to have both sa passwords set to blank (no password) while the
migration
> takes place. Where do I go to change the SA password on a sql 2000 server
and
> a sql 7.0 server?
> My expertise isn't in SQL so any steps would be very helpful!
> Thanks!!!
|||"Candie" <Candie@.discussions.microsoft.com> wrote in message
news:1A4D7D3E-2075-4D88-97C0-B192663943FB@.microsoft.com...
> We are about to start a migration and one of the requirements from the
script
> is to have both sa passwords set to blank (no password) while the
migration
> takes place.
Huh? I'd really question WHY?
In any case if you do this, make darn sure you have the machines isolated
from the Internet via a firewall or something. A number of exploits exist
that look for and exploit blank passwords.

> Where do I go to change the SA password on a sql 2000 server and
> a sql 7.0 server?
> My expertise isn't in SQL so any steps would be very helpful!
> Thanks!!!

Changing SA password to "blank" (null)

We are about to start a migration and one of the requirements from the script
is to have both sa passwords set to blank (no password) while the migration
takes place. Where do I go to change the SA password on a sql 2000 server and
a sql 7.0 server?
My expertise isn't in SQL so any steps would be very helpful!
Thanks!!!"Candie" <Candie@.discussions.microsoft.com> wrote in message
news:1A4D7D3E-2075-4D88-97C0-B192663943FB@.microsoft.com...
> We are about to start a migration and one of the requirements from the
> script
> is to have both sa passwords set to blank (no password) while the
> migration
> takes place. Where do I go to change the SA password on a sql 2000 server
> and
> a sql 7.0 server?
> My expertise isn't in SQL so any steps would be very helpful!
> Thanks!!!
SQL Enterprise Manager -> Security
Pick the sa login and change the password.
Rick Sawtell
MCT, MCSD, MCDBA|||Query Analyzer works.
exec sp_password
Read about it within Books Online (within the SQL Server program group).
--
Keith
"Candie" <Candie@.discussions.microsoft.com> wrote in message
news:1A4D7D3E-2075-4D88-97C0-B192663943FB@.microsoft.com...
> We are about to start a migration and one of the requirements from the
script
> is to have both sa passwords set to blank (no password) while the
migration
> takes place. Where do I go to change the SA password on a sql 2000 server
and
> a sql 7.0 server?
> My expertise isn't in SQL so any steps would be very helpful!
> Thanks!!!|||"Candie" <Candie@.discussions.microsoft.com> wrote in message
news:1A4D7D3E-2075-4D88-97C0-B192663943FB@.microsoft.com...
> We are about to start a migration and one of the requirements from the
script
> is to have both sa passwords set to blank (no password) while the
migration
> takes place.
Huh? I'd really question WHY?
In any case if you do this, make darn sure you have the machines isolated
from the Internet via a firewall or something. A number of exploits exist
that look for and exploit blank passwords.
> Where do I go to change the SA password on a sql 2000 server and
> a sql 7.0 server?
> My expertise isn't in SQL so any steps would be very helpful!
> Thanks!!!

Changing record number

Hi,
I have cancelled some records due to null fields,then the record number is not coming in order.how to code it to get it in orderwow, that's a short description.........
So I'm gonna have to guess the detail. You are probably, from the brief description, experiencing a problem because your report is having trouble with the nulls. More information, including database type, OS, CR version always helps us better assess the problem.
I think you need a formula to convert fields likely to be nulls, eg:
@.AccountBalance

if IsNull({tablename.AccountBalance}) then
formula = 0
else
formula = {tablename.AccountBalance}
end if

I've deliberatley kept this simple to demonstrate the principles.

Dave

changing nullability- any other options besides alter

I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegroups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>

changing nullability- any other options besides alter

I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegroups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>

changing nullability- any other options besides alter

I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.
TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark
|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegro ups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>

Changing NULL, to NOT NULL

SQL Server 2005
When I run the command:
alter table FileRepository alter column parentID int not null
I get the message:
Msg 5074, Level 16, State 1, Line 1
The index 'IX_FileRepository' is dependent on column 'parentID'.
Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
'parentID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN parentID failed because one or more objects access
this column.
No problem on the index as that is easy to drop and recreate. But what is
this stuff about statistics and how do I deal with it?
FWIW, I know just enough about 2000 to do my job, but diddle about 2005.You drop statistics in the same way that you drop indexes. Typically, you
don't have both statistics and indexes on the same column - except, say, if
you have an index on a single column and statistics on a pair of columns,
and vice-versa. In your case, it looks like the Database Tuning Advisor
recommended the stats and the DBA running the DTA allowed them to be built.
You'll have to run:
DROP STATISTICS FileRepository ._dta_stat_191339746_1_12_11_2
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:C172210B-9AF9-438D-AB31-58B55DFC5367@.microsoft.com...
SQL Server 2005
When I run the command:
alter table FileRepository alter column parentID int not null
I get the message:
Msg 5074, Level 16, State 1, Line 1
The index 'IX_FileRepository' is dependent on column 'parentID'.
Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
'parentID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN parentID failed because one or more objects access
this column.
No problem on the index as that is easy to drop and recreate. But what is
this stuff about statistics and how do I deal with it?
FWIW, I know just enough about 2000 to do my job, but diddle about 2005.|||Thank you sir.
After having a further conversation, it seems they have tried the alter
already and it ran for several hours before timing out ( a very large table).
This implies to me that it is doing a table rebuild, even though all data in
that column is already NOT NULL and only the DDL needs to be changed.
Am I probably right here? Can we make this type of change without it taking
more than a few seconds to do the alter?
"Tom Moreau" wrote:
> You drop statistics in the same way that you drop indexes. Typically, you
> don't have both statistics and indexes on the same column - except, say, if
> you have an index on a single column and statistics on a pair of columns,
> and vice-versa. In your case, it looks like the Database Tuning Advisor
> recommended the stats and the DBA running the DTA allowed them to be built.
> You'll have to run:
> DROP STATISTICS FileRepository ._dta_stat_191339746_1_12_11_2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:C172210B-9AF9-438D-AB31-58B55DFC5367@.microsoft.com...
> SQL Server 2005
> When I run the command:
> alter table FileRepository alter column parentID int not null
> I get the message:
> Msg 5074, Level 16, State 1, Line 1
> The index 'IX_FileRepository' is dependent on column 'parentID'.
> Msg 5074, Level 16, State 1, Line 1
> The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
> 'parentID'.
> Msg 4922, Level 16, State 9, Line 1
> ALTER TABLE ALTER COLUMN parentID failed because one or more objects access
> this column.
>
> No problem on the index as that is easy to drop and recreate. But what is
> this stuff about statistics and how do I deal with it?
> FWIW, I know just enough about 2000 to do my job, but diddle about 2005.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0A6F_01C77871.851AEB80
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Well, that's the issue. If you have a lot of data, it will take a long
time. Your log may fill up during that time. Ultimately, you may need to
create a new table, with the intended DDL. Then, you move the data in
chunks to the other table. Meanwhile, you create a partitioned view (with
the same name as the old table) that includes both tables. However, you
will need to add INSTEAD OF triggers for any INSERT/UPDATE/DELETE activity
that will occur during that time. Once all data have been moved, you can
drop the original table and view, renaming the new table.
The attached code has an example.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:3E756320-3BA2-46CA-A2E6-9DBD404D83B4@.microsoft.com...
Thank you sir.
After having a further conversation, it seems they have tried the alter
already and it ran for several hours before timing out ( a very large
table).
This implies to me that it is doing a table rebuild, even though all data in
that column is already NOT NULL and only the DDL needs to be changed.
Am I probably right here? Can we make this type of change without it taking
more than a few seconds to do the alter?
"Tom Moreau" wrote:
> You drop statistics in the same way that you drop indexes. Typically, you
> don't have both statistics and indexes on the same column - except, say,
> if
> you have an index on a single column and statistics on a pair of columns,
> and vice-versa. In your case, it looks like the Database Tuning Advisor
> recommended the stats and the DBA running the DTA allowed them to be
> built.
> You'll have to run:
> DROP STATISTICS FileRepository ._dta_stat_191339746_1_12_11_2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:C172210B-9AF9-438D-AB31-58B55DFC5367@.microsoft.com...
> SQL Server 2005
> When I run the command:
> alter table FileRepository alter column parentID int not null
> I get the message:
> Msg 5074, Level 16, State 1, Line 1
> The index 'IX_FileRepository' is dependent on column 'parentID'.
> Msg 5074, Level 16, State 1, Line 1
> The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
> 'parentID'.
> Msg 4922, Level 16, State 9, Line 1
> ALTER TABLE ALTER COLUMN parentID failed because one or more objects
> access
> this column.
>
> No problem on the index as that is easy to drop and recreate. But what is
> this stuff about statistics and how do I deal with it?
> FWIW, I know just enough about 2000 to do my job, but diddle about 2005.
>
--=_NextPart_000_0A6F_01C77871.851AEB80
Content-Type: text/plain;
name="Alter.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="Alter.sql"
-- Listing 1
create table dbo.t1
(
id int primary key nonclustered
, dt datetime not null
, junk varchar (20) not null
)
create clustered index C_t1 on dbo.t1 (dt)
create table dbo.t2
(
id int primary key nonclustered
, dt datetime not null
, junk varchar (20) not null
, other1 bit not null
default (0)
, other2 varchar (30) null
)
create clustered index C_t2 on dbo.t2 (dt)
create view dbo.t
as
select
id
, dt
, junk
from
dbo.t2
union all
select
id
, dt
, junk
from
dbo.t1
go
-- Listing 2
create table dbo.threshold
(
threshold datetime not null
)
insert dbo.threshold values ('1995-01-01')
go
-- Listing 3
create trigger tri_t on dbo.t instead of insert
as
insert dbo.t1 (id, dt, junk)
select id, dt, junk
from inserted
where dt >= (select threshold from dbo.threshold)
insert dbo.t2 (id, dt, junk)
select id, dt, junk
from inserted
where dt < (select threshold from dbo.threshold)
go
-- Listing 4
create trigger tru_t on dbo.t instead of update
as
if (update (dt))
begin
-- no crossing of threshold
update x
set
dt = i.dt
, junk = i.junk
from
inserted i
join deleted d on d.id = i.id
join dbo.t2 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt < z.threshold
and d.dt < z.threshold
update x
set
dt = i.dt
, junk = i.junk
from
inserted i
join deleted d on d.id = i.id
join dbo.t1 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt >= z.threshold
and d.dt >= z.threshold
-- threshold crossed
delete x
from
inserted i
join deleted d on d.id = i.id
join dbo.t2 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt >= z.threshold
and d.dt < z.threshold
delete x
from
inserted i
join deleted d on d.id = i.id
join dbo.t1 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt < z.threshold
and d.dt >= z.threshold
insert dbo.t1 (id, dt, junk)
select i.id, i.dt, i.junk
from
inserted i
join deleted d on d.id = i.id
cross join
dbo.threshold z
where
i.dt >= z.threshold
and d.dt < z.threshold
insert dbo.t2 (id, dt, junk)
select i.id, i.dt, i.junk
from
inserted i
join deleted d on d.id = i.id
cross join
dbo.threshold z
where
i.dt < z.threshold
and d.dt >= z.threshold
end
else
begin
update x
set
junk = i.junk
from
inserted i
join dbo.t1 x on x.id = i.id
where
i.dt >= (select threshold from dbo.threshold)
update x
set
junk = i.junk
from
inserted i
join dbo.t2 x on x.id = i.id
where
i.dt < (select threshold from dbo.threshold)
end
go
-- Listing 5
create trigger tru_t on dbo.t instead of delete
as
if @.@.ROWCOUNT = 0
return
delete x
from
deleted d
join dbo.t1 x on x.id = d.id
delete x
from
deleted d
join dbo.t2 x on x.id = d.id
go
-- populate the table
declare
@.year smallint
, @.month tinyint
, @.day tinyint
, @.id int
, @.date datetime
, @.icount int
, @.max int
select
@.year = 1995
, @.id = 1
while @.year < 2004
begin
set @.month = 1
while @.month <= 12
begin
set @.day = 1
while @.day <= case when @.month in (1, 3, 5, 7, 8, 10, 12) then 31
when @.month in (4, 6, 9, 11) then 30
when @.year % 4 = 0 then 29
else 28 end
begin
set @.date = cast (@.year as char (4))
+ replace (str (@.month, 2), ' ', '0')
+ replace (str (@.day, 2), ' ', '0')
select
@.max = rand () * 1000
, @.icount = 1
while @.icount <= @.max
begin
insert t1 (id, dt, junk)
values (@.id, @.date, replicate ('X', 20))
select
@.icount = @.icount + 1
, @.id = @.id + 1
end
set @.day = @.day + 1
end
set @.month = @.month + 1
end
set @.year = @.year + 1
end
go
-- Listing 6
-- migrate the data
declare
@.date datetime
, @.str varchar (8000)
select
@.date = min (dt)
from
t1
while @.date is not null and not datepart (hh, getdate()) between 8 and 18
begin
begin tran
update threshold
set
threshold = dateadd (dd, 1, @.date)
insert t2 (id, dt, junk)
select id, dt, junk
from t1
where dt = @.date
delete t1
where dt = @.date
commit tran
select
@.date = min (dt)
from
t1
end
/*
drop view t
drop table t1
drop table t2
drop table threshold
*/
--=_NextPart_000_0A6F_01C77871.851AEB80--|||> After having a further conversation, it seems they have tried the alter
> already and it ran for several hours before timing out ( a very large
> table).
> This implies to me that it is doing a table rebuild, even though all data
> in
> that column is already NOT NULL and only the DDL needs to be changed.
> Am I probably right here?
That depends, did they open Enterprise Manager and click around in the GUI
to make the change? By default, in most cases, when you click OK Enterprise
Manager will do exactly that behind your back: create a new table, move the
data over, drop the old table, rename the new table to the old name, etc.
No exactly pretty.
You should probably wait to attempt this during a maintenance window (e.g.
on a Sunday at midnight), and even enforce it with setting the database to
SINGLE_USER, and then you won't have to worry about blocking users, or being
blocked by users, or being slowed down by other activity in the database.
If it still takes too long, you may have to use Tom's solution, which is a
bit more complex but is certainly better than what Enterprise Manager tries
to do.|||> You should probably wait to attempt this
And by "this" I mean an ALTER statement in Query Analyzer, not clicking OK
to the change in Enterprise Manager.|||Yes, they tried to do it through the GUI.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > After having a further conversation, it seems they have tried the alter
> > already and it ran for several hours before timing out ( a very large
> > table).
> > This implies to me that it is doing a table rebuild, even though all data
> > in
> > that column is already NOT NULL and only the DDL needs to be changed.
> >
> > Am I probably right here?
> That depends, did they open Enterprise Manager and click around in the GUI
> to make the change? By default, in most cases, when you click OK Enterprise
> Manager will do exactly that behind your back: create a new table, move the
> data over, drop the old table, rename the new table to the old name, etc.
> No exactly pretty.
> You should probably wait to attempt this during a maintenance window (e.g.
> on a Sunday at midnight), and even enforce it with setting the database to
> SINGLE_USER, and then you won't have to worry about blocking users, or being
> blocked by users, or being slowed down by other activity in the database.
> If it still takes too long, you may have to use Tom's solution, which is a
> bit more complex but is certainly better than what Enterprise Manager tries
> to do.
>
>|||At present, I'm on a dev server (our only 2005 box).
As it happens, the ALTER statement does see that it's a simple DDL change
and took a couple of seconds.
I'm releived that SQL Server is smart enough to do that.
Thank you very much.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > After having a further conversation, it seems they have tried the alter
> > already and it ran for several hours before timing out ( a very large
> > table).
> > This implies to me that it is doing a table rebuild, even though all data
> > in
> > that column is already NOT NULL and only the DDL needs to be changed.
> >
> > Am I probably right here?
> That depends, did they open Enterprise Manager and click around in the GUI
> to make the change? By default, in most cases, when you click OK Enterprise
> Manager will do exactly that behind your back: create a new table, move the
> data over, drop the old table, rename the new table to the old name, etc.
> No exactly pretty.
> You should probably wait to attempt this during a maintenance window (e.g.
> on a Sunday at midnight), and even enforce it with setting the database to
> SINGLE_USER, and then you won't have to worry about blocking users, or being
> blocked by users, or being slowed down by other activity in the database.
> If it still takes too long, you may have to use Tom's solution, which is a
> bit more complex but is certainly better than what Enterprise Manager tries
> to do.
>
>

Changing NULL to zero

Greetings folks,
I have a dilemma that I thought was going to be a slam dunk. I have a
table with several integer and money columns that contain about 50%
null values. I need to sum them, and have the nulls treated as zero.
Secondly, I need to insert all of the values into another table. If
there happens to be a null in the source columns, it needs to become a
zero in the destination. (I know someone is going to fuss about the
storing of calculated values. Not my choice, I don't get a say-so).
The tables have millions of records, and there are roughly 40 int and
money columns. So I'm looking for a solution that will perform well,
and will not require me to update those 40 columns individually. Any
suggestions?
thanks,
CL--isnull will return the column if it is not null, and the second value (in
this case 0) if it is null
select isnull(Column1, 0) from table
or
--Coalesce returns the first non-null value in the list. I think if you
have only one value you want to use isnull, but here is is anyway...
select coalesce(column1,0) from table
"CL" <clhawkins74@.yahoo.com> wrote in message
news:1137790173.259283.202260@.g49g2000cwa.googlegroups.com...
> Greetings folks,
> I have a dilemma that I thought was going to be a slam dunk. I have a
> table with several integer and money columns that contain about 50%
> null values. I need to sum them, and have the nulls treated as zero.
> Secondly, I need to insert all of the values into another table. If
> there happens to be a null in the source columns, it needs to become a
> zero in the destination. (I know someone is going to fuss about the
> storing of calculated values. Not my choice, I don't get a say-so).
> The tables have millions of records, and there are roughly 40 int and
> money columns. So I'm looking for a solution that will perform well,
> and will not require me to update those 40 columns individually. Any
> suggestions?
> thanks,
> CL
>

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto Porter
On Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
--
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto PorterOn Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing Data Format On Select

Hey all,
I have a basic table that looks something like this.

CREATE TABLE MyTable
(
ID INT IDENTITY PRIMARY KEY,
Company_ID INT NOT NULL,
Round VARCHAR(50) NOT NULL,
Details VARCHAR(250) NOT NULL
)

It has a few rows of data that look like this:

Identity Company_ID Round Details
--------------
1 5 A Blah, blah.
2 5 B Generic data, blah blah.
3 5 WERT More generic blah blah.

Now what i'm trying to do during my select statement is select all the rows
that belong to company_id 5 but if any of the rows round value contains the
text "WERT" convert that text into just a "--" for presentation purposes,
but still select that row. I can't seem to figure out how i would transform
the text in the select statement? My immediate thought was substring /
replace but i would need to combine it with an if else statement which i've
no idea how to make work in a select (sub-query maybe?) statement. Is this
possible? Perhaps i'm stuck iterating through the returned data within the
client application before presenting?

Any help, as always, would be greatly appreciated.

MuhdMuhd,

SELECT ID, Company_ID,
[Round] = CASE [Round] WHEN 'WERT' THEN '--' ELSE [Round] END,
Details
FROM MyTable
WHERE Company_ID = 5

-Andy

"Muhd" <eat@.joes.com> wrote in message news:7MZ4d.91084$%S.84951@.pd7tw2no...
> Hey all,
> I have a basic table that looks something like this.
> CREATE TABLE MyTable
> (
> ID INT IDENTITY PRIMARY KEY,
> Company_ID INT NOT NULL,
> Round VARCHAR(50) NOT NULL,
> Details VARCHAR(250) NOT NULL
> )
> It has a few rows of data that look like this:
> Identity Company_ID Round Details
> --------------
> 1 5 A Blah, blah.
> 2 5 B Generic data, blah blah.
> 3 5 WERT More generic blah blah.
>
> Now what i'm trying to do during my select statement is select all the
> rows that belong to company_id 5 but if any of the rows round value
> contains the text "WERT" convert that text into just a "--" for
> presentation purposes, but still select that row. I can't seem to figure
> out how i would transform the text in the select statement? My immediate
> thought was substring / replace but i would need to combine it with an if
> else statement which i've no idea how to make work in a select (sub-query
> maybe?) statement. Is this possible? Perhaps i'm stuck iterating through
> the returned data within the client application before presenting?
> Any help, as always, would be greatly appreciated.
> Muhd|||Although Andy has shown you how you can accomplish the task using
Transact-SQL, formatting data for presentation purposes is generally best
handled on the client side, IMHO. Most programming languages provide a
number of methods to format data.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Muhd" <eat@.joes.com> wrote in message news:7MZ4d.91084$%S.84951@.pd7tw2no...
> Hey all,
> I have a basic table that looks something like this.
> CREATE TABLE MyTable
> (
> ID INT IDENTITY PRIMARY KEY,
> Company_ID INT NOT NULL,
> Round VARCHAR(50) NOT NULL,
> Details VARCHAR(250) NOT NULL
> )
> It has a few rows of data that look like this:
> Identity Company_ID Round Details
> --------------
> 1 5 A Blah, blah.
> 2 5 B Generic data, blah blah.
> 3 5 WERT More generic blah blah.
>
> Now what i'm trying to do during my select statement is select all the
> rows that belong to company_id 5 but if any of the rows round value
> contains the text "WERT" convert that text into just a "--" for
> presentation purposes, but still select that row. I can't seem to figure
> out how i would transform the text in the select statement? My immediate
> thought was substring / replace but i would need to combine it with an if
> else statement which i've no idea how to make work in a select (sub-query
> maybe?) statement. Is this possible? Perhaps i'm stuck iterating through
> the returned data within the client application before presenting?
> Any help, as always, would be greatly appreciated.
> Muhd|||While the front end is a very good place for presentation or value decoding
...
having a record transformation in the database allows the logic to be
centralized and recorded somewhere.

what if in addition to WERT you need to ignore SPAM, just add a row to the
table. no front end changes needed.

create a decoder table and join to that returning --

create view round_decoder
(
Round VARCHAR(50) NOT NULL,
DisplayName VARCHAR(50) NOT NULL
)

insert into round_decoder ( 'WERT' , '--' )

SELECT
Identity,
Company_ID ,
IsNull( d.DisplayName , x.Round ) as Round ,
Details
FROM MyTable x
LEFT JOIN round_decoder d on x.Round = d.Round

you can query this decoder table for drop down lists or other data
selection / presentation stuff in the front end.
i would recommend a reusable object that get's this data and does the
decoding in the front end.

"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:pfd6d.1939$zc1.1654@.newssvr12.news.prodigy.co m...
> Although Andy has shown you how you can accomplish the task using
> Transact-SQL, formatting data for presentation purposes is generally best
> handled on the client side, IMHO. Most programming languages provide a
> number of methods to format data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Muhd" <eat@.joes.com> wrote in message
news:7MZ4d.91084$%S.84951@.pd7tw2no...
> > Hey all,
> > I have a basic table that looks something like this.
> > CREATE TABLE MyTable
> > (
> > ID INT IDENTITY PRIMARY KEY,
> > Company_ID INT NOT NULL,
> > Round VARCHAR(50) NOT NULL,
> > Details VARCHAR(250) NOT NULL
> > )
> > It has a few rows of data that look like this:
> > Identity Company_ID Round Details
> > --------------
> > 1 5 A Blah, blah.
> > 2 5 B Generic data, blah
blah.
> > 3 5 WERT More generic blah blah.
> > Now what i'm trying to do during my select statement is select all the
> > rows that belong to company_id 5 but if any of the rows round value
> > contains the text "WERT" convert that text into just a "--" for
> > presentation purposes, but still select that row. I can't seem to
figure
> > out how i would transform the text in the select statement? My
immediate
> > thought was substring / replace but i would need to combine it with an
if
> > else statement which i've no idea how to make work in a select
(sub-query
> > maybe?) statement. Is this possible? Perhaps i'm stuck iterating
through
> > the returned data within the client application before presenting?
> > Any help, as always, would be greatly appreciated.
> > Muhd|||Thanks for that, David... Great idea!

-Andy

"David Rawheiser" <rawhide58@.hotmail.com> wrote in message
news:4Cx6d.645799$Gx4.11917@.bgtnsc04-news.ops.worldnet.att.net...
> While the front end is a very good place for presentation or value
> decoding
> ...
> having a record transformation in the database allows the logic to be
> centralized and recorded somewhere.
> what if in addition to WERT you need to ignore SPAM, just add a row to the
> table. no front end changes needed.
> create a decoder table and join to that returning --
> create view round_decoder
> (
> Round VARCHAR(50) NOT NULL,
> DisplayName VARCHAR(50) NOT NULL
> )
> insert into round_decoder ( 'WERT' , '--' )
> SELECT
> Identity,
> Company_ID ,
> IsNull( d.DisplayName , x.Round ) as Round ,
> Details
> FROM MyTable x
> LEFT JOIN round_decoder d on x.Round = d.Round
> you can query this decoder table for drop down lists or other data
> selection / presentation stuff in the front end.
> i would recommend a reusable object that get's this data and does the
> decoding in the front end.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:pfd6d.1939$zc1.1654@.newssvr12.news.prodigy.co m...
>> Although Andy has shown you how you can accomplish the task using
>> Transact-SQL, formatting data for presentation purposes is generally best
>> handled on the client side, IMHO. Most programming languages provide a
>> number of methods to format data.
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "Muhd" <eat@.joes.com> wrote in message
> news:7MZ4d.91084$%S.84951@.pd7tw2no...
>> > Hey all,
>> > I have a basic table that looks something like this.
>>> > CREATE TABLE MyTable
>> > (
>> > ID INT IDENTITY PRIMARY KEY,
>> > Company_ID INT NOT NULL,
>> > Round VARCHAR(50) NOT NULL,
>> > Details VARCHAR(250) NOT NULL
>> > )
>>> > It has a few rows of data that look like this:
>>> > Identity Company_ID Round Details
>> > --------------
>> > 1 5 A Blah, blah.
>> > 2 5 B Generic data, blah
> blah.
>> > 3 5 WERT More generic blah blah.
>>>> > Now what i'm trying to do during my select statement is select all the
>> > rows that belong to company_id 5 but if any of the rows round value
>> > contains the text "WERT" convert that text into just a "--" for
>> > presentation purposes, but still select that row. I can't seem to
> figure
>> > out how i would transform the text in the select statement? My
> immediate
>> > thought was substring / replace but i would need to combine it with an
> if
>> > else statement which i've no idea how to make work in a select
> (sub-query
>> > maybe?) statement. Is this possible? Perhaps i'm stuck iterating
> through
>> > the returned data within the client application before presenting?
>>> > Any help, as always, would be greatly appreciated.
>>> > Muhd
>>>
>>|||Thanks all !!!

I've decided to keep the presentation logic on the presentation tier but i
did rig up a "decoder" table to give it a try and it worked for me really
well. It's something i'll have to add to my toolkit of tricks in case i
need it in the future.

Thanks,
Muhd.

"Andy Williams" <f_u_b_a_r_1_1_1_9@.y_a_h_o_o_._c_o_m> wrote in message
news:c1z6d.11644$Qv5.5768@.newssvr33.news.prodigy.c om...
> Thanks for that, David... Great idea!
> -Andy
> "David Rawheiser" <rawhide58@.hotmail.com> wrote in message
> news:4Cx6d.645799$Gx4.11917@.bgtnsc04-news.ops.worldnet.att.net...
>> While the front end is a very good place for presentation or value
>> decoding
>> ...
>> having a record transformation in the database allows the logic to be
>> centralized and recorded somewhere.
>>
>> what if in addition to WERT you need to ignore SPAM, just add a row to
>> the
>> table. no front end changes needed.
>>
>> create a decoder table and join to that returning --
>>
>> create view round_decoder
>> (
>> Round VARCHAR(50) NOT NULL,
>> DisplayName VARCHAR(50) NOT NULL
>> )
>>
>> insert into round_decoder ( 'WERT' , '--' )
>>
>> SELECT
>> Identity,
>> Company_ID ,
>> IsNull( d.DisplayName , x.Round ) as Round ,
>> Details
>> FROM MyTable x
>> LEFT JOIN round_decoder d on x.Round = d.Round
>>
>> you can query this decoder table for drop down lists or other data
>> selection / presentation stuff in the front end.
>> i would recommend a reusable object that get's this data and does the
>> decoding in the front end.
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:pfd6d.1939$zc1.1654@.newssvr12.news.prodigy.co m...
>>> Although Andy has shown you how you can accomplish the task using
>>> Transact-SQL, formatting data for presentation purposes is generally
>>> best
>>> handled on the client side, IMHO. Most programming languages provide a
>>> number of methods to format data.
>>>
>>> --
>>> Hope this helps.
>>>
>>> Dan Guzman
>>> SQL Server MVP
>>>
>>> "Muhd" <eat@.joes.com> wrote in message
>> news:7MZ4d.91084$%S.84951@.pd7tw2no...
>>> > Hey all,
>>> > I have a basic table that looks something like this.
>>>>> > CREATE TABLE MyTable
>>> > (
>>> > ID INT IDENTITY PRIMARY KEY,
>>> > Company_ID INT NOT NULL,
>>> > Round VARCHAR(50) NOT NULL,
>>> > Details VARCHAR(250) NOT NULL
>>> > )
>>>>> > It has a few rows of data that look like this:
>>>>> > Identity Company_ID Round Details
>>> > --------------
>>> > 1 5 A Blah, blah.
>>> > 2 5 B Generic data, blah
>> blah.
>>> > 3 5 WERT More generic blah blah.
>>>>>>> > Now what i'm trying to do during my select statement is select all the
>>> > rows that belong to company_id 5 but if any of the rows round value
>>> > contains the text "WERT" convert that text into just a "--" for
>>> > presentation purposes, but still select that row. I can't seem to
>> figure
>>> > out how i would transform the text in the select statement? My
>> immediate
>>> > thought was substring / replace but i would need to combine it with an
>> if
>>> > else statement which i've no idea how to make work in a select
>> (sub-query
>>> > maybe?) statement. Is this possible? Perhaps i'm stuck iterating
>> through
>>> > the returned data within the client application before presenting?
>>>>> > Any help, as always, would be greatly appreciated.
>>>>> > Muhd
>>>>>
>>>
>>
>>