Invalid character value for cast specification/other errors

can you verify that the publisher and subscriber have the same mdac level.
If not there is probably something in your data which is an invalid value.
Can you query your date data to ensure that all values are valid.
--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Post by Doug
We are on SQL Server 2000 SP3
We are trying to get to SQL 2005, but cannot do that until we consolidate
our databases so the upgrade will complete in a reasonable amount of time.
I am trying to use bcp to copy data out from each database and move it
into a centralized database. Of the 108 tables/articles I have, only one is
causing me a problem. It appears to be on a text defined column(we have
other text columns that are fine). The column can contain HTML tags, code,
etc. From what I see, the system is encountering some character(s) in the
file that is treating it a an end-of-line. If I adjust the first record
being processed to just have 'normal' text in it, it will load in just fine.
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 01000, NativeError = 4836
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]
BCP copy in failed
---------------------------------------
Any ideas/suggestions on how to get around this? I tried putting the data
into a temporary table, but received the same error. With bcp are the
merge triggers fired? I didn't think so as nothing goes into
MSmerge_contents. I had read about a stored procedure that may be getting
executed that tries to parse the data. They said the solution was to not
call this particular S.P. (don't recall it's name right now). But, if I'm
not executing any merge S.P., then any other S.P.s the system may be using
for the bcp are out of my control aren't they?
Thanks for any help,
Doug

Doug

2007-03-30 19:18:01 UTC

Hillary,

The bcp out & in are both occurring on the server/publisher. At this
point, there is no subscription involvement.

What is valid verses invalid for a text data type? The users can put
basically whatever they want into this data type. It is usually HTML and
text, but I suppose end-of-line, tabs, other HTML formatting can be in there.
We really don't want the system to interpret any of the data in this column,
just want it to move it. My guess is the problem is it's moving out to a
data file. I tried using a temporary table, hoping that would help, but it
didn't.

Looks like the MDAC level on the server is 2.82.1830.0

Doug

Post by Hilary Cotter
can you verify that the publisher and subscriber have the same mdac level.
If not there is probably something in your data which is an invalid value.
Can you query your date data to ensure that all values are valid.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Hilary Cotter

2007-03-30 22:49:27 UTC

Anything but binary should be acceptable for text, however it is complaining
about bcp'ing into the datetime column, and a numeric column.

Post by Doug
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22005, NativeError = 0

It is possible that there is something in the text data which it is
interpreting as an end of column or end of row character which causes it to
try to insert text into a datetime column. I notice you have exactly 10
failure messages before a final give up message. How many columns are there
in the table?
--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Post by Doug
Hillary,
The bcp out & in are both occurring on the server/publisher. At this
point, there is no subscription involvement.
What is valid verses invalid for a text data type? The users can put
basically whatever they want into this data type. It is usually HTML and
text, but I suppose end-of-line, tabs, other HTML formatting can be in there.
We really don't want the system to interpret any of the data in this column,
just want it to move it. My guess is the problem is it's moving out to a
data file. I tried using a temporary table, hoping that would help, but it
didn't.
Looks like the MDAC level on the server is 2.82.1830.0
Doug

Post by Doug
We are on SQL Server 2000 SP3
We are trying to get to SQL 2005, but cannot do that until we consolidate
our databases so the upgrade will complete in a reasonable amount of time.
I am trying to use bcp to copy data out from each database and move it
into a centralized database. Of the 108 tables/articles I have, only
one
is
causing me a problem. It appears to be on a text defined column(we have
other text columns that are fine). The column can contain HTML tags, code,
etc. From what I see, the system is encountering some character(s) in the
file that is treating it a an end-of-line. If I adjust the first record
being processed to just have 'normal' text in it, it will load in just fine.
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 01000, NativeError = 4836
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]
BCP copy in failed
---------------------------------------
Any ideas/suggestions on how to get around this? I tried putting the data
into a temporary table, but received the same error. With bcp are the
merge triggers fired? I didn't think so as nothing goes into
MSmerge_contents. I had read about a stored procedure that may be getting
executed that tries to parse the data. They said the solution was to not
call this particular S.P. (don't recall it's name right now). But, if I'm
not executing any merge S.P., then any other S.P.s the system may be using
for the bcp are out of my control aren't they?
Thanks for any help,
Doug

Doug

2007-03-31 15:46:02 UTC

Hilary,

I too think there is something the bcp is interpreting as
end-of-line...something. But it appears to me it is on creating the out
file. I've created .txt, .dat, etc files (don't know that the format of the
output file matters to bcp). When I open the output file, I see part of the
text column has been put on the second line and even some has gone to the
third line.

I'm thinking the creation of the output file is where the problem really is.
It hasn't kept each record's data to its own line. When it tries to
populate a column, it is picking up data from another column which records is
the data type errors.

I have 11 records in the output. I had 'adjusted' the first record before
extracting it so it didnt have any special code in it,just some text. This
line loaded okay. The 10 errors you mention are probably because of the
other 10 records, not so much the number of columns. There are 12 columns in
the table.

It seems the bcp is trying to interpret the data as the output file is
being created. Is there either some way to stop that or some options that
would allow it to properly read this text column? Again, about anything
can be in it. I know if I do an INSERT INTO table1 .........SELECT col1,
col2, etc FROM this_table that works fine. I'm trying to avoid the
INSERT as I don't want the MSmerge_contents to be populated as the subscriber
already has the data. We're just trying to consolidate publisher dbs.

Thanks,

Doug

Post by Hilary Cotter
Anything but binary should be acceptable for text, however it is complaining
about bcp'ing into the datetime column, and a numeric column.

It is possible that there is something in the text data which it is
interpreting as an end of column or end of row character which causes it to
try to insert text into a datetime column. I notice you have exactly 10
failure messages before a final give up message. How many columns are there
in the table?
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Post by Doug
We are on SQL Server 2000 SP3
We are trying to get to SQL 2005, but cannot do that until we consolidate
our databases so the upgrade will complete in a reasonable amount of time.
I am trying to use bcp to copy data out from each database and move it
into a centralized database. Of the 108 tables/articles I have, only
one
is
causing me a problem. It appears to be on a text defined column(we have
other text columns that are fine). The column can contain HTML tags, code,
etc. From what I see, the system is encountering some character(s) in the
file that is treating it a an end-of-line. If I adjust the first record
being processed to just have 'normal' text in it, it will load in just fine.
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification
SQLState = 01000, NativeError = 4836
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]
BCP copy in failed
---------------------------------------
Any ideas/suggestions on how to get around this? I tried putting the data
into a temporary table, but received the same error. With bcp are the
merge triggers fired? I didn't think so as nothing goes into
MSmerge_contents. I had read about a stored procedure that may be getting
executed that tries to parse the data. They said the solution was to not
call this particular S.P. (don't recall it's name right now). But, if I'm
not executing any merge S.P., then any other S.P.s the system may be using
for the bcp are out of my control aren't they?
Thanks for any help,
Doug

Hilary Cotter

2007-04-01 13:20:50 UTC

Can you try to bcp the data out, and then bcp it into a table with an
identical structure as the published table. Using the firstrow and lastrow
options you should be able to figure out where the problem data is.
--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Post by Doug
Hilary,
I too think there is something the bcp is interpreting as
end-of-line...something. But it appears to me it is on creating the out
file. I've created .txt, .dat, etc files (don't know that the format of the
output file matters to bcp). When I open the output file, I see part of the
text column has been put on the second line and even some has gone to the
third line.
I'm thinking the creation of the output file is where the problem really is.
It hasn't kept each record's data to its own line. When it tries to
populate a column, it is picking up data from another column which records is
the data type errors.
I have 11 records in the output. I had 'adjusted' the first record before
extracting it so it didnt have any special code in it,just some text.
This
line loaded okay. The 10 errors you mention are probably because of the
other 10 records, not so much the number of columns. There are 12 columns in
the table.
It seems the bcp is trying to interpret the data as the output file is
being created. Is there either some way to stop that or some options that
would allow it to properly read this text column? Again, about anything
can be in it. I know if I do an INSERT INTO table1 .........SELECT col1,
col2, etc FROM this_table that works fine. I'm trying to avoid the
INSERT as I don't want the MSmerge_contents to be populated as the subscriber
already has the data. We're just trying to consolidate publisher dbs.
Thanks,
Doug

Post by Hilary Cotter
Anything but binary should be acceptable for text, however it is complaining
about bcp'ing into the datetime column, and a numeric column.

It is possible that there is something in the text data which it is
interpreting as an end of column or end of row character which causes it to
try to insert text into a datetime column. I notice you have exactly 10
failure messages before a final give up message. How many columns are there
in the table?
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Post by Doug
Hillary,
The bcp out & in are both occurring on the server/publisher. At this
point, there is no subscription involvement.
What is valid verses invalid for a text data type? The users can put
basically whatever they want into this data type. It is usually HTML and
text, but I suppose end-of-line, tabs, other HTML formatting can be in there.
We really don't want the system to interpret any of the data in this column,
just want it to move it. My guess is the problem is it's moving out to a
data file. I tried using a temporary table, hoping that would help,
but
it
didn't.
Looks like the MDAC level on the server is 2.82.1830.0
Doug

Post by Doug
We are on SQL Server 2000 SP3
We are trying to get to SQL 2005, but cannot do that until we consolidate
our databases so the upgrade will complete in a reasonable amount of time.
I am trying to use bcp to copy data out from each database and
move
it
into a centralized database. Of the 108 tables/articles I have, only
one
is
causing me a problem. It appears to be on a text defined
column(we
have
other text columns that are fine). The column can contain HTML
tags,
code,
etc. From what I see, the system is encountering some character(s)
in
the
file that is treating it a an end-of-line. If I adjust the first record
being processed to just have 'normal' text in it, it will load in
just
fine.
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value
for
cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value
for
cast
specification
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid date format
SQLState = 22003, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Numeric value out of range
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value
for
cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value
for
cast
specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value
for
cast
specification
SQLState = 01000, NativeError = 4836
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]
BCP copy in failed
---------------------------------------
Any ideas/suggestions on how to get around this? I tried putting
the
data
into a temporary table, but received the same error. With bcp are the
merge triggers fired? I didn't think so as nothing goes into
MSmerge_contents. I had read about a stored procedure that may be getting
executed that tries to parse the data. They said the solution was
to
not
call this particular S.P. (don't recall it's name right now). But,
if
I'm
not executing any merge S.P., then any other S.P.s the system may be using
for the bcp are out of my control aren't they?
Thanks for any help,
Doug

Doug

2007-04-02 14:24:01 UTC

Hilary,

fyi........ I had used the -c option for creating the output file and
reading it in.

I changed this to use the -n option and then the process worked okay.
The out file just won't be as easy to look at for later reference, but at
least the process of moving the data to the consolidated database works,
which is most important at this point.

Thanks for your help,

Doug

Post by Hilary Cotter
Can you try to bcp the data out, and then bcp it into a table with an
identical structure as the published table. Using the firstrow and lastrow
options you should be able to figure out where the problem data is.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Post by Hilary Cotter
Anything but binary should be acceptable for text, however it is complaining
about bcp'ing into the datetime column, and a numeric column.