Sql-server – Why can’t I access stored procedures on a linked MSDE 8.x server

linked-serversql serverstored-procedures

I am trying to execute a stored procedure on a linked MSDE 8.x server by running the following SQL against the parent SQL Server 2000 instance…

DECLARE @RC int

EXECUTE @RC = [LINKED_SERVER_NAME].[LINKED_SERVER_DATABASE].[dbo].[STORED_PROCEDURE_NAME]

GO

…but I get this error message:

Msg 7411, Level 16, State 1, Line 2
Server 'LINKED_SERVER_NAME' is not configured for RPC.

Best Answer

You can store credentials in a powershell session using get-credentials but I'm guessing that's not what you're after.

Powershell runs in the context of whoever startet it (like all windows executeables), so for SQL Server operations I'd just use integrated windows security for the connection, and then assign the correct SQL Permissions to the user/service account that you set up to be running the scripts. That way you use the security model in WIndows and won't have to make your own :-)

Related Solutions

Sql-server – SQL Server Linked Server RPC Options

An example of an RPC in this context is a stored procedure. To link another server and run an sp on it you'll need to set the RPC Out option.

-Anders

Sql-server – SQL 2005 Replication error

I think the code below is what's failing. I got this by running sp_helptext sp_changedistributiondb & also looking at what sproc's call sp_verify_job_identifiers.

It looks like changedistributiondb is having trouble updating the distribution cleanup agent job when it runs sp_update_jobstep :

SELECT @command =  'EXEC dbo.sp_MSdistribution_cleanup @min_distretention = ' + 
                CONVERT(nvarchar(12), @new_min_distretention) + ', @max_distretention = ' +
                CONVERT(nvarchar(12), @max_distretention)
            EXEC @retcode = msdb.dbo.sp_update_jobstep @job_name = @agentname, @step_id = 1, 
                @command = @command

maybe this job has been deleted or there is something else strange going on, either way, you should be able to modify the job step manually.

EDIT: The cleanup agent runs on the server with the distribution database. It removes replication history and runs every 10 minutes by default. The job should look like this:

name: Agent history clean up: distribution
category: REPL-History Cleanup
steps: (1)Run Agent
database: distribution
**command:**EXEC dbo.sp_MShistory_cleanup @history_retention = 48

Best Answer

Related Solutions

Sql-server – SQL Server Linked Server RPC Options

Sql-server – SQL 2005 Replication error

Related Topic