Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

smallmoney to money datatype

Posted on 2009-04-16
17
Medium Priority
?
1,017 Views
Last Modified: 2012-05-06
I have a asp web prgram that was written for sql using a key called TIMEID.  It is a time clock program and every entry recorded has a timeid with a time punch.  It stopped working yesterday and through my troubleshooting I discovered the developer used smallmoney as the datatype for the timeid field.  It reached its maximum number yesterday (214748.00) and the program gives me this message  --Microsoft OLE DB Provider for ODBC Drivers error '80040e57'
[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type smallmoney.
/stimegreeting.asp, line 96

Can I simply change the column TIMEID to a money type?  How will that affect my program.  Here is the create script for the table? If I could do this, what would the alter statement be?

CREATE TABLE [dbo].[TimeSheet](
	[timeID] [smallmoney] NOT NULL,
	[ActivityDate] [smalldatetime] NULL,
	[CliID] [int] NULL,
	[HID] [int] NULL,
	[ClientName] [varchar](255) NULL,
	[CurrentStatus] [smallint] NULL CONSTRAINT [DF__TimeSheet__Curre__4AB81AF0]  DEFAULT ((0)),
	[STATUS_IN] [smallint] NULL CONSTRAINT [DF__TimeSheet__STATU__4BAC3F29]  DEFAULT ((0)),
	[STATUS_OUT] [smallint] NULL CONSTRAINT [DF__TimeSheet__STATU__4CA06362]  DEFAULT ((0)),
	[STATUS_MEETING] [smallint] NULL CONSTRAINT [DF__TimeSheet__STATU__4D94879B]  DEFAULT ((0)),
	[STATUS_CONF] [smallint] NULL CONSTRAINT [DF__TimeSheet__STATU__4E88ABD4]  DEFAULT ((0)),
	[STATUS_LUNCH] [smallint] NULL CONSTRAINT [DF__TimeSheet__STATU__4F7CD00D]  DEFAULT ((0)),
	[STATUS_OFF] [smallint] NULL CONSTRAINT [DF__TimeSheet__STATU__5070F446]  DEFAULT ((0)),
	[STATUS_VAC] [smallint] NULL CONSTRAINT [DF__TimeSheet__STATU__5165187F]  DEFAULT ((0)),
	[Back_At] [varchar](15) NULL,
	[STATUS_MSG] [varchar](80) NULL,
	[OooReason] [smallint] NULL CONSTRAINT [DF_TimeSheet_OooReason]  DEFAULT ((0)),
	[tssAddDate] [smalldatetime] NULL,
	[tssCreateUser] [int] NULL,
 CONSTRAINT [PK__TimeSheet__49C3F6B7] PRIMARY KEY CLUSTERED 
(
	[timeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Open in new window

0
Comment
Question by:red_75116
  • 8
  • 5
  • 4
17 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 24157112
You should be able to change a smallmoney datatype to money without any issues.  I'm curious as to why a money datatype was used for timeid.  I would think that you would use an integer.  

Greg


0
 

Author Comment

by:red_75116
ID: 24157141
I don't know why it was used.  I guess I have to drop the contraint first and then change it and then add it back.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24157174
if you can drop it, than its great, I don't recommed money datatype for timeid.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 21

Accepted Solution

by:
JestersGrind earned 2000 total points
ID: 24157199
I forgot to add the ALTER TABLE statements.

Greg



ALTER TABLE TimeSheet DROP CONSTRAINT [PK__TimeSheet__49C3F6B7]
 
ALTER TABLE TimeSheet ALTER COLUMN TimeID MONEY NOT NULL
 
ALTER TABLE TimeSheet ADD CONSTRAINT [PK__TimeSheet__49C3F6B7] PRIMARY KEY CLUSTERED (TimeID ASC)

Open in new window

0
 

Author Comment

by:red_75116
ID: 24157366
It appears all the coding in the program is written for smallmoney conversions. etc/.

Can I simply renumber the entries to give myself more time to solve this issues.  This program has been running for about 3 years and just now reached the maximum number of characters/numebrs for small money?
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 24158408
I think that it would be much more involved to try to renumber the column.  How many other places is timeid used?  All of those would have to be taken into account.  Care would have to be taken to make sure none of the relationships were lost in the process.  

If you just change smallmoney to money in all of the tables involved, you're done.

Greg


0
 

Author Comment

by:red_75116
ID: 24158904
Greg,

I changed the smallmoney to money and discovered that it was referenced in about 15 stored procedures not the asp code.  I changed thosed and got it working thanks!.

However, should I eventually change that data type to an int?  Why would it be written with a smallmoney type anyway?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24159082
if possible than do convert your datatype to any numeric datatyp like int or long. I guess there is not meaning to keep smallmoney
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 24159112
I always use INT for my ID fields.  I can't think of a reason to use money or smallmoney.  Obviously, I reserve those datatypes for money!  

The conversion to INT might not be as easy as the one you just did.  You need to make sure that there aren't any non-integer values in those fields.  If there are, you would have to convert them to a whole number, that isn't already being used.  

Greg


0
 

Author Comment

by:red_75116
ID: 24159905
The program is mostly working but there is a page that allows you to manually add entries to the program and that doesnt work right.  If you modify anyone in that window, they you can't see them anymore.  So something still isn't quite right.  Is there any difference between small money and money?  It seems this page isn't displaying anyone that was changed.  However, other pages seem to be working and adding new records ok.

What would the script be to convert the timeid column from money to int?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24159937
ALTER TABLE TimeSheet DROP CONSTRAINT [PK__TimeSheet__49C3F6B7]
 
ALTER TABLE TimeSheet ALTER COLUMN TimeID INT NOT NULL
 
ALTER TABLE TimeSheet ADD CONSTRAINT [PK__TimeSheet__49C3F6B7] PRIMARY KEY CLUSTERED (TimeID ASC)
0
 

Author Comment

by:red_75116
ID: 24159956
the timeid column has entries like

1000.00
1001.00
1002.00

will the alter drop those zeros?  I don't think they are needed?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24159972
yes, if you will make it INT, it will be truncated.
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 24160095
Here are the differences between them.

Data   type            Minimum   value                         Maximum   value                     Storage   size  
smallmoney      -214,748.3648                           214,748,3647                           4 bytes  
money                -922,337,203,685,477.5808   922,337,203.685,477.5807   8 bytes

As far as how they work, there shouldn't be any difference.  One just holds a larger value than the other.

Yes, the zeros after the decimal point will be dropped.  If any of the values are not zero, it will error when you try to convert it to INT.

Greg


0
 

Author Comment

by:red_75116
ID: 24166626
Greg,

Almost there.  The changes fixed all the pages excecpt the manual change page which you can make adjusting entries.  When you post the changes by clicking the apply button, you get a blank page with no errors.  When I view source I get  <p>Microsoft OLE DB Provider for ODBC Drivers</font> <font face="Arial" size=2>error '80040e57'</font>
<p>
<font face="Arial" size=2>[Microsoft][ODBC SQL Server Driver][SQL Server]There is insufficient result space to convert a money value to smallmoney.</font>
<p>
<font face="Arial" size=2>/timegridedit.asp</font><font face="Arial" size=2>, line 106</font>


Here is the offending code.  The last line is the execute statement which is on line 106
<p>Microsoft OLE DB Provider for ODBC Drivers</font> <font face="Arial" size=2>error '80040e57'</font>
<p>
<font face="Arial" size=2>[Microsoft][ODBC SQL Server Driver][SQL Server]There is insufficient result space to convert a money value to smallmoney.</font>
<p>
<font face="Arial" size=2>/timegridedit.asp</font><font face="Arial" size=2>, line 106</font> 

Open in new window

0
 

Author Comment

by:red_75116
ID: 24166701
Finally, that last post helped me fix the problem.  I had changed all the asp pages and procedures to use money instead of small money, but there was an obscure log table which was recording the id numbers and it was still using small money.

0
 

Author Closing Comment

by:red_75116
ID: 31570938
Thanks for all your help!  If I had any documentation on this program, it might have gone easier.  You were really a big help.  Thanks again
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question