Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







MSSQL Float Data Problem


Hello,

I am connecting to MSSQL through ODBC using regular SQL commands (SQLAllocHandle, SQLConnect, SQLFetch). Sometimes when I retrieve float data from the server it gets corrupted. For instance, instead of 59.457443 I will actually get 59.45744299999998. The value that is stored in the database is 59.457443 and it is stored as a float. The code I use to retrieve the data worked OK with an Access database, the problem only appeared after switching to MSSQL. Does anybody know if it's a configuration issue? Any help would be greatly appreciated.

EoF


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
MSSQL && ODBC Float Problem
Hi,

I am having problems using MSSQL Express with ODBC. I am trying to retrieve a float field from the MSSQL server, but the value I get is corrupted (or at least approximated wrong). For instance, the value I want to retrieve is 59.457443, but what I actually get is 59.457442999999998. This only happens if I connect through ODBC. I did some tracing with ODBC and I've noticed that it's not a problem in my code, since the wrong value is what is actually extracted from the database. Following is an extract from the log file.

        a88-ec    EXIT  SQLGetData  with return code 0 (SQL_SUCCESS)        HSTMT               03C62918        UWORD                        5         SWORD                        1 <SQL_C_CHAR>        PTR                 0x0415EB30 [      18] "59.457442999999998"        SQLLEN                    50        SQLLEN *            0x0415E720 (18)


I am wondering if I overlooked something configuration-wise of if this is a bug in the driver? Any help would be greatly appreciated.

EoF

View Replies !   View Related
MSSQL Float Precision Problem By Using PHP Driver
I'm connecting MSSQL Server 2005 Express via MS Driver for PHP (CTP version October 2007) and sometimes I don't retrieve exact float values. For example, in database is 0.7 and I get 0.69999999999999996, but for 1.0 in database I get 1.0. The result is the same, if I use prepared statement (sqlsrv_conn_prepare() and sqlsrv_stmt_execute()) or directly sql_conn_execute().

The table definition is very simple:
CREATE TABLE [dbo].[test](
    a float NOT NULL
);
insert into test values(0.7);
insert into test values(1.0);
insert into test values(1.1);
insert into test values(1.2);
insert into test values(1.3);
insert into test values(1.4);
insert into test values(1.5);
insert into test values(1.6);
insert into test values(1.7);
insert into test values(1.8);
insert into test values(1.9);
insert into test values(2.0);

And select command is:
select a from test;

PHP code:
    $conn = sqlsrv_connect("localhostsqlexpress", array("UID" => "sa", "PWD" => "password"));
    sqlsrv_conn_execute($conn, "USE dbname");
    $stmt = sqlsrv_conn_execute($conn, "select a from test");

    while($row = sqlsrv_stmt_fetch_array($stmt, SQLSRV_FETCH_TYPE_ARRAY))
        echo $row[1]."; ";

Result:
0.69999999999999996; 1.0; 1.1000000000000001; 1.2; 1.3; 1.3999999999999999; 1.5; 1.6000000000000001; 1.7; 1.8; 1.8999999999999999; 2.0

Expected result:
0.7; 1.0; 1.1; 1.2; 1.3; 1.4; 1.5; 1.6; 1.7; 1.8; 1.9; 2.0

Running configuration:
Windows 2003 Server SP2, SQL Server 2005 Express SP2, IIS6, PHP 5.2.5 ISAPI

Thank you very much for help.
Vlasta

View Replies !   View Related
Convert A Binary Float To FLOAT Datatype
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 Replies !   View Related
Float Data Types
I've got a float data type in a table. I imported data from a csv file. One value was 0.5195, but when I use the value in a calculation or select it using the Query Analyser, I'm getting a value of 0.51949999999999996. Is there any way around this, it's a real pain?

Thanks in advance.

View Replies !   View Related
Float Data Type
We are using a GL package called Solomon. It uses SQL Server 7.0 for it's database. I want to create a data warehouse using this data as the source. The package uses the float data type for dollar amounts. The dollar amounts in the data have either no numbers, 1 number, or 2 numbers after the decimal point. Is the float data type the best one to use in my data warehouse for dollars and cents, or should I try using the monetary or decimal (precision 2) data type? Which type uses the most storage?

View Replies !   View Related
Float Data Type
I am using SQL Server 7.0. I create a table with one field..type of float.
Using SQL Server Query Analyzer:
INSERT INTO MyTable(MyField) VALUES (4.9)
INSERT INTO MyTable(MyField) VALUES (Round(4.9,2))


SELECT * FROM MyTable

Result = 4.9000000000000004

This is a basic example of a problem I am having in another table with the
same float field that I am using to store money in. I don't want to use
the money field as the BDE from Borland has some issues with money fields.
Any suggestions? Thanks in advance.

View Replies !   View Related
Error In Reading Float Data
I am using SQL Server 2000, VS 2003
I have Education table in which there is a field CGPA having float data type (null allowed) I retreive the data from SQL server using stroed proc and store it in SqlDataReader dr while reading if CGPA contains 0 then it raises an error that "Specified cast is not valid" other wise it does not raise any error.
while (dr.Read()){ Education e = new Education(); e.EducationId = dr.GetInt32(0);  e.Country = dr.GetInt32(1); e.InstitutionName = dr.GetString(2); e.Grade = dr.GetString(3); e.CGPA = dr.GetFloat(4); // ERROR HERE  e.Percentage = dr.GetFloat(5); e.PassingYear = dr.GetString(6);}
where as in Education CGPA is also the float property can any one tell me how to read 0 value of float from SQL server
 

View Replies !   View Related
Problem With Float Data Type
Hi all,
I have declared a field with datatype as float.
When I enter value with two precision it chooses to round off to lower value and insert into the database.

I am losing precision in this case.

I want to insert 4.56. It inserts the way shown and hence all my further calculations go haywire.

Is SQL server designed to store float like this or Am I doing soemting wrong ???

Please advise...

4.56 (Inserted)

4.5599999999999996 (Stored)

View Replies !   View Related
SQL Server Float Data Type
1. In c++ (or any other language) I'm copying bit by bit to a doublevariable dob_var.2. If I copy dob_var to dob_var2 which is also a double I do not loseinformation.3. This implies that the same would be when copying to a data base, butwhen I write dob_var to an sql server float field (same size as adouble variable = 64 bit) it sometimes loses information. e.g. whenvalue=1E-320.It is true that SQL float is valid until 1E-308 but so is a C++ double.Any comments?

View Replies !   View Related
MS Access And SQL Float Data Type
Hi,

I have a developer running an update on a table row that has a float
data tpye column and another TEXT type column among the others.

There is an in explicable situation where some updates to the TEXT column
is executed from MS ACCESS bu not others. Trapping the SQL code coming
from MS ACCESS we realized that the command fails when the float column
has a long precision values, for example like, 62.699999999999996. However
if I run the same update from SQL Query Analyzer it works fine.

The problem is that Access is NOT using the index in this update but incorrectly includes ALL columns in the tables in it's WHERE clause, including this float column , when the command fails. I assume it does NOT do so when the command executes correctly on values NOT like 62.699999999999996, because the SQL Profiler did not list the command run.

How can access be told to use the index ot NOT to use all columns in WHERE
clause. Or is this a BUG in access.

Thanks in advance.

Please also reply to my email address,
r.wimalaeatne@iaea.org

Ranjit

View Replies !   View Related
Why I Cannot Save Float Data Type
Hai...

Hopefully ur guys can help me. I declare data type as float in price table. So the problem here is, whenever i enter a float number and move to the second row to enter a new number. The first number in the first row will automatically change to integer, without no reason. For example "2.3" will become "25".

Is there any configuration that need to do.

View Replies !   View Related
Load Float Data From Excel
 

For example load  0.15 from Excel to varchar field in database and get 14.99999, why?
How just get 0.15 in varchar field?

View Replies !   View Related
Float Data Types And Scientific Notation
 

Hi All,
 
Am I missing something or is there something odd with float data types.  i know that float is not the most precise definition but i came across something really odd today.
 
first let me define the scenario.

this is sql server 2005, standard edition build 3042.
 
I have a table defined as
 

CREATE TABLE [dbo].[ASSET](

[Property_Num] [numeric](10, 0) NOT NULL,

[Accrual_Factor_Val] [float] NULL

)

 

the accrual_factor_val was updated to a value of 0.00005 then the web service failed because the proc returned 5E-05!
 
i opened the table, and discovered this is the stored value.  is this correct? 

 
 
thoughts and ideas?
 
thanks for your input!
 

View Replies !   View Related
Error Converting Data Type Varchar To Float
I have created a stored procedure that contain this field (below) inorder to meet certain criteria. But my problem is when I try to runthe stored procedure I encounter an error "Error converting data typevarchar to float".CASE Final WHEN 0 THEN '--' ELSE Final END AS FinalGradeThe Final field is a float data type.Could anyone teach me how to fix this problem?

View Replies !   View Related
Error Converting Data Type Varchar To Float
Hello everyone... i have some problem with instead of trigger... after insert values into v_PhotovoltaicClimateData, i got this error, Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6
Error converting data type varchar to float.

below is my DDL for Photovoltaic table

quote:CREATE TABLE [dbo].[Photovoltaic](
[PV_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Photovoltaic_PV_application_id] DEFAULT (newid()),
[PV_site] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_state] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_type_of_system] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_nominal_power] [float] NULL,
[PV_module] [varchar](150) COLLATE Latin1_General_CI_AI NULL,
[PV_mounting] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_building_type] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_topology] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_new_or_retrofit] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_design] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_construction] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_commissioning_date] [datetime] NULL CONSTRAINT [DF_Photovoltaic_PV_commissioning_date] DEFAULT (getdate()),
[PV_site_photo] [varbinary](max) NULL,
[PV_peak_nominal_rating] [float] NULL,
[User_application_id] [uniqueidentifier] NULL,
[Org_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_Photovoltaic_1] PRIMARY KEY CLUSTERED
(
[PV_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Below is my DDL for ClimateData table

quote:CREATE TABLE [dbo].[ClimateData](
[Climate_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClimateData_Climate_application_id] DEFAULT (newid()),
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[Altitude] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[Climate_type] [varchar](100) COLLATE Latin1_General_CI_AI NULL,
[PV_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_ClimateData_1] PRIMARY KEY CLUSTERED
(
[Climate_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]



below is my v_PhotovoltaicClimateData

quote:CREATE VIEW [dbo].[v_PhotovoltaicClimateData]
AS
SELECT dbo.Photovoltaic.PV_site, dbo.Photovoltaic.PV_state, dbo.Photovoltaic.PV_type_of_system, dbo.Photovoltaic.PV_nominal_power,
dbo.Photovoltaic.PV_module, dbo.Photovoltaic.PV_mounting, dbo.ClimateData.Latitude, dbo.ClimateData.Longitude, dbo.ClimateData.Altitude,
dbo.ClimateData.Climate_type
FROM dbo.ClimateData INNER JOIN
dbo.Photovoltaic ON dbo.ClimateData.PV_application_id = dbo.Photovoltaic.PV_application_id

below is my instead of trigger command...

quote:CREATE trigger [tr_v_PhotovoltaicClimateData] on [dbo].[v_PhotovoltaicClimateData] instead of insert as
BEGIN

insert Photovoltaic (PV_site, PV_state, PV_type_of_system, PV_nominal_power, PV_module,
PV_mounting)
select distinct inserted.PV_site, inserted.PV_state, inserted.PV_type_of_system,
inserted.PV_nominal_power, inserted.PV_module, inserted.PV_mounting
from inserted left join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
and inserted.PV_state = Photovoltaic.PV_state
and inserted.PV_type_of_system = Photovoltaic.PV_type_of_system
and inserted.PV_nominal_power = Photovoltaic.PV_nominal_power
and inserted.PV_nominal_power = Photovoltaic.PV_module
and inserted.PV_nominal_power = Photovoltaic.PV_mounting
where Photovoltaic.PV_site IS NULL /*** Exclude Photovoltaic already in the table ***/

insert ClimateData (Latitude, Longitude, Altitude, Climate_type, PV_application_id)
select distinct inserted.Latitude, inserted.Longitude, inserted.Altitude, inserted.Climate_type,
Photovoltaic.PV_application_id
from inserted inner join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
left join ClimateData on inserted.Latitude = ClimateData.Latitude
and inserted.Longitude = ClimateData.Longitude
and inserted.Altitude = ClimateData.Altitude
and inserted.Climate_type = ClimateData.Climate_type
where ClimateData.Latitude IS NULL /*** Exclude Climate Data already in the table ***/

END -- trigger def

this is my commad insert values using instead of trigger that i've created...

quote:insert into v_PhotovoltaicClimateData(PV_site, PV_state, PV_type_of_system, PV_nominal_power,
PV_module, PV_mounting, Latitude, Longitude, Altitude, Climate_type)
values ('CETDEM', 'Sequ Inspection', 'Cheras', 34.44 , 'ew', 'ewew',
34343.44, 323.32, 'ewew', 'ewewe')

after execute this commad, i got this error..
quote:Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6
Error converting data type varchar to float.

what should i do??? anyone help me.

View Replies !   View Related
Error Converting Data Type Varchar To Float.
hello anyone... i got this message "Error converting data type varchar to float" when i was trying to insert values into table using instead of trigger...

below is my table ClimateData

quote:USE [PVMC Database]
GO
/****** Object: Table [dbo].[ClimateData] Script Date: 03/26/2008 03:04:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ClimateData](
[Climate_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClimateData_Climate_application_id] DEFAULT (newid()),
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[Altitude] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[Climate_type] [varchar](100) COLLATE Latin1_General_CI_AI NULL,
[PV_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_ClimateData_1] PRIMARY KEY CLUSTERED
(
[Climate_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [PVMC Database]
GO
ALTER TABLE [dbo].[ClimateData] WITH CHECK ADD CONSTRAINT [FK_ClimateData_Photovoltaic] FOREIGN KEY([PV_application_id])
REFERENCES [dbo].[Photovoltaic] ([PV_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE


Below is photovoltaic table

quote:USE [PVMC Database]
GO
/****** Object: Table [dbo].[Photovoltaic] Script Date: 03/26/2008 03:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Photovoltaic](
[PV_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Photovoltaic_PV_application_id] DEFAULT (newid()),
[PV_site] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_state] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_type_of_system] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_nominal_power] [float] NULL,
[PV_module] [varchar](150) COLLATE Latin1_General_CI_AI NULL,
[PV_mounting] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_building_type] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_topology] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_new_or_retrofit] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_design] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_construction] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_commissioning_date] [datetime] NULL CONSTRAINT [DF_Photovoltaic_PV_commissioning_date] DEFAULT (getdate()),
[PV_site_photo] [varbinary](max) NULL,
[PV_peak_nominal_rating] [float] NULL,
[User_application_id] [uniqueidentifier] NULL,
[Org_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_Photovoltaic_1] PRIMARY KEY CLUSTERED
(
[PV_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [PVMC Database]
GO
ALTER TABLE [dbo].[Photovoltaic] WITH CHECK ADD CONSTRAINT [FK_Photovoltaic_OrganizationDetail] FOREIGN KEY([Org_application_id])
REFERENCES [dbo].[OrganizationDetail] ([Org_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Photovoltaic] WITH CHECK ADD CONSTRAINT [FK_Photovoltaic_Users] FOREIGN KEY([User_application_id])
REFERENCES [dbo].[Users] ([User_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE

Below also my command for instead of trigger


quote:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


CREATE trigger [tr_v_PhotovoltaicClimateData] on [dbo].[v_PhotovoltaicClimateData] instead of insert as
BEGIN

insert Photovoltaic (PV_site, PV_state, PV_type_of_system, PV_nominal_power, PV_module,
PV_mounting)
select distinct inserted.PV_site, inserted.PV_state, inserted.PV_type_of_system,
inserted.PV_nominal_power, inserted.PV_module, inserted.PV_mounting
from inserted left join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
and inserted.PV_state = Photovoltaic.PV_state
and inserted.PV_type_of_system = Photovoltaic.PV_type_of_system
and inserted.PV_nominal_power = Photovoltaic.PV_nominal_power
and inserted.PV_nominal_power = Photovoltaic.PV_module
and inserted.PV_nominal_power = Photovoltaic.PV_mounting
where Photovoltaic.PV_site IS NULL /*** Exclude Organization Detail already in the table ***/

insert ClimateData (Latitude, Longitude, Altitude, Climate_type, PV_application_id)
select distinct inserted.Latitude, inserted.Longitude, inserted.Altitude, inserted.Climate_type,
Photovoltaic.PV_application_id
from inserted inner join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
left join ClimateData on inserted.Latitude = ClimateData.Latitude
and inserted.Longitude = ClimateData.Longitude
and inserted.Altitude = ClimateData.Altitude
and inserted.Climate_type = ClimateData.Climate_type
where ClimateData.Latitude IS NULL /*** Exclude Organization Types already in the table ***/

END -- trigger def




and finally, i hav tried using this command to insert into table v_PhotovoltaicClimateData.. this is the command to insert

quote:insert into v_PhotovoltaicClimateData(PV_site, PV_state, PV_type_of_system, PV_nominal_power,
PV_module, PV_mounting, Latitude, Longitude, Altitude, Climate_type)
values ('CETDEM', 'Sequ Inspection', 'Cheras', 34.44, 'ew', 'ewew',
34343.44, 323.32, 'ewew', 'ewewe')

and i got this error...

quote:Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6
Error converting data type varchar to float.



so anyone... plzz help me...

View Replies !   View Related
Using Float/Money/Numeric Data Types On SQL Server 2000
Hi,
I am trying to execute a stored procedure in my ASP VB script code to insert a recordset into a table of SQL Server 2000.
On the tables, I use some money data and a float. In the stored procedure, I used the numeric type for both values:

CREATE PROCEDURE dbo.PRODUCT_Insert_Accessories
@partnumber CHAR(50),
@product CHAR(255),
@prodesc CHAR(500),
@proprice numeric, <----- this is normally a money type in the table
@price numeric, <----- this is normally a money type in the table
@shipoption INT,
@peso numeric, <----- this is normally a float type in the table
@supplier int,
@cost numeric, <----- this is normally a money type in the table
@bspecial int,
@miles INT,
@speprice numeric, <----- this is normally a money type in the table
@fship int,
@id int OUTPUT


To call this stored procedure, I used the following ASP code:
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_Backoffice_STRING
Command1.CommandText = "dbo.PRODUCT_Insert_Accessories"
Command1.CommandType = 4
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Parameters.Append Command1.CreateParameter("@RETURN_VALUE", 3, 4)
Command1.Parameters.Append Command1.CreateParameter("@partnumber", 129, 1,50,Command1__partnumber)
Command1.Parameters.Append Command1.CreateParameter("@product", 129, 1,255,Command1__product)
Command1.Parameters.Append Command1.CreateParameter("@prodesc", 129, 1,500,Command1__prodesc)
Command1.Parameters.Append Command1.CreateParameter("@proprice", 131, 1,8,Command1__proprice)
Command1.Parameters.Append Command1.CreateParameter("@price", 131, 1,8,Command1__price)
Command1.Parameters.Append Command1.CreateParameter("@shipoption", 3, 1,4,Command1__shipoption)
Command1.Parameters.Append Command1.CreateParameter("@peso", 131, 1,8,Command1__peso)
Command1.Parameters.Append Command1.CreateParameter("@supplier", 3, 1,4,Command1__supplier)
Command1.Parameters.Append Command1.CreateParameter("@cost", 131, 1,8,Command1__cost)
Command1.Parameters.Append Command1.CreateParameter("@bspecial", 3, 1,4,Command1__bspecial)
Command1.Parameters.Append Command1.CreateParameter("@miles", 3, 1,4,Command1__miles)
Command1.Parameters.Append Command1.CreateParameter("@speprice", 131, 1,8,Command1__speprice)
Command1.Parameters.Append Command1.CreateParameter("@fship", 3, 1,4,Command1__fship)
Command1.Parameters.Append Command1.CreateParameter("@id", 3, 2,4)
Command1.Execute()

But, I have an error (type mismatched, not the good data type) on the lines where are declared the money data and the float data.

Please, help me to found out the solution. Is it a data type problem?

Thanks,
Chris

View Replies !   View Related
Replace Nulls With Blank Spaces In Float Data Type
Hello,
I have a simple question. Is it at all possible to replace columns which has nulls with blank spaces for a float data type column.
The columns has null values( written)) in it in some rows and has numbers in other rows . I want to remove nulls before copying it to another file.
Thanks

View Replies !   View Related
Converting A Data Type Double To A Type Float Using A Data Adapter &&amp; SSCE 3.5
Hi,
 
I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is:
 

this.court0TableAdapter1.Update(this.mycourtsDataSet1.Court0);

this.mycourtsDataSet1.AcceptChanges();

this.court0TableAdapter1.Fill(this.mycourtsDataSet1.Court0);

 
This did not happen with VS2005/CE3.01.
 
I have tried changing all references to decimal (or money in CE3.5) without luck.
 
I'm beginning to think that string may be the way to go!!!!!!!
 
Can someone shed some light on my problem.
 
Thanks,
 
Later:
 
It's necessary to update the datatable adapter as the 3.01 and 3.5 CE are not compatible.
 

View Replies !   View Related
Arithmetic Overflow Error Converting Expression To Data Type Float
I'm hoping you can help me.

I have the following expression:

[Inflation Adjusted Total] = POWER(dbo.v_ProjectTotalCosts.[Total Job Incl IDC]*
CONVERT(DECIMAL(10,5),[contingency])/100+1*CONVERT(DECIMAL(10,5),[Inflation Rate])/100+1,
Case when (CONVERT(DECIMAL(10,5),[In Service Year]) -
CONVERT(DECIMAL(10,5),[Estimate Base Year]))= 0
then 0 ELSE (CONVERT(DECIMAL(10,5),[In Service Year]) - CONVERT(DECIMAL(10,5),[Estimate Base Year]))END),

Total Job Incl Idc(decimal(12,6) = 546789.102568
Contingency(Real) = 10.0
Inflation Rate(Real) = 3.0
In service yr(int) = 2001
Estimate Base Year(int) = 2001

I have tried to modify this expression every which way but I still get the error message:

Arithmetic overflow error converting expression to data type float.
Warning: Null value is eliminated by an aggregate or other SET operation.

Do you have any idea what I'm doing wrong?

View Replies !   View Related
Disabled The Automatic Round Of SQL SERVER 2000 On The Float Data Type
Hello,

I have a problem with the round of SQL Server 2K on the float data type.

In my application I do a SQL request for find a row in a table of SQL Server 2K.

This is the request :

SELECT DISTINCT N_ROW_ID FROM COMM WHERE N_ARTICLE_ID=79510 AND N_DATASOURCE_ID=1 AND N_SOURCE_ID=-102 AND N_PROVIDER_ID=-100
AND N_LEAD_TIME IS NULL AND N_UNIT_PRICE = 329.78 AND N_UNIT_PRICE_CURRENCY_ID=1

 

N_UNIT_PRICE is a float data type.

In the line need, the field N_UNIT_PRICE has 329.78 but with the round of SQL Server 2K I have 329.779999999999997.

So I never find the row

 

Do you have a solution for comparate a float flied with SQL Server round and a float send in a request as my 329.78?

I can't transform the column in decimal or numeric data type.

Is it possible to Disable the function?

 

Regards

View Replies !   View Related
(Urgent)How Can I Specify The Excel File's Column To Import Data As Varchar Instead Of Float
 

Hi,
     I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...
 
but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...
 
what can i do to rectify the problem...
 
regards
Karen

View Replies !   View Related
Help With &"Error Converting Data Type Varchar To Float&"
Error converting data type varchar to float.


I am getting this error. Any one there to guide me

View Replies !   View Related
Importing CSV Data To MSSQL Using PHP
Hi, is this possible at all? From my understanding there are tools you canbuy to convert and merge data from an excel / csv document into an MSSQLtable, the thing is the convertor needs to be run on the server side and hasdirect connection to MSSQL. What I need to do though, is to import an excel/ csv file into an MSSQL table hopefully remotely via PHP. So is there a wayto do this at all?I know PHP has file functionality to load the file's contents into a string,so in theory we can write some PHP code to take a URL and analyse the csvfile and then manipulate the data and import into the MSSQL db, do you knowany sample code that does this? And am I right in thinking that this isdoable?Thanks,Jules

View Replies !   View Related
Help With MSSQL Data Types
I currently run our company website using MS Access and ASP. I am trying to upgrade the database to MSSQL Server 2000. Unfortunately I'm not finding it that straight forward. I am trying to learn MSSQL but the learning curve is a bit steep.

I have a column in my MSSQL db called tbl_FULL which contains a full description of the product along with html formatting for line breaks, paragraphs and special characters (ie •).

I have set the data type to varchar 1800. All seemed to be well until I tried to enter new data, but unfortunately it only displays the first 50 or so characters.

Can anyone tell me what I am doing wrong?

View Replies !   View Related
Image Data From MSSQL 6.5
I would like to use bcp and transfer image data from MS-SQL Server 6.5 to MS SQL Server 2005.

1. Can I use bcp successfully? if so should I mention any option to copy the BLOB?
2. Is there any reliable method to move tables with BLOBs

Regards,

/ash

View Replies !   View Related
Data Type That Accept . For MSSQL
Hi,


I wish to enter some string data with period(.) in a column with char type. It doesn't accept period(.) What data type should I choose for this field ?

I'm using MSSQL Server 2000.


Please advice.

View Replies !   View Related
I Need To Know Where Mssql Server Stores Its Data.
Hello,I need to learn where mssql server stores its data. I knew it was inProgram filesCommon filesBut I cant find.. Its urgent pls..Thanx

View Replies !   View Related
Data Replication From MSSQL To MYSQL
Hi all.I need to set a oneway replication of some data from MSSQL (running underWindows 2000 server) to MYSQL (running under Linux).Do you have some ideas to solve the problem?Thank youLuca

View Replies !   View Related
Dump MSSQL Data As SQL Statements
What I need to do is dump out the structure AND data of an MSSQL database as SQL scripts (DDL and a series of Insert statements). The first is easy, the second is not supported within enterprise manager, and seems rather harder than I imagined, especially as mySQL has this very useful technique built in.

Any pointers on tools/code that could help would be much appreciated, note in our instance we have are using GUID keys and Blob fields extensively, so a basic int/varchar dump wont do the necessary. Assuming there is something out there that will help, then a really nice feature would be the ability to dump in SQL format for different targets - Oracle, DB2, MSSql and mySQL would be a good starting point.

View Replies !   View Related
What Must Have Clients To Work With Mssql Data
reading help i know that client for working with mssql remote db must have installed oledbprovider for XXX

i always use ole db provider for ODBC
, this ole db provider is installed with mssql, but what exactly must to have the clients which are not familiar with this ole bd provider, what files ?

View Replies !   View Related
Extracting Data From MSSQL To MSExcel
I recall something about Analysis Services (SQL server tool) that is able to extract data from the DB tables (select multiple tables if one wishes (and to link those tables up ??!?)) then extracted to MS Excel. It has been a while since the course was last taught, I'm not at all sure how the steps were, or how Excel were able to display them. Would someone advice or refer to a tutorial if possible?

Much thanks!

View Replies !   View Related
MSSQL DTS Newbie - Data Transfer Between Two DBs
I am just starting out with MSSQL, but have previous experience with MySQL and PGSQL. What I'm trying to do is do a nightly dump of data from a proprietary DB into MSSQL. Access to the prop DB is through an ODBC driver.

I already have a DTS script that will dump a specific table, but every time it runs it just appends the data to the end of the table.

What I am looking for is a way to download a list of tables, upload them into the MSSQL DB, and if something failed, rollback to the state before the data transfer and somehow alert of the fact.

Any help will be greatly appreciated!

View Replies !   View Related
Synchronizing Data Between MSSQL And Sybase ASE
Hi

I would like to know how to schedule MSSQL and Sybase ASE to synchronize (ie. pull and push data) data between themselves every 3 mins.

Do i need to write my own timer program, to transfer data to and fro between MSSQL and Sybase ASE? If so, how?

Will merge replication in MSSQL work?

Thanks

View Replies !   View Related
Export Mssql Data To Excel Using Vb6
Where is the correct forum for that,
Thanks,
Dani.

View Replies !   View Related
How To Specify The MSSQL.1 DATA Folder In SQL Express?
How do you determine the data folder during the SQL2005 Express install?
No matter what folder I start with it gets appended with MSSQL.1 or MSSQL.2
If another vendor has installed a different instance of SQL2005 how do I know whether mine is .1 or .2 ?
 
Basically I need to know the full folder in order to copy our MDF/LDF files into so we can attach them.
 
Or is there another way?
 
Thanks.
 
Graham
 
 

View Replies !   View Related
Insert Data To MsSQL Express Database?
Hello there,I'm to asp.net, so please be patient :DMy question is, how do I simply add some data to my database? - With vb.net code, not a grid view or something like that..I want to connect to my database, insert some data to a table.It shouldn't be that hard?- Hope someone will take the 5 minutes, and help me :)Regards Jeppe

View Replies !   View Related
Transfer Data To Hostserver MSSQL-2000
Thanks for the replies,

As per the querys:
------------------------
1) I am trying to use the Database of my host (hosting server of datapacket). They have a support for MS-SQL-2000. Therefore surely the server is there. After my CPanel configuration i got the Database Server IP (67.122.X.X), Port - 1433, SQL Server user and pasword.

2) In my local machine SQL Server 2000 is installed. Where the databse and DNS is configured.

Now, from this local machine how can i transfer the data to the host machine (remote SQL Server).

Simpliy copying Files *.MDG and *.LDf files it is not working. And i don't have access to the remote SQL-SERVER as it is in the host machine of datapacket. I can only configure it throuch there Control Panel and throuch FTP can copy the required files in there machine. I have a user in that remote MS-Sql server that all i have.

As per my understanding after the control panel configuration the database and the DNS is created in the remote Sql-Server of datapacket. Now all i have to do is transfer the data & tables. HOW TO DO THAT?????

I have tried using Export but it fails to connet the remote database.

Thanks in Advance

Dipya

View Replies !   View Related
MSSQL: Storage Of Varying Data Types
I am developing an online web-based address book for multiple users. There are STANDARD FIELDS and CUSTOM FIELDS.

Standard fields include: Name,Street,City,State,Zip.
Custom fields are those defined by a specific user. For example:

User-A Custom fields:
Interest Rate <real>
Loan Amount <currency>
Start date <date>

User-B Custom fields:
Blood type <char 3>
Date of birth <date>
Referred by <varchar 50>

Different users can have different custom fields in their address book. As you can see, while the standard fields for each user can be

stored in a single table. However, I have several methods by which I can store the CUSTOM fields.

------------------------------------------------
Method 1: Create 2 separate tables called CustomField and CustomValue:

CustomField has fields:
FieldID <int>
FieldName <varchar 25>
UserID <int>

CustomValue has fields:
ValueID <int>
Value <varchar 50>
FieldID <int>

------------------------------------------------
Method 2: Create a separate Field and multiple Value tables for each data type:
CustomField, CustomCharValue, CustomIntValue, CustomMoneyValue, etc...

CustomField has fields:
FieldID <int>
FieldName <varchar 25>
FieldType <smallint> (determines which TABLE, below, contains the data)
UserID <int>

CustomCharValue
CharValueID <int>
IntValue <Varchar 50>
FieldID <int>

CustomIntValue
IntValueID <int>
IntValue <int>
FieldID <int>

etc....etc...


The structures of those tables would be similar to Method 1, but the data would be segregated based on their data type.

--------------------------------------------------

I'm thinking that while Method 1 will be easier to implement, Method 2 may offer me better performance if coded correctly. I'm going

to assume that I'll have at least 1-5 million records to work with over the course of my first year and I will need the ability to sort

records based on values in the custom fields as well.

My first question is: Which method should I be considering and is there an alternative or hybrid that I should be considering?

My second question is: What statements should I use in my stored procedure that will enable me to
retrieve a list of USERID, CustomFieldIDs and their values as one resulting table that I can query at will and with solid performance?

Gregory
email: sqlGuy@clubtel.com

View Replies !   View Related
How To View Binary Data In MSSQL 2005
Hi all,

I have a problem reading binary data in MSSQL using the Server Mgmt Studio. All it shows in the column is "<Binary data>". Is there a way to view this data at least the SIZE?

Thanks.

View Replies !   View Related
MSSQL 2000 - Import Data Error
Can someone give a relative newcomer some advice please?

I have a comma delimted file that I wish to import into a table in MSSQL 2000. I am using the import wizard and completing all necessary tabs but when I run the import, I get an error that the file failed to import. Clicking the message reveals "Error at Destination for Row 1523. Errors encountered so far in this task: 1."

There are 1523 data rows in my CSV file plus a header row. The wizard accepts that I have a header row and displays the text accordingly. I have tried deleting the last record in the file but the same error is displayed - except that the row number changes to 1522. I'm assuming that there's a problem with the format of the data but can't see what it is.

Does anyone have any advice?

Thanks in advance!

View Replies !   View Related
MsSQL Image Data Type Truncation
Hello, I am trying to store pictures in an Image data type column of my MsSQL table from PHP, or even SQL Query Analyzer for that matter. In PHP I use the bin2hex() function to get the HEX equivilent of the picture, I'm sure everyone knows that when you convert a Binary file to HEX, the file size is doubled. When I try to insert the HEX file into my table with a query like:
INSERT INTO PicTable (fileType, fileData) VALUES ('jpg', 0x47494638396164014100f70000000000ffffff2f2f2fe800020c0c0ce....)

MsSQL will store EXACTLY HALF of the file. The byte count of the stored HEX data and original Binary data is exactly the same, so when I try to extract the file and display it, in a browser window for instance, I can see exactly half of the image. I have tried everything I can think of to fix this, but I am at a loss. Does anyone know of anything that would cause this strange behavior. I have no problems at all doing this with MySQL's BLOB data type. Thanks in advance for any help.

View Replies !   View Related
Importing Data From Btrieve To Mssql Server
simon titi writes "I have a huge data in btrieve. the data includes images.
now I am migrating to sqlserver. Can I used the data in btrieve?
How can I import it to mssql?

Thank you!

Simon.t."

View Replies !   View Related
Insert Data To MSSQL Within Trigger From Oracle
I created a link from Oracle to SQL server.
There is a trigger with insert statment in Oracle side.
I got error message as follow when trigger is invoked:

SQL> insert into test1 values ('zerbra','brazi');
insert into test1 values ('zerbra','brazi')
*
ERROR at line 1:
ORA-02047: cannot join the distributed transaction in progress
ORA-06512: at "GGWEB.TRIGGER2", line 4
ORA-04088: error during execution of trigger 'GGWEB.TRIGGER2'

my simple trigger is as follows:
create or replace
TRIGGER TESTRI
AFTER INSERT ON TEST1
FOR EACH ROW
BEGIN
insert into test1@sqlserver (city,state) values ('what','nine');


END;

View Replies !   View Related
Problems Converting Data From MS Access To MSSQL
Hi All,

I am converting an old MS Access database to MSSQL. While I do get some data into MSSQL, some weird things are happening that are beyond my capabilities.

The database I am trying to convert exists of several tables. The first few are converted perfectly, but one of the last is giving difficulties. What I do is:
            SET @SQLQuery = 'INSERT INTO TableName ( ' +
                                                   '[field1], ' +
                                                   '[field2], ' +
                                                   '[field3], ' +
                                                   '[field4], ' +
                                                   '[Date], ' +
                                                   '[field6]) ' +
                            'SELECT  ' +
                                                   '[field1], ' +
                                                   '[field2], ' +
                                                   '[field3], ' +
                                                   '[field4], ' +
                                                   '[Date], ' +
                                                   '[field6]) ' +
                                    ' FROM '+ RTRIM(@LinkedServerName) +'...TableName'

            EXEC (@SQLQuery)

I do the same for each table, all with their own TableNames and field names. 80% of the tables have a date field, which is of type DBTYPE_DBTIMESTAMP in Access and datetime in MSSQL.

As mentioned: the first tables are converted perfectly, however one fails with error message:
Msg 8114, Level 16, State 8, Server XXXXXX, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

If I leave the date field out of the query for that table, it all works (obviously, with an empty date column). The weird thing is that most of the other tables have a date column as well, they are the same data types as in this column and the conversion query is very similar as well.

I have Googled on the full error message and on DBTYPE_DBTIMESTAMP alone, but all results I get seem different to what I have. I have tried using convert to put the date in the correct format (format in the date tables is DD-MM-YYYY), but this doesn't help. Would have been funny if it had helped, as all other tables use the same date format, and according to the specs, DBTYPE_DBTIMESTAMP should be automatically converted to datetime anyway (which in all other tables works)...

Basically, what I am saying is: I have 8 extremely similar tables, which I all try to convert using the exact same method, 7 tables succeed and one fails...

Thanks in advance for your help!
Freddy

View Replies !   View Related
Export MSsql Data To Excel Files
Hi

i have a MSsql file wich i want to export information from.
Every post in the database should be exportet to a own single file with all the information about the post.
So from a single question to the database i would like to have a lot of files named by a speciel filed i the post.
I also want to be able to design what the excel file should look like.

How can i do this?

Greatful for help.

/Mitmit

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved