Data migration from Access to SQL server 2005

Hi,

I am trying to migrate the data from the Access database to Microsoft SQL server on my machine using some scripts. I have enabled the OPENROWSET and OPENDATASOURCE support through the surface area configuration. The script that accesses the excel file works fine but when i run the script that trys to open the .mdb file it gives me following error.

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Microsoft Access Driver] Cannot open database '(unknown)'. It may not be a database that your application recognizes, or the file may be corrupt.".

Msg 7303, Level 16, State 1, Line 35

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

Any help would be greatly appreciated. Thanks.

-padu

Make sure that the startup account for sqlserver is not a service account (i.e. localsystem/service/etc.). Just use a domain acct.|||

Hi oj,

How do you check the startup account is not a service account? I am pretty new to SQL server.

Thanks.

-padu

|||You can run SQL Server Configuration Manager. It allows you to change service account there (if desired).

Data Migration - Insert Script / Fresh install

Hi

I have some tables in my SQL Server 2005 database. I need to create insert scripts for them. Since these tables contain the CLOB and BLOB values and the number of records is huge, I need to devise a way to install this table data on any new database(Assuming the new database is created and the table structure exists) .Can some one suggest me how I can do this ?

DTS or SSIS is out of question because least manual interference is required.

Thanks & Regards
ImtiazYou could use BCP to export the data into text/binary files and then import it back. This is the most efficient way. Generating insert statements is not cumbersome (there are some 3rd party tools that does this). But the files will still be huge and difficult to manage.

Data Migration

Hi friends,
I have changed the existing database scripts by adding primary key and
foreign key constraints.
Now i have created the new structure, I want to mograte the existing data
into the new structure. There is a chance for duplicate records and also
records that does not satisfy referential integrity.
How to create a populate data from the existing DB and how to avoid the
errors?
thanks a lot
vanithaYou have to eliminate the duplicate records, do you want to delete and
discard them, or do you want to keep them ? You have to provide further
informatione / DDL to help you.
Hth, jens Suessmeyer.
http://www.sqlserver2005.de
--|||hi vanitha,
--You can use this code to scan duplicate keys
use northwind
select orderid from [order details]
group by orderid
having count(orderid)>1
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Vanitha" wrote:

> Hi friends,
> I have changed the existing database scripts by adding primary key and
> foreign key constraints.
> Now i have created the new structure, I want to mograte the existing data
> into the new structure. There is a chance for duplicate records and also
> records that does not satisfy referential integrity.
> How to create a populate data from the existing DB and how to avoid the
> errors?
> thanks a lot
> vanitha|||How to generate the populate script?
thanks
vanitha
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> hi vanitha,
> --You can use this code to scan duplicate keys
> use northwind
> select orderid from [order details]
> group by orderid
> having count(orderid)>1
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Vanitha" wrote:
>|||Hi
You may want to check the data first to see if this does apply:
e.g. for duplicates
SELECT A.col1, A.col2, A.col3
FROM MyTableA
GROUP BY A.col1, A.col2, A.col3
HAVING COUNT(*) > 1
For a foreign key try something like:
SELECT A.col1, A.col2, A.col3
FROM MyTableA A
WHERE NOT EXISTS ( SELECT * FROM FKTable F WHERE A.col2 = F.col1 )
If you want to eliminate the duplicates when inserting the data use a
DISTINCT clause
INSERT INTO NewTable ( col1, col2, col3 )
SELECT DISTINCT A.col1, A.col2, A.col3
FROM MyTableA
To eliminate those that do not have FKs
INSERT INTO NewTable ( col1, col2, col3 )
SELECT A.col1, A.col2, A.col3
FROM MyTableA A
WHERE EXISTS ( SELECT * FROM FKTable F WHERE A.col2 = F.col1 )
John
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:DA29DC9C-1109-491C-9970-E69EBAAB16AC@.microsoft.com...
> Hi friends,
> I have changed the existing database scripts by adding primary key and
> foreign key constraints.
> Now i have created the new structure, I want to mograte the existing data
> into the new structure. There is a chance for duplicate records and also
> records that does not satisfy referential integrity.
> How to create a populate data from the existing DB and how to avoid the
> errors?
> thanks a lot
> vanitha|||insert into destination(orderid,x,y,z) --> this are the insert hint
select orderid,x,y,z from orderdetails -->inserted must match
where orderid not in
(
select orderid from [order details]
group by orderid
having count(orderid)>1
)
process those that are duplicate and insert it afterwards
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Vanitha" wrote:
> How to generate the populate script?
> thanks
> vanitha
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>

Data Imprort scripts

Hi All,
I am trying to copy data from Sql 7.0 to Sql 2005. The structures in
both the database is very different. I cannot just map the fileds from
one db.table to another db.table
I will have to write a script tha would insert data in the new
db.table.
One table in the new database will need to be mapped to multiple
tables/fields from the old database.
For eg. The users table in the new database will need to be populated
with data from users and login table from the old database.
Does anyone have sample script I can use? Or point me the right
direction?
Thanks in advance!
MumbaiChefOne approach to consider is to write views on the source server that
return result sets that match the definition of the tables on the
target server. Then you can use DTS to copy the data, reading the
views on the source and writing to the tables on the target.
Another approach would be to define the 7.0 server as a linked server
on the 2005 system. This would allow directly referencing the tables
on the source server from the target.
Roy Harvey
Beacon Falls, CT
On 2 May 2006 05:41:14 -0700, mumbaichef@.gmail.com wrote:

>Hi All,
>I am trying to copy data from Sql 7.0 to Sql 2005. The structures in
>both the database is very different. I cannot just map the fileds from
>one db.table to another db.table
>I will have to write a script tha would insert data in the new
>db.table.
>One table in the new database will need to be mapped to multiple
>tables/fields from the old database.
>For eg. The users table in the new database will need to be populated
>with data from users and login table from the old database.
>Does anyone have sample script I can use? Or point me the right
>direction?
>Thanks in advance!
>MumbaiChef|||How do I make do the linked server? Sorry, for the basic question, I am
new to this:)
I created a SSIS package and saved it in SQL Server. But I am not able
to open it or find it!!!
Any ideas?
Thanks
MumbaiChef|||I'd never set one up in 2005 before, but is seems pretty straight
forward.
In SQL Server Management Studio, under Server Objects, right click on
Linked Servers. Choose New Linked Server...
Give it the name of the 7.0 server as a SQL Server. You will also
have to tell it something under Security. I just got it to work on my
system (defind a linked 2000 server) by choosing the option that
logins not listed at the top use the login's current security context.
Once I had done that I was able to query the other server by adding
the server qualifier:
select *
from OtherServerName.master.dbo.sysobjects
Good luck!
Roy
On 2 May 2006 06:46:07 -0700, mumbaichef@.gmail.com wrote:

>How do I make do the linked server? Sorry, for the basic question, I am
>new to this:)
>I created a SSIS package and saved it in SQL Server. But I am not able
>to open it or find it!!!
>Any ideas?
>Thanks
>MumbaiChef