Create an XML file from a stored procedure

I'm trying to create a temp xml file to to convert it eventually into an excel file.

using C#

I am calling a stored procedure and from that data retrieved I want to write it in xml format.

I tried using the xmlTextWriter.. but I get an exception, it refers to a token..(?)

I am new to using xml, so any help would be great..

Can I write an xml file by using a streamWriter or writing all bytes?

Thanks,

kt

You haven't mentioned if you are using SQL Server 2000 or 2005.
If its 2000, then check the FOR XML {AUTO, RAW, EXPLICIT} syntax in books online.
If its 2005 you might want to use the FOR XML PATH.

These will return for you the results in the XML format you want and you might not really need to format it manually using C#.

Hope that helps!
|||

I'm using SQL Server 2005...

You wouldn't possibly know any good resources on formatting.. for using "FOR XML PATH"..?

sql

Create an XML file from a stored procedure

I'm trying to create a temp xml file to to convert it eventually into an excel file.

using C#

I am calling a stored procedure and from that data retrieved I want to write it in xml format.

I tried using the xmlTextWriter.. but I get an exception, it refers to a token..(?)

I am new to using xml, so any help would be great..

Can I write an xml file by using a streamWriter or writing all bytes?

Thanks,

kt

You haven't mentioned if you are using SQL Server 2000 or 2005.
If its 2000, then check the FOR XML {AUTO, RAW, EXPLICIT} syntax in books online.
If its 2005 you might want to use the FOR XML PATH.

These will return for you the results in the XML format you want and you might not really need to format it manually using C#.

Hope that helps!
|||

I'm using SQL Server 2005...

You wouldn't possibly know any good resources on formatting.. for using "FOR XML PATH"..?

Create an Excel Sheet with parameters

On rendering Excel, i want to make a new sheet which contains the RS
parameters of the rendering.
So, the end user get an XLS data and the input parameters.
Is there any way to make this "parameters" sheet ?
Thanks
AntoineTry putting your parameters on a rectangle, in the body of the report, not
the header. Then go to the properties on the rectangle, indicate to have a
page break before or after the object. when the report display in your
browser, the first page will just be any headers and your parameters but,
when you export to excel, a separate tab will have been created for your
parameters. For the online view, you probably will want to indicate that the
rectangle should repeat based on your detailed data area. Hope this helps!
--
ChrisinPA
"tonio932" wrote:
> On rendering Excel, i want to make a new sheet which contains the RS
> parameters of the rendering.
> So, the end user get an XLS data and the input parameters.
> Is there any way to make this "parameters" sheet ?
> Thanks
> Antoine
>sql

Create a Package for export the query results to Excel

Hi,

In SQL Server,

does anybody provide the steps for create a Package for exporting the query results to Excel environment?

I just know that click the "Export", then choose the query file to export to Excel.

I want to know how to create the Package to export it.

Please let me know, thanks.1. Create new package

2. Connection from Connection menu (or right click)
* Add SQL server connection, specify the server name, authentication, and dbname
* Add Excel connection, specify the path where u want to place the excel file

3. Transform data task from Task menu (or right click)
* Add Transform data task, select the source and destination
* Double click the task
- Source tab : select the table/view or write down the query
- Destination tab : just click ok on create table script window (for sheet & column name)
- Transformation tab : Map the required columns

4. Save the DTS

5. Right click - Execute package or u can schedule it as job|||Question:

I created the package and execute the package, it works,
but, I got another problem, if I execute it for 2 times, the values will be output double in the same table,

eg.
1st execute:
Name:
Jim
Mary

2nd execute:
Name:
Jim
Mary
Jim
Mary

I want to execute the package which values will replace the previous file's values if the filename is same.

How should I do? Please let me know, thanks.|||em....i dunno how to delete data in excel destination file. but u can do it if the destination is ms access file, with additional task and workflow :
- Add a task (Execute SQL Task) as 1st step in the DTS
Existing connection : choose the ms access connection which was already created
SQL statement : DELETE * FROM <destination_tablename>

- Add on success workflow from above task to sql connection

crashing excel when printing exported RS report

We are having a problem with some reports that get exported to excel and then
trying to print. we don't get this with all reports just some.
Here is what happens:
The report generates fine in RS. We then export to excel. We tell excel to
print and then get a 'Memory can't be read' error and excel crashes.
Whenever we want to just print a report as is, we export to PDF and print
that way. But many times the report is used in excel for further analisys
with other data. I have tried printing without touching the report in excel
and still get the error. So I know it is not something we added.
Because we are only getting this error with some reports and not all, I am
guessing that there must be something that was(n't) added to the report in RS
that would cause this.
Did I make enough sense to explain my issue?We are having the same problem with a couple of reports that were recently
modified. The modifications included adding a couple of additonal parameters
and then displaying the parameter values in the report footer. We already had
the report parameters displaying in the footer with no problems.
Our users are running Office 97 on WIndows NT worksatations. Our
deveoplment environment is running Windows XP with Office 2003 and the report
printing problem cannot be reproduced in the development environment.
Anyone had any luck with fixing this problem?
Ray
"David" wrote:
> We are having a problem with some reports that get exported to excel and then
> trying to print. we don't get this with all reports just some.
> Here is what happens:
> The report generates fine in RS. We then export to excel. We tell excel to
> print and then get a 'Memory can't be read' error and excel crashes.
> Whenever we want to just print a report as is, we export to PDF and print
> that way. But many times the report is used in excel for further analisys
> with other data. I have tried printing without touching the report in excel
> and still get the error. So I know it is not something we added.
> Because we are only getting this error with some reports and not all, I am
> guessing that there must be something that was(n't) added to the report in RS
> that would cause this.
> Did I make enough sense to explain my issue?
>
>|||We have finally solved this issue.
Our poblem was solved by reducing the amount of information in the report
footer. We display the users input parameter values in the footer to allow
them to print the parameters used when rendering the report. In our case we
had increased the number of parameters in the report and hence displayed in
the footer from 6 to 8. This caused Excel to crash when attempting to print
the export file. We have since reduced the parameters in the footer to 6
again and now the report prints in Excel.
Hope this may help you in finding the soucre of you printing problems.
"David" wrote:
> We are having a problem with some reports that get exported to excel and then
> trying to print. we don't get this with all reports just some.
> Here is what happens:
> The report generates fine in RS. We then export to excel. We tell excel to
> print and then get a 'Memory can't be read' error and excel crashes.
> Whenever we want to just print a report as is, we export to PDF and print
> that way. But many times the report is used in excel for further analisys
> with other data. I have tried printing without touching the report in excel
> and still get the error. So I know it is not something we added.
> Because we are only getting this error with some reports and not all, I am
> guessing that there must be something that was(n't) added to the report in RS
> that would cause this.
> Did I make enough sense to explain my issue?
>
>

CPU Usage on AS 2005 Server

I have 5 cubes with 15 dimensions and one cube (like Virtual cube before) with 5 linked cubes.

My client is Excel 2003 Pivot table.

From Excel/Pivot table if I run a query which is taking long time. I press "Esc" key to cancel my query. However if I look into my Server "CPU Usage" even after canceling my query is very high. Looks it doesn't cancel my query on server. I am the only user using this server.

If I restart my Analysis Services 2005 the CPU Usage drops in 90 degrees to almost 0%.

Where is the problem any idea?

Thank you - Ashok

Looks like you have a classic case of "runaway query". It is always hard to get to the bottom of who sent the query and why the query is running that long.

Oten the one of the better recommendations for improving query performance and therefore improving responcivness of your cube is to desing aggregations. Aggregations are the key to Analysis Services performance.
Try and run Aggregation Design wizard and create aggregations for your cube. On top of that you can capture queries sent in the query log and then use Aggregation Manager sample application to desing aggregations supporting exact set of queries.

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

|||

In other words how to cancel/kill these "runaway query" without restarting server?

I do have aggregation setup. In SQL Server database we can kill any running query... can we do same in Analysis Services?

|||I had the same problem, if you run a query and you close the tool executing it then the query will still keep on running and slowing down the system. I don't think you can kill it. Maybe you can try to set the timeout much lower so that it will kill any query running longer than 30 seconds. I'm not sure if this will work but it should sure prevent the query of running longer than 30 seconds protecting the server from a resource eating *** query from hell.

|||

you can use the MSAS Activity Viewer tool and you can kill sessions.

|||

What is MSAS Activity Viewer tool ?

CPU Usage on AS 2005 Server

I have 5 cubes with 15 dimensions and one cube (like Virtual cube before) with 5 linked cubes.

My client is Excel 2003 Pivot table.

From Excel/Pivot table if I run a query which is taking long time. I press "Esc" key to cancel my query. However if I look into my Server "CPU Usage" even after canceling my query is very high. Looks it doesn't cancel my query on server. I am the only user using this server.

If I restart my Analysis Services 2005 the CPU Usage drops in 90 degrees to almost 0%.

Where is the problem any idea?

Thank you - Ashok

Looks like you have a classic case of "runaway query". It is always hard to get to the bottom of who sent the query and why the query is running that long.

Oten the one of the better recommendations for improving query performance and therefore improving responcivness of your cube is to desing aggregations. Aggregations are the key to Analysis Services performance.
Try and run Aggregation Design wizard and create aggregations for your cube. On top of that you can capture queries sent in the query log and then use Aggregation Manager sample application to desing aggregations supporting exact set of queries.

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

|||

In other words how to cancel/kill these "runaway query" without restarting server?

I do have aggregation setup. In SQL Server database we can kill any running query... can we do same in Analysis Services?

|||I had the same problem, if you run a query and you close the tool executing it then the query will still keep on running and slowing down the system. I don't think you can kill it. Maybe you can try to set the timeout much lower so that it will kill any query running longer than 30 seconds. I'm not sure if this will work but it should sure prevent the query of running longer than 30 seconds protecting the server from a resource eating *** query from hell.

|||

you can use the MSAS Activity Viewer tool and you can kill sessions.

|||

What is MSAS Activity Viewer tool ?

Countries & Cities Tables

I need to have tables of world countries and cities to use in a user registration form, I searched the Internet for scripts,xml,excel files for countries & cities lists but I couldn't find. Can anybody tell me where to get them from?

Hello my friend,

Let me introduce you to a website I used to worked on that has plenty of this information: -

http://www.wshtravel.com/travel_guides

Kind regards

Scotty

|||

Thanks but the link is not working

|||

I think that if you will try to do something like this on your page it will be very slow and very big, so maybe just use countries. You can try to get countries fromwww.usps.com just try to send pckage to international destination and you will get drop down list with countries to which USPS delivers packages. You can also trywww.UPS.com maybe they have also cites?

Thanks

JPazgier