Floating Point Numbers in BCP File

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

float numbers

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 float
declare @.b int

set @.a=0.9616458
set @.b=60

print @.a*@.b
----------
will show :57.6987

How do I do to make MSSQL to show me the value whothout rounding it?

Thanks!Use SELECT:

select @.a*@.b

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<homa@.havet.se> wrote in message
news:1144073554.334910.187210@.g10g2000cwb.googlegr oups.com...
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 float
declare @.b int

set @.a=0.9616458
set @.b=60

print @.a*@.b
----------
will show :57.6987

How do I do to make MSSQL to show me the value whothout rounding it?

Thanks!|||Hi! Itried your code and it works fine in the example I gave but not
when I implement it in my code:

declare @.x float(53)
declare @.grades int
declare @.minutes int
declare @.seconds float(53)
declare @.minwithrest float(53)
declare @.leftover float(53)

select @.x = 57.66602743
select @.grades = floor(@.x)
select @.leftover = @.x-@.grades
select @.minwithrest = (@.leftover * 60)
select @.minutes = floor(@.minwithrest)
select @.seconds = (@.minwithrest - @.minutes) * 60

print @.grades
print @.minutes
print @.seconds

gives me 57.6987 as @.seconds while it should be 57,698748

thanks!|||sorry. I meant that it gives me 57.698747999999114 and It should be
57,698748 .
(The print is replaced by select)|||
I ran the 2nd code you've posted and I get the 57.6987 result|||Again, use SELECT:

select @.seconds

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<homa@.havet.se> wrote in message
news:1144077300.519079.316740@.t31g2000cwb.googlegr oups.com...
Hi! Itried your code and it works fine in the example I gave but not
when I implement it in my code:

declare @.x float(53)
declare @.grades int
declare @.minutes int
declare @.seconds float(53)
declare @.minwithrest float(53)
declare @.leftover float(53)

select @.x = 57.66602743
select @.grades = floor(@.x)
select @.leftover = @.x-@.grades
select @.minwithrest = (@.leftover * 60)
select @.minutes = floor(@.minwithrest)
select @.seconds = (@.minwithrest - @.minutes) * 60

print @.grades
print @.minutes
print @.seconds

gives me 57.6987 as @.seconds while it should be 57,698748

thanks!|||At this point, I'm confused about what result you are looking for. Use
convert(decimal(X,Y),@.seconds) to fine-tune the result you need. X can
be as large as 38 places and Y is your tunable decimal size.

HTH,
Gabe|||Try:

select
cast (@.seconds as numeric (8, 6))

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<homa@.havet.se> wrote in message
news:1144077414.697728.270000@.v46g2000cwv.googlegr oups.com...
sorry. I meant that it gives me 57.698747999999114 and It should be
57,698748 .
(The print is replaced by select)|||I get:

57.698748

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"gabe101" <gabe101@.gmail.com> wrote in message
news:1144078109.143813.199680@.z34g2000cwc.googlegr oups.com...

I ran the 2nd code you've posted and I get the 57.6987 result|||Tom,

Why do you suppose that is? Is it our versions? I'm on 8.00.760...|||I'm on 8.00.818, which is the post-SP3a security hotfix:

http://support.microsoft.com/kb/821277

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"gabe101" <gabe101@.gmail.com> wrote in message
news:1144079629.602630.102500@.u72g2000cwu.googlegr oups.com...
Tom,

Why do you suppose that is? Is it our versions? I'm on 8.00.760...|||Something else that is puzzling me about all of this:

Put 'select @.leftover' at the end of this code.

I get:

0.66602742999999975 as the @.leftover value. Shouldn't that be
0.66602743......why did Query Analyzer do this?

If floor(@.x) = 57
and
@.Leftover = 57.66602743 - @.x then the result should be 0.66602743|||I get:

0.66602742999999975

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
"gabe101" <gabe101@.gmail.com> wrote in message
news:1144080712.526308.302260@.e56g2000cwe.googlegr oups.com...
Something else that is puzzling me about all of this:

Put 'select @.leftover' at the end of this code.

I get:

0.66602742999999975 as the @.leftover value. Shouldn't that be
0.66602743......why did Query Analyzer do this?

If floor(@.x) = 57
and
@.Leftover = 57.66602743 - @.x then the result should be 0.66602743|||I can't use the convert since I don't know how many decimals to round
the result to and for this function that I'm writing the result must be
exact. This is a GPS conversion formula and if I round things always
with fir examole 6 decimals then the x,y coordinats will be wrong for
the GPS system. In my example above I need the result to be exactley
57,698748 and not 57.6987 or 57.698747999999114 ... Since I don't
know the amount of decimlas returned I cant use the convert..|||> Since I don't
> know the amount of decimlas returned I cant use the convert..

This leads to another question...what about 1 divided by 3. What will
you do with this result?

Gabe|||very interesting..

I don't know...|||(homa@.havet.se) writes:
> I can't use the convert since I don't know how many decimals to round
> the result to and for this function that I'm writing the result must be
> exact.

In such case you should not use float. Float gives you approxamite numbers.
Float values consists of a 53-bit number with a mantissa. This permits
for a broad range of value, to the price of approxamite precision.

The given example:

declare @.a float
declare @.b int

set @.a=0.9616458
set @.b=60

print @.a*@.b
select @.a*@.b

Gives you 57.6987 for the print, because there is an implicit conversion
to string in SQL Server.

The SELECT statement returns a binary float value to the client, so it
up to the client how it is presented. In Query Analyzer I get
57.698748000000002, whereas in Management Studio that ships with
SQL 2005, I get 57,698748. ISQL, the command-line tool that uses
DB-Library to connect returns 57.698748.

> This is a GPS conversion formula and if I round things always
> with fir examole 6 decimals then the x,y coordinats will be wrong for
> the GPS system. In my example above I need the result to be exactley
> 57,698748 and not 57.6987 or 57.698747999999114 ... Since I don't
> know the amount of decimlas returned I cant use the convert..

If you don't know the decimals of your result before hand, you will
have to convert the result to a string, and then try to guess how many
decimals you really have.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

firewalls? port numbers? ancient curses cast upon my servers?

Hello All,
Hopefully someone has come across this...

I have the client side of my application installed in the US,
with the application and the database servers running in london.

The program language is C#, all built in .net, with the .net installer.
When the user in the US runs the program and it gets to the
import progress part (background processing occurring on London servers),
they are getting no feedback as to the progress of the import.
ie, nothing is being sent to the US from the london server.
But everything runs smooth the other way, ie, tables are written to
the sql db's in london etc... And I get data sending and receiving
here in london when i run the client app on my machine and send to
the servers here, so its some problem with the connection with the US....
firewalls? port numbers? ancient curses cast upon my servers?

Cheers mike

Is this using SQL Server Integration Services (the SQL Server 2005 replacement for DTS)?
(In any case, if the "import" part isn't giving feedback, you probably should investigate the "import" part, to see what it does, and revise it to give feedback -- it may be hard for anyone here to know what this "import" part is -- if you suspect a connection problem, network sniffing, or even simple use of sysinternals tcpview may be helpful.)

firewalls? port numbers? ancient curses cast upon my servers?

Hello All
I have the client side of my application installed in the US, with the
application and the database servers running in london.
The program language is C#, all built in .net, with the .net installer. When
the user in the US runs the program and it gets to the import progress part
(background processing occurring on London servers), they are getting no
feedback as to the progress of the import. ie, nothing is being sent to the
US from the london server. But everything runs smooth the other way, ie,
tables are written to the sql db's in london etc... And I get data sending
and receiving here in london when i run the client app on my machine and send
to the servers here, so its some problem with the connection with the US...
firewalls' port numbers' ancient curses cast upon my servers' anyone come
across anything like this?
Cheers mikeHave you tested access with Query Analyzer to see if you can connect, access
the database from both places and perform the import tasks? It's unclear
from your post whether you're just having problems with the import task or
if it's even connecting.
joe.
"blomm via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54B0800E732D8@.SQLMonster.com...
> Hello All
> I have the client side of my application installed in the US, with the
> application and the database servers running in london.
> The program language is C#, all built in .net, with the .net installer.
> When
> the user in the US runs the program and it gets to the import progress
> part
> (background processing occurring on London servers), they are getting no
> feedback as to the progress of the import. ie, nothing is being sent to
> the
> US from the london server. But everything runs smooth the other way, ie,
> tables are written to the sql db's in london etc... And I get data
> sending
> and receiving here in london when i run the client app on my machine and
> send
> to the servers here, so its some problem with the connection with the
> US...
> firewalls' port numbers' ancient curses cast upon my servers' anyone
> come
> across anything like this?
> Cheers mike|||"It's unclear
from your post whether you're just having problems with the import task or
if it's even connecting."
Sorry, i will try and clarify:
it is connecting, theres problems with the callback functionality i think, if
that means anything to you.
okay, thanks for your pointers, i'm off to follow them up.
m