large varchar columns in SQL server

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

RE: large varchar columns in SQL server

"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
>
>

RE: large varchar columns in SQL server

"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.

Re: large varchar columns in SQL server

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

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.

Re: large varchar columns in SQL server

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.

> (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.)
>
>
I don't know how you are going to do that without access to the original
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"

Re: large varchar columns in SQL server

>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

Re: large varchar columns in SQL server

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

Re: large varchar columns in SQL server

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

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

Re: large varchar columns in SQL server

> 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

Re: large varchar columns in SQL server

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

Re: large varchar columns in SQL server

Sorry, I never kept track of that. I just always made sure my servers had
the latest version available.




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
>

Re: large varchar columns in SQL server

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.

Re: large varchar columns in SQL server

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"

Re: large varchar columns in SQL server

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"

Re: large varchar columns in SQL server

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"

Re: large varchar columns in SQL server

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"

Re: large varchar columns in SQL server

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"

Re: large varchar columns in SQL server

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


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"

Re: large varchar columns in SQL server

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

> 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.

> 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"

Re: large varchar columns in SQL server

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"

Re: large varchar columns in SQL server

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

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.

Re: large varchar columns in SQL server

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

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.
>
>
>
>
> 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
> >

Re: large varchar columns in SQL server

> 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

Re: large varchar columns in SQL server

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

Re: large varchar columns in SQL server

>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

Re: large varchar columns in SQL server

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"

Re: large varchar columns in SQL server

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

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"

Re: large varchar columns in SQL server

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"

Re: large varchar columns in SQL server

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

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"

Re: large varchar columns in SQL server

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

The proper sacrifices to the database gods having been performed


--
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.

Re: large varchar columns in SQL server

Aaron, if you're out there I'd appreciate hearing about what
experiences prompted you to write this about recordsets.

Brian

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
>
> The proper sacrifices to the database gods having been performed
>
>
> --
> 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.

Re: large varchar columns in SQL server

> > "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

Re: large varchar columns in SQL server

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"

Re: large varchar columns in SQL server

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

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"

Re: large varchar columns in SQL server

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"

Re: large varchar columns in SQL server

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

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"

Re: large varchar columns in SQL server

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"

Re: large varchar columns in SQL server

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"

Re: large varchar columns in SQL server

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

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"

Re: large varchar columns in SQL server

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

Re: large varchar columns in SQL server

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.

>
> 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.

> 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).

> 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"

Re: large varchar columns in SQL server

> > 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

Re: large varchar columns in SQL server

brian.lukoff@gmail.com wrote:
>>> 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?

I've no idea. All I know came from reading those KB articles.

>
> 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'm pretty sure.

>
> I have read some places that mention using GetChunk to retrieve
> text/memo fields--is this necessary?
>
I've never had to.

--
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: large varchar columns in SQL server

> >>> 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?
>
> I've no idea. All I know came from reading those KB articles.

The only KB articles about this topic that I think I have seen
mentioned on the newsgroups are
http://support.microsoft.com/default.aspx/kb/200124 and
http://support.microsoft.com/default.aspx/kb/175239, and I don't think
either of them mention truncation. Which KB article were you referring
to?

Do you have any ideas about other resources that I can consult to try
to get more information on this issue?

Brian

Re: large varchar columns in SQL server

brian.lukoff@gmail.com wrote:
>>>>> 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?
>>
>> I've no idea. All I know came from reading those KB articles.
>
> The only KB articles about this topic that I think I have seen
> mentioned on the newsgroups are
> http://support.microsoft.com/default.aspx/kb/200124 and
> http://support.microsoft.com/default.aspx/kb/175239, and I don't think
> either of them mention truncation. Which KB article were you referring
> to?
>
> Do you have any ideas about other resources that I can consult to try
> to get more information on this issue?
>
No.

--
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: large varchar columns in SQL server

OK--could you tell me which KB articles you were referring to about the
truncation issue? The two that I listed in my post above don't seem to
mention it.

Brian

Re: large varchar columns in SQL server

brian.lukoff@gmail.com wrote:
> OK--could you tell me which KB articles you were referring to about
> the truncation issue? The two that I listed in my post above don't
> seem to mention it.
>
> Brian

Everything I know about this issue has been discussed in this thread.
Again, I have nothing new to add.
That's it. I'm done with this thread.

--
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: large varchar columns in SQL server

Brian, you're really, really, really beating a dead horse here.

Don't use SELECT *. I think if you can agree to do that then there should
be no more discussion about this. Move on! We've all got better things to
do than analyze a problem that has been solved for years. This is pretty
much common knowledge, and I really don't remember which version/service
pack/hotfix corrected the issue for good, because long before Microsoft
acted on the problem we learned to work around it with best practices.



wrote in message
news:1152841356.356874.173770@m79g2000cwm.googlegroups.com.. .
> OK--could you tell me which KB articles you were referring to about the
> truncation issue? The two that I listed in my post above don't seem to
> mention it.
>
> Brian
>

Re: large varchar columns in SQL server

I have no problem not using SELECT *. I just need to confirm that this
is the *only* measure I need to take to use text/memo/large varchar
fields without problems, and that other things I have read about (e.g.,
having to read the memo fields from left to right in the order they
appear in the query, using GetChunk, etc.) are unnecessary.

I understand this is an old issue, but it is new to me and as such I
still haven't figured out what the right strategy is for dealing with
it. I appreciate all of the time you've spent with me in this thread!

Brian

Aaron Bertrand [SQL Server MVP] wrote:
> Brian, you're really, really, really beating a dead horse here.
>
> Don't use SELECT *. I think if you can agree to do that then there should
> be no more discussion about this. Move on! We've all got better things to
> do than analyze a problem that has been solved for years. This is pretty
> much common knowledge, and I really don't remember which version/service
> pack/hotfix corrected the issue for good, because long before Microsoft
> acted on the problem we learned to work around it with best practices.
>
>
>
> wrote in message
> news:1152841356.356874.173770@m79g2000cwm.googlegroups.com.. .
> > OK--could you tell me which KB articles you were referring to about the
> > truncation issue? The two that I listed in my post above don't seem to
> > mention it.
> >
> > Brian
> >

Re: large varchar columns in SQL server

Aaron provided you with the right strategy in his first post in this
thread... about 6 months ago, I think it was.

--
Mike Brind

brian.lukoff@gmail.com wrote:
> I have no problem not using SELECT *. I just need to confirm that this
> is the *only* measure I need to take to use text/memo/large varchar
> fields without problems, and that other things I have read about (e.g.,
> having to read the memo fields from left to right in the order they
> appear in the query, using GetChunk, etc.) are unnecessary.
>
> I understand this is an old issue, but it is new to me and as such I
> still haven't figured out what the right strategy is for dealing with
> it. I appreciate all of the time you've spent with me in this thread!
>
> Brian
>
> Aaron Bertrand [SQL Server MVP] wrote:
> > Brian, you're really, really, really beating a dead horse here.
> >
> > Don't use SELECT *. I think if you can agree to do that then there should
> > be no more discussion about this. Move on! We've all got better things to
> > do than analyze a problem that has been solved for years. This is pretty
> > much common knowledge, and I really don't remember which version/service
> > pack/hotfix corrected the issue for good, because long before Microsoft
> > acted on the problem we learned to work around it with best practices.
> >
> >
> >
> > wrote in message
> > news:1152841356.356874.173770@m79g2000cwm.googlegroups.com.. .
> > > OK--could you tell me which KB articles you were referring to about the
> > > truncation issue? The two that I listed in my post above don't seem to
> > > mention it.
> > >
> > > Brian
> > >