Hello Muhammad,
The text length returned is limited by @TEXTSIZE option in a connection.
Also, client tool such as Query Analyzer in SQL 2000 has the limitation of
8K. You could use the following method to set the @TEXTSIZE to the maximum
value.
SELECT @@TEXTSIZE
SET TEXTSIZE 2147483647
SELECT @@TEXTSIZE
Please refer to TEXTSIZE in Books Online for more details.
You could set maximum text size returned by Query Analyzer in 2000 to 8000.
Tools->Options->Results->Maximum characters per columns.
If you use Osql and use Textptr to get the text, the limiation is about
25K.
SET TEXTSIZE 2147483647
DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info)
FROM pub_info pr where pub_id = 0736
READTEXT pub_info.pr_info @ptrval 0 63357
The most simple method to work around the limiation is to use bcp utility
to export the result text to a file:
bcp "SELECT pr_info FROM pubs..pub_info where pub_id = 0736" queryout
pubsout.txt
-c -Sservername -Usa -Ppassword
Also, you could use SP and your own application such as VB or .Net code to
get the text directly. For example:
Create a stored procedure "Test":
===============
CREATE PROCEDURE Test
AS
BEGIN
SET NOCOUNT ON;
Select pr_info from pub_info where pub_id=0736;
END
GO
==============
Dim cn As ADODB.Connection
Dim strm As ADODB.Stream
Dim cmd As ADODB.Command
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;data Source=<server\instance>;Initial
Catalog=pubs;integrated security=sspi"
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "Test"
Set strm = New ADODB.Stream
strm.Open
cmd.Properties("Output Stream") = strm
cn.Execute "SET TEXTSIZE 200000"
cmd.Execute , , adExecuteStream
strm.SaveToFile "c:\pubinfo.txt", adSaveCreateOverWrite
cn.Close
==============
317034 HOW TO: Read and Write a File to and from a BLOB Column by Using
Chunking in
ADO.NET and Visual Basic .NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;317034 Also, in SQL 2005, you could set the "Maximum Characters Retrieved" to a
bigger value in Management Studio.
Hope this helps. If you have any further questions or comments, please feel
free to let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications
<
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<
http://msdn.microsoft.com/subscriptions/support/default.aspx>. ==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
