Results Of Float On Different Servers
Jan 7, 2004
Good morning,
I'm having trouble resolving an issue with the results of a column defined as a float. The problem is that the results returned on a development server are different from the results returned on the production server. Both servers are running sql2000 service pack 3, and both servers are on Windows 2000 service pack 3.
When running the statement "select factor from csm_security
where cusip = '07383FJB5' ", the results differ. The dev server returns 0.91531495529999995, but the prod server returns 0.91531496000000001. The result from the development server is what we expect, and require on the production server.
I've compared all configuration settings, and cannot see a difference.
Any ideas as to why this may be happening?
Thanks for any insight.
John
View 3 Replies
ADVERTISEMENT
Aug 30, 2007
Hi,
We have a client who runs SQL Server 2000 queries on one database server and performance is approx. 4 seconds. If the database is backed up, no tables in the query or indexes on these tables are modified (we may run a small script that affects stored procedures, views, etc.) the query can run virtually forever.
The customer is runing a cluster and we are running a stand-alone. Although, the two environments that they run in and have these wildly different results in are the same.
The queries are not worth listing (join a couple of tables and views, select a few columns, put on a few conditions--nothing crazy).
Is this normal behavior for MS SQL Server?
I've personally seen where a database is backed up and query plans and performance are different from one server to another, but we are looking at extreme cases here. In fact, on the second server, the majority of the queries are faster and only a couple run very slowly.
Also, the query optimizer seems to be making poor decisions at this custoemer. For example, two tables will be cross joined (forming over 200 million records) and then table scans ensue. The process in some cases will take a 4 second query to 45 minutes.
To me none of this makes any sense. I've been working with SQL Server since 1997 and have not experienced any type of performance problems or variances of this magnitude. Although this is a 6GB database, SQL Server 7 ran on a terabyte without even blinking, so I wouldn't understand why this would have anything to do with it.
Also hampering our efforts is that we do not have easy access to this SQL Server database to get our hands on it and debug these issues.
Does anyone know of a way to examine these issues in a "system wide" manner to determine what the problems could be since the problems are not specific to the database (i.e. .bak file) but seem to be specific to the server?
They have also had database corruption (an index that wouldn't update) and had to roll back the database. Would that indicate that the MDF/LDF's are unstable? Is there a way to figure out if there is some type of MDF/LDF file structure corruption?
Thanks,
Henry.
View 6 Replies
View Related
Apr 9, 2007
I can't take full credit for this. I want to share this with Jeff Moden who did the important research for this calculation here.
All I did was just adapting some old code according to the mantissa finding Jeff made and optimized it a little
Some test codeDECLARE@SomeNumber FLOAT,
@BinFloat BINARY(8)
SELECT@SomeNumber = -185.6125,
@BinFloat = CAST(@SomeNumber AS BINARY(8))
SELECT@SomeNumber AS [Original],
CAST(@SomeNumber AS BINARY(8)) AS [Binary],
dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Converted],
@SomeNumber - dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Error]
And here is the code for the function.CREATE FUNCTION dbo.fnBinaryFloat2Float
(
@BinaryFloat BINARY(8)
)
RETURNS FLOAT
AS
BEGIN
DECLARE@Part TINYINT,
@PartValue TINYINT,
@Mask TINYINT,
@Mantissa FLOAT,
@Exponent SMALLINT,
@Bit TINYINT,
@Ln2 FLOAT,
@BigValue BIGINT
SELECT@Part = 1,
@Mantissa = 1,
@Bit = 1,
@Ln2 = LOG(2),
@BigValue = CAST(@BinaryFloat AS BIGINT),
@Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)
WHILE @Part <= 8
BEGIN
SELECT@Part = @Part + 1,
@PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),
@Mask =CASE WHEN @Part = 2 THEN 8 ELSE 128 END
WHILE @Mask > 0
BEGIN
IF @PartValue & @Mask > 0
SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)
SELECT@Bit = @Bit + 1,
@Mask = @Mask / 2
END
END
RETURNSIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023)
END
Thanks again Jeff!
Peter Larsson
Helsingborg, Sweden
View 3 Replies
View Related
Nov 21, 2007
I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.
We use rectangles to force page breaks to page 2 and to page 3.
When running the report on the report server, it shows and prints fine.
When running the report from the QA website internally, it shows and prints just fine.
When running the report from the production website from a machine internally, it shows and prints just fine.
When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.
I need help in determining where I even begin with trouble shooting this!
View 1 Replies
View Related
Aug 21, 2001
can anyone tell me if they know of a way to automate the update process from development servers to live server, with little interference from an administrator
I have a development team that are constantly updating their databases along with their ASP code, and want to publish changes an a weekly basis. They have asked me for a way to take their new structures, tables, procedures etc, and copy them to the live servers, but NOT to interfere with existing customer data.
Funny I know – and I hate the idea btw :(
Any references, contacts, 3rd party tool recommendations welcome,
Thanx,
Darren
View 1 Replies
View Related
Sep 27, 2007
I am in the middle of a major migraton project, moving from x86 SQL 2000 to IA64 SQL 2005. I have a business need to link to several legacy servers. I have a number of problems I am trying to solve.
1) Linking a Kerberos server to a non-Kerberos server.
2) Linking x64 or IA64 servers to x86 servers.
3) Linking SQL 2005 to SQL 2000.
Two of the errors I am encountering are:
------------------------------
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
OLE DB provider "SQLNCLI" for linked server "SCDC250DB" returned message "Communication link failure".
(Microsoft SQL Server, Error: 10054)
------------------------------
And
------------------------------
The OLE DB provider "SQLNCLI" for the linked server "SCDC250DB" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SCDC250DB".
OLE DB provider "SQLCLI" for linked server "SCDC250DB" returned message "Invalid authorization specification".
(Microsoft SQL Server, Error: 7399)
If someone has worked through these problems before, I would appreciate it if you could direct me to the relevant documentation to resolve these issues.
Thanks!
Brandon Forest
Database Administrator
Data & Web Services Team
Sutter Connect Information Technologyforesb@sutterhealth.org
View 2 Replies
View Related
Apr 1, 2007
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies
View Related
Mar 25, 2015
I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
View 4 Replies
View Related
Feb 12, 2008
Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.
View 1 Replies
View Related
May 14, 2008
Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..
Thanks
dotnetdev1
View 4 Replies
View Related
Jun 18, 2008
Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1))) AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1. This doesn't make sense to me at all! For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI
BrianAustinHope83432230.7187500.7352941.15625014612201221
GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519
MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218
RobBennettMelville83029240.8275860.8333331.55172411904102117
AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015
ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP. Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)***
View 2 Replies
View Related
Feb 18, 2004
Does it make a difference if I use the Float or Int data type for a field such as ReceiptNumber or CheckNumber?
Thanks for any thoughts,
View 5 Replies
View Related
Oct 19, 2007
Hi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks
View 3 Replies
View Related
Jan 30, 2008
Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.
View 6 Replies
View Related
Feb 20, 2000
Hi!
I'm quite new to SQL Server. I need to set a float datatype to display something like 3.55. However, all values that are stored in the float column are truncated to 4 or some other single digit. How can this be prevented?
Regards,
Sam
View 1 Replies
View Related
Sep 5, 2002
Hello everyone,
I am sure this is a newbie question as I am new to Microsoft SQL server but any help is greatly appreciated. I am populating a SQL database from an AS400. The decimal numbers from the AS400 are coming accross with extra decimals. (ie. 63.02 is coming accross as 63.0200001)
Is there a way to limit the number of decimals in a float or real field - or a SQL command I can put in a script to truncate each field to 2 decimal places after they are populated.
Thanks,
Randy
View 1 Replies
View Related
Jun 29, 1998
We are having problems with rounding errors on large monetary calculations in sql server 6.5
The calculations include float fields (for volumes and unit of measure conversions in product movements). I was wondering if the float being "approximate" could be the problem.
IF it is, why would I want to store things as a float instead of a dec(28,14)?
Is it faster to compute numbers stored as approximate binaries? Will we see a big performance hit if we switch some of the table`s field`s to decimals?
Thanks in advance.
View 3 Replies
View Related
Jun 21, 2007
Hi,
why does converting integer to float take so long? Its a column with about 5 Million rows.
I want to avoid cast(inumber1 as float) / cast(inmuber2 as float), thats why converting them. Queries should be a bit faster after that.. hope so :)
Thanks a lot
View 14 Replies
View Related
Aug 30, 2007
I have some engineering data in my table and the db designer is representing it with a float datatype. Here's what is happening. If I query on a record based on id num and get a row and put it in text boxes in my Windows App, min_riv_hd_dia (the float) is 0.026<14 zeroes>2. If I try to query and get that same record again but this time based on id num and min_riv_hd_dia equal to 0.026<14 zeroes>2, I get no row found. If I just do a select on this row based on id number, sql server displays it as 0.026. But if I query with 0.026 as my value, still the row is not found. If I query min_riv_hd_dia > 0.026, the record is found.
So my question is, how can I tell the exact value that must be input in my search criteria in order to find this row?
Thank you so much if you can help!
View 4 Replies
View Related
Dec 29, 2003
Hi there
I have two Databases
in both databases are fields with float - no null
If I am transfering data from one database to the other everything works well unless there is a comma in the field ( 0,99 or 123,456 )
"SQLAString = "Insert into InventurDaten (Artikelnummer,Hauptartikelnummer,Auspraegung,Arti kelbezeichnung,Artikeltext1,EDVEingang ,EDVAusgang,InventurmengeEDV) values ('" & ArtNr & "','" & ArtNrT & "','" & AP & "','" & ArtBez & "','" & ArtText & "'," & EDVEingang & "," & EDVAusgang & "," & ArtMenge & ")"
"
where EDVEingang and EDVAusgang are defined as float, no null
Then the programm stops with the following message:
Within the INSERT-Procedure there are less columns then there are Contents in the Value-Clause.
I have to finish the programm until tomorrow morning and don't know what the problem is.
If anybody has an idea, please let me know.
regards
Reiner
View 14 Replies
View Related
May 4, 2004
When should I choose decimal over float and vice versa?
Mike B
View 4 Replies
View Related
Apr 10, 2008
Hi Everyone,
here is a simple SP
Create procedure test
@input float
as
Begin
return @input
ENd
If I execute the following
declare @output float
exec @output=test 1.12121
select @output
why I got 1 not 1.12121?
any idea? How to get the correct result?
Thank you in advance
View 2 Replies
View Related
Apr 3, 2006
Hi!How do I do to make t-sql not rounding the result that i returned?For example:0.9616458*60 = 57,698748 (in any calculator)while following:--------------------------------declare @a floatdeclare @b intset @a=0.9616458set @b=60print @a*@b---------------------------------will show :57.6987How do I do to make MSSQL to show me the value whothout rounding it?Thanks!
View 16 Replies
View Related
Nov 22, 2006
select convert(float,'1.2334e+006')1233400.0select convert(decimal(20,2),'1.2334e+006')Server: Msg 8114, Level 16, State 5, Line 1Error converting data type varchar to numeric.Is there any way around?Is there any set options? I tried arithabort or arithignore and theydon't work.Thanks.
View 2 Replies
View Related
Jul 20, 2005
HI,I WANT THIS TO PRODUCE EXACT RESULT. (IN SQL SERVER)SELECT (23 / 233) * 100THE ANSWER SHOULD COME 9.871244635 OR 9.87BUT IT RETURNS 0.I WANT THE PERCENTAGE.HOW TO HANDLE THIS KIND OF PROBLEM.IS THERE ANY SET COMMAND FOR IT?THANKST.S.NEGIJoin Bytes!
View 1 Replies
View Related
Jul 20, 2005
Hi,Just wonder whyPRINT CAST(0.0573542567654 AS float)will give the rounded reult0.0573543rather than the original number?"float" should be 'big' enough to hold numbers that have even moredecimal places. How come it round up at the 7 decimal place?since I need to do some calculations with accumulated values. Therounded figure will cause significant error after a number of opertions.Are there any way to work around it?thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Mar 20, 2008
I had database with "-1.#IND" (Indefinite /infinite) values in float columns.
Is there anyway I can insert -1#IND value into float column using some insert query and query analyzer? I am using SQL Server 2000.
I want to insert this value to replicate the issue..just for testing. I am also wondring how my database got these values.
Thanks in Advance!
View 1 Replies
View Related
Nov 20, 2007
I am keep getting an arithmetic overflow converting float to type numeric when running a script that looks something like this.
insert into table1
(
column1
)
select
column2
from source server.
column1 is a numeric (28,8) and column2 is float.
there are about 2000000 records in column2, and I know that when I tried just copying the top 1000000 wasn't a problem.
does anyone know what could be causing this problem???
*it's not because the data in column2 is out of range.
thank you
View 10 Replies
View Related
Mar 20, 2008
I had database with "-1.#IND" (Indefinite /infinite) values in float columns.
Is there anyway I can insert -1#IND value into float column using some insert query and query analyzer? I am using SQL Server 2000.
I want to insert this value to replicate the issue..just for testing.I am wondring how my database got these values.
Thanks in Advance!
View 1 Replies
View Related
Dec 23, 2006
I'm using c# 2.0.
I have a datareader that reads an invoice line item from a table in my SQL Server database. The UnitPrice field is a Money field in SQL server. At the same time I have an invoice class in my application that has a UnitPrice property which is a float.
How do I convert the SQLMoney to a float using my datareader?
Right now I have:
cm.CommandText = "SELECT ItemID, Quantity, UnitPrice, Discount FROM tblInvoiceLineItems";
using (SqlDataReader dr = cm.ExecuteReader())
{
while (dr.Read())
{
LineItem li = new LineItem();
li.UnitPrice = (float)(double)dr.GetFloat(3); // cast error here.
lineItems.Add(li);
}
}
View 1 Replies
View Related
May 28, 2007
hi
im using a stored procedure to insert float numbers in a data base....the parameter i send to the stored procedure is a float but every time i use it, it inserts a number with a lot of extra numbers i mean if i try to insert 5.3 it inserts 5.30000019073486 any idea why this is happening???
thak you
View 6 Replies
View Related
Jan 7, 2008
Hi All,
What is the difference betwen real and float datatype of SQL server.
Please one exapmle for each.
The real datatype is like a float except that real can only store numbers.What does it mean: float can store even characters.
Thanks
Abdul
View 2 Replies
View Related
Mar 11, 2004
Hi
I have only been coding in .Net for about six months, and am not sure if this is a C# problem or an SQL one. I use the Data Access Application Block in my program.
I have two optional fields on my form (RangeFrom and RangeTo). If the user chooses not to enter data into these textboxes(textbox = ""), an entry on the db is created with null values. It works.
But sometimes the user wants to enter 0 as either an upper or lower end of a range. This is where my problem comes in. My program saves 0 as null too.
In my program I do a test on the textboxes and populate two float values in a business object (objQuestion) accordingly, like this:
if (txtrangefrom.Text != "") {
objQuestion.RangeFrom=float.Parse(txtrangefrom.Text);
objQuestion.RangeTo=float.Parse(txtrangeto.Text);
}
else {
objQuestion.RangeFrom=Convert.ToSingle(null);
objQuestion.RangeTo=Convert.ToSingle(null);
}
And this is what my Business object look like. It sets up the parameters and calls the Data Access Application Block to create an entry in my table:
// fieldslist
float cvintRangeFrom;
float cvintRangeTo;
//properties
public float RangeFrom {
get {
return cvintRangeFrom;
}
set {
cvintRangeFrom = value;
}
}
public float RangeTo {
get {
return cvintRangeTo;
}
set {
cvintRangeTo = value;
}
}
// some code deleted for readability....
public int AddOption() {
string cvstrSpName = "addOption";
SqlParameter [] cvstrStoredParams = SqlHelperParameterCache.GetSpParameterSet(gcstrConnectionString, cvstrSpName, true);
//lines deleted for readability...
//check if the optional fields have a value associated with them. if not, assign dbnull.value.
cvstrStoredParams[4].Value=(cvintRangeFrom != Convert.ToSingle(null) ? cvintRangeFrom : (object)DBNull.Value);
cvstrStoredParams[5].Value=(cvintRangeTo != Convert.ToSingle(null) ? cvintRangeTo : (object)DBNull.Value);
//lines deleted for readability...
SqlHelper.ExecuteNonQuery(gcstrConnectionString, CommandType.StoredProcedure, cvstrSpName, cvstrStoredParams);
return(cvintOptionID = Convert.ToInt32(cvstrStoredParams[0].Value));
}
I use Convert.ToSingle when working with nulls (or possible nulls) because I get an error when I use float.parse for this.
The thing is, after this method AddOption has been executed, I test the value if the business object's rangefrom (that is where I entered 0) and display it on my form. I still shows a 0, but on my database table it is null!
objQuestion.AddOption();
//txtrangefrom.Text=""; on the next line I test the value in the business object...
txtrangefrom.Text=objQuestion.RangeFrom.ToString(); // and this displays 0!!!
//txtrangeto.Text="";
txtrangeto.Text=objQuestion.RangeTo.ToString();
So to me it seems the problem seems to be either the DAAB or on the SQL side, but hopefully somebody can prove me wrong! I was thinking that it could also be float.parse/Convert.ToSingle methods and have done various tests, but I am none the wiser...
Any help or ideas will be greatly appreciated...
View 2 Replies
View Related
