|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Need to learn best approach to extract SQL2000 data for reportingproblem: As administrator for a small/mid-size company running an ERP that still uses SQL 2000 Standard (our ERP software provider has not tested for SQL2005 until a later release), I am seeking a method to provide reports from our ERP that the software package itself does not provide easily. The vendor has sent me their ODBC drivers (with little documentation on how to install them), so I'm trying to do a bit of legwork to understand how to use ODBC drivers to export data into a format that I can produce custom reports from. I'm assuming that there is a way to port SQL2000 data into MS Access 2003. If so, is there any documentation on how to do this? I've been unsucessful in my research attempts so far. Also, does SQL Reporting Services provide any possibilities (i.e., does it require any additional costs other than our purchased SQL2000 license, and is it the right tool to use anyway?) I would greatly appreciate any assistance toward improving my method of producing better reports (with existing software - zero additional software cost is a key goal for the moment) - using an ERP utilizing SQL2000. -- pbrill1 > As administrator for a small/mid-size company running an ERP that still Why do you need additional ODBC drivers? You can generate reeports on SQL > uses > SQL 2000 Standard (our ERP software provider has not tested for SQL2005 > until > a later release), I am seeking a method to provide reports from our ERP > that > the software package itself does not provide easily. The vendor has sent > me > their ODBC drivers (with little documentation on how to install them), so > I'm > trying to do a bit of legwork to understand how to use ODBC drivers to > export > data into a format that I can produce custom reports from. 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. > I'm assuming that As I said, I don't think there is a need to do the import. If you really > there is a way to port SQL2000 data into MS Access 2003. If so, is there > any > documentation on how to do this? I've been unsucessful in my research > attempts so far. 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. > Also, does SQL Reporting Services provide any possibilities IIRC it is free if you have, like you mentioned, the Standard Edition.> (i.e., does it require any additional costs other than our purchased > SQL2000 > license, and is it the right tool to use anyway?) 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? -- Show quoteHide quotepbrill1 "Dejan Sarka" wrote: > > As administrator for a small/mid-size company running an ERP that still > > uses > > SQL 2000 Standard (our ERP software provider has not tested for SQL2005 > > until > > a later release), I am seeking a method to provide reports from our ERP > > that > > the software package itself does not provide easily. The vendor has sent > > me > > their ODBC drivers (with little documentation on how to install them), so > > I'm > > trying to do a bit of legwork to understand how to use ODBC drivers to > > export > > data into a format that I can produce custom reports from. > > 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. > > > I'm assuming that > > there is a way to port SQL2000 data into MS Access 2003. If so, is there > > any > > documentation on how to do this? I've been unsucessful in my research > > attempts so far. > > 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. > > > Also, does SQL Reporting Services provide any possibilities > > (i.e., does it require any additional costs other than our purchased > > SQL2000 > > license, and is it the right tool to use anyway?) > > IIRC it is free if you have, like you mentioned, the Standard Edition. > > -- > Dejan Sarka > http://www.solidqualitylearning.com/blogs/ > > > 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. Show quoteHide quote "pbrill1" <pbri***@discussions.microsoft.com> wrote in message news:3AE662FC-85D7-4D01-8F5A-51E98F495AD1@microsoft.com... > 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: > >> > As administrator for a small/mid-size company running an ERP that still >> > uses >> > SQL 2000 Standard (our ERP software provider has not tested for SQL2005 >> > until >> > a later release), I am seeking a method to provide reports from our ERP >> > that >> > the software package itself does not provide easily. The vendor has >> > sent >> > me >> > their ODBC drivers (with little documentation on how to install them), >> > so >> > I'm >> > trying to do a bit of legwork to understand how to use ODBC drivers to >> > export >> > data into a format that I can produce custom reports from. >> >> 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. >> >> > I'm assuming that >> > there is a way to port SQL2000 data into MS Access 2003. If so, is >> > there >> > any >> > documentation on how to do this? I've been unsucessful in my research >> > attempts so far. >> >> 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. >> >> > Also, does SQL Reporting Services provide any possibilities >> > (i.e., does it require any additional costs other than our purchased >> > SQL2000 >> > license, and is it the right tool to use anyway?) >> >> IIRC it is free if you have, like you mentioned, the Standard Edition. >> >> -- >> Dejan Sarka >> http://www.solidqualitylearning.com/blogs/ >> >> >> 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/assistance/CH062526761033.aspx -- Show quoteHide quotepbrill1 "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" <pbri***@discussions.microsoft.com> wrote in message > news:3AE662FC-85D7-4D01-8F5A-51E98F495AD1@microsoft.com... > > 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: > > > >> > As administrator for a small/mid-size company running an ERP that still > >> > uses > >> > SQL 2000 Standard (our ERP software provider has not tested for SQL2005 > >> > until > >> > a later release), I am seeking a method to provide reports from our ERP > >> > that > >> > the software package itself does not provide easily. The vendor has > >> > sent > >> > me > >> > their ODBC drivers (with little documentation on how to install them), > >> > so > >> > I'm > >> > trying to do a bit of legwork to understand how to use ODBC drivers to > >> > export > >> > data into a format that I can produce custom reports from. > >> > >> 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. > >> > >> > I'm assuming that > >> > there is a way to port SQL2000 data into MS Access 2003. If so, is > >> > there > >> > any > >> > documentation on how to do this? I've been unsucessful in my research > >> > attempts so far. > >> > >> 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. > >> > >> > Also, does SQL Reporting Services provide any possibilities > >> > (i.e., does it require any additional costs other than our purchased > >> > SQL2000 > >> > license, and is it the right tool to use anyway?) > >> > >> IIRC it is free if you have, like you mentioned, the Standard Edition. > >> > >> -- > >> Dejan Sarka > >> http://www.solidqualitylearning.com/blogs/ > >> > >> > >> > > > 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 Show quoteHide quote "pbrill1" <pbri***@discussions.microsoft.com> wrote in message news:32103F8B-95AA-42F2-9E98-B8D5E82EAB01@microsoft.com... >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/assistance/CH062526761033.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" <pbri***@discussions.microsoft.com> wrote in message >> news:3AE662FC-85D7-4D01-8F5A-51E98F495AD1@microsoft.com... >> > 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: >> > >> >> > As administrator for a small/mid-size company running an ERP that >> >> > still >> >> > uses >> >> > SQL 2000 Standard (our ERP software provider has not tested for >> >> > SQL2005 >> >> > until >> >> > a later release), I am seeking a method to provide reports from our >> >> > ERP >> >> > that >> >> > the software package itself does not provide easily. The vendor has >> >> > sent >> >> > me >> >> > their ODBC drivers (with little documentation on how to install >> >> > them), >> >> > so >> >> > I'm >> >> > trying to do a bit of legwork to understand how to use ODBC drivers >> >> > to >> >> > export >> >> > data into a format that I can produce custom reports from. >> >> >> >> 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. >> >> >> >> > I'm assuming that >> >> > there is a way to port SQL2000 data into MS Access 2003. If so, is >> >> > there >> >> > any >> >> > documentation on how to do this? I've been unsucessful in my >> >> > research >> >> > attempts so far. >> >> >> >> 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. >> >> >> >> > Also, does SQL Reporting Services provide any possibilities >> >> > (i.e., does it require any additional costs other than our purchased >> >> > SQL2000 >> >> > license, and is it the right tool to use anyway?) >> >> >> >> IIRC it is free if you have, like you mentioned, the Standard Edition. >> >> >> >> -- >> >> Dejan Sarka >> >> http://www.solidqualitylearning.com/blogs/ >> >> >> >> >> >> >> >> >> Hi
You can link your tables from SQL Server into Access, and alternative to access would be reporting services. To link a database to access you can use the file/get external data/link table. You will need a (Machine) DSN configured (ODBC Data Sources under the Administrative Tools menu). SQL Server has it's own OLEDB driver which you would normally use, so I am not sure why there vendor has a different ODBC driver. If the Access database is to be used on other machines the DSN should be created on that machine as well. HTH John Show quoteHide quote "pbrill1" wrote: > I am attempting to learn more about how to best approach the following SQL > problem: > > As administrator for a small/mid-size company running an ERP that still uses > SQL 2000 Standard (our ERP software provider has not tested for SQL2005 until > a later release), I am seeking a method to provide reports from our ERP that > the software package itself does not provide easily. The vendor has sent me > their ODBC drivers (with little documentation on how to install them), so I'm > trying to do a bit of legwork to understand how to use ODBC drivers to export > data into a format that I can produce custom reports from. I'm assuming that > there is a way to port SQL2000 data into MS Access 2003. If so, is there any > documentation on how to do this? I've been unsucessful in my research > attempts so far. Also, does SQL Reporting Services provide any possibilities > (i.e., does it require any additional costs other than our purchased SQL2000 > license, and is it the right tool to use anyway?) > > I would greatly appreciate any assistance toward improving my method of > producing better reports (with existing software - zero additional software > cost is a key goal for the moment) - using an ERP utilizing SQL2000. > > > -- > pbrill1  
Thread options
Other interesting topics
Sybase/Sql server Outer Joins, nulls, and counts
Potential Concurrency Issue? xp_cmdshell returns null Problems Defragging Indices (SQL2005 64-bit) Building New SQL Server Query on date/time data type SQL 2000 performance objects doesnt appear on performance monitor Help with addition to Missing date query how to create .sdf files ? Disabling SQL 2005 Case Sensitive |
|||||||||||||||||||||||
