| Author |
 Topic  |
|
Mr Pink
Junior Member
United Kingdom
387 Posts |
 Posted - 19 May 2008 : 16:18:27
|
I've been playing around with a test forum for the past few days and I'm not getting very far with converting my Access to MSSql.
I do not have SQL Enterprise Server Manager so can't follow the steps listed in this guide.
I do have SQL Server Management Studio Express, DTSWizard and Microsoft SQL Server Migration Assistant for Access.
After trying variations of the above, some of the tables are uploaded to the SQL server but some always come up with errors. FORUM_A_TOPICS and FORUM_FORUM are always empty after trying a conversion.
If anyone knows of a step by step guide for one of the above packages I would be grateful for some help.< |
Martin
Leyland Forum Leyland Lancashire UK |
 |
|
|
pierretopping
Junior Member
United Kingdom
224 Posts |
Posted - 03 January 2009 : 13:09:43
|
Hello All,
I've a small problem I hope you can help with. I'm upgrading from Access to MS-SQL 2000, and I've followed the guide above, but when I enter setup.asp?RC=5, its saying at the top "Database Type: Microsoft Access", and not MS-SQL ? I have changed the config.asp, and I've also checked it connects by doing a ODBC connect from another PC using the news user in the database and it all looks good ?
If I do go ahead and enter the administrator user name and password, it tells me that there were 13 critical errors
Any idea where I have made the mistake ?
< |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
pierretopping
Junior Member
United Kingdom
224 Posts |
Posted - 03 January 2009 : 14:09:48
|
Hi, I think I've got the right string, and if I use the wrong password, it does not connect (so I'm guessing its write..).
I'm using the below....
strConnString = "driver={SQL Server};server=localhost< |
|
|
|
pierretopping
Junior Member
United Kingdom
224 Posts |
Posted - 03 January 2009 : 14:14:09
|
Sorry, you are right :o)
I left the strDBtype = access and not sqlserver
Sorry, you people are great :o)< |
Edited by - pierretopping on 03 January 2009 14:14:19 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
stratacafe
Starting Member
Canada
44 Posts |
Posted - 18 February 2009 : 21:44:58
|
Hi all,
Hopefully I can sort through my remaining issue here =)
I've done all the steps up to running setup.asp to build all the tables. So far so good.
I cannot import my Access database. I've tried to install the DTS tool, no errors, just no install. I've tried to install the "toolkit", but it said I already had everything installed from that package.
My core problem right now is that I don't have an "import" command anywhere to get my MDB into the SQL 2005 Express Management app.
Is there another way I could turn the MDB into maybe a SQL .BAK file so I could "restore" the database, rather than importing it?< |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
|
|
stratacafe
Starting Member
Canada
44 Posts |
Posted - 19 February 2009 : 16:54:16
|
Definitely has some issues to sort out after that, but that Migration assistant did the trick.
Advertise that link, that was beautiful compared to the crap I've been dealing with trying to get the import command into the SQL management tool.
Thank you!< |
|
|
|
stratacafe
Starting Member
Canada
44 Posts |
Posted - 19 February 2009 : 20:25:06
|
I'm having some issues which seem to indicate that some fields which were nullable are no longer.
--
Microsoft OLE DB Provider for SQL Server error '80040e2f'
The INSERT statement conflicted with the CHECK constraint "SSMA_CC$FORUM_MEMBERS_PENDING$M_HOMEPAGE$disallow_zero_length". The conflict occurred in database "michaell_snitzforums", table "snitzadmin.FORUM_MEMBERS_PENDING", column 'M_HOMEPAGE'.
/forum/register.asp, line 599
--
This is an error from the new member registration page.
Is there a list somewhere of what fields should be nullable?
Thanks,
Michael< |
|
|
|
stratacafe
Starting Member
Canada
44 Posts |
Posted - 20 February 2009 : 02:32:43
|
*sigh*
It looks like the migration assistant didn't respect the null status of any field in any table (other than auto ids).
Can I copy the raw SQL code from the same table in a newly created SQL database to the existing database without altering the content of that table?< |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 20 February 2009 : 03:17:20
|
| just remove the constraints, if you can access your db via enterprise manager then just set them to allow null (they should be anyway)< |
|
|
|
NWBA
Starting Member
2 Posts |
Posted - 05 August 2009 : 10:11:35
|
After reading through the various posts regarding migrating from access, I request some confirmation of the steps I took.
I used the SQL Server Migration Assistant to create the MSSQL database.
It added constraints to some of the fields to disallow zero length which are not in the constraints if you let setup create the tables. I deleted these.
Everything is working okay so far in my testing. But this is only local testing with no volume or multiple users.
I am a little confused by reading some posts that indicate that setup needs to create the tables.
Any confirmation would be appreciated that this will work or if there are some mine fields in the future.
Thanks
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 06 August 2009 : 05:42:12
|
The Migration assistant has evolved and I think it doesn't create the same type of errors it did before. However, the Access database doesn't have the indexes that SQL Server requires, so I still think it's wise to create the database before moving the data, in order to avoid future problems. I can't also state beyond doubt that the structure is properly created.
That being the case, we still advise for the table structure be created by the Snitz code. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Topic |
|
Topic Locked
.<