Unable to use sp_AddRole = Server: Msg 8114, Level 16, State 4, Procedure sp_addrole, Line 0 
Author Message
 Unable to use sp_AddRole = Server: Msg 8114, Level 16, State 4, Procedure sp_addrole, Line 0

SQL Server 2000 SP2 or 3?
Windows 2000 Server - SP1

Problem:

Creating a new ROLE for any database on the server either through the GUI or
by SQL Script will result in this error:

Msg 8114, Level 16, State 4, Server NZ01SQL01, Procedure sp_addrole, Line 0
Error converting data type varchar to int.

Example:

exec sp_addrole 'chris'

Server: Msg 8114, Level 16, State 4, Procedure sp_addrole, Line 0
Error converting data type varchar to int.

As a result of this an attempt to apply Service Pack 3 for SQL Server 2000
failed during the running of 80SP1-Tools.SQL

An error occurred during the execution of Script #1 of 7 which upgrades the
internal tables of the SQL Server.

Error message "Error during the installation of 80sp1-tools.sql"

SQL Server Version prior to Upgrade
    Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
    Nov 19 2001 13:23:50
    Copyright (c) 1988-2000 Microsoft Corporation
    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1)

SQL Server Version after failed Upgrade
    Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
    Dec 17 2002 14:22:05
    Copyright (c) 1988-2003 Microsoft Corporation
    Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1)

Contents of "80SP3-Tools.out" which is generated by the tools that perform
the upgrade to the SQL Server.

Updating database objects, executing 80SP3-TOOLS.SQL

Started at Mar 9 2003 3:29PM
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Configuration option 'allow updates' changed from 0 to 1. Run the
RECONFIGURE statement to install.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Creating procedure sp_MSobjectprivs...
Creating extended stored procedure xp_adsirequest...
Creating procedure sp_ActiveDirectory_Obj...
Creating extended stored procedure xp_GetAdminGroupName...
Creating procedure sp_ActiveDirectory_SCP...
Creating procedure sp_sqlagent_has_server_access...
Creating procedure sp_verify_subsystem...
Creating procedure sp_get_sqlagent_properties...
Creating procedure sp_sqlagent_get_perf_counters...
Creating procedure sp_post_msx_operation
Creating procedure sp_delete_targetserver
Creating procedure sp_msx_enlist...
Creating procedure sp_enlist_tsx
Creating stored procedure sp_enlist_tsx
Creating procedure sp_add_jobstep_internal...
Creating procedure sp_add_jobstep...
Creating procedure sp_update_jobstep...
Creating extended stored procedure xp_sqlagent_msx_account

Creating extended stored procedure xp_SetSQLSecurity
Msg 8114, Level 16, State 4, Server NZ01SQL01, Procedure sp_addrole, Line 0
Error converting data type varchar to int.

Msg 4604, Level 16, State 1, Server NZ01SQL01, Line 1
There is no such user or group 'RepositoryUser'.

Msg 4604, Level 16, State 1, Server NZ01SQL01, Line 1
There is no such user or group 'RepositoryUser'.

and lots more of those last lines....

Can anyone help with this weird problem?

--

Chris Crowe



Thu, 25 Aug 2005 11:13:51 GMT
 Unable to use sp_AddRole = Server: Msg 8114, Level 16, State 4, Procedure sp_addrole, Line 0

I found it - the sp_addRole SP had been modified and had some other code in
it.

Replaced with a normal copy of the sp_addRole and all works well again.

Chris

--


Quote:
> SQL Server 2000 SP2 or 3?
> Windows 2000 Server - SP1

> Problem:

> Creating a new ROLE for any database on the server either through the GUI
or
> by SQL Script will result in this error:

> Msg 8114, Level 16, State 4, Server NZ01SQL01, Procedure sp_addrole, Line
0
> Error converting data type varchar to int.

> Example:

> exec sp_addrole 'chris'

> Server: Msg 8114, Level 16, State 4, Procedure sp_addrole, Line 0
> Error converting data type varchar to int.

> As a result of this an attempt to apply Service Pack 3 for SQL Server 2000
> failed during the running of 80SP1-Tools.SQL

> An error occurred during the execution of Script #1 of 7 which upgrades
the
> internal tables of the SQL Server.

> Error message "Error during the installation of 80sp1-tools.sql"

> SQL Server Version prior to Upgrade
>     Microsoft SQL Server 2000 - 8.00.534 (Intel X86)
>     Nov 19 2001 13:23:50
>     Copyright (c) 1988-2000 Microsoft Corporation
>     Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1)

> SQL Server Version after failed Upgrade
>     Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
>     Dec 17 2002 14:22:05
>     Copyright (c) 1988-2003 Microsoft Corporation
>     Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 1)

> Contents of "80SP3-Tools.out" which is generated by the tools that perform
> the upgrade to the SQL Server.

> Updating database objects, executing 80SP3-TOOLS.SQL

> Started at Mar 9 2003 3:29PM
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Configuration option 'allow updates' changed from 0 to 1. Run the
> RECONFIGURE statement to install.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Creating procedure sp_MSobjectprivs...
> Creating extended stored procedure xp_adsirequest...
> Creating procedure sp_ActiveDirectory_Obj...
> Creating extended stored procedure xp_GetAdminGroupName...
> Creating procedure sp_ActiveDirectory_SCP...
> Creating procedure sp_sqlagent_has_server_access...
> Creating procedure sp_verify_subsystem...
> Creating procedure sp_get_sqlagent_properties...
> Creating procedure sp_sqlagent_get_perf_counters...
> Creating procedure sp_post_msx_operation
> Creating procedure sp_delete_targetserver
> Creating procedure sp_msx_enlist...
> Creating procedure sp_enlist_tsx
> Creating stored procedure sp_enlist_tsx
> Creating procedure sp_add_jobstep_internal...
> Creating procedure sp_add_jobstep...
> Creating procedure sp_update_jobstep...
> Creating extended stored procedure xp_sqlagent_msx_account

> Creating extended stored procedure xp_SetSQLSecurity
> Msg 8114, Level 16, State 4, Server NZ01SQL01, Procedure sp_addrole, Line
0
> Error converting data type varchar to int.

> Msg 4604, Level 16, State 1, Server NZ01SQL01, Line 1
> There is no such user or group 'RepositoryUser'.

> Msg 4604, Level 16, State 1, Server NZ01SQL01, Line 1
> There is no such user or group 'RepositoryUser'.

> and lots more of those last lines....

> Can anyone help with this weird problem?

> --

> Chris Crowe



Thu, 25 Aug 2005 11:25:51 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Server: Msg 8114, Level 16, State 1, Procedure co_AddTrader_Information, Line 0

2. Server: Msg 214, Level 16, State 2, Procedure sp_execute, Line 52

3. Server: Msg 8115, Level 16, State 2, Procedure sp_monitor, Line 94

4. Server: Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 333 The databa

5. Server: Msg 7399, Level 16, State 1, Line 1 error on .mdb as linked server

6. Server: Msg 7399, Level 16, State 1, Line 1 error on .mdb as linked server

7. Server: Msg 4425, Level 16, State 1, Line 1

8. Server: Msg 1776, Level 16, State 1, Line 1

9. Server: Msg 7619, Level 16, State 1, Line 2

10. Server: Msg 7619, Level 16, State 1, Line 1

11. Server: Msg 7354, Level 16, State 1, Line 1

12. Server: Msg 4321, Level 16, State 1, Line 2


 
Powered by phpBB® Forum Software