| Author |
 Topic Search  Topic Options
|
Joey805 
Groupie
Joined: 24 November 2003
Location: United States
Status: Offline
Points: 92
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Access to SQL Import problems
Posted: 16 December 2003 at 8:15pm |
I read alot of post about upsizing from Access to SQL so I decided to create a test server and give it a shot. After I get everything set up, I open the Access file and try to run the upsizing wizard. I get an error that says "Object variable or With block variable not set" so that ends the day there. I next try and do it through the import process within SQL. I run msSQL_server_setup.asp and it creates the tables and sets everything up. I am now able to get into the forum and navigate/post ect. Now I try and import the data using Import. I select all the tables and it starts its process. The final result being 7 tables successful and 11 failed. I figured because the tables may already be there. With that import I am able to get into the forum and see my data but there are alot of things missing like permissions and such. Overall, it did not import correctly so I try again, this time I dont run msSQL_server_setup.asp. Everything imported successfully this time. However the forum is locked and I cannot unlock it.
Can I get some help here. I did some searching first and did not find my answers.
I appreciate your help,
Joey
|
 |
Joey805
Groupie
Joined: 24 November 2003
Location: United States
Status: Offline
Points: 92
|
Post Options
Thanks(0)
Quote Reply
Posted: 16 December 2003 at 10:48pm |
OK I was able to get a little farther now. I tired using an older version of acccess rather than than the one that came with office 2003. I got it to go through the upsizing wizard. I guess what Im needing now are some steps in what order to do this in. I cant seem to find any writeups on this. These are the things I have tried.
1) manually creating the DB in SQL, and then running msSQL_server_setup.asp, the upsizing using access into this database. I get an error report that 4 tables did not upsize.
2) let the upsizing wizard create its own DB and successfully upsize all the data, then I try to log into it and I get an internal servel error. I then run msSQL_server_setup.asp and get several other errors. Then I am able to log into the DB and everything seems to be running fine. At this point in time, is everything working or should I worry about all the errors generated by msSQL_server_setup.asp such as the following:
|
Error Creating the Table tblCategory (it may already exsist)
Error Creating the Table tblForum (it may already exsist)
Error Creating the Table tblTopic (it may already exsist)
Error Creating the Table tblThread (it may already exsist)
Error Creating the Table tblAuthor (it may already exsist)
Error Creating the Table tblPMMessage (it may already exsist)
Error Creating the Table tblBuddyList (it may already exsist)
Error Creating the Table tblActiveUser (it may already exsist)
Error Creating the Table tblConfiguration (it may already exsist)
Error Creating the Table tblDateTimeFormat (it may already exsist)
Error Creating the Table tblGroup (it may already exsist)
Error Creating the Table tblPoll (it may already exsist)
Error Creating the Table tblPollChoice (it may already exsist)
Error Creating the Table tblEmailNotify (it may already exsist)
Error Creating the Table tblPermissions (it may already exsist)
Error Creating the Table tblGuestName (it may already exsist)
Error Creating the Table tblSmut (it may already exsist)
Error Creating the Table tblBanList (it may already exsist)
Error Creating one or more Indexs (it may already exsist)
Error Creating the Relationship between the tblAuthor and tblGroup tables (it may already exsist)
Error Creating the Relationship between the tblForum and tblCategory tables (it may already exsist)
Error Creating the Relationship between the tblPollChoice and tblPoll tables (it may already exsist)
Error Creating the Relationship between the tblBuddyList and tblAuthor tables (it may already exsist)
Error Creating the Relationship between the tblEmailNotify and tblAuthor tables (it may already exsist)
Error Creating the Relationship between the tblPMMessage and tblAuthor tables (it may already exsist)
Error Creating the Relationship between the tblPermissions and tblForum tables (it may already exsist)
Error Creating the Relationship between the tblTopic and tblForum tables (it may already exsist)
Error Creating the Relationship between the tblGuestName and tblThread tables (it may already exsist)
Error Creating the Relationship between the tblThread, tblTopic and tblAuthor tables (it may already exsist)
Error entering default values in the Table tblAuthor
Error entering default values in the Table tblDateTimeFormat
Error Creating the Stored Procedure wwfSpSelectConfiguration (it may already exsist)
Error Creating the Stored Procedure wwfSpChkUserID (it may already exsist)
Error Creating the Stored Procedure wwfSpCategoryAll (it may already exsist)
Error Creating the Stored Procedure wwfSpForumsAllWhereCatIs (it may already exsist)
Error Creating the Stored Procedure wwfSpForumTopicCount (it may already exsist)
Error Creating the Stored Procedure wwfSpLastForumPostEntry (it may already exsist)
Error Creating the Stored Procedure wwfSpModeratorGroup (it may already exsist)
Error Creating the Stored Procedure wwfSpModeratorGroup (it may already exsist)
If someone would just walk me through the steps so I can get around these errors, that would be great.
Thanks,
Joey
Error Creating the Stored Procedure wwfSpForumPermissions (it may already exsist)
Error Creating the Stored Procedure wwfSpForumThreadCount (it may already exsist)
Error Creating the Stored Procedure wwfSpUpdateLasVisit (it may already exsist)
Error Creating the Stored Procedure wwfSpAuthorDesc (it may already exsist)
Error Creating the Stored Procedure wwfSpTimeAndDateSettings (it may already exsist)
Error Creating the Stored Procedure wwfSpCountOfPMs (it may already exsist)
Error Creating the Stored Procedure wwfSpDateOfLastUnReadPM (it may already exsist)
Error Creating the Stored Procedure wwfSpActiveUsersWhereIPis (it may already exsist)
Error Creating the Stored Procedure wwfSpAddNewActiveUser (it may already exsist)
Error Creating the Stored Procedure wwfSpUpdateActiveUser (it may already exsist)
Error Creating the Stored Procedure wwfSpDeleteActiveUser (it may already exsist)
Error Creating the Stored Procedure wwfSpDeleteActiveUserDoubleEntry (it may already exsist)
Error Creating the Stored Procedure wwfSpDeleteActiveUserDoubleEntry (it may already exsist)
Error Creating the Stored Procedure wwfSpUpdateViewPostCount (it may already exsist)
Error Creating the Stored Procedure wwfSpForumsAllWhereForumIs (it may already exsist)
Error Creating the Stored Procedure wwfSpTopicDetialsInTheLastXX (it may already exsist)
Error Creating the Stored Procedure wwfSpTopicDetialsAll (it may already exsist)
Error Creating the Stored Procedure wwfSpThreadDetails (it may already exsist)
Error Creating the Stored Procedure wwfSpPollDetails (it may already exsist)
Error Creating the Stored Procedure wwfSpAuthorLastPostDate (it may already exsist)
Error Creating the Stored Procedure wwfSpCountOfActiveUsers (it may already exsist)
Error Creating the Stored Procedure wwfSpCountOfActiveGuests (it may already exsist)
Error Creating the Stored Procedure wwfSpActiveToipcs (it may already exsist)
Error Creating the Stored Procedure wwfSpTopicEmailNotify (it may already exsist)
Error Creating the Stored Procedure wwfSpForumEmailNotify (it may already exsist)
Error Creating the Stored Procedure wwfSpBannedIPs (it may already exsist)
Error Creating the Stored Procedure wwfSpAuthorDetails (it may already exsist)
Error Creating the Stored Procedure wwfSpAuthorLastPostDate (it may already exsist)
Error Creating the Stored Procedure wwfSpDBinfo (it may already exsist)
SQL Server database is set up, but with Error!
|
|
|
Joey805
Groupie
Joined: 24 November 2003
Location: United States
Status: Offline
Points: 92
|
Post Options
Thanks(0)
Quote Reply
Posted: 17 December 2003 at 4:16pm |
|
Nobody can help me with this? Is there at least a post or guide I can follow to upgrade from access to SQL? There has got to me some documentation somewhere.
|
|
steve
Groupie
Joined: 26 June 2002
Location: United States
Status: Offline
Points: 126
|
Post Options
Thanks(0)
Quote Reply
Posted: 22 October 2004 at 10:09pm |
I'm in the same boat as you right now Joey. I'm guessing you're
on Jodohost. The conversion part isn't too tough, but the MS SQL
Manager they give you doesn't seem to function properly. I sent
in a ticket to see if they can correct it.
I'm getting errors like these:
Microsoft OLE DB Provider for SQL Server error '80040e09'
EXECUTE permission denied on object 'wwfSpChkUserID', database 'xyz_db', owner 'dbo'.
/mypathtoforum/common.asp, line 499
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 23 October 2004 at 2:26pm |
|
If you are using Jodohost you may need to get tech support to change
someof the permissions on the server to be able to run Web Wiz Forums.
|
|
|
|
steve
Groupie
Joined: 26 June 2002
Location: United States
Status: Offline
Points: 126
|
Post Options
Thanks(0)
Quote Reply
Posted: 23 October 2004 at 5:06pm |
I've been going back and forth with them and they tell me the problem
should be corrected with the MS SQL manager and when I go in to try,
it's not. 
I'll just have to start forwarding my error messages to them and see if
they can fix it manually. It's frustrating not having the tools
to do this myself...
Here's a question I'd be surprised if you could answer, but do you have
any ideas on what setting should be changed from an access to SQL
conversion with JodoHost? I don't want to go through a 12-24 hour
waiting period with each error if I can help it.
Thanks Borg.
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 23 October 2004 at 5:19pm |
|
All you should need to do is upsize the Access database making sure all the default values etc. are moved across.
Then run the MS SQL server setup file, ignoring the errors about tables
etc. as they are already created. This will then create the stored
procedures for you on the SQL Server.
The only problem you may encounter is that the setup file will put in
default values, like extra admin and guest accounts etc. which you will
need to remove.
You could try editing the MS SQL Server setup file to remove all the
parts that create the tables etc. and just leave in the part that
creates the stored procedures.
|
|
|
|
steve
Groupie
Joined: 26 June 2002
Location: United States
Status: Offline
Points: 126
|
Post Options
Thanks(0)
Quote Reply
Posted: 25 October 2004 at 6:55pm |
|
What a nightmare, I'm going back and forth with the host still on
getting all of my permissions set right. I can basically do
anything but write to the database now (no posting or registrations),
but I've noticed it's gotten very very slow. I have a 36 MB
Database with 20,000 posts approx 500 users and 20-30 concurrent most
of the time. My page loads on Access were usually 1 second
or under, now when using the MS SQL db it's over 6 seconds. My db
log file is close to 60 megs (bringing my db size up to almost 100
mb). Is that normal?
Every part of it is much much slower. I'm hosted with JodoHost
and it is still not totally functioning correctly, so I'm giving it the
benefit of the doubt until it's totally working.
Any advice other than going back to Access?
|
|
