Why can't I access stored procedures on a linked MSDE 8.x server?

Question

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.

Answer 1

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

Answer 2

You can store them in a file and read it afterwards, see this article:

http://bsonposh.com/archives/338

Answer 3

In SQL Server Management Studio:

1. Right-click the MSDE linked server instance and choose [Properties]
2. Select [Server Options]
3. Make sure that the [RPC] and [RPC Out] properties are set to [TRUE]

(in my case they were both set to FALSE)