I have a SQL Server function which returns the data type VARCHAR(max).
When I use the function in a view and link to the view through ODBC,
it becomes a Text(255) data type in Access and most of the return
values get truncated.

After browsing this forum, I've seen many suggested alternatives to
VARCHAR, none of which translate correctly to Memo.
- All variations of VARCHAR and NVARCHAR get translated into Access'
Text data type.
- LONGTEXT, LONGCHAR, MEMO, and NOTE aren't recognized by SQL Server.
- TEXT generates the error "The text data type is invalid for return
values".

Can anyone point me in the right direction? All help is appreciated.

I'm using SQL Server 2005 and Access 2003. For an ODBC driver, I'm
using the one called "SQL Native Client", version 2005.90.4035.00.


-TC

existential.philosophy (AT) gmail (DOT) com wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm using SQL Server 2000 and Access XP and attaching a View w/ a
function that returns a varchar(3000). The function results in a Memo
column. My ODBC driver is "SQL Server," version 2000.85.1132.00, file
name SQLSRV32.dll.

Have you made sure the attached View has a unique index column? I'm not
sure that would cause the Varchar -> Text problem, but maybe??

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdWWSoechKqOuFEgEQJr3ACgorkbo/42mMyi2XIyyiUqVNbnOVEAoO7h
g/YuoS+TIeZMlzdtU4cVvVAL
=qcHs
-----END PGP SIGNATURE-----

On Apr 2, 9:53*pm, MGFoster <m... (AT) privacy (DOT) com> wrote:
That worked. When I use the driver "SQL Server" v2000.85.1132.00, the
VARCHAR column gets interpreted correctly as a Memo. From this, I
infer that the problem is entirely with the "SQL Native Client"
driver, and I should stop using it. From the version numbers of those
two drivers, I had assumed that "SQL Native Client" was the
appropriate one to use with SQL Server 2005. Now I know better.

Thanks for doing the experiment for me.

-TC

existential.philosophy (AT) gmail (DOT) com wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I did some further cursory research on the Native Client and I'm not
sure using the SQL Server v2000..... is the best when using SQL 2005.
Here's a link to several papers, forums & newsgroups on the SQL Native
Client. Perhaps they will shed more light on the proper driver to use
w/ SQL 2005.

http://msdn.microsoft.com/en-us/data/aa937733.aspx

HTH,

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSdY0mIechKqOuFEgEQKJpwCeJNQpBVG1lYGSZ9BKd4fQbm r+9dwAoIxG
vCMlcuQqpqZp2Xf0wmCvSXeZ
=xt7H
-----END PGP SIGNATURE-----