SQL Server problem
Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=15188
Printed Date: 13 April 2026 at 1:46am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: SQL Server problem
Posted By: imhotep
Subject: SQL Server problem
Date Posted: 22 May 2005 at 3:23pm
|
I recently upgraded from Access to SQL Server and when I try to update or add information, I get the following error message,
ADODB.Recordset error '800a0cb3'
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
/forum2/forum/post_message.asp, line 746
- All of my tables and post are there and are in sync
- All of the tables are owned by 'dbo'
- I upgraded using the Access Upsize Wizard
- I have already visited the particular forum dedicated to this subject above, but none of their solutions are working for me.
Can anyone help me out here?
-------------
Man Know Thyself
|
Replies:
Posted By: dj air
Date Posted: 22 May 2005 at 3:26pm
im not sure but i think i had a simular problem once when doing the upgrade for a client ,
but im not to sure on the spacifics but the best way to do so (i found
it to work) is to upsize to a local SQL server then use DTS/Enterprise
Manager DTS
and sending the tables in correct order. take a look at the main topic on the sequance i used on one i did the other day.
its the only way ive managed to do so.
the other way is to upsize to local server and then take a back up, and restore on the webserver
|
Posted By: imhotep
Date Posted: 22 May 2005 at 3:32pm
|
I upsized from Access to a local Machine version of SQL Server. Then I transferred that information using the third option in the import process -- Copy objects and data between SQL Server databases --
When I did that, it said complete, but not all objects were transferred. What is the "order" (step by step) in which I am supposed to upload the content? Do I do one table at a time? The method I described is the only way the information would upload, but I still can't update.
-------------
Man Know Thyself
|
Posted By: imhotep
Date Posted: 22 May 2005 at 4:52pm
|
I found this article on Upsizing from Access to SQL Server. It is for an older database but I wonder if the same rules apply. Here is the link http://www.windowsitpro.com/Article/ArticleID/6133/6133.html?Ad=1 - http://www.windowsitpro.com/Article/ArticleID/6133/6133.html?Ad=1
Can anyone tell me if this ok?
I would personally like to know how to determine the dependency order for the access tables. It says to upload in depency order but I am confused as to how I should approach this.
-------------
Man Know Thyself
|
Posted By: dj air
Date Posted: 22 May 2005 at 5:08pm
the order for the forum that i used was
- forum categories table
- forum forum table
- forum author table
- forum topics table
- forum threads table
- then i think it was forum Private message table
- then i sent the other tables all in one go
the first 5 i did one by one. then i sent the others all together.
i had amended tables within the tranfer (not created or anything).. you do this by clicking tranform within the dialog box.
|
Posted By: imhotep
Date Posted: 22 May 2005 at 6:11pm
|
Should I use the upsize wizard to create the tables and relationships, then use the SQL Server's Import tool to upload the data?
-------------
Man Know Thyself
|
Posted By: dj air
Date Posted: 22 May 2005 at 6:20pm
|
i used the upsize wizard within MS Access 2003 . then i think just
uploaded straight from MSSQL server to MSSQL server without adding
first.. i think...
|
Posted By: imhotep
Date Posted: 22 May 2005 at 6:29pm
|
I am using office XP. Is there a significant difference in how this is carried out between versions? I know I am asking far too many questions, I just have been trying to get this right all weekend.
So when we are talking about uploading one table at a time, this is in SQL Server right? I mean, from SQL Server to SQL Server?
- Should we load the table structure and data all together from Microsoft Access to the local MSSQL Server and then load each table individually?
- Do we just upload the structure and relationships into the local MSSQL Server Database and then from there import into the server on the host computer AND THEN import the data seperately and do the process for each table individually
-------------
Man Know Thyself
|
Posted By: dj air
Date Posted: 22 May 2005 at 7:05pm
i have tried from XP before and i think there was a bug because i could
never get it to work from XP (Access XP trhat is).. but have with 2003
you need to upsize to one server then it shouold be ok .. if its inhouse..
if you upsize the hosting computer.
else you may have to upsize to one computer then the other... i think you can just send table by table creating as you go.
one thing actually to check is what the table owner will be of the new
tranferred tables.. within DTS check the tranform button and see what
the desitnation Creator is ,, it should be DBO.. or what ever you are
logged in as. DBo is best
|
Posted By: imhotep
Date Posted: 22 May 2005 at 11:30pm
|
Everytime I try to import each table individually, I get this screen
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Unspecified Error
Connection failure
Context: Error Calling GetRowSet to get DBSCHEMA_PROVIDER_TYPES schema info. Your provider does not support all the schema rowsets rewuired by DTS
****************************
The only way i can get the information into the database is to choose the option "Copy objects and data between SQL Servers." If I try the first option, "Copy tables and views from the source database," I get connection errors like the one above and only 1 to 3 tables will create in the database. I can't choose which tables to upload first. I have to do them all at once in order to get them up there. But we are back at the same problem I had at the beginning, I can't update tables. What am I doing wrong?
-------------
Man Know Thyself
|
Posted By: imhotep
Date Posted: 23 May 2005 at 12:26am
|
I forgot to mention also that when I finish uploading contents using the third method mentioned earlier, it gives me this message:
"Failed to copy objects from Microsoft SQL Server to Microsoft SQL Server"
What objects weren't carried over? How can this be resolved?
-------------
Man Know Thyself
|
Posted By: dj air
Date Posted: 23 May 2005 at 6:09am
after upsizing the database, i then think i used the third option.
after selecting the 3rd option i am presented with a list of tables within the copy from SQL DB,
you should be able to select/deselect what ones are sent. and also edit the destination details.. like the db owner.
the send them one by one in the order on the other page.,
the error i was getting when tranferring from one SQL server to another
was about the threads table, you need to make sure all tables are owned
by DBO, and that the threads table ise sent after the topics table. its
because of the FAK's
|
Posted By: imhotep
Date Posted: 24 May 2005 at 6:55pm
|
I am getting this error message when trying to upload the tblThreads table from SQL Server to SQL Server
Error for Destination row number 1. Errors encountered so far for this task: 1
String data length mismatch
[DBNETLIB][Connection write (send()).] General network error. Check your network documentation
Any ideas of what could be the problem? I actually installed Office 2003 this time and applied all updates.
-------------
Man Know Thyself
|
Posted By: dj air
Date Posted: 24 May 2005 at 7:09pm
look at my last post within the main thread on upsizing, i give a
description on the process using Access 2003 and 2 SQL server and with
just oine,
that error ussually means that it was taking to long to send the data
over.if you can the best way by the looks of it is to take a .bak from
the old server and restore it on the new server.
|
Posted By: imhotep
Date Posted: 24 May 2005 at 11:38pm
|
I have tried every method suggested to me and I still can't update any tables. Is is something in the relationships or something that is being changed? I don't know enough to recognize any changes but I am still getting this message:
ADODB.Recordset error '800a0cb3'
Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.
/forum2/forum/post_message.asp, line 746
Why isn't this working. I will give my database up to see if someone else has the same problem with the database.
-------------
Man Know Thyself
|
Posted By: dj air
Date Posted: 25 May 2005 at 7:42am
ok ,
do the following
upsize the access Database to a SQL server ( you can try upsizing to the actual host ).
and see if that works.
OR if you have a local server try upsizing to that.
OR if you have a local server and a remote server upsize to your local
server then take a .bak file (back up) and restore on the remote server
(web server)
or you can try a tranfer from one server to another by creating a
default version on the remote server (web server ) then import from
your local server in the below order,
bring in part in brackets is what type of tranfer to do on the new server, bring in these one at a time
- category table (ammend table settings)
- forums (amend)
- Author table (delete existing details)
- Prviate message table (amend)
- Topics (amend)
- threads (amend)
- Poll(amend)
- PollChoices (amend)
- EmailNotify(amend)
and then bring in the other tables apart from the configuration and time settings tbles as they can be configured
also when you select the tables to import make sure (MAKE SURE) that the 2nd to last [] is dbo
so you have the first part then on the end it needs top be [dbo][table name]
table name is the name of the table you are tranfrring (this part is on the section after selecting import tables and views.
select the tables 1 by 1 in the above order making sure about the dbo then run the tranfer.
|
Posted By: imhotep
Date Posted: 25 May 2005 at 10:01am
|
A few things
- Trying to restore the database is a no go. Says I don't have permissions to do so
- I have upsized to a local server and am trying to transfer to the host.
- The first table transfers over fine, but when I get to the "forum" table, I get the following error -
"Error for Destination row number 27. Errors encountered so far for this task: 1
String data length mismatch
[DBNETLIB][ConnectionRead (send()).] General network error. Check your network documentation"
If I was to give you the access database, would you be able to see if there is something wrong with it? Also if I set up something on my host's SQL Server for you to look at, would you be able to determine anything from that? I don't know what the problem is and I am following your advice verbatum.
-------------
Man Know Thyself
|
Posted By: dj air
Date Posted: 25 May 2005 at 10:10am
|
contact me over MSN, and ill see what i can do. my MSN is in my proflei
|
Posted By: imhotep
Date Posted: 25 May 2005 at 11:14am
ive removed your details to stop spammers/ spiders.. ill do the up grade soon.. and ill send you an email when its done
mailto:diregardthispart_info@mochasuite.com -
-------------
Man Know Thyself
|
Posted By: dj air
Date Posted: 25 May 2005 at 1:27pm
i have nearly downoaded the database was interupted when i left it so is going to take a couple minutes.
then i will upsize the database to my local then tranfer to yours
|
Posted By: dj air
Date Posted: 25 May 2005 at 2:30pm
|
hi, right we have had a few problems but i think they are being
tranferred ok now.. i give a ETA of about 1/2 - 1 hour, ill reply when
finished
|
Posted By: dj air
Date Posted: 25 May 2005 at 3:47pm
the all tables are over apart from the poll, poll choice and threads table . which is doing now.
i had a few problems but nothing major, i think it is almost done
|
Posted By: imhotep
Date Posted: 25 May 2005 at 4:19pm
It seems like it's working. What exactly did you do?
-------------
Man Know Thyself
|
Posted By: dj air
Date Posted: 25 May 2005 at 4:20pm
upgrade is complete all details are tranferred and ive tested a new user and posted a new topic.
you can delete both the topic and aaron test user account.
|
Posted By: dj air
Date Posted: 25 May 2005 at 4:22pm
hi, just seen your message
well i couldn't do the upsize to my server then tranfer to yours.
so i used the access database and made a dns for it.
then tranferred the tables one by one into a fresh copy that i
created.. so i imported the data from the access database into the
tables of a bklank WWF SQL database (default set up).. and imported
thoser records.
it all seems ok now
|
|