| Author |
 Topic  |
|
Astralis
Senior Member
USA
1218 Posts |
 Posted - 25 December 2004 : 16:37:28
|
| I'm trying to upgrade an Access 2000 DB to MSSQL server. I can get it to upgrade but all the primary keys are gone. How do people use DTS if it deletes this type of data? What is the workaround? |
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 25 December 2004 : 17:46:47
|
Transfer Objects and Data
Or use the Access upsizing wizard (not for Snitz DB's though)
|
======
Doug G
======
Computer history and help at www.dougscode.com |
 |
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 25 December 2004 : 18:06:51
|
| How do you get that option to show up? It is greyed-out in the DTS. Plus it says it, "between SQL Server databases". Will this still work between Access and SQL Server? |
Edited by - Astralis on 25 December 2004 18:10:08 |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 25 December 2004 : 18:13:37
|
BTW, I've tried Access Upsizing Wizard (not for Snitz, of course) and I can't get it to connect to the DB which is very odd.
I followed these instructions exactly:
1. Open your existing database inside of MS Access on your computer and choose Tools > Database Utilities > Upsizing Wizard.
2. Once the wizard has started choose "Use existing database" and click "Next.". You will then be asked to choose a data source. If you already have one set-up for your SQL account, you may select it here and skip to step eight.
3. Go to the "Machine Data Source" tab and click New
4. Choose a user or system data source depending on your security needs. Then select "SQL Server" from the list and click "Next" and "Finish."
5. You will then be prompted for the SQL server information. You received or selected this when you signed up for your SQL account. The name can be anything you wish to refer to the data source with. The description field is optional and the server is the IP address of your SQL server.
6. On the next screen choose to login using "SQL Server Authentication." Enter your username and password for your SQL account and click "Next."
Note: Be sure "TCP/IP" is selected in the "Client Configuration"
7. Leave the next two screens at their default settings and click "Finish." This will pop-up a confirmation screen where you can test the data source. I DID THIS WITH SUCCESS!
8. Once you have your data source created, make sure it is selected from the list and hit "OK."
9. Enter your SQL password and hit "OK."
10. You will then be asked which tables you want to convert to the new database. Move the tables you wish to convert into the window on the right and click "OK."
11. Leave the remaining settings at their defaults unless you know you need to change them. Click "Finish."
Then, I get this error:
[DBNETLIB][ConnectionOpen(Connect()).]Specified SQL Server not found.
I'm on CrystalTech and I presume they have the latest drivers and edition of SQL Server. I'm on XP Home SP1. |
Edited by - Astralis on 25 December 2004 18:30:33 |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 26 December 2004 : 01:33:03
|
Apparently you didn't set up the DSN properly, based on the error message. I haven't used an upsizing wizard against CT but I don't know why it wouldn't work.
I think you're right, the "transfer objects and data" is only for sql server db's.
I'd recommend getting a local sql server set up, do your upsizing there and make sure the resulting db is good, then use DTS to copy objects & data up to your CT DB. The sql server developer version is only about $50.00 or so.
|
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 26 December 2004 : 01:37:54
|
Doug,
Thanks. I have EM still in trial. Do I still need the SQL Server Dev version or is that all included?
quote:
Apparently you didn't set up the DSN properly, based on the error message. I haven't used an upsizing wizard against CT but I don't know why it wouldn't work.
That's what is odd is that I actually tested the connection and it worked by following that example above except it won't connect to the db when doing access to sql. |
Edited by - Astralis on 26 December 2004 01:39:29 |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 26 December 2004 : 15:50:14
|
The SQL Server Developer Edition includes a complete server, OLAP, and all the client tools including EM, DTS, Query Analyzer and such. I found it on the shelf at Fry Electronics here in California for under $50.00 US. The license prohibits using the developer edition as a production server.
|
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 26 December 2004 : 16:24:25
|
Using it to store information to transfer wouldn't be as a production server, would it? Can I buy it online?
I'm still stumped, though, on transferring using the upsize wizard. There's not explanation on why it isn't connecting to the server. |
Edited by - Astralis on 26 December 2004 16:27:19 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 26 December 2004 : 17:23:49
|
How many tables do you have? Isn't it an option to define the primary keys using EM?
Sorry if this doesn't help much, and I may be called 'old fashioned', and I think tools like DTS are invaluable, but the way it handles table creation does not satisfy me. Considering that I would advise you a thorough review of the table structure for all of the tables, after the DTS transfer. During that review, you could add the primary key definition and anything else that may be missing.
So my advice, again, is to double check each field in each table, after DTS table creation and fix whatever may be wrong. There are other issues the upsize wizard may not handle adequately, so this is the only foolproof way of doing it. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
Edited by - ruirib on 26 December 2004 17:24:29 |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 26 December 2004 : 17:43:14
|
ruirib,
Okay...that sounds like what I'll do. What objects do you recommend that I look for to manually replace?
I just realized: can I add a primary key if there are already rows? |
Edited by - Astralis on 26 December 2004 17:44:58 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 26 December 2004 : 18:25:11
|
You should look at each field, make sure the type was correctly chosen by DTS (likely it was...), make sure default values are properly defined.
You can define any of the existing fields as a primary key, using EM. You certainly don't need to add the fields, since DTS transferred all your Access fields.
You should be aware that Access autonumber fields need to be set as identity fiels in SQL Server (you can do that by setting the Identity property to Yes, in EM). |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 27 December 2004 : 04:56:44
|
Thanks ruirib.
This is just getting more odd. In EM, I'm able to open and save a table I just created via DTS. But, it won't let me open it up to pull all the records. I can retransfer the data with no problem and the site can open the data that's in there, and I can open up other tables that are in the DB with no problems. When I try to open one of them, it tells me of the unexpected error with a lot of DBLIB info, but nothing specific nor any numbers. Then I click okay and it tells me I lost my connection to the database and if I want to reconnect. Why? What is going on here? |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 27 December 2004 : 10:18:10
|
I e-mailed CrystalTech, my host. We'll see if they can spot the problem. Other odd things like this are happening with my other SQL DBs with them. I don't know...
Anyhow, I can connect to the DB using both QA and EM. I just can't pull up the tables that I transferred via DTS. But, I know the data is in there because I exported it from EM to confirm and it did it with success. |
|
|
|
Astralis
Senior Member
USA
1218 Posts |
Posted - 27 December 2004 : 10:59:27
|
CT claims with certainty that the problem is with my software, and not the webserver or SQL server.
This is the error I'm getting. Does this look familiar?
[MS Design Tools] - ODBC error: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).
[Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation. |
Edited by - Astralis on 27 December 2004 11:19:16 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Topic |
|
Topic Locked
