Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Converting Access to SQL

Posted on 2007-08-02
26
Medium Priority
?
339 Views
Last Modified: 2010-03-19
Hello,

I'm trying to convert an MS Access database to SQL Server, and I'm using SQL Server Migration Assistant for Access. I'm following the steps of the wizard, but when I get to the "Connect to SQL Server" window, nothing shows up in the "Server Name" dropdown.  I have an instance of Management Studio up and running, but I get no options for a server selection.

What am I missing?

0
Comment
Question by:NigelRocks
  • 14
  • 10
  • +1
26 Comments
 
LVL 13

Accepted Solution

by:
adraughn earned 2000 total points
ID: 19617739
0
 
LVL 13

Expert Comment

by:Lucas
ID: 19617740
Maybe you didn't install SQL server properly.  Is it on the network or on your PC?
0
 
LVL 13

Expert Comment

by:adraughn
ID: 19617746
did you try typing in the server name?
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 1

Author Comment

by:NigelRocks
ID: 19618223
I went ahead and used the Import/Export wizard in SQL Server 2000 Enterprise Manager.  It worked fine except that two tables were copied without their data.  Once I get that figured out, I think I'll be in good shape.
0
 
LVL 13

Assisted Solution

by:adraughn
adraughn earned 2000 total points
ID: 19618356
when you link the table in access, copy the data out of the old table, then use Edit-Paste Append to paste the data in the new table.

or

right click on table (or db name, i don't remember) in managment studio, choose tasks - import data and follow the wizard.

a
0
 
LVL 1

Author Comment

by:NigelRocks
ID: 19618486
I tried importing a table at a time in Management Studio, but I get the following errors on the first one:


- Copying to [KnowledgeTracker].[dbo].[Assignment] (Error)
Messages
Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".
 (SQL Server Import and Export Wizard)
 
Error 0xc020901c: Data Flow Task: There was an error with input column "ASSIGNMENT_END_DATE" (66) on input "Destination Input" (50). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
 (SQL Server Import and Export Wizard)
 
Error 0xc0209029: Data Flow Task: The "input "Destination Input" (50)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (50)" specifies failure on error. An error occurred on the specified object of the specified component.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - Assignment" (37) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0209029.
 (SQL Server Import and Export Wizard)
0
 
LVL 13

Expert Comment

by:adraughn
ID: 19618508
did you try the paste append? what steps did you take to get the above error?
0
 
LVL 1

Author Comment

by:NigelRocks
ID: 19618727
I didn't try the paste/append yet because I'm not sure what you meant.

The above error occurs when I do an "Import Data" operation in Management Studio.  When I hit "finish", it generates the error.
0
 
LVL 13

Expert Comment

by:adraughn
ID: 19618827
ok, did you link the sql table in access yet?
0
 
LVL 13

Assisted Solution

by:adraughn
adraughn earned 2000 total points
ID: 19618863
this is fromt he thread i posted at the beginning of this message:

a) From the tables tab in access, click on new - link table
b) Change 'Files of Type' to 'ODBC Databases'
c) Click on 'New' on the Select Data Source tab
d) Scroll down in the listbox and choose SQL Server as your driver
e) Type in a data source name and click Next
f) Click Finish
g) Type in a description for the database and select your server from the drop down or type it in then click Next
h) i would suggest using NT authentication, but it's your choice - make it and click next
i) click on 'change the default database to'. you don't want to store your data in master. you will need to create a database on the server specifically for your database. Once you have, select the database name from the drop down.
j) Click next - click finish - click Test Data Source
k) Click OK and then OK again
l) Click on the .dsn file that you just created and click OK
m) a list of tables will appear that are in that database on the server. select the tables that you would like to link from the list and click OK
n) you will want to open the table in design view and check the primary keys.

you would link a SQL View the same way, except it will prompt you for a primary key. Note that if you do not provide a primary key for a view, it will be read only.

oh, you only have to create the .dsn once. if you want to link more tables/views, you just use the .dsn that you already created.

once you have your tables linked, notice that they come in as dbo_tablename. You will need to rename the tables as tablename, removing the dbo_. Then you can delete your access table (hopefully a link to a table if you have a split db) and your queries will work with the sql table.
0
 
LVL 1

Author Comment

by:NigelRocks
ID: 19619518
>>>>>>you will need to create a database on the server specifically for your database. Once you have, select the database name from the drop down.

I wasn't able to do that.  I had to use one that already exists.  If I typed a new one in, it would say, "The database entered is not valid."


>>>>>>once you have your tables linked, notice that they come in as dbo_tablename. You will need to rename the tables as tablename, removing the dbo_. Then you can delete your access table (hopefully a link to a table if you have a split db) and your queries will work with the sql table.


They don't have the .dbo prefix when I open them in SQL Server.  Everything looks good except one of the tables is empty.  Or do you mean I have to rename them in Access by removing the dbo_ ?




0
 
LVL 2

Expert Comment

by:cpctech
ID: 19619814
Silly thought...

If you are using Access 2000 you have to have at least SP2 for it to work
0
 
LVL 1

Author Comment

by:NigelRocks
ID: 19619851
I'm using Access 2003, but thanks for the silly thought.  :-)
0
 
LVL 13

Assisted Solution

by:adraughn
adraughn earned 2000 total points
ID: 19620165
<I wasn't able to do that. >
you can't create the database there. you need to create the database in sql. you do have a sql database created already, right?

<Or do you mean I have to rename them in Access by removing the dbo_ ?>
right. you won't see the dbo in sql. but in access, if you were referecing qryTest in your form and now qryTest is dbo_qryTest, your form will not work. So it is easier to rename the linked sql tables by removing the dbo_ then it is to change all of your code/forms/reports.

a
0
 
LVL 1

Author Comment

by:NigelRocks
ID: 19620597
The first transfer I did was with a dummy database for obvious reasons.  Now I don't get any results with the real database (which is just like the dummy database).  I must be leaving out a step somewhere....
0
 
LVL 1

Author Comment

by:NigelRocks
ID: 19836277
adraughn,

I'm getting hung up on the DSN part of things.  I followed your steps almost all the way through.  When I get to "L" I don't get the tables you're talking about.  I get one table called "SysConstraints" that doesn't seem to have anything to do with anything.  What's missing?

0
 
LVL 13

Assisted Solution

by:adraughn
adraughn earned 2000 total points
ID: 19836702
<I'm getting hung up on the DSN part of things.>
Did you successfully create the DSN as stated in steps c-j?

< I get one table called "SysConstraints" >
maybe you mapped to the wrong database. What database are you trying to map to? You said that you can't create a new database? How did you try to create it? What happened? Open the database that you think you are mapped to in sql and look at the tables.

a
0
 
LVL 1

Author Comment

by:NigelRocks
ID: 19841164
adraughn,

Still no luck.  I was able to build a database on the server and my DSN tests positive.  But my list of link tables has only:

dbo.syscontraints
dbo.syssegments

Maybe if I keep playing with it, I'll stumble on something.
0
 
LVL 13

Expert Comment

by:adraughn
ID: 19841824
Open the database that you think you are mapped to in sql and look at the tables. What tables are there?
0
 
LVL 13

Expert Comment

by:adraughn
ID: 19841855
did you change the default database when you added the dsn? you have to change it from master to your new database name.
0
 
LVL 1

Author Comment

by:NigelRocks
ID: 19842136
Yes, I changed the default, but there are no tables in my database.
0
 
LVL 13

Expert Comment

by:adraughn
ID: 19842449
you eman there are no tables in your db in sql either? did you create the tables in the sql db?

a
0
 
LVL 1

Author Comment

by:NigelRocks
ID: 19843577
No, I didn't create tables in the SQL db.  I just created a database to contain those tables.  To me, the whole idea of an operation like this is that I don't have to create tables on the destination server.
0
 
LVL 13

Expert Comment

by:adraughn
ID: 19843633
In my solution above, you create the table design manually in SQL (instead of using the migration assistant) then you link the tables, as stated above in instructions a-? and then you populate th tables thru access by copying the data from your access table and paste appending to the sql table.

In my opinion, it actually saves time in the long run to create the tables manually in sql. the migration assistant often prompts you with errors on data types, data, etc. You want to make sure that your data types come in exactly how you need them. So you have to review what the upsize tool does anyway, so why not just do it yourself and skip troubleshooting the errors?

If you neeed assistance with creating a table in sql, let me know. If you would rather sift thru upsize errors, we can work on that too. but i prefer option 1, having been there myself.

adria
0
 
LVL 13

Expert Comment

by:adraughn
ID: 19843659
<It worked fine except that two tables were copied without their data. >

That's why I thought you had your tables in SQL already...
0
 
LVL 13

Expert Comment

by:adraughn
ID: 19986398
Did you get this resolved?

-a
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question