Automating report execution - saving to separate files

I'm trying to do something which I hope can be accomplished relatively simply.

I have a report similar to bank statements let's say. When run, it currently prints out each person's statement into one file, with page breaks sepearating each person's statement. What I need to do, is when the report is run, save each person's report into a seperate file for the purpose of emailing to them later.

I could easily modify my report to just output for one particular person, but I'm not sure if there's a way to "bulk render" all the reports and have them saved to sepearate files.

I should also add that I'm using an MS Access Data Project (ADP) as the front end to my app - connected to a SQL Server 2005 DB. I currently display the reports by embedding a web browser object into an Access form and rendering the report via HTML.

Thanks in advance,

H

I actually figured out an answer to my own question which I'll post here in case others need to do the same thing. If anyone else has an alternative suggestion please feel free to let me know.

There's a VBA function called "URLDownloadToFile". If I use the reporting services URL access and specify type XLS or PDF, I can pass this as the URL and save the file to a specific folder. Kind of nice actually - and very simple.

Automating Access to SQL Server

We're trying to put a view of data maintained in desktop Access databases online and into SQL Server.
The desktop Access system uses separate databases instead of tables within one database, It's a strange design, but it can't be changed.
We have been importing all of the separate databases into a single, new Access database, then upsizing the new databse to SQL Server, then uploading it.
This is not going to work long term, because we are stuck with a 250 mB Access database to upsize and upload, when we never need to update more than 2 or 3 of the tables and upload more than 2 mB.
We'd like to be able to upload only the tables -- preferably the Access *.mdb's -- that have changed, and then replace the SQL Server tables with the new information. And we'd like to automate it as much as possible, without upsizing Wizardy.
I don't know where to even begin looking for information about how this might be done.
Any suggestions would be deeply appreciated.
- Tinker
This sounds possibly like a job for DTS. You could set up a DTSpackage on the SQL Server that will loop through the Access databasesand perform the updates as needed.
This site is a good resources for DTS work:http://www.sqldts.com.
|||Hi Terri, and thank you.
When I first ran across information about DTS, I skipped right past it. I'm curently out in the MS Tech Net learning more and it appears DTS may be much closer to what we want to do than I first thought.
Neither the FAQs athttp://www.sqldts.com/ nor the information I have trolled-through so far on the Tech Net discuss automating DTS; is that possible?
While we don't want to ride herd on these data updates any more than is absolutely necessary, it would be super great if I don't have to try to program my way through deleting all the records in an SQL table, then refilling the table with the new records...
Appreciate your suggestion,
- Tinker|||Sure, it's possible to automate DTS. You can use SQL Agent toschedule a DTS package to run on a schedule of your choosing.|||Thank you. I finally found where I get to do all this.
Sometimes I feel dumber than usual... 8-)
- Tinker

Automatic failover... help

Hi there,

We've recently set up a Principle, Mirror and Witness configuration with the Mirror and Witness in a separate building to the Principle. All three are part of the same domain (DMZ) and are different servers, the buildings are connected via a fiber optic cable. All servers and SQL Server instances are logged in with the same domain admin account DMZ\esAdmin.

Mirroring is all set-up and the databases are synchronized. Every once in a while some (not all, normally 6 out of 15) databases will switch roles and become active on the mirror. The SQL Server mirroring monitor job then reports:

Date 25/01/2007 12:37:01
Log Job History (Database Mirroring Monitor Job)

Step ID 1
Server DMZSQL01
Job Name Database Mirroring Monitor Job
Step Name
Duration 00:00:02
Sql Severity 16
Sql Message ID 32038
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: DMZ\esadmin. An internal error has occurred in the database mirroring monitor. [SQLSTATE 42000] (Error 32038). The step failed.

I have no idea, what causes the failover, it could be a slow network or a bad set-up, can anyone give me some ideas of what to do to track down the problem or any experience of what could be causing this, it happens randomly every day or three. No warning and if I go to the mirror and failover back to the principle again then it's all just fine. However I don't want half my databases working on 1 server and half on the other.

Any ideas?

Thanks
Ed

UPDATE:

I've just been looking at the logs on my Mirror and at the same time it reports in this order

Error: 1479, Severity: 16, State: 1.

The mirroring connection to "TCP://DMZSQL01.dmz.local:5022" has timed out for database "WARCMedia" after 10 seconds without a response. Check the service and network connections.

Database mirroring is inactive for database 'WARCMedia'. This is an informational message only. No user action is required.

Recovery is writing a checkpoint in database 'WARCMedia' (41). This is an informational message only. No user action is required.

The mirrored database "WARCMedia" is changing roles from "PRINCIPAL" to "MIRROR" due to Failover.

Database mirroring is inactive for database 'WARCMedia'. This is an informational message only. No user action is required.

...

This looks like a time out, is there any way to set the TimeOut threashold for Database mirroring or set retry intervals?

Have you implemented network monitoring? I suggest you compare the failover times to the network monitoring history.

-Matt

|||

No, but any pointes on what to monitor would be usefull.

Thanks

Ed

|||

Couple of things to consider:

1. run with the safety OFF (then you wouldn't need the witness server). This won't allow automatic failover from with a real failure or a false failure. The false failures are what you seem to be seeing now, but it will allow your system to run in a more predictable manner, while you understand what is going on.

2. Consider moving the Witness (if you MUST run with the witness) to the building with the Principal. All things being equal, (i.e. if the probability of both building having a disaster is equal); then having the Witness and Principal share a "more reliable" connection should provide better reliability. Assuming: 1. servers closer together physically is more reliable. 2. You can't put the Witness in a third, equally reliable data center (who has that kind of $$$) 3. Your business needs dictate that you can put the Witness with the Principal

3. Increase the timeout "ping" for mirroring. ALTER DATABASE db SET PARTNER TIMEOUT = xx. (see BOL) By default, it is 10 seconds.

|||

Thank you all for your help.

I modified the timout pint to 60 seconds and I've not had any problems since. I have a feeling either there is something on the network causing a delay or the server was just under too much load at one off times. This has solved 1 problem so I'll continue to look into the cause over the coming weeks.

Thanks
Ed

Automatic Data Ranges

Hello -

I'm trying to come up with a report that will separate a range of data into X slices. For example, the range might be from 0 through 100. I'd then want to slice it 10 ways, ending up with 10 groups. For each of those groups, I'd want a count of the number of records in my database that fall into each of those ranges. So, the number of records between 0 and 10 becomes the value of range 1. 11-20 becomes range 2, etc. After all that I'll be charting that data, but I think that'll be the start and then I can handle it from there.

Ideas?

Thanks,

Greg.You can do it one of two ways. Both ways require you to create a group - this can be done from the Insert/Group menu.

First way would be to use the "Specified Order" option in the order drop down list. this allows you to specify conditions that will group the data. This is fairly static in that you have to specify the conditions at design time, although there are ways to make this more dynamic using parameters - but that is a little more complex.

The second method is more flexible. You can create a group based on a Formula. Create a new Formula by right clicking on the Formula heading in the Field Explorer (assuming you are using V9 or above). You now have access to the complete formula language to define how you want to group your data - this can be very dynamic, as it can be conditioned by the data coming into the report itself. Once you have created your formula you can create a group on it.

Regards,
DS.