Problem saving long string via recordset

Problem saving long string via recordset

Post by Alan Brya » Mon, 31 Jul 2000 04:00:00



I have no idea what I'm doing wrong. I'm trying to save a 400
character long string to a field through the recordset interface. The
VB code snippet is:

rsBuild("Brief") = Left(strBrief, 400)

It fails with the helpful message, "Error occurred" and references
that line number. The string is fine, and the database column is
designed for it.

BUT... If I change the Left value to 255 it works, and fails again
beginning at 256, so I smell a limit somewhere, but I can't find any
such limit documented. VB strings certainly aren't limited to 255
characters...

Any ideas? Suggestions?

 
 
 

Problem saving long string via recordset

Post by Joe » Mon, 31 Jul 2000 04:00:00


And I am assuming that in the database, you have defined the field size to
be at least varchar(404)?

Joe


Quote:> I have no idea what I'm doing wrong. I'm trying to save a 400
> character long string to a field through the recordset interface. The
> VB code snippet is:

> rsBuild("Brief") = Left(strBrief, 400)

> It fails with the helpful message, "Error occurred" and references
> that line number. The string is fine, and the database column is
> designed for it.

> BUT... If I change the Left value to 255 it works, and fails again
> beginning at 256, so I smell a limit somewhere, but I can't find any
> such limit documented. VB strings certainly aren't limited to 255
> characters...

> Any ideas? Suggestions?


 
 
 

Problem saving long string via recordset

Post by Alan Brya » Mon, 31 Jul 2000 04:00:00



>And I am assuming that in the database, you have defined the field size to
>be at least varchar(404)?

Effectively yes; my listing here showed 400, but I've been using 390.

The point is that ANY value in excess of 255 characters fails to
write, regardless of how the column is actually configured in the
table.

I've tried working around this by using the Execute method on the
connection and entering an UPDATE SQL command, but that bombs out with
an error from the ODBC provider that the element in question (the
string in this case) can't be longer than 128 characters, even though
the **IDENTICAL** SQL works perfectly from the SQL Server Query
Analyzer...

Is ANYBODY getting long strings into a SQL Server 7 database through
ADO using VBScript?  This is driving me insane...

>Joe



>> I have no idea what I'm doing wrong. I'm trying to save a 400
>> character long string to a field through the recordset interface. The
>> VB code snippet is:

>> rsBuild("Brief") = Left(strBrief, 400)

>> It fails with the helpful message, "Error occurred" and references
>> that line number. The string is fine, and the database column is
>> designed for it.

>> BUT... If I change the Left value to 255 it works, and fails again
>> beginning at 256, so I smell a limit somewhere, but I can't find any
>> such limit documented. VB strings certainly aren't limited to 255
>> characters...

>> Any ideas? Suggestions?

 
 
 

Problem saving long string via recordset

Post by Chris Brow » Wed, 02 Aug 2000 04:00:00


I'm using SQL 6.5 and ADO 2.5 from both VB and VBScript.  I've found
from experience that SQL 6.5 does not seem to like varchars longer than
255 characters, so ADO may be trying to default string types to this
limit to avoid compatibility problems.

When putting long strings into memo fields in SQL 6.5, I've found it
easier to understand if I create an empty recordset and populate it with
my own field collection using the append method of the recordset's field
collection.  That way you can force the field type using ADO constants.
For example, a varchar is adVarChar (200) and memo fields are
adLongVarChar (201).  I found these values by running some code to open
a recordset with the columns I wanted and looping through
rs.fields.item("Name").Type and looking those up from the URL
http://msdn.microsoft.com/library/psdk/dasdk/mdae8o19.htm

HTH

Chris Brown



> >And I am assuming that in the database, you have defined the field
size to
> >be at least varchar(404)?

> Effectively yes; my listing here showed 400, but I've been using 390.

> The point is that ANY value in excess of 255 characters fails to
> write, regardless of how the column is actually configured in the
> table.

-- snip --

> Is ANYBODY getting long strings into a SQL Server 7 database through
> ADO using VBScript?  This is driving me insane...

 
 
 

Problem saving long string via recordset

Post by Alan Brya » Wed, 02 Aug 2000 04:00:00


On Tue, 1 Aug 2000 11:19:59 +0100, "Chris Brown"


>I'm using SQL 6.5 and ADO 2.5 from both VB and VBScript.  I've found
>from experience that SQL 6.5 does not seem to like varchars longer than
>255 characters, so ADO may be trying to default string types to this
>limit to avoid compatibility problems.

In the final analysis, it was someone else's suggestion to update the
MDAC driver set that fixed the problem. Installing the latest Version
2.1 update allowed it to work like a champ, confirming what another
post said about earlier versions having trouble with varchar fields >
255 characters.

Of course, it would have been nice if I could have found any mention
(anywhere) of this being a known problem, but while Microsoft puts out
tons of information, finding the piece you need is the real challenge.

Thanks for the post.

 
 
 

1. [LONG] Bug / Problem with SQL Server Desktop Edition (roaming profile no longer saved)

This is the description of a problem that costed me a half day of work. If
it can spare you hours of hair pulling...

After having installed SQL Server Desktop Editiion on a NT WS system, I
noticed that the roaming profile of the user account that I used to install
was no longer saved to the network server. It was only saved locally. Each
time I tried to logon this user again, the system told me that my local
profile was newer than my roaming profile (for sure, it was no longer
updated). The problem occured even if the SQL Server service was suhtdown
and even if the service was not launched on startup.

Assuming that the problem was related to the SQL Server services anyway, I
loaded the neat NT Service Manager utility that you can find at
(http://www-rnks.informatik.tu-cottbus.de/~fsch/english/nttools.htm). It
showed me that the SQLServerAgent service was started under the account that
had been used to install. ***Even if this service is shutdown, this account
remains logged on* (this is easy to see by looking at HK_USERS in the
registry)***.

When you logon using this account, logging off will not trigger any backup
of your roaming profile to the server because the account is still
maintained active by the service process. No error is displayed, your
roaming profile is just not saved. What puzzled me is that the account
remains active even if I shutdown both services (MSSQLServer and
SQLServerAgent).

The solution is to absolutely avoid to specify a domain account for both the
MSSQLServer et SQLServerAgent services when installing SQL Server Desktop
Edition. The installation program proposes the current logged on user
account as a default. I think it's better to use the SYSTEM account for both
services.

Patrick Philippot (MainSoft sarl)

http://ourworld.compuserve.com/homepages/mainsoft
tel/fax: +33 (0)1 69 40 94 85

2. TEMPDB and SQL 7

3. saving XML-string via ADO directly into an Access database

4. Good Books?

5. Instert String into a Oracle Long Column via JDBC

6. Problems exporting with OEM (Oracle 7.2.2)

7. dimensions datasources

8. Insert a long value ORA-01704: string literal too long

9. Long, long character string

10. Converting long to string and to convert the unix dates in the string to regular dates

11. Problem saving recordset as XML