Problem writing XML files.

[Pi]

Hi guys,

I have a stored procedure that I will subsequently post below this
message. What the stored procedure does is, it reads some specific
tables in a database and created XML off it.

The problem comes in when the data is bigger than a few hundred/
thousand characters. The data is truncated and the XML file is not
fully made.

Now i have been reading some posts by some people and tried using TEXT/
NTEXT/ IMAGE type to write the files but they give me errors which,
again, I am adding to this email.

STORED PROC:
****************************

CREATE PROCEDURE usrp_sp_makexmlfile
@str varchar(50),
@templatefile varchar(255),
@ReturnValue as image OUT

AS
SET NOCOUNT ON
DECLARE @strVar as varchar(8000)
/*DECLARE @ReturnValue as varchar(255)*/

Set @strVar = 'Select * from ' + @str + ' FOR XML AUTO'
Set @templatefile = 'c:\template.tpl'

EXEC (@strVar)

SELECT @ReturnValue
GO

THIS IS WHAT I GET WHEN I USE IMAGE/NTEXT/TEXT TYPE:
************************************************** *******

An unhandled exception of type 'System.InvalidOperationException'
occurred in system.data.dll

Additional information: Parameter 2: '@ReturnValue' of type: Byte[],
the property Size has an invalid size: 0
Also, if i try to add an integer value to the image/text/ntext like
8000 or something less than that, it tells me that the result has to be
discarded because the current process has had some error.
Can someone give me a suggestion on how to resolve this or an example
that would illustrate the solution? Thank you all in advance.

Pi.

[John Bell]

Hi

You may want to check out http://sqlxml.org/faqs.aspx?faq=29 but there may
be issues with line breaks for sp_makewebtask or look at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp

John

"Pi" <3.*********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi guys,

I have a stored procedure that I will subsequently post below this
message. What the stored procedure does is, it reads some specific
tables in a database and created XML off it.

The problem comes in when the data is bigger than a few hundred/
thousand characters. The data is truncated and the XML file is not
fully made.

Now i have been reading some posts by some people and tried using TEXT/
NTEXT/ IMAGE type to write the files but they give me errors which,
again, I am adding to this email.

STORED PROC:
****************************

CREATE PROCEDURE usrp_sp_makexmlfile
@str varchar(50),
@templatefile varchar(255),
@ReturnValue as image OUT

AS
SET NOCOUNT ON
DECLARE @strVar as varchar(8000)
/*DECLARE @ReturnValue as varchar(255)*/

Set @strVar = 'Select * from ' + @str + ' FOR XML AUTO'
Set @templatefile = 'c:\template.tpl'

EXEC (@strVar)

SELECT @ReturnValue
GO

THIS IS WHAT I GET WHEN I USE IMAGE/NTEXT/TEXT TYPE:
************************************************** *******

An unhandled exception of type 'System.InvalidOperationException'
occurred in system.data.dll

Additional information: Parameter 2: '@ReturnValue' of type: Byte[],
the property Size has an invalid size: 0
Also, if i try to add an integer value to the image/text/ntext like
8000 or something less than that, it tells me that the result has to be
discarded because the current process has had some error.
Can someone give me a suggestion on how to resolve this or an example
that would illustrate the solution? Thank you all in advance.

Pi.

[ZeldorBlat]

According to the Sybase ASE manual at :

<http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman/@Generic__BookTextView/4429;pt=4429#X>

<snip>
Restrictions on text and image columns
text and image columns cannot be used:
*As parameters to stored procedures or as values passed to
these parameters
*As local variables
</snip>

[John Bell]

Hi

With Microsoft SQL Server text is a valid datatype for parameter.

John
"ZeldorBlat" <ze********@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

According to the Sybase ASE manual at :

<http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/refman/@Generic__BookTextView/4429;pt=4429#X>

<snip>
Restrictions on text and image columns
text and image columns cannot be used:
*As parameters to stored procedures or as values passed to
these parameters
*As local variables
</snip>