MS SQL Server - Re: Need to learn best approach to extract SQL2000 data for report

Author

Re: Need to learn best approach to extract SQL2000 data for report

pbrill1

2006-10-26, 12:12 am

Thank you for the info - I realize that I may not have been clear enough
about my ulitmate purpose for this data. I will take your suggestions and
look at these approaches - but part of my goal is to allow end users on a few
client machines to access reports and create simple queries from the data
that resides on SQL2000 tables. Reporting Services can't easily be accessed
from a client desktop, like MS Access can...can it?
--
pbrill1

"Dejan Sarka" wrote:

>
> Why do you need additional ODBC drivers? You can generate reeports on SQL
> Server 2000 data directly. You just need to learn in which tables your data
> is and how to query these tables (joins, filters,...). You can help yourself
> with SQL Profiler, a tools that comes with SQL Server. Its trace can
> intercept commands SQL Server is getting, so you can, for example, insert
> some data in your application and find out how the application wrote this
> data, i.e. in which tables the data was written. Of course, i would start
> with a good database diagram. You should get it from your vendor, after all,
> it is yours, not vendors data in the database.
>
>
> As I said, I don't think there is a need to do the import. If you really
> want to use Access and not Reporting Services, check the linked tables in
> Access. If you really want to transfer the data, check the Data
> Transformation Services - another tool shipped with SQL Server.
>
>
> IIRC it is free if you have, like you mentioned, the Standard Edition.
>
> --
> Dejan Sarka
> http://www. solidqualitylearning
.com/blogs/
>
>
>

Kevin3NF

2006-10-26, 12:12 am

For reports only, I would use an Access ADP instead of an MDB file...this
will allow the SQL Server engine to process the data needed for the reports.

Not sure of the smartest approach to allow user to create ad-hoc queries.
certainly not an ADP though, since those queries would get saved into the
SQL Server db itself.

--
Kevin Hill
3NF Consulting
www.3nf-inc.com
http://kevin3nf.blogspot.com

"pbrill1" <pbrill1@discussions.microsoft.com> wrote in message
news:3AE662FC-85D7-4D01-8F5A- 51E98F495AD1@microso
ft.com...[color=darkred]
> Thank you for the info - I realize that I may not have been clear enough
> about my ulitmate purpose for this data. I will take your suggestions and
> look at these approaches - but part of my goal is to allow end users on a
> few
> client machines to access reports and create simple queries from the data
> that resides on SQL2000 tables. Reporting Services can't easily be
> accessed
> from a client desktop, like MS Access can...can it?
> --
> pbrill1
>
>
> "Dejan Sarka" wrote:
>

pbrill1

2006-10-28, 7:14 pm

I have started to approach the use of ACCESS ADP for my needs, since I can
allow users to create/receive reports through defined ACCESS reports/queries
that aren't available from our ERP system.

I've found the following link that I'm using to learn how to set up the
ACCESS ADP as you suggest. I haven't made it through all the documentation
yet, but hope to find a way to ensure that the data created/modified in
Access is SELECT ONLY, and can be restricted from UPDATE/DELETE, so that I
don't modify the existing SQL 2000 tables.

Here is the Office Online - Access Projects link that I found, and am wading
through at the moment.
http://office.microsoft.com/en-us/a...2526761033.aspx

--
pbrill1

"Kevin3NF" wrote:

> For reports only, I would use an Access ADP instead of an MDB file...this
> will allow the SQL Server engine to process the data needed for the reports.
>
> Not sure of the smartest approach to allow user to create ad-hoc queries.
> certainly not an ADP though, since those queries would get saved into the
> SQL Server db itself.
>
> --
> Kevin Hill
> 3NF Consulting
> www.3nf-inc.com
> http://kevin3nf.blogspot.com
>
>
> "pbrill1" <pbrill1@discussions.microsoft.com> wrote in message
> news:3AE662FC-85D7-4D01-8F5A- 51E98F495AD1@microso
ft.com...
>
>
>

Kevin3NF

2006-10-31, 12:15 am

Please note that I recommended an ADP for report running. I would NOT let
me users create those reports, or even see the Access Database Window, since
queries they create become SQL Server objects.

Ideally, you build the queries and reports based on their specs, and they
have permissions in SQL Server that allow them to run them

--
Kevin Hill
3NF Consulting
http://www.3nf-inc.com/NewsGroups.htm
http://kevin3nf.blogspot.com

"pbrill1" <pbrill1@discussions.microsoft.com> wrote in message
news:32103F8B-95AA-42F2-9E98- B8D5E82EAB01@microso
ft.com...[color=darkred]
>I have started to approach the use of ACCESS ADP for my needs, since I can
> allow users to create/receive reports through defined ACCESS
> reports/queries
> that aren't available from our ERP system.
>
> I've found the following link that I'm using to learn how to set up the
> ACCESS ADP as you suggest. I haven't made it through all the
> documentation
> yet, but hope to find a way to ensure that the data created/modified in
> Access is SELECT ONLY, and can be restricted from UPDATE/DELETE, so that I
> don't modify the existing SQL 2000 tables.
>
> Here is the Office Online - Access Projects link that I found, and am
> wading
> through at the moment.
> http://office.microsoft.com/en-us/a...2526761033.aspx
>
> --
> pbrill1
>
>
> "Kevin3NF" wrote:
>

Sponsored Links