What are the challenges you face when working across database platforms? Take the survey

Arithmetic Overflow error when comparing

edited April 21, 2011 2:30PM in SQL Compare Previous Versions
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?


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()


  • Options
    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 (NOLOCK)
    INNER JOIN sys.sysusers su WITH (NOLOCK) 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.

    Peter Peart
    Red Gate Software Ltd
    +44 (0)870 160 0037 ext. 8569
    1 866 RED GATE ext. 8569
Sign In or Register to comment.