Importing multiple Access databases

Hi,
I have 30 remote sites that each day send an access
database of sales figures for that day, how can I automate
the process of importing these into an SQL database. I run
SQL server 2000.
As simple a solution as possible please as I am new to SQL
server.
Thanks Dave
Data Transform Services (DTS) would typically be used to import Access data in to SQL server tables, and packages can be scheduled. There is coverage of DTS in BOL and there are several DTS books (Professional SQL Server 2000 DTS is good).
A way to get started is to use the DTS Import/Export Wizard (see tools/Data Transformation Services) with one of the Access databases as the source and the SQL Server database as the destination. Check the Save Package box (select SQL Server for storage
) to save your package to SQL Server (you might want to uncheck the execute imediate box) and use it as a template.

> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Dave
>
|||Assuming the access db's always have the same names, and are located in the
same way, you should be able to simply import the data into your SQL
database, by right clicking the database, selecting import, and then going
through the wizard which is reasonably self explanatory.
At the end of the wizard you will be asked if you want to run the import
now, save it, or schedule it. If you choose the schedule option you can then
select a time for that data to be imported, and how often it should be done,
and from there it should go through without a hitch. To test it, go to
Management, SQL Server Agent, Jobs, and you should see your DTS job in the
right hand side. Right click on it and tell it to start, and it will go
through and perform the import. Once finished you can see the status of the
job, and if necessary view the job history and details of what happened if
it fails for some reason.
Hope that helps
Keith
"Dave Tordoff" <david.tordoff@.companioncare.co.uk> wrote in message
news:167d01c46f3f$5c33fbf0$a601280a@.phx.gbl...
> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Dave

Importing multiple Access databases

Hi,
I have 30 remote sites that each day send an access
database of sales figures for that day, how can I automate
the process of importing these into an SQL database. I run
SQL server 2000.
As simple a solution as possible please as I am new to SQL
server.
Thanks DaveAssuming the access db's always have the same names, and are located in the
same way, you should be able to simply import the data into your SQL
database, by right clicking the database, selecting import, and then going
through the wizard which is reasonably self explanatory.
At the end of the wizard you will be asked if you want to run the import
now, save it, or schedule it. If you choose the schedule option you can then
select a time for that data to be imported, and how often it should be done,
and from there it should go through without a hitch. To test it, go to
Management, SQL Server Agent, Jobs, and you should see your DTS job in the
right hand side. Right click on it and tell it to start, and it will go
through and perform the import. Once finished you can see the status of the
job, and if necessary view the job history and details of what happened if
it fails for some reason.
Hope that helps
Keith
"Dave Tordoff" <david.tordoff@.companioncare.co.uk> wrote in message
news:167d01c46f3f$5c33fbf0$a601280a@.phx.gbl...
> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Dave

Importing multiple Access databases

Hi,
I have 30 remote sites that each day send an access
database of sales figures for that day, how can I automate
the process of importing these into an SQL database. I run
SQL server 2000.
As simple a solution as possible please as I am new to SQL
server.
Thanks DaveData Transform Services (DTS) would typically be used to import Access data
in to SQL server tables, and packages can be scheduled. There is coverage o
f DTS in BOL and there are several DTS books (Professional SQL Server 2000
DTS is good).
A way to get started is to use the DTS Import/Export Wizard (see tools/Data
Transformation Services) with one of the Access databases as the source and
the SQL Server database as the destination. Check the Save Package box (se
lect SQL Server for storage
) to save your package to SQL Server (you might want to uncheck the execute
imediate box) and use it as a template.

> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Dave
>|||Assuming the access db's always have the same names, and are located in the
same way, you should be able to simply import the data into your SQL
database, by right clicking the database, selecting import, and then going
through the wizard which is reasonably self explanatory.
At the end of the wizard you will be asked if you want to run the import
now, save it, or schedule it. If you choose the schedule option you can then
select a time for that data to be imported, and how often it should be done,
and from there it should go through without a hitch. To test it, go to
Management, SQL Server Agent, Jobs, and you should see your DTS job in the
right hand side. Right click on it and tell it to start, and it will go
through and perform the import. Once finished you can see the status of the
job, and if necessary view the job history and details of what happened if
it fails for some reason.
Hope that helps
Keith
"Dave Tordoff" <david.tordoff@.companioncare.co.uk> wrote in message
news:167d01c46f3f$5c33fbf0$a601280a@.phx.gbl...
> Hi,
> I have 30 remote sites that each day send an access
> database of sales figures for that day, how can I automate
> the process of importing these into an SQL database. I run
> SQL server 2000.
> As simple a solution as possible please as I am new to SQL
> server.
> Thanks Davesql

Importing logins?

Hi All,
I have restored some databases. How can I now import the logins?
this is sql 2000Ok I just remembered, generate sql from master db, and there is an option for just the logins.

thanks anyway|||Hi there,

You can also use a 'transfer logins task' in SQL 2000 DTS, it is very easy to use.|||thanks, I am going to start using this method instead

importing from .txt

orange May 14, 5:16 pm show options
Newsgroups: comp.databases.ms-access
From: "orange" <orange...@.mail.ru> - Find messages by this author
Date: 14 May 2005 14:16:49 -0700
Local: Sat,May 14 2005 5:16 pm
Subject: import complex data
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

I've got a database in .txt file similar to this:

BookName;Author;Year;ReviewedBy;Rating;Pages
Nemesis;Isaac Asimov;1989;13,31,24;good;110
Sense & Sensibility;Jane Austen;1970;45,32;great;120
The Bicentennial Man;Isaac Asimov;1965;14;excellent;124
...

the data in field 'ReviewedBy' should be connected to names of people
that read the book. I'd like to have a separate table in Access like
this:

Id;Reviewer
13;Bob
...
24;Jim
...
31;Tom

How do I import that data into database?Hi

This is a SQL Server newsgroup, therefore you may want to post to a
newsgroup more specific to your subject.

You can import a text file from the Insert/table menu. For your 'ReviewedBy'
field you can define relationships using the Tools/Relationships menu. This
will make sure that they are only existing users.
To copy them into subsequent tables then you can write Append queries that
will produce queries like INSERT INTO NewTable SELECT Id, ReviewedBy FROM
LoadedDataTable if you view the SQL.

HTH

John

<orangeKDS@.mail.ru> wrote in message
news:1116107071.499762.101180@.g43g2000cwa.googlegr oups.com...
> orange May 14, 5:16 pm show options
> Newsgroups: comp.databases.ms-access
> From: "orange" <orange...@.mail.ru> - Find messages by this author
> Date: 14 May 2005 14:16:49 -0700
> Local: Sat,May 14 2005 5:16 pm
> Subject: import complex data
> Reply | Reply to Author | Forward | Print | Individual Message | Show
> original | Remove | Report Abuse
> I've got a database in .txt file similar to this:
> BookName;Author;Year;ReviewedBy;Rating;Pages
> Nemesis;Isaac Asimov;1989;13,31,24;good;110
> Sense & Sensibility;Jane Austen;1970;45,32;great;120
> The Bicentennial Man;Isaac Asimov;1965;14;excellent;124
> ..
> the data in field 'ReviewedBy' should be connected to names of people
> that read the book. I'd like to have a separate table in Access like
> this:
> Id;Reviewer
> 13;Bob
> ..
> 24;Jim
> ..
> 31;Tom
> How do I import that data into database?

Importing Errors when using BCP

Alright, I've been getting an interesting error when using BCP between two databases on the same MSSQL Server 2000. I am bulk copying data from an older version of a database into a newer one, which includes new tables and columns. When bcp gets to a certain table, I get this error

SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

If anyone would happen to know exactly what SQL is executed by BCP or where to find such information, that would be great. None of the columns' data types have been changes in the newer version of the database.
-MikeYou migt try running profiler while using BCP but I don't think you will find much. The error seems to indicate a problem converting data.

Could you post the table structures, BCP command(s) and any BCP format files?|||DTS might give you more of a clue to where the actual error is.

I had a problem like this once that turned out to be typos in date fields that were before 1/1/1753 or whatever the magic "start date" in SQL server is (like '12/31/0999' instead of '12/31/1999').|||You should get a nice picture of the problem with the -o(filename) switch on BCP. The output file will just contain the rows that are causing the errors. I had a similar problem with "Right string truncation" errors in BCP, and the -o spotlighted the problem immediately.

Importing Error

Hi all,

From SQL Server 2000 Import wizard, if we use Copy Objects & data between SQL Server Databases it'll copies table with primary key. Similar to this what is the alternative method in SQL Server 2005.

Please suggest me to solve the problem

Thanks in advance
Karna

You still have the Import/Export wizard in SQL Server 2005 but you would need to have SQL Server Management Studio not the express version.

Right click on a database and choose tasks-> export data

which then lets you copy tables.

I hope that is what you are looking for.

|||Hi,

Thanks for reply,

As u said it is similar to Import/Export wizard that we found in SQL server 2005 Management studio, here in this example it does the export of table to the destination database but primary key will not be attached. Is it SSIS is helpful for this purpose.

My problem is when I export table to different database it should export all attributes of table which includes Primary Key (as similar to copy objects & data between SQL Server Databases in SQL Server 2000).

Please suggest me to solve my problem

Thanks in advance
Karna|||

Have you looked at the Copy Objects task available in the SSIS designer?

Thanks.

|||Hi,

Thanks for the reply.

It is same as I briefed in earlier mail.
My problem is when I import table from one database to another it has to import data with primary key constraints.

Thanks in advance
Karna

Importing Error

Hi all,

From SQL Server 2000 Import wizard, if we use Copy Objects & data between SQL Server Databases it'll copies table with primary key. Similar to this what is the alternative method in SQL Server 2005.

Please suggest me to solve the problem

Thanks in advance
Karna

You still have the Import/Export wizard in SQL Server 2005 but you would need to have SQL Server Management Studio not the express version.

Right click on a database and choose tasks-> export data

which then lets you copy tables.

I hope that is what you are looking for.

|||Hi,

Thanks for reply,

As u said it is similar to Import/Export wizard that we found in SQL server 2005 Management studio, here in this example it does the export of table to the destination database but primary key will not be attached. Is it SSIS is helpful for this purpose.

My problem is when I export table to different database it should export all attributes of table which includes Primary Key (as similar to copy objects & data between SQL Server Databases in SQL Server 2000).

Please suggest me to solve my problem

Thanks in advance
Karna|||

Have you looked at the Copy Objects task available in the SSIS designer?

Thanks.

|||Hi,

Thanks for the reply.

It is same as I briefed in earlier mail.
My problem is when I import table from one database to another it has to import data with primary key constraints.

Thanks in advance
Karna

Importing databases in SQL 2005

I'm having a lot of trouble importing/moving databases between SQL2005
servers.
-If i detach/copy files/attach, I get user security problems (ie. user
defined in database "does not exist" on new server, but cannot be removed
from database as owns schemas etc.)
-Same with Backup/restore
-If I try to copy or import data after creating a "blank" database with only
the user ID in question as dbo, I lose primary keys.
All actions are being performed through Management Console with the SQL
admin ID. Most databases are running in 2000 compatability mode following the
recent upgrades. Servers are not on Active Directory.
Can someone give me some hints about how to migrate databases?
Seems that some Login of the Source Server is not existing on the Target
Server. If it is so, then first create those Logins on the Target Server and
then try the Backup/Restore method.
"The Vogon" wrote:

> I'm having a lot of trouble importing/moving databases between SQL2005
> servers.
> -If i detach/copy files/attach, I get user security problems (ie. user
> defined in database "does not exist" on new server, but cannot be removed
> from database as owns schemas etc.)
> -Same with Backup/restore
> -If I try to copy or import data after creating a "blank" database with only
> the user ID in question as dbo, I lose primary keys.
> All actions are being performed through Management Console with the SQL
> admin ID. Most databases are running in 2000 compatability mode following the
> recent upgrades. Servers are not on Active Directory.
> Can someone give me some hints about how to migrate databases?
|||Thanks for the reply... I'll try that again and let you know...
"Absar Ahmad" wrote:
[vbcol=seagreen]
> Seems that some Login of the Source Server is not existing on the Target
> Server. If it is so, then first create those Logins on the Target Server and
> then try the Backup/Restore method.
> "The Vogon" wrote:

Importing databases in SQL 2005

I'm having a lot of trouble importing/moving databases between SQL2005
servers.
-If i detach/copy files/attach, I get user security problems (ie. user
defined in database "does not exist" on new server, but cannot be removed
from database as owns schemas etc.)
-Same with Backup/restore
-If I try to copy or import data after creating a "blank" database with only
the user ID in question as dbo, I lose primary keys.
All actions are being performed through Management Console with the SQL
admin ID. Most databases are running in 2000 compatability mode following th
e
recent upgrades. Servers are not on Active Directory.
Can someone give me some hints about how to migrate databases?Seems that some Login of the Source Server is not existing on the Target
Server. If it is so, then first create those Logins on the Target Server and
then try the Backup/Restore method.
"The Vogon" wrote:

> I'm having a lot of trouble importing/moving databases between SQL2005
> servers.
> -If i detach/copy files/attach, I get user security problems (ie. user
> defined in database "does not exist" on new server, but cannot be removed
> from database as owns schemas etc.)
> -Same with Backup/restore
> -If I try to copy or import data after creating a "blank" database with on
ly
> the user ID in question as dbo, I lose primary keys.
> All actions are being performed through Management Console with the SQL
> admin ID. Most databases are running in 2000 compatability mode following
the
> recent upgrades. Servers are not on Active Directory.
> Can someone give me some hints about how to migrate databases?|||Thanks for the reply... I'll try that again and let you know...
"Absar Ahmad" wrote:
[vbcol=seagreen]
> Seems that some Login of the Source Server is not existing on the Target
> Server. If it is so, then first create those Logins on the Target Server a
nd
> then try the Backup/Restore method.
> "The Vogon" wrote:
>

Importing databases in SQL 2005

I'm having a lot of trouble importing/moving databases between SQL2005
servers.
-If i detach/copy files/attach, I get user security problems (ie. user
defined in database "does not exist" on new server, but cannot be removed
from database as owns schemas etc.)
-Same with Backup/restore
-If I try to copy or import data after creating a "blank" database with only
the user ID in question as dbo, I lose primary keys.
All actions are being performed through Management Console with the SQL
admin ID. Most databases are running in 2000 compatability mode following the
recent upgrades. Servers are not on Active Directory.
Can someone give me some hints about how to migrate databases?Seems that some Login of the Source Server is not existing on the Target
Server. If it is so, then first create those Logins on the Target Server and
then try the Backup/Restore method.
"The Vogon" wrote:
> I'm having a lot of trouble importing/moving databases between SQL2005
> servers.
> -If i detach/copy files/attach, I get user security problems (ie. user
> defined in database "does not exist" on new server, but cannot be removed
> from database as owns schemas etc.)
> -Same with Backup/restore
> -If I try to copy or import data after creating a "blank" database with only
> the user ID in question as dbo, I lose primary keys.
> All actions are being performed through Management Console with the SQL
> admin ID. Most databases are running in 2000 compatability mode following the
> recent upgrades. Servers are not on Active Directory.
> Can someone give me some hints about how to migrate databases?|||Thanks for the reply... I'll try that again and let you know...
"Absar Ahmad" wrote:
> Seems that some Login of the Source Server is not existing on the Target
> Server. If it is so, then first create those Logins on the Target Server and
> then try the Backup/Restore method.
> "The Vogon" wrote:
> > I'm having a lot of trouble importing/moving databases between SQL2005
> > servers.
> > -If i detach/copy files/attach, I get user security problems (ie. user
> > defined in database "does not exist" on new server, but cannot be removed
> > from database as owns schemas etc.)
> > -Same with Backup/restore
> > -If I try to copy or import data after creating a "blank" database with only
> > the user ID in question as dbo, I lose primary keys.
> >
> > All actions are being performed through Management Console with the SQL
> > admin ID. Most databases are running in 2000 compatability mode following the
> > recent upgrades. Servers are not on Active Directory.
> >
> > Can someone give me some hints about how to migrate databases?

Importing Data With Duplicate Keys

I'm trying to merge two Access databases into one SQL server database. I have 3 tables that are all related with primary and foreign keys.

When I try to import my second set of 3 tables I get errors about the keys already existing in the database. Is there any way to force SQL server to assign new keys while preserving my existing relationships? Thanks!No.
You will have to update the keys in two or your Access databases to ensure that no records from the three databases conflict. And you will likely also have to deal with issues where you have identical records in each database that should be merged rather than duplicated (such as in lookup lists...).
Or, another possible solutions is to load the data from separate databases into separate versions of the same SQL Server table, assigning new GUID identifiers simultaneously. The three tables can then be merged using GUIDs rather than the integer/identity values you probable have right now. Note that you will still need to update foreign keys in related tables to reference the new GUIDs.
These things are all just part of a DBA's day...|||Blah, I knew this wouldn't be fun. Thanks for the info.|||want to show us some DDL and sample data of what the problem is?|||I never said it wouldn't be fun. I just said it is what we DBAs do all day.

Every day.

Day in, day out...

Day after day after day after day...

Hell, it's a blast.|||i have 2 contradictory seanisms on this...

I sometimes tell the bellachers around here

"if you ain't having fun, go do something else for a living"

and when they complain sometimes I tell them...

"if it was easy and fun they would not pay us money to do it"|||I figured out an easy way to do this. Basically I check the ID of the last field in my master database. Before I import data I remove the primary key restriction and run an update query whereby I add that last number to each of the existing ID numbers. This automatically iterates my keys to the new start point I need.|||Fine. Now make sure you update all the foreign key references to the primary keys you just modified...|||I forgot to mention to do that immediately afterward, thanks.|||sounds like a total fubar process

how does a surrogate key have any meaning?|||I admit, it's not the best solution--if I understood what you said. But we're only talking about three tables.

importing data to new database

hi,

i have 2 databases (DT1 and DT2), both contains one table customers with almost identical structure, the only difference is the firstname and lastname in DT1 are char(30) and varchar(50) in DT2. i've created both tables already. i tried using SQL 2005 managment studio to do a 'import data' and it worked. all the information in DT1 were copied to DT2.

now i have to repeat the process for many databases and it would be too tedious to do it one by one. my question is, is there a script i can use to do it so that i could make a small application to automate the process?

thanks in advance.

bob

hi bob,

you can save the script generated by the wizard as an ssis or dts package

you can automate from ssis or dts

regards,

|||

I addition to Joyea and the use of SSIS, which is very extensible you can also use the threepart notation to access the data from another database and write a stored procedure for this:

Select * From Database.Schema.Objectname

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

thanks for your suggestions.

Bobby

Importing data duplicate databases

I want to import data from a live site database into a development database (SQL Server 2005 Express) using the DTSWizard. Once I copy/paste the live database to my dev machine, I cant attach the live site database because it has the same name as the database on the dev site.

A simple solution I would assume is to change one of the names. But I can't seem to change the "orignal file name".

A backup/restore won't work for me because I made table/field changes to the dev database.

Thanks
--Dietrich

Are you using SQL2000 or SQL2005?

|||

Hi Dietrich,

1. Before copying the database .mdf and .ldf files, you'd better first make the database offline.
2. Copy the database to your new machine, and rename them.
3. In the new database server, attach the database and you can specify new name of the database.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

importing access tables to sql server express

hi. this is a beginner question as i am new to databases :)

so, I have an access database and I want to take 2 tables from it and copy it to sql server in my ASP.NET application (.MDF file)

I have visual studio 2005 installed on my computer and got no sql server 2005 installed on it.

what is the way copy 2 tables to the ASP.NET database application?

thanks alot guys.

Take a look at this thread:http://forums.asp.net/519536/ShowPost.aspx|||

THANKS

Importing Access databases into SQL Server

Hi there,

I have a situation where an application needs to import data from
number of access mdb files on a daily bases. The file names change
every day. The data import is very straight forward:

insert into sql_table select * from acess_table

There are up to 8 tables in each access file and some access files will
have less. So the process needs to figure out which tables exist in
Access mdb file and import them whole into sql staging tables.

Any recommendations are appreciated.

ThanksIt probably depends where you're running the load process from - that's
not really clear (to me) from your comments. If you push the data from
Access, then presumably it's not a problem, because you know which
tables are in each database. If you need to pull from MSSQL, and the
Access database names are always the same, then you could create linked
servers to each one, and get the data that way.

If the Access database names change, and you don't know in advance how
many tables there will be in each one, then you'll need something more
flexible. Personally, I would probably use DTS to connect to each
database, query the metadata to get the table names (although I don't
know exactly how to do that - perhaps an Access group could give more
details), and then load the data dynamically. Or write a tool in Perl,
C# or whatever to dynamically export and import the data via flat files
or ADO.

Finally, one other option would be to convert your Access databases to
ADPs, so you would have the data in MSSQL already. But this may not be
possible or desirable in your situation.

If this doesn't help, I suggest you post some more specific details of
what you need to do.

Simon|||How are these Access files being created daily
with different names and more importantly why?

What kind of bizarre methodology would require
different-named Access files on a daily basis?

GeoSynch

<boblotz2001@.yahoo.com> wrote in message
news:1112217371.433176.279470@.f14g2000cwb.googlegr oups.com...
> Hi there,
> I have a situation where an application needs to import data from
> number of access mdb files on a daily bases. The file names change
> every day. The data import is very straight forward:
> insert into sql_table select * from acess_table
> There are up to 8 tables in each access file and some access files will
> have less. So the process needs to figure out which tables exist in
> Access mdb file and import them whole into sql staging tables.
> Any recommendations are appreciated.
> Thanks