Problem writing XML files.

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.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.something@.gmail.com> wrote in message
news:1126041751.328093.220490@.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.|||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|||Hi

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

John
"ZeldorBlat" <zeldorblat@.gmail.com> wrote in message
news:1126057842.315787.150600@.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

Problem writing to fixed width text file destination

I am trying to export data from a query in SQL Server 2005 SSIS to a flat file destination. Everything works fine except the rows returned from my query are written to the flat file in one long string (i.e., without line breaks). I have tried appending a new line character to the rows returned from the query but that only throws an error when the package is executed. My rows returned from the query are 133 characters wide (essentially only one column per row) so I have set the properties accordingly for a fixed width file format with 133 character wide rows.

Any suggestions or ideas on how to correct this would be greatly appreciated.

Thank you,

Michael

What viewer are you using when looking at the text file? Are you sure you don't have newlines in the files? Look in notepad and again in word and see if that helps.|||

The Flat File Connection manager has a Format property, and I guess you have selected "Fixed Width", but strictly speaking this format does not include row delimiters. What you actually need is Ragged Right.

The best way to do this is to open your Destination, and click the New connection button. Now read the options carefull, as most people probably select #2, as it says Fixed Width, but #3 is what you want, Fixed Width with Row Delimiters. This builds the appropriate connection using Ragged Right, giving you what you want.

Problem writing data after upgrading

Hello,

I was using for years an application that had an MSDE database.

Recently. I've upgraded my computer to Windows Vista, so I had to upgrade MSDE to SQL Express.

I've managed to attach the old MSDE database of the application to SQL Express, and I am able to retrieve my data from within the application.

My problem is that I can not write or update data from within the application anymore. However, If I open a table in SQL Management Studio Express I can write/ update data with no problem.

The company that build the application I was using doesnt exist any more so I cant take any support from them.

The only thing I know for a fact is that the application is connecting to the database using the sa user.

Any ideas?

Thanks a lot in advance!!!

Tassos

hi,

that would need some deeper investigation. What are you experiencing during trying to update the database ? Do you get an error ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Just an error within the application saying "Update failed"|||That is a wrapped error of the application not a SQL Server error, you will either have to use the SQL Server profiler (which is NOT included in SQL Server Express) or change the application to expose the error messages which come directly from the server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem writing data after upgrading

Hello,

I was using for years an application that had an MSDE database.

Recently. I've upgraded my computer to Windows Vista, so I had to upgrade MSDE to SQL Express.

I've managed to attach the old MSDE database of the application to SQL Express, and I am able to retrieve my data from within the application.

My problem is that I can not write or update data from within the application anymore. However, If I open a table in SQL Management Studio Express I can write/ update data with no problem.

The company that build the application I was using doesnt exist any more so I cant take any support from them.

The only thing I know for a fact is that the application is connecting to the database using the sa user.

Any ideas?

Thanks a lot in advance!!!

Tassos

hi,

that would need some deeper investigation. What are you experiencing during trying to update the database ? Do you get an error ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Just an error within the application saying "Update failed"|||That is a wrapped error of the application not a SQL Server error, you will either have to use the SQL Server profiler (which is NOT included in SQL Server Express) or change the application to expose the error messages which come directly from the server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem writing a report on a generated Report Model

I get this error message:

Semantic query execution failed.
The 'PerspectiveID' custom property for the 'query' perspective is either not set or is not set to the string data type.

The report model does have a perspective.

Anyone else seen this?

I believe this occurs when you create a report based on a report model generated from Analysis Services, and you do not specify a perspective. This should never happen in Report Builder (it should always force you to choose a perspective). Can you provide more details?|||

Hi Bob, yes thanks for your interest, I was trying to use BI Dev Studio report designer to write the report on a report model generated from AS.

The AS database has perspectives, so the RM picks those up OK. Report Builder works fine, but I cant get BI Dev Studio to connect to the perspective - hence the error I posted before.

Thanks again

|||Sorry for the delay in responding...in BI Development Studio there is currently no UI for specifying a perspective on a report model query. The perspective is specified using a CustomProperty in the report model query XML. If you create a basic query using the same perspective in Report Builder, then save that report as a file and bring it up in BI Dev Studio, you can use the generic (text) query designer to see the custom property in the query XML that specifies the perspective. You can use this basic query as a starting point in your report, or you can copy the custom property into your existing query to avoid the error you are getting.|||Is still the only solution to creating a report Desinder report attached to a model?|||

Are there any plans to address this issue in either a service pack for BI Studio 2005 or in Orcas or Katmai? It's pretty lame to have to go in and edit the semantic query xml by hand - the whole point of using the semantic model is to abstract away the arcane SQL (or MDX) so it seems like an oversight to have missed this little nuance for OLAP sources.

By the way the only reason I want this fixed so bad is that the ability to use BI Studio to design reports over a semantic model is extremely awesome and a great selling point for my end users that would be too limited by Report Builder - so it just kills me that this otherwise awesome story is a little tarnished with this complexity.

|||

Hi, I met the same problem when I use report builder to design report from a report model. This report model was generated from a SSAS cube in my report server web site.

Initially, this report builder worked well. After I enabled "Allow user drill this report from other reports" in the report properties window, the error occured. I could not save or view the report.

The problem is the report model is in the report server. How may I get the XML file? And we need let end user to use Report Builder to design and publish reports by themselves. It is difficult to explain all of above operation to end user and let them edit XML file to fix the issue.

Obviously,it is really a critical issue. Please fix it asap.

Problem writing a report on a generated Report Model

I get this error message:

Semantic query execution failed.
The 'PerspectiveID' custom property for the 'query' perspective is either not set or is not set to the string data type.

The report model does have a perspective.

Anyone else seen this?

I believe this occurs when you create a report based on a report model generated from Analysis Services, and you do not specify a perspective. This should never happen in Report Builder (it should always force you to choose a perspective). Can you provide more details?|||

Hi Bob, yes thanks for your interest, I was trying to use BI Dev Studio report designer to write the report on a report model generated from AS.

The AS database has perspectives, so the RM picks those up OK. Report Builder works fine, but I cant get BI Dev Studio to connect to the perspective - hence the error I posted before.

Thanks again

|||Sorry for the delay in responding...in BI Development Studio there is currently no UI for specifying a perspective on a report model query. The perspective is specified using a CustomProperty in the report model query XML. If you create a basic query using the same perspective in Report Builder, then save that report as a file and bring it up in BI Dev Studio, you can use the generic (text) query designer to see the custom property in the query XML that specifies the perspective. You can use this basic query as a starting point in your report, or you can copy the custom property into your existing query to avoid the error you are getting.|||Is still the only solution to creating a report Desinder report attached to a model?|||

Are there any plans to address this issue in either a service pack for BI Studio 2005 or in Orcas or Katmai? It's pretty lame to have to go in and edit the semantic query xml by hand - the whole point of using the semantic model is to abstract away the arcane SQL (or MDX) so it seems like an oversight to have missed this little nuance for OLAP sources.

By the way the only reason I want this fixed so bad is that the ability to use BI Studio to design reports over a semantic model is extremely awesome and a great selling point for my end users that would be too limited by Report Builder - so it just kills me that this otherwise awesome story is a little tarnished with this complexity.

|||

Hi, I met the same problem when I use report builder to design report from a report model. This report model was generated from a SSAS cube in my report server web site.

Initially, this report builder worked well. After I enabled "Allow user drill this report from other reports" in the report properties window, the error occured. I could not save or view the report.

The problem is the report model is in the report server. How may I get the XML file? And we need let end user to use Report Builder to design and publish reports by themselves. It is difficult to explain all of above operation to end user and let them edit XML file to fix the issue.

Obviously,it is really a critical issue. Please fix it asap.

Problem writing a report on a generated Report Model

I get this error message:

Semantic query execution failed.
The 'PerspectiveID' custom property for the 'query' perspective is either not set or is not set to the string data type.

The report model does have a perspective.

Anyone else seen this?

I believe this occurs when you create a report based on a report model generated from Analysis Services, and you do not specify a perspective. This should never happen in Report Builder (it should always force you to choose a perspective). Can you provide more details?|||

Hi Bob, yes thanks for your interest, I was trying to use BI Dev Studio report designer to write the report on a report model generated from AS.

The AS database has perspectives, so the RM picks those up OK. Report Builder works fine, but I cant get BI Dev Studio to connect to the perspective - hence the error I posted before.

Thanks again

|||Sorry for the delay in responding...in BI Development Studio there is currently no UI for specifying a perspective on a report model query. The perspective is specified using a CustomProperty in the report model query XML. If you create a basic query using the same perspective in Report Builder, then save that report as a file and bring it up in BI Dev Studio, you can use the generic (text) query designer to see the custom property in the query XML that specifies the perspective. You can use this basic query as a starting point in your report, or you can copy the custom property into your existing query to avoid the error you are getting.|||Is still the only solution to creating a report Desinder report attached to a model?|||

Are there any plans to address this issue in either a service pack for BI Studio 2005 or in Orcas or Katmai? It's pretty lame to have to go in and edit the semantic query xml by hand - the whole point of using the semantic model is to abstract away the arcane SQL (or MDX) so it seems like an oversight to have missed this little nuance for OLAP sources.

By the way the only reason I want this fixed so bad is that the ability to use BI Studio to design reports over a semantic model is extremely awesome and a great selling point for my end users that would be too limited by Report Builder - so it just kills me that this otherwise awesome story is a little tarnished with this complexity.

|||

Hi, I met the same problem when I use report builder to design report from a report model. This report model was generated from a SSAS cube in my report server web site.

Initially, this report builder worked well. After I enabled "Allow user drill this report from other reports" in the report properties window, the error occured. I could not save or view the report.

The problem is the report model is in the report server. How may I get the XML file? And we need let end user to use Report Builder to design and publish reports by themselves. It is difficult to explain all of above operation to end user and let them edit XML file to fix the issue.

Obviously,it is really a critical issue. Please fix it asap.

Problem writing a report on a generated Report Model

I get this error message:

Semantic query execution failed.
The 'PerspectiveID' custom property for the 'query' perspective is either not set or is not set to the string data type.

The report model does have a perspective.

Anyone else seen this?

I believe this occurs when you create a report based on a report model generated from Analysis Services, and you do not specify a perspective. This should never happen in Report Builder (it should always force you to choose a perspective). Can you provide more details?|||

Hi Bob, yes thanks for your interest, I was trying to use BI Dev Studio report designer to write the report on a report model generated from AS.

The AS database has perspectives, so the RM picks those up OK. Report Builder works fine, but I cant get BI Dev Studio to connect to the perspective - hence the error I posted before.

Thanks again

|||Sorry for the delay in responding...in BI Development Studio there is currently no UI for specifying a perspective on a report model query. The perspective is specified using a CustomProperty in the report model query XML. If you create a basic query using the same perspective in Report Builder, then save that report as a file and bring it up in BI Dev Studio, you can use the generic (text) query designer to see the custom property in the query XML that specifies the perspective. You can use this basic query as a starting point in your report, or you can copy the custom property into your existing query to avoid the error you are getting.|||Is still the only solution to creating a report Desinder report attached to a model?|||

Are there any plans to address this issue in either a service pack for BI Studio 2005 or in Orcas or Katmai? It's pretty lame to have to go in and edit the semantic query xml by hand - the whole point of using the semantic model is to abstract away the arcane SQL (or MDX) so it seems like an oversight to have missed this little nuance for OLAP sources.

By the way the only reason I want this fixed so bad is that the ability to use BI Studio to design reports over a semantic model is extremely awesome and a great selling point for my end users that would be too limited by Report Builder - so it just kills me that this otherwise awesome story is a little tarnished with this complexity.

|||

Hi, I met the same problem when I use report builder to design report from a report model. This report model was generated from a SSAS cube in my report server web site.

Initially, this report builder worked well. After I enabled "Allow user drill this report from other reports" in the report properties window, the error occured. I could not save or view the report.

The problem is the report model is in the report server. How may I get the XML file? And we need let end user to use Report Builder to design and publish reports by themselves. It is difficult to explain all of above operation to end user and let them edit XML file to fix the issue.

Obviously,it is really a critical issue. Please fix it asap.