I need to write a floating point value to a column in a SQL server database. I have the following line ofcode, that formats the float value before passing it off to the stored procedure. Please look at the line of code below. Does the 8 after SqlDbType.Foat mean that this will be a floatingpoint with 8 signigicant digits past the decimal point? mySqlCommand.Parameters.Add("@Price", SqlDbType.Float, 8).Value = float.Parse(InitialPrice);
I can't see any reason for this error, not having a high level understanding of maths I thought I'd post it and hope someone could share some light on it.
I yesterday got called by a client who said that a payment for £15 + VAT was being passed to their payment gateway as 17.62 when it should be 17.63. The VAT calculation is performed in a SQL Server 2000 stored procedure. In the end I tracked it down and it wasn't a propblem with my calculation.
The price was coming out as 17.63 fine. The stored procedure then had to return this price in pence (17.63 * 100 = 17.63). When I put in a print statement with this calculation it was correct but when I output the variable that the result was assigned to it was coming out as 1762.
The variable that the result was being put into was of real datatype.
I then wrote a udf to test this. Here is the function:
CREATE FUNCTION dbo.POUNDS_TO_PENCE ( @POUNDVALUE real ) RETURNS INTEGER AS BEGIN
RETURN @POUNDVALUE * 100
END
As you can see nothing very special.
If you run this runction and pass in 17.63 it will return 1762!!!
The bit I don't get is if I change the @POUNDSVALUE intput variable to type float it returns the correct amount.
I've also found that the same problem occurs when passing in £30 + VAT (35.25) + 1pence. So, 35.26 comes out as 3525 instead of 3526. This is the case if you keep doubling the number (and adding a few pence here and there).
Does anyone know why this is or is it a bug in the processor?
The SQL books online say the following about the float and real data types:
-------------------------------------------------------- float and real (T-SQL) 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.
Syntax float[(n)] Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.
n is Precision Storage size 1-24 7 digits 4 bytes 25-53 15 digits 8 bytes
The Microsoft® SQL Server™ float[(n)] data type conforms to the SQL-92 standard for all values of n from 1 to 53. The synonym for double precision is float(53).
real Floating point number data from –3.40E + 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24).
Intermittently, the following error has been displayed when a SQL job on our box (SQL Server 2000 Service Pack 4 running on Windows 2000) runs.
'A floating point exception occurred in the user process. Current transaction is canceled.' The error number given is 3628 though I've also seen a 4xxx number (not at machine at the moment so cant be precise for the latter).
The intermittent problem over the last two days has become more regular and now does not appear to be related solely to the SQL job. For example, when connecting to the box using my local copy of SQL 2005 Management Studio and I attempt to browse the database objects I get this problem.
To the best of my knowledge, this server has not had any software installed on it and/or nothing has changed on it recently.
It has now got to the point that this particular job no longer runs. The job in question, in case this is significant, is a vanilla stored proc that returns data using OPEN ROWSET to communicate with an Access database (this connectivity has been verified to be okay, in that other similar jobs all run just fine)
If anybody can shed some light and/or point me in the right direction I would be extremely grateful.
Hi,I would like to know, if I need to do some floting point operations(mainly multiplication and division) on each roll of a table, should Iread the data out from the DB and do the calculation with a programminglanguage, say C#, or should I just use sql to do it on the sql server.An obvious advantage of doing it in the sql server is that you dontneed to transfer the data between the sql server and he applicationserver.But I am not sure if there are any other factors that will overridethis advantage: like the performance of doing lots of floting pointoperations in sql server.How is the performance of doing floting point operations in sql servercomparing to C# or other languages?Also are there any other factors that should be considered for thiscase andare there any other advantages to do this is sql server or in c#?ThanksBenny*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I'm trying to perform a calculation on a field in SQL Server thatshould return a value with a decimal point. My problem is that thevalue returned is truncated without the decimal point. Is there asetting that needs to be turned on in SQL server to allow this?for exampleSelect 20/3should return 6.6666667but instead I get 6
Hi SQL people, I have rating system on pages in my website, each page being rated one to five by users. At the back end, an ASP.NET page displays the average rating and number of ratings for each page. As the rating is stored as an integer, the SQL statement returns an integer average. I would like to get a floating point average. Currently the statement used to return the average looks like this:
select AVG(Rating) as AverageRating from Ratings where [RatingPage] = @RatingPage
Is there a simple way to modify this to return a floating point average without iterating through the records, or converting the Rating field to a float?
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.
System.Data.SqlClient.SqlException: A floating point exception occurred in the user process. Current transaction is canceled. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at ...etc etc
when updating one particular table, but not when updating another using nearly the same code.
I've searched and searched on the inet, but with no joy.
Microsoft have several different manifestations of it, under various different circumstances, but the solutions all seem to involve installing SP4, which by the looks of it is a massive procedure that you have to go to university to be competent to do.
In any case, according to Enterprise Manager, I already have SP4 included. On the other hand, the one file of mine I checked the date of against MS's hotfix file list was 2002 not 2003, so who knows?
My Service Manager is v 8.00.760, and it's the Development Edition (ie it's all running on the one machine).
According to the info on sqlDataAdaptor.update, the sqlError that gets returned as part of the sqlException contains fields Class, which gives the seriousness of the error, and State, which identifies the exact error. I get Class=16 (user-fixable) and State=1, which we have to go to SQL Books Online to discover the meaning of. SQL BO probably sounded like a good idea to W.Gates, but it took me half an hour to get anywhere near finding out what that '1' meant. When I did, it was (guess): 'A floating point exception occurred in the user process. Current transaction is canceled.' !!!
Can anyone please tell me what exactly might be causing the error?
I am trying to understand why SQL Server gives me significantly lower precision than many of the other sources that have tried when using the POWER function. My environment is 2008 R2 SP2 (10.50.4000.0 X64, Standard edition) on Windows 2008 X64 SP1
-- using results from other sources (c#, windows calc, casio.com) SELECT POWER((1+3.33272237835747E-05),12) -- 1.0004 SELECT POWER((1+3.3327223783495255846580902358195e-5),12)-- 1.0004 SELECT POWER((1+3.332722378349525584658E-5),12) -- 1.0004
It is not important what I am trying to do, but in case it will work, I am trying to calculate the monthly return for a 90-day T-bill given the compounded annual return. x is the annualized return.
sql server 2000 sp4 build 2184runs stats with maintenance plansp_msforeachtablestored procedure updating full stats for each table and the resultsare identicalA floating point exception occurred in the user process. Currenttransaction is canceled.all pointers are to move to latest service packAny pointers on how to avoid this errorThanks for your timeMassa
I am trying to run "select * from <table> where <columnname> = 0.0" inquery analyzer<columnname> is a floatI get the following error:Server: Msg 3628, Level 16, State 1, Line 1A floating point exception occurred in the user process. Currenttransaction is canceled.I get the same error when I use the float <columnname> through anapplicationI have tried upgrading to SP4, without any luck - still get the errormessage.Any help would indeed be appreciated.Thanks -AH
We get the following error message."a floating point exception occured in the user process. currenttransaction is cancelled".this message comes when trying to excute a stored procedure. Thisexception is unpredictable.OS : Windows 2000 (SP3)Version: SQL server 2000 (SP3).
Hi,I'm running SQL Server Version 8.00.194 on Windows 2000.I am am running this query:select TOP 2000TheoVolImpliedfrom OptionTradeswhere ReutersSymbol = 'IBM.N'and TheoVolImplied > 0.0TheoVolImplied is of type float, precision 15, length 8.When I run this query I get this error:Server: Msg 3628, Level 16, State 1, Line 1A floating point exception occurred in the user process. Currenttransaction is canceled.If I run this query:select TOP 2000TheoVolImpliedfrom OptionTradeswhere TheoVolImplied > 0.0It works fine with no problems.If I run this query:select TOP 2000TheoVolImpliedfrom OptionTradeswhere ReutersSymbol = 'IBM.N'It works fine with no problems.Anyone have any ideas about what might be wrong?
First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.
Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.
Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.
To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.
I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.
My questions are as follows:
a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?
b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).
I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.
I am trying to export ssrs report from sharepoint to pdf file.But i am getting error in it."Sorry, Something Went Wrong".But when i try to render report in visual studio when i preview report. It gets exported in 14 minutes. but there is no error.
I installed SQL Server 2005 Express Edition on my Laptop for use with IIS 7.0; Everything was working fine untill I opened the Report Server Site with WVD 2008, it asked me to Modify the web.config for debugging. After doing that the Report Server Site started showing an error page saying there was a rsInternal error. I tried fixing the Web.config by hand but it dident work, so I tried completly reinstalling the SQL Server 2005 Express Edition. It reinstalled perfectly excluding the Report Server. When it came time to install the Report Server; I got the error message.
The setup has encountered an unexpected error while Setting reporting service and share point exclusion path. The error is: Fatal error during installation.
--Actual Log--
Microsoft SQL Server 2005 9.00.3042.00 ============================== OS Version : Professional Service Pack 1 (Build 6001) Time : Mon Apr 14 04:41:42 2008
Machine : JEOSYSTEMS-0002 Product : Microsoft SQL Server Setup Support Files (English) Product Version : 9.00.3042.00 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0008_JEOSYSTEMS-0002_SQLSupport_1.log ----------------------------------------------------------------------------- Machine : JEOSYSTEMS-0002 Product : Microsoft Office 2003 Web Components Product Version : 11.0.6558.0 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0008_JEOSYSTEMS-0002_OWC11_1.log ----------------------------------------------------------------------------- Machine : JEOSYSTEMS-0002 Product : Microsoft SQL Server 2005 Backward compatibility Product Version : 8.05.2004 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0008_JEOSYSTEMS-0002_BackwardsCompat_1.log ----------------------------------------------------------------------------- Machine : JEOSYSTEMS-0002 Product : Microsoft SQL Server Setup Support Files (English) Product Version : 9.00.3042.00 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0008_JEOSYSTEMS-0002_SQLSupport_2.log ----------------------------------------------------------------------------- Machine : JEOSYSTEMS-0002 Product : Microsoft SQL Server Native Client Product Version : 9.00.3042.00 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0008_JEOSYSTEMS-0002_SQLNCLI_1.log ----------------------------------------------------------------------------- Machine : JEOSYSTEMS-0002 Product : Microsoft SQL Server VSS Writer Product Version : 9.00.3042.00 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0008_JEOSYSTEMS-0002_SqlWriter_1.log ----------------------------------------------------------------------------- Machine : JEOSYSTEMS-0002 Product : Microsoft SQL Server 2005 Product Version : 9.2.3042.00 Install : Successful Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0008_JEOSYSTEMS-0002_SQL.log ----------------------------------------------------------------------------- Machine : JEOSYSTEMS-0002 Product : Reporting Services Error : The setup has encountered an unexpected error while Setting reporting service and share point exclusion path. The error is: Fatal error during installation. ---------------------------------------------------------------------------- Machine : JEOSYSTEMS-0002 Product : Microsoft SQL Server 2005 Reporting Services Product Version : 9.2.3042.00 Install : Failed Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0008_JEOSYSTEMS-0002_RS.log Last Action : InstallFinalize Error String : The setup has encountered an unexpected error while Setting reporting service and share point exclusion path. The error is: Fatal error during installation. Error Number : 29528 -----------------------------------------------------------------------------
If anyone knows how to get Report Services to install after Error number 29528 occurs, Please let me know. Thanks.
I am trying to deploy the reports on the share point site which is integrated with the reports Server.
I am getting below error while deploying it.
Sharepoint Server, Reports Server and Database Servers are on 3 different boxes.
===================================
A connection could not be made to the report server http://vstsvr:171/sites/wslReports. (Microsoft Report Designer)
===================================
Server was unable to process request. ---> The request failed with HTTP status 401: Unauthorized. (System.Web.Services)
------------------------------ Program Location:
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at Microsoft.SqlServer.ReportingServices2006.ReportingService2006.ListSecureMethods() at Microsoft.ReportDesigner.Project.ReportServiceClient2006.CheckAuthenticated() at Microsoft.ReportDesigner.Project.ReportClientManager.DetectEndpointAndAuthenticate(String url, ICredentials credentials, String& authCookieName, Cookie& authCookie, EndpointType& endpointType) at Microsoft.ReportDesigner.Project.ReportClientManager.GetCredentials(String url) at Microsoft.ReportDesigner.Project.ReportProjectDeployer.PrepareDeploy()
After performing a side-by-side instalation of SQL2005, I am not able to open Enterprise manager (for managing SQL2000 instance). It aborts with some errors related to mmc.exe not being able to use a couple of dlls: semsfc.dll and lmmc.dll. I've tried to unresgister/register them but I get the following error message:
semsfc.dll is loaded, but the dllRegisterServer entry point was not found... This file can not be registered .
I found a similar complaint in the VB forum, which recomended to use Reagasm but I couldn't understand it really, and I'm not sure if it applies to this SQL server issue.
I have a table Catalog_Item with 365000 rows. I wish to move the contents of the table to an identical database on a different server using bcp. The table has no primary keys, foreign keys, or indexes. I am able to successfully bcp out the data in character format and native format.
I attempt to bcp the data in to the identical table on the other server, but the bcp ALWAYS fails at 19000 or 20000 rows; it does not complete, just hangs there in the command window, with no more info. When I use a small data set of 5000 rows, the same happens at the end of those 5000. I have tried to redo the bcp in native mode, and character mode. I have tried to specify the first line starts at 125000. I have tried to bcp the data into a different database on a different server; that also quits at 19000 or 20000 rows.
BCP of other large data sets into the same database occurs smoothly. Only this combination of table and data set is giving this problem.
I got this error message during SQL Server Express install with no error code on Server 2003 SP1. I'm installing on an older development machine and although I do have a very large log file somewhere I wanted to post before I even dug into it. I noticed in IIS under my Webs that the MSSQLExpress webs were there at first but when I clicked on them to get any info or Properties... they dissapeared. So they were almost installed but they no longer exist. Also I do not have a SQL Service Manager icon down near the clock.
I almost took a step to fix this. After reading a few posts about similar problems I thought maybe this happened because the Default Web Sites IP # was not set to (All Unassigned). I was going to try a repair install from Add/Remove Programs but the temporary setup files are already gone. I really dont want to uninstall all 5 SQL components from Add/Remove Programs and start from scratch.
As good security practice we usually remove the Default Web or as in this development servers case we changed the IP from (All Unassigned) to an IP that is only accessible from certain machines on the internal network. I obviously should have read more pre setup guides. Anyway how can I fix this without reinstalling everything?
I am having trouble connecting to an SQL Server from an ASP.NET page written in C#. I have reduced the code in my page to this: public void Page_Load( object s, EventArgs e ) {
SqlConnection conn = new SqlConnection( "Server=server;User ID=user;Password=pass;" ); conn.Open();
} and I get the following error: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
This works for other server/user/pass except the one I am having trouble with. The SQL Server is using SQL & Windows Authentication. I am able to connect from the machine that is running the webserver via osql, ODBC, and MS Access using the same arguments (server, user, and pass), so I know that the SQL server does exist, and the User/Password should give me access. Does anyone have any ideas why this isn't working or what ASP.NET is does differently from the other access methods? Any ideas are greatly appreciated. Thanks, Mark Dane
I have created a maintenance plan which backups to a device which I have created. However the device does not show in explorer and maintenance plan cannot find it when it trys to open the device. Consequently I'm not getting a backup. Even if I delete the device and re create it things don't change.
I was working at a customer site and wanted to verify that backups were indeed being run successfully before I began to make changes.
I noticed a dump device for the production database with the nightly backup in it but I cannot find the task that produces this dump. I did find the task that dumps the log each hour.
Is there another area that some of you DBAs used to schedule dumps other than the task manager that I might be missing? Maybe an AT job?
Hi all,I have a statement that reads as follows:select [Sales Ledger] - gl as differencefrom(SELECT SUM(RPAAP / 100) AS [Sales Ledger] FROM F03B11) Q1join(SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +GBAN06+ GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100AS GLFROM F0902WHERE (GBAID = '00667809') AND (GBFY = 3)) Q2My first nested statement however I keep getting the message:Server: Msg 170, Level 15, State 1, Line 8Line 8: Incorrect syntax near 'Q2'.I just cannot fathom why this is so?Any suggestions would be most helpful.Moby*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hey All, Hope this is the right place for this post.
I set up logshipping between two databases recently. everything looks fine. the backup, copy and restore jobs are all suceeding(from the job log). the problem is no Tlogs are being restored in secondary database. The restore job skips all the tlogs and says it did not find any tlog back up file to restore!! The jobs finishes with this:
007-07-17 14:40:30.59 Could not find a log backup file that could be applied to secondary database 'SaaSNet_dataStore'.2007-07-17 14:40:30.59 The restore operation was successful. Secondary Database: 'SaaSNet_dataStore', Number of log backup files restored: 02007-07-17 14:40:30.59 Deleting old log backup files. Primary Database: 'SaaSNet_DataStore'2007-07-17 14:40:30.59 The restore operation was successful. Secondary ID: '5808a414-2ada-41d2-a8a0-2cf84f85174a'
In general, patch management solutions use an approach very similar to inventory and deployment, although, obviously, implementation details vary. Inventory relies on an external database to establish what is considered to be a recommended patch level and provides criteria for validating whether a particular patch has been installed. Products from Microsoft and Shavlik Technologies (on which HFNetChk, MBSA, and SMS 2.0 Feature Pack are based), keep track of published patches on the Microsoft Web site using the same mechanism, based on an XML formatted file called mssecure.xml. This file, available centrally at predefined locations, serves as a template against which the status of updates on target systems is compared. mssecure.xml can be obtained directly or via its compressed, digitally signed version, mssecure.cab. Both files can be downloaded from:
the Microsoft Web site at https://www.microsoft.com/technet/security/search/mssecure.xml and http://download.microsoft.com/download/xml/security/1.0/nt5/en-us/mssecure.cab the Shavlik.com Web site at https://xml.shavlik.com/mssecure.xml and http://xml.shavlik.com/mssecure.cab
