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.

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.com...
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.com...
> 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.

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.com...
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.com...
> 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.

First Data Processing Extension Problem

I am trying to write my first data processing extension. The project
is based upon the Event Log example in chapter 13 of Hitchhiker's
Guide to SQL Server 2000 Reporting Services. The assembly has been
copied to the various directories and the various config files have
been modified.
The extension is used in a report project and a shared datasource is
created using the extension. When a report's dataset is created the
Fields window in the IDE is not populated. When the command is run in
the data window of the designer a null pointer error occurs. In the
preview window the error indicates the connection is not valid. This
project is written in C# and the supplied sample is written in VB.NET.
The same error occurs using the VB assemblies.
The "connection not valid" exception is generated from command class'
ExecuteReader method. The connection member is found to be null w/in
the method. In the overloaded c'tor where the connection argument is
assigned to the connection member, the argument is not null and the
member is not null after it has been assigned to. I am baffled as to
what/why/how this occuring. Suggestion?
Equally or more important how does one setup a data processing
extension project for debugging?
thank you
A.G.All issues resolved. Using System.Diagnostics.Debugger.Break I was
able to access the CLR debugger and see what was wrong. First problem
was a typo in a string. Then an indexing error was found and then the
extension 'worked' returning just a single record.
A closer examination of the datareader's Read method revealed a number
of issues involving the iteration of the event log. Changing the
method so it read one record at a time made everything work as
expected. In the sample code the entire event log is iterated when the
Read method is invoked.
Fortunately the exercise in frustration was offset by the learning and
understanding experience.
regards
A.G.
On Tue, 13 Dec 2005 18:37:43 -0500, Registered User
<n4jvp@.ix.netcom.com> wrote:
>I am trying to write my first data processing extension. The project
>is based upon the Event Log example in chapter 13 of Hitchhiker's
>Guide to SQL Server 2000 Reporting Services. The assembly has been
>copied to the various directories and the various config files have
>been modified.
>The extension is used in a report project and a shared datasource is
>created using the extension. When a report's dataset is created the
>Fields window in the IDE is not populated. When the command is run in
>the data window of the designer a null pointer error occurs. In the
>preview window the error indicates the connection is not valid. This
>project is written in C# and the supplied sample is written in VB.NET.
>The same error occurs using the VB assemblies.
>The "connection not valid" exception is generated from command class'
>ExecuteReader method. The connection member is found to be null w/in
>the method. In the overloaded c'tor where the connection argument is
>assigned to the connection member, the argument is not null and the
>member is not null after it has been assigned to. I am baffled as to
>what/why/how this occuring. Suggestion?
>Equally or more important how does one setup a data processing
>extension project for debugging?
>thank you
>A.G.