|
|
You're in the
inetserver asp db
group:large varchar columns in SQL server
inetserver asp db:
(http://www.aspfaq.com/show.asp?id=2188) which said: -- Depending on the version of your MDAC driver, and the database you are connecting to, these columns can either (a) not show up at all, (b) only show up the first time they're called, or (c) cause 'Unspecified Error', 'Exception Occured'or 'Errors Occurred' runtime errors -- The article mentioned that it would apply to large varchar columns (more than 255 in length) as well as text columns, but I haven't seen this said anywhere else. Would I see this error consistently (on all fields all of the time or on some fields all of the time) or just randomly (on all fields only some of the time or on only some fields some of the time)? Brian
[quoted text, click to view] "brian.lukoff@gmail.com" wrote:
> I recently came across an article > (http://www.aspfaq.com/show.asp?id=2188) which said: > -- > Depending on the version of your MDAC driver, and the database you are > connecting to, these columns can either (a) not show up at all, (b) > only show up the first time they're called, or (c) cause 'Unspecified > Error', 'Exception Occured'or 'Errors Occurred' runtime errors > -- > The article mentioned that it would apply to large varchar columns > (more than 255 in length) as well as text columns, but I haven't seen > this said anywhere else. Would I see this error consistently (on all > fields all of the time or on some fields all of the time) or just > randomly (on all fields only some of the time or on only some fields > some of the time)? > > Brian >
[quoted text, click to view]
"brian.lukoff@gmail.com" wrote: These are very old bugs that were fixed way back in MDAC 2.5 IIRC. If you > I recently came across an article > (http://www.aspfaq.com/show.asp?id=2188) which said: > -- > Depending on the version of your MDAC driver, and the database you are > connecting to, these columns can either (a) not show up at all, (b) > only show up the first time they're called, or (c) cause 'Unspecified > Error', 'Exception Occured'or 'Errors Occurred' runtime errors > -- > The article mentioned that it would apply to large varchar columns > (more than 255 in length) as well as text columns, but I haven't seen > this said anywhere else. Would I see this error consistently (on all > fields all of the time or on some fields all of the time) or just > randomly (on all fields only some of the time or on only some fields > some of the time)? > have a later version of MDAC then this should not be a problem, especially if you exclusively use the native OLE DB provider for SQL Server (SQLOLEDB)
I am trying to diagnose whether this problem affected some web
applications that I had written (and were run) some time ago, so I can't be sure what version of MDAC they were run under. If I see that access to the large varchar (or memo) fields worked at least some of the time, can I be sure that this error didn't afflict my app? (For example, I had users' data stored in a SQL server database that was later retrieved via an ASP script that wrote out the values of the fields (for each record) to a web page; the original SQL server database is gone but we kept the resulting web page. I want to make sure that no data is missing from this web page.) Brian [quoted text, click to view] Bob Barrows wrote:
> "brian.lukoff@gmail.com" wrote: > > > I recently came across an article > > (http://www.aspfaq.com/show.asp?id=2188) which said: > > -- > > Depending on the version of your MDAC driver, and the database you are > > connecting to, these columns can either (a) not show up at all, (b) > > only show up the first time they're called, or (c) cause 'Unspecified > > Error', 'Exception Occured'or 'Errors Occurred' runtime errors > > -- > > The article mentioned that it would apply to large varchar columns > > (more than 255 in length) as well as text columns, but I haven't seen > > this said anywhere else. Would I see this error consistently (on all > > fields all of the time or on some fields all of the time) or just > > randomly (on all fields only some of the time or on only some fields > > some of the time)? > > > These are very old bugs that were fixed way back in MDAC 2.5 IIRC. If you > have a later version of MDAC then this should not be a problem, especially if > you exclusively use the native OLE DB provider for SQL Server (SQLOLEDB) > rather than the older ODBC drivers.
[quoted text, click to view]
brian.lukoff@gmail.com wrote: > I am trying to diagnose whether this problem affected some web > applications that I had written (and were run) some time ago, so I > can't be sure what version of MDAC they were run under. If I see that > access to the large varchar (or memo) fields worked at least some of > the time, can I be sure that this error didn't afflict my app? Hard to say. IIRC, this problem was intermittent. [quoted text, click to view] > (For I don't know how you are going to do that without access to the original > example, I had users' data stored in a SQL server database that was > later retrieved via an ASP script that wrote out the values of the > fields (for each record) to a web page; the original SQL server > database is gone but we kept the resulting web page. I want to make > sure that no data is missing from this web page.) > > data ... You have no backups available? -- 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]
>I am trying to diagnose whether this problem affected some web > applications that I had written (and were run) some time ago, so I > can't be sure what version of MDAC they were run under. If I see that > access to the large varchar (or memo) fields worked at least some of > the time, can I be sure that this error didn't afflict my app? (For > example, I had users' data stored in a SQL server database that was > later retrieved via an ASP script that wrote out the values of the > fields (for each record) to a web page; the original SQL server > database is gone but we kept the resulting web page. I want to make > sure that no data is missing from this web page.) As Bob suggests, it's going to be really hard to tell. Do you still have the ASP code that retrieved the data at least? If so, check to make sure that the code (a) did not use select *, (b) named memo columns last, and (c) stuffed the value into a variable before writing it out. If some of those things weren't done, then if MDAC was old and bad enough (do you have a rough timeframe when the page was produced?), you may have had empty strings. Bob also mentioned that the problem was intermittent, so it could have gone one way or the other, and I think it would be impossible to know for sure, even if you can collect all of the data above, unless you can find the data source from somewhere and compare. A
The code did use SELECT * and did not write the values to variables
before outputting. Is there a version of MDAC that these bugs are completely gone from, regardless of how I am connecting to the database, querying the database, or storing the values? This page was produced around 4/2005. Brian [quoted text, click to view] Aaron Bertrand [SQL Server MVP] wrote:
> >I am trying to diagnose whether this problem affected some web > > applications that I had written (and were run) some time ago, so I > > can't be sure what version of MDAC they were run under. If I see that > > access to the large varchar (or memo) fields worked at least some of > > the time, can I be sure that this error didn't afflict my app? (For > > example, I had users' data stored in a SQL server database that was > > later retrieved via an ASP script that wrote out the values of the > > fields (for each record) to a web page; the original SQL server > > database is gone but we kept the resulting web page. I want to make > > sure that no data is missing from this web page.) > > As Bob suggests, it's going to be really hard to tell. Do you still have > the ASP code that retrieved the data at least? If so, check to make sure > that the code (a) did not use select *, (b) named memo columns last, and (c) > stuffed the value into a variable before writing it out. If some of those > things weren't done, then if MDAC was old and bad enough (do you have a > rough timeframe when the page was produced?), you may have had empty > strings. Bob also mentioned that the problem was intermittent, so it could > have gone one way or the other, and I think it would be impossible to know > for sure, even if you can collect all of the data above, unless you can find > the data source from somewhere and compare. > > A
Also, how often would this bug appear? (Once every 5 accesses to a
large varchar field? Once every 50?) The ASP page that collected this data accessed large varchar fields itself to get the text that users were presented with, and in my testing I never saw the contents of large varchar fields not appearing. Brian [quoted text, click to view] brian.lukoff@gmail.com wrote:
> The code did use SELECT * and did not write the values to variables > before outputting. Is there a version of MDAC that these bugs are > completely gone from, regardless of how I am connecting to the > database, querying the database, or storing the values? This page was > produced around 4/2005. > > Brian > > Aaron Bertrand [SQL Server MVP] wrote: > > >I am trying to diagnose whether this problem affected some web > > > applications that I had written (and were run) some time ago, so I > > > can't be sure what version of MDAC they were run under. If I see that > > > access to the large varchar (or memo) fields worked at least some of > > > the time, can I be sure that this error didn't afflict my app? (For > > > example, I had users' data stored in a SQL server database that was > > > later retrieved via an ASP script that wrote out the values of the > > > fields (for each record) to a web page; the original SQL server > > > database is gone but we kept the resulting web page. I want to make > > > sure that no data is missing from this web page.) > > > > As Bob suggests, it's going to be really hard to tell. Do you still have > > the ASP code that retrieved the data at least? If so, check to make sure > > that the code (a) did not use select *, (b) named memo columns last, and (c) > > stuffed the value into a variable before writing it out. If some of those > > things weren't done, then if MDAC was old and bad enough (do you have a > > rough timeframe when the page was produced?), you may have had empty > > strings. Bob also mentioned that the problem was intermittent, so it could > > have gone one way or the other, and I think it would be impossible to know > > for sure, even if you can collect all of the data above, unless you can find > > the data source from somewhere and compare. > > > > A
What version of MDAC and/or SQL Server (or whatever else) can I be sure
this is definitely fixed in (regardless of the manner in which I connect to the database, the query strings I use, whether I write out the data to the page directly from the rs(field) syntax or not, etc.)? Brian [quoted text, click to view] Aaron Bertrand [SQL Server MVP] wrote:
> > Also, how often would this bug appear? (Once every 5 accesses to a > > large varchar field? Once every 50?) > > There was no formula, it was intermittent. Maybe every 5, maybe every 50, > really hard to say. > > A
[quoted text, click to view]
> Also, how often would this bug appear? (Once every 5 accesses to a > large varchar field? Once every 50?) There was no formula, it was intermittent. Maybe every 5, maybe every 50, really hard to say. A
Bob--
Do you know of a reference that would be able to tell me definitively what version of MDAC fixed these bugs? Brian [quoted text, click to view] Bob Barrows wrote:
> "brian.lukoff@gmail.com" wrote: > > > I recently came across an article > > (http://www.aspfaq.com/show.asp?id=2188) which said: > > -- > > Depending on the version of your MDAC driver, and the database you are > > connecting to, these columns can either (a) not show up at all, (b) > > only show up the first time they're called, or (c) cause 'Unspecified > > Error', 'Exception Occured'or 'Errors Occurred' runtime errors > > -- > > The article mentioned that it would apply to large varchar columns > > (more than 255 in length) as well as text columns, but I haven't seen > > this said anywhere else. Would I see this error consistently (on all > > fields all of the time or on some fields all of the time) or just > > randomly (on all fields only some of the time or on only some fields > > some of the time)? > > > These are very old bugs that were fixed way back in MDAC 2.5 IIRC. If you > have a later version of MDAC then this should not be a problem, especially if > you exclusively use the native OLE DB provider for SQL Server (SQLOLEDB) > rather than the older ODBC drivers.
Sorry, I never kept track of that. I just always made sure my servers had
the latest version available. [quoted text, click to view] <brian.lukoff@gmail.com> wrote in message news:1150677556.754703.283160@g10g2000cwb.googlegroups.com... > What version of MDAC and/or SQL Server (or whatever else) can I be sure > this is definitely fixed in (regardless of the manner in which I > connect to the database, the query strings I use, whether I write out > the data to the page directly from the rs(field) syntax or not, etc.)? > > Brian > > Aaron Bertrand [SQL Server MVP] wrote: >> > Also, how often would this bug appear? (Once every 5 accesses to a >> > large varchar field? Once every 50?) >> >> There was no formula, it was intermittent. Maybe every 5, maybe every >> 50, >> really hard to say. >> >> A >
According to http://support.microsoft.com/kb/175239/EN-US/
"This behavior is by design. However, it does not occur when using Mdac 2.1sp2 or later with the 3.7 driver or later for SQL Server." So this is confusing. On the one hand they are saying it's not really a bug, but intended behavior. On the other they are implying that it was fixed in mdac2.1 sp2. I suspect that behavior was built into the earlier driver to discourage the use of Text/Memo columns (making it "by-design"), but as hardware and bandwidth got cheaper, they modified the behavior in that service pack. However, this is only speculation on my part. The other confusing thing is that later versions of mdac appear in the "Applies To" list. The bottom line: without scrutinizing the data, there is no way to verify that the truncation did not occur. The pain of doing this can be decreased by using a query to filter out the rows where the length of data in that column is > 255. Actually, since the data was truncated to 255, you might want to look for rows where the length of the data in that column is exactly 255 and see if it appears that truncation did occur. [quoted text, click to view] brian.lukoff@gmail.com wrote: > Bob-- > > Do you know of a reference that would be able to tell me definitively > what version of MDAC fixed these bugs? > > Brian > > Bob Barrows wrote: >> "brian.lukoff@gmail.com" wrote: >> >>> I recently came across an article >>> (http://www.aspfaq.com/show.asp?id=2188) which said: >>> -- >>> Depending on the version of your MDAC driver, and the database you >>> are connecting to, these columns can either (a) not show up at all, >>> (b) >>> only show up the first time they're called, or (c) cause >>> 'Unspecified Error', 'Exception Occured'or 'Errors Occurred' >>> runtime errors -- >>> The article mentioned that it would apply to large varchar columns >>> (more than 255 in length) as well as text columns, but I haven't >>> seen >>> this said anywhere else. Would I see this error consistently (on >>> all fields all of the time or on some fields all of the time) or >>> just >>> randomly (on all fields only some of the time or on only some fields >>> some of the time)? >>> >> These are very old bugs that were fixed way back in MDAC 2.5 IIRC. >> If you have a later version of MDAC then this should not be a >> problem, especially if you exclusively use the native OLE DB >> provider for SQL Server (SQLOLEDB) rather than the older ODBC >> drivers. -- 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"
Thanks for finding this! However, I'm a little confused--this seems
different than the bug we were talking about. I thought that the bug was that retrieving the contents of a large varchar field using rs(...) syntax can simply return nothing at all, not that an error occurred. Also, I didn't know that truncation was part of the bug. Brian [quoted text, click to view] Bob Barrows [MVP] wrote:
> According to http://support.microsoft.com/kb/175239/EN-US/ > > "This behavior is by design. However, it does not occur when using Mdac > 2.1sp2 or later with the 3.7 driver or later for SQL Server." > > So this is confusing. On the one hand they are saying it's not really a bug, > but intended behavior. On the other they are implying that it was fixed in > mdac2.1 sp2. I suspect that behavior was built into the earlier driver to > discourage the use of Text/Memo columns (making it "by-design"), but as > hardware and bandwidth got cheaper, they modified the behavior in that > service pack. > > However, this is only speculation on my part. > > The other confusing thing is that later versions of mdac appear in the > "Applies To" list. > > The bottom line: without scrutinizing the data, there is no way to verify > that the truncation did not occur. The pain of doing this can be decreased > by using a query to filter out the rows where the length of data in that > column is > 255. Actually, since the data was truncated to 255, you might > want to look for rows where the length of the data in that column is exactly > 255 and see if it appears that truncation did occur. > > brian.lukoff@gmail.com wrote: > > Bob-- > > > > Do you know of a reference that would be able to tell me definitively > > what version of MDAC fixed these bugs? > > > > Brian > > > > Bob Barrows wrote: > >> "brian.lukoff@gmail.com" wrote: > >> > >>> I recently came across an article > >>> (http://www.aspfaq.com/show.asp?id=2188) which said: > >>> -- > >>> Depending on the version of your MDAC driver, and the database you > >>> are connecting to, these columns can either (a) not show up at all, > >>> (b) > >>> only show up the first time they're called, or (c) cause > >>> 'Unspecified Error', 'Exception Occured'or 'Errors Occurred' > >>> runtime errors -- > >>> The article mentioned that it would apply to large varchar columns > >>> (more than 255 in length) as well as text columns, but I haven't > >>> seen > >>> this said anywhere else. Would I see this error consistently (on > >>> all fields all of the time or on some fields all of the time) or > >>> just > >>> randomly (on all fields only some of the time or on only some fields > >>> some of the time)? > >>> > >> These are very old bugs that were fixed way back in MDAC 2.5 IIRC. > >> If you have a later version of MDAC then this should not be a > >> problem, especially if you exclusively use the native OLE DB > >> provider for SQL Server (SQLOLEDB) rather than the older ODBC > >> drivers. > > -- > 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"
You're right. I had a couple problems mixed up. It can't hurt to check for
truncation, but that won't help with the other issue you are trying to detect. In fact, without the source data, nothing will. There is nothing else about these issues that I can help you with. You now know all that I know about them. Sorry. [quoted text, click to view] brian.lukoff@gmail.com wrote: > Thanks for finding this! However, I'm a little confused--this seems > different than the bug we were talking about. I thought that the bug > was that retrieving the contents of a large varchar field using > rs(...) syntax can simply return nothing at all, not that an error > occurred. > Also, I didn't know that truncation was part of the bug. > > Brian > > Bob Barrows [MVP] wrote: >> According to http://support.microsoft.com/kb/175239/EN-US/ >> >> "This behavior is by design. However, it does not occur when using >> Mdac >> 2.1sp2 or later with the 3.7 driver or later for SQL Server." >> >> So this is confusing. On the one hand they are saying it's not >> really a bug, but intended behavior. On the other they are implying >> that it was fixed in mdac2.1 sp2. I suspect that behavior was built >> into the earlier driver to discourage the use of Text/Memo columns >> (making it "by-design"), but as hardware and bandwidth got cheaper, >> they modified the behavior in that service pack. >> >> However, this is only speculation on my part. >> >> The other confusing thing is that later versions of mdac appear in >> the "Applies To" list. >> >> The bottom line: without scrutinizing the data, there is no way to >> verify that the truncation did not occur. The pain of doing this can >> be decreased by using a query to filter out the rows where the >> length of data in that column is > 255. Actually, since the data was >> truncated to 255, you might want to look for rows where the length >> of the data in that column is exactly 255 and see if it appears that >> truncation did occur. >> >> brian.lukoff@gmail.com wrote: >>> Bob-- >>> >>> Do you know of a reference that would be able to tell me >>> definitively what version of MDAC fixed these bugs? >>> >>> Brian >>> >>> Bob Barrows wrote: >>>> "brian.lukoff@gmail.com" wrote: >>>> >>>>> I recently came across an article >>>>> (http://www.aspfaq.com/show.asp?id=2188) which said: >>>>> -- >>>>> Depending on the version of your MDAC driver, and the database you >>>>> are connecting to, these columns can either (a) not show up at >>>>> all, (b) >>>>> only show up the first time they're called, or (c) cause >>>>> 'Unspecified Error', 'Exception Occured'or 'Errors Occurred' >>>>> runtime errors -- >>>>> The article mentioned that it would apply to large varchar columns >>>>> (more than 255 in length) as well as text columns, but I haven't >>>>> seen >>>>> this said anywhere else. Would I see this error consistently (on >>>>> all fields all of the time or on some fields all of the time) or >>>>> just >>>>> randomly (on all fields only some of the time or on only some >>>>> fields some of the time)? >>>>> >>>> These are very old bugs that were fixed way back in MDAC 2.5 IIRC. >>>> If you have a later version of MDAC then this should not be a >>>> problem, especially if you exclusively use the native OLE DB >>>> provider for SQL Server (SQLOLEDB) rather than the older ODBC >>>> drivers. >> >> -- >> 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" -- 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"
Thanks for your help so far--I appreciate all of the time you've spent
on this thread. Going forward, how can I ensure that code I write now won't be affected by this bug? Brian [quoted text, click to view] Bob Barrows [MVP] wrote:
> You're right. I had a couple problems mixed up. It can't hurt to check for > truncation, but that won't help with the other issue you are trying to > detect. In fact, without the source data, nothing will. > > There is nothing else about these issues that I can help you with. You now > know all that I know about them. > > Sorry. > > brian.lukoff@gmail.com wrote: > > Thanks for finding this! However, I'm a little confused--this seems > > different than the bug we were talking about. I thought that the bug > > was that retrieving the contents of a large varchar field using > > rs(...) syntax can simply return nothing at all, not that an error > > occurred. > > Also, I didn't know that truncation was part of the bug. > > > > Brian > > > > Bob Barrows [MVP] wrote: > >> According to http://support.microsoft.com/kb/175239/EN-US/ > >> > >> "This behavior is by design. However, it does not occur when using > >> Mdac > >> 2.1sp2 or later with the 3.7 driver or later for SQL Server." > >> > >> So this is confusing. On the one hand they are saying it's not > >> really a bug, but intended behavior. On the other they are implying > >> that it was fixed in mdac2.1 sp2. I suspect that behavior was built > >> into the earlier driver to discourage the use of Text/Memo columns > >> (making it "by-design"), but as hardware and bandwidth got cheaper, > >> they modified the behavior in that service pack. > >> > >> However, this is only speculation on my part. > >> > >> The other confusing thing is that later versions of mdac appear in > >> the "Applies To" list. > >> > >> The bottom line: without scrutinizing the data, there is no way to > >> verify that the truncation did not occur. The pain of doing this can > >> be decreased by using a query to filter out the rows where the > >> length of data in that column is > 255. Actually, since the data was > >> truncated to 255, you might want to look for rows where the length > >> of the data in that column is exactly 255 and see if it appears that > >> truncation did occur. > >> > >> brian.lukoff@gmail.com wrote: > >>> Bob-- > >>> > >>> Do you know of a reference that would be able to tell me > >>> definitively what version of MDAC fixed these bugs? > >>> > >>> Brian > >>> > >>> Bob Barrows wrote: > >>>> "brian.lukoff@gmail.com" wrote: > >>>> > >>>>> I recently came across an article > >>>>> (http://www.aspfaq.com/show.asp?id=2188) which said: > >>>>> -- > >>>>> Depending on the version of your MDAC driver, and the database you > >>>>> are connecting to, these columns can either (a) not show up at > >>>>> all, (b) > >>>>> only show up the first time they're called, or (c) cause > >>>>> 'Unspecified Error', 'Exception Occured'or 'Errors Occurred' > >>>>> runtime errors -- > >>>>> The article mentioned that it would apply to large varchar columns > >>>>> (more than 255 in length) as well as text columns, but I haven't > >>>>> seen > >>>>> this said anywhere else. Would I see this error consistently (on > >>>>> all fields all of the time or on some fields all of the time) or > >>>>> just > >>>>> randomly (on all fields only some of the time or on only some > >>>>> fields some of the time)? > >>>>> > >>>> These are very old bugs that were fixed way back in MDAC 2.5 IIRC. > >>>> If you have a later version of MDAC then this should not be a > >>>> problem, especially if you exclusively use the native OLE DB > >>>> provider for SQL Server (SQLOLEDB) rather than the older ODBC > >>>> drivers. > >> > >> -- > >> 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" > > -- > 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"
By using the SQLOLEDB provider (http://www.aspfaq.com/show.asp?id=2126),
this behavior should not appear at all. I've been using large varchar columns for years with no problems. However, it does not hurt to follow the other recommendations made in that article (avoid selstar and immediately assign the contents of your large varchar fields to variables), because, not only do they prevent this behavior, they also cause you to write more maintainable and efficient code. The recommendation in the KB article to create lots of 255-length varchar columns should only be done if you are forced to use a pre MDAC 2.1 sp2 version of ODBC. [quoted text, click to view] brian.lukoff@gmail.com wrote: > Thanks for your help so far--I appreciate all of the time you've spent > on this thread. Going forward, how can I ensure that code I write now > won't be affected by this bug? > > Brian > > Bob Barrows [MVP] wrote: >> You're right. I had a couple problems mixed up. It can't hurt to >> check for truncation, but that won't help with the other issue you >> are trying to detect. In fact, without the source data, nothing will. >> >> There is nothing else about these issues that I can help you with. >> You now know all that I know about them. >> >> Sorry. >> >> brian.lukoff@gmail.com wrote: >>> Thanks for finding this! However, I'm a little confused--this seems >>> different than the bug we were talking about. I thought that the >>> bug was that retrieving the contents of a large varchar field using >>> rs(...) syntax can simply return nothing at all, not that an error >>> occurred. >>> Also, I didn't know that truncation was part of the bug. >>> >>> Brian >>> >>> Bob Barrows [MVP] wrote: >>>> According to http://support.microsoft.com/kb/175239/EN-US/ >>>> >>>> "This behavior is by design. However, it does not occur when using >>>> Mdac >>>> 2.1sp2 or later with the 3.7 driver or later for SQL Server." >>>> >>>> So this is confusing. On the one hand they are saying it's not >>>> really a bug, but intended behavior. On the other they are implying >>>> that it was fixed in mdac2.1 sp2. I suspect that behavior was built >>>> into the earlier driver to discourage the use of Text/Memo columns >>>> (making it "by-design"), but as hardware and bandwidth got cheaper, >>>> they modified the behavior in that service pack. >>>> >>>> However, this is only speculation on my part. >>>> >>>> The other confusing thing is that later versions of mdac appear in >>>> the "Applies To" list. >>>> >>>> The bottom line: without scrutinizing the data, there is no way to >>>> verify that the truncation did not occur. The pain of doing this >>>> can be decreased by using a query to filter out the rows where the >>>> length of data in that column is > 255. Actually, since the data >>>> was truncated to 255, you might want to look for rows where the >>>> length >>>> of the data in that column is exactly 255 and see if it appears >>>> that truncation did occur. >>>> >>>> brian.lukoff@gmail.com wrote: >>>>> Bob-- >>>>> >>>>> Do you know of a reference that would be able to tell me >>>>> definitively what version of MDAC fixed these bugs? >>>>> >>>>> Brian >>>>> >>>>> Bob Barrows wrote: >>>>>> "brian.lukoff@gmail.com" wrote: >>>>>> >>>>>>> I recently came across an article >>>>>>> (http://www.aspfaq.com/show.asp?id=2188) which said: >>>>>>> -- >>>>>>> Depending on the version of your MDAC driver, and the database >>>>>>> you are connecting to, these columns can either (a) not show up >>>>>>> at >>>>>>> all, (b) >>>>>>> only show up the first time they're called, or (c) cause >>>>>>> 'Unspecified Error', 'Exception Occured'or 'Errors Occurred' >>>>>>> runtime errors -- >>>>>>> The article mentioned that it would apply to large varchar >>>>>>> columns (more than 255 in length) as well as text columns, but >>>>>>> I haven't seen >>>>>>> this said anywhere else. Would I see this error consistently >>>>>>> (on all fields all of the time or on some fields all of the >>>>>>> time) or just >>>>>>> randomly (on all fields only some of the time or on only some >>>>>>> fields some of the time)? >>>>>>> >>>>>> These are very old bugs that were fixed way back in MDAC 2.5 >>>>>> IIRC. If you have a later version of MDAC then this should not >>>>>> be a problem, especially if you exclusively use the native OLE DB >>>>>> provider for SQL Server (SQLOLEDB) rather than the older ODBC >>>>>> drivers. >>>> >>>> -- >>>> 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" >> >> -- >> 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" -- 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"
So with SQLOLEDB the problem won't occur regardless of MDAC version?
Does the sample apply for using Microsoft.Jet.OLEDB.4.0 to connect to an Access database? How about using a DSN to connect to a SQL server? Brian [quoted text, click to view] Bob Barrows [MVP] wrote:
> By using the SQLOLEDB provider (http://www.aspfaq.com/show.asp?id=2126), > this behavior should not appear at all. I've been using large varchar > columns for years with no problems. > > However, it does not hurt to follow the other recommendations made in that > article (avoid selstar and immediately assign the contents of your large > varchar fields to variables), because, not only do they prevent this > behavior, they also cause you to write more maintainable and efficient code. > > The recommendation in the KB article to create lots of 255-length varchar > columns should only be done if you are forced to use a pre MDAC 2.1 sp2 > version of ODBC. > > > brian.lukoff@gmail.com wrote: > > Thanks for your help so far--I appreciate all of the time you've spent > > on this thread. Going forward, how can I ensure that code I write now > > won't be affected by this bug? > > > > Brian > > > > Bob Barrows [MVP] wrote: > >> You're right. I had a couple problems mixed up. It can't hurt to > >> check for truncation, but that won't help with the other issue you > >> are trying to detect. In fact, without the source data, nothing will. > >> > >> There is nothing else about these issues that I can help you with. > >> You now know all that I know about them. > >> > >> Sorry. > >> > >> brian.lukoff@gmail.com wrote: > >>> Thanks for finding this! However, I'm a little confused--this seems > >>> different than the bug we were talking about. I thought that the > >>> bug was that retrieving the contents of a large varchar field using > >>> rs(...) syntax can simply return nothing at all, not that an error > >>> occurred. > >>> Also, I didn't know that truncation was part of the bug. > >>> > >>> Brian > >>> > >>> Bob Barrows [MVP] wrote: > >>>> According to http://support.microsoft.com/kb/175239/EN-US/ > >>>> > >>>> "This behavior is by design. However, it does not occur when using > >>>> Mdac > >>>> 2.1sp2 or later with the 3.7 driver or later for SQL Server." > >>>> > >>>> So this is confusing. On the one hand they are saying it's not > >>>> really a bug, but intended behavior. On the other they are implying > >>>> that it was fixed in mdac2.1 sp2. I suspect that behavior was built > >>>> into the earlier driver to discourage the use of Text/Memo columns > >>>> (making it "by-design"), but as hardware and bandwidth got cheaper, > >>>> they modified the behavior in that service pack. > >>>> > >>>> However, this is only speculation on my part. > >>>> > >>>> The other confusing thing is that later versions of mdac appear in > >>>> the "Applies To" list. > >>>> > >>>> The bottom line: without scrutinizing the data, there is no way to > >>>> verify that the truncation did not occur. The pain of doing this > >>>> can be decreased by using a query to filter out the rows where the > >>>> length of data in that column is > 255. Actually, since the data > >>>> was truncated to 255, you might want to look for rows where the > >>>> length > >>>> of the data in that column is exactly 255 and see if it appears > >>>> that truncation did occur. > >>>> > >>>> brian.lukoff@gmail.com wrote: > >>>>> Bob-- > >>>>> > >>>>> Do you know of a reference that would be able to tell me > >>>>> definitively what version of MDAC fixed these bugs? > >>>>> > >>>>> Brian > >>>>> > >>>>> Bob Barrows wrote: > >>>>>> "brian.lukoff@gmail.com" wrote: > >>>>>> > >>>>>>> I recently came across an article > >>>>>>> (http://www.aspfaq.com/show.asp?id=2188) which said: > >>>>>>> -- > >>>>>>> Depending on the version of your MDAC driver, and the database > >>>>>>> you are connecting to, these columns can either (a) not show up > >>>>>>> at > >>>>>>> all, (b) > >>>>>>> only show up the first time they're called, or (c) cause > >>>>>>> 'Unspecified Error', 'Exception Occured'or 'Errors Occurred' > >>>>>>> runtime errors -- > >>>>>>> The article mentioned that it would apply to large varchar > >>>>>>> columns (more than 255 in length) as well as text columns, but > >>>>>>> I haven't seen > >>>>>>> this said anywhere else. Would I see this error consistently > >>>>>>> (on all fields all of the time or on some fields all of the > >>>>>>> time) or just > >>>>>>> randomly (on all fields only some of the time or on only some > >>>>>>> fields some of the time)? > >>>>>>> > >>>>>> These are very old bugs that were fixed way back in MDAC 2.5 > >>>>>> IIRC. If you have a later version of MDAC then this should not > >>>>>> be a problem, especially if you exclusively use the native OLE DB > >>>>>> provider for SQL Server (SQLOLEDB) rather than the older ODBC > >>>>>> drivers. > >>>> > >>>> -- > >>>> 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" > >> > >> -- > >> 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" > > -- > 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]
brian.lukoff@gmail.com wrote: > So with SQLOLEDB the problem won't occur regardless of MDAC version? AFAIK. I know that it has never happened to me. There was a thread yesterday in asp.general that might cast some doubt on the assertion, except that the poster was so vague about so many things that I for one will never know what really solved (or failed to solve) his problem. Anyways, several years ago when was I was still learning, I encountered this problem while using an ODBC DSN. I was advised to switch to OLE DB, upon which the problem never occurred again. But that's just my experience with it. If you're looking for someone to say that this will absolutely prevent the symptoms* then you're in the wrong place. I doubt anyone will be able to provide that reassurance. I think using OLE DB [quoted text, click to view] > Does the sample apply for using Microsoft.Jet.OLEDB.4.0 to connect to > an Access database? Given that I never used memo fields in Jet in the few asp applications I wrote that used Jet, I really can't say. The only application I can remember creating that used Memo fields was an Access application that used DAO, not ADO, so I have no experience to say one way or the other. The Jet OLE DB provider eliminates a software layer (ODBC) between your application and the database, so that's a pretty good reason to use it. [quoted text, click to view] > How about using a DSN to connect to a SQL server? DSN = ODBC. Background: ADO ALWAYS uses an OLE DB provider to communicate with the database. In the case of using an ODBC DSN, the provider used is called MSDASQL**, aka the "Microsoft OLE DB Provider for ODBC Databases". This is the default provider, i.e., if no provider is specified in the connection string, it is assumed you are using ODBC and the MSDASQL provider is used to communicate with the ODBC driver you specified. So, instead of this: Application -> OLE DB library -> database engine you get this: Application -> OLE DB library -> ODBC library -> database engine Extra software layer leaves more opportunities for things to go wrong. Anyways, bottom line: Use SQLOLEDB Assign the text./memo field values to local variables as quickly as possible, which has the added benefit that you can get rid of your recordset, as well as closing your connection, sooner in the process than you normally would. Your goal should be to keep the time that recordsets and connections are open to a minimum, which allows connection pooling to work at its full efficiency. Bob Barrows *which never occurred with varchar fields that I remember ... I was a little surprised to read that in the aspfaq article **which has been deprecated, BTW ... another reason to stop using ODBC -- 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"
Is the truncation issue another bug that I need to check for? Does
this have the same symptom (i.e., full-length data is in the database, but retrieving it using rs(...) syntax silently truncates to 255 characters)? Brian [quoted text, click to view] Bob Barrows [MVP] wrote:
> You're right. I had a couple problems mixed up. It can't hurt to check for > truncation, but that won't help with the other issue you are trying to > detect. In fact, without the source data, nothing will. > > There is nothing else about these issues that I can help you with. You now > know all that I know about them. > > Sorry. > > brian.lukoff@gmail.com wrote: > > Thanks for finding this! However, I'm a little confused--this seems > > different than the bug we were talking about. I thought that the bug > > was that retrieving the contents of a large varchar field using > > rs(...) syntax can simply return nothing at all, not that an error > > occurred. > > Also, I didn't know that truncation was part of the bug. > > > > Brian > > > > Bob Barrows [MVP] wrote: > >> According to http://support.microsoft.com/kb/175239/EN-US/ > >> > >> "This behavior is by design. However, it does not occur when using > >> Mdac > >> 2.1sp2 or later with the 3.7 driver or later for SQL Server." > >> > >> So this is confusing. On the one hand they are saying it's not > >> really a bug, but intended behavior. On the other they are implying > >> that it was fixed in mdac2.1 sp2. I suspect that behavior was built > >> into the earlier driver to discourage the use of Text/Memo columns > >> (making it "by-design"), but as hardware and bandwidth got cheaper, > >> they modified the behavior in that service pack. > >> > >> However, this is only speculation on my part. > >> > >> The other confusing thing is that later versions of mdac appear in > >> the "Applies To" list. > >> > >> The bottom line: without scrutinizing the data, there is no way to > >> verify that the truncation did not occur. The pain of doing this can > >> be decreased by using a query to filter out the rows where the > >> length of data in that column is > 255. Actually, since the data was > >> truncated to 255, you might want to look for rows where the length > >> of the data in that column is exactly 255 and see if it appears that > >> truncation did occur. > >> > >> brian.lukoff@gmail.com wrote: > >>> Bob-- > >>> > >>> Do you know of a reference that would be able to tell me > >>> definitively what version of MDAC fixed these bugs? > >>> > >>> Brian > >>> > >>> Bob Barrows wrote: > >>>> "brian.lukoff@gmail.com" wrote: > >>>> > >>>>> I recently came across an article > >>>>> (http://www.aspfaq.com/show.asp?id=2188) which said: > >>>>> -- > >>>>> Depending on the version of your MDAC driver, and the database you > >>>>> are connecting to, these columns can either (a) not show up at > >>>>> all, (b) > >>>>> only show up the first time they're called, or (c) cause > >>>>> 'Unspecified Error', 'Exception Occured'or 'Errors Occurred' > >>>>> runtime errors -- > >>>>> The article mentioned that it would apply to large varchar columns > >>>>> (more than 255 in length) as well as text columns, but I haven't > >>>>> seen > >>>>> this said anywhere else. Would I see this error consistently (on > >>>>> all fields all of the time or on some fields all of the time) or > >>>>> just > >>>>> randomly (on all fields only some of the time or on only some > >>>>> fields some of the time)? > >>>>> > >>>> These are very old bugs that were fixed way back in MDAC 2.5 IIRC. > >>>> If you have a later version of MDAC then this should not be a > >>>> problem, especially if you exclusively use the native OLE DB > >>>> provider for SQL Server (SQLOLEDB) rather than the older ODBC > >>>> drivers. > >> > >> -- > >> 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" > > -- > 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"
Obviously, we haven;'t been able to resolve this for you one way or the
other. Since this is really an ADO issue, you might try asking in the ADO newsgroup: microsoft.public.data.ado [quoted text, click to view] brian.lukoff@gmail.com wrote: > Is the truncation issue another bug that I need to check for? Does > this have the same symptom (i.e., full-length data is in the database, > but retrieving it using rs(...) syntax silently truncates to 255 > characters)? > > Brian > > Bob Barrows [MVP] wrote: >> You're right. I had a couple problems mixed up. It can't hurt to >> check for truncation, but that won't help with the other issue you >> are trying to detect. In fact, without the source data, nothing will. >> >> There is nothing else about these issues that I can help you with. >> You now know all that I know about them. >> >> Sorry. >> >> brian.lukoff@gmail.com wrote: >>> Thanks for finding this! However, I'm a little confused--this seems >>> different than the bug we were talking about. I thought that the >>> bug was that retrieving the contents of a large varchar field using >>> rs(...) syntax can simply return nothing at all, not that an error >>> occurred. >>> Also, I didn't know that truncation was part of the bug. >>> >>> Brian >>> >>> Bob Barrows [MVP] wrote: >>>> According to http://support.microsoft.com/kb/175239/EN-US/ >>>> >>>> "This behavior is by design. However, it does not occur when using >>>> Mdac >>>> 2.1sp2 or later with the 3.7 driver or later for SQL Server." >>>> >>>> So this is confusing. On the one hand they are saying it's not >>>> really a bug, but intended behavior. On the other they are implying >>>> that it was fixed in mdac2.1 sp2. I suspect that behavior was built >>>> into the earlier driver to discourage the use of Text/Memo columns >>>> (making it "by-design"), but as hardware and bandwidth got cheaper, >>>> they modified the behavior in that service pack. >>>> >>>> However, this is only speculation on my part. >>>> >>>> The other confusing thing is that later versions of mdac appear in >>>> the "Applies To" list. >>>> >>>> The bottom line: without scrutinizing the data, there is no way to >>>> verify that the truncation did not occur. The pain of doing this >>>> can be decreased by using a query to filter out the rows where the >>>> length of data in that column is > 255. Actually, since the data >>>> was truncated to 255, you might want to look for rows where the >>>> length of the data in that column is exactly 255 and see if it >>>> appears that truncation did occur. >>>> >>>> brian.lukoff@gmail.com wrote: >>>>> Bob-- >>>>> >>>>> Do you know of a reference that would be able to tell me >>>>> definitively what version of MDAC fixed these bugs? >>>>> >>>>> Brian >>>>> >>>>> Bob Barrows wrote: >>>>>> "brian.lukoff@gmail.com" wrote: >>>>>> >>>>>>> I recently came across an article >>>>>>> (http://www.aspfaq.com/show.asp?id=2188) which said: >>>>>>> -- >>>>>>> Depending on the version of your MDAC driver, and the database >>>>>>> you are connecting to, these columns can either (a) not show up >>>>>>> at all, (b) >>>>>>> only show up the first time they're called, or (c) cause >>>>>>> 'Unspecified Error', 'Exception Occured'or 'Errors Occurred' >>>>>>> runtime errors -- >>>>>>> The article mentioned that it would apply to large varchar >>>>>>> columns (more than 255 in length) as well as text columns, but >>>>>>> I haven't seen >>>>>>> this said anywhere else. Would I see this error consistently >>>>>>> (on all fields all of the time or on some fields all of the >>>>>>> time) or just >>>>>>> randomly (on all fields only some of the time or on only some >>>>>>> fields some of the time)? >>>>>>> >>>>>> These are very old bugs that were fixed way back in MDAC 2.5 >>>>>> IIRC. If you have a later version of MDAC then this should not >>>>>> be a problem, especially if you exclusively use the native OLE DB >>>>>> provider for SQL Server (SQLOLEDB) rather than the older ODBC >>>>>> drivers. >>>> >>>> -- >>>> 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" >> >> -- >> 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" -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup.
Hi Aaron,
I do have a large amount of older code that uses SELECT * and makes use of rs(field) syntax without saving to a variable first. This older code is still in use, so what I'm still unclear about is whether all of this needs to be changed if the web server the code is running on is using the latest version of MDAC. Brian [quoted text, click to view] Aaron Bertrand [SQL Server MVP] wrote:
> Sorry, I never kept track of that. I just always made sure my servers had > the latest version available. > > > > > <brian.lukoff@gmail.com> wrote in message > news:1150677556.754703.283160@g10g2000cwb.googlegroups.com... > > What version of MDAC and/or SQL Server (or whatever else) can I be sure > > this is definitely fixed in (regardless of the manner in which I > > connect to the database, the query strings I use, whether I write out > > the data to the page directly from the rs(field) syntax or not, etc.)? > > > > Brian > > > > Aaron Bertrand [SQL Server MVP] wrote: > >> > Also, how often would this bug appear? (Once every 5 accesses to a > >> > large varchar field? Once every 50?) > >> > >> There was no formula, it was intermittent. Maybe every 5, maybe every > >> 50, > >> really hard to say. > >> > >> A > >
[quoted text, click to view]
> I do have a large amount of older code that uses SELECT * and makes use > of rs(field) syntax without saving to a variable first. This older > code is still in use, so what I'm still unclear about is whether all of > this needs to be changed if the web server the code is running on is > using the latest version of MDAC. Depends on your definition of "need." The former, I strongly recommend getting rid of. SELECT * should not exist in production code regardless of MDAC version. The latter, I suppose it doesn't really matter. Saving to local variables first allows you to dispose of the recordset earlier, which is never a bad thing, but it really depends on the scenario. A
I guess what I'm asking is if code run on a recent version of MDAC will
work correctly (performance aside) if SELECT * or the rs(field) syntax without saving to a variable is in place. Brian [quoted text, click to view] Aaron Bertrand [SQL Server MVP] wrote:
> > I do have a large amount of older code that uses SELECT * and makes use > > of rs(field) syntax without saving to a variable first. This older > > code is still in use, so what I'm still unclear about is whether all of > > this needs to be changed if the web server the code is running on is > > using the latest version of MDAC. > > Depends on your definition of "need." > > The former, I strongly recommend getting rid of. SELECT * should not exist > in production code regardless of MDAC version. > > The latter, I suppose it doesn't really matter. Saving to local variables > first allows you to dispose of the recordset earlier, which is never a bad > thing, but it really depends on the scenario. > > A
[quoted text, click to view]
>I guess what I'm asking is if code run on a recent version of MDAC will > work correctly (performance aside) if SELECT * or the rs(field) syntax > without saving to a variable is in place. You'll have to try it, I really don't know. And FWIW, SELECT * is not just about performance. A
Let's end this thread, Brian. The only answer that anyone is going to be
able to give you is: "as far as we know". If you're looking for an ironclad guarantee that you can continue to use poor coding practices and yet be safe, then you are not going to find it here, or anywhere else that i can think of. [quoted text, click to view] brian.lukoff@gmail.com wrote: > I guess what I'm asking is if code run on a recent version of MDAC > will > work correctly (performance aside) if SELECT * or the rs(field) syntax > without saving to a variable is in place. > > Brian > > Aaron Bertrand [SQL Server MVP] wrote: >>> I do have a large amount of older code that uses SELECT * and makes >>> use of rs(field) syntax without saving to a variable first. This >>> older >>> code is still in use, so what I'm still unclear about is whether >>> all of this needs to be changed if the web server the code is >>> running on is using the latest version of MDAC. >> >> Depends on your definition of "need." >> >> The former, I strongly recommend getting rid of. SELECT * should >> not exist in production code regardless of MDAC version. >> >> The latter, I suppose it doesn't really matter. Saving to local >> variables first allows you to dispose of the recordset earlier, >> which is never a bad thing, but it really depends on the scenario. >> >> A -- 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"
I guess why I am asking is that I wasn't aware that using SELECT * was
problematic in the first place. Could you point me to a reference that describes what the pitfalls are of using SELECT *? I also don't understand why directly writing out a field's contents to a page using rs(field) would be problematic (for reasons other than this particular bug). Brian [quoted text, click to view] Bob Barrows [MVP] wrote:
> Let's end this thread, Brian. The only answer that anyone is going to be > able to give you is: "as far as we know". If you're looking for an ironclad > guarantee that you can continue to use poor coding practices and yet be > safe, then you are not going to find it here, or anywhere else that i can > think of. > > brian.lukoff@gmail.com wrote: > > I guess what I'm asking is if code run on a recent version of MDAC > > will > > work correctly (performance aside) if SELECT * or the rs(field) syntax > > without saving to a variable is in place. > > > > Brian > > > > Aaron Bertrand [SQL Server MVP] wrote: > >>> I do have a large amount of older code that uses SELECT * and makes > >>> use of rs(field) syntax without saving to a variable first. This > >>> older > >>> code is still in use, so what I'm still unclear about is whether > >>> all of this needs to be changed if the web server the code is > >>> running on is using the latest version of MDAC. > >> > >> Depends on your definition of "need." > >> > >> The former, I strongly recommend getting rid of. SELECT * should > >> not exist in production code regardless of MDAC version. > >> > >> The latter, I suppose it doesn't really matter. Saving to local > >> variables first allows you to dispose of the recordset earlier, > >> which is never a bad thing, but it really depends on the scenario. > >> > >> A > > -- > 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]
brian.lukoff@gmail.com wrote: > I guess why I am asking is that I wasn't aware that using SELECT * was > problematic in the first place. Could you point me to a reference > that describes what the pitfalls are of using SELECT *? I've read several books that talk about this. The only web reference for it that I know of (google may provide others) is: http://www.aspfaq.com/show.asp?id=2096 [quoted text, click to view] > I also don't > understand why directly writing out a field's contents to a page using > rs(field) would be problematic (for reasons other than this particular > bug). > It implies keeping a recordset, and its connection to the database, open for longer than is needed. This is especially critical when using Jet as the backend. The goal in an ASP page should be to retrieve the data from the database and close the connection as quickly as possible. The use of GetRows or GetString greatly facilitates this goal. http://www.aspfaq.com/show.asp?id=2467 -- 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"
Thanks for the references. I'm somewhat confused by this line in the
FAQ about recordset iteration: "Recordset objects are significantly more complex from a structural standpoint and as such are inherently less stable than low-level objects such as strings and arrays." Do you know what stability problems are being referred to here? Also, why is Jet particularly problematic? Brian [quoted text, click to view] Bob Barrows [MVP] wrote:
> brian.lukoff@gmail.com wrote: > > I guess why I am asking is that I wasn't aware that using SELECT * was > > problematic in the first place. Could you point me to a reference > > that describes what the pitfalls are of using SELECT *? > > I've read several books that talk about this. The only web reference for it > that I know of (google may provide others) is: > http://www.aspfaq.com/show.asp?id=2096 > > > > I also don't > > understand why directly writing out a field's contents to a page using > > rs(field) would be problematic (for reasons other than this particular > > bug). > > > > It implies keeping a recordset, and its connection to the database, open for > longer than is needed. This is especially critical when using Jet as the > backend. > The goal in an ASP page should be to retrieve the data from the database and > close the connection as quickly as possible. The use of GetRows or GetString > greatly facilitates this goal. > http://www.aspfaq.com/show.asp?id=2467 > > -- > 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"
Aaron, if you're out there I'd appreciate hearing about what
experiences prompted you to write this about recordsets. Brian [quoted text, click to view] Bob Barrows [MVP] wrote:
> brian.lukoff@gmail.com wrote: > > Thanks for the references. I'm somewhat confused by this line in the > > FAQ about recordset iteration: "Recordset objects are significantly > > more complex from a structural standpoint and as such are inherently > > less stable than low-level objects such as strings and arrays." Do > > you know what stability problems are being referred to here? > > Nothing specific that I know of. It's a general observation, that's all. > > > Also, > > why is Jet particularly problematic? > > Since it was originally created as a desktop database, with multi-user > capabilities added on later, Jet cannot really cope with a large number > of simultaneous connections*. This makes it critical that connections be > kept as short as possible ... get in, get the data, get out, process the > data. > > An alternative to the GetRows approach is to use disconnected > recordsets: open a client-side recordset and set its Activeconnection > property to Nothing. This disconnects the recordset (which still > contains the data) from the data source, allowing the connection to be > closed while the data in the recordset is processed. > > Bob Barrows > > * and before you ask, a definitive cutoff point for the maximum number > of users Jet can handle has never been published. You will find sources, > including some in the MSDN library, that cite the number 10, but they > never back this up. The number that can be handled depends on many > things including: > the type of activity being performed > the design of the database > the skill of the developer > <joke> > The proper sacrifices to the database gods having been performed > </joke> > > -- > Microsoft MVP -- ASP/ASP.NET > Please reply to the newsgroup. The email account listed in my From > header is my spam trap, so I don't check it very often. You will get a > quicker response by posting to the newsgroup.
[quoted text, click to view]
brian.lukoff@gmail.com wrote: > Thanks for the references. I'm somewhat confused by this line in the > FAQ about recordset iteration: "Recordset objects are significantly > more complex from a structural standpoint and as such are inherently > less stable than low-level objects such as strings and arrays." Do > you know what stability problems are being referred to here? Nothing specific that I know of. It's a general observation, that's all. [quoted text, click to view] > Also, > why is Jet particularly problematic? Since it was originally created as a desktop database, with multi-user capabilities added on later, Jet cannot really cope with a large number of simultaneous connections*. This makes it critical that connections be kept as short as possible ... get in, get the data, get out, process the data. An alternative to the GetRows approach is to use disconnected recordsets: open a client-side recordset and set its Activeconnection property to Nothing. This disconnects the recordset (which still contains the data) from the data source, allowing the connection to be closed while the data in the recordset is processed. Bob Barrows * and before you ask, a definitive cutoff point for the maximum number of users Jet can handle has never been published. You will find sources, including some in the MSDN library, that cite the number 10, but they never back this up. The number that can be handled depends on many things including: the type of activity being performed the design of the database the skill of the developer <joke> The proper sacrifices to the database gods having been performed </joke> -- Microsoft MVP -- ASP/ASP.NET Please reply to the newsgroup. The email account listed in my From header is my spam trap, so I don't check it very often. You will get a quicker response by posting to the newsgroup.
[quoted text, click to view]
> > "Recordset objects are significantly > > more complex from a structural standpoint and as such are inherently > > less stable than low-level objects such as strings and arrays." Do > > you know what stability problems are being referred to here? > > Nothing specific that I know of. It's a general observation, that's all. So is the idea here that any complex object is just more susceptible to errors from network problems because there's more data to send? I'm trying to determine if this is referring to problems that people have had because they used recordsets or if this is just a theoretical observation that would apply to any complex data structure that was sent over a network. Brian
[quoted text, click to view]
brian.lukoff@gmail.com wrote: The latter.>>> "Recordset objects are significantly >>> more complex from a structural standpoint and as such are inherently >>> less stable than low-level objects such as strings and arrays." Do >>> you know what stability problems are being referred to here? >> >> Nothing specific that I know of. It's a general observation, that's >> all. > > So is the idea here that any complex object is just more susceptible > to errors from network problems because there's more data to send? > I'm trying to determine if this is referring to problems that people > have had because they used recordsets or if this is just a theoretical > observation that would apply to any complex data structure that was > sent over a network. > -- 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"
One final question for you: when you say that the bugs were
intermittent, you mean that the same code running the same query would sometimes work and sometimes not, not that some code would work and other (equally correct) code would not, correct? (I'm writing some simulations to test out our current configuration.) Brian [quoted text, click to view] Bob Barrows [MVP] wrote:
> brian.lukoff@gmail.com wrote: > >>> "Recordset objects are significantly > >>> more complex from a structural standpoint and as such are inherently > >>> less stable than low-level objects such as strings and arrays." Do > >>> you know what stability problems are being referred to here? > >> > >> Nothing specific that I know of. It's a general observation, that's > >> all. > > > > So is the idea here that any complex object is just more susceptible > > to errors from network problems because there's more data to send? > > I'm trying to determine if this is referring to problems that people > > have had because they used recordsets or if this is just a theoretical > > observation that would apply to any complex data structure that was > > sent over a network. > > > The latter. > > -- > 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]
brian.lukoff@gmail.com wrote: > One final question for you: Hah! Sure, sure ... ;-) [quoted text, click to view] > when you say that the bugs were > intermittent, you mean that the same code running the same query would > sometimes work and sometimes not, In my situation, this was not the case. When it happened to me, it was easily reproducible. In others I've responded to in the past several years, it was intermittent.* [quoted text, click to view] > not that some code would work and > other (equally correct) code would not, correct? I'm not quite sure what you mean by this. [quoted text, click to view] > (I'm writing some > simulations to test out our current configuration.) Good luck. Bob Barrows * IIRC -- 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"
I'm trying to test for the problem by running a simple query many times
and checking to make sure that every time the query is run and a value is retrieved using rs(field), I get the value and not a truncated or empty string. If I am understanding the bug correctly, I should be able to see from this whether the bug is present in my current configuration--is this correct? Brian [quoted text, click to view] Bob Barrows [MVP] wrote:
> brian.lukoff@gmail.com wrote: > > One final question for you: > > Hah! Sure, sure ... ;-) > > > when you say that the bugs were > > intermittent, you mean that the same code running the same query would > > sometimes work and sometimes not, > > In my situation, this was not the case. When it happened to me, it was > easily reproducible. > In others I've responded to in the past several years, it was intermittent.* > > > not that some code would work and > > other (equally correct) code would not, correct? > > I'm not quite sure what you mean by this. > > > (I'm writing some > > simulations to test out our current configuration.) > > Good luck. > > Bob Barrows > * IIRC > -- > 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]
brian.lukoff@gmail.com wrote: > I'm trying to test for the problem by running a simple query many > times > and checking to make sure that every time the query is run and a value > is retrieved using rs(field), I get the value and not a truncated or > empty string. If I am understanding the bug correctly, I should be > able to see from this whether the bug is present in my current > configuration--is this correct? > I can't say for sure, but, probably. Again, no ironclad guarantees. My experience iinvolved an easily reproducible symptom. Others I remember seeing in these newsgroups didn't. Could you explain why your data's integrity is so dependant on this functionality? I'm getting the idea from your previous posts that you are somehow using an ADO recordset in an ASP app to perform some sort of export/backup function ...? If so, this is definitely the wrong tool for the job. -- 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"
My understanding of the bug is that it could cause a simple script like
this to fail: Set rs = conn.Execute("SELECT * FROM x") Response.Write rs("y") if y is a text column. This seems like basic functionality that should work all of the time (even with SELECT *). Any ideas how I can ensure that this will work? Brian [quoted text, click to view] Bob Barrows [MVP] wrote:
> brian.lukoff@gmail.com wrote: > > I'm trying to test for the problem by running a simple query many > > times > > and checking to make sure that every time the query is run and a value > > is retrieved using rs(field), I get the value and not a truncated or > > empty string. If I am understanding the bug correctly, I should be > > able to see from this whether the bug is present in my current > > configuration--is this correct? > > > > I can't say for sure, but, probably. > Again, no ironclad guarantees. My experience iinvolved an easily > reproducible symptom. Others I remember seeing in these newsgroups didn't. > > Could you explain why your data's integrity is so dependant on this > functionality? I'm getting the idea from your previous posts that you are > somehow using an ADO recordset in an ASP app to perform some sort of > export/backup function ...? If so, this is definitely the wrong tool for the > job. > > -- > 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"
One more thing--a while back we had talked about a possible truncation
bug. Is the only possible situation truncation to 255 characters, or could truncation to another length string happen? Brian [quoted text, click to view] brian.lukoff@gmail.com wrote:
> My understanding of the bug is that it could cause a simple script like > this to fail: > > Set rs = conn.Execute("SELECT * FROM x") > Response.Write rs("y") > > if y is a text column. This seems like basic functionality that should > work all of the time (even with SELECT *). Any ideas how I can ensure > that this will work? > > Brian > > Bob Barrows [MVP] wrote: > > brian.lukoff@gmail.com wrote: > > > I'm trying to test for the problem by running a simple query many > > > times > > > and checking to make sure that every time the query is run and a value > > > is retrieved using rs(field), I get the value and not a truncated or > > > empty string. If I am understanding the bug correctly, I should be > > > able to see from this whether the bug is present in my current > > > configuration--is this correct? > > > > > > > I can't say for sure, but, probably. > > Again, no ironclad guarantees. My experience iinvolved an easily > > reproducible symptom. Others I remember seeing in these newsgroups didn't. > > > > Could you explain why your data's integrity is so dependant on this > > functionality? I'm getting the idea from your previous posts that you are > > somehow using an ADO recordset in an ASP app to perform some sort of > > export/backup function ...? If so, this is definitely the wrong tool for the > > job. > > > > -- > > 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"
Hi Bob--sorry for the multiple posts...I want to try to wrap up our
discussion and pose the remaining questions I have about this issue: 1. You mentioned a truncation problem a while back. Is there a bug where retrieving data from a large varchar field is intermittently truncated, or was I misunderstanding your previous e-mail? 2. When people have had the intermittently occurring problem of data fields showing up as blank in the past, has it been with one particular query, or did the problem occur with any query? (If the problem occurred [eventually] with any query, then I should be able to test for the problem by trying a single query repeatedly and seeing if accessing a recordset field ever fails to work.) 3. One page that I found (http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=80) mentions using client cursors as a solution. Do you have any experience with this solution? 4. You had said that you have been using SQLOLEDB without issue. Has SQLOLEDB worked with SELECT * and repeatedly accessing a field using rs(field)? (I have old code that I need to ensure will work in the future.) I appreciate all of the time you've spent helping me with this! Brian
[quoted text, click to view]
brian.lukoff@gmail.com wrote: > Hi Bob--sorry for the multiple posts...I want to try to wrap up our > discussion and pose the remaining questions I have about this issue: > > 1. You mentioned a truncation problem a while back. Is there a bug > where retrieving data from a large varchar field is intermittently > truncated, or was I misunderstanding your previous e-mail? I have nothing to add that I didn't say in my previous post. [quoted text, click to view] > > 2. When people have had the intermittently occurring problem of data > fields showing up as blank in the past, has it been with one > particular query, or did the problem occur with any query? (If the > problem occurred [eventually] with any query, then I should be able > to test for the problem by trying a single query repeatedly and > seeing if accessing a recordset field ever fails to work.) > Again, I have nothing to add. [quoted text, click to view] > 3. One page that I found > (http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=80) mentions using > client cursors as a solution. Do you have any experience with this > solution? > I rarely use client-side cursors in my ASP applications. I can see why they might alleviate the problem, though: since all the data is stored locally there is no need to return to the database to get Text data that was "left behind" in case you didn't need it (speculation on my part, but I've always suspected that this was one of the root causes of this class of bugs). [quoted text, click to view] > 4. You had said that you have been using SQLOLEDB without issue. Has > SQLOLEDB worked with SELECT * and repeatedly accessing a field using > rs(field)? (I have old code that I need to ensure will work in the > future.) > I don't know. I stopped using selstar many years ago. -- 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]
> > 1. You mentioned a truncation problem a while back. Is there a bug > > where retrieving data from a large varchar field is intermittently > > truncated, or was I misunderstanding your previous e-mail? > > I have nothing to add that I didn't say in my previous post. The reason why I'm confused is because you had mentioned truncation, but then said "I had a couple problems mixed up. It can't hurt to check for truncation, but that won't help with the other issue you are trying to detect." I'd appreciate it if you could clarify what you meant by this. Did you mean that (a) sometimes when retrieving data from fields, they come up truncated to 255 characters, just like they can come up empty, (b) there is no bug like this, or (c) something else? If I - use SQLOLEDB (or the equivalent for Access), - do not use SELECT *, and - retrieve the value of an rs() variable only once, or assign it to a temporary variable the first time, then can I be sure the bug will not affect me? I have read some places that mention using GetChunk to retrieve text/memo fields--is this necessary? Brian
[quoted text, click to view] > >>> 1. You mentioned a truncation problem a while back. Is there a bug > >>> where retrieving data from a large varchar field is intermittently > >>> truncated, or was I misunderstanding yo |