How can I retrieve data from text datatype

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How can I retrieve data from text datatype

Author

Topic

SQL_SERVER
Starting Member

7 Posts

Posted - 2007-10-05 : 14:07:00

Hi,

I have a table which has two columns.The first column's data type is text.I need a query to fetch the value of the column using SQL Query analyser.I am using SQL Server 2000.Please help me.

Thanks in Advance,

Swati

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-05 : 14:08:46

select columnName
from YourTable

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SQL_SERVER
Starting Member

7 Posts

Posted - 2007-10-05 : 14:20:21

This query is not returning the whole data store in the column in the table.This is returning only a part of the data.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-05 : 14:23:45

returning where? query analyzer?
if that's so then all you have to do is set the column length to 8000 in tools->options->can't remember where exatly from here

if you have more than 8k lenght then you won't be able to see the whole row data in query analyzer.
however it will return all data to a client provider like ado.net

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 14:37:17

It's Tools..Options..Results..Maximum characters per column.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-05 : 14:38:32

thanx Tara!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SQL_SERVER
Starting Member

7 Posts

Posted - 2007-10-05 : 14:51:56

So does it mean that using the SQL Query analyzer we can never see the total data of a text datatype colunm if it having more than 8k value? Unfortunetly it is not possible to change the column size of the database table right now.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-05 : 14:54:55

That is correct. Query Analyzer does not have this capability. You'll need to view the data from your application.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

SQL_SERVER
Starting Member

7 Posts

Posted - 2007-10-05 : 15:00:56

Thanks a lot for your help and time.

SQL_SERVER
Starting Member

7 Posts

Posted - 2007-10-05 : 15:25:01

Is there any query to get the length of the data present in the text type column in the table?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-05 : 15:30:00

select datalength(yourcolumn) from yourTable

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

SQL_SERVER
Starting Member

7 Posts

Posted - 2007-10-05 : 15:35:22

Hi,I tried to fetch the data length of TEST_DESC with the followig quey,But i am getting an error as Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '('.

DECLARE @ptr VARBINARY (16)
SELECT @ptr = TEXTPTR (TEST_DESC) FROM TEST WHERE TEST_ID = 3
READTEXT TEST.TEST_DESC @ptr 0 (select datalength(TEST_DESC) from TEST WHERE TEST_ID = 3)

SQL_SERVER
Starting Member

7 Posts

Posted - 2007-10-05 : 15:47:51

Hi.I have solved it. by
DECLARE @ptr VARBINARY (16)
SELECT @ptr = TEXTPTR (TEST_DESC) FROM TEST WHERE TEST_ID = 3
DECLARE @length int
SELECT @length = datalength(TEST_DESC) FROM TEST WHERE TEST_ID = 3

READTEXT TEST.TEST_DESC @ptr 0 @length

Thanks a lot guys....

Kristen
Test

22859 Posts

Posted - 2007-10-06 : 05:35:05

When I do that in Query Analyser I get the first 8,000 characters ...

Did I miss something?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources