RE: Export the Data of a field of 5000 characters length

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hello Muhammad,

I replied you in the microsoft.public.sqlserver.connect newsgroup, and I
have included the content here for your reference:
=========================================


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

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================


This posting is provided "AS IS" with no warranties, and confers no rights.




.