Extract data from MS Access

Hi,
What are the various methods by which I can extract data
from MS Access databases using scripts (TSQL/ActiveX). I
know I can use MS DTS. But I'm interested to know about
other options.
TIA,
HariIf you want to bring the Access data into SQLS Server you can use OPENROWSET
or create a Linked Server and query the Access database directly. See
OPENROWSET or sp_addlinkedserver in Books Online for details and examples.
--
David Portas
SQL Server MVP
--|||Hari
You can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:2de1701c46a4d$a3b57220$a301280a@.phx.gbl...
> Hi,
> What are the various methods by which I can extract data
> from MS Access databases using scripts (TSQL/ActiveX). I
> know I can use MS DTS. But I'm interested to know about
> other options.
> TIA,
> Hari

Extract data from MS Access

Hi,
What are the various methods by which I can extract data
from MS Access databases using scripts (TSQL/ActiveX). I
know I can use MS DTS. But I'm interested to know about
other options.
TIA,
HariIf you want to bring the Access data into SQLS Server you can use OPENROWSET
or create a Linked Server and query the Access database directly. See
OPENROWSET or sp_addlinkedserver in Books Online for details and examples.
David Portas
SQL Server MVP
--|||Hari
You can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:2de1701c46a4d$a3b57220$a301280a@.phx
.gbl...
> Hi,
> What are the various methods by which I can extract data
> from MS Access databases using scripts (TSQL/ActiveX). I
> know I can use MS DTS. But I'm interested to know about
> other options.
> TIA,
> Hari

Extract data from MS Access

Hi,
What are the various methods by which I can extract data
from MS Access databases using scripts (TSQL/ActiveX). I
know I can use MS DTS. But I'm interested to know about
other options.
TIA,
Hari
If you want to bring the Access data into SQLS Server you can use OPENROWSET
or create a Linked Server and query the Access database directly. See
OPENROWSET or sp_addlinkedserver in Books Online for details and examples.
David Portas
SQL Server MVP
|||Hari
You can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:2de1701c46a4d$a3b57220$a301280a@.phx.gbl...
> Hi,
> What are the various methods by which I can extract data
> from MS Access databases using scripts (TSQL/ActiveX). I
> know I can use MS DTS. But I'm interested to know about
> other options.
> TIA,
> Hari

extending function: view dependencies

So I have a number of separate databases on my SQL 2005 Server.

I also have a number of Reports in SSRS.

Many of the stored procedures in the various databases reference tables, functions, and stored procedures in other databases on the same server.

How can I accomplish the effect of right clicking on a stored procedure for instance, clicking view dependencies, and having everything show up in that list, not just the items in that DB?

It is all stored in the database in one form or another, but I must be missing some crucial piece to integrating it all together.

Note: I did not design this system, just maintaining and modifying existing items. There are no schemas to speak of. SSRS will most likely move to a dedicated server at one point with other data warehousing functions, so the ability to span servers would be useful.

Thanks!

Matt

Mhmm, I think this is not possible unless you parse the procedue on your own. There are no entries for dependencies in the system tables created for non-db-local objects. Additionally you can′t use schemabinding for maintainance as schemabinding applies only to two part names.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Are there any third party tools that would combine all this information into an enterprise version of "view dependencies"?

thanks|||

Hi,

Jens is correct. There is a lot of confusion about "sysdepends", so we've added a new section explaining SQL dependencies in a web refresh section of the BOL. Please read:

SQL Server 2005 Books Online

Understanding SQL Dependencies

New: 5 December 2005
http://msdn2.microsoft.com/en-us/library/ms345449.aspx

Re: " any third party tools" - A quick MSN search turned up

http://www.red-gate.com/products/sql_dependency_tracker/index.htm

which I have never used.

Regards

Exporting table structures to another database

We have databases with large numbers of tables. We have a separate database for each year. For various reasons, we need to export about 100 of the tables (Structure only, not their data) from last years database into this year's database. What is the best method for doing this? The import/export wizard creates the tables but does not bring in important things like keys.

Regards Shirley A

hi Shirley,

if you are using sql server 2000.

you can use the enterprise manager.

you can right click the database click on "all task" and then

click on "generate Sql scripts" then clcik on options.

click on the objects you need such as PKs, triggers and foreign keys"

for Sql server 2005 you can use the Management studio

right click the database. clcik on task. clcik on generate scripts

and check the options that you need.

after you generate the scripts run it on the new server

regards,

joey

|||

Thanks very much, that worked a treat. I used Enterprise Manager as our databases are SQL Server 2000. Regards

Shirley