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.
The errors on the bcp insert are:
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
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
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:57DB6ED2-A5EE-4843-B7E8-E10917575280@.microsoft.com...
> 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.
> The errors on the bcp insert are:
> 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
>
|||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
"Hilary Cotter" wrote:
> 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
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:57DB6ED2-A5EE-4843-B7E8-E10917575280@.microsoft.com...
>
>
|||Anything but binary should be acceptable for text, however it is complaining
about bcp'ing into the datetime column, and a numeric column.
[vbcol=seagreen]
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
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:28B7B9DD-0782-4B07-8C84-978959E6E0B7@.microsoft.com...[vbcol=seagreen]
> 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
> "Hilary Cotter" wrote:
|||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
"Hilary Cotter" wrote:
> 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
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:28B7B9DD-0782-4B07-8C84-978959E6E0B7@.microsoft.com...
>
>
|||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
"Doug" <Doug@.discussions.microsoft.com> wrote in message
news:407ECC97-CDC6-4CBA-8CA3-E750BC0C1B13@.microsoft.com...[vbcol=seagreen]
> 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
>
> "Hilary Cotter" wrote:
|||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
"Hilary Cotter" wrote:
> 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
>
> "Doug" <Doug@.discussions.microsoft.com> wrote in message
> news:407ECC97-CDC6-4CBA-8CA3-E750BC0C1B13@.microsoft.com...
>
>
