ADODB.Field error "800a0bb9" - Working with SQL Server ntext fields

I can't get past this one. I am trying to import an ntext column from
one SQL Server table to another.

Set RS = Conn.Execute("SELECT * FROM COLL")

do while not RS.EOF
Set RSOut = Conn.Execute("SELECT ImageNotes FROM Images WHERE
(Inventory='" & RS("Inventory") & "')")
if not RSOut.EOF then
lngFS = RS("Notes").ActualSize
if (lngFS) then
strTxt = RS.Fields.Item("Notes").GetChunk(lngFS)
RSOut("ImageNotes").AppendChunk strTxt
end if
end if
RS.MoveNext
loop

MY CODE DIES AT THE CALL TO GETCHUNK

I get the following error:

ADODB.Field error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

Both columns are of the type ntext in both tables.

I can't figure out what to try next.

Help!

Re: ADODB.Field error "800a0bb9" - Working with SQL Server ntext fields

Derek Erb wrote:
> I can't get past this one. I am trying to import an ntext column from
> one SQL Server table to another.
>
> Set RS = Conn.Execute("SELECT * FROM COLL")

This is probably the problem. You should avoid using selstar (Select *).
Always name the columns you are retrieving. However, see below for a better
idea.

>
> do while not RS.EOF
> Set RSOut = Conn.Execute("SELECT ImageNotes FROM Images WHERE
> (Inventory='" & RS("Inventory") & "')")
> if not RSOut.EOF then
> lngFS = RS("Notes").ActualSize
> if (lngFS) then
> strTxt = RS.Fields.Item("Notes").GetChunk(lngFS)
> RSOut("ImageNotes").AppendChunk strTxt
> end if
> end if
> RS.MoveNext
> loop
>
> MY CODE DIES AT THE CALL TO GETCHUNK
>
> I get the following error:
>
> ADODB.Field error '800a0bb9'
>
> Arguments are of the wrong type, are out of acceptable range, or are
> in conflict with one another.
>
> Both columns are of the type ntext in both tables.
>
> I can't figure out what to try next.
>
> Help!

Are both these tables on the same SQL Server box? If so, you can use an
UPDATE statement, even if the tables are in different databases.:

sSQL = "UPDATE i SET ImageNotes = c.Notes " & _
"FROM Images i INNER JOIN COLL c ON i.Inventory=c.Inventory"


Or, the more ANSI-compliant version:

sSQL = "UPDATE Images SET ImageNotes = (SELECT " & _
Notes FROM COLL WHERE Inventory = Images.Inventory)"

Either way, simply execute it as:

Conn.Execute sSQL,,1

No need for cursors.

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ADODB.Field error "800a0bb9" - Working with SQL Server ntext fields

"Bob Barrows [MVP]" wrote in message
news:e$y$DKMXFHA.616@TK2MSFTNGP12.phx.gbl...
> Derek Erb wrote:
>> I can't get past this one. I am trying to import an ntext column from
>> one SQL Server table to another.
>>
>> Set RS = Conn.Execute("SELECT * FROM COLL")

Not to detract from what Bob said (it's all good advice) but there's another
obvious problem: connection.Execute always returns a firehose
(fwd-only/read-only cursor.) Calling AppendChunk is therefore problematic
on any of that recordset's field objects. (The error that results is, I'd
agree, less than forthcoming.)

-Mark




> This is probably the problem. You should avoid using selstar (Select *).
> Always name the columns you are retrieving. However, see below for a
> better idea.
>
>>
>> do while not RS.EOF
>> Set RSOut = Conn.Execute("SELECT ImageNotes FROM Images WHERE
>> (Inventory='" & RS("Inventory") & "')")
>> if not RSOut.EOF then
>> lngFS = RS("Notes").ActualSize
>> if (lngFS) then
>> strTxt = RS.Fields.Item("Notes").GetChunk(lngFS)
>> RSOut("ImageNotes").AppendChunk strTxt
>> end if
>> end if
>> RS.MoveNext
>> loop
>>
>> MY CODE DIES AT THE CALL TO GETCHUNK
>>
>> I get the following error:
>>
>> ADODB.Field error '800a0bb9'
>>
>> Arguments are of the wrong type, are out of acceptable range, or are
>> in conflict with one another.
>>
>> Both columns are of the type ntext in both tables.
>>
>> I can't figure out what to try next.
>>
>> Help!
>
> Are both these tables on the same SQL Server box? If so, you can use an
> UPDATE statement, even if the tables are in different databases.:
>
> sSQL = "UPDATE i SET ImageNotes = c.Notes " & _
> "FROM Images i INNER JOIN COLL c ON i.Inventory=c.Inventory"
>
>
> Or, the more ANSI-compliant version:
>
> sSQL = "UPDATE Images SET ImageNotes = (SELECT " & _
> Notes FROM COLL WHERE Inventory = Images.Inventory)"
>
> Either way, simply execute it as:
>
> Conn.Execute sSQL,,1
>
> No need for cursors.
>
> HTH,
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Re: ADODB.Field error "800a0bb9" - Working with SQL Server ntext fields

Bob,

Thank you so much for your very helpful and detailed reply.

I definitely like the ANSI-compliant pure SQL version especially as my
tables are all in the same database.

I tried executing "UPDATE Images SET ImageNotes = (SELECT Notes FROM
COLL WHERE (Inventory=Images.Inventory))" and I got the following
error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Driver][SQL Server] The text, ntext, and image
data types are invalid in this subquery or aggregate expression.

I feel like I'm back to stage one in that I know have an excellent SQL
command to do the import but I am still blocking on the fact that my
column is an ntext column.

I truly appreciate your assistance and hope that this is just something
stupid I'm doing wrong.

Thanks.

Re: ADODB.Field error "800a0bb9" - Working with SQL Server ntext fields

Mark J. McGinty wrote:
> "Bob Barrows [MVP]" wrote in message
> news:e$y$DKMXFHA.616@TK2MSFTNGP12.phx.gbl...
>> Derek Erb wrote:
>>> I can't get past this one. I am trying to import an ntext column
>>> from one SQL Server table to another.
>>>
>>> Set RS = Conn.Execute("SELECT * FROM COLL")
>
> Not to detract from what Bob said (it's all good advice) but there's
> another obvious problem: connection.Execute always returns a firehose
> (fwd-only/read-only cursor.) Calling AppendChunk is therefore
> problematic on any of that recordset's field objects. (The error
> that results is, I'd agree, less than forthcoming.)
>

Oops. Good catch. I did miss that one.

Now I need to investigate why the UPDATE statement is failing ...

Bob

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ADODB.Field error "800a0bb9" - Working with SQL Server ntext fields

Derek Erb wrote:
> Bob,
>
> Thank you so much for your very helpful and detailed reply.
>
> I definitely like the ANSI-compliant pure SQL version especially as my
> tables are all in the same database.
>
> I tried executing "UPDATE Images SET ImageNotes = (SELECT Notes FROM
> COLL WHERE (Inventory=Images.Inventory))" and I got the following
> error:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Driver][SQL Server] The text, ntext, and image
> data types are invalid in this subquery or aggregate expression.
>

Well that's a surprise. I'm going to have to try some tests. I was unaware
of this restriction.
[...]

Well, the good news is that the proprietary Transact-SQL syntax works fine:
sSQL = "UPDATE i SET ImageNotes = c.Notes " & _
"FROM Images i INNER JOIN COLL c ON i.Inventory=c.Inventory"

I do not know of any way to allow the pure SQL version to work in SQL
Server. You may wish to try one of the SQL Server groups: I suggest
m.p.sqlserver.programming

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ADODB.Field error "800a0bb9" - Working with SQL Server ntext fields

Derek Erb wrote:
> Bob,
>
> Thank you so much for your very helpful and detailed reply.
>
> I definitely like the ANSI-compliant pure SQL version especially as my
> tables are all in the same database.
>
> I tried executing "UPDATE Images SET ImageNotes = (SELECT Notes FROM
> COLL WHERE (Inventory=Images.Inventory))" and I got the following
> error:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Driver][SQL Server] The text, ntext, and image
> data types are invalid in this subquery or aggregate expression.
>
Oh! I forgot to mention: the restriction against ntext columns in subqueries
IS documented in BOL:

Subquery Rules
A subquery is subject to a number of restrictions:


The ntext, text and image data types are not allowed in the select list of
subqueries.

I had forgotten about that one ...

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: ADODB.Field error "800a0bb9" - Working with SQL Server ntext fields

Bob,

Thank you again for your extremely helpful reply.

The T-SQL version works absolutely perfectly. It certainly doesn't
both me as most of my triggers are in T-SQL as well.

Thank you for your help.