DSN errors; DSN-Less doesn't

I'm dealing with a problem that occurs when I return a field (typeof nText) via the DSN (in VB6) but is solved when I use a connection string that includes: Provider=SQLOLEDB

Runtime error '-214217887 (80040e21)':

The field in question holds HTML content - small to moderate sized pages. When I use Query Analyzer to see how it returns data it runs without errors but truncates the field in question...the output stream simply stops.

As I said....I've found the right combination of ConnectString parameters but I'd like to better understand how it is that a DSN connection is not working. For some reason I'm thinking it should be _more stable/verbose. Also, the wizard that creates the DSN starts off by asking which provider to use. (version on win98 client, iis4/nt host)

Who is Participating?
simonsabinConnect With a Mentor Commented:
The DSN connection is probably using the MSDASQL provider using the SQL SERVER driver part. To use the SQLOLEDB provider you need to use a UDL.
If you go to explorer and right click a folder, and select the new option. One of the things you can create is a Microsoft Data Link (a UDL).

I believe no I had a similar problem with older versions of MDAC (2.0 I think).
juststeveAuthor Commented:
Thankx simon...let me drill on this just a bit deeper. As regards what I'm seeing happen from Query Analyzer...the truncating of the long text block...is that an error condition? (even though none's reported?)

Unless I'm mistaken, EM & QA use OBDC instead of OLE DB - does that account for the difference in what's returned?

thankx again.
The truncation is an environment variable called TEXTSIZE which limits the size of text fields in ISQLW

try the following
reate table test1 (col1 text)
insert into test1 values ('123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890')
set textsize 100
select * from test1
set textsize 500
select * from test1
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.