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