Exec soted procedure on a remote server

[stiej]

Hi,

My sql statement of "exec myserver.mydatabase.dbo.mysp" inside a dts pack that should execute a stored procedure located on a remote server complains that the server is not set up to run stored procedures on a remote server.

What do i need to do to accomplish this. On my local server i have the remote server set up as a linked server, which the local server can succesfully see. I can select some data from remote tables and it returns on the local server's screen.

Though i seem to not be able to launch a stored procedure of the same connection.

Any ideas?

Thanks in advance...

 

[DrSql]

Use a "Execute Process Task Properties"

Remove 'Create procedure' from the SP, and save in a notepad.

Description: [tt]Name of your Task[/tt]
Win32process: [tt]\\cdcftp02\C$\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe[/tt]
(locate your 'osql.exe' in you local server, (not in remote))
Parameters[tt]-S remote_instance -U userid -P password -i \\cdcftp02\d$\MSSQL\Scripts\SQLEXEC\sql.txt -o
\\cdcftp02\d$\MSSQL\Scripts\SQLEXEC\NtRenameOutput.txt[/tt]
Return code:[tt]0[/tt]
Timeout[tt]600[/tt]
Terminate process after timeout check

If you need clarifications let me know.

Dr.Sql
Good Luck.

 

[mrdenny]

Open the Linked Server properties in Enterprise Manager. Check the RPC and RPC Out check boxes and try again.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[stiej]

Thanks guys. All works now. Cheers!