Arithmetic Overflow error when comparing

I am aware of the arithmetic overflow error that is received when you have more than 32768 users in a database. So before comparing a scripts folder with a SQL 2008 R2 database, I removed most of the users from the database and now only have 57 users. Yet I get the following error:

Arithmetic overflow error for data type smallint, value = 33528

What is this trying to indicate? The log indicates this is being encountered during the 'Populate Permissions'. It would seem that the tool is not recognizing that the users were dropped. ( I have confirmed in sys.database_principals that they are truly gone).

Any ideas?

Thanks!
Tina

The log states:
08:36:45.947|Info |SQL Compare Engine |11 |Populate Permissions start.
08:36:46.072|Error |SQL Compare UI |1 |Exception in progress dialog
System.Data.SqlClient.SqlException: Arithmetic overflow error for data type smallint, value = 33528.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.HasMoreRows()
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
at #8rg.#Ysg.#4S()
at #8rg.#Ssg.#dmh()
at #8rg.#Ssg.#amh()
at #8rg.#Ssg.#vl(DBPopulationOptions popOptions)
at RedGate.SQLCompare.Engine.Database.Register(ConnectionProperties connectionProperties, Options options)
at #s7G.#w9G.#DaH(Database database, LiveDatabaseSource dataSource)
at #s7G.#w9G.#DaH(Database database, DataSource dataSource)
at #s7G.#w9G.#8bH()
at RedGate.Shared.Utils.ProgressTasks.ProgressTask.a()
at RedGate.Shared.Controls.ProgressDialogEx.e()

SS_DBA April 08, 2011 11:46

Comments

1 comment

This has been resolved through a support call.

Basically, the issue occurs when SQL Compare looks to populate Permissions. When it does this, it runs this query:
```
SELECT *
FROM sys.database_permissions AS sp WITH &#40;NOLOCK&#41;
INNER JOIN sys.sysusers su WITH &#40;NOLOCK&#41; ON su.uid=sp.grantee_principal_id
```
Where sys.sysusers.uid is a smallint (with a max of 32,767) and sys.database_principals.grantee_principal_id is an int (2,147,483,647). Sys.sysusers.uid is a sql server view and the fix for this problem would be for SQL Compare to get the information elsewhere. This has been recorded in our bug tracking software as reference: SC-4529.

In this case, there were users still in the database with a uid greater than 32767 which needed to be removed. Once this was done, SQL Compare worked a treat.

Pete
peter.peart April 21, 2011 14:30

0

Add comment

Please sign in to leave a comment.

How can we help you today?

Arithmetic Overflow error when comparing

Comments

Add comment