MS-Access export to SQL Server 7.0

[John Casey]

Hello;

I used the import/export utility in Sql Server 7.0 to export
the data in one of my MS-Access databases to SQL 7.0. The utility
indicated all went well. Now if I try and use either ISQL or ODBC to
see the data in some tables (not all) I get the following error:

Msg 4004, Level 16, State 1, Server WVTORSQL1, Line 1
ntext data can not be sent to clients using DB-Library (such as ISQL)
or ODBC version 3.7 or earlier.

All of my tables are pretty generic and quite similar. I tried to find
a pattern in the good vs bad tables, but I can't see any. Any thoughts
or resolutions on this problem would be muchly appreciated...

tanks in advance

John Casey
Applications Support Specialist
Working Ventures Canadain Fund Inc.

[Alexey Dolganov]

Hi John!
Your data (at least one column in the table you are quering) imported into
MS SQL as Unicode text (ntext datatype). So you cannot request the data
using DBLibrary.
To avoid this problem you should first change column datatype from ntext
to text, if you really don't want to use unicode.
Other workaround is to use osql instead of isql and newer ODBC drivers

Regards,
Alexey Dolganov,
MCP,
Moscow, Russia

[John Casey]

Thank You, Thank You...you hit it right on the money. It seems SQL
import/export converted all my MS-Access memo fields to the SQL ntext
data type. Does SQL Server have a comparable memo field type? If not
how do people get around the memo field requirement? Create text field
large enough for the average requirement?

Thanks Again

[Umachandar Jayachandran]

The following BLOB data types are available in SQL Server 7.0:

<b>text</b> - for text data similar to <b>memo</b> field in Access or
Visual FoxPro
<b>image</b> - for storing binary data similar to <b>General</b> field
in Access or Visual FoxPro

<b>ntext</b> is a variant of text that allows you to store unicode text
& the maximum characters is half of that of text data type i.e. 2 power
30 -1. This is the same as the ANSI <b>national text</b> data type.
--
Umachandar Jayachandran
MCDBA, MCSE + Internet, MCP + Internet, MCSE

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

[John Casey]

Shit man your designations are longer than your name. You must be the
Yoda of computers or something. Thanks for the response.

Cheers ;o)

P.S. hmmmm, question. If I use a text datatype in SQL 7.0 to equate to
an MS-Access memo field and set the length to 1000 or 2000 (room
required for memo). Will SQL set asside 1000-2000 bytes for each
record used or not? Should I used one of the Var data types?

John Casey
Applications Support Specialist

Working Ventures Canadian Fund Inc.

[Bronek Kozicki]

If you want to have text less than 8000 chars, but actual string length will
vary, optimal type for this field would be varchar(your_max_allowed_length).
There's no drawaback on unused space - just the actual text length is stored
in the database. Using text (or image) data is not recommended (assuming
8000 chars is enough for your strings) as its manipulation is more
complicated.

Regards

Bronek Kozicki

John Casey wrote in message <376a81e8.331517176@wv-proxy>...

[John Casey]

Tanks...that was just what I was looking for.

[John Casey]

Ok I think I have got this now. Only one more question... You said
there is no drawback to using varchar vs. char. From what I understand
char stores your data and then pads the field with spaces to get the
indicated length. Varchar will store just the number of characters
with no padding. The length only indicated the max length. Is this
correct? There must be a speed advantage to using char during the
modification of data where you may update with more or less data in
the field.