Exporting data while people are using the site?

I'm exporting a large file from a large Production database (SQL).

Users are currently updating and inserting new records.

Does the export take a snapshot of the data when it starts ? or will
it freeze the database so all udpates/inserts happen after the export
completes?

Also, how do I get the delta of data that wasn't exported, if I want
to get the difference the next day?

Thank yoU!!!"Angel Cat" <kittycatangel@.hotmail.com> wrote in message
news:9b08c59d.0312091239.14a243f7@.posting.google.c om...
> I'm exporting a large file from a large Production database (SQL).
> Users are currently updating and inserting new records.

Is it 24hr updating or is there a break during the night?

> Does the export take a snapshot of the data when it starts ? or will
> it freeze the database so all udpates/inserts happen after the export
> completes?

Snapshot.

> Also, how do I get the delta of data that wasn't exported, if I want
> to get the difference the next day?

Comparitive reporting on some key field to provide stats.
OTOH these import tasks can be automated and scheduled.
If the database shows no activity at 3:00am daily, do it then.|||THe database is in use 24 hours a day. I need to take a dump of the
data and and make some updates but what happens when a user is updting
while I take a dump. Does DTS freeze the data when I'm exporting?

ALso, is there a way I can take the difference easily?

Thank yoU!

"mountain man" <hobbit@.southern_seaweed.com.op> wrote in message news:<38sBb.46396$aT.11510@.news-server.bigpond.net.au>...
> "Angel Cat" <kittycatangel@.hotmail.com> wrote in message
> news:9b08c59d.0312091239.14a243f7@.posting.google.c om...
> > I'm exporting a large file from a large Production database (SQL).
> > Users are currently updating and inserting new records.
> Is it 24hr updating or is there a break during the night?
> > Does the export take a snapshot of the data when it starts ? or will
> > it freeze the database so all udpates/inserts happen after the export
> > completes?
> Snapshot.
> > Also, how do I get the delta of data that wasn't exported, if I want
> > to get the difference the next day?
> Comparitive reporting on some key field to provide stats.
> OTOH these import tasks can be automated and scheduled.
> If the database shows no activity at 3:00am daily, do it then.|||"Angel Cat" <kittycatangel@.hotmail.com> wrote in message
news:9b08c59d.0312100813.124f1c34@.posting.google.c om...
> THe database is in use 24 hours a day. I need to take a dump of the
> data and and make some updates but what happens when a user is updting
> while I take a dump. Does DTS freeze the data when I'm exporting?

Effectively it does a snapshot.

> ALso, is there a way I can take the difference easily?

The quantitative difference can be approximated in the first
instance by publishing the rowcount of the table "as at snapshot"
as distinct from the rowcount of the realtime SQL table which
can be easily obtained dynamically between loads. This will
represent (net) rows to be added/deleted.

Operationally this will imply part of the export process
will be to write the "last record included" type of thing
to some local table.

The quantitative differences are more difficult issues to deal
with but obviously you need to decide what elements of the
data you wish to keep track of during inter-load build-ups.
These you can add to the rowcount info if/when req'd.

Another idle thought here includes a master-publication
register accessible to the distribution group (who are getting
your export file) which is updated at every publication with
relevant information, and coupled with a realtime query into
SQL to compare nowtime with last publication.

> Thank yoU!
> "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
news:<38sBb.46396$aT.11510@.news-server.bigpond.net.au>...
> > "Angel Cat" <kittycatangel@.hotmail.com> wrote in message
> > news:9b08c59d.0312091239.14a243f7@.posting.google.c om...
> > > I'm exporting a large file from a large Production database (SQL).
> > > Users are currently updating and inserting new records.
> > Is it 24hr updating or is there a break during the night?
> > > Does the export take a snapshot of the data when it starts ? or will
> > > it freeze the database so all udpates/inserts happen after the export
> > > completes?
> > Snapshot.
> > > Also, how do I get the delta of data that wasn't exported, if I want
> > > to get the difference the next day?
> > Comparitive reporting on some key field to provide stats.
> > OTOH these import tasks can be automated and scheduled.
> > If the database shows no activity at 3:00am daily, do it then.|||Angel Cat,

It was my understanding that SQL 7 and 2000 have a snapshot of the data "as
of" the end time of the databases.
If you start your backup at 6pm and users are inserting and updating, and
your database backup finishes at 6:30pm, your backup will be as of 6:30pm.

MSSQL 6.5 had a snapshot of the data at 6pm. Assuming the example above.

Oscar...

"Angel Cat" <kittycatangel@.hotmail.com> wrote in message
news:9b08c59d.0312091239.14a243f7@.posting.google.c om...
> I'm exporting a large file from a large Production database (SQL).
> Users are currently updating and inserting new records.
> Does the export take a snapshot of the data when it starts ? or will
> it freeze the database so all udpates/inserts happen after the export
> completes?
> Also, how do I get the delta of data that wasn't exported, if I want
> to get the difference the next day?
> Thank yoU!!!

Exporting data from SQL Server to Excel from a Stored Procedure

I need to export data, from within a MSSql stored procedure to excel. Right now we use DTS, but its cumbersome and the users always screw it up.

I would usually just send the tabel to a .csv fiel and pick it up in excel, but I have a field that has preceding zeros and excel truncates them and uses a general fromat.

Any ideas

ThanksI'd use the Data | Get External Data | New Database Query menuitem in Excel. When you get to MS-Query, don't select any tables. Use the SQL button and type in the EXECUTE command for the stored procedure. Save this query and then the users can simply execute it!

-PatP

Exporting binary data

We're using MS-SQL Sever for a documentation database. One of the tables
stores all the DOC, XLS and PPT documents fed by our users.
We need to export all the files to certain directory and the rest of the
table contents to a spreadsheet. We want to distribute the documents in a
promotional CD.
The data export to a CSV works without a problem but we don't know how to
export the binary data of the files that are stored in the table. How can
this be done?
Thanks in advance,
JT
Jaime Torres wrote:
> We're using MS-SQL Sever for a documentation database. One of the
> tables stores all the DOC, XLS and PPT documents fed by our users.
> We need to export all the files to certain directory and the rest of
> the table contents to a spreadsheet. We want to distribute the
> documents in a promotional CD.
> The data export to a CSV works without a problem but we don't know
> how to export the binary data of the files that are stored in the
> table. How can this be done?
> Thanks in advance,
> JT
Please try not to multi-post. See ".Clients" for the current active
thread.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Exporting binary data

We're using MS-SQL Sever for a documentation database. One of the tables
stores all the DOC, XLS and PPT documents fed by our users.
We need to export all the files to certain directory and the rest of the
table contents to a spreadsheet. We want to distribute the documents in a
promotional CD.
The data export to a CSV works without a problem but we don't know how to
export the binary data of the files that are stored in the table. How can
this be done?
Thanks in advance,
JTJaime Torres wrote:
> We're using MS-SQL Sever for a documentation database. One of the
> tables stores all the DOC, XLS and PPT documents fed by our users.
> We need to export all the files to certain directory and the rest of
> the table contents to a spreadsheet. We want to distribute the
> documents in a promotional CD.
> The data export to a CSV works without a problem but we don't know
> how to export the binary data of the files that are stored in the
> table. How can this be done?
> Thanks in advance,
> JT
Please try not to multi-post. See ".Clients" for the current active
thread.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Exporting binary data

We're using MS-SQL Sever for a documentation database. One of the tables
stores all the DOC, XLS and PPT documents fed by our users.
We need to export all the files to certain directory and the rest of the
table contents to a spreadsheet. We want to distribute the documents in a
promotional CD.
The data export to a CSV works without a problem but we don't know how to
export the binary data of the files that are stored in the table. How can
this be done?
Thanks in advance,
JTJaime Torres wrote:
> We're using MS-SQL Sever for a documentation database. One of the
> tables stores all the DOC, XLS and PPT documents fed by our users.
> We need to export all the files to certain directory and the rest of
> the table contents to a spreadsheet. We want to distribute the
> documents in a promotional CD.
> The data export to a CSV works without a problem but we don't know
> how to export the binary data of the files that are stored in the
> table. How can this be done?
> Thanks in advance,
> JT
Please try not to multi-post. See ".Clients" for the current active
thread.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Export users to other database

Dear All,
I have created users that able to access database A, now i want those
users able to access database B in same server.
How to export users from one database to other databases in the same server?
Thanks
Robert LieRobert
What is about logins? Do you have to move them as well?
Lookup at microsoft web site for two stored procedures that move logins with
their original SID.
"How to Move Logins in SQL Server"
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uM6CTLWdFHA.892@.tk2msftngp13.phx.gbl...
> Dear All,
> I have created users that able to access database A, now i want those
> users able to access database B in same server.
> How to export users from one database to other databases in the same
server?
> Thanks
> Robert Lie

Export users to other database

Dear All,
I have created users that able to access database A, now i want those
users able to access database B in same server.
How to export users from one database to other databases in the same server?
Thanks
Robert Lie
Robert
What is about logins? Do you have to move them as well?
Lookup at microsoft web site for two stored procedures that move logins with
their original SID.
"How to Move Logins in SQL Server"
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uM6CTLWdFHA.892@.tk2msftngp13.phx.gbl...
> Dear All,
> I have created users that able to access database A, now i want those
> users able to access database B in same server.
> How to export users from one database to other databases in the same
server?
> Thanks
> Robert Lie

Export users to other database

Dear All,
I have created users that able to access database A, now i want those
users able to access database B in same server.
How to export users from one database to other databases in the same server?
Thanks
Robert LieRobert
What is about logins? Do you have to move them as well?
Lookup at microsoft web site for two stored procedures that move logins with
their original SID.
"How to Move Logins in SQL Server"
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:uM6CTLWdFHA.892@.tk2msftngp13.phx.gbl...
> Dear All,
> I have created users that able to access database A, now i want those
> users able to access database B in same server.
> How to export users from one database to other databases in the same
server?
> Thanks
> Robert Lie

Export to PDF error

Some users get the following error when exporting to PDF:
There was an error opening this document. The file does not exist.
They can save the document locally though and open it. We're all using the
same version of Acrobat Reader (6.0.1).
Has anyone experienced this before?
Thanks,
Jason MacKenzieJason, I have aboslutely experienced this same issue and it is a major
problem. Can someone from Microsoft please comment on this issue. We have
various version of .pdf in the office. 5.0 works fine it seems for Reporting
Services rendering to .pdf but we have a requirement for employees to see
their ADP paystubs in 6.0. Adober .pdf's load fine in the browser when
pulled up directly from other sources which leads me to believe this is a
driver issue on the reporting services side?
"Jason MacKenzie" wrote:
> Some users get the following error when exporting to PDF:
> There was an error opening this document. The file does not exist.
> They can save the document locally though and open it. We're all using the
> same version of Acrobat Reader (6.0.1).
> Has anyone experienced this before?
> Thanks,
> Jason MacKenzie
>
>|||Here's an additional caveat. This seems to be happening primarily on systems
with Adobe Acrobt 6.0 Professional installed?
"Scott" wrote:
> Jason, I have aboslutely experienced this same issue and it is a major
> problem. Can someone from Microsoft please comment on this issue. We have
> various version of .pdf in the office. 5.0 works fine it seems for Reporting
> Services rendering to .pdf but we have a requirement for employees to see
> their ADP paystubs in 6.0. Adober .pdf's load fine in the browser when
> pulled up directly from other sources which leads me to believe this is a
> driver issue on the reporting services side?
> "Jason MacKenzie" wrote:
> > Some users get the following error when exporting to PDF:
> >
> > There was an error opening this document. The file does not exist.
> >
> > They can save the document locally though and open it. We're all using the
> > same version of Acrobat Reader (6.0.1).
> >
> > Has anyone experienced this before?
> >
> > Thanks,
> >
> > Jason MacKenzie
> >
> >
> >

Export to html and print

Hi all,
i've created a print button for users to obviously print their reports. When
it's clicked, the webpage automatically renders the report into a html view.
The i've written some javascript 'window.print() actually ;)' so it
automatically begins printing.
But when i look to the report after it is printed, i see that when i have eg
15 columns, only the first three are printed. (so only the first page). Do i
have to add something specifiek so that it prints the entire page ? Because
everything is well rendered, it just doesn't print good.
Thanks in advance !
Kind regards,
Koen Vermeire
koen.vermeire@.gmail.comI think what you are doing is the same thing as if the user printed from IE
using the print functionality of IE. You only get the page that is currently
visible.
My suggestion if you can wait is to do so. By the middle of this month SP2
will be released and it includes client side printing.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Koen" <Koen@.discussions.microsoft.com> wrote in message
news:A2248AB1-0F1E-4C08-9DE4-13BB7D7BF489@.microsoft.com...
> Hi all,
> i've created a print button for users to obviously print their reports.
When
> it's clicked, the webpage automatically renders the report into a html
view.
> The i've written some javascript 'window.print() actually ;)' so it
> automatically begins printing.
> But when i look to the report after it is printed, i see that when i have
eg
> 15 columns, only the first three are printed. (so only the first page). Do
i
> have to add something specifiek so that it prints the entire page ?
Because
> everything is well rendered, it just doesn't print good.
> Thanks in advance !
>
> Kind regards,
> Koen Vermeire
> koen.vermeire@.gmail.com|||Well, the thing is, i am making this stuff because i'm currently finishing my
internship. So i wanted to deliver a complete solution to my company. But as
you said, SP 2 will be out soon, so i'll guess it'll be beter to wait for
that.
Thanks for the very fast response ;)
Kind regards,
Koen Vermeire
"Bruce L-C [MVP]" wrote:
> I think what you are doing is the same thing as if the user printed from IE
> using the print functionality of IE. You only get the page that is currently
> visible.
> My suggestion if you can wait is to do so. By the middle of this month SP2
> will be released and it includes client side printing.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Koen" <Koen@.discussions.microsoft.com> wrote in message
> news:A2248AB1-0F1E-4C08-9DE4-13BB7D7BF489@.microsoft.com...
> > Hi all,
> >
> > i've created a print button for users to obviously print their reports.
> When
> > it's clicked, the webpage automatically renders the report into a html
> view.
> > The i've written some javascript 'window.print() actually ;)' so it
> > automatically begins printing.
> >
> > But when i look to the report after it is printed, i see that when i have
> eg
> > 15 columns, only the first three are printed. (so only the first page). Do
> i
> > have to add something specifiek so that it prints the entire page ?
> Because
> > everything is well rendered, it just doesn't print good.
> >
> > Thanks in advance !
> >
> >
> > Kind regards,
> > Koen Vermeire
> > koen.vermeire@.gmail.com
>
>

Export to Excel--customizing the save option

Hi,
Am creating reports using Sql Reporting services. Suppose am creating a
report for all the users and the file name is "User Report" and am passing
the "username" by parametervalue. while exporting it to excel, a dialogue box
appears which gives an option of 'open','save','cancel' with "user
Report.xls". but what if i have to save it specific to the paramvalue i.e-- "
<username> UserReport.xls"'I dont think you can do that just from the report Manager.
You will have to write your own application to display the
reports and the provide a export to excel option and then
render the report in excel format(using the Web Services
Render method) and save the excel file from code with the
desired filename.
>--Original Message--
>Hi,
>Am creating reports using Sql Reporting services. Suppose
am creating a
>report for all the users and the file name is "User
Report" and am passing
>the "username" by parametervalue. while exporting it to
excel, a dialogue box
>appears which gives an option of 'open','save','cancel'
with "user
>Report.xls". but what if i have to save it specific to
the paramvalue i.e-- "
><username> UserReport.xls"'
>
>.
>

Export to Excel: Format cells to "Number"

Hi all
When I export my reports to excel, all cells are set to "General" number
style.
Users wanted to show numbers as "Number", but I didn't found the way to set
the cell output style.
Any idea?
Thanks in advance.On Aug 20, 12:57 pm, "David Rodr=EDguez Rinc=F3n" <d_rin...@.hotmail.com>
wrote:
> Hi all
> When I export my reports to excel, all cells are set to "General" number
> style.
> Users wanted to show numbers as "Number", but I didn't found the way to s=et
> the cell output style.
> Any idea?
> Thanks in advance.
Depending on your needs, you may be able to set the field's format
based on what the numbering format needs to be. Maybe something like
one of the following as an expression in the Format property (select
the control -> select the View drop-down list -> Properties Window ->
to the right of Format, select <Expression...>)
#,0
-or-
#,0.00
etc
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi Enrique.
First of all, thank you very much for your quick answer
My issue is not related woth the format in the Report Viewer. I can show
data using the correct format.
The problem is in the excel file exported by MS RS, when all the cells are
set as "General" (right click on cell >> Format Cell >> Number Category)
Our users want to set numbers to "Number" category, to be able to apply
formulas to validate data.
I tried your approach, and it works in the Report Viewer, but unfortunatelly
the excel file is exported with no specific cell format :(
Any other suggestion?
Thanks again.
David Rodríguez
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1187661848.228911.233150@.50g2000hsm.googlegroups.com...
On Aug 20, 12:57 pm, "David Rodríguez Rincón" <d_rin...@.hotmail.com>
wrote:
> Hi all
> When I export my reports to excel, all cells are set to "General" number
> style.
> Users wanted to show numbers as "Number", but I didn't found the way to
> set
> the cell output style.
> Any idea?
> Thanks in advance.
Depending on your needs, you may be able to set the field's format
based on what the numbering format needs to be. Maybe something like
one of the following as an expression in the Format property (select
the control -> select the View drop-down list -> Properties Window ->
to the right of Format, select <Expression...>)
#,0
-or-
#,0.00
etc
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi again!
Enrique, your solution works.
My problem was that if you format the value in the .Value expresion, using
funtions like FormatNumber or FormatPercentage, the format entered in the
.Format expresion is not taked into account.
If I only show the Value in the textbox, and use the .Format property using
the MS Excel custom styles[ ="#,##0;(#,##0)"] it works!!!!!
Thanks you very much!!
Regards.
David Rodríguez Rincón
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1187661848.228911.233150@.50g2000hsm.googlegroups.com...
On Aug 20, 12:57 pm, "David Rodríguez Rincón" <d_rin...@.hotmail.com>
wrote:
> Hi all
> When I export my reports to excel, all cells are set to "General" number
> style.
> Users wanted to show numbers as "Number", but I didn't found the way to
> set
> the cell output style.
> Any idea?
> Thanks in advance.
Depending on your needs, you may be able to set the field's format
based on what the numbering format needs to be. Maybe something like
one of the following as an expression in the Format property (select
the control -> select the View drop-down list -> Properties Window ->
to the right of Format, select <Expression...>)
#,0
-or-
#,0.00
etc
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||On Aug 22, 1:11 pm, "David Rodr=EDguez Rinc=F3n" <d_rin...@.hotmail.com>
wrote:
> Hi again!
> Enrique, your solution works.
> My problem was that if you format the value in the .Value expresion, using
> funtions like FormatNumber or FormatPercentage, the format entered in the
> .Format expresion is not taked into account.
> If I only show the Value in the textbox, and use the .Format property usi=ng
> the MS Excel custom styles[ =3D"#,##0;(#,##0)"] it works!!!!!
> Thanks you very much!!
> Regards.
> David Rodr=EDguez Rinc=F3n
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1187661848.228911.233150@.50g2000hsm.googlegroups.com...
> On Aug 20, 12:57 pm, "David Rodr=EDguez Rinc=F3n" <d_rin...@.hotmail.com>
> wrote:
> > Hi all
> > When I export my reports to excel, all cells are set to "General" number
> > style.
> > Users wanted to show numbers as "Number", but I didn't found the way to
> > set
> > the cell output style.
> > Any idea?
> > Thanks in advance.
> Depending on your needs, you may be able to set the field's format
> based on what the numbering format needs to be. Maybe something like
> one of the following as an expression in the Format property (select
> the control -> select the View drop-down list -> Properties Window ->
> to the right of Format, select <Expression...>)
> #,0
> -or-
> #,0.00
> etc
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
You're welcome. Glad I could be of assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi again!
Enrique, your solution works.
My problem was that if you format the value in the .Value expresion, using
funtions like FormatNumber or FormatPercentage, the format entered in the
.Format expresion is not taked into account.
If I only show the Value in the textbox, and use the .Format property using
the MS Excel custom styles[ ="#,##0;(#,##0)"] it works!!!!!
Thanks you very much!!
Regards.
David Rodríguez Rincón
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1187661848.228911.233150@.50g2000hsm.googlegroups.com...
On Aug 20, 12:57 pm, "David Rodríguez Rincón" <d_rin...@.hotmail.com>
wrote:
> Hi all
> When I export my reports to excel, all cells are set to "General" number
> style.
> Users wanted to show numbers as "Number", but I didn't found the way to
> set
> the cell output style.
> Any idea?
> Thanks in advance.
Depending on your needs, you may be able to set the field's format
based on what the numbering format needs to be. Maybe something like
one of the following as an expression in the Format property (select
the control -> select the View drop-down list -> Properties Window ->
to the right of Format, select <Expression...>)
#,0
-or-
#,0.00
etc
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Export to Excel causes merged cells?

I have a report that is a table of data... the users export this report to
excel and then pivot the data in the table in order to cut and slice the data
to do analysis. When they go to insert a pivot table off of this report
Excel throws an error saying that the data source is not valid. What I've
noticed is that when reporting services exports the report to excel it
created one very narrow column and merges that column with the the next and
merges the cells in each of the rows below it' Also, even if I remove the
merged or hidden columns I still can't seem to pivot the data unless I do a
copy paste special values into a new workbook... it won't work if I just
paste into a new worksheet in the same workbook'
Any help you could provide would be greatly appreciated!On Mar 15, 12:54 pm, mr.letni <mrle...@.discussions.microsoft.com>
wrote:
> I have a report that is a table of data... the users export this report to
> excel and then pivot the data in the table in order to cut and slice the data
> to do analysis. When they go to insert a pivot table off of this report
> Excel throws an error saying that the data source is not valid. What I've
> noticed is that when reporting services exports the report to excel it
> created one very narrow column and merges that column with the the next and
> merges the cells in each of the rows below it' Also, even if I remove the
> merged or hidden columns I still can't seem to pivot the data unless I do a
> copy paste special values into a new workbook... it won't work if I just
> paste into a new worksheet in the same workbook'
> Any help you could provide would be greatly appreciated!
If you are using multiple tables (one-above-the-other) in a single
column report, I would suggest making sure that the column widths
match exactly between the multiple tables (that are one-above-the-
other). Also, make sure that your report title/independent textboxes
and table widths are the exact same width (i.e., don't have a report
title textbox smaller in width than the table(s) width). Other similar
kinds of nuances should apply as well. It depends on which axis you
see the merged cells on as far as which items need to be expanded to
match widths, etc. Also, make sure that there is no open space below
all items in the report to the bottom report border and no open space
to the right of the report items to the right border of the report.
I've had some very picky clients in regards to merged cell exports to
Excel and this was how I alleviated the merged cells for them. Hope
this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||The main issue I have seen is when I use a textbox at the top of my report
to put my title in. Instead, add additional header rows, then merge the
header row cells that you want to put your title in. When you use a text box
you end up with merged fields that then prevents sorting (or other
operations) on the data in Excel.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"mr.letni" <mrletni@.discussions.microsoft.com> wrote in message
news:4DD288E7-2C95-4A53-BB29-A86283B1C962@.microsoft.com...
>I have a report that is a table of data... the users export this report to
> excel and then pivot the data in the table in order to cut and slice the
> data
> to do analysis. When they go to insert a pivot table off of this report
> Excel throws an error saying that the data source is not valid. What I've
> noticed is that when reporting services exports the report to excel it
> created one very narrow column and merges that column with the the next
> and
> merges the cells in each of the rows below it' Also, even if I remove
> the
> merged or hidden columns I still can't seem to pivot the data unless I do
> a
> copy paste special values into a new workbook... it won't work if I just
> paste into a new worksheet in the same workbook'
> Any help you could provide would be greatly appreciated!

Export to Excel

We are converting Crystal V10 reports to SSRS and have ran into an
issue when exporting a large dataset 104,000 rows. In Crystal when the
users export this same data it spreads it over multiple worksheets does
any have a solution in SSRS that gives me this same ability.Yes it is possible in SSRS, when you have pagination and export to excel,
then it will in multiple sheets
Amarnath
"smiksuek" wrote:
> We are converting Crystal V10 reports to SSRS and have ran into an
> issue when exporting a large dataset 104,000 rows. In Crystal when the
> users export this same data it spreads it over multiple worksheets does
> any have a solution in SSRS that gives me this same ability.
>

Export Slow from SQL Reporting Service

We have users experiencing very slow to export SQL Reporting Service 2005
rendered data to EXCEL. Is there any way to improve the performance?
BrianOn Mar 9, 8:37 pm, Brian <B...@.discussions.microsoft.com> wrote:
> We have users experiencing very slow to export SQL Reporting Service 2005
> rendered data to EXCEL. Is there any way to improve the performance?
> Brian
Usually this is due to either high activity on the web server that the
reports are on -or- that the query or stored procedure sourcing the
report needs to be revised to improve performance. I would suggest
using the Database Engine Tuning Adviser against the stored procedure
or query to implement indexes, etc to improve the performance. Hope
this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Hello,
What about exporting to other formats like PDF or MHTML. Is it very slow
too?
How complicated are these reports and how much data do they contain?
Sometimes the reason of slow exporting to Excel is complex structure of
reports (a lot of cells, nested objects, etc.).
If reports contain a lot of hidden rows all of them are saving to Excel file
(even if they are unseen during exporting).
Maybe this is the reason.
Regards,
Radoslaw Lebkowski
U¿ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:632830EF-9C4F-4A1C-B8C9-672E327E95C4@.microsoft.com...
> We have users experiencing very slow to export SQL Reporting Service 2005
> rendered data to EXCEL. Is there any way to improve the performance?
> Brian|||Thank you for the quick response.
The SQL Reporting 2005 report is a simple report which has no complex
structure in it. It is a single line type of report. However, it has the
company logo image on the title of the report. The report rendered within
one minute with 123 pages. When I export to EXCEL, it was ten times slower
then the web page rendered.
Does export require SQL to query the data again or it gathered the rendered
data to EXCEL?
Would increase SQL memory from dynamic to static with 3GB help? How about
caching the report?
Thanks,
Brian
"Radoslaw Lebkowski" wrote:
> Hello,
> What about exporting to other formats like PDF or MHTML. Is it very slow
> too?
> How complicated are these reports and how much data do they contain?
> Sometimes the reason of slow exporting to Excel is complex structure of
> reports (a lot of cells, nested objects, etc.).
> If reports contain a lot of hidden rows all of them are saving to Excel file
> (even if they are unseen during exporting).
> Maybe this is the reason.
>
> Regards,
> Radoslaw Lebkowski
>
> U¿ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> news:632830EF-9C4F-4A1C-B8C9-672E327E95C4@.microsoft.com...
> > We have users experiencing very slow to export SQL Reporting Service 2005
> > rendered data to EXCEL. Is there any way to improve the performance?
> >
> > Brian
>
>|||By changing the export from EXCEL to PDF, It does not make any difference.
When I ran the SQL Web Reporting, it rendered very fast. Only when I need to
export the data to EXCEL and it is much slower.
Brian
"EMartinez" wrote:
> On Mar 9, 8:37 pm, Brian <B...@.discussions.microsoft.com> wrote:
> > We have users experiencing very slow to export SQL Reporting Service 2005
> > rendered data to EXCEL. Is there any way to improve the performance?
> >
> > Brian
> Usually this is due to either high activity on the web server that the
> reports are on -or- that the query or stored procedure sourcing the
> report needs to be revised to improve performance. I would suggest
> using the Database Engine Tuning Adviser against the stored procedure
> or query to implement indexes, etc to improve the performance. Hope
> this helps.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||On Mar 10, 11:25 am, Brian <B...@.discussions.microsoft.com> wrote:
> By changing the export from EXCEL to PDF, It does not make any difference.
> When I ran the SQL Web Reporting, it rendered very fast. Only when I need to
> export the data to EXCEL and it is much slower.
> Brian
> "EMartinez" wrote:
> > On Mar 9, 8:37 pm, Brian <B...@.discussions.microsoft.com> wrote:
> > > We have users experiencing very slow to export SQL Reporting Service 2005
> > > rendered data to EXCEL. Is there any way to improve the performance?
> > > Brian
> > Usually this is due to either high activity on the web server that the
> > reports are on -or- that the query or stored procedure sourcing the
> > report needs to be revised to improve performance. I would suggest
> > using the Database Engine Tuning Adviser against the stored procedure
> > or query to implement indexes, etc to improve the performance. Hope
> > this helps.
> > Regards,
> > Enrique Martinez
> > Sr. SQL Server Developer
If the image is large or there is a lot of report data to export, this
may be the reason. Have you tried the Database Engine Tuning Advisor
yet? I think that caching the report might improve the performance and
response time.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||If you render previous executed reports to other output format, it doesn't
require regathering data from datasources.
Rendering process uses intermediate format of these reports stored in RS
cache.
Rendering to output format uses SQL Server only for Report Processing
Extensions (not for executing SQL queries from datasources).
Rendering reports to PDF or Excel are the slowest operations whereas
exporting to HTML and CSV are the fastests methods.
I've heard a lot of complaints for slow rendering to PDF and Excel. It's
very common situation.
To improve speed of rendering reports try to find server's bottlenecks.
Maybe it's a CPU or server memory.
Try to use SQL Server Profiler to measure CPU usage during rendering to
different formats.
I hope it will be helpful.
Radoslaw Lebkowski
U¿ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa³ w wiadomo¶ci
news:5C8EF1BB-10C8-4468-B221-C75D18D9434C@.microsoft.com...
> Thank you for the quick response.
> The SQL Reporting 2005 report is a simple report which has no complex
> structure in it. It is a single line type of report. However, it has the
> company logo image on the title of the report. The report rendered within
> one minute with 123 pages. When I export to EXCEL, it was ten times
> slower
> then the web page rendered.
> Does export require SQL to query the data again or it gathered the
> rendered
> data to EXCEL?
> Would increase SQL memory from dynamic to static with 3GB help? How about
> caching the report?
> Thanks,
> Brian
>
> "Radoslaw Lebkowski" wrote:
>> Hello,
>> What about exporting to other formats like PDF or MHTML. Is it very slow
>> too?
>> How complicated are these reports and how much data do they contain?
>> Sometimes the reason of slow exporting to Excel is complex structure of
>> reports (a lot of cells, nested objects, etc.).
>> If reports contain a lot of hidden rows all of them are saving to Excel
>> file
>> (even if they are unseen during exporting).
>> Maybe this is the reason.
>>
>> Regards,
>> Radoslaw Lebkowski
>>
>> U?ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa3 w wiadomo?ci
>> news:632830EF-9C4F-4A1C-B8C9-672E327E95C4@.microsoft.com...
>> > We have users experiencing very slow to export SQL Reporting Service
>> > 2005
>> > rendered data to EXCEL. Is there any way to improve the performance?
>> >
>> > Brian
>>|||If it is much data at all then what you should do is have your users export
via CSV. It will still open it up in Excel. One other point, however. The
default CSV format is Unicode which Excel doesn't know how to handle (at
least Excel 2003 and earlier can't handle, I don't know about 2007). In RS
2005 you can change a configuration setting that causing CSV exports to be
in ASCII. CSV and HTML rendering is about the same performance wise.
As a test just try it. Excel will (I believe) put all the data in a single
column and then you have to use the menu in Excel to split the data out into
multiple columns.
I am gone next week to the MVP Summit in Seattle. All the newsgroups will
see very little MVP involvement for the next week.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Brian" <Brian@.discussions.microsoft.com> wrote in message
news:5C8EF1BB-10C8-4468-B221-C75D18D9434C@.microsoft.com...
> Thank you for the quick response.
> The SQL Reporting 2005 report is a simple report which has no complex
> structure in it. It is a single line type of report. However, it has the
> company logo image on the title of the report. The report rendered within
> one minute with 123 pages. When I export to EXCEL, it was ten times
> slower
> then the web page rendered.
> Does export require SQL to query the data again or it gathered the
> rendered
> data to EXCEL?
> Would increase SQL memory from dynamic to static with 3GB help? How about
> caching the report?
> Thanks,
> Brian
>
> "Radoslaw Lebkowski" wrote:
>> Hello,
>> What about exporting to other formats like PDF or MHTML. Is it very slow
>> too?
>> How complicated are these reports and how much data do they contain?
>> Sometimes the reason of slow exporting to Excel is complex structure of
>> reports (a lot of cells, nested objects, etc.).
>> If reports contain a lot of hidden rows all of them are saving to Excel
>> file
>> (even if they are unseen during exporting).
>> Maybe this is the reason.
>>
>> Regards,
>> Radoslaw Lebkowski
>>
>> U¿ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa³ w wiadomo¶ci
>> news:632830EF-9C4F-4A1C-B8C9-672E327E95C4@.microsoft.com...
>> > We have users experiencing very slow to export SQL Reporting Service
>> > 2005
>> > rendered data to EXCEL. Is there any way to improve the performance?
>> >
>> > Brian
>>|||Exporting to CSV helps and improve the export time. Let's hope Microsoft
will improve the export process to EXCEL in the future.
Thanks,
Brian
"Bruce L-C [MVP]" wrote:
> If it is much data at all then what you should do is have your users export
> via CSV. It will still open it up in Excel. One other point, however. The
> default CSV format is Unicode which Excel doesn't know how to handle (at
> least Excel 2003 and earlier can't handle, I don't know about 2007). In RS
> 2005 you can change a configuration setting that causing CSV exports to be
> in ASCII. CSV and HTML rendering is about the same performance wise.
> As a test just try it. Excel will (I believe) put all the data in a single
> column and then you have to use the menu in Excel to split the data out into
> multiple columns.
> I am gone next week to the MVP Summit in Seattle. All the newsgroups will
> see very little MVP involvement for the next week.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Brian" <Brian@.discussions.microsoft.com> wrote in message
> news:5C8EF1BB-10C8-4468-B221-C75D18D9434C@.microsoft.com...
> > Thank you for the quick response.
> >
> > The SQL Reporting 2005 report is a simple report which has no complex
> > structure in it. It is a single line type of report. However, it has the
> > company logo image on the title of the report. The report rendered within
> > one minute with 123 pages. When I export to EXCEL, it was ten times
> > slower
> > then the web page rendered.
> >
> > Does export require SQL to query the data again or it gathered the
> > rendered
> > data to EXCEL?
> >
> > Would increase SQL memory from dynamic to static with 3GB help? How about
> > caching the report?
> >
> > Thanks,
> > Brian
> >
> >
> >
> > "Radoslaw Lebkowski" wrote:
> >
> >> Hello,
> >> What about exporting to other formats like PDF or MHTML. Is it very slow
> >> too?
> >> How complicated are these reports and how much data do they contain?
> >> Sometimes the reason of slow exporting to Excel is complex structure of
> >> reports (a lot of cells, nested objects, etc.).
> >> If reports contain a lot of hidden rows all of them are saving to Excel
> >> file
> >> (even if they are unseen during exporting).
> >> Maybe this is the reason.
> >>
> >>
> >> Regards,
> >> Radoslaw Lebkowski
> >>
> >>
> >>
> >> U¿ytkownik "Brian" <Brian@.discussions.microsoft.com> napisa³ w wiadomo¶ci
> >> news:632830EF-9C4F-4A1C-B8C9-672E327E95C4@.microsoft.com...
> >> > We have users experiencing very slow to export SQL Reporting Service
> >> > 2005
> >> > rendered data to EXCEL. Is there any way to improve the performance?
> >> >
> >> > Brian
> >>
> >>
> >>
>
>

export roles and users into CSV

First of all, my SQL coding skills are not very good. Therefore I am
looking for a script which will export all SQL2k DB Roles (Application
& Database) including the Users (native SQL & AD Groups (we are using
mixed mode)) into a *.CSV.
thanks for your helpThat would be a nice Script to have.
In the meantime, try going to each database and selecting Generate Scripts.
Then only choose to scipt logins ?
that should be it,
Now to do it for one or more databases on one or more Servers.
Might have to try that on sunday
"Marty" <martin-za.frick@.ubs.com> wrote in message
news:fe528b81-5d2a-4732-93f3-ad47f8316644@.c33g2000hsd.googlegroups.com...
> First of all, my SQL coding skills are not very good. Therefore I am
> looking for a script which will export all SQL2k DB Roles (Application
> & Database) including the Users (native SQL & AD Groups (we are using
> mixed mode)) into a *.CSV.
> thanks for your help|||Hi Randy
Have a look at this link:
http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31889/
Just need to build a DTS around the script and than adding it on each
DB server
On 13 Mrz., 16:55, "Randy Pitkin" <RandyPit...@.ydpages.com> wrote:
> That would be a nice Script to have.
> In the meantime, try going to each database and selecting Generate Scripts.
> Then only choose to scipt logins ?
> that should be it,
> Now to do it for one or more databases on one or more Servers.
> Might have to try that on sunday
> "Marty" <martin-za.fr...@.ubs.com> wrote in message
> news:fe528b81-5d2a-4732-93f3-ad47f8316644@.c33g2000hsd.googlegroups.com...
> > First of all, my SQL coding skills are not very good. Therefore I am
> > looking for a script which will export all SQL2k DB Roles (Application
> > & Database) including the Users (native SQL & AD Groups (we are using
> > mixed mode)) into a *.CSV.
> > thanks for your help

export roles and users into CSV

First of all, my SQL coding skills are not very good. Therefore I am
looking for a script which will export all SQL2k DB Roles (Application
& Database) including the Users (native SQL & AD Groups (we are using
mixed mode)) into a *.CSV.
thanks for your help
That would be a nice Script to have.
In the meantime, try going to each database and selecting Generate Scripts.
Then only choose to scipt logins ?
that should be it,
Now to do it for one or more databases on one or more Servers.
Might have to try that on sunday
"Marty" <martin-za.frick@.ubs.com> wrote in message
news:fe528b81-5d2a-4732-93f3-ad47f8316644@.c33g2000hsd.googlegroups.com...
> First of all, my SQL coding skills are not very good. Therefore I am
> looking for a script which will export all SQL2k DB Roles (Application
> & Database) including the Users (native SQL & AD Groups (we are using
> mixed mode)) into a *.CSV.
> thanks for your help
|||Hi Randy
Have a look at this link:
http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31889/
Just need to build a DTS around the script and than adding it on each
DB server
On 13 Mrz., 16:55, "Randy Pitkin" <RandyPit...@.ydpages.com> wrote:[vbcol=seagreen]
> That would be a nice Script to have.
> In the meantime, try going to each database and selecting Generate Scripts.
> Then only choose to scipt logins ?
> that should be it,
> Now to do it for one or more databases on one or more Servers.
> Might have to try that on sunday
> "Marty" <martin-za.fr...@.ubs.com> wrote in message
> news:fe528b81-5d2a-4732-93f3-ad47f8316644@.c33g2000hsd.googlegroups.com...
>