Flummoxed by Connection Problem

Everything was working fine until suddenly this problem appeared from
nowhere:
Boot up the pc and go into SQL Server Management Studio. The database is
present and the data can be accessed.
Go into VB2005 Express and run the project. Everything works fine.
Open Database Explorer and connect to the database and the following
happens:
1. The project no longer works and this error appears at cnn.open()
'Cannot open user default database. Login failed. Login failed for user'
2. The database is no longer available in Management Studio. It appears
but no longer has the little + sign next to it and the data is no longer
accessible. If I try and view the database properties I get this error:
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.Express.ConnectionInfo)
Database 'T:\VS_DATA\NORTHWND.MDF' cannot be opened due to inaccessible
files or insufficient memory or disk space. See the SQL Server errorlog
for details. (Microsoft SQL Server, Error: 945)
The only way I can get out of this situation is to reboot my pc. I am ok
as long as I don't try and connect to the database via Database Explorer.
I have tried disconnecting from and even deleting the database in Database
Explorer to no avail. HELP!
Des
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/I have narrowed down the sequence:
If I close the connection to the database in VB2005 Database Explorer
(very important) and then restart the server my project works again. But
as sonn as I connect vian Database Explorer it breaks again.
On Tue, 17 Jul 2007 12:32:05 +0100, DesCF <descf@.aol.com> wrote:

> Everything was working fine until suddenly this problem appeared from
> nowhere:
> Boot up the pc and go into SQL Server Management Studio. The database
> is present and the data can be accessed.
> Go into VB2005 Express and run the project. Everything works fine.
> Open Database Explorer and connect to the database and the following
> happens:
> 1. The project no longer works and this error appears at cnn.open()
> 'Cannot open user default database. Login failed. Login failed for user'
> 2. The database is no longer available in Management Studio. It appears
> but no longer has the little + sign next to it and the data is no longer
> accessible. If I try and view the database properties I get this error:
> Cannot show requested dialog.
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.Express.ConnectionInfo)
> Database 'T:\VS_DATA\NORTHWND.MDF' cannot be opened due to inaccessible
> files or insufficient memory or disk space. See the SQL Server errorlog
> for details. (Microsoft SQL Server, Error: 945)
>
> The only way I can get out of this situation is to reboot my pc. I am
> ok as long as I don't try and connect to the database via Database
> Explorer. I have tried disconnecting from and even deleting the
> database in Database Explorer to no avail. HELP!
>
>
> Des
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/

Fluctuate in DB Performance Statistics

Dear All,
I have noticed that the DB performance statistics is not
stable as it will rise at certain time dramatically.
I have checked that there isn't any job at that time.
E.g. Server\memory/pages , Current disk queue length,
processor time
Can any one suggest on any cause this?
Thanks.most database applications today consists of a mix of
simple transactions and complex queries that process a lot
of data. this includes reports or other sophisticated
features.
if you have for example 100 concurrent users running the
simple transactions, you might see a stable load on the
server,
however, anytime someone hits one of the complex queries,
you might see cpu spike.
this is why many people recommend separating OLTP and DSS
applications. there is no such thing as a machine powerful
enough to handle both functions on one server.
the OLTP server depends on fast response times, meaning it
should operate at low cpu loading.
the DSS app is supposed to run with cpu pegged, if not,
then you should be running more DSS queries to it, you
bought the hardware, you may as well use it.
>--Original Message--
>Dear All,
>I have noticed that the DB performance statistics is not
>stable as it will rise at certain time dramatically.
>I have checked that there isn't any job at that time.
>E.g. Server\memory/pages , Current disk queue length,
>processor time
>Can any one suggest on any cause this?
>Thanks.
>.
>|||Run profiler at that time to see if it is any TSQL commands causing this. Also, it can be the
checkpoint process.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jackty" <anonymous@.discussions.microsoft.com> wrote in message
news:022d01c3ad86$140683a0$a601280a@.phx.gbl...
> Dear All,
> I have noticed that the DB performance statistics is not
> stable as it will rise at certain time dramatically.
> I have checked that there isn't any job at that time.
> E.g. Server\memory/pages , Current disk queue length,
> processor time
> Can any one suggest on any cause this?
> Thanks.

Floyd's and Warshall's algorithms on relational DB schema

Hello,
I'm searching for an example of Floyd's and Warshall's algorithms in T-SQL
to find all possible paths (based on the PK - FK tables relations) in a
relational database schema (Graph).
Anyone some usefull tips?
Thanx,
Peter"PeterM" <PeterM@.discussions.microsoft.com> wrote in message
news:1914E310-C649-4A24-8B95-32DD63B625F4@.microsoft.com...
> Hello,
> I'm searching for an example of Floyd's and Warshall's algorithms in T-SQL
> to find all possible paths (based on the PK - FK tables relations) in a
> relational database schema (Graph).
> Anyone some usefull tips?
> Thanx,
> Peter
See http://tinyurl.com/49gft.
There's a recursive solution first, that you can't use with SQL Server 2000
but you can with SQL Server 2005, which is followed by an iterative solution
.
The solutions were implemented for DB2 but if you add an @. to the front of
variable names and change END WHILE to END it should be legal T-SQL.
JAG|||Peter,
Here is a link to a naive transitive closure algorithm (keep taking
powers of the adjacency matrix until you get nothing new). It might
at least help you implement Warshall's or other graph algorithms.
http://groups.google.co.uk/groups?q=kass+transclose
Steve Kass
Drew University
PeterM wrote:

>Hello,
>I'm searching for an example of Floyd's and Warshall's algorithms in T-SQL
>to find all possible paths (based on the PK - FK tables relations) in a
>relational database schema (Graph).
>Anyone some usefull tips?
>Thanx,
>Peter
>

flow of events while sending message from one serivce two another

Hello,

I want to know the exact flow of events when I use this statement:

BEGIN DIALOG CONVERSATION @.dialog_handle
FROM SERVICE [SERVICE1]
TO SERVICE 'SERVICE2'
ON CONTRACT [MainContract]

Assuming that I have defined SERVICE1 for Queue1 in the initiator ,

and I have defined SERVICE2 for queue2 on the target.

Is this the flow:

1.Message first goes to the queue1
or
it directly goes to the SERVICE2 on target end point which in turn puts this message in Queue2 on the target?

2. target queue then activates the stored procedure which is connected to queue2 (let's say the procedure name is 'Processqueue2')

3.I noticed that even when none of the item comes in the queue, still when u use "ALTER QUEUE queue2 WITH STATUS = ON", that time also the 'processqueue2' is called.

4.'processqueue2' then fetch message from queue2 and process this. optionally it can send the acknowledgement message to the initiator or just send the end dialog message.

5.if i dont want 'processqueue2' to send the acknowledgement then
can I can directly send the End dialog from the inititor it self i.e. the end dialog just after sending the message.

BEGIN DIALOG CONVERSATION @.dialog_handle
FROM SERVICE [SERVICE1]
TO SERVICE 'SERVICE2'
ON CONTRACT [MainContract]

SEND ON CONVERSATION @.dialog_handle
MESSAGE TYPE SendMessageType ('hello from intiator')

END CONVERSATION @.dialog

In this case why do i need queue1 at all?

my assumption here is that my communication is one way and i don't need the ACK from the target.

Thanks,

BEGIN DIALOG alone does not actualy send any message. It just create the initiator endpoint in sys.conversation_endpoints.

When you SEND a message, the message goes at first into sys.transmission_queue. After the SEND is commited, the message is picked up from sys.transmission_queue and delivered to he machines where SERVICE2 is hosted and is enqueued into Queue2. After the enqueue into Queue2 is commited, an ACK is automatically sent back to the host of SERVICE1 and this allows the message to be deleted from sys.transmission_queue.
In the case when SERVICE1 and SERVICE2 are within the same SQL Server instance, we might try to optimize the SEND by directly enqueueing the message into Queue2 (skip the intermediate step of sys.transmission_queue). If this optimization attempt fails for whatever reason (e.g. Queue2 is diasbled), then the normal path of sys.transmission_queue is used even within the same SQL instance (in fcat, even within the same database).

You application cannot send ACK replies, it can only send real message replies. These are ordinary messages sent from target to initiator, and they would follow the exact sequence as above.

Procedure activation (Processqueue2) happens whenever there are available (i.e. unlocked) messages in the queue. It is not a trigger, the procedure does not get activated once for each message. The algorithm that determines when to activate a new instance of the procedure (up to the max of MAX_QUEUE_READERS setting) monitors the activity of the procedure (RECEIVE statements) vs. the incomming rate of messages and determines when the procedure cannot keep up and launches a new instance of it. When you enable a queue, if there are messages in the queue, it will activate the procedure. Same goes for server start-up, database going online etc (if there are messages in the queue, it will activate the procedure).
It is not guaranteed that the activated procedure will actually find messages in the queue. The code of the procedure should always be prepared with being activated but finding the queue empty (altough we do try hard not to activate in such situations).

A one way message flow that does BEGIN DIALOG/SEND/END is at risk of running into problems if the target service suddenly starts erroring dialogs (e.g. permissions change, or service contract changes etc). Because the initiator has already ended the conversation, when the error comes back from the target it will be droped. The initiator has no way of evem knowing the error occured. A much better message exchange patttern is to BEGIN DIALOG/SEND from the initiator, RECEIVE/END from the target, then RECEIVE/END from the initiator (i.e. the target ENDs first). The initiator does not have to sit there waiting for the target to reply, the EndDialog message sent tby the target can be processed by a procedure attached to queue1. This issue is also discussed in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=273523&SiteID=1.

HTH,
~ Remus

sql

flow layout in rs2005

I have a report that needs to show postal addresses. The address is broken down into several fields. The problem I have is some of the address parts are optional. If they are empty, I'm left with nasty gaps in the address. I'd really like next label to reclaim the space of any empty labels.

a quick example

A full address would look like this..

customer name
address line 1
address line 2
town
county
post code

if address line 2 isnt given, I get:

customer name
address line 1

town
county
post code

but I want:

customer name
address line 1
town
county
post code

Can anyone help?

Many thanks,

Paul

Anyone know? Even if the answer is "sorry, can't do that". Could really do with an answer, I think I've downloaded the entire internet over the last day trying to figure it out!

Cheers|||

Hey Paul,

Can you try this:

=Fields!Customer_Name.Value
& IIf(Len(Fields!Address_Line_1.Value) > 0, chr(10) & Fields!Address_Line_1.Value, "")
& IIf(Len(Fields!Address_Line_2.Value) > 0, chr(10) & Fields!Address_Line_2.Value, "")
& IIf(Len(Fields!Town.Value) > 0, chr(10) & Fields!Town.Value, "")
& IIf(Len(Fields!County.Value) > 0, chr(10) & Fields!County.Value, "")
& IIf(Len(Fields!Postal_Code.Value) > 0, chr(10) & Fields!Postal_Code.Value, "")

This assumes that the Customer_Name field is required, but none of the others are. If the other fields have any length, it will add the line feed, otherwise it will not. It will put the whole thing in one textbox, I'm not sure if that was a requirement.

Hope this helps.

Jarret

|||

Ahh, ok. That works!!! Completely a different answer to how I expected, I thought it would be some layout thing somewhere.

Anyway - exactly the result I was after. Thanks for taking the time to help.

Paul

flow functions ?

Hi

Does anyone know where can I find a list of flow functions valid in MDX? The list in

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/e363722a-3e5b-40a9-a0b5-399dd2d93f6d.htm ist not complete. For example: the case when else end function is not there. I really need a switch.

Thanks!

Have you looked at this article. http://msdn2.microsoft.com/en-us/library/ms144841.aspx

It's a bit short, but should give you some general idea.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Flow Control in stored procedures

A newbie question..

If within a stored procedure there are a list of Stored Procedure calls.

Create Procedure spTest AS

EXEC spMyActionQuery1
EXEC spMyActionQuery2
EXEC spMyActionQuery3
EXEC spMyActionQuery4

GO

Assuming each sub stored procedure executes an action query, does SQL Server ALWAYS wait for the current procedure to finish execution before proceding with the next stored procedure? I believe the answer is yes.The answer IS yes :D

Flow Control in SSIS

I am having a hard time with what appears to be something simple. I want to import an excel spreadsheet into a table on a daily basis from a command line. I created a package from the Import Wizzard in the SQL Management Studio and saved it. Since I want a clean table each day, my process needs to be create a temp table, import from the Excel file into the temp table. If that is successful, delete the original table and rename the temp table the original name. The point of this process is to provide for a fail-safe if there is some unforseen problem downloading the data on a particular day.

When I run the package, the first thing it does is delete the original table. I know this because the process shows the time that it finished is before anything else has started or finished. The time shown for the completion of the data flow task is about 2 minutes after that time.

This is maddening!!! The one thing I do not want to happen I can not seem to prevent. I have my control flow set on success. Why does it do this?

Are you using precedence constraints? You should have a flow like:

Create temp table(Execute SQL task) -- load temp table (Dataflow) --> delete orig. table & rename temp (Execute SQL Task)

The precedence constraint should be set upon success of the previous task

|||

Thanks for the reply. I was using contraints and what you descibe is how I put it in my original message. However, after a good night's sleep I see that in my package I the drop table task comes after the table is renamed, rather than before. It appears SSIS was trying its best to complete all those items the best it could.

After putting all in correct order, the package ran as it should.

Flow Control in SSIS

I am having a hard time with what appears to be something simple. I want to import an excel spreadsheet into a table on a daily basis from a command line. I created a package from the Import Wizzard in the SQL Management Studio and saved it. Since I want a clean table each day, my process needs to be create a temp table, import from the Excel file into the temp table. If that is successful, delete the original table and rename the temp table the original name. The point of this process is to provide for a fail-safe if there is some unforseen problem downloading the data on a particular day.

When I run the package, the first thing it does is delete the original table. I know this because the process shows the time that it finished is before anything else has started or finished. The time shown for the completion of the data flow task is about 2 minutes after that time.

This is maddening!!! The one thing I do not want to happen I can not seem to prevent. I have my control flow set on success. Why does it do this?

Are you using precedence constraints? You should have a flow like:

Create temp table(Execute SQL task) -- load temp table (Dataflow) --> delete orig. table & rename temp (Execute SQL Task)

The precedence constraint should be set upon success of the previous task

|||

Thanks for the reply. I was using contraints and what you descibe is how I put it in my original message. However, after a good night's sleep I see that in my package I the drop table task comes after the table is renamed, rather than before. It appears SSIS was trying its best to complete all those items the best it could.

After putting all in correct order, the package ran as it should.

sql

floor function

I am trying to pull the number preceeding the decimal, but I want my output in a fixed lengh. Here is what I tried thinking it might work, however it did not.

sf_retail = right('000' + floor(cast(labsf.last_retail_price as varchar)),3),

the number I am running this against is '0000001.45' I would like my output to read '001'.....I am getting only '1'

Any suggestions?We just did something like that...

Check out...

http://www.dbforums.com/t987264.html|||Thanks, that was helpful. I ended up using:

sf_retail = right('000' + convert(varchar(3), floor(labsf.last_retail_price)),3),

Flood of SQL services

Hi,

I've just setup SQL Express 2005 SP2 on my fresh SBS 2003 R2 system. I never had anything to do with and have no idea about SQL, the only reason for updating from MSDE was the annoying and ugly event id 1000 relating to sharepoint services in my application log. There seems to be no working solution for that yet.

The point after upgrading to 2005 SP2 now is, that there is a total of seven services related to SQL running on my machine. They are named

MSSQL$SBSMONITORING

MSSQL$SHAREPOINT

SQL Server (SQLEXPRESS)

SQL Server VSS Writer

SQL Server-Browser

SQLAgent$SBSMONITORING

SQLAgent$Sharepoint

Especially the duplicate services regarding SBS-Monitoring and Sharepoint Services are eye-catchers to me. I thought updating to Express 2005 would trail existing databases - or at least asks you whether to do. And in control panel / software there are still two entries relating to MSDE as well.

Is that all alright? Could an SQL-MVP like me, who has no idea about SQL and hates software cadavers on his system, remove "old" MSDE without crashing the whole system?

Thanks in advance for a hint.

Regards,

Tim

By the way: at least after first reboot of the server there is everything "blue" in application log! :-))

hi,

you have to "uncheck" the "hide advanced configuration options" in the wizard as, by default, the setup package installs a named instance named "SQLExpress" as you can see from you "situation" as well..

if you uncheck that option, you can then specify the instance name and, in your case, you will select the already installed instance.. this will upgrade that instance to SQLExpress edition and not install a new one.. so, in the "Instance name" installation step, press the "Installed instances" button and all local installed instance will be listed... select the MSDE instance you like to upgrade and proceed..

in your scenario, you can eventually maintain the .\SQLExpress installed named instance and just uninstall the MSDE instance..

regards

Flood of SQL services

Hi,
I've just setup SQL Express 2005 SP2 on my fresh SBS 2003 R2 system. I never
had anything to do with and have no idea about SQL, the only reason for
updating from MSDE was the annoying and ugly event id 1000 relating to
sharepoint services in my application log. There seems to be no working
solution for that yet.
The point after upgrading to 2005 SP2 now is, that there is a total of seven
services related to SQL running on my machine. They are named
MSSQL$SBSMONITORING
MSSQL$SHAREPOINT
SQL Server (SQLEXPRESS)
SQL Server VSS Writer
SQL Server-Browser
SQLAgent$SBSMONITORING
SQLAgent$Sharepoint
Especially the duplicate services regarding SBS-Monitoring and Sharepoint
Services are eye-catchers to me. I thought updating to Express 2005 would
trail existing databases - or at least asks you whether to do. And in control
panel / software there are still two entries relating to MSDE as well.
Is that all alright? Could an SQL-MVP like me, who has no idea about SQL and
hates software cadavers on his system, remove "old" MSDE without crashing the
whole system?
Thanks in advance for a hint.
Regards,
Tim
By the way: at least after first reboot of the server there is everything
"blue" in application log! :-))
SQL Agent is essentially a part of SQL Server. It manages scheduled jobs,
alerts, etc. It cannot be removed from the corresponding SQL Server
instance. According to what I see, you have essentially 3 instances of SQL
Server running on your machine, one of which is SQL Express.
BTW, when did you become an MVP? I don't see you listed.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Tim Meltzer" <TimMeltzer@.discussions.microsoft.com> wrote in message
news:C2C0D24F-1B83-43D3-B3FA-95C951907BAC@.microsoft.com...
Hi,
I've just setup SQL Express 2005 SP2 on my fresh SBS 2003 R2 system. I never
had anything to do with and have no idea about SQL, the only reason for
updating from MSDE was the annoying and ugly event id 1000 relating to
sharepoint services in my application log. There seems to be no working
solution for that yet.
The point after upgrading to 2005 SP2 now is, that there is a total of seven
services related to SQL running on my machine. They are named
MSSQL$SBSMONITORING
MSSQL$SHAREPOINT
SQL Server (SQLEXPRESS)
SQL Server VSS Writer
SQL Server-Browser
SQLAgent$SBSMONITORING
SQLAgent$Sharepoint
Especially the duplicate services regarding SBS-Monitoring and Sharepoint
Services are eye-catchers to me. I thought updating to Express 2005 would
trail existing databases - or at least asks you whether to do. And in
control
panel / software there are still two entries relating to MSDE as well.
Is that all alright? Could an SQL-MVP like me, who has no idea about SQL and
hates software cadavers on his system, remove "old" MSDE without crashing
the
whole system?
Thanks in advance for a hint.
Regards,
Tim
By the way: at least after first reboot of the server there is everything
"blue" in application log! :-))
sql

Flood of SQL services

Hi,
I've just setup SQL Express 2005 SP2 on my fresh SBS 2003 R2 system. I never
had anything to do with and have no idea about SQL, the only reason for
updating from MSDE was the annoying and ugly event id 1000 relating to
sharepoint services in my application log. There seems to be no working
solution for that yet.
The point after upgrading to 2005 SP2 now is, that there is a total of seven
services related to SQL running on my machine. They are named
MSSQL$SBSMONITORING
MSSQL$SHAREPOINT
SQL Server (SQLEXPRESS)
SQL Server VSS Writer
SQL Server-Browser
SQLAgent$SBSMONITORING
SQLAgent$Sharepoint
Especially the duplicate services regarding SBS-Monitoring and Sharepoint
Services are eye-catchers to me. I thought updating to Express 2005 would
trail existing databases - or at least asks you whether to do. And in contro
l
panel / software there are still two entries relating to MSDE as well.
Is that all alright? Could an SQL-MVP like me, who has no idea about SQL and
hates software cadavers on his system, remove "old" MSDE without crashing th
e
whole system?
Thanks in advance for a hint.
Regards,
Tim
By the way: at least after first reboot of the server there is everything
"blue" in application log! :-))SQL Agent is essentially a part of SQL Server. It manages scheduled jobs,
alerts, etc. It cannot be removed from the corresponding SQL Server
instance. According to what I see, you have essentially 3 instances of SQL
Server running on your machine, one of which is SQL Express.
BTW, when did you become an MVP? I don't see you listed.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Tim Meltzer" <TimMeltzer@.discussions.microsoft.com> wrote in message
news:C2C0D24F-1B83-43D3-B3FA-95C951907BAC@.microsoft.com...
Hi,
I've just setup SQL Express 2005 SP2 on my fresh SBS 2003 R2 system. I never
had anything to do with and have no idea about SQL, the only reason for
updating from MSDE was the annoying and ugly event id 1000 relating to
sharepoint services in my application log. There seems to be no working
solution for that yet.
The point after upgrading to 2005 SP2 now is, that there is a total of seven
services related to SQL running on my machine. They are named
MSSQL$SBSMONITORING
MSSQL$SHAREPOINT
SQL Server (SQLEXPRESS)
SQL Server VSS Writer
SQL Server-Browser
SQLAgent$SBSMONITORING
SQLAgent$Sharepoint
Especially the duplicate services regarding SBS-Monitoring and Sharepoint
Services are eye-catchers to me. I thought updating to Express 2005 would
trail existing databases - or at least asks you whether to do. And in
control
panel / software there are still two entries relating to MSDE as well.
Is that all alright? Could an SQL-MVP like me, who has no idea about SQL and
hates software cadavers on his system, remove "old" MSDE without crashing
the
whole system?
Thanks in advance for a hint.
Regards,
Tim
By the way: at least after first reboot of the server there is everything
"blue" in application log! :-))

Flood of SQL services

Hi,
I've just setup SQL Express 2005 SP2 on my fresh SBS 2003 R2 system. I never
had anything to do with and have no idea about SQL, the only reason for
updating from MSDE was the annoying and ugly event id 1000 relating to
sharepoint services in my application log. There seems to be no working
solution for that yet.
The point after upgrading to 2005 SP2 now is, that there is a total of seven
services related to SQL running on my machine. They are named
MSSQL$SBSMONITORING
MSSQL$SHAREPOINT
SQL Server (SQLEXPRESS)
SQL Server VSS Writer
SQL Server-Browser
SQLAgent$SBSMONITORING
SQLAgent$Sharepoint
Especially the duplicate services regarding SBS-Monitoring and Sharepoint
Services are eye-catchers to me. I thought updating to Express 2005 would
trail existing databases - or at least asks you whether to do. And in control
panel / software there are still two entries relating to MSDE as well.
Is that all alright? Could an SQL-MVP like me, who has no idea about SQL and
hates software cadavers on his system, remove "old" MSDE without crashing the
whole system?
Thanks in advance for a hint.
Regards,
Tim
By the way: at least after first reboot of the server there is everything
"blue" in application log! :-))SQL Agent is essentially a part of SQL Server. It manages scheduled jobs,
alerts, etc. It cannot be removed from the corresponding SQL Server
instance. According to what I see, you have essentially 3 instances of SQL
Server running on your machine, one of which is SQL Express.
BTW, when did you become an MVP? I don't see you listed.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Tim Meltzer" <TimMeltzer@.discussions.microsoft.com> wrote in message
news:C2C0D24F-1B83-43D3-B3FA-95C951907BAC@.microsoft.com...
Hi,
I've just setup SQL Express 2005 SP2 on my fresh SBS 2003 R2 system. I never
had anything to do with and have no idea about SQL, the only reason for
updating from MSDE was the annoying and ugly event id 1000 relating to
sharepoint services in my application log. There seems to be no working
solution for that yet.
The point after upgrading to 2005 SP2 now is, that there is a total of seven
services related to SQL running on my machine. They are named
MSSQL$SBSMONITORING
MSSQL$SHAREPOINT
SQL Server (SQLEXPRESS)
SQL Server VSS Writer
SQL Server-Browser
SQLAgent$SBSMONITORING
SQLAgent$Sharepoint
Especially the duplicate services regarding SBS-Monitoring and Sharepoint
Services are eye-catchers to me. I thought updating to Express 2005 would
trail existing databases - or at least asks you whether to do. And in
control
panel / software there are still two entries relating to MSDE as well.
Is that all alright? Could an SQL-MVP like me, who has no idea about SQL and
hates software cadavers on his system, remove "old" MSDE without crashing
the
whole system?
Thanks in advance for a hint.
Regards,
Tim
By the way: at least after first reboot of the server there is everything
"blue" in application log! :-))

Floats with no scientific notation

Hello,
I've been searching the groups for a way to accomplish this, but I've
come up short. What I ultimately need is a way to count the number of
decimal places in a floating point number (ie 37.88955 ==> 5, 0.02 ==>
2, etc).
What I'm currently working on is how to convert a float to decimal,
with no scientific notation.
cast(cast(f.formula_percent as decimal(20,10)) as varchar(20))
This almost does the trick, except that I also need to trim off the
trailing 0's (ie 0.02000000 -> 0.02 ==> 2).
My data has anywhere from 0 to 7 decimals, so I need a way to calculate
this dynamically.
Any Ideas?
Thanks in advance.
Brian AckermannThere have been many posts on the topic of floating point values and how
they are represented in a binary format. Simply put, what you are asking is
impossible since floating point datatypes are inherently inaccurate. There
was a post today with the topic "float return" that begins to delve into the
subject.
The short answer is to change the way you store the data if you need to
"see" the exact value that was entered. Otherwise, you will need to use
some algorithm based on an understanding of the floating point storage issue
and an analysis of the domain represented by the table/column. Something
along the lines of [convert from float to 7 place decimal number and
evaluate the result] - any logic that you do develop will be, by design, of
limited accuracy. Below is a query that you can use for testing (thanks to
Steve Kass via the thread mentioned earlier).
select cast (16.9 as float), cast (16.89999 as float)
Incidentally, presentation of data is best left to a client application.
"Brian Ackermann" <brian.ackermann@.gmail.com> wrote in message
news:1113326461.535229.251350@.f14g2000cwb.googlegroups.com...
> Hello,
> I've been searching the groups for a way to accomplish this, but I've
> come up short. What I ultimately need is a way to count the number of
> decimal places in a floating point number (ie 37.88955 ==> 5, 0.02 ==>
> 2, etc).
> What I'm currently working on is how to convert a float to decimal,
> with no scientific notation.
> cast(cast(f.formula_percent as decimal(20,10)) as varchar(20))
> This almost does the trick, except that I also need to trim off the
> trailing 0's (ie 0.02000000 -> 0.02 ==> 2).
> My data has anywhere from 0 to 7 decimals, so I need a way to calculate
> this dynamically.
> Any Ideas?
> Thanks in advance.
> Brian Ackermann
>|||Scott,
I agree with what you say. However, in my case, there are other
circumstances. For one, this is not a data presentation issue. I'm
actually adding functionality to an existing db, adding a seperate
column for the lab to be able to specify per line item precision. This
problem arises when trying to fill in this new field with data from the
table we are trying to make the precision adjustments on.
For the record, by trial and error I assembled the following. It
works, though I'm sure it does not do the job very well. Perhaps
others may find it useful too.
update formula
set Significant_Figures =
CASE
WHEN patindex('0000000000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 14
WHEN patindex('000000000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 13
WHEN patindex('00000000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 12
WHEN patindex('0000000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 11
WHEN patindex('000000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 10
WHEN patindex('00000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 9
WHEN patindex('0000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 8
WHEN patindex('000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 7
WHEN patindex('00000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 6
WHEN patindex('0000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 5
WHEN patindex('000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 4
WHEN patindex('00', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 3
WHEN patindex('0', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 2
WHEN patindex('', cast(cast(f.formula_percent as decimal(20,10))
as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 1
WHEN patindex('%0', cast(cast(f.formula_percent as decimal(20,10))
as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 0
WHEN patindex('%', cast(cast(f.formula_percent as decimal(20,10))
as varchar(20))) > 0
THEN patindex('%', cast(cast(f.formula_percent as decimal(20,10))
as varchar(20)))
ELSE 'foo' --never happens
END
from formula f
Thanks,
Brian|||On 12 Apr 2005 11:46:55 -0700, Brian Ackermann wrote:
(snip)
>For the record, by trial and error I assembled the following. It
>works, though I'm sure it does not do the job very well. Perhaps
>others may find it useful too.
Hi Brian,
If you tested it, then I have no reason to doubt your statement that
this works - but why so complicated? Consider replacing the lengthy CASE
expression with a simple expression:
declare @.a float
set @.a = 37.800954
select len(rtrim(replace(cast(@.a as decimal(20,10)),'0',' ')))
- charindex('.',cast(@.a as decimal(20,10)))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I work strictly on geochemical and/or hydrogeological databases in
Environmental Consulting. I have struggled for years with presenting number
s
with the correct number of significant figures, including trailing 0's(i.e.
1.00 is 1.00, not 1). The best fix that I have come up with was to create
corresponding text columns for my number fields. i.e. Result and Result_tex
t
I use this sp to populate the Result_text column
create proc update_result_text as
update lab_results
set result_text =
CASE
WHEN sig_figs + floor(-log10(abs(result))) < 0 THEN
-- The result will have trailing zeros - round first and then
use str
STR(round(result, cast(sig_figs + floor(-log10(abs(result))) as
int)), 10, 0)
ELSE
-- The result will have no trailing zeros - use str directly
STR(result, 10, cast(sig_figs + floor(-log10(abs(result))) as
int))
END
There is no way to count the number of decimal places in a floating point
number, but you can use the floor(-log10(abs(result))) function to find the
order of magnitude of the number you are looking for.
Archer
"Brian Ackermann" wrote:

> Hello,
> I've been searching the groups for a way to accomplish this, but I've
> come up short. What I ultimately need is a way to count the number of
> decimal places in a floating point number (ie 37.88955 ==> 5, 0.02 ==>
> 2, etc).
> What I'm currently working on is how to convert a float to decimal,
> with no scientific notation.
> cast(cast(f.formula_percent as decimal(20,10)) as varchar(20))
> This almost does the trick, except that I also need to trim off the
> trailing 0's (ie 0.02000000 -> 0.02 ==> 2).
> My data has anywhere from 0 to 7 decimals, so I need a way to calculate
> this dynamically.
> Any Ideas?
> Thanks in advance.
> Brian Ackermann
>|||It sounds like you might want to look at DECIMAL(s,p) and NUMERIC(s,p)
data types instead of FLOAT and REAL. The advantage of a floating
point representation is that can get it on a co-processor chip for lab
work. The advantage of a BCD representation is that there is limited
rounding errors within the range. but nobody makes BCD co-processor
chips that I know about; Burroughs had something like this in one of
their COBOL machines.
And throw in the usual lecture about doign display in the front end,
not the database.

floating point truncation

How can I truncate a floating point number to required number of decimal points
Eg:
100.642364074 to 100.64 and 67.643929847 to 67.645
Thanks.STR|||...And the last number should be rounded to 67.644 not 67.645

Floating point precision

Hi, I have a SP which adds a bunch of the same number together 365
times
The base number is 0.0575342465753425
I am storing this as a float but when I view it in the first iteration
it appears as
0.0575342
The next one is
0.115068
and the 10th one is
0.575342
and finally the 365 one is
21.0575
However if I multiply the original number by 365 I get the following
21.0000000000000125
Which is vastly different from the one I got using a float.
How can I get more precision using MSSQL float? Am I using the wrong
Datatype'
TIA
Mark
================================= 2006MJGOOGLENEWSDon't use FLOAT it is not accurate in terms of precision
What you get if you use DECIMAL datatype intead?
<MarkusJNZ@.gmail.com> wrote in message
news:1159441537.669380.150810@.b28g2000cwb.googlegroups.com...
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================> 2006MJGOOGLENEWS
>|||MarkusJNZ@.gmail.com wrote:
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================> 2006MJGOOGLENEWS
>
From Books Online:
"Floating point data is approximate; not all values in the data type
range can be precisely represented."
Float is not a precise data type, use DECIMAL or one of the other
numeric types instead...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>Hi, I have a SP which adds a bunch of the same number together 365
>times
>The base number is 0.0575342465753425
>I am storing this as a float but when I view it in the first iteration
>it appears as
>0.0575342
>The next one is
>0.115068
>and the 10th one is
>0.575342
>and finally the 365 one is
>21.0575
>However if I multiply the original number by 365 I get the following
>21.0000000000000125
>Which is vastly different from the one I got using a float.
>How can I get more precision using MSSQL float? Am I using the wrong
>Datatype'
Hi Mark,
Nothing wrong with the datatype - the pproblem is in the code. Since the
result is off by 0.0575, which is exactly the number you start with, I'd
double-check the code - you're probably adding the same number 366 times
instead of 365 times.
Here's some code I used (note the CAST near the end to force display of
numbers to the far right of the decimal point):
declare @.flt float, @.res float, @.i int
set @.flt = 0.0575342465753425
set @.res = 0
set @.i = 0
while @.i < 365
begin
set @.i = @.i + 1
set @.res = @.res + @.flt
end
select cast(@.res as decimal(38,30))
select cast(@.flt * 365.0 as decimal(38,30))
Results:
---
21.000000000000046000000000000000
---
21.000000000000014000000000000000
As you see, there is SOME loss of precision, but not quites as much as
you had.
Incidentally, if you change the datatypes of @.flt and @.res in the code
above to decimal(38,10), the results change to
---
21.000000000000012500000000000000
---
21.000000000000012500000000000000
Hugo Kornelis, SQL Server MVP|||Thanks everyone for your help.
Hug, you were right, I was adding it 1 more than I needed to; late
night programming lol
Thanks
Mark
Hugo Kornelis wrote:
> On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
> >Hi, I have a SP which adds a bunch of the same number together 365
> >times
> >
> >The base number is 0.0575342465753425
> >
> >I am storing this as a float but when I view it in the first iteration
> >it appears as
> >
> >0.0575342
> >
> >The next one is
> >
> >0.115068
> >
> >and the 10th one is
> >
> >0.575342
> >
> >and finally the 365 one is
> >
> >21.0575
> >
> >However if I multiply the original number by 365 I get the following
> >21.0000000000000125
> >
> >Which is vastly different from the one I got using a float.
> >
> >How can I get more precision using MSSQL float? Am I using the wrong
> >Datatype'
> Hi Mark,
> Nothing wrong with the datatype - the pproblem is in the code. Since the
> result is off by 0.0575, which is exactly the number you start with, I'd
> double-check the code - you're probably adding the same number 366 times
> instead of 365 times.
> Here's some code I used (note the CAST near the end to force display of
> numbers to the far right of the decimal point):
> declare @.flt float, @.res float, @.i int
> set @.flt = 0.0575342465753425
> set @.res = 0
> set @.i = 0
> while @.i < 365
> begin
> set @.i = @.i + 1
> set @.res = @.res + @.flt
> end
> select cast(@.res as decimal(38,30))
> select cast(@.flt * 365.0 as decimal(38,30))
> Results:
>
> ---
> 21.000000000000046000000000000000
>
> ---
> 21.000000000000014000000000000000
> As you see, there is SOME loss of precision, but not quites as much as
> you had.
> Incidentally, if you change the datatypes of @.flt and @.res in the code
> above to decimal(38,10), the results change to
>
> ---
> 21.000000000000012500000000000000
>
> ---
> 21.000000000000012500000000000000
>
> --
> Hugo Kornelis, SQL Server MVPsql

Floating point precision

Hi, I have a SP which adds a bunch of the same number together 365
times
The base number is 0.0575342465753425
I am storing this as a float but when I view it in the first iteration
it appears as
0.0575342
The next one is
0.115068
and the 10th one is
0.575342
and finally the 365 one is
21.0575
However if I multiply the original number by 365 I get the following
21.0000000000000125
Which is vastly different from the one I got using a float.
How can I get more precision using MSSQL float? Am I using the wrong
Datatype?
TIA
Mark
=================================
2006MJGOOGLENEWS
Don't use FLOAT it is not accurate in terms of precision
What you get if you use DECIMAL datatype intead?
<MarkusJNZ@.gmail.com> wrote in message
news:1159441537.669380.150810@.b28g2000cwb.googlegr oups.com...
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype?
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>
|||MarkusJNZ@.gmail.com wrote:
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype?
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>
From Books Online:
"Floating point data is approximate; not all values in the data type
range can be precisely represented."
Float is not a precise data type, use DECIMAL or one of the other
numeric types instead...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:

>Hi, I have a SP which adds a bunch of the same number together 365
>times
>The base number is 0.0575342465753425
>I am storing this as a float but when I view it in the first iteration
>it appears as
>0.0575342
>The next one is
>0.115068
>and the 10th one is
>0.575342
>and finally the 365 one is
>21.0575
>However if I multiply the original number by 365 I get the following
>21.0000000000000125
>Which is vastly different from the one I got using a float.
>How can I get more precision using MSSQL float? Am I using the wrong
>Datatype?
Hi Mark,
Nothing wrong with the datatype - the pproblem is in the code. Since the
result is off by 0.0575, which is exactly the number you start with, I'd
double-check the code - you're probably adding the same number 366 times
instead of 365 times.
Here's some code I used (note the CAST near the end to force display of
numbers to the far right of the decimal point):
declare @.flt float, @.res float, @.i int
set @.flt = 0.0575342465753425
set @.res = 0
set @.i = 0
while @.i < 365
begin
set @.i = @.i + 1
set @.res = @.res + @.flt
end
select cast(@.res as decimal(38,30))
select cast(@.flt * 365.0 as decimal(38,30))
Results:
21.000000000000046000000000000000
21.000000000000014000000000000000
As you see, there is SOME loss of precision, but not quites as much as
you had.
Incidentally, if you change the datatypes of @.flt and @.res in the code
above to decimal(38,10), the results change to
21.000000000000012500000000000000
21.000000000000012500000000000000
Hugo Kornelis, SQL Server MVP
|||Thanks everyone for your help.
Hug, you were right, I was adding it 1 more than I needed to; late
night programming lol
Thanks
Mark
Hugo Kornelis wrote:
> On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>
> Hi Mark,
> Nothing wrong with the datatype - the pproblem is in the code. Since the
> result is off by 0.0575, which is exactly the number you start with, I'd
> double-check the code - you're probably adding the same number 366 times
> instead of 365 times.
> Here's some code I used (note the CAST near the end to force display of
> numbers to the far right of the decimal point):
> declare @.flt float, @.res float, @.i int
> set @.flt = 0.0575342465753425
> set @.res = 0
> set @.i = 0
> while @.i < 365
> begin
> set @.i = @.i + 1
> set @.res = @.res + @.flt
> end
> select cast(@.res as decimal(38,30))
> select cast(@.flt * 365.0 as decimal(38,30))
> Results:
>
> 21.000000000000046000000000000000
>
> 21.000000000000014000000000000000
> As you see, there is SOME loss of precision, but not quites as much as
> you had.
> Incidentally, if you change the datatypes of @.flt and @.res in the code
> above to decimal(38,10), the results change to
>
> 21.000000000000012500000000000000
>
> 21.000000000000012500000000000000
>
> --
> Hugo Kornelis, SQL Server MVP

Floating point precision

Hi, I have a SP which adds a bunch of the same number together 365
times
The base number is 0.0575342465753425
I am storing this as a float but when I view it in the first iteration
it appears as
0.0575342
The next one is
0.115068
and the 10th one is
0.575342
and finally the 365 one is
21.0575
However if I multiply the original number by 365 I get the following
21.0000000000000125
Which is vastly different from the one I got using a float.
How can I get more precision using MSSQL float? Am I using the wrong
Datatype'
TIA
Mark
=================================
2006MJGOOGLENEWSDon't use FLOAT it is not accurate in terms of precision
What you get if you use DECIMAL datatype intead?
<MarkusJNZ@.gmail.com> wrote in message
news:1159441537.669380.150810@.b28g2000cwb.googlegroups.com...
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>|||MarkusJNZ@.gmail.com wrote:
> Hi, I have a SP which adds a bunch of the same number together 365
> times
> The base number is 0.0575342465753425
> I am storing this as a float but when I view it in the first iteration
> it appears as
> 0.0575342
> The next one is
> 0.115068
> and the 10th one is
> 0.575342
> and finally the 365 one is
> 21.0575
> However if I multiply the original number by 365 I get the following
> 21.0000000000000125
> Which is vastly different from the one I got using a float.
> How can I get more precision using MSSQL float? Am I using the wrong
> Datatype'
> TIA
> Mark
> =================================
> 2006MJGOOGLENEWS
>
From Books Online:
"Floating point data is approximate; not all values in the data type
range can be precisely represented."
Float is not a precise data type, use DECIMAL or one of the other
numeric types instead...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:

>Hi, I have a SP which adds a bunch of the same number together 365
>times
>The base number is 0.0575342465753425
>I am storing this as a float but when I view it in the first iteration
>it appears as
>0.0575342
>The next one is
>0.115068
>and the 10th one is
>0.575342
>and finally the 365 one is
>21.0575
>However if I multiply the original number by 365 I get the following
>21.0000000000000125
>Which is vastly different from the one I got using a float.
>How can I get more precision using MSSQL float? Am I using the wrong
>Datatype'
Hi Mark,
Nothing wrong with the datatype - the pproblem is in the code. Since the
result is off by 0.0575, which is exactly the number you start with, I'd
double-check the code - you're probably adding the same number 366 times
instead of 365 times.
Here's some code I used (note the CAST near the end to force display of
numbers to the far right of the decimal point):
declare @.flt float, @.res float, @.i int
set @.flt = 0.0575342465753425
set @.res = 0
set @.i = 0
while @.i < 365
begin
set @.i = @.i + 1
set @.res = @.res + @.flt
end
select cast(@.res as decimal(38,30))
select cast(@.flt * 365.0 as decimal(38,30))
Results:
---
21.000000000000046000000000000000
---
21.000000000000014000000000000000
As you see, there is SOME loss of precision, but not quites as much as
you had.
Incidentally, if you change the datatypes of @.flt and @.res in the code
above to decimal(38,10), the results change to
---
21.000000000000012500000000000000
---
21.000000000000012500000000000000
Hugo Kornelis, SQL Server MVP|||Thanks everyone for your help.
Hug, you were right, I was adding it 1 more than I needed to; late
night programming lol
Thanks
Mark
Hugo Kornelis wrote:
> On 28 Sep 2006 04:05:37 -0700, MarkusJNZ@.gmail.com wrote:
>
> Hi Mark,
> Nothing wrong with the datatype - the pproblem is in the code. Since the
> result is off by 0.0575, which is exactly the number you start with, I'd
> double-check the code - you're probably adding the same number 366 times
> instead of 365 times.
> Here's some code I used (note the CAST near the end to force display of
> numbers to the far right of the decimal point):
> declare @.flt float, @.res float, @.i int
> set @.flt = 0.0575342465753425
> set @.res = 0
> set @.i = 0
> while @.i < 365
> begin
> set @.i = @.i + 1
> set @.res = @.res + @.flt
> end
> select cast(@.res as decimal(38,30))
> select cast(@.flt * 365.0 as decimal(38,30))
> Results:
>
> ---
> 21.000000000000046000000000000000
>
> ---
> 21.000000000000014000000000000000
> As you see, there is SOME loss of precision, but not quites as much as
> you had.
> Incidentally, if you change the datatypes of @.flt and @.res in the code
> above to decimal(38,10), the results change to
>
> ---
> 21.000000000000012500000000000000
>
> ---
> 21.000000000000012500000000000000
>
> --
> Hugo Kornelis, SQL Server MVP

Floating Point Numbers in BCP File

Hi,
I am BCPing in a tab-delimited text file and I am getting this error
message:
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
The text file records look like below:
200509 67195 M12AB00 67195 SSL TEST RECORD 7217 240.41
200509 67338 DNMXAED 67338 CTA TEST RECORD 50 237.5
Not sure if that floating point number at the end of the file is the cause
of this problem or not.
Any ideas on how to solve the problem?
JDJoe Delphi wrote:
> Hi,
> I am BCPing in a tab-delimited text file and I am getting this
> error message:
> Starting copy...
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value
> for cast specification
> The text file records look like below:
> 200509 67195 M12AB00 67195 SSL TEST RECORD 7217 240.41
> 200509 67338 DNMXAED 67338 CTA TEST RECORD 50 237.5
> Not sure if that floating point number at the end of the file is the
> cause of this problem or not.
> Any ideas on how to solve the problem?
> JD
Have you verified there are actually TAB characters in the file where
they should be? You might try using the DTS Import Wizard to see if that
works. The wizard will show you the parsed data based on your
delimiters, so that might clue you in to the problem.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Joe Delphi (delphi561@.nospam.cox.net) writes:
> I am BCPing in a tab-delimited text file and I am getting this error
> message:
> Starting copy...
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast specification
> The text file records look like below:
> 200509 67195 M12AB00 67195 SSL TEST RECORD 7217 240.41
> 200509 67338 DNMXAED 67338 CTA TEST RECORD 50 237.5
> Not sure if that floating point number at the end of the file is the cause
> of this problem or not.
> Any ideas on how to solve the problem?
Please post the CREATE TABLE statement for the table. It's impossible to
tell without that information what is going on.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Floating point fun

I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
migration. I've read certain floating-point values can't be represented
accurately, however, .1 doesn't look like one of them as the FLOAT(2) field
copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1 round
to .10000000000000001? Converting the column to DECIMAL is probably not an
option.
Thanks
Damien
CREATE TABLE #float_test ( ft_id INT PRIMARY KEY, rate1 FLOAT, rate2
FLOAT(2) )
-- Try and insert the value 4.1 into the float table
INSERT INTO #float_test ( ft_id, rate1 )
SELECT 1, 4.1 UNION
SELECT 2, 4 + .1 UNION
SELECT 3, '4.10' UNION
SELECT 4, 4.11 UNION
SELECT 5, 4.95 UNION
SELECT 6, CONVERT( REAL, 4.1, 0 ) UNION
SELECT 7, .1
GO
UPDATE #float_test
SET rate2 = rate1
SELECT * FROM #float_test
DROP TABLE #float_testHi
Read http://www.aspfaq.com/show.asp?id=2477
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:C4AF11D4-0AD6-4EE3-8453-FFF203829E25@.microsoft.com...
> I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
> migration. I've read certain floating-point values can't be represented
> accurately, however, .1 doesn't look like one of them as the FLOAT(2)
> field
> copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1
> round
> to .10000000000000001? Converting the column to DECIMAL is probably not
> an
> option.
> Thanks
> Damien
> CREATE TABLE #float_test ( ft_id INT PRIMARY KEY, rate1 FLOAT, rate2
> FLOAT(2) )
> -- Try and insert the value 4.1 into the float table
> INSERT INTO #float_test ( ft_id, rate1 )
> SELECT 1, 4.1 UNION
> SELECT 2, 4 + .1 UNION
> SELECT 3, '4.10' UNION
> SELECT 4, 4.11 UNION
> SELECT 5, 4.95 UNION
> SELECT 6, CONVERT( REAL, 4.1, 0 ) UNION
> SELECT 7, .1
> GO
> UPDATE #float_test
> SET rate2 = rate1
> SELECT * FROM #float_test
> DROP TABLE #float_test
>|||On Tue, 23 Aug 2005 01:43:07 -0700, Damien
<Damien@.discussions.microsoft.com> wrote:

>I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
>migration. I've read certain floating-point values can't be represented
>accurately, however, .1 doesn't look like one of them as the FLOAT(2) field
>copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1 round
>to .10000000000000001? Converting the column to DECIMAL is probably not an
>option.
It's not clear to me what you expect to happen. Floating-point values
*are* precisely represented by floating-point values. (cough)
OTOH, certain fractional values are not in the domain of certain
floating-point data types. In those cases, software generally picks
the closest value available. (The result is "error of approximation",
not a rounding error.)
You seem to think that every number that ends in '.1' should have the
same behavior. That's simply not true of floating-point data types.
It might help to think of it this way. Neighboring, distinct values in
exact data types are the same distance apart on a number line. That
is, each value in a SQL INTEGER data type is plus or minus 1 from its
neighbor.
But neighboring, distinct values in a floating-point data type are not
the same distance apart on a number line. The closer you get to zero
(from either direction), the more closely spaced the neighboring
distinct values are.
Mike Sherrill|||> Also, why does 4.1 round to .0999999999999996 but 0.1 round
to .10000000000000001?
because under the hood floats are stored as binaries. So, binary
numbers are represented accurately, up to some accuracy, of course.
Decimals are rounded to binaries. When you convert binaries back to
decimals, expect some mismatch|||On 24 Aug 2005 14:36:10 -0700, ford_desperado@.yahoo.com wrote:
[snip]
>Decimals are rounded to binaries.
Error of approximation, which seems to be what you're stumbling
toward, doesn't mean "fixed point numbers are rounded to binary". See
Knuth, vol 2.

Floating Point Exception in SQL Server 2000

Hi,

I got below error in the SQL Server Production Server and i checked in the microsoft site it needs to install SQL Server service pack 4 to resolve the
problem.

"A floating point exception occurred in the user process. Current transaction is canceled"

I need help that i want to reproduce this below problem in the SQL Server environment and tried several ways but no luck.

Please advise me how to reproduce the problem.

Would be appreciate your help.

Regards
SathishFor what cause you are trying to do that? Check this ...Link (http://www.dbforums.com/showthread.php?t=318196)|||Wants to check after installing service pack 4. so that we can confirm it should not happen in future.
Any clues to reprodue it.

Regards
Sathish|||Wants to check after installing service pack 4. so that we can confirm it should not happen in future.
Any clues to reprodue it.

Regards
Sathish
FYI...
If the following conditions are all true, Microsoft SQL Server may store floating point data with an exponent lower than -308, which may cause floating point underflow exceptions that terminate a clients connection to SQL Server:

• The client application is using stored procedures or server side cursors to perform data modification
and passes the request to the SQL Server server as a remote procedure call (RPC) event.
• The client application is passing parameters for the RPC event to the SQL Server server.
• The column of the table affected by the parameter that is passed is defined as a float datatype.
• If a stored procedure is called, the parameter is defined as a float datatype.

And plz check the previous link that I gave you...You could get you answer there.|||Joydeep,

Thanks for your information. I have tried following ways to reproduce it

1. By using Index Tuning Wizard Execution
2. By passing the expression 0/0 (zero divided by zero) to SQL Server as a floating point value for a stored procedure parameter
3. By trying query with aggregate function
4. By running a Complex Query
5. By Query optimization

But i couldnt able to reproduce it. Do you have any stored procedure or SQL Query to stimulate this problem.

Regards
Sathishsql

floating point exception - unexplainable - even after SP4 still ge

I am in the process of translating Access 97 Databases into SQL, and working
through many very complex interrelated queries which work fine in Access. On
translating many queries all work fine apart from when I get to the top leve
l
query which effectively nests many level of queries. On trying to display
this top level view I get the horrible
Server: Msg 3628, Level 16, State 1, Line 1
A floating point exception occurred in the user process. Current transaction
is canceled.
Now I have tried to narrow the problem down, even removed any floating point
datatypes from the view but I still get the problem.
I now have a view which works if I join 3 tables but on trying to join 4
tables gives the exception. It does not matter which table I miss out...
Am I hitting some limit of SQL which does not exist in Access 97? Or is
these a nasty bug floating around which cannot handle nested views of a
certain level...
Could some clever person please could come back to this posting urgently
with when there will be another fix for this problem, or email me at
getalifestyle@.easyget.bizHi
The fix as described in
http://support.microsoft.com/defaul...kb;en-us;892840 is not
included in SP4, so you need to get a seperate hotfix for it from Microsoft.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Steve Giergiel" wrote:

> I am in the process of translating Access 97 Databases into SQL, and worki
ng
> through many very complex interrelated queries which work fine in Access.
On
> translating many queries all work fine apart from when I get to the top le
vel
> query which effectively nests many level of queries. On trying to display
> this top level view I get the horrible
> Server: Msg 3628, Level 16, State 1, Line 1
> A floating point exception occurred in the user process. Current transacti
on
> is canceled.
> Now I have tried to narrow the problem down, even removed any floating poi
nt
> datatypes from the view but I still get the problem.
> I now have a view which works if I join 3 tables but on trying to join 4
> tables gives the exception. It does not matter which table I miss out...
> Am I hitting some limit of SQL which does not exist in Access 97? Or is
> these a nasty bug floating around which cannot handle nested views of a
> certain level...
> Could some clever person please could come back to this posting urgently
> with when there will be another fix for this problem, or email me at
> getalifestyle@.easyget.biz
>|||Hi
Can you please post the SQL Script here, so that we can try to give u a
solution
best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
---
*** Sent via Developersdex http://www.examnotes.net ***

Floating point exception

Hi guys,
what is the problem? how can I fix it?
DBCC INDEXDEFRAG (0, table1, 2)
Server: Msg 3628, Level 16, State 1, Line 1
A floating point exception occurred in the user process. Current transaction
is canceled.With all the problems you've been having, my suggestion would be to create
your database over again from scratch.
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23S7$KOjjFHA.2444@.tk2msftngp13.phx.gbl...
> Hi guys,
> what is the problem? how can I fix it?
>
> DBCC INDEXDEFRAG (0, table1, 2)
>
> Server: Msg 3628, Level 16, State 1, Line 1
> A floating point exception occurred in the user process. Current
> transaction
> is canceled.
>|||Britney wrote:
> Hi guys,
> what is the problem? how can I fix it?
>
> DBCC INDEXDEFRAG (0, table1, 2)
>
> Server: Msg 3628, Level 16, State 1, Line 1
> A floating point exception occurred in the user process. Current
> transaction is canceled.
What version of SQL Server are you using?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||..818
I think it's because corrupted data.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eKHiWTjjFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Britney wrote:
> What version of SQL Server are you using?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

Floating Point Error - Order By Mystery

I'm having a problem that I think is due to corrupt data. Depending on

the column I use in my order by clause two problems are occuring.

1. No results are returned and I get this error:
A floating point exception occured in the user process.

2. Results are returned but there are a different number of rows depending on which columns I use in my Order By clause.

Examples
SELECT * FROM SymbolStats
ORDER BY calc_date, symbol

Returns - 12207 rows but only includes one of the 25 dates in the table.

-

SELECT * from SymbolStats
ORDER BY current_hv

Returns - 0 rows.

-

SELECT * from SymbolStats
ORDER BY average_hv

Returns - floating point error

With more conditions in the WHERE clause the number of results returned varies greatly.

The

fact that different numbers of rows can be returned from the same query

only differing in how they are ordered seems like a bug.

Does this sound like corrupt data? If so, what are the best methods for fixing it?

Thanks,
patrickYou can run DBCC CHECKDB on your database to look for any possible corruption. You should also check your NT Eventlog for any hardware failures or driver issues or OS related problems.

floating point calculation

I'm trying to perform a calculation on a field in SQL Server that
should return a value with a decimal point. My problem is that the
value returned is truncated without the decimal point. Is there a
setting that needs to be turned on in SQL server to allow this?

for example

Select 20/3

should return 6.6666667

but instead I get 6"Never" <nevermind@.iname.com> wrote in message
news:e43b4225.0405071534.9a29f0a@.posting.google.co m...
> I'm trying to perform a calculation on a field in SQL Server that
> should return a value with a decimal point. My problem is that the
> value returned is truncated without the decimal point. Is there a
> setting that needs to be turned on in SQL server to allow this?
> for example
> Select 20/3
> should return 6.6666667

No, you should get 6.

Try select 20.0/3.0

You'll get 6.666666

When you say select 20/3 you're telling SQL Server you're starting with
ints, so it converts the answer to an int.

> but instead I get 6

floating point

Hi
Enybody had this error?
When I try connect to database shows weird error:
floating point exeption
MSDE working properly on other database.
Best regards
> When I try connect to database
Connect with what application? Using what mode of authentication? With
what type of user (peon, god, etc)? Anything different about this database
compared to the other database (different collation, recent restore, etc)?

> shows weird error:
> floating point exeption
Is that the whole and exact error?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
sql

Floating headers in a matrix

I added floating headers in a RS matrix and it worked great. I developed another RS matrix and decided I would add floating headers. I went back to the first RS matrix looking to find out how I did it. I couldn't find the location of the Header/ footer section that I checked that said " Header should remain visable when scrolling" Can someone help?Well I found it under the column and row heading. It wouldn't let me render a report with a floating header as the matrix is contained in a list. Any Ideas?|||

Please try the following approach:

1. Put the list into a main report.
2. Add a subreport inside the list.
3. Point the subreport to a report that has the matrix as a top-level data region.

-- Robert

|||ty

Floating headers

Hi ,
Is there any way to keep the first set of N columns along with the data as
frozen and the other columns move behind it as in excel horizontally.
I tried to check the option in layout --> fixed header = true but the header
along remains frozen but the data is getting overlapped.
please do help at the earliest.
thanks,
VidulaYup. You can apply a custom XSLT to the report XML, so you can create SSML
(Excel 2003's XML format) with any desired Excel elements you want.
Start like this:
* -- run your report and export to Excel. This will give you a base Excel
layout.
* -- tell your users to make whatever changes they want and give you back
the document.
* -- Save the result As an Xml Spreadsheet (SSML).
* -- write the XSLT you will apply to subsequent report runs to get this
result.
Note: it is probably possible to do the same thing to get XSLX (Excel 2007
base format). However you won't be able to do it by directly applying the
XSLT file using Report Properties, I don't think -- you'd have to specify
XML output and then do the work after receiving the raw XML data.
Either way, you may want to look into the DeviceInfo setting
UseFormattedValues -- you might prefer to receive the values in the columns
raw rather than formatted so that you can format them exactly the way Excel
prefers for various data types. This part really depends on the individual
report.
>L<
"Vidu" <Vidu@.discussions.microsoft.com> wrote in message
news:1D50F22D-EE6B-411D-80B5-27F34040E238@.microsoft.com...
> Hi ,
> Is there any way to keep the first set of N columns along with the data as
> frozen and the other columns move behind it as in excel horizontally.
> I tried to check the option in layout --> fixed header = true but the
> header
> along remains frozen but the data is getting overlapped.
> please do help at the earliest.
> thanks,
> Vidula
>

float4

I'm just looking through my lecture notes to finish off my assignment and have noticed something in the notes - float4.
I can't find an explanation of what it is/does in them and I don't have my books with me at the moment. Can someone tell me what float4 is and what it does please?Guessing from the name, it may be a data type. But there is no FLOAT4 in SQL - neither a data type with that name nor anything else is named that way in the standard.|||FLOAT(p) [UNSIGNED] [ZEROFILL]

A floating-point number. p represents the precision. It can be from 0 to 24 for a single-precision floating-point number and from 25 to 53 for a double-precision floating-point number. These types are like the FLOAT and DOUBLE types described immediately following. FLOAT(p) has the same range as the corresponding FLOAT and DOUBLE types, but the display width and number of decimals are undefined. As of MySQL 3.23, this is a true floating-point value. In earlier MySQL versions, FLOAT(p) always has two decimals. This syntax is provided for ODBC compatibility. Using FLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision.

helps? :)|||I'd think the "4" refers to the storage length in bytes.
As opposed to the other two often used, viz. "float-8" and "float-16". (The latter is e.g. available in the C and C++ programming languages.)
I believe that most relational systems use the term "FLOAT" or "REAL" for float-4, and the term "DOUBLE" for float-8.
Since typically 3 of the 4 bytes are used for the mantissa and 1 for the exponent and the signs, a quick calculation shows that the precision of a float-4 will be about 7 digits (24 bits), while for a float-8 it will be about 14 digits (48 bits).
If I'm not mistaken, the mySQL "p" refers to the number of bits in the precision; hence a FLOAT (or float-4) is most often a FLOAT(24), while a DOUBLE (or float-8) is most often a FLOAT(48).

float vs decimal - computation time

I have heard that using floats in calculated fields are much faster than
using decimals. Is this true? What is the role of the floating-point
processor in these computations?
JasonCP Developer (steved@.newsgroup.nospam) writes:
> I have heard that using floats in calculated fields are much faster than
> using decimals. Is this true? What is the role of the floating-point
> processor in these computations?
Instead of asking again, why not researching the responses to your post
from Wednesday?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland et al,
I apologize. I meant to follow-up my earlier post and I realize that it was
both unclear and unnecessary to repost. I have found what I was looking for.
Thank you very much for your help.
CP Developer
"Erland Sommarskog" wrote:

> CP Developer (steved@.newsgroup.nospam) writes:
> Instead of asking again, why not researching the responses to your post
> from Wednesday?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Float vs Decimal

Hello, sorry if this question is silly
I'm about difference between Decimal and Float data types , if im
writing accounting application and use 10:4 as precision/scale in all
numbers , does it matters if i choose fields as Decimal or Float in that
case ?
in BOL its says about float that
Approximate number data types for use with floating point numeric data.
Floating point data is approximate; not all values in the data type range
can be precisely represented.
i do not know what that means? can anyone give small example adding 2
different numbers that will give different answers if column type is decimal
than float ?
Best Regards
Bassamfloats store numbers as base 2, decimals as base 10. You can lookup a full
explaination on google, I would not do it justice. Tey this to see how they
differ.
create table Test
(NumDecimal decimal(10,4)
, numFloat float
);
insert into Test (NumDecimal, numFloat) values (0.1, 0.1);
insert into Test (NumDecimal, numFloat) values (0.3, 0.3);
insert into Test (NumDecimal, numFloat) values (0.25, 0.25);
insert into Test (NumDecimal, numFloat) values (1.0/3.0, 1.0/3.0);
insert into Test (NumDecimal, numFloat) values (1.0/6.0, 1.0/6.0);
select * from test;
select numdecimal*3 , numfloat*3 from test;
drop table Test;
"Bassam" <bassam@.nptco.com.eg> wrote in message
news:OpLEDirbGHA.2456@.TK2MSFTNGP04.phx.gbl...
> Hello, sorry if this question is silly
> I'm about difference between Decimal and Float data types , if im
> writing accounting application and use 10:4 as precision/scale in all
> numbers , does it matters if i choose fields as Decimal or Float in that
> case ?
> in BOL its says about float that
> Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type range
> can be precisely represented.
> i do not know what that means? can anyone give small example adding 2
> different numbers that will give different answers if column type is
decimal
> than float ?
> --
> Best Regards
> Bassam
>
>|||> can anyone give small example adding 2
> different numbers that will give different answers if column type is decim
al
> than float ?
Run below in Query Analyzer and you will see:
DECLARE @.fa float, @.fb float, @.da decimal(10,4), @.db decimal(10,4)
SELECT @.fa = 3.1, @.da = 3.1
SELECT @.fb = 5.5, @.db = 5.5
SELECT @.fa + @.fb
SELECT @.da + @.db
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bassam" <bassam@.nptco.com.eg> wrote in message news:OpLEDirbGHA.2456@.TK2MSFTNGP04.phx.gbl.
.
> Hello, sorry if this question is silly
> I'm about difference between Decimal and Float data types , if im
> writing accounting application and use 10:4 as precision/scale in all
> numbers , does it matters if i choose fields as Decimal or Float in that
> case ?
> in BOL its says about float that
> Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type range
> can be precisely represented.
> i do not know what that means? can anyone give small example adding 2
> different numbers that will give different answers if column type is decim
al
> than float ?
> --
> Best Regards
> Bassam
>
>|||I'm not sure you'll get an example by adding 2 numbers, but an
important point to note is that floats will sometimes "miss by a bit",
so you'll find that if you're doing lots of division and multiplication
you might end up with 1.00000000016 rather than 1. Generally your
application will have a natural degree of accuracy which you're working
within, so decimals are much better to use as they will not cause this
kind of behaviour|||Bassam (bassam@.nptco.com.eg) writes:
> Hello, sorry if this question is silly
> I'm about difference between Decimal and Float data types , if im
> writing accounting application and use 10:4 as precision/scale in all
> numbers , does it matters if i choose fields as Decimal or Float in that
> case ?
> in BOL its says about float that
> Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type range
> can be precisely represented.
> i do not know what that means? can anyone give small example adding 2
> different numbers that will give different answers if column type is
> decimal than float ?
Run this in Query Analyzer:
declare @.d1 decimal(10, 4), @.d2 decimal(10, 4),
@.f1 float, @.f2 float
SELECT @.d1 = 98.234, @.d2 = 87.0987
SELECT @.f1 = 98.234, @.f2 = 87.0987
SELECT @.d1 = 98.234, @.d2 = 87.0987
SELECT @.d1 + @.d2, @.f1 + @.f2
More generally, while is valid and reasonable to write:
WHERE decimalcol = 0
the same is not true for
WHERE floatcal = 0
Because due to rounding errors, floatcol may have a value like
0.0000000000000123
It's possible to use float in an accounting application, but you have to
be very careful. Decimal has its pitfalls too, but is probably safer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In an accounting application, exact values (decimal data type) should be
used.
For float and real data types, they are approximate as it a the specified
number of bits to store the mantissa of the float number in scientific
notation, in binary form. The conversion of a decimal value (with decimal
places) to a binary value usually results in a lost of precision.
Try the following.
select convert(decimal(10, 4), 111.1111) as MyDecimalValue,
convert(float(24), 111.1111) as MyFloatValue
Martin C K Poon
Senior Analyst Programmer
====================================
"Bassam" <bassam@.nptco.com.eg> bl
news:OpLEDirbGHA.2456@.TK2MSFTNGP04.phx.gbl g...
> Hello, sorry if this question is silly
> I'm about difference between Decimal and Float data types , if im
> writing accounting application and use 10:4 as precision/scale in all
> numbers , does it matters if i choose fields as Decimal or Float in that
> case ?
> in BOL its says about float that
> Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type range
> can be precisely represented.
> i do not know what that means? can anyone give small example adding 2
> different numbers that will give different answers if column type is
decimal
> than float ?
> --
> Best Regards
> Bassam
>
>|||you know, when running this in Management Studio I get
@.fa + @.fb = 8.6
@.da + @.db = 8.600
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23VAvd2rbGHA.2396@.TK2MSFTNGP02.phx.gbl...
>
> Run below in Query Analyzer and you will see:
> DECLARE @.fa float, @.fb float, @.da decimal(10,4), @.db decimal(10,4)
> SELECT @.fa = 3.1, @.da = 3.1
> SELECT @.fb = 5.5, @.db = 5.5
> SELECT @.fa + @.fb
> SELECT @.da + @.db
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bassam" <bassam@.nptco.com.eg> wrote in message
> news:OpLEDirbGHA.2456@.TK2MSFTNGP04.phx.gbl...|||Presenting the values returned (in binary format) from SQL Server is the tas
k of the client
application. Apparently, SSMS assumes that you aren't that concerned about a
ll the decimals when you
use float and real, while QA is more exact in the representation of these va
lues.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <ss@.Mailinator.com> wrote in message news:ufjgO$rbGHA.3800@.TK2MSFTNGP04.phx.gbl...[
color=darkred]
> you know, when running this in Management Studio I get
> @.fa + @.fb = 8.6
> @.da + @.db = 8.600
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23VAvd2rbGHA.2396@.TK2MSFTNGP02.phx.gbl...
>[/color]|||For completeness... what are the pitfalls of the Decimal datatype? I've
always found that as long as I'm careful with the precision the results
are accurate.|||I'd rather have the results from SSMS include all the decimals. Anyway to
force that, or is there an option to change?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OAme4FsbGHA.3388@.TK2MSFTNGP05.phx.gbl...
> Presenting the values returned (in binary format) from SQL Server is the
> task of the client application. Apparently, SSMS assumes that you aren't
> that concerned about all the decimals when you use float and real, while
> QA is more exact in the representation of these values.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve" <ss@.Mailinator.com> wrote in message
> news:ufjgO$rbGHA.3800@.TK2MSFTNGP04.phx.gbl...
>sql

float vs decimal

select convert(float,'1.2334e+006')
1233400.0

select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

Is there any way around?
Is there any set options? I tried arithabort or arithignore and they
don't work.
Thanks.(othellomy@.yahoo.com) writes:

Quote:

Originally Posted by

select convert(float,'1.2334e+006')
1233400.0
>
select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
>
Is there any way around?
Is there any set options? I tried arithabort or arithignore and they
don't work.

1.2334e+006 is not a legal literal for decimal. You will have to convert
in two steps, first to float, then to decimal.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Am 21 Nov 2006 22:09:05 -0800 schrieb othellomy@.yahoo.com:

Quote:

Originally Posted by

select convert(float,'1.2334e+006')
1233400.0
>
select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
>
Is there any way around?
Is there any set options? I tried arithabort or arithignore and they
don't work.
Thanks.

select convert(decimal(20,2),cast('1.2334e+006' as float))

bye,
Helmut

Float type

I inhereted a database that has a lot of Float type columns. Typically
these columns hold decimal-type data rounded to four decimal points
such as:
987.1234
Is Float the appropriate data type for use with these types of columns?
Float is generally referred to as an approximate datatype (approximate for the 10.base system, which
us humans tend to use). This mean that a value you input might not be the one which is stored, just
try below:
SELECT CAST(3.1 AS float)
If above is not acceptable, then use NUMERIC with a scale of 4 instead, like:
SELECT CAST(3.1 AS numeric(9,4))
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1135913496.671119.91530@.g49g2000cwa.googlegro ups.com...
>I inhereted a database that has a lot of Float type columns. Typically
> these columns hold decimal-type data rounded to four decimal points
> such as:
> 987.1234
> Is Float the appropriate data type for use with these types of columns?
>