| Author |
How do I use SELECT on a column with datatype TEXT?
|
|
| gene.ellis@gmail.com 2005-12-20, 8:24 pm |
| Hello. I using a simply SELECT statement to retrieve some data from a
SQL SERVER via an ODBC connection. I had to go from VARCHAR to TEXT
because the amount of data. Anyway, my SQL statements worked just fine
when I was using VARCHAR, but now since I am using TEXT, I am only
receiving part of the content back. Do I have to do some sort of
special Casting or something if I want to get all the content back?
It's over 8,000 characters. Thank you very much. I have racking my
brain on this for a while.
| |
| Dan Guzman 2005-12-20, 8:24 pm |
| The number of bytes returned is controlled by the SET TEXTSIZE connection
setting. According the Books Online, the default is 4096 and ODBC sets it
to 2147483647 when connecting. Additionally, Query Analyzer limits the
column size according to the Tools-->Options-->Results setting.
In any case, a Profile trace should reveal the SET TEXTSIZE setting.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<gene.ellis@gmail.com> wrote in message
news:1135107229.394633.213590@o13g2000cwo.googlegroups.com...
> Hello. I using a simply SELECT statement to retrieve some data from a
> SQL SERVER via an ODBC connection. I had to go from VARCHAR to TEXT
> because the amount of data. Anyway, my SQL statements worked just fine
> when I was using VARCHAR, but now since I am using TEXT, I am only
> receiving part of the content back. Do I have to do some sort of
> special Casting or something if I want to get all the content back?
> It's over 8,000 characters. Thank you very much. I have racking my
> brain on this for a while.
>
| |
| Bob Barrows [MVP] 2005-12-20, 8:24 pm |
| gene.ellis@gmail.com wrote:
> Hello. I using a simply SELECT statement to retrieve some data from a
> SQL SERVER via an ODBC connection. I had to go from VARCHAR to TEXT
> because the amount of data. Anyway, my SQL statements worked just fine
> when I was using VARCHAR, but now since I am using TEXT, I am only
> receiving part of the content back. Do I have to do some sort of
> special Casting or something if I want to get all the content back?
> It's over 8,000 characters. Thank you very much. I have racking my
> brain on this for a while.
Let me guess: you're testing this in QA and noticing that the TEXT data is
truncated ...?
If so, go into the Options dialog and uncheck the option to truncate TEXT
results.
Bob Barrows
--
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.
| |
|
| When you say 'receiving part of the content' is this in your
application or on SQL Query Analyzer?
If is the later then change the settings under: Options-results, but if
it is an app then may be you should try reading it into a variable that
can read a Text type from SQL.
just my 2 cents.
| |
| gene.ellis@gmail.com 2005-12-20, 8:24 pm |
| Thanks for the reply. When I say "receiving part of the content" I am
talking about in my application. I am using PHP to connect via ODBC
(Easysoft Driver) to the SQL Server machines. I am reading the content
back into a variable, and then displaying the variable. But the content
is truncated. Do I have to do something special since I am reading in a
text datatype? It reads in everything else just fine, and acted fine
when this colum was a varchar datatype.
| |
| Dan Guzman 2005-12-20, 8:24 pm |
| I don't know about your Easysoft Driver but the Microsoft SQL Server ODBC
driver sets the textsize value at 2GB. If the Easysoft Driver doesn't set
the textsize, it will default to 4096.
You ought to be able to issue an explicit 'SET TEXTSIZE 2147483647' in your
application to prevent truncation.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<gene.ellis@gmail.com> wrote in message
news:1135116527.697886.169220@o13g2000cwo.googlegroups.com...
> Thanks for the reply. When I say "receiving part of the content" I am
> talking about in my application. I am using PHP to connect via ODBC
> (Easysoft Driver) to the SQL Server machines. I am reading the content
> back into a variable, and then displaying the variable. But the content
> is truncated. Do I have to do something special since I am reading in a
> text datatype? It reads in everything else just fine, and acted fine
> when this colum was a varchar datatype.
>
| |
| gene.ellis@gmail.com 2005-12-21, 8:25 pm |
| That does help. Question. How does the "SET TEXTSIZE" usually go? Is it
part of the SELECT statement? Thanks again for your help.
| |
| gene.ellis@gmail.com 2005-12-21, 8:25 pm |
| Ok I found where to place the SET TEXTSIZE statement, but still
nothing. Everything is still truncated. Any other ideas?
| |
| Dan Guzman 2005-12-22, 3:24 am |
| SET TEXTSIZE must be executed on the same connection as the subsequent
SELECT. You can run a Profiler trace to verify that is indeed what is
happening. Perhaps your driver is doing something screwy behind your back.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<gene.ellis@gmail.com> wrote in message
news:1135209287.815636.64060@g47g2000cwa.googlegroups.com...
> Ok I found where to place the SET TEXTSIZE statement, but still
> nothing. Everything is still truncated. Any other ideas?
>
|
|
|
|