An error has occurred while writing to the databas

Post Reply

Page <123 >

Author	Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic
jpullam2 Members Profile Find Members Posts Groupie Joined: 29 September 2004 Location: Canada Status: Offline Points: 52	Post Options Post Reply Quote jpullam2 Report Post Thanks(0) Quote Reply Posted: 04 January 2016 at 2:38pm
	Yes. I just converted from Access and 7.9. Everything looks good otherwise, although there were a few glitches that had to be resolved. I switched it to detailed error reporting which should help.

WebWiz-Bruce Members Profile Find Members Posts Admin Group Web Wiz Developer Joined: 03 September 2001 Location: Bournemouth Status: Offline Points: 9844	Post Options Post Reply Quote WebWiz-Bruce Report Post Thanks(0) Quote Reply Posted: 04 January 2016 at 2:54pm
	Shame you did not mention this in your original post as this would have pointed straight away to the issue. The errors that you are reporting are highly unusual and should not occur. It is therefore likely that the migration from Access to SQL Server did not work correctly and you have ended up with a database that is causing your forum to be unstable with these errors. Access and SQL server handle dates in different ways and as the tables giving the errors are those that also rely on the date/time for updates I suspect that these fields have not be migrated correctly from Access. I would advise to go back to your Access database and try the migration again. The Microsoft SQL Server Migration Assistant (SSMA) does a very good job of migrating the Web Wiz Forums Access database to SQL Server using the default settings, although you will still need to do some tweaking to the database after the migration.
	Web Wiz Forums Hosting ASP.NET Web Hosting

WebWiz-Bruce Members Profile Find Members Posts Admin Group Web Wiz Developer Joined: 03 September 2001 Location: Bournemouth Status: Offline Points: 9844	Post Options Post Reply Quote WebWiz-Bruce Report Post Thanks(0) Quote Reply Posted: 04 January 2016 at 3:00pm
	You may also be interested in our Web Wiz Forums hosting packages; https://www.webwiz.net/web-wiz-forums/forum-hosting.htm In the migration service we offer with the hosted service our own developers can take your Access database and convert it to SQL Server 2014, fix any errors caused by the migration, and have your forum running stably on our servers without the issue you are presently facing.
	Web Wiz Forums Hosting ASP.NET Web Hosting

jpullam2 Members Profile Find Members Posts Groupie Joined: 29 September 2004 Location: Canada Status: Offline Points: 52	Post Options Post Reply Quote jpullam2 Report Post Thanks(0) Quote Reply Posted: 04 January 2016 at 3:34pm
	I did use the Migration Assistant. Could you elaborate on the tweaking I might need to do?

WebWiz-Bruce Members Profile Find Members Posts Admin Group Web Wiz Developer Joined: 03 September 2001 Location: Bournemouth Status: Offline Points: 9844	Post Options Post Reply Quote WebWiz-Bruce Report Post Thanks(0) Quote Reply Posted: 05 January 2016 at 9:22am
	It does take a bit of an expert eye to do the tweaking, but the simplest way for you to do this is create an original Web Wiz Forums SQL Server Database and compare this against the migrated database that you have created.
	Web Wiz Forums Hosting ASP.NET Web Hosting

jpullam2 Members Profile Find Members Posts Groupie Joined: 29 September 2004 Location: Canada Status: Offline Points: 52	Post Options Post Reply Quote jpullam2 Report Post Thanks(0) Quote Reply Posted: 07 January 2016 at 3:15pm
	My asp is a little rusty but I managed to track this down to the following... There is a SQL update statement in functions_login.asp that after substitution becomes this: UPDATE tblAuthor WITH (ROWLOCK) SET tblAuthor.Last_visit = '20160106 18:18:50' WHERE tblAuthor.Author_ID = 1; When I execute this statement using SSMS, it fails the same way as the app so I am pretty sure I have nailed it. The field tblAuthor.Last_visit is defined in the SQL database as datetime2 which I am not familiar with. I normally code fields as datetime. I assume that the Microsoft conversion utility decided to make it datetime2 and that may be the problem. When I added a field defined as datetime and applied the same query against it, it ran fine. So I am thinking of just deleting that column and recreating it as datetime. Can you advise if there is anything wrong with that tweak as far as you know?

WebWiz-Bruce Members Profile Find Members Posts Admin Group Web Wiz Developer Joined: 03 September 2001 Location: Bournemouth Status: Offline Points: 9844	Post Options Post Reply Quote WebWiz-Bruce Report Post Thanks(0) Quote Reply Posted: 08 January 2016 at 2:56pm
	Rather than deleting the field go through the database and any fields with the data type datetime2 change to datetime data type.
	Web Wiz Forums Hosting ASP.NET Web Hosting

jpullam2 Members Profile Find Members Posts Groupie Joined: 29 September 2004 Location: Canada Status: Offline Points: 52	Post Options Post Reply Quote jpullam2 Report Post Thanks(0) Quote Reply Posted: 11 January 2016 at 9:34pm
	It's been 3 days without a problem so I wanted to put some closure to this issue and provide a bit of feedback on some of the comments made, in case others may find this posting because of similar issues. 1 - Was the problem the datetime2 specification versus datetime? I no longer think that was it. I successfully tried the same SQL query out on a datetime field in that table and it failed too. 2 - Change the type of all datetime fields to datetime2. You can't change the type because SSMS says you have to delete and add a new field. Fortunately I didn't need to do that. 3 - How did I fix it? My hoster suggested moving the database to SQL 2014 from SQL 2008, so he did an export and import and I changed the settings in the file database_settings.asp, From that point on, no errors. So maybe it was an issue converting the Access database which has been fixed in SQL 2014. Who knows?

Post Reply	Page <123 >

Forum Jump

Forum Permissions View Drop Down

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum