Solved

DSN errors; DSN-Less doesn't

Posted on 2000-03-31
3
392 Views
Last Modified: 2008-03-03
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 3.70.06.90 on win98 client, iis4/nt host)

thankx
--steve...
0
Comment
Question by:juststeve
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
simonsabin earned 50 total points
ID: 2673389
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).
0
 

Author Comment

by:juststeve
ID: 2673717
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.
0
 
LVL 7

Expert Comment

by:simonsabin
ID: 2679220
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)
go
insert into test1 values ('123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890123456789012456789012345678901234567890')
go
set textsize 100
select * from test1
set textsize 500
select * from test1
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need Counts 11 43
SQL Backup skipping a few tables 7 28
c# code 19 61
SQL index creation taking an abnormally long time 2 20
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now