SQL SERVER – 2005 – Fix : Error : Msg 7411, Level 16, State 1 Server is not configured for RPC

Error : Msg 7411, Level 16, State 1 Server is not configured for RPC

This was annoying error which was fixed by Jr. DBA, whom I am personally training at my organization. I think he is going to be great programmer. He worked in my organization for more than 8 months. I finally have decided to coach him myself. When I encountered this error, I gave him task to figure this out himself. I absolutely gave him no direction and very few min to fix this problem. As you might have guessed without using internet help (as there is no help online available for this error) he found the solution.

Go to ServerInstance and follow next two diagrams. They are self-explanatory. Turn on the RPC from False to True.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

29 thoughts on “SQL SERVER – 2005 – Fix : Error : Msg 7411, Level 16, State 1 Server is not configured for RPC

  1. thanks dev
    I have fix an error “is not configured for RPC”, by chaning ( on local server), linked server configuration to remote server setting “RPCout ” as True.

    can we create tables and stored procedures from local server?

    and also insert values from local server?

    please tell me any way to do these tasks .

    thank u
    vego reddy

  2. you have given the example of linked server.

    Data access issue also arrises if i issue the command

    Sp_dropserver ‘Servername’,’Droplogins’
    it will drop the server from the catalog sys.servers

    whose data can be view from this

    Select * from sys.servers

    and after dropping if i add it again by

    sp_addserver ‘Servername’
    Then it will add the server with the specified Servername but the Data Access is not allowable as there is a “0″ Zero in the field “is_Data_Access_enabled”, which we can verify from after executing
    Select * from sys.servers

    So plz help as how to enable Data Access for Local Servers NOT linked Servers.

  3. continuation of the previous message
    I am getting this error.

    Msg 7411, Level 16, State 1, Line 1
    Server ‘vmbztk-prod’ is not configured for DATA ACCESS.

    VMBZTK-PROD is my server name

  4. continuation of the above message

    i have executed this,
    sp_serveroption @server = ‘vmbztk-prod’
    ,@optname = ‘DATA ACCESS’
    ,@optvalue = ‘TRUE’

    and it has solved the problem in a way, that all the procedure that i executed has make the local server as the linked server and make me access the data,

    there is the entry of the Local Server in the Sys.servers, and they have “is_data_Access_enabled” has a “0″ Zero in it, and the server can make queries from the full qualified tablename.

    means like this
    Select * from [vmbztk-prod].databasename.dbo.tablename

    but what i have done, it has make the entry in sys.servers catalog like a linked server,

    what should i do to properly solve the problem

  5. Continuation and final solution to the Previous Problems

    Dear All,

    What ONE has to do after changing the Server name is that

    issue this SQL Statement to verify the name

    select @@servername <== This will return the oldName
    SELECT CONVERT(char(20), SERVERPROPERTY(‘servername’)); <== This will return the NewName <== Because this shows the

    Networkname of the computer, and it is changed now.

    Select * from sys.servers
    it will give the list of Servers that are linked to the current servers via Network or Locally.

    Now issue this command

    sp_dropserver ‘Oldservername’
    this will remove the oldservername

    sp_addserver ‘Newservername’ , ‘Local’
    this will add the newservername to the sysserver catalog.

    now when we issue this again

    select @@servername <== This will return the oldName
    SELECT CONVERT(char(20), SERVERPROPERTY(‘servername’)); <== This will return the NewName
    it will again showing the old and new name respectively.

    now what one has to do is simply restart the sql service, it will reconfigure it with the new name.

    select @@servername
    SELECT CONVERT(char(20), SERVERPROPERTY(‘servername’));

    Now both these statements shows the Same New Name

  6. I have RPC problem. The above mentioned options in linked servers to make true is not found workable for the rpc connection . Is there a need to install msdtc in another m/c.

  7. I have run into your site many times when searching for answers to a variety of SQL Server problems. Each time I come here, I find a short, concise, easy to understand article that tells me exactly what I need to know. Thanks for providing such a great site.

  8. For SQL 2000, options are under Security, Linked Servers and right click for Properties.

    RPC and RPC Out check boxes are then shown under Options tab.

  9. I still get this error when trying to mark ‘RPC’ and ‘RPC out’ fields as ‘True’:
    TITLE: Microsoft SQL Server Management Studio
    ——————————

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————
    ADDITIONAL INFORMATION:

    Ad hoc updates to system catalogs are not allowed. (Microsoft SQL Server, Error: 259)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=259&LinkId=20476

    ——————————
    BUTTONS:

    OK
    ——————————

    • USE [master]
      GO
      EXEC master.dbo.sp_serveroption @server=N’LinkedServerName’, @optname=N’rpc’, @optvalue=N’true’
      GO

      This will work. There is an extraneous statement I eliminated that the gui tries to run to update the system table directly.

  10. hi,

    i already set to true the value of RPC and RPC out as well as Data access but still im getting same error. pls help. Im using sql2008r2 transactional replication

  11. when i pass 2 output parameter to a function from linked server I am getting this error:
    for 1 out parameter its working fine but when i use two output parameters it gives this error:

    “Msg 7215, Level 17, State 1, Procedure Function_Name, Line 28 Could not execute statement on remote server ‘linkserver_name’.”

    Here is the query:

    DECLARE @RET INT

    EXECUTE (‘BEGIN ? := package_name.function_name(?,?,?,?,?,?,?,?,?,?,?,?,?); END;’, @RET OUTPUT
    , @USER_NAME, @FIRST_NAME, @MIDDLE_NAME, @LAST_NAME, @DATE_OF_BIRTH
    , @MOTHER_MAIDEN_NAME, @MOBILE_NO, @P_NATIONAL_ID, @NATIONAL_ID_EXP_DT, @PASSPORT_NO, @PPT_EXP_DATE
    , @ERROR_CODE OUTPUT, @ERROR_DESCRIPTION OUTPUT )
    AT linkserver_name;
    waiting for the reply.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s