Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Can select ... for xml support more rows?

14 posts in General Discussion Last posting was on 2011-10-21 05:37:05.0Z
Eisen Posted on 2011-10-18 08:07:31.0Z
Sender: 7f04.4e9cf3ea.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Can select ... for xml support more rows?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e9d33c3.1493.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 Oct 2011 01:07:31 -0700
X-Trace: forums-1-dub 1318925251 10.22.241.41 (18 Oct 2011 01:07:31 -0700)
X-Original-Trace: 18 Oct 2011 01:07:31 -0700, 10.22.241.41
Lines: 11
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30606
Article PK: 73496

Dear all
I found a new feather of ASE15 is xml service -- add "for
xml" after the select statement could transfer the resultset
into a XML file formant. I'm very interested in it. But soon
I found the XML file' size can't exceed @@textsize, so if
the resultset is a large one, the output will truncated.
Would anyone know how to configure it to support more rows
only limited in one @@textsize? Thanks.

Best Regards
Eisen


Rob V Posted on 2011-10-18 08:55:03.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Can select ... for xml support more rows?
References: <4e9d33c3.1493.1681692777@sybase.com>
In-Reply-To: <4e9d33c3.1493.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e9d3ee7$1@forums-1-dub>
Date: 18 Oct 2011 01:55:03 -0700
X-Trace: forums-1-dub 1318928103 10.22.241.152 (18 Oct 2011 01:55:03 -0700)
X-Original-Trace: 18 Oct 2011 01:55:03 -0700, vip152.sybase.com
Lines: 36
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30607
Article PK: 73497


On 18-Oct-2011 10:07, Eisen wrote:
> Dear all
> I found a new feather of ASE15 is xml service -- add "for
> xml" after the select statement could transfer the resultset
> into a XML file formant. I'm very interested in it. But soon
> I found the XML file' size can't exceed @@textsize, so if
> the resultset is a large one, the output will truncated.
> Would anyone know how to configure it to support more rows
> only limited in one @@textsize? Thanks.
>
> Best Regards
> Eisen

@@textsize can be changed by running 'set textsize <number>'. But I have
the feeling that is not what you mean?
If so, an you elaborate?

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


J Posted on 2011-10-18 16:33:52.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Can select ... for xml support more rows?
Reply-To: J@bogusemailAddress.com
Message-ID: <4e9daa2d.6312156@forums.sybase.com>
References: <4e9d33c3.1493.1681692777@sybase.com> <4e9d3ee7$1@forums-1-dub>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 18 Oct 2011 09:33:52 -0700
X-Trace: forums-1-dub 1318955632 10.22.241.152 (18 Oct 2011 09:33:52 -0700)
X-Original-Trace: 18 Oct 2011 09:33:52 -0700, vip152.sybase.com
Lines: 48
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30614
Article PK: 73504

On 18 Oct 2011 01:55:03 -0700, Rob V
<rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:

Rob,

I think you gave him the proper response. I was a bit surprised that
the xml resultset is returned as a single text field but this is the
way it works.

Jay

>On 18-Oct-2011 10:07, Eisen wrote:
>> Dear all
>> I found a new feather of ASE15 is xml service -- add "for
>> xml" after the select statement could transfer the resultset
>> into a XML file formant. I'm very interested in it. But soon
>> I found the XML file' size can't exceed @@textsize, so if
>> the resultset is a large one, the output will truncated.
>> Would anyone know how to configure it to support more rows
>> only limited in one @@textsize? Thanks.
>>
>> Best Regards
>> Eisen
>
>@@textsize can be changed by running 'set textsize <number>'. But I have
>the feeling that is not what you mean?
>If so, an you elaborate?
>
>--
>HTH,
>
>Rob V.
>-----------------------------------------------------------------
>Rob Verschoor
>
>Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
>and Replication Server 15.0.1/12.5 // TeamSybase
>
>Author of Sybase books (order online at www.sypron.nl/shop):
>"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
>"The Complete Sybase ASE Quick Reference Guide"
>"The Complete Sybase Replication Server Quick Reference Guide"
>
>rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
>Sypron B.V., The Netherlands | Chamber of Commerce 27138666
>-----------------------------------------------------------------
>


Eisen Posted on 2011-10-19 02:02:57.0Z
Sender: 7f04.4e9cf3ea.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Re: Can select ... for xml support more rows?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4e9e2fd1.5a4e.1681692777@sybase.com>
References: <4e9d3ee7$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 18 Oct 2011 19:02:57 -0700
X-Trace: forums-1-dub 1318989777 10.22.241.41 (18 Oct 2011 19:02:57 -0700)
X-Original-Trace: 18 Oct 2011 19:02:57 -0700, 10.22.241.41
Lines: 60
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30615
Article PK: 73508

Dear Rob and J
Thanks for your reply.
to Rob
Yes. I'm using "set textsize xxx " to fix that issue now.
But actually as J said I'm also surprised that ASE used a
variable to store the entire xml output... and with more
test I found the textsize can't be set too large --
otherwise it would raise error like below :
1> set textsize 1073741824
2> go
1> select * from tb_wj_debug for xml
2> go
Fatal error: memory allocation failed.
I totally have no idea of this function now.

Best Regards
Eisen

> On 18-Oct-2011 10:07, Eisen wrote:
> > Dear all
> > I found a new feather of ASE15 is xml service -- add
> > "for xml" after the select statement could transfer the
> > resultset into a XML file formant. I'm very interested
> > in it. But soon I found the XML file' size can't exceed
> > @@textsize, so if the resultset is a large one, the
> > output will truncated. Would anyone know how to
> > configure it to support more rows only limited in one
> @@textsize? Thanks. >
> > Best Regards
> > Eisen
>
> @@textsize can be changed by running 'set textsize
> <number>'. But I have the feeling that is not what you
> mean? If so, an you elaborate?
>
> --
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Sybase Professional DBA for ASE
> 15.0/12.5/12.0/11.5/11.0 and Replication Server
> 15.0.1/12.5 // TeamSybase
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
> Reference Guide" "The Complete Sybase Replication Server
> Quick Reference Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------
>


Rob V Posted on 2011-10-19 05:42:23.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Can select ... for xml support more rows?
References: <4e9d3ee7$1@forums-1-dub> <4e9e2fd1.5a4e.1681692777@sybase.com>
In-Reply-To: <4e9e2fd1.5a4e.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e9e633f$1@forums-1-dub>
Date: 18 Oct 2011 22:42:23 -0700
X-Trace: forums-1-dub 1319002943 10.22.241.152 (18 Oct 2011 22:42:23 -0700)
X-Original-Trace: 18 Oct 2011 22:42:23 -0700, vip152.sybase.com
Lines: 90
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30617
Article PK: 73509

Hm... something does not look right (in ASE, not in your test). Some
questions:
- what is our precise ASE version?
- are there any messages in your errorlog?
- what is the size of your table tb_wj_debug? (check with sp_spaceused)

Rob V.

On 19-Oct-2011 04:02, Eisen wrote:
> Dear Rob and J
> Thanks for your reply.
> to Rob
> Yes. I'm using "set textsize xxx " to fix that issue now.
> But actually as J said I'm also surprised that ASE used a
> variable to store the entire xml output... and with more
> test I found the textsize can't be set too large --
> otherwise it would raise error like below :
> 1> set textsize 1073741824
> 2> go
> 1> select * from tb_wj_debug for xml
> 2> go
> Fatal error: memory allocation failed.
> I totally have no idea of this function now.
>
> Best Regards
> Eisen
>
>
>
>> On 18-Oct-2011 10:07, Eisen wrote:
>>> Dear all
>>> I found a new feather of ASE15 is xml service -- add
>>> "for xml" after the select statement could transfer the
>>> resultset into a XML file formant. I'm very interested
>>> in it. But soon I found the XML file' size can't exceed
>>> @@textsize, so if the resultset is a large one, the
>>> output will truncated. Would anyone know how to
>>> configure it to support more rows only limited in one
>> @@textsize? Thanks.>
>>> Best Regards
>>> Eisen
>> @@textsize can be changed by running 'set textsize
>> <number>'. But I have the feeling that is not what you
>> mean? If so, an you elaborate?
>>
>> --
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Sybase Professional DBA for ASE
>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>> 15.0.1/12.5 // TeamSybase
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>> Reference Guide" "The Complete Sybase Replication Server
>> Quick Reference Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
>>

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Rob V Posted on 2011-10-19 08:25:22.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Can select ... for xml support more rows?
References: <4e9e633f$1@forums-1-dub> <4e9e6fa3.6ed7.1681692777@sybase.com>
In-Reply-To: <4e9e6fa3.6ed7.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e9e8972$1@forums-1-dub>
Date: 19 Oct 2011 01:25:22 -0700
X-Trace: forums-1-dub 1319012722 10.22.241.152 (19 Oct 2011 01:25:22 -0700)
X-Original-Trace: 19 Oct 2011 01:25:22 -0700, vip152.sybase.com
Lines: 150
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30619
Article PK: 73510

I can reproduce this with a small and simple table, without any text or
image column present.
I think this is incorrect, and I filed a bug for it.
If this represents a problem for you, please open a case with Sybase
TechSupport and request that your case be linked with CR 687707
(TechSupport won't be able to solve the issue for you, but your case
will increase the visibility and priority of the problem internally at
SYbase -- this helps getting it fixed).

Sorry not to have a better answer...

HTH,

Rob V.

On 19-Oct-2011 08:35, Eisen wrote:
> Hi Rob
> Thanks for your kindness. Here's the answer:
> 1. My ASE is a ASE15.0.3ESD#4 for AIX with 16G memory and
> 8G datacache.
> 2. No error messages in the error log file
> 3. the size of tb_wj_debug is not huge --
> 1> sp_spaceused tb_wj_debug
> 2> go
> name rowtotal
> reserved data
> index_size unused
> --------------------------------- ------------
> ------------------------ -------------
> ------------------------------ -------
> tb_wj_debug 5931
> 960 KB 512 KB
> 320 KB 128 KB
>
> Best Regards
> Eisen
>> Hm... something does not look right (in ASE, not in your
>> test). Some questions:
>> - what is our precise ASE version?
>> - are there any messages in your errorlog?
>> - what is the size of your table tb_wj_debug? (check with
>> sp_spaceused)
>>
>> Rob V.
>>
>>
>> On 19-Oct-2011 04:02, Eisen wrote:
>>> Dear Rob and J
>>> Thanks for your reply.
>>> to Rob
>>> Yes. I'm using "set textsize xxx " to fix that issue
>>> now. But actually as J said I'm also surprised that ASE
>>> used a variable to store the entire xml output... and
>>> with more test I found the textsize can't be set too
>>> large -- otherwise it would raise error like below :
>>> 1> set textsize 1073741824
>>> 2> go
>>> 1> select * from tb_wj_debug for xml
>>> 2> go
>>> Fatal error: memory allocation failed.
>>> I totally have no idea of this function now.
>>>
>>> Best Regards
>>> Eisen
>>>
>>>
>>>
>>>> On 18-Oct-2011 10:07, Eisen wrote:
>>>>> Dear all
>>>>> I found a new feather of ASE15 is xml service --
>> add>>> "for xml" after the select statement could
>> transfer the>>> resultset into a XML file formant. I'm
>> very interested>>> in it. But soon I found the XML file'
>> size can't exceed>>> @@textsize, so if the resultset is a
>> large one, the>>> output will truncated. Would anyone
>> know how to>>> configure it to support more rows only
>> limited in one>> @@textsize? Thanks.>
>>>>> Best Regards
>>>>> Eisen
>>>> @@textsize can be changed by running 'set textsize
>>>> <number>'. But I have the feeling that is not what you
>>>> mean? If so, an you elaborate?
>>>>
>>>> --
>>>> HTH,
>>>>
>>>> Rob V.
>>>>
>> ----------------------------------------------------------
>>>> ------- Rob Verschoor>>
>>>> Certified Sybase Professional DBA for ASE
>>>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>>>> 15.0.1/12.5 // TeamSybase
>>>>
>>>> Author of Sybase books (order online at
>>>> www.sypron.nl/shop): "Tips, Tricks& Recipes for
>> Sybase>> ASE" (ASE 15 edition) "The Complete Sybase ASE
>> Quick>> Reference Guide" "The Complete Sybase Replication
>> Server>> Quick Reference Guide"
>>>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>>>> @rob_verschoor Sypron B.V., The Netherlands | Chamber
>> of>> Commerce 27138666
>> ----------------------------------------------------------
>>>> ------->>
>>
>> --
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Sybase Professional DBA for ASE
>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>> 15.0.1/12.5 // TeamSybase
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>> Reference Guide" "The Complete Sybase Replication Server
>> Quick Reference Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
>>

--
HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


J Posted on 2011-10-19 16:08:09.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Can select ... for xml support more rows?
Reply-To: J@bogusemailAddress.com
Message-ID: <4e9eed14.88976218@forums.sybase.com>
References: <4e9d3ee7$1@forums-1-dub> <4e9e2fd1.5a4e.1681692777@sybase.com> <4e9e633f$1@forums-1-dub>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 19 Oct 2011 09:08:09 -0700
X-Trace: forums-1-dub 1319040489 10.22.241.152 (19 Oct 2011 09:08:09 -0700)
X-Original-Trace: 19 Oct 2011 09:08:09 -0700, vip152.sybase.com
Lines: 100
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30621
Article PK: 73511

On 18 Oct 2011 22:42:23 -0700, Rob V
<rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:

From another problem that I had with text in the past, I suspect that
the memory which is not large enough to handle his text output might
be "procedure cache".

Jay

>Hm... something does not look right (in ASE, not in your test). Some
>questions:
>- what is our precise ASE version?
>- are there any messages in your errorlog?
>- what is the size of your table tb_wj_debug? (check with sp_spaceused)
>
>Rob V.
>
>
>On 19-Oct-2011 04:02, Eisen wrote:
>> Dear Rob and J
>> Thanks for your reply.
>> to Rob
>> Yes. I'm using "set textsize xxx " to fix that issue now.
>> But actually as J said I'm also surprised that ASE used a
>> variable to store the entire xml output... and with more
>> test I found the textsize can't be set too large --
>> otherwise it would raise error like below :
>> 1> set textsize 1073741824
>> 2> go
>> 1> select * from tb_wj_debug for xml
>> 2> go
>> Fatal error: memory allocation failed.
>> I totally have no idea of this function now.
>>
>> Best Regards
>> Eisen
>>
>>
>>
>>> On 18-Oct-2011 10:07, Eisen wrote:
>>>> Dear all
>>>> I found a new feather of ASE15 is xml service -- add
>>>> "for xml" after the select statement could transfer the
>>>> resultset into a XML file formant. I'm very interested
>>>> in it. But soon I found the XML file' size can't exceed
>>>> @@textsize, so if the resultset is a large one, the
>>>> output will truncated. Would anyone know how to
>>>> configure it to support more rows only limited in one
>>> @@textsize? Thanks.>
>>>> Best Regards
>>>> Eisen
>>> @@textsize can be changed by running 'set textsize
>>> <number>'. But I have the feeling that is not what you
>>> mean? If so, an you elaborate?
>>>
>>> --
>>> HTH,
>>>
>>> Rob V.
>>> ----------------------------------------------------------
>>> ------- Rob Verschoor
>>>
>>> Certified Sybase Professional DBA for ASE
>>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>>> 15.0.1/12.5 // TeamSybase
>>>
>>> Author of Sybase books (order online at
>>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>>> Reference Guide" "The Complete Sybase Replication Server
>>> Quick Reference Guide"
>>>
>>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>>> Commerce 27138666
>>> ----------------------------------------------------------
>>> -------
>>>
>
>
>--
>HTH,
>
>Rob V.
>-----------------------------------------------------------------
>Rob Verschoor
>
>Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
>and Replication Server 15.0.1/12.5 // TeamSybase
>
>Author of Sybase books (order online at www.sypron.nl/shop):
>"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
>"The Complete Sybase ASE Quick Reference Guide"
>"The Complete Sybase Replication Server Quick Reference Guide"
>
>rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
>Sypron B.V., The Netherlands | Chamber of Commerce 27138666
>-----------------------------------------------------------------
>


J Posted on 2011-10-19 20:59:17.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Can select ... for xml support more rows?
Reply-To: J@bogusemailAddress.com
Message-ID: <4e9f39f3.108654828@forums.sybase.com>
References: <4e9d3ee7$1@forums-1-dub> <4e9e2fd1.5a4e.1681692777@sybase.com> <4e9e633f$1@forums-1-dub> <4e9eed14.88976218@forums.sybase.com>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 19 Oct 2011 13:59:17 -0700
X-Trace: forums-1-dub 1319057957 10.22.241.152 (19 Oct 2011 13:59:17 -0700)
X-Original-Trace: 19 Oct 2011 13:59:17 -0700, vip152.sybase.com
Lines: 108
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30624
Article PK: 73514

On 19 Oct 2011 09:08:09 -0700, jtotally_bogus@sbcglobal.net (J) wrote:

I was wrong about the Procedure Cache - see Paul Dow's correct posting
about the isql memory allocation being the problem.

Jay

>On 18 Oct 2011 22:42:23 -0700, Rob V
><rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY> wrote:
>
>From another problem that I had with text in the past, I suspect that
>the memory which is not large enough to handle his text output might
>be "procedure cache".
>
>Jay
>
>>Hm... something does not look right (in ASE, not in your test). Some
>>questions:
>>- what is our precise ASE version?
>>- are there any messages in your errorlog?
>>- what is the size of your table tb_wj_debug? (check with sp_spaceused)
>>
>>Rob V.
>>
>>
>>On 19-Oct-2011 04:02, Eisen wrote:
>>> Dear Rob and J
>>> Thanks for your reply.
>>> to Rob
>>> Yes. I'm using "set textsize xxx " to fix that issue now.
>>> But actually as J said I'm also surprised that ASE used a
>>> variable to store the entire xml output... and with more
>>> test I found the textsize can't be set too large --
>>> otherwise it would raise error like below :
>>> 1> set textsize 1073741824
>>> 2> go
>>> 1> select * from tb_wj_debug for xml
>>> 2> go
>>> Fatal error: memory allocation failed.
>>> I totally have no idea of this function now.
>>>
>>> Best Regards
>>> Eisen
>>>
>>>
>>>
>>>> On 18-Oct-2011 10:07, Eisen wrote:
>>>>> Dear all
>>>>> I found a new feather of ASE15 is xml service -- add
>>>>> "for xml" after the select statement could transfer the
>>>>> resultset into a XML file formant. I'm very interested
>>>>> in it. But soon I found the XML file' size can't exceed
>>>>> @@textsize, so if the resultset is a large one, the
>>>>> output will truncated. Would anyone know how to
>>>>> configure it to support more rows only limited in one
>>>> @@textsize? Thanks.>
>>>>> Best Regards
>>>>> Eisen
>>>> @@textsize can be changed by running 'set textsize
>>>> <number>'. But I have the feeling that is not what you
>>>> mean? If so, an you elaborate?
>>>>
>>>> --
>>>> HTH,
>>>>
>>>> Rob V.
>>>> ----------------------------------------------------------
>>>> ------- Rob Verschoor
>>>>
>>>> Certified Sybase Professional DBA for ASE
>>>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>>>> 15.0.1/12.5 // TeamSybase
>>>>
>>>> Author of Sybase books (order online at
>>>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>>>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>>>> Reference Guide" "The Complete Sybase Replication Server
>>>> Quick Reference Guide"
>>>>
>>>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>>>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>>>> Commerce 27138666
>>>> ----------------------------------------------------------
>>>> -------
>>>>
>>
>>
>>--
>>HTH,
>>
>>Rob V.
>>-----------------------------------------------------------------
>>Rob Verschoor
>>
>>Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
>>and Replication Server 15.0.1/12.5 // TeamSybase
>>
>>Author of Sybase books (order online at www.sypron.nl/shop):
>>"Tips, Tricks& Recipes for Sybase ASE" (ASE 15 edition)
>>"The Complete Sybase ASE Quick Reference Guide"
>>"The Complete Sybase Replication Server Quick Reference Guide"
>>
>>rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
>>Sypron B.V., The Netherlands | Chamber of Commerce 27138666
>>-----------------------------------------------------------------
>>
>


Paul Dow <Paul.Dow Posted on 2011-10-19 09:46:37.0Z
From: Paul Dow <Paul.Dow@sybase_dot.com>
Organization: Sybase Engineering/iMET
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Can select ... for xml support more rows?
References: <4e9d3ee7$1@forums-1-dub> <4e9e2fd1.5a4e.1681692777@sybase.com>
In-Reply-To: <4e9e2fd1.5a4e.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4e9e9c7d$1@forums-1-dub>
Date: 19 Oct 2011 02:46:37 -0700
X-Trace: forums-1-dub 1319017597 10.22.241.152 (19 Oct 2011 02:46:37 -0700)
X-Original-Trace: 19 Oct 2011 02:46:37 -0700, vip152.sybase.com
Lines: 111
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30620
Article PK: 73516

Eisen,

The Fatal error seems to be an isql/OCS (i.e. client size) raised error. Running isql under truss shows lots
of memory being allocated. I am guessing in anticipation of the large potential textsize
as I believe that ASE will be trying to return the WHOLE requested result set as a single object.

You are liable to hit similar memory allocation errors with something like select convert( text, "aaaaaaa" )
when textsize if very big.

On a Solaris 2.9 box, for xml, I noticed that the 1,073,741,824 value for textsize
trigged a memory allocation error. 1,000,000,000 worked though.
You may want to try dropping the number to a much smaller value though....

Note that as the numbers of rows increases, the 15.0.3 server I tried this on started to report a
712 error (There is insufficient heap memory to allocate 0 bytes. Please increase
configuration parameter 'heap memory per user' or try again when there is less activity on the system.)


What you may want to do for larger tables is to use the for xml in sets of rows instead of for the whole table
and then re-assemble it at the client size if you want a single XML representation of the table (should be pretty straight
forwards with a bit of scripting/processing by carefully deleting the output between the intermediate </resultset> and
<resultset ...> lines inclusive e.g.

....
</row>
</resultset>


(1 row affected)

-------------------------------------------------------------------------------------------------- (lots of -'s removed)
<row>
.....

There may be smarter solutions (I've not used the for xml much).

Note that the (1 row affected) also gives an indication to the challenge - the for xml request
seems to try to return the whole requested result set as a SINGLE row - which is
logical when you think about it.

- Paul

On 10/19/2011 3:02 AM, Eisen wrote:
> Dear Rob and J
> Thanks for your reply.
> to Rob
> Yes. I'm using "set textsize xxx " to fix that issue now.
> But actually as J said I'm also surprised that ASE used a
> variable to store the entire xml output... and with more
> test I found the textsize can't be set too large --
> otherwise it would raise error like below :
> 1> set textsize 1073741824
> 2> go
> 1> select * from tb_wj_debug for xml
> 2> go
> Fatal error: memory allocation failed.
> I totally have no idea of this function now.
>
> Best Regards
> Eisen
>
>
>
>> On 18-Oct-2011 10:07, Eisen wrote:
>>> Dear all
>>> I found a new feather of ASE15 is xml service -- add
>>> "for xml" after the select statement could transfer the
>>> resultset into a XML file formant. I'm very interested
>>> in it. But soon I found the XML file' size can't exceed
>>> @@textsize, so if the resultset is a large one, the
>>> output will truncated. Would anyone know how to
>>> configure it to support more rows only limited in one
>> @@textsize? Thanks.>
>>> Best Regards
>>> Eisen
>>
>> @@textsize can be changed by running 'set textsize
>> <number>'. But I have the feeling that is not what you
>> mean? If so, an you elaborate?
>>
>> --
>> HTH,
>>
>> Rob V.
>> ----------------------------------------------------------
>> ------- Rob Verschoor
>>
>> Certified Sybase Professional DBA for ASE
>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>> 15.0.1/12.5 // TeamSybase
>>
>> Author of Sybase books (order online at
>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>> Reference Guide" "The Complete Sybase Replication Server
>> Quick Reference Guide"
>>
>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>> Commerce 27138666
>> ----------------------------------------------------------
>> -------
>>


J Posted on 2011-10-19 16:12:24.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Can select ... for xml support more rows?
Reply-To: J@bogusemailAddress.com
Message-ID: <4e9ef63c.91319890@forums.sybase.com>
References: <4e9d3ee7$1@forums-1-dub> <4e9e2fd1.5a4e.1681692777@sybase.com> <4e9e9c7d$1@forums-1-dub>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 19 Oct 2011 09:12:24 -0700
X-Trace: forums-1-dub 1319040744 10.22.241.152 (19 Oct 2011 09:12:24 -0700)
X-Original-Trace: 19 Oct 2011 09:12:24 -0700, vip152.sybase.com
Lines: 125
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30622
Article PK: 73512

On 19 Oct 2011 02:46:37 -0700, Paul Dow <Paul.Dow@sybase_dot.com>
wrote:

Paul,

<speculation> I think this could be procedure cache memory from
another problem that I had in the past (it was on blob input), where
ASE used a constant node for the text/image. I filed CR we need to use
2x the text size in procedure cache.
<speculation off>

Jay

>Eisen,
>
>The Fatal error seems to be an isql/OCS (i.e. client size) raised error. Running isql under truss shows lots
>of memory being allocated. I am guessing in anticipation of the large potential textsize
>as I believe that ASE will be trying to return the WHOLE requested result set as a single object.
>
>You are liable to hit similar memory allocation errors with something like select convert( text, "aaaaaaa" )
>when textsize if very big.
>
>On a Solaris 2.9 box, for xml, I noticed that the 1,073,741,824 value for textsize
>trigged a memory allocation error. 1,000,000,000 worked though.
>You may want to try dropping the number to a much smaller value though....
>
>Note that as the numbers of rows increases, the 15.0.3 server I tried this on started to report a
>712 error (There is insufficient heap memory to allocate 0 bytes. Please increase
>configuration parameter 'heap memory per user' or try again when there is less activity on the system.)
>
>
>What you may want to do for larger tables is to use the for xml in sets of rows instead of for the whole table
>and then re-assemble it at the client size if you want a single XML representation of the table (should be pretty straight
>forwards with a bit of scripting/processing by carefully deleting the output between the intermediate </resultset> and
><resultset ...> lines inclusive e.g.
>
> ....
> </row>
></resultset>
>
>
>(1 row affected)
>
> -------------------------------------------------------------------------------------------------- (lots of -'s removed)
> <row>
> .....
>
>There may be smarter solutions (I've not used the for xml much).
>
>Note that the (1 row affected) also gives an indication to the challenge - the for xml request
>seems to try to return the whole requested result set as a SINGLE row - which is
>logical when you think about it.
>
>- Paul
>
>
>
>
>
>
>
>
>On 10/19/2011 3:02 AM, Eisen wrote:
>> Dear Rob and J
>> Thanks for your reply.
>> to Rob
>> Yes. I'm using "set textsize xxx " to fix that issue now.
>> But actually as J said I'm also surprised that ASE used a
>> variable to store the entire xml output... and with more
>> test I found the textsize can't be set too large --
>> otherwise it would raise error like below :
>> 1> set textsize 1073741824
>> 2> go
>> 1> select * from tb_wj_debug for xml
>> 2> go
>> Fatal error: memory allocation failed.
>> I totally have no idea of this function now.
>>
>> Best Regards
>> Eisen
>>
>>
>>
>>> On 18-Oct-2011 10:07, Eisen wrote:
>>>> Dear all
>>>> I found a new feather of ASE15 is xml service -- add
>>>> "for xml" after the select statement could transfer the
>>>> resultset into a XML file formant. I'm very interested
>>>> in it. But soon I found the XML file' size can't exceed
>>>> @@textsize, so if the resultset is a large one, the
>>>> output will truncated. Would anyone know how to
>>>> configure it to support more rows only limited in one
>>> @@textsize? Thanks.>
>>>> Best Regards
>>>> Eisen
>>>
>>> @@textsize can be changed by running 'set textsize
>>> <number>'. But I have the feeling that is not what you
>>> mean? If so, an you elaborate?
>>>
>>> --
>>> HTH,
>>>
>>> Rob V.
>>> ----------------------------------------------------------
>>> ------- Rob Verschoor
>>>
>>> Certified Sybase Professional DBA for ASE
>>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>>> 15.0.1/12.5 // TeamSybase
>>>
>>> Author of Sybase books (order online at
>>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>>> Reference Guide" "The Complete Sybase Replication Server
>>> Quick Reference Guide"
>>>
>>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>>> Commerce 27138666
>>> ----------------------------------------------------------
>>> -------
>>>
>


J Posted on 2011-10-19 20:58:17.0Z
From: jtotally_bogus@sbcglobal.net (J)
Newsgroups: sybase.public.ase.general
Subject: Re: Can select ... for xml support more rows?
Reply-To: J@bogusemailAddress.com
Message-ID: <4e9f39b1.108589187@forums.sybase.com>
References: <4e9d3ee7$1@forums-1-dub> <4e9e2fd1.5a4e.1681692777@sybase.com> <4e9e9c7d$1@forums-1-dub>
X-Newsreader: Forte Free Agent 1.21/32.243
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 19 Oct 2011 13:58:17 -0700
X-Trace: forums-1-dub 1319057897 10.22.241.152 (19 Oct 2011 13:58:17 -0700)
X-Original-Trace: 19 Oct 2011 13:58:17 -0700, vip152.sybase.com
Lines: 123
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30623
Article PK: 73513

On 19 Oct 2011 02:46:37 -0700, Paul Dow <Paul.Dow@sybase_dot.com>
wrote:

Paul,

You had this right. Please ignore my wrong comment about Procedure
Cache. I can see it is memory allocation at the client that is the
problem.

Jay

>Eisen,
>
>The Fatal error seems to be an isql/OCS (i.e. client size) raised error. Running isql under truss shows lots
>of memory being allocated. I am guessing in anticipation of the large potential textsize
>as I believe that ASE will be trying to return the WHOLE requested result set as a single object.
>
>You are liable to hit similar memory allocation errors with something like select convert( text, "aaaaaaa" )
>when textsize if very big.
>
>On a Solaris 2.9 box, for xml, I noticed that the 1,073,741,824 value for textsize
>trigged a memory allocation error. 1,000,000,000 worked though.
>You may want to try dropping the number to a much smaller value though....
>
>Note that as the numbers of rows increases, the 15.0.3 server I tried this on started to report a
>712 error (There is insufficient heap memory to allocate 0 bytes. Please increase
>configuration parameter 'heap memory per user' or try again when there is less activity on the system.)
>
>
>What you may want to do for larger tables is to use the for xml in sets of rows instead of for the whole table
>and then re-assemble it at the client size if you want a single XML representation of the table (should be pretty straight
>forwards with a bit of scripting/processing by carefully deleting the output between the intermediate </resultset> and
><resultset ...> lines inclusive e.g.
>
> ....
> </row>
></resultset>
>
>
>(1 row affected)
>
> -------------------------------------------------------------------------------------------------- (lots of -'s removed)
> <row>
> .....
>
>There may be smarter solutions (I've not used the for xml much).
>
>Note that the (1 row affected) also gives an indication to the challenge - the for xml request
>seems to try to return the whole requested result set as a SINGLE row - which is
>logical when you think about it.
>
>- Paul
>
>
>
>
>
>
>
>
>On 10/19/2011 3:02 AM, Eisen wrote:
>> Dear Rob and J
>> Thanks for your reply.
>> to Rob
>> Yes. I'm using "set textsize xxx " to fix that issue now.
>> But actually as J said I'm also surprised that ASE used a
>> variable to store the entire xml output... and with more
>> test I found the textsize can't be set too large --
>> otherwise it would raise error like below :
>> 1> set textsize 1073741824
>> 2> go
>> 1> select * from tb_wj_debug for xml
>> 2> go
>> Fatal error: memory allocation failed.
>> I totally have no idea of this function now.
>>
>> Best Regards
>> Eisen
>>
>>
>>
>>> On 18-Oct-2011 10:07, Eisen wrote:
>>>> Dear all
>>>> I found a new feather of ASE15 is xml service -- add
>>>> "for xml" after the select statement could transfer the
>>>> resultset into a XML file formant. I'm very interested
>>>> in it. But soon I found the XML file' size can't exceed
>>>> @@textsize, so if the resultset is a large one, the
>>>> output will truncated. Would anyone know how to
>>>> configure it to support more rows only limited in one
>>> @@textsize? Thanks.>
>>>> Best Regards
>>>> Eisen
>>>
>>> @@textsize can be changed by running 'set textsize
>>> <number>'. But I have the feeling that is not what you
>>> mean? If so, an you elaborate?
>>>
>>> --
>>> HTH,
>>>
>>> Rob V.
>>> ----------------------------------------------------------
>>> ------- Rob Verschoor
>>>
>>> Certified Sybase Professional DBA for ASE
>>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>>> 15.0.1/12.5 // TeamSybase
>>>
>>> Author of Sybase books (order online at
>>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>>> Reference Guide" "The Complete Sybase Replication Server
>>> Quick Reference Guide"
>>>
>>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>>> Commerce 27138666
>>> ----------------------------------------------------------
>>> -------
>>>
>


Paul Dow <Paul.Dow Posted on 2011-10-20 12:09:42.0Z
From: Paul Dow <Paul.Dow@sybase_dot.com>
Organization: Sybase Engineering/iMET
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Can select ... for xml support more rows?
References: <4e9d3ee7$1@forums-1-dub> <4e9e2fd1.5a4e.1681692777@sybase.com> <4e9e9c7d$1@forums-1-dub> <4e9f39b1.108589187@forums.sybase.com>
In-Reply-To: <4e9f39b1.108589187@forums.sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ea00f86$1@forums-1-dub>
Date: 20 Oct 2011 05:09:42 -0700
X-Trace: forums-1-dub 1319112582 10.22.241.152 (20 Oct 2011 05:09:42 -0700)
X-Original-Trace: 20 Oct 2011 05:09:42 -0700, vip152.sybase.com
Lines: 135
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30625
Article PK: 73515

No problem.

Once the client memory allocation issue is resolved (change the
textsize to a suitable value or perhaps use a 64 bit client), then I think
there will be some ASE config limits that will then start to be hit as the xml result
size increases.

- Paul.

On 10/19/2011 9:58 PM, J wrote:
> On 19 Oct 2011 02:46:37 -0700, Paul Dow<Paul.Dow@sybase_dot.com>
> wrote:
>
> Paul,
>
> You had this right. Please ignore my wrong comment about Procedure
> Cache. I can see it is memory allocation at the client that is the
> problem.
>
> Jay
>
>> Eisen,
>>
>> The Fatal error seems to be an isql/OCS (i.e. client size) raised error. Running isql under truss shows lots
>> of memory being allocated. I am guessing in anticipation of the large potential textsize
>> as I believe that ASE will be trying to return the WHOLE requested result set as a single object.
>>
>> You are liable to hit similar memory allocation errors with something like select convert( text, "aaaaaaa" )
>> when textsize if very big.
>>
>> On a Solaris 2.9 box, for xml, I noticed that the 1,073,741,824 value for textsize
>> trigged a memory allocation error. 1,000,000,000 worked though.
>> You may want to try dropping the number to a much smaller value though....
>>
>> Note that as the numbers of rows increases, the 15.0.3 server I tried this on started to report a
>> 712 error (There is insufficient heap memory to allocate 0 bytes. Please increase
>> configuration parameter 'heap memory per user' or try again when there is less activity on the system.)
>>
>>
>> What you may want to do for larger tables is to use the for xml in sets of rows instead of for the whole table
>> and then re-assemble it at the client size if you want a single XML representation of the table (should be pretty straight
>> forwards with a bit of scripting/processing by carefully deleting the output between the intermediate</resultset> and
>> <resultset ...> lines inclusive e.g.
>>
>> ....
>> </row>
>> </resultset>
>>
>>
>> (1 row affected)
>>
>> -------------------------------------------------------------------------------------------------- (lots of -'s removed)
>> <row>
>> .....
>>
>> There may be smarter solutions (I've not used the for xml much).
>>
>> Note that the (1 row affected) also gives an indication to the challenge - the for xml request
>> seems to try to return the whole requested result set as a SINGLE row - which is
>> logical when you think about it.
>>
>> - Paul
>>
>>
>>
>>
>>
>>
>>
>>
>> On 10/19/2011 3:02 AM, Eisen wrote:
>>> Dear Rob and J
>>> Thanks for your reply.
>>> to Rob
>>> Yes. I'm using "set textsize xxx " to fix that issue now.
>>> But actually as J said I'm also surprised that ASE used a
>>> variable to store the entire xml output... and with more
>>> test I found the textsize can't be set too large --
>>> otherwise it would raise error like below :
>>> 1> set textsize 1073741824
>>> 2> go
>>> 1> select * from tb_wj_debug for xml
>>> 2> go
>>> Fatal error: memory allocation failed.
>>> I totally have no idea of this function now.
>>>
>>> Best Regards
>>> Eisen
>>>
>>>
>>>
>>>> On 18-Oct-2011 10:07, Eisen wrote:
>>>>> Dear all
>>>>> I found a new feather of ASE15 is xml service -- add
>>>>> "for xml" after the select statement could transfer the
>>>>> resultset into a XML file formant. I'm very interested
>>>>> in it. But soon I found the XML file' size can't exceed
>>>>> @@textsize, so if the resultset is a large one, the
>>>>> output will truncated. Would anyone know how to
>>>>> configure it to support more rows only limited in one
>>>> @@textsize? Thanks.>
>>>>> Best Regards
>>>>> Eisen
>>>>
>>>> @@textsize can be changed by running 'set textsize
>>>> <number>'. But I have the feeling that is not what you
>>>> mean? If so, an you elaborate?
>>>>
>>>> --
>>>> HTH,
>>>>
>>>> Rob V.
>>>> ----------------------------------------------------------
>>>> ------- Rob Verschoor
>>>>
>>>> Certified Sybase Professional DBA for ASE
>>>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
>>>> 15.0.1/12.5 // TeamSybase
>>>>
>>>> Author of Sybase books (order online at
>>>> www.sypron.nl/shop): "Tips, Tricks& Recipes for Sybase
>>>> ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
>>>> Reference Guide" "The Complete Sybase Replication Server
>>>> Quick Reference Guide"
>>>>
>>>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
>>>> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
>>>> Commerce 27138666
>>>> ----------------------------------------------------------
>>>> -------
>>>>
>>
>


Eisen Posted on 2011-10-21 05:37:05.0Z
Sender: 3f8c.4ea0ffcb.1804289383@sybase.com
From: Eisen
Newsgroups: sybase.public.ase.general
Subject: Re: Can select ... for xml support more rows?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ea10501.407b.1681692777@sybase.com>
References: <4ea00f86$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 20 Oct 2011 22:37:05 -0700
X-Trace: forums-1-dub 1319175425 10.22.241.41 (20 Oct 2011 22:37:05 -0700)
X-Original-Trace: 20 Oct 2011 22:37:05 -0700, 10.22.241.41
Lines: 154
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30629
Article PK: 73520

Thanks for all
Now I know it's a client error not the server side ones.
Thanks you again

Best Regards
Eisen

> No problem.
>
> Once the client memory allocation issue is resolved
> (change the textsize to a suitable value or perhaps use a
> 64 bit client), then I think there will be some ASE config
> limits that will then start to be hit as the xml result
> size increases.
>
> - Paul.
>
>
> On 10/19/2011 9:58 PM, J wrote:
> > On 19 Oct 2011 02:46:37 -0700, Paul
> > Dow<Paul.Dow@sybase_dot.com> wrote:
> >
> > Paul,
> >
> > You had this right. Please ignore my wrong comment about
> > Procedure Cache. I can see it is memory allocation at
> > the client that is the problem.
> >
> > Jay
> >
> >> Eisen,
> >>
> >> The Fatal error seems to be an isql/OCS (i.e. client
> size) raised error. Running isql under truss shows lots
> >> of memory being allocated. I am guessing in
> anticipation of the large potential textsize >> as I
> believe that ASE will be trying to return the WHOLE
> requested result set as a single object. >>
> >> You are liable to hit similar memory allocation errors
> with something like select convert( text, "aaaaaaa" ) >>
> when textsize if very big. >>
> >> On a Solaris 2.9 box, for xml, I noticed that the
> 1,073,741,824 value for textsize >> trigged a memory
> allocation error. 1,000,000,000 worked though. >> You may
> want to try dropping the number to a much smaller value
> though.... >>
> >> Note that as the numbers of rows increases, the 15.0.3
> server I tried this on started to report a >> 712 error
> (There is insufficient heap memory to allocate 0 bytes.
> Please increase >> configuration parameter 'heap memory
> per user' or try again when there is less activity on the
> system.) >>
> >>
> >> What you may want to do for larger tables is to use the
> for xml in sets of rows instead of for the whole table >>
> and then re-assemble it at the client size if you want a
> single XML representation of the table (should be pretty
> straight >> forwards with a bit of scripting/processing by
> carefully deleting the output between the
> intermediate</resultset> and >> <resultset ...> lines
> inclusive e.g. >>
> >> ....
> >> </row>
> >> </resultset>
> >>
> >>
> >> (1 row affected)
> >>
> >>
> ----------------------------------------------------------
> ---------------------------------------- (lots of -'s
> removed) >> <row>
> >> .....
> >>
> >> There may be smarter solutions (I've not used the for
> xml much). >>
> >> Note that the (1 row affected) also gives an indication
> to the challenge - the for xml request >> seems to try to
> return the whole requested result set as a SINGLE row -
> which is >> logical when you think about it.
> >>
> >> - Paul
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >>
> >> On 10/19/2011 3:02 AM, Eisen wrote:
> >>> Dear Rob and J
> >>> Thanks for your reply.
> >>> to Rob
> >>> Yes. I'm using "set textsize xxx " to fix that
> issue now. >>> But actually as J said I'm also surprised
> that ASE used a >>> variable to store the entire xml
> output... and with more >>> test I found the textsize
> can't be set too large -- >>> otherwise it would raise
> error like below : >>> 1> set textsize 1073741824
> >>> 2> go
> >>> 1> select * from tb_wj_debug for xml
> >>> 2> go
> >>> Fatal error: memory allocation failed.
> >>> I totally have no idea of this function now.
> >>>
> >>> Best Regards
> >>> Eisen
> >>>
> >>>
> >>>
> >>>> On 18-Oct-2011 10:07, Eisen wrote:
> >>>>> Dear all
> >>>>> I found a new feather of ASE15 is xml service
> -- add >>>>> "for xml" after the select statement could
> transfer the >>>>> resultset into a XML file formant. I'm
> very interested >>>>> in it. But soon I found the XML
> file' size can't exceed >>>>> @@textsize, so if the
> resultset is a large one, the >>>>> output will truncated.
> Would anyone know how to >>>>> configure it to support
> more rows only limited in one >>>> @@textsize? Thanks.>
> >>>>> Best Regards
> >>>>> Eisen
> >>>>
> >>>> @@textsize can be changed by running 'set textsize
> >>>> <number>'. But I have the feeling that is not what
> you >>>> mean? If so, an you elaborate?
> >>>>
> >>>> --
> >>>> HTH,
> >>>>
> >>>> Rob V.
> >>>>
> ----------------------------------------------------------
> >>>> ------- Rob Verschoor >>>>
> >>>> Certified Sybase Professional DBA for ASE
> >>>> 15.0/12.5/12.0/11.5/11.0 and Replication Server
> >>>> 15.0.1/12.5 // TeamSybase
> >>>>
> >>>> Author of Sybase books (order online at
> >>>> www.sypron.nl/shop): "Tips, Tricks& Recipes for
> Sybase >>>> ASE" (ASE 15 edition) "The Complete Sybase ASE
> Quick >>>> Reference Guide" "The Complete Sybase
> Replication Server >>>> Quick Reference Guide"
> >>>>
> >>>> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> >>>> @rob_verschoor Sypron B.V., The Netherlands |
> Chamber of >>>> Commerce 27138666
> >>>>
> ----------------------------------------------------------
> >>>> ------- >>>>
> >>
> >
>