all groups > sql server odbc > december 2005 >
You're in the

sql server odbc

group:

How do I use SELECT on a column with datatype TEXT?


How do I use SELECT on a column with datatype TEXT? gene.ellis NO[at]SPAM gmail.com
12/20/2005 11:33:49 AM
sql server odbc: 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.
Re: How do I use SELECT on a column with datatype TEXT? adi
12/20/2005 11:49:15 AM
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.
Re: How do I use SELECT on a column with datatype TEXT? Dan Guzman
12/20/2005 1:44:21 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

[quoted text, click to view]

Re: How do I use SELECT on a column with datatype TEXT? gene.ellis NO[at]SPAM gmail.com
12/20/2005 2:08:47 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.
Re: How do I use SELECT on a column with datatype TEXT? Bob Barrows [MVP]
12/20/2005 2:46:21 PM
[quoted text, click to view]

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.

Re: How do I use SELECT on a column with datatype TEXT? Dan Guzman
12/20/2005 4:23:18 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

[quoted text, click to view]

Re: How do I use SELECT on a column with datatype TEXT? gene.ellis NO[at]SPAM gmail.com
12/21/2005 3:28:10 PM
That does help. Question. How does the "SET TEXTSIZE" usually go? Is it
part of the SELECT statement? Thanks again for your help.
Re: How do I use SELECT on a column with datatype TEXT? gene.ellis NO[at]SPAM gmail.com
12/21/2005 3:54:47 PM
Ok I found where to place the SET TEXTSIZE statement, but still
nothing. Everything is still truncated. Any other ideas?
Re: How do I use SELECT on a column with datatype TEXT? Dan Guzman
12/21/2005 9:48:07 PM
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

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.
Don't see what you're looking for? Try a search.
View other messages in the sql server odbc group.
AddThis Social Bookmark Button
View Other Months
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
home · blog · groups Questions? Comments? Contact the dn