I'm writing a script with an insert statment to insert records from one
database to another but I have to change the type first. How can I find out
more about what can be causing this or is it something wrong with my syntax?
The field [patient_weight] is a varchar(256) stripped of all spaces left and
right.
Thanks, Alpha
cast([patient_weight] as decimal(10,2))
What result do you get here?
SELECT COUNT(*) FROM [source table name]
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> I'm writing a script with an insert statment to insert records from one
> database to another but I have to change the type first. How can I find
out
> more about what can be causing this or is it something wrong with my
syntax?
> The field [patient_weight] is a varchar(256) stripped of all spaces left
and
> right.
> Thanks, Alpha
> cast([patient_weight] as decimal(10,2))
>
|||I got 4. I looked at the table and they're all '0'(zero) except these 4.
"Aaron [SQL Server MVP]" wrote:
> What result do you get here?
> SELECT COUNT(*) FROM [source table name]
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> out
> syntax?
> and
>
>
|||Well, you need to fix those 4 rows.
SELECT * FROM Source_Table_Name
WHERE ISNUMERIC(patient_weight) = 0
If they look like 0's, maybe they're o's, or maybe they're zero's with
non-printable characters attached (e.g. carriage return).
UPDATE Source_Table_Name SET patient_weight = '0'
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...[vbcol=seagreen]
> I got 4. I looked at the table and they're all '0'(zero) except these 4.
> "Aaron [SQL Server MVP]" wrote:
one[vbcol=seagreen]
find[vbcol=seagreen]
left[vbcol=seagreen]
|||Thank you that works but I don't understadn what happend here. So these 4
rows has a '0'(zero) char in it with CR. So how does this query able to
select it? ISNUMERIC cast it to 0 (zero in numerica) right? I don't
understand.
Thanks,
Alpha
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
"Aaron [SQL Server MVP]" wrote:
> Well, you need to fix those 4 rows.
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
> If they look like 0's, maybe they're o's, or maybe they're zero's with
> non-printable characters attached (e.g. carriage return).
> UPDATE Source_Table_Name SET patient_weight = '0'
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> one
> find
> left
>
>
|||> Thank you that works but I don't understadn what happend here. So these 4
> rows has a '0'(zero) char in it with CR. So how does this query able to
> select it? ISNUMERIC cast it to 0 (zero in numerica) right?
No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
it returns 1, otherwise it returns 0. Something about the way you decided
to store numeric data in a character data type led to problems in these 4
rows (which could have been predicted at the outset, and prevented by
choosing the correct data type).
* ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
|||Oh, got it. Thank you very much and have a nice day.
Alpha
"Aaron [SQL Server MVP]" wrote:
> No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
> it returns 1, otherwise it returns 0. Something about the way you decided
> to store numeric data in a character data type led to problems in these 4
> rows (which could have been predicted at the outset, and prevented by
> choosing the correct data type).
> * ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
>
>
Showing posts with label varchar. Show all posts
Showing posts with label varchar. Show all posts
Monday, March 12, 2012
Error when cast Varchar to decimal
I'm writing a script with an insert statment to insert records from one
database to another but I have to change the type first. How can I find out
more about what can be causing this or is it something wrong with my syntax?
The field [patient_weight] is a varchar(256) stripped of all spaces left
and
right.
Thanks, Alpha
cast([patient_weight] as decimal(10,2))What result do you get here?
SELECT COUNT(*) FROM [source table name]
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> I'm writing a script with an insert statment to insert records from one
> database to another but I have to change the type first. How can I find
out
> more about what can be causing this or is it something wrong with my
syntax?
> The field [patient_weight] is a varchar(256) stripped of all spaces left[/vbco
l]
and[vbcol=seagreen]
> right.
> Thanks, Alpha
> cast([patient_weight] as decimal(10,2))
>|||I got 4. I looked at the table and they're all '0'(zero) except these 4.
"Aaron [SQL Server MVP]" wrote:
> What result do you get here?
> SELECT COUNT(*) FROM [source table name]
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> out
> syntax?
> and
>
>|||Well, you need to fix those 4 rows.
SELECT * FROM Source_Table_Name
WHERE ISNUMERIC(patient_weight) = 0
If they look like 0's, maybe they're o's, or maybe they're zero's with
non-printable characters attached (e.g. carriage return).
UPDATE Source_Table_Name SET patient_weight = '0'
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...[vbcol=seagreen]
> I got 4. I looked at the table and they're all '0'(zero) except these 4.
> "Aaron [SQL Server MVP]" wrote:
>
one[vbcol=seagreen]
find[vbcol=seagreen]
left[vbcol=seagreen]|||Thank you that works but I don't understadn what happend here. So these 4
rows has a '0'(zero) char in it with CR. So how does this query able to
select it? ISNUMERIC cast it to 0 (zero in numerica) right? I don't
understand.
Thanks,
Alpha
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
"Aaron [SQL Server MVP]" wrote:
> Well, you need to fix those 4 rows.
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
> If they look like 0's, maybe they're o's, or maybe they're zero's with
> non-printable characters attached (e.g. carriage return).
> UPDATE Source_Table_Name SET patient_weight = '0'
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> one
> find
> left
>
>|||> Thank you that works but I don't understadn what happend here. So these 4
> rows has a '0'(zero) char in it with CR. So how does this query able to
> select it? ISNUMERIC cast it to 0 (zero in numerica) right?
No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
it returns 1, otherwise it returns 0. Something about the way you decided
to store numeric data in a character data type led to problems in these 4
rows (which could have been predicted at the outset, and prevented by
choosing the correct data type).
* ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390|||Oh, got it. Thank you very much and have a nice day.
Alpha
"Aaron [SQL Server MVP]" wrote:
> No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric
*,
> it returns 1, otherwise it returns 0. Something about the way you decided
> to store numeric data in a character data type led to problems in these 4
> rows (which could have been predicted at the outset, and prevented by
> choosing the correct data type).
> * ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
>
>
database to another but I have to change the type first. How can I find out
more about what can be causing this or is it something wrong with my syntax?
The field [patient_weight] is a varchar(256) stripped of all spaces left
and
right.
Thanks, Alpha
cast([patient_weight] as decimal(10,2))What result do you get here?
SELECT COUNT(*) FROM [source table name]
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> I'm writing a script with an insert statment to insert records from one
> database to another but I have to change the type first. How can I find
out
> more about what can be causing this or is it something wrong with my
syntax?
> The field [patient_weight] is a varchar(256) stripped of all spaces left[/vbco
l]
and[vbcol=seagreen]
> right.
> Thanks, Alpha
> cast([patient_weight] as decimal(10,2))
>|||I got 4. I looked at the table and they're all '0'(zero) except these 4.
"Aaron [SQL Server MVP]" wrote:
> What result do you get here?
> SELECT COUNT(*) FROM [source table name]
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> out
> syntax?
> and
>
>|||Well, you need to fix those 4 rows.
SELECT * FROM Source_Table_Name
WHERE ISNUMERIC(patient_weight) = 0
If they look like 0's, maybe they're o's, or maybe they're zero's with
non-printable characters attached (e.g. carriage return).
UPDATE Source_Table_Name SET patient_weight = '0'
WHERE ISNUMERIC(patient_weight) = 0
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...[vbcol=seagreen]
> I got 4. I looked at the table and they're all '0'(zero) except these 4.
> "Aaron [SQL Server MVP]" wrote:
>
one[vbcol=seagreen]
find[vbcol=seagreen]
left[vbcol=seagreen]|||Thank you that works but I don't understadn what happend here. So these 4
rows has a '0'(zero) char in it with CR. So how does this query able to
select it? ISNUMERIC cast it to 0 (zero in numerica) right? I don't
understand.
Thanks,
Alpha
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
"Aaron [SQL Server MVP]" wrote:
> Well, you need to fix those 4 rows.
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
> If they look like 0's, maybe they're o's, or maybe they're zero's with
> non-printable characters attached (e.g. carriage return).
> UPDATE Source_Table_Name SET patient_weight = '0'
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> one
> find
> left
>
>|||> Thank you that works but I don't understadn what happend here. So these 4
> rows has a '0'(zero) char in it with CR. So how does this query able to
> select it? ISNUMERIC cast it to 0 (zero in numerica) right?
No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
it returns 1, otherwise it returns 0. Something about the way you decided
to store numeric data in a character data type led to problems in these 4
rows (which could have been predicted at the outset, and prevented by
choosing the correct data type).
* ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390|||Oh, got it. Thank you very much and have a nice day.
Alpha
"Aaron [SQL Server MVP]" wrote:
> No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric
*,
> it returns 1, otherwise it returns 0. Something about the way you decided
> to store numeric data in a character data type led to problems in these 4
> rows (which could have been predicted at the outset, and prevented by
> choosing the correct data type).
> * ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
>
>
Error when cast Varchar to decimal
I'm writing a script with an insert statment to insert records from one
database to another but I have to change the type first. How can I find out
more about what can be causing this or is it something wrong with my syntax?
The field [patient_weight] is a varchar(256) stripped of all spaces left and
right.
Thanks, Alpha
cast([patient_weight] as decimal(10,2))What result do you get here?
SELECT COUNT(*) FROM [source table name]
WHERE ISNUMERIC(patient_weight) = 0
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> I'm writing a script with an insert statment to insert records from one
> database to another but I have to change the type first. How can I find
out
> more about what can be causing this or is it something wrong with my
syntax?
> The field [patient_weight] is a varchar(256) stripped of all spaces left
and
> right.
> Thanks, Alpha
> cast([patient_weight] as decimal(10,2))
>|||I got 4. I looked at the table and they're all '0'(zero) except these 4.
"Aaron [SQL Server MVP]" wrote:
> What result do you get here?
> SELECT COUNT(*) FROM [source table name]
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> > I'm writing a script with an insert statment to insert records from one
> > database to another but I have to change the type first. How can I find
> out
> > more about what can be causing this or is it something wrong with my
> syntax?
> > The field [patient_weight] is a varchar(256) stripped of all spaces left
> and
> > right.
> > Thanks, Alpha
> >
> > cast([patient_weight] as decimal(10,2))
> >
>
>|||Well, you need to fix those 4 rows.
SELECT * FROM Source_Table_Name
WHERE ISNUMERIC(patient_weight) = 0
If they look like 0's, maybe they're o's, or maybe they're zero's with
non-printable characters attached (e.g. carriage return).
UPDATE Source_Table_Name SET patient_weight = '0'
WHERE ISNUMERIC(patient_weight) = 0
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> I got 4. I looked at the table and they're all '0'(zero) except these 4.
> "Aaron [SQL Server MVP]" wrote:
> > What result do you get here?
> >
> > SELECT COUNT(*) FROM [source table name]
> > WHERE ISNUMERIC(patient_weight) = 0
> >
> > --
> > Please post DDL, sample data and desired results.
> > See http://www.aspfaq.com/5006 for info.
> >
> >
> >
> >
> > "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> > news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> > > I'm writing a script with an insert statment to insert records from
one
> > > database to another but I have to change the type first. How can I
find
> > out
> > > more about what can be causing this or is it something wrong with my
> > syntax?
> > > The field [patient_weight] is a varchar(256) stripped of all spaces
left
> > and
> > > right.
> > > Thanks, Alpha
> > >
> > > cast([patient_weight] as decimal(10,2))
> > >
> >
> >
> >|||Thank you that works but I don't understadn what happend here. So these 4
rows has a '0'(zero) char in it with CR. So how does this query able to
select it? ISNUMERIC cast it to 0 (zero in numerica) right? I don't
understand.
Thanks,
Alpha
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
"Aaron [SQL Server MVP]" wrote:
> Well, you need to fix those 4 rows.
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
> If they look like 0's, maybe they're o's, or maybe they're zero's with
> non-printable characters attached (e.g. carriage return).
> UPDATE Source_Table_Name SET patient_weight = '0'
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> > I got 4. I looked at the table and they're all '0'(zero) except these 4.
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> > > What result do you get here?
> > >
> > > SELECT COUNT(*) FROM [source table name]
> > > WHERE ISNUMERIC(patient_weight) = 0
> > >
> > > --
> > > Please post DDL, sample data and desired results.
> > > See http://www.aspfaq.com/5006 for info.
> > >
> > >
> > >
> > >
> > > "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> > > news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> > > > I'm writing a script with an insert statment to insert records from
> one
> > > > database to another but I have to change the type first. How can I
> find
> > > out
> > > > more about what can be causing this or is it something wrong with my
> > > syntax?
> > > > The field [patient_weight] is a varchar(256) stripped of all spaces
> left
> > > and
> > > > right.
> > > > Thanks, Alpha
> > > >
> > > > cast([patient_weight] as decimal(10,2))
> > > >
> > >
> > >
> > >
>
>|||> Thank you that works but I don't understadn what happend here. So these 4
> rows has a '0'(zero) char in it with CR. So how does this query able to
> select it? ISNUMERIC cast it to 0 (zero in numerica) right?
No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
it returns 1, otherwise it returns 0. Something about the way you decided
to store numeric data in a character data type led to problems in these 4
rows (which could have been predicted at the outset, and prevented by
choosing the correct data type).
* ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390|||Oh, got it. Thank you very much and have a nice day.
Alpha
"Aaron [SQL Server MVP]" wrote:
> > Thank you that works but I don't understadn what happend here. So these 4
> > rows has a '0'(zero) char in it with CR. So how does this query able to
> > select it? ISNUMERIC cast it to 0 (zero in numerica) right?
> No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
> it returns 1, otherwise it returns 0. Something about the way you decided
> to store numeric data in a character data type led to problems in these 4
> rows (which could have been predicted at the outset, and prevented by
> choosing the correct data type).
> * ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
>
>
database to another but I have to change the type first. How can I find out
more about what can be causing this or is it something wrong with my syntax?
The field [patient_weight] is a varchar(256) stripped of all spaces left and
right.
Thanks, Alpha
cast([patient_weight] as decimal(10,2))What result do you get here?
SELECT COUNT(*) FROM [source table name]
WHERE ISNUMERIC(patient_weight) = 0
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> I'm writing a script with an insert statment to insert records from one
> database to another but I have to change the type first. How can I find
out
> more about what can be causing this or is it something wrong with my
syntax?
> The field [patient_weight] is a varchar(256) stripped of all spaces left
and
> right.
> Thanks, Alpha
> cast([patient_weight] as decimal(10,2))
>|||I got 4. I looked at the table and they're all '0'(zero) except these 4.
"Aaron [SQL Server MVP]" wrote:
> What result do you get here?
> SELECT COUNT(*) FROM [source table name]
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> > I'm writing a script with an insert statment to insert records from one
> > database to another but I have to change the type first. How can I find
> out
> > more about what can be causing this or is it something wrong with my
> syntax?
> > The field [patient_weight] is a varchar(256) stripped of all spaces left
> and
> > right.
> > Thanks, Alpha
> >
> > cast([patient_weight] as decimal(10,2))
> >
>
>|||Well, you need to fix those 4 rows.
SELECT * FROM Source_Table_Name
WHERE ISNUMERIC(patient_weight) = 0
If they look like 0's, maybe they're o's, or maybe they're zero's with
non-printable characters attached (e.g. carriage return).
UPDATE Source_Table_Name SET patient_weight = '0'
WHERE ISNUMERIC(patient_weight) = 0
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> I got 4. I looked at the table and they're all '0'(zero) except these 4.
> "Aaron [SQL Server MVP]" wrote:
> > What result do you get here?
> >
> > SELECT COUNT(*) FROM [source table name]
> > WHERE ISNUMERIC(patient_weight) = 0
> >
> > --
> > Please post DDL, sample data and desired results.
> > See http://www.aspfaq.com/5006 for info.
> >
> >
> >
> >
> > "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> > news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> > > I'm writing a script with an insert statment to insert records from
one
> > > database to another but I have to change the type first. How can I
find
> > out
> > > more about what can be causing this or is it something wrong with my
> > syntax?
> > > The field [patient_weight] is a varchar(256) stripped of all spaces
left
> > and
> > > right.
> > > Thanks, Alpha
> > >
> > > cast([patient_weight] as decimal(10,2))
> > >
> >
> >
> >|||Thank you that works but I don't understadn what happend here. So these 4
rows has a '0'(zero) char in it with CR. So how does this query able to
select it? ISNUMERIC cast it to 0 (zero in numerica) right? I don't
understand.
Thanks,
Alpha
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
"Aaron [SQL Server MVP]" wrote:
> Well, you need to fix those 4 rows.
> SELECT * FROM Source_Table_Name
> WHERE ISNUMERIC(patient_weight) = 0
> If they look like 0's, maybe they're o's, or maybe they're zero's with
> non-printable characters attached (e.g. carriage return).
> UPDATE Source_Table_Name SET patient_weight = '0'
> WHERE ISNUMERIC(patient_weight) = 0
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:5B264C8D-9F07-4A20-8066-93256AD0D975@.microsoft.com...
> > I got 4. I looked at the table and they're all '0'(zero) except these 4.
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> > > What result do you get here?
> > >
> > > SELECT COUNT(*) FROM [source table name]
> > > WHERE ISNUMERIC(patient_weight) = 0
> > >
> > > --
> > > Please post DDL, sample data and desired results.
> > > See http://www.aspfaq.com/5006 for info.
> > >
> > >
> > >
> > >
> > > "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> > > news:D0914609-50C8-4480-A15D-302752BECA86@.microsoft.com...
> > > > I'm writing a script with an insert statment to insert records from
> one
> > > > database to another but I have to change the type first. How can I
> find
> > > out
> > > > more about what can be causing this or is it something wrong with my
> > > syntax?
> > > > The field [patient_weight] is a varchar(256) stripped of all spaces
> left
> > > and
> > > > right.
> > > > Thanks, Alpha
> > > >
> > > > cast([patient_weight] as decimal(10,2))
> > > >
> > >
> > >
> > >
>
>|||> Thank you that works but I don't understadn what happend here. So these 4
> rows has a '0'(zero) char in it with CR. So how does this query able to
> select it? ISNUMERIC cast it to 0 (zero in numerica) right?
No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
it returns 1, otherwise it returns 0. Something about the way you decided
to store numeric data in a character data type led to problems in these 4
rows (which could have been predicted at the outset, and prevented by
choosing the correct data type).
* ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390|||Oh, got it. Thank you very much and have a nice day.
Alpha
"Aaron [SQL Server MVP]" wrote:
> > Thank you that works but I don't understadn what happend here. So these 4
> > rows has a '0'(zero) char in it with CR. So how does this query able to
> > select it? ISNUMERIC cast it to 0 (zero in numerica) right?
> No, ISNUMERIC(expression) returns either 1 or 0. If the number is numeric*,
> it returns 1, otherwise it returns 0. Something about the way you decided
> to store numeric data in a character data type led to problems in these 4
> rows (which could have been predicted at the outset, and prevented by
> choosing the correct data type).
> * ISNUMERIC() is not perfect. See http://www.aspfaq.com/2390
>
>
Sunday, February 26, 2012
ERROR USING BULK INSERT
Hi
I'm executing
declare @.ARCHIVOidx varchar(300)
set @.ARCHIVOidx = (Select top 1 MyFile from task)
declare @.sentencia varchar(300)
set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
exec (@.sentencia)
and the SQL says:
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 51
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
The event viewer says
18052 : Error: 3624, Severity: 20, State: 1.
THE machine has (SQL DEV):
WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
SQL WITH SP3
What can I do?
Thanks a lot> exec (@.sentencia)
Change this to PRINT @.sentencia and see if what it yields makes sense, also
try executing this manually.|||ok
The output is:
bulk insert a from 'C:\input\bankAA.idx' with (formatfile
='c:\input\bcpfmt.txt',batchsize=100)
"Aaron Bertrand [SQL Server MVP]" wrote:
> Change this to PRINT @.sentencia and see if what it yields makes sense, als
o
> try executing this manually.
>
>|||The Sintax is OK.
The server inserts 600.000 records and generate error, however
using a smaller file it does not generate error. The original file has 2
million registries, the second 600,000 registries (with errors).
The Tsql generates error with the greatest archives
"Macisu" wrote:
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Okay, now, what happened when you ran this command manually?
"Macisu" <Macisu@.discussions.microsoft.com> wrote in message
news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> The Sintax is OK.
I understand that. Which is why I suggested RUNNING the code, not just
looking at it.
> The server inserts 600.000 records and generate error, however
> using a smaller file it does not generate error.
So have you considered using the MAXERRORS parameter? Or cleaning the file
before builk inserting it? Obviously the one with errors in it is failing
because there are rows with errors.
By "smaller file" do you really mean "a file that does not happen to include
any rows that cause an error"?|||The command ran ok, the command generates error 6 mins after executing itsel
f,
when the table has 600.000 records aprox.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Okay, now, what happened when you ran this command manually?
>
>
> "Macisu" <Macisu@.discussions.microsoft.com> wrote in message
> news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
>
>|||> The command ran ok, the command generates error
Uh, could you be more specific?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Uh, could you be more specific?
>
>
the window output says
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 53
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
Query batch completed with errors|||This may be a bug, but I have a couple of questions:
Can you post the format file?
Can you try MAXERRORS = 1 and also MAXERRORS = 100, and see if the
problem moves to a different place than around 600000 rows? (By
default, MAXERRORS is 10.)
Can you watch the computer's memory and virtual memory use to see if it
is going up and up to the max before the crash?
Steve Kass
Drew University
Macisu wrote:
>Hi
>I'm executing
>declare @.ARCHIVOidx varchar(300)
>set @.ARCHIVOidx = (Select top 1 MyFile from task)
>declare @.sentencia varchar(300)
>set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
>(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
>exec (@.sentencia)
>and the SQL says:
>Server: Msg 3624, Level 20, State 1, Line 1
>Location: record.cpp:825
>Expression: newColLen >= WideTextPointer::OVERHEAD
>SPID: 51
>Process ID: 3420
>ODBC: Msg 0, Level 16, State 1
>Communication link failure
>Connection Broken
>
>The event viewer says
>18052 : Error: 3624, Severity: 20, State: 1.
>THE machine has (SQL DEV):
>WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
>SQL WITH SP3
>What can I do?
>Thanks a lot
>
>
>
>
I'm executing
declare @.ARCHIVOidx varchar(300)
set @.ARCHIVOidx = (Select top 1 MyFile from task)
declare @.sentencia varchar(300)
set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
exec (@.sentencia)
and the SQL says:
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 51
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
The event viewer says
18052 : Error: 3624, Severity: 20, State: 1.
THE machine has (SQL DEV):
WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
SQL WITH SP3
What can I do?
Thanks a lot> exec (@.sentencia)
Change this to PRINT @.sentencia and see if what it yields makes sense, also
try executing this manually.|||ok
The output is:
bulk insert a from 'C:\input\bankAA.idx' with (formatfile
='c:\input\bcpfmt.txt',batchsize=100)
"Aaron Bertrand [SQL Server MVP]" wrote:
> Change this to PRINT @.sentencia and see if what it yields makes sense, als
o
> try executing this manually.
>
>|||The Sintax is OK.
The server inserts 600.000 records and generate error, however
using a smaller file it does not generate error. The original file has 2
million registries, the second 600,000 registries (with errors).
The Tsql generates error with the greatest archives
"Macisu" wrote:
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||Okay, now, what happened when you ran this command manually?
"Macisu" <Macisu@.discussions.microsoft.com> wrote in message
news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
> ok
> The output is:
> bulk insert a from 'C:\input\bankAA.idx' with (formatfile
> ='c:\input\bcpfmt.txt',batchsize=100)
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||> The Sintax is OK.
I understand that. Which is why I suggested RUNNING the code, not just
looking at it.
> The server inserts 600.000 records and generate error, however
> using a smaller file it does not generate error.
So have you considered using the MAXERRORS parameter? Or cleaning the file
before builk inserting it? Obviously the one with errors in it is failing
because there are rows with errors.
By "smaller file" do you really mean "a file that does not happen to include
any rows that cause an error"?|||The command ran ok, the command generates error 6 mins after executing itsel
f,
when the table has 600.000 records aprox.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Okay, now, what happened when you ran this command manually?
>
>
> "Macisu" <Macisu@.discussions.microsoft.com> wrote in message
> news:98CA0497-3E9A-4407-8628-9D51BBBE1434@.microsoft.com...
>
>|||> The command ran ok, the command generates error
Uh, could you be more specific?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Uh, could you be more specific?
>
>
the window output says
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
(100 row(s) affected)
Server: Msg 3624, Level 20, State 1, Line 1
Location: record.cpp:825
Expression: newColLen >= WideTextPointer::OVERHEAD
SPID: 53
Process ID: 3420
ODBC: Msg 0, Level 16, State 1
Communication link failure
Connection Broken
Query batch completed with errors|||This may be a bug, but I have a couple of questions:
Can you post the format file?
Can you try MAXERRORS = 1 and also MAXERRORS = 100, and see if the
problem moves to a different place than around 600000 rows? (By
default, MAXERRORS is 10.)
Can you watch the computer's memory and virtual memory use to see if it
is going up and up to the max before the crash?
Steve Kass
Drew University
Macisu wrote:
>Hi
>I'm executing
>declare @.ARCHIVOidx varchar(300)
>set @.ARCHIVOidx = (Select top 1 MyFile from task)
>declare @.sentencia varchar(300)
>set @.sentencia = 'bulk insert a from ''' + @.ARCHIVOidx+'''' + ' with
>(formatfile ='''+'c:\input\bcpfmt.txt' + ''',batchsize=100) '
>exec (@.sentencia)
>and the SQL says:
>Server: Msg 3624, Level 20, State 1, Line 1
>Location: record.cpp:825
>Expression: newColLen >= WideTextPointer::OVERHEAD
>SPID: 51
>Process ID: 3420
>ODBC: Msg 0, Level 16, State 1
>Communication link failure
>Connection Broken
>
>The event viewer says
>18052 : Error: 3624, Severity: 20, State: 1.
>THE machine has (SQL DEV):
>WIN XP SP1 (Pentium 4), with 40 Gb HD (free 10 Gb), Memory 512 Mb
>SQL WITH SP3
>What can I do?
>Thanks a lot
>
>
>
>
Friday, February 17, 2012
Error severity
Hi,
I want to write this SQL script
--
create table table1(
ID_TABLE1 varchar(15) not null
constraint PK_TABLE1 primary key clustered
(ID_TABLE1)
)
IF @.@.ERROR <> 0
BEGIN
print 'Error create table1'
END
--
The problem is when there is a 'create error', the code
print 'Error ...' is not executed because the error's
severity is too high and the script stops.
Is it possible to trap the default error ? When there is a
create error, I want that's the @.@.error is initialized and
the test is executed.
Thanks in advance for your help
Antoine (F-Paris)
MSSQL 7 & MSSQL 2000If the error is severe enough it will stop the batch and you can not trap it
within the batch. You would have to check for the error where you call the
batch. If your using something like ADO, sql server will report an error
that you can see in the errors collection.
Andrew J. Kelly
SQL Server MVP
"Antoine C." <acazedev@.libertysurfXXX.fr> wrote in message
news:11da01c3df60$d4f02bf0$a401280a@.phx.gbl...
Thank you for the quick answer Andrew.
Bye
I want to write this SQL script
--
create table table1(
ID_TABLE1 varchar(15) not null
constraint PK_TABLE1 primary key clustered
(ID_TABLE1)
)
IF @.@.ERROR <> 0
BEGIN
print 'Error create table1'
END
--
The problem is when there is a 'create error', the code
print 'Error ...' is not executed because the error's
severity is too high and the script stops.
Is it possible to trap the default error ? When there is a
create error, I want that's the @.@.error is initialized and
the test is executed.
Thanks in advance for your help
Antoine (F-Paris)
MSSQL 7 & MSSQL 2000If the error is severe enough it will stop the batch and you can not trap it
within the batch. You would have to check for the error where you call the
batch. If your using something like ADO, sql server will report an error
that you can see in the errors collection.
Andrew J. Kelly
SQL Server MVP
"Antoine C." <acazedev@.libertysurfXXX.fr> wrote in message
news:11da01c3df60$d4f02bf0$a401280a@.phx.gbl...
quote:|||
> Hi,
> I want to write this SQL script
> --
> create table table1(
> ID_TABLE1 varchar(15) not null
> constraint PK_TABLE1 primary key clustered
> (ID_TABLE1)
> )
> IF @.@.ERROR <> 0
> BEGIN
> print 'Error create table1'
> END
> --
> The problem is when there is a 'create error', the code
> print 'Error ...' is not executed because the error's
> severity is too high and the script stops.
> Is it possible to trap the default error ? When there is a
> create error, I want that's the @.@.error is initialized and
> the test is executed.
> Thanks in advance for your help
> Antoine (F-Paris)
> MSSQL 7 & MSSQL 2000
quote:
>--Original Message--
>If the error is severe enough it will stop the batch
> and you can not trap itwithin the batch.
> You would have to check for the error where you call the
>batch.
>--
Thank you for the quick answer Andrew.
Bye
Subscribe to:
Posts (Atom)