Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
report execution
- Thread starter GShen
- Start date
- Thread starter
- #3
Not sure what you mean by DEFINE JOB. I want to execute an existing Access report which resides in a ADP from a job (as a step) and then email it to a recipient. The job currently executes 1 sql stored procedure. I want to add a step after it which will execute the report and subsequently, email it. I think I can the email piece to work. 1 step at a time. thanks.
Remember when... everything worked and there was a reason for it?
Remember when... everything worked and there was a reason for it?
1. in your stored procedure call xp_cmdshell to launch Access file.
2. in Access create an autoexec macro to automatically print a your report (hard part will be getting it to print to PDF without user interaction)
3. have your stored procedure check for a file and email it using sql mail.
Simi
2. in Access create an autoexec macro to automatically print a your report (hard part will be getting it to print to PDF without user interaction)
3. have your stored procedure check for a file and email it using sql mail.
Simi
- Thread starter
- #6
Where does the data reside for the report that you want mailed?
Just because the report you want exists in Acess doesn't mean that's where this has to come from.
Lodlaiden
If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]
Just because the report you want exists in Acess doesn't mean that's where this has to come from.
Lodlaiden
If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]
- Thread starter
- #8
What version of SQL are you using?
You may be able to look at setting up SSRS (Report Services) I belive this has the abilty to mail reports baked in. (Hearsay)
Lodlaiden
If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]
You may be able to look at setting up SSRS (Report Services) I belive this has the abilty to mail reports baked in. (Hearsay)
Lodlaiden
If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]
- Thread starter
- #10
You data is stored in a SQL database and you have an Access front end that generates a report. Why does there HAVE to be an easy way for a SQL Agent job to execute the report AND email it when this report does not live in SQL?
While related, the two technologies are not identical nor are they fully compatable in this case (at least to my knowledge).
If you want this report to live in Access, you will need to use Access to control the "running and distribution". This is best left to Access, which has the back end coding to handle this. This has been suggested as the database with an autoexec macro to do the work.
There are a couple of SQL options though. Both have advantages and disadvantages.
You can create the report in SQL Server Reporting Services. The downside here is if you don't SSRS already setup. You would need to set it up on your server. If you haven't used it before, you will find that your experience in creating reports in Access will shorten your learning curve. Once the report is created in SSRS, you can then schedule it to run and email itself to whomever you need. This is all built into the SSRS engine. The positives for using SSRS are many in my opion: no more need to have Access JUST for reports, ALL reports can be deployed to the same place and controlled with some simple basic security, caching/pre-rending large reports for performance gains, etc.
Another option that can be use is to have the SQL Agent job run the stored procedure and email the results using dbmail. The downside here is that you get the results as if they were run in SQL Management Studio. Your formatting options are very limited.
Ultimately you will need to decide solution fits your needs, resources, and budget.
=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)
Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer
While related, the two technologies are not identical nor are they fully compatable in this case (at least to my knowledge).
If you want this report to live in Access, you will need to use Access to control the "running and distribution". This is best left to Access, which has the back end coding to handle this. This has been suggested as the database with an autoexec macro to do the work.
There are a couple of SQL options though. Both have advantages and disadvantages.
You can create the report in SQL Server Reporting Services. The downside here is if you don't SSRS already setup. You would need to set it up on your server. If you haven't used it before, you will find that your experience in creating reports in Access will shorten your learning curve. Once the report is created in SSRS, you can then schedule it to run and email itself to whomever you need. This is all built into the SSRS engine. The positives for using SSRS are many in my opion: no more need to have Access JUST for reports, ALL reports can be deployed to the same place and controlled with some simple basic security, caching/pre-rending large reports for performance gains, etc.
Another option that can be use is to have the SQL Agent job run the stored procedure and email the results using dbmail. The downside here is that you get the results as if they were run in SQL Management Studio. Your formatting options are very limited.
Ultimately you will need to decide solution fits your needs, resources, and budget.
=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)
Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
C#.NET Programmer
- Thread starter
- #12
Thanks Robert,
It is just a few raw totals. Yes, it is formatted all nice and pretty in ACCESS, I could get a way with just running the sp and emailing the results. For that matter, I can just send the recipient and email when the job is done to go to XX and run the report. I will look at ssrs. I appreciate your feedback and insight. All reporting is done thru ACCESS as well as the systems.
Remember when... everything worked and there was a reason for it?
It is just a few raw totals. Yes, it is formatted all nice and pretty in ACCESS, I could get a way with just running the sp and emailing the results. For that matter, I can just send the recipient and email when the job is done to go to XX and run the report. I will look at ssrs. I appreciate your feedback and insight. All reporting is done thru ACCESS as well as the systems.
Remember when... everything worked and there was a reason for it?
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question