Is SSRS right for our needs?

I'd really appreciate some help deciding if SQL Server Reporting Services is
right for our needs. We have the following two requirements:
1) Users of our ASP .Net application pull reports from the new ASP .Net
SSRS embedded control.
(Users log in with Forms Authentication)
2) Automated nightly push of emailed reports to thousands of users.
Q1: Does the new ASP .Net embedded control for SSRS work with the
aspnet_users and aspnet_roles tables to identify the current user, display
the list of reports for them and manage security login to SQL Server?
Q2: Can the generation of SSRS report Excel-Output be automated to file
from a nightly SQL Server Job or perhaps from an application that is kicked
off at midnight?
I apologize if this seems a little vague. It's almost as if asking the
question is more difficult than answering it. Ultimately, the question is:
Will SSRS meet our needs for both Pull and Push reports?
Danny LesandriniOn Jul 13, 10:17 am, "dlesandrini" <dataf...@.comcast.net> wrote:
> I'd really appreciate some help deciding if SQL Server Reporting Services is
> right for our needs. We have the following two requirements:
> 1) Users of our ASP .Net application pull reports from the new ASP .Net
> SSRS embedded control.
> (Users log in with Forms Authentication)
> 2) Automated nightly push of emailed reports to thousands of users.
> Q1: Does the new ASP .Net embedded control for SSRS work with the
> aspnet_users and aspnet_roles tables to identify the current user, display
> the list of reports for them and manage security login to SQL Server?
> Q2: Can the generation of SSRS report Excel-Output be automated to file
> from a nightly SQL Server Job or perhaps from an application that is kicked
> off at midnight?
> I apologize if this seems a little vague. It's almost as if asking the
> question is more difficult than answering it. Ultimately, the question is:
> Will SSRS meet our needs for both Pull and Push reports?
> Danny Lesandrini
If I'm following you correctly, in Q1 you are referring to the report
viewer control (which works w/both Crystal Reports and Reporting
Services). The current user is available in SSRS via this expression:
=User!UserID.ToString
As far as I know, controlling access to SQL Server is done via the
datasource set up and controlled via the Report Manager. Otherwise,
controlling report access can only be set via general user security in
the Report Manager. Users can be set up to just browse/view reports,
etc (but no control over which reports, just all). Of course, the
stored procedure/query that sources the report can control whether or
not the user sees the results if the stored procedure accepts an input
parameter that receives the User expression given above.
In regards to Q2, the Report Manager has built in snapshot reporting
and scheduling, etc. The report can be scheduled to execute on a
schedule and exported to Excel.
The main difference between Crystal Reports and Reporting Services is
that the license for SSRS is free w/a SQL Server license on the same
box; however, Crystal Reports requires its own license. So, SSRS is
much cheaper on the budget. Also, Crystal Reports and SSRS have
slightly different export options. SSRS has very convenient report
export scheduling.
Hope this helps in your decision.
Regards,
Enrique Martinez
Sr. Software Consultant

Is ODBC right for me?

I am a forum newbie - appreciate your patience.
We are embarking on a new adventure to scale up an Access DB up to SQL
Server. We are not going to use the upsizing wizardry, but have chosen to
build native SQL tables instead.
The question is which route to go with the Front-End?
- Access 2003: SQL tables linked via ODBC
- Access Data Project(ADP)
- Access 2007: SQL tables linked (MDB or ACCDB?)
We were prepared to move ahead with creating an Access Data Project using
Access 2003, but then I read the following on microsoft.com ...
" Access creates front-end applications that leverage SQL Server as a
backend data source. Access forms and reports can be optimized as efficiently
as Visual Basic front-end for SQL Server. Office Access 2007 offers two ways
to connect to SQL Server data: linking to SQL Server and Access Data Projects
(ADPs).
The preferred way to connect to SQL Server is MDB file format or ACCDB file
format. This enables you to use the full flexibility of local tables and
local queries, while leveraging the full power of SQL Server. In addition,
MDB and ACCDB files link to multiple SQL Servers and a wide variety of other
data sources. Office Access 2007 contains many new features available in both
MDB and ACCDB file formats, but only a subset of those features are available
in ADPs. "
This seems to state that best practice is to use linked tables. Is this true
in the "real world"? Isn't using the layers of ODBC going to make for a
slower app? Is ADP a technology that may not be supported by MS in the near
future?
Where would I find info to learn how to optimize Access/ODBC to work as well
as a VB front-end as stated in the quote above?
Your thoughts would be appreciated!
Hi Dirn,
I'm not an Access guru but one thing I found when working with an ADP
against SQL 2005 is that the database must be in SQL 2000 compatible format
since an Access ADP isn't compatible with any schema but DBO.
You can use SQL Express if the amount of your data is within its size
limitations.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...

> ... The question is which route to go with the Front-End?
> - Access 2003: SQL tables linked via ODBC
> - Access Data Project(ADP)
> - Access 2007: SQL tables linked (MDB or ACCDB?) ...
|||Cindy & Van T.
Thanks for taking time to respond to my question. I still have three
lindering wunderabouts:
- Which approach (ADP vs. Linked Tables) to a Access FE / SQL Server BE app
does MS consider best practice with the current revs of their Access & SQL
products?
- If, linked tables - how to choose between MDB and ACCDB?
- What did they really mean in the quote above about making an Access
FE as effiecient as a VB FE?
If you have any additional info or know of a resource who might be of
assistance please respond further.
Your help is so incredibly appreciated.
Best Regards,
David Hogan
"Van T. Dinh" wrote:

> ADP is still supported in Access 2007 which means that it will be supported
> to at least end of 2016 (5 years mainstream support + 5 years extended
> support).
> I am sure there are lots of ADPs in the real-world but the majority of
> Access databases still use MDB format.
> In my experience, there is no problems using ODBC provided you follow a few
> simple techniques on reducing the amount of data being pulled through the
> network (this applies to all Client / Server database applications, anyway).
> There are a number of articles in the Microsoft KB, e.g:
> http://support.microsoft.com/kb/208858
> and
> http://support.microsoft.com/kb/209551
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "DIRN" <DIRN@.discussions.microsoft.com> wrote in message
> news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...
>
>
|||Van
Thanks again for the wisdom. Access "Vision" document was very helpful. I
will begin to start looking for material on how to code an application
optimzed for a SQL server BE. I think your thoughts on minimizing network
traffic are right on.
I appreciate your generous assistance!
David
"Van T. Dinh" wrote:

> * Definitely Linked Tables from what I have heard for some time now. When
> Access 2000 & Access 2002 came out, Microsoft was pushing ADP but it is
> definitely not the case with Access 2007. In fact, I think the new ACCDB
> format utilises the new JET engine (and JET engine was supposed to be in
> maintenance mode only!)
> See http://www.tinyurl.com/y2yjzn
> and have a look at the first paragraph of page 10 from the Microsoft
> document above and I am sure you can infer what Microsoft impplies.
> * I haven't done much testing with Access 2007 + new ACCDB format but AFAIK,
> you need A2007 to use ACCDB. If you have a uniform enviroment where
> everyone has A2007, ACCDB will probably be better (since the Microsoft
> Access / JET engineers have exclusive control of the engine which enable
> them to tune it to suit Access while the JET 4 engine is shared in the OS so
> they can't change it easily). If you have a mixed environment, I think it
> is safer to stick to the MDB / MDE format for the moment.
> * I am confused about "VB" nowadays since it could mean either the old VB6
> or VB.Net or VB2005 but don't you have to use some sort of interface such as
> OLEDB to access data regardles of the BE engine? In my databases, I used
> ODBC-linked Tables (for Forms , Reports, etc ...) but in code, I use mostly
> ADO / OLEDB for SQL Server to access and manipulate data and the speed is
> quite fast.
> I think the major bottleneck in most database application is network
> transmission bandwidth, not the database engine or the interface to the
> database (I once added a million simple records one at a time to a JET Table
> using DAO Recordset code and it took only about 3-4 seconds). I concentrate
> on reducing the need to transfer data across the network to a minimum, e.g.
> getting the SQL Server to do most processing and only pass back the
> processed result to Access FE.
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "DIRN" <DIRN@.discussions.microsoft.com> wrote in message
> news:04CD459E-1686-41C1-90AF-A773661CF283@.microsoft.com...
>
>

Is ODBC right for me?

I am a forum newbie - appreciate your patience.
We are embarking on a new adventure to scale up an Access DB up to SQL
Server. We are not going to use the upsizing wizardry, but have chosen to
build native SQL tables instead.
The question is which route to go with the Front-End?
- Access 2003: SQL tables linked via ODBC
- Access Data Project(ADP)
- Access 2007: SQL tables linked (MDB or ACCDB?)
We were prepared to move ahead with creating an Access Data Project using
Access 2003, but then I read the following on microsoft.com ...
" Access creates front-end applications that leverage SQL Server as a
backend data source. Access forms and reports can be optimized as efficientl
y
as Visual Basic front-end for SQL Server. Office Access 2007 offers two ways
to connect to SQL Server data: linking to SQL Server and Access Data Project
s
(ADPs).
The preferred way to connect to SQL Server is MDB file format or ACCDB file
format. This enables you to use the full flexibility of local tables and
local queries, while leveraging the full power of SQL Server. In addition,
MDB and ACCDB files link to multiple SQL Servers and a wide variety of other
data sources. Office Access 2007 contains many new features available in bot
h
MDB and ACCDB file formats, but only a subset of those features are availabl
e
in ADPs. "
This seems to state that best practice is to use linked tables. Is this true
in the "real world"? Isn't using the layers of ODBC going to make for a
slower app? Is ADP a technology that may not be supported by MS in the near
future?
Where would I find info to learn how to optimize Access/ODBC to work as well
as a VB front-end as stated in the quote above?
Your thoughts would be appreciated!Hi Dirn,
I'm not an Access guru but one thing I found when working with an ADP
against SQL 2005 is that the database must be in SQL 2000 compatible format
since an Access ADP isn't compatible with any schema but DBO.
You can use SQL Express if the amount of your data is within its size
limitations.
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@.cindywinegarden.com
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...

> ... The question is which route to go with the Front-End?
> - Access 2003: SQL tables linked via ODBC
> - Access Data Project(ADP)
> - Access 2007: SQL tables linked (MDB or ACCDB?) ...|||ADP is still supported in Access 2007 which means that it will be supported
to at least end of 2016 (5 years mainstream support + 5 years extended
support).
I am sure there are lots of ADPs in the real-world but the majority of
Access databases still use MDB format.
In my experience, there is no problems using ODBC provided you follow a few
simple techniques on reducing the amount of data being pulled through the
network (this applies to all Client / Server database applications, anyway).
There are a number of articles in the Microsoft KB, e.g:
http://support.microsoft.com/kb/208858
and
http://support.microsoft.com/kb/209551
HTH
Van T. Dinh
MVP (Access)
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...
>I am a forum newbie - appreciate your patience.
> We are embarking on a new adventure to scale up an Access DB up to SQL
> Server. We are not going to use the upsizing wizardry, but have chosen to
> build native SQL tables instead.
> The question is which route to go with the Front-End?
> - Access 2003: SQL tables linked via ODBC
> - Access Data Project(ADP)
> - Access 2007: SQL tables linked (MDB or ACCDB?)
> We were prepared to move ahead with creating an Access Data Project using
> Access 2003, but then I read the following on microsoft.com ...
> " Access creates front-end applications that leverage SQL Server as a
> backend data source. Access forms and reports can be optimized as
> efficiently
> as Visual Basic front-end for SQL Server. Office Access 2007 offers two
> ways
> to connect to SQL Server data: linking to SQL Server and Access Data
> Projects
> (ADPs).
> The preferred way to connect to SQL Server is MDB file format or ACCDB
> file
> format. This enables you to use the full flexibility of local tables and
> local queries, while leveraging the full power of SQL Server. In addition,
> MDB and ACCDB files link to multiple SQL Servers and a wide variety of
> other
> data sources. Office Access 2007 contains many new features available in
> both
> MDB and ACCDB file formats, but only a subset of those features are
> available
> in ADPs. "
> This seems to state that best practice is to use linked tables. Is this
> true
> in the "real world"? Isn't using the layers of ODBC going to make for a
> slower app? Is ADP a technology that may not be supported by MS in the
> near
> future?
> Where would I find info to learn how to optimize Access/ODBC to work as
> well
> as a VB front-end as stated in the quote above?
> Your thoughts would be appreciated!|||Cindy & Van T.
Thanks for taking time to respond to my question. I still have three
lindering wunderabouts:
- Which approach (ADP vs. Linked Tables) to a Access FE / SQL Server BE app
does MS consider best practice with the current revs of their Access & SQL
products?
- If, linked tables - how to choose between MDB and ACCDB?
- What did they really mean in the quote above about making an Access
FE as effiecient as a VB FE?
If you have any additional info or know of a resource who might be of
assistance please respond further.
Your help is so incredibly appreciated.
Best Regards,
David Hogan
"Van T. Dinh" wrote:

> ADP is still supported in Access 2007 which means that it will be supporte
d
> to at least end of 2016 (5 years mainstream support + 5 years extended
> support).
> I am sure there are lots of ADPs in the real-world but the majority of
> Access databases still use MDB format.
> In my experience, there is no problems using ODBC provided you follow a fe
w
> simple techniques on reducing the amount of data being pulled through the
> network (this applies to all Client / Server database applications, anyway
).
> There are a number of articles in the Microsoft KB, e.g:
> http://support.microsoft.com/kb/208858
> and
> http://support.microsoft.com/kb/209551
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "DIRN" <DIRN@.discussions.microsoft.com> wrote in message
> news:BB082997-C04F-4C65-BF6F-496C53A07E9D@.microsoft.com...
>
>|||* Definitely Linked Tables from what I have heard for some time now. When
Access 2000 & Access 2002 came out, Microsoft was pushing ADP but it is
definitely not the case with Access 2007. In fact, I think the new ACCDB
format utilises the new JET engine (and JET engine was supposed to be in
maintenance mode only!)
See http://www.tinyurl.com/y2yjzn
and have a look at the first paragraph of page 10 from the Microsoft
document above and I am sure you can infer what Microsoft impplies.
* I haven't done much testing with Access 2007 + new ACCDB format but AFAIK,
you need A2007 to use ACCDB. If you have a uniform enviroment where
everyone has A2007, ACCDB will probably be better (since the Microsoft
Access / JET engineers have exclusive control of the engine which enable
them to tune it to suit Access while the JET 4 engine is shared in the OS so
they can't change it easily). If you have a mixed environment, I think it
is safer to stick to the MDB / MDE format for the moment.
* I am confused about "VB" nowadays since it could mean either the old VB6
or VB.Net or VB2005 but don't you have to use some sort of interface such as
OLEDB to access data regardles of the BE engine? In my databases, I used
ODBC-linked Tables (for Forms , Reports, etc ...) but in code, I use mostly
ADO / OLEDB for SQL Server to access and manipulate data and the speed is
quite fast.
I think the major bottleneck in most database application is network
transmission bandwidth, not the database engine or the interface to the
database (I once added a million simple records one at a time to a JET Table
using DAO Recordset code and it took only about 3-4 seconds). I concentrate
on reducing the need to transfer data across the network to a minimum, e.g.
getting the SQL Server to do most processing and only pass back the
processed result to Access FE.
HTH
Van T. Dinh
MVP (Access)
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:04CD459E-1686-41C1-90AF-A773661CF283@.microsoft.com...
> Cindy & Van T.
> Thanks for taking time to respond to my question. I still have three
> lindering wunderabouts:
> - Which approach (ADP vs. Linked Tables) to a Access FE / SQL Server BE
> app
> does MS consider best practice with the current revs of their Access & SQL
> products?
> - If, linked tables - how to choose between MDB and ACCDB?
> - What did they really mean in the quote above about making an Access
> FE as effiecient as a VB FE?
> If you have any additional info or know of a resource who might be of
> assistance please respond further.
> Your help is so incredibly appreciated.
> Best Regards,
> David Hogan|||Van
Thanks again for the wisdom. Access "Vision" document was very helpful. I
will begin to start looking for material on how to code an application
optimzed for a SQL server BE. I think your thoughts on minimizing network
traffic are right on.
I appreciate your generous assistance!
David
"Van T. Dinh" wrote:

> * Definitely Linked Tables from what I have heard for some time now. When
> Access 2000 & Access 2002 came out, Microsoft was pushing ADP but it is
> definitely not the case with Access 2007. In fact, I think the new ACCDB
> format utilises the new JET engine (and JET engine was supposed to be in
> maintenance mode only!)
> See http://www.tinyurl.com/y2yjzn
> and have a look at the first paragraph of page 10 from the Microsoft
> document above and I am sure you can infer what Microsoft impplies.
> * I haven't done much testing with Access 2007 + new ACCDB format but AFAI
K,
> you need A2007 to use ACCDB. If you have a uniform enviroment where
> everyone has A2007, ACCDB will probably be better (since the Microsoft
> Access / JET engineers have exclusive control of the engine which enable
> them to tune it to suit Access while the JET 4 engine is shared in the OS
so
> they can't change it easily). If you have a mixed environment, I think it
> is safer to stick to the MDB / MDE format for the moment.
> * I am confused about "VB" nowadays since it could mean either the old VB6
> or VB.Net or VB2005 but don't you have to use some sort of interface such
as
> OLEDB to access data regardles of the BE engine? In my databases, I used
> ODBC-linked Tables (for Forms , Reports, etc ...) but in code, I use mostl
y
> ADO / OLEDB for SQL Server to access and manipulate data and the speed is
> quite fast.
> I think the major bottleneck in most database application is network
> transmission bandwidth, not the database engine or the interface to the
> database (I once added a million simple records one at a time to a JET Tab
le
> using DAO Recordset code and it took only about 3-4 seconds). I concentra
te
> on reducing the need to transfer data across the network to a minimum, e.g
.
> getting the SQL Server to do most processing and only pass back the
> processed result to Access FE.
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
> "DIRN" <DIRN@.discussions.microsoft.com> wrote in message
> news:04CD459E-1686-41C1-90AF-A773661CF283@.microsoft.com...
>
>|||You're welcome ... Glad to help ...
HTH
Van T. Dinh
MVP (Access)
"DIRN" <DIRN@.discussions.microsoft.com> wrote in message
news:7444F6AD-25F7-4DB5-B9E2-FD249F9C4C40@.microsoft.com...
> Van
> Thanks again for the wisdom. Access "Vision" document was very helpful. I
> will begin to start looking for material on how to code an application
> optimzed for a SQL server BE. I think your thoughts on minimizing network
> traffic are right on.
> I appreciate your generous assistance!
> David
>