Float to Decimal Conversion - Transaction Log Fills Up

Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.
If the tables are small enough, do only one table then backup the
transaction log. If the tables are still too large, you may have to create
another table with the intended datatypes, insert data in stages, backing up
the log after each stage. Then, drop the original table and rename the new
table.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.c om...
Hi,
I am trying to change multiple columns on multiple tables to decimal
28,8 from float. Everytime I attempt to do this, the transaction log
fills up and the process stops. I have attempted this via T-SQL all to
no avail.
What is the correct way of doing this?
SQL Server 7 is the version being used.
Thanks,
Tony.
|||To add on to Tom's response, you can avoid filling the log if you have the
'select into' database option on (SIMPLE/BULK_LOGGED recovery model in SQL
2000) by creating a new table with a minimally-logged SELECT ... INTO. You
may need to add ISNULL to coerce NOT NULL for converted columns that are NOT
NULL in the source table.
CREATE TABLE MyTable
(
Col1 float not null,
Col2 float null
)
GO
SELECT
ISNULL(CAST(Col1 AS decimal(28, 8)), 0) AS Col1,
CAST(Col2 AS decimal(28, 8)) AS Col2
INTO MyTable_New
FROM MyTable
GO
DROP TABLE MyTable
EXEC sp_rename 'MyTable_New', 'MyTable'
--recreate constraints and indexes
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"T Kennedy" <tony.kennedy@.intl.pepsico.com> wrote in message
news:ebc9c5a7.0502010311.54fd362d@.posting.google.c om...
> Hi,
> I am trying to change multiple columns on multiple tables to decimal
> 28,8 from float. Everytime I attempt to do this, the transaction log
> fills up and the process stops. I have attempted this via T-SQL all to
> no avail.
> What is the correct way of doing this?
> SQL Server 7 is the version being used.
> Thanks,
> Tony.