We help IT Professionals succeed at work.

Access Cross-Tab includes Korean characters not in source?

Ouch!  Time for my annual Access panic!

Regularly export a SQL mdf to Access mdb using DTS.
Have finally upgraded from SQL2000 to 2008 R2

Using the Import/Export wizard to an Access 2003 DB (Access 2000 format)
Seems to go fine

Then import Queries and Macros from as previous DB

But although the source data is in Roman the output is in ?Korean

See screenshot


Setting needing changing?

 Screenshot
Comment
Watch Question

Mark WillsTopic Advisor, Page Editor
SILVER EXPERT
Distinguished Expert 2018

Commented:
What are the table definitions in SQL 2008 ? Any unicode or binary being introduced which was not there before ?

How are you now doing the export ? are you saying the raw table looks OK in Access - or - is it just that the export process happens without error ?

What queries and Macros ? is this a new Access database - does it behave any differently if importing into the DB with the queries and macros ?

Trying to isolate :
1) is it SQL server not exporting properly
2) is it a mismatch between datatypes of say SQL to Access
3) Does the "raw" data look OK in Access
4) Why does importing the Queries and Macros make it go Korean

Jeffrey CoachmanMIS Liason
SILVER EXPERT
Most Valuable Expert 2012
Commented:
Yes, there a lot of unknowns here...
Is this Memo Data?
Did the cross tab ever work (Is this a new issue)
Are you quite sure this is "Korean" specifically...

<Using the Import/Export wizard to an Access 2003 DB (Access 2000 format)
Seems to go fine>
"Seems" to go fine?
Either it goes fine, or it does not...
;-)

I know that Memo data can have issues when it is aggregated, crosstabbed or Pivoted...

JeffCoachman

SILVER EXPERT
Most Valuable Expert 2013

Author

Commented:
Thanks both, the main issue here is you're dealing  with someone who knows just enough to cause havoc with SQL2000 and is only just getting used to 2008 :)  You may need to uses pictures and short words in a big font to get the message across to me (I'd prefer you don't use hand gestures!)

I'm doing this by creating a blank Access DB then using the Import/Export Wizard in 2008 to export the data from the SQL10 Native Client to Access MDB - I'm just exporting Tables not Views and the process kind of resembles the DTS Wizard in SQL2000 (without the helpful progress counter showing how many records for each table have been processed) so I'm hoping this the the correct route.

With the exported tables in the .mdb, I then import a series of queries, macros and reports that were used on the previous month's data so these are known to be OK.  The problem comes when one of the cross-tab queries is run and the result in the image is seen, normally the column with the "Korean" characters (No I don't know this is Korean for sure) would contain strings of 2-4 characters.  In the source table the cross-tab queries the records appear correct and that field displays as it should.

The mdb is growing like Topsy, I've been doing this every month for about four years and the total size of the Access DB is now in excess of 1.6GB, but it is only around 80MB bigger than last month's which is about the normal increment.

AFAIK there is no memo data in any of the tables involved in the query.

I'm guessing I've messed up somewhere in the settings 2008 uses to export as that's the only change in the current process, however the raw data in the .mdb looks right is the right size and so I believe the export has
done what it says on the box.

1) is it SQL server not exporting properly
This is my theory as in SQL2000 it was working fine

2) is it a mismatch between datatypes of say SQL to Access
Possibly, where in the export can I see?

3) Does the "raw" data look OK in Access
Yes, none of the tables involved in the query when viewed in the .mdb contain the rogue characters

4) Why does importing the Queries and Macros make it go Korean
Good question, the only change is the mdb is now made from SQL2008R2 and not SQL2000

Hope this helps, yes there are a lot of unknowns -  that's a fault with the user who doesn't know :)
SILVER EXPERT
Most Valuable Expert 2013

Author

Commented:
Hmm..

Is this any help? This is the only potential conversion error I can see.


Conversion.png
Mark WillsTopic Advisor, Page Editor
SILVER EXPERT
Distinguished Expert 2018
Commented:
Yeah, it does help...

If you look at your Access database, or at least the table in question and go into "design" mode you will see the datatypes of the Access fields.

My guess is you will see MEMO fields and as Jeff said above, they can be an issue in some types of queries. Would be worthwhile comparing to an older version of your Access table (if you still have one)

That would also explain why they "look" ok within Access.

nvarchar doesnt necessaqrily have to go to longtext (unless it is over 255 characters).

If they are MEMO in access, then suggest you change the datatype / mapping to TEXT as the destination, and change the "On Truncation" to not be "fail".

You can also try changing the data type of your Access table interactively - make it TEXT with a length of say 100 and see if your quries still show up kanji characters.
SILVER EXPERT
Most Valuable Expert 2013

Author

Commented:
You're both right on the money here, I've gradually added in tables from the new export into an older version and once the errors start appearing the table structure which previously contained Text with a defined field size is now showing as Memo.

So is this simply a question of telling "new" SQL to export anything it guesses should be Memo as Text? If so where do I do that?  The only option I see apart from "Fail" on "On truncation" is "Ignore"

EDIT: Both result in Memo where previously Text was used.
Topic Advisor, Page Editor
SILVER EXPERT
Distinguished Expert 2018
Commented:
Well, I dont have access to 2008 R2 import export wizard just now...

But there are a couple of things... First you can double click and see what it is really doing. It will also tell you the conversion file (the xml definition being used) for the destination. You used to be able to click on the destination type and change it directly, not sure if that is still possible in 2008 R2.

Now, a few gotchas to consider first... Columns > 255 characters will need to go to memo. nvarchar if it does contain unicode data will most likely need to go to memo. I think you will find that most character definitions for export will be converting to longtext (ie memo) except char() which should end up as a text column.

If you save as an SSIS package you could go back in and modify the data flow task...

But, you can also change the XML. You recall the conversion file when you double clicked on it ? Well they are simple text (xml) files that you can change.  They *should* live in : C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles

When SSIS (or the wizard) does an export, it reads in via the conversion file into one of the SSIS datatypes, then writes out to the destination using the export definition. You can very simply go in and modify those default behaviours and is arguably the easiest way to change for the wizard.

Have a read of : http://msdn.microsoft.com/en-us/library/ms141209.aspx  and http://msdn.microsoft.com/en-us/library/bb895363.aspx to understand more about the import / export wizard and SSIS.

SILVER EXPERT
Most Valuable Expert 2013

Author

Commented:
Thanks both, in the end took the pragmatic way out and altered the structure of the exported Access tables that are in the crosstab queries, but now I understand why it happens.  I guess soon I'm just going to have to bite the bullet and learn how to directly quiz the SQL tables (or get someone in to do it for me :))

Explore More ContentExplore courses, solutions, and other research materials related to this topic.