| Groups | Blog | Home |
inetserver asp db : 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!
[quoted text, click to view] "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> 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 [quoted text, click to view] > 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" >
[quoted text, click to view]
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. [quoted text, click to view] > > 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"
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.
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.
[quoted text, click to view]
Mark J. McGinty wrote: > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> 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"
[quoted text, click to view]
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"
[quoted text, click to view]
Derek Erb wrote: Oh! I forgot to mention: the restriction against ntext columns in subqueries > 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. > IS documented in BOL: Subquery Rules A subquery is subject to a number of restrictions: <snip> 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"
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |