Web Wiz Forums - DB errors, smallint to Int (or Bigint), not workin

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

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.

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

Thanks for the help anyway