Print Page | Close Window

DB errors, smallint to Int (or Bigint), not workin

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=29090
Printed Date: 01 April 2026 at 4:04pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com


Topic: DB errors, smallint to Int (or Bigint), not workin
Posted By: MaximeRoy77
Subject: DB errors, smallint to Int (or Bigint), not workin
Date Posted: 29 January 2011 at 2:37pm
Hi,

I get theses errors now:

  • Posts count does not work anymore.

  • I get this error when I run the ADMIN / RE-Sync posts count
    Arithmetic overflow error for data type smallint, value = 32919.

From my research.... this lead to:

My forum DB is getting bigger and now I have to change the datatype of this field of
tblForum.Last_post_author_ID (smallint) to a INT or BIGINT.

But when I try to change the datatype of the tblForum.Last_post_author_ID to INT or BIGINT I get the error message on the screenshot I attached.



 How to do it ?

Thanks






Replies:
Posted By: MaximeRoy77
Date Posted: 29 January 2011 at 2:41pm
Tried to do it manually 

ALTER TABLE tblForum
ALTER COLUMN Last_post_author_ID int


But, get this... this explains the errors above (screenshot)


Msg 5074, Level 16, State 1, Line 1
The object 'DF__tblForum__Last_p__18F6A22A' is dependent on column 'Last_post_author_ID'.
Msg 5074, Level 16, State 1, Line 1
The index 'Last_post_author_ID' is dependent on column 'Last_post_author_ID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Last_post_author_ID failed because one or more objects access this column.



????


Posted By: MaximeRoy77
Date Posted: 29 January 2011 at 2:48pm
Does dropping and re-creating the constraint and index would work ?

I mean dropping them, making the datatype change and then re-creating them ?


Posted By: MaximeRoy77
Date Posted: 29 January 2011 at 8:23pm
Option above worked. The ADMIN Force Re-Sync Post count, etc give no errors but the number of posts of members have not re-calculated. How to ?


Posted By: MaximeRoy77
Date Posted: 30 January 2011 at 1:41pm
I managed to fix it by changing to INT the 

tblForum.Last_post_author_ID (smallint) to a (int) datatype

This need to be fixed in the upcoming version or my forum won't work in the future release.

Thanks


Posted By: MaximeRoy77
Date Posted: 30 January 2011 at 9:03pm
I had to update the posts count manually for member ID 32919 and above...


Posted By: WebWiz-Bruce
Date Posted: 01 February 2011 at 1:47pm
The tblTopic.Last_post_author_ID should be set to the INT data type. I have checked the database setup files and it is indeed set to the INT Data Type. 

It sounds like either the database was not setup correctly or was modified to change it to a SMALLINT Data Type.

As to your error when trying to change the value in SQL Server Studio Manager this is caused by the default settings in SQL Server Studio Manager. So that you can make these types of changes to tables in SQL Server Studio Manager go to:-

Tools -> Options -> Designers 

Where you can uncheck the option to 'Prevent saving changes that require table re-creation'


-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting


Posted By: MaximeRoy77
Date Posted: 01 February 2011 at 2:08pm
No reason I would have change the datatype to SMALLINT Ermm

Here's my theory:
I run the forums since 2004 version 7? And maybe it's the upgrade script that did not made the change on my DB because of that setting "Prevent saving changes that require table re-creation" .

Just a tough ...




Posted By: WebWiz-Bruce
Date Posted: 01 February 2011 at 2:24pm
Already thought of that the tblTopic.Last_post_author_ID was added as new for version 8 beta 1.

Database changes version 8 beta 1
---------------------------------

Changes to tblForum
New fields:-
1. Sub_ID - data type = interger - default value = 0
2. Display - data type = interger
3.  - data type = interger - default value 1
4. Last_post_date - data type = date/time - default value = now()/GetDate()

I have checked both the code that creates a new database and also that which upgrades from version 7.x and both create the data type as an INT data type.


-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting


Posted By: MaximeRoy77
Date Posted: 01 February 2011 at 2:32pm
Wacko

Thanks for the help anyway



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net