Invalid non-ASCII character conversion over JDBC to Solaris client

Hi,

I'm working on a database conversion from Sybase to SQL Server 2005 and have hit a wall with a character conversion problem when reading non-ASCII characters (encrypted password) via JDBC.

My application runs on Solaris and accesses a SQL Server 2005 database via the Microsoft JDBC driver. The server was unfortunately specified as having a SQL_Latin1_General_CP1_CI_AS collation at installation time, and the database being accessed has taken this default. After creation the data was migrated across via DTS.

The invalid character is a dagger '?'. When read over JDBC it is converted to a question mark '?'.

In my original environment a Sybase database was accessed via JDBC driver from Solaris and the correct value was returned. The Sybase database used Latin1_General_BIN as it's collation. By way of experimentation I have modified the default collation sequence within the SQL Server 2005 database, and created a new table to hold the password. I am then able to correctly return strings containing this character from within SQL Server Management Studio, but the same problem still exists when accessing it via JDBC.

I am not sure where to focus my investigation and would be grateful for any useful pointers/advice. To me it looks like it's a JDBC driver issue as with the change in collation it works from a non-JDBC client.

Many thanks

Alistair

Just to make sure we are on the same page, are you using the Microsoft 2005 Jdbc driver?

http://msdn.microsoft.com/data/ref/jdbc/

We have just shipped the June community tech preview of this driver if you want to play with the latest and greatest:

http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en

The Microsoft 2000 Jdbc driver is not supported here. If you are using the latest driver do you have some code that inserts the invalid data into the database and then returns it incorrectly? I would be happy to take a look at this.

|||

Thank you for the reply. I have been using the 2005 driver, but have managed to resolve the problem by changing the column data type to a binary rather than varchar.

Out of interest do you have any idea when the new driver will go on general release?

|||

Glad to hear you got this working!

We are currently working on shipping the v1.1 release of the 2005 JDBC driver, of course I can't promise anything but we are targetting an August release date.

|||

Hi,

Please can you update me on the shipping date for the v1.1 release of the 2005 JDBC driver? Is it likely to be within the next couple of weeks?

Regards,

Alistair

|||

Hi Alistair,

Yes, the v1.1 release should be available within the next couple of weeks. It may even be available as early as next week.

Thank you,

--David Olix

JDBC Development

sql

Invalid non-ASCII character conversion over JDBC to Solaris client

Hi,

I'm working on a database conversion from Sybase to SQL Server 2005 and have hit a wall with a character conversion problem when reading non-ASCII characters (encrypted password) via JDBC.

My application runs on Solaris and accesses a SQL Server 2005 database via the Microsoft JDBC driver. The server was unfortunately specified as having a SQL_Latin1_General_CP1_CI_AS collation at installation time, and the database being accessed has taken this default. After creation the data was migrated across via DTS.

The invalid character is a dagger '?'. When read over JDBC it is converted to a question mark '?'.

In my original environment a Sybase database was accessed via JDBC driver from Solaris and the correct value was returned. The Sybase database used Latin1_General_BIN as it's collation. By way of experimentation I have modified the default collation sequence within the SQL Server 2005 database, and created a new table to hold the password. I am then able to correctly return strings containing this character from within SQL Server Management Studio, but the same problem still exists when accessing it via JDBC.

I am not sure where to focus my investigation and would be grateful for any useful pointers/advice. To me it looks like it's a JDBC driver issue as with the change in collation it works from a non-JDBC client.

Many thanks

Alistair

Just to make sure we are on the same page, are you using the Microsoft 2005 Jdbc driver?

http://msdn.microsoft.com/data/ref/jdbc/

We have just shipped the June community tech preview of this driver if you want to play with the latest and greatest:

http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en

The Microsoft 2000 Jdbc driver is not supported here. If you are using the latest driver do you have some code that inserts the invalid data into the database and then returns it incorrectly? I would be happy to take a look at this.

|||

Thank you for the reply. I have been using the 2005 driver, but have managed to resolve the problem by changing the column data type to a binary rather than varchar.

Out of interest do you have any idea when the new driver will go on general release?

|||

Glad to hear you got this working!

We are currently working on shipping the v1.1 release of the 2005 JDBC driver, of course I can't promise anything but we are targetting an August release date.

|||

Hi,

Please can you update me on the shipping date for the v1.1 release of the 2005 JDBC driver? Is it likely to be within the next couple of weeks?

Regards,

Alistair

|||

Hi Alistair,

Yes, the v1.1 release should be available within the next couple of weeks. It may even be available as early as next week.

Thank you,

--David Olix

JDBC Development

Invalid non-ASCII character conversion over JDBC to Solaris client

Hi,

I'm working on a database conversion from Sybase to SQL Server 2005 and have hit a wall with a character conversion problem when reading non-ASCII characters (encrypted password) via JDBC.

My application runs on Solaris and accesses a SQL Server 2005 database via the Microsoft JDBC driver. The server was unfortunately specified as having a SQL_Latin1_General_CP1_CI_AS collation at installation time, and the database being accessed has taken this default. After creation the data was migrated across via DTS.

The invalid character is a dagger '?'. When read over JDBC it is converted to a question mark '?'.

In my original environment a Sybase database was accessed via JDBC driver from Solaris and the correct value was returned. The Sybase database used Latin1_General_BIN as it's collation. By way of experimentation I have modified the default collation sequence within the SQL Server 2005 database, and created a new table to hold the password. I am then able to correctly return strings containing this character from within SQL Server Management Studio, but the same problem still exists when accessing it via JDBC.

I am not sure where to focus my investigation and would be grateful for any useful pointers/advice. To me it looks like it's a JDBC driver issue as with the change in collation it works from a non-JDBC client.

Many thanks

Alistair

Just to make sure we are on the same page, are you using the Microsoft 2005 Jdbc driver?

http://msdn.microsoft.com/data/ref/jdbc/

We have just shipped the June community tech preview of this driver if you want to play with the latest and greatest:

http://www.microsoft.com/downloads/details.aspx?familyid=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en

The Microsoft 2000 Jdbc driver is not supported here. If you are using the latest driver do you have some code that inserts the invalid data into the database and then returns it incorrectly? I would be happy to take a look at this.

|||

Thank you for the reply. I have been using the 2005 driver, but have managed to resolve the problem by changing the column data type to a binary rather than varchar.

Out of interest do you have any idea when the new driver will go on general release?

|||

Glad to hear you got this working!

We are currently working on shipping the v1.1 release of the 2005 JDBC driver, of course I can't promise anything but we are targetting an August release date.

|||

Hi,

Please can you update me on the shipping date for the v1.1 release of the 2005 JDBC driver? Is it likely to be within the next couple of weeks?

Regards,

Alistair

|||

Hi Alistair,

Yes, the v1.1 release should be available within the next couple of weeks. It may even be available as early as next week.

Thank you,

--David Olix

JDBC Development

invalid character when I browse Reports

When I go to inetmgr
=> Web Sites
=> Reports
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error
and then click the Refresh button, or try again later.
----
A name was started with an invalid character. Error processing
resource 'http://localhost/Reports/'. Line 1, Position 2
<%@. Page language="c#" Codebehind="Home.aspx.cs"
AutoEventWireup="false"
Inherits="Microsoft.ReportingServices.UI.HomePag...
When I go to inetmgr
=> Web Sites
=> ReportServer
Reporting Services Error
----
The report server has encountered a configuration error. See the
report server log files for more information.
(rsServerConfigurationError)
Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL.
3\Reporting Services\ReportServer\RSReportServer.config' is denied.
----
SQL Server Reporting Services
Any ideas welcome,
BryanThis is a wild guess but check and make sure that the Reports and
ReportServer websites in IIS are setup for the correct version of the dotnet
framework. For RS 2000 it should be 1.1 for RS 2005 it needs to be 2.0.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"dba" <bryanmurtha@.gmail.com> wrote in message
news:1193425095.280637.260080@.d55g2000hsg.googlegroups.com...
> When I go to inetmgr
> => Web Sites
> => Reports
> The XML page cannot be displayed
> Cannot view XML input using XSL style sheet. Please correct the error
> and then click the Refresh button, or try again later.
>
> ----
> A name was started with an invalid character. Error processing
> resource 'http://localhost/Reports/'. Line 1, Position 2
> <%@. Page language="c#" Codebehind="Home.aspx.cs"
> AutoEventWireup="false"
> Inherits="Microsoft.ReportingServices.UI.HomePag...
> When I go to inetmgr
> => Web Sites
> => ReportServer
> Reporting Services Error
> ----
> The report server has encountered a configuration error. See the
> report server log files for more information.
> (rsServerConfigurationError)
> Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL.
> 3\Reporting Services\ReportServer\RSReportServer.config' is denied.
> ----
> SQL Server Reporting Services
> Any ideas welcome,
> Bryan
>

invalid character value on 7000th record

Our developers are trying to pinpoint why a function keeps bombing out
(email below). The database was created using the same setup as other
dbs, none of which have had this problem. I ran a trace, which showed
several Sort Warnings before the process stopped, but no error
messages. The process seems to be a complex query for data, which is
then loaded into a table.
Any suggestions?
I am trying to debug a problem with some data conversion (from a dbf
file into a SQL table) For some reason we get this once we have loaded
our 7000th record. It is not a problem with the record, not always the
same one, has something to do with the limit. Not sure why 7000, but
always crashes there. I have tried everything on the code side.
Is there any setting in SQL that my enforce some limits on data loading
or on the store call, maybe something odd with this table?
"Underlying DBMS error[Microsoft OLE DB Provider for SQL Server:
Invalid character value for cast specification. (.dbo.a109)]"hi,
If you try to move that table using an ETL tool as DTS, inside the pump you
can define how many error as maximum you want to pass.
"naomi" wrote:

> Our developers are trying to pinpoint why a function keeps bombing out
> (email below). The database was created using the same setup as other
> dbs, none of which have had this problem. I ran a trace, which showed
> several Sort Warnings before the process stopped, but no error
> messages. The process seems to be a complex query for data, which is
> then loaded into a table.
> Any suggestions?
>
> I am trying to debug a problem with some data conversion (from a dbf
> file into a SQL table) For some reason we get this once we have loaded
> our 7000th record. It is not a problem with the record, not always the
> same one, has something to do with the limit. Not sure why 7000, but
> always crashes there. I have tried everything on the code side.
> Is there any setting in SQL that my enforce some limits on data loading
> or on the store call, maybe something odd with this table?
> "Underlying DBMS error[Microsoft OLE DB Provider for SQL Server:
> Invalid character value for cast specification. (.dbo.a109)]"
>

Invalid character value for cast specification/other errors

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...
>
>

Invalid character value for cast specification error

I'm using Access 2K via ODBC to replicated SQL Server 2K. In some tables (not all) when I try to add a record either with a form or directly in the datasheet I get this error message and all form controls/table cells display '#Name?'. The record is added
and displays correctly if I close and reopen the form/table. I can add records with no problem in Enterprise Manager.
Any help gratefully received.
Jonathan Attree
Hi, I am getting exactly the same problem although this problem has only occurred since I implemented merge replication. Does anyone have an answer?
Amanda

Invalid character value for cast specification

Hi: gurus,
I am using sqlxml3.0 to bulkload to insert the data to SQLServer. Every
thing works fine if I don't use the datetime type column in sql. If I use the
datetime column in sql I am getting this message "Invalid character value for
cast specification"
Here is my XSD, I am not sure how to map sql annotation for date time field
"effdate" (do I have to) for this XSD. I will really appreciate your response.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="BillerInfo" sql:relation="BillerInfo">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="blrid" type="xsd:string"/>
<xsd:element name="acdind" type="xsd:string"/>
<xsd:element name="effdate" type="xsd:dateTime"/>
<xsd:element name="trnaba" type="xsd:string"/>
<xsd:element name="billername" type="xsd:string"/>
<xsd:element name="billerclass" type="xsd:string"/>
<xsd:element name="dmpprenote" type="xsd:boolean"/>
<xsd:element name="dmppayonly" type="xsd:boolean"/>
<xsd:element name="blroldname" type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
I guess the problem most likely lies in your data file. I assume that you
have a datetime value which is not matching with the xsd:dataTime format.
If you don't have the control on the input data, you may specify xsd:string
instead of xsd:dataTime.
Thanks.
"Rashid" <Rashid@.discussions.microsoft.com> wrote in message
news:FE70E9A6-B2AF-4BD4-B0DC-88425795A2F1@.microsoft.com...
> Hi: gurus,
> I am using sqlxml3.0 to bulkload to insert the data to SQLServer. Every
> thing works fine if I don't use the datetime type column in sql. If I use
the
> datetime column in sql I am getting this message "Invalid character value
for
> cast specification"
> Here is my XSD, I am not sure how to map sql annotation for date time
field
> "effdate" (do I have to) for this XSD. I will really appreciate your
response.
>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="BillerInfo" sql:relation="BillerInfo">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="blrid" type="xsd:string"/>
> <xsd:element name="acdind" type="xsd:string"/>
> <xsd:element name="effdate" type="xsd:dateTime"/>
> <xsd:element name="trnaba" type="xsd:string"/>
> <xsd:element name="billername" type="xsd:string"/>
> <xsd:element name="billerclass" type="xsd:string"/>
> <xsd:element name="dmpprenote" type="xsd:boolean"/>
> <xsd:element name="dmppayonly" type="xsd:boolean"/>
> <xsd:element name="blroldname" type="xsd:string"/>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
>

Invalid character in XML

I'm using XML EXPLICIT to query some data which may contain some invalid XML
characters. While I'm reading in the data I get an error. Other than
removing the characters before the data is inserted into the database, is
there a way to handle (or omit) reading the invalid characters?
Thanks.Steve,
Have you thought about escaping the invalid characters? You can escape using
either &<decimal>; or &x<hexadecimal>;
Thanks,
Amol
"SteveISOA" wrote:

> I'm using XML EXPLICIT to query some data which may contain some invalid X
ML
> characters. While I'm reading in the data I get an error. Other than
> removing the characters before the data is inserted into the database, is
> there a way to handle (or omit) reading the invalid characters?
> Thanks.|||Amol,
At what point can you escape the invalid characters? I do not want to
modify the existing data in the database, and I'm using a very simple proces
s
of reading and writing the data. It looks something like this:
...
SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
...
XmlTextWriter txtWriter = new XmlTextWriter(...);
XmlReader xmlReader = mCommand.ExecuteXmlReader();
while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
{
txtWriter.WriteNode(xmlReader,false);
}
...
Thanks again.
Steve
"Amol Kher" wrote:
> Steve,
> Have you thought about escaping the invalid characters? You can escape usi
ng
> either &<decimal>; or &x<hexadecimal>;
> Thanks,
> Amol
> "SteveISOA" wrote:
>|||Steve,
The escaping should happen before the reader is created. But looks like you
dont have control over the reader creation. Once the reader is created, it
will work off the stream and if you can somehow intercept this stream then
you can replace it there.
Unfortunately invalid characters in XML is not allowed by the XML Spec so
the best solution is if you can fix it when the data gets in and not when yo
u
pull it out. Even if you find a solution to work around this issue,
potentially this is a compatibility issue with other compliant parsers.
Thanks,
Amol
"SteveISOA" wrote:
> Amol,
> At what point can you escape the invalid characters? I do not want to
> modify the existing data in the database, and I'm using a very simple proc
ess
> of reading and writing the data. It looks something like this:
> ...
> SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
> ...
> XmlTextWriter txtWriter = new XmlTextWriter(...);
> XmlReader xmlReader = mCommand.ExecuteXmlReader();
> while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
> {
> txtWriter.WriteNode(xmlReader,false);
> }
> ...
> Thanks again.
> Steve
> "Amol Kher" wrote:
>|||You need to ensure that all binary columns or char columns which has invalid
char values like 0xa, 0xb are binary encoded with encodings like binbase64.
--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"SteveISOA" <SteveISOA@.discussions.microsoft.com> wrote in message
news:385305A5-2C52-4843-A93B-36D209ED13DD@.microsoft.com...
> Amol,
> At what point can you escape the invalid characters? I do not want to
> modify the existing data in the database, and I'm using a very simple
> process
> of reading and writing the data. It looks something like this:
> ...
> SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
> ...
> XmlTextWriter txtWriter = new XmlTextWriter(...);
> XmlReader xmlReader = mCommand.ExecuteXmlReader();
> while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
> {
> txtWriter.WriteNode(xmlReader,false);
> }
> ...
> Thanks again.
> Steve
> "Amol Kher" wrote:
>|||Assuming that the characters are invalid not because of the wrong encoding
(FOR XML results are UTF-16 encoded which means that you need to set it
accordingly on the client side), you have to filter the invalid characters
out in your TSQL code. There may be some non-standard option on the XML
parser that allows you to parse the invalid characters in System.XML, but I
am not sure about that.
Best regards
Michael
"SteveISOA" <SteveISOA@.discussions.microsoft.com> wrote in message
news:385305A5-2C52-4843-A93B-36D209ED13DD@.microsoft.com...
> Amol,
> At what point can you escape the invalid characters? I do not want to
> modify the existing data in the database, and I'm using a very simple
> process
> of reading and writing the data. It looks something like this:
> ...
> SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
> ...
> XmlTextWriter txtWriter = new XmlTextWriter(...);
> XmlReader xmlReader = mCommand.ExecuteXmlReader();
> while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
> {
> txtWriter.WriteNode(xmlReader,false);
> }
> ...
> Thanks again.
> Steve
> "Amol Kher" wrote:
>

Invalid character in XML

I'm using XML EXPLICIT to query some data which may contain some invalid XML
characters. While I'm reading in the data I get an error. Other than
removing the characters before the data is inserted into the database, is
there a way to handle (or omit) reading the invalid characters?
Thanks.
Steve,
Have you thought about escaping the invalid characters? You can escape using
either &<decimal>; or &x<hexadecimal>;
Thanks,
Amol
"SteveISOA" wrote:

> I'm using XML EXPLICIT to query some data which may contain some invalid XML
> characters. While I'm reading in the data I get an error. Other than
> removing the characters before the data is inserted into the database, is
> there a way to handle (or omit) reading the invalid characters?
> Thanks.
|||Amol,
At what point can you escape the invalid characters? I do not want to
modify the existing data in the database, and I'm using a very simple process
of reading and writing the data. It looks something like this:
...
SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
...
XmlTextWriter txtWriter = new XmlTextWriter(...);
XmlReader xmlReader = mCommand.ExecuteXmlReader();
while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
{
txtWriter.WriteNode(xmlReader,false);
}
...
Thanks again.
Steve
"Amol Kher" wrote:
[vbcol=seagreen]
> Steve,
> Have you thought about escaping the invalid characters? You can escape using
> either &<decimal>; or &x<hexadecimal>;
> Thanks,
> Amol
> "SteveISOA" wrote:
|||Steve,
The escaping should happen before the reader is created. But looks like you
dont have control over the reader creation. Once the reader is created, it
will work off the stream and if you can somehow intercept this stream then
you can replace it there.
Unfortunately invalid characters in XML is not allowed by the XML Spec so
the best solution is if you can fix it when the data gets in and not when you
pull it out. Even if you find a solution to work around this issue,
potentially this is a compatibility issue with other compliant parsers.
Thanks,
Amol
"SteveISOA" wrote:
[vbcol=seagreen]
> Amol,
> At what point can you escape the invalid characters? I do not want to
> modify the existing data in the database, and I'm using a very simple process
> of reading and writing the data. It looks something like this:
> ...
> SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
> ...
> XmlTextWriter txtWriter = new XmlTextWriter(...);
> XmlReader xmlReader = mCommand.ExecuteXmlReader();
> while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
> {
> txtWriter.WriteNode(xmlReader,false);
> }
> ...
> Thanks again.
> Steve
> "Amol Kher" wrote:
|||You need to ensure that all binary columns or char columns which has invalid
char values like 0xa, 0xb are binary encoded with encodings like binbase64.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"SteveISOA" <SteveISOA@.discussions.microsoft.com> wrote in message
news:385305A5-2C52-4843-A93B-36D209ED13DD@.microsoft.com...[vbcol=seagreen]
> Amol,
> At what point can you escape the invalid characters? I do not want to
> modify the existing data in the database, and I'm using a very simple
> process
> of reading and writing the data. It looks something like this:
> ...
> SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
> ...
> XmlTextWriter txtWriter = new XmlTextWriter(...);
> XmlReader xmlReader = mCommand.ExecuteXmlReader();
> while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
> {
> txtWriter.WriteNode(xmlReader,false);
> }
> ...
> Thanks again.
> Steve
> "Amol Kher" wrote:
|||Assuming that the characters are invalid not because of the wrong encoding
(FOR XML results are UTF-16 encoded which means that you need to set it
accordingly on the client side), you have to filter the invalid characters
out in your TSQL code. There may be some non-standard option on the XML
parser that allows you to parse the invalid characters in System.XML, but I
am not sure about that.
Best regards
Michael
"SteveISOA" <SteveISOA@.discussions.microsoft.com> wrote in message
news:385305A5-2C52-4843-A93B-36D209ED13DD@.microsoft.com...[vbcol=seagreen]
> Amol,
> At what point can you escape the invalid characters? I do not want to
> modify the existing data in the database, and I'm using a very simple
> process
> of reading and writing the data. It looks something like this:
> ...
> SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
> ...
> XmlTextWriter txtWriter = new XmlTextWriter(...);
> XmlReader xmlReader = mCommand.ExecuteXmlReader();
> while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
> {
> txtWriter.WriteNode(xmlReader,false);
> }
> ...
> Thanks again.
> Steve
> "Amol Kher" wrote:

Invalid Character in Flat File or Turncate problem

My source is a csv flat file. Currently I use that same flat file on SQL 2000 and SQL 2005.

On SQL 2000 it runs fine and it inserts that character as part of the string (varchar), however, it gives me truncate error on sql 2005. I already use the "Suggest Types...." and my Output columns have the correct lenght (specially that lehght of that column is only 30 char which is less than the default anyways). If I remove that character it runs fine for that column.....

This is the values that I get in my flat file for the trouble coulmn is "ATTN: JON OLSEN a€“ CTRL8 "

And the error that I get when running the SSIS is

[Flat File Source OrderDetail [1]] Error: Data conversion failed. The data conversion for column "Column 15" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

[Flat File Source OrderDetail [1]] Error: The "output column "ShipToAddr1" (63)" failed because truncation occurred, and the truncation row disposition on "output column "ShipToAddr1" (63)" specifies failure on truncation.
A truncation error occurred on the specified object of the specified component.

[Flat File Source OrderDetail [1]] Error: An error occurred while processing file "C:\Inetpub\ftproot\orderdetail.csv" on data row 6.

Any help greatly approciated.....

Thank you,

Maria

What code page are you using? 1252?|||

Yes, It is 1252.

|||There are more than 30 characters in your example.|||

Hi Phil,

yes, you are right I just counted and it is 32 characters.....What do you suggest - Should I have the flat file to be corrected OR should I handle it on my side in SSIS? If to handle on my side in SSIS - than what's the best way to address it? The table column is 30 char.

I did not realize as it never has been problem in sql 2000

Thank you,

Maria

|||Since you are going into a varchar, try trimming the column.

Stick a derived column between the source and destination and use the RTRIM() character function to eliminate the trailing whitespace. Then it'll fit.

So, in your expression, you'll do: RTRIM([columnname])

Try that. The characters are valid in the 1252 code page, so that shouldn't be an issue.|||

Phil,

The derived column did not solve the issue - it fails before it reaches the derived column. I went to the Flat File Source Editor -> Error Output and change to "Ignore Failure" for Truncate for that Column and that did it. But I am not comfortable with doing it....not sure if that is a right way to do it.

Can you think of anything that I am overlooking before reaching the derived column?

Thank you,

Maria

|||

It may be better to use 'Redirect rows' to a external file for further inspection. May be if you see all the faulty rows you would get better clues. If you still want those rows to make the final destination use a multicast after the error output; then use one output to the error file destination and the other to merge them back to the main flow (via Union all). This won't solve your problem but at least will give you the ability of looking into the faulty rows.

Just a suggestion...

|||How exactly do you have your SSIS package setup? What source, destination, and transformation components do you have and in what order?

You might have to put the derived column *right* after the source and then you may have to remap some columns in downstream components so that they pick up the correct column lengths.|||

Thank you Rafael. I will try that.

Maria

|||

Phil,

within my Data Flow i have it set up as follow

Flat File Source -> Derived Column ->OLE DB Destination.

The data flow part should pretty much take the flat file and insert it into my Tmp table.....and then within Contol Flow i do the update, insert, etc from that tmp table. But the problem that I get is within my Data Flow, Flat File Source to be specific. I only have one Derived Column to format data for couple of other columns including the RTRIM(column_name) that you suggested earlier.

Thank you,

Maria

|||Yep, try Rafael's suggestion and then report back with the error number that's provided for those rows.

Thanks,
Phil|||

Phil and Rafael,

I ran a test with just first 6 rows to see what exactly is happening......And in my ErrorOutput file I was able to see the ErrorCode which was always -1071607675 (Truncate error) in my case and then ErrorColumn which was the ID. What seems to be is that when I did the "Suggest Type....." it picked the Flat File Source External Column width from flat file which has columns width bigger by 1 or 2 char from Flat file source Output column....So that is why this is failing and gives me the truncate error message. The Flat file source Output Column has the correct width. The bigger width in External Column is random and it is dependent on whats in the flat file so I cannot assume that my Product Description column will be always 81 varchar, for some products might be bigger depending on what the invalid character will be......I guess it is caused by the ascii char that cannot be properly handled by text editor.....

We will have to address the problem at the point where the flat file is being generated and my SSIS should work just fine.

Thank you both for your help.

Maria

|||

mariap wrote:

Phil and Rafael,

I ran a test with just first 6 rows to see what exactly is happening......And in my ErrorOutput file I was able to see the ErrorCode which was always -1071607675 (Truncate error) in my case and then ErrorColumn which was the ID. What seems to be is that when I did the "Suggest Type....." it picked the Flat File Source External Column width from flat file which has columns width bigger by 1 or 2 char from Flat file source Output column....So that is why this is failing and gives me the truncate error message. The Flat file source Output Column has the correct width. The bigger width in External Column is random and it is dependent on whats in the flat file so I cannot assume that my Product Description column will be always 81 varchar, for some products might be bigger depending on what the invalid character will be......I guess it is caused by the ascii char that cannot be properly handled by text editor.....

We will have to address the problem at the point where the flat file is being generated and my SSIS should work just fine.

Thank you both for your help.

Maria

Just remember that suggest types is only valid for getting you to a starting point. You should still implement your own quality control for every column to be sure they are correct. Don't rely on the suggest types feature to be accurate as you are finding out.

Phil|||

Thank you Phil. you are absolutly right!

Maria

Invalid character in a report

Hi!

Trying to generate a report (using WebForm ReportViewer, from dynamically created RDL report and SQL Server 2005 OLAP cube), if a database field contains control characters (code < 0x20), Reporting Services generate following message:

' ', hexadecimal value 0x02, is an invalid character. Line 1, position 2376.

Is it possible to ignore that? I don't care if browser shows an octopus, the report must work.

Thanks, Andrei.

Could you publish RDL (or e-mail it to me)?

thanks!

|||

Lev,

I've just emailed RDL and other details to you.

I could reproduce the problem using sample AdventureWorksDW and OLAP (standard edition).

Create an OLAP table report, put e.g. Model Name in one of columns. The report works fine. Now change e.g. ModelName for one of products, to include control character(s), e.g.:

update DimProduct
set ModelName = 'Mountain-100 ' + char(31) + char(2) + ' AB'
where ProductAlternateKey = 'BK-M82S-38'

Reprocess Product dimension.

Refresh the report. Once Montain-100 model is about to be displayed on the page you should get following message:

hexadecimal value 0x1F, is an invalid character. Line 1, position 2385.

I've just noticed that after the change applied even OLAP browser of SQL Server Management Studio generates the same error if only the ModelName is about to be displayed.

So it may be Analysis Service's problem indeed.

I've tried with non-OLAP reports in Reporting Services, and they work fine, displaying square placeholders.

|||That is known AS issue. Certain control characters cannot be transmitted from server to client.

Invalid character in a report

Hi!

Trying to generate a report (using WebForm ReportViewer, from dynamically created RDL report and SQL Server 2005 OLAP cube), if a database field contains control characters (code < 0x20), Reporting Services generate following message:

' ', hexadecimal value 0x02, is an invalid character. Line 1, position 2376.

Is it possible to ignore that? I don't care if browser shows an octopus, the report must work.

Thanks, Andrei.

Could you publish RDL (or e-mail it to me)?

thanks!

|||

Lev,

I've just emailed RDL and other details to you.

I could reproduce the problem using sample AdventureWorksDW and OLAP (standard edition).

Create an OLAP table report, put e.g. Model Name in one of columns. The report works fine. Now change e.g. ModelName for one of products, to include control character(s), e.g.:

update DimProduct
set ModelName = 'Mountain-100 ' + char(31) + char(2) + ' AB'
where ProductAlternateKey = 'BK-M82S-38'

Reprocess Product dimension.

Refresh the report. Once Montain-100 model is about to be displayed on the page you should get following message:

hexadecimal value 0x1F, is an invalid character. Line 1, position 2385.

I've just noticed that after the change applied even OLAP browser of SQL Server Management Studio generates the same error if only the ModelName is about to be displayed.

So it may be Analysis Service's problem indeed.

I've tried with non-OLAP reports in Reporting Services, and they work fine, displaying square placeholders.

|||That is known AS issue. Certain control characters cannot be transmitted from server to client.