Calling stored proc on a remote server

Question

Calling stored proc on a remote server

valli

SSC Enthusiast

Points: 192
More actions
December 2, 2002 at 9:43 am

#79953

Hi
Can anyone help me with the following...
I am new to T-SQL and I am trying to execute a stored proc residing on a remote server via a stored proc. I need to know the syntax to pass the remote server login & password and then the exec statement to exec the stored proc.
Thanx in advance for your help
VR

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Mkumari Hall of Fame Points: 3498 More actions · Answer 1

Initially Linked servers need to be configured and execute the stored procedure with the server name, database name , sp name.

EXEC servername.databasename.dbo.storedproc

valli SSC Enthusiast Points: 192 More actions · Answer 2

The way these servers are set up we do not have permission to set up link servers. Is there any other way....

Mkumari Hall of Fame Points: 3498 More actions · Answer 3

If you cannot do it with linked servers try remote servers.

Klaas-Jan SSCrazy Points: 2297 More actions · Answer 4

try using openrowset , there you get put the connection info . see sqlserver books online (bol) for more details

Antares686 SSC Guru Points: 125444 More actions · Answer 5

If you need to set values of parameters then you need to use a linked server as described otherwise you can use OPENROWSET or a linked server with OPENQUERY. If you have to provide parameters and choose to use OPENROWSET then you will need to build query string dynamically for it to work then execute it.

valli SSC Enthusiast Points: 192 More actions · Answer 6

I am going to try OPENROWSET with dynamic sql as I do need to pass parameters. Thanx for the help. Will let you know how it works shortly.

valli SSC Enthusiast Points: 192 More actions · Answer 7

I tried the OPENROWSET option.

Is there any particular permission that the user needs to have to execute it??

I keep getting the "server does not exist or access denied error"

I know that the password etc are right and the server is running.

Any suggestions?

Thanx

VR

Antares686 SSC Guru Points: 125444 More actions · Answer 8

Antares686

SSC Guru

Points: 125444

December 3, 2002 at 3:53 pm

#442950

Can you post what you did? (alter security stuff)

valli SSC Enthusiast Points: 192 More actions · Answer 9

Here is the code that I added to the stored proc that calls the proc on the remote server:

===

Select @user_ssn

From OPENROWSET('provider','server_name';sa;pswd,

'Exec sp_executesql EXEC db.dbo.proc_name @parameter1,

@parameter1 varchar(20),

@parameter1')

AS a

=====

The calling stored proc would send the vlue for parameter1 and the remote proc should send the user_ssn.

Did I miss something in the syntax??

I tried the same by hardcoding values and also a simple query from Northwind DB. All gave the same error that I had mentioned earlier: access denied or server does not exist.

Antares686 SSC Guru Points: 125444 More actions · Answer 10

I see now. In order to get what you want you have to build the entire thing dynamically.

DECLARE @Query VARCHAR(8000)

SET @Query = 'Select user_ssn

From OPENROWSET(''provider'',''server_name'';sa;pswd,

''EXEC db.dbo.proc_name ' + @parameter1 + ''')

AS a'

EXEC (@Query) --I used EXEC here for quick test sp_executesql may or may not show to be better.

Note if @parameter1 is anything other than character type data you will need to CAST(@parameter AS varchar(xxx)) or similar to make usuable in the string.

valli SSC Enthusiast Points: 192 More actions · Answer 11

Hi Andreas,

Here are the changes I did, as per your last reply, to the code...still getting the same access denied error..

Any permissions that I need to check?

SET @Query = 'Select user_ssn

From OPENROWSET("provider","server";"uid";"pswd",

"EXEC db.dbo.proc_name + @parameter")

AS a'

EXEC (@Query)

The parameter I am passing is a username so only a string.

VR

David Burrows SSC Guru Points: 65138 More actions · Answer 12

"uid" will need execute permission to db.dbo.proc_name on "server" as well as access to the database db.

Far away is close at hand in the images of elsewhere.
Anon.

Antares686 SSC Guru Points: 125444 More actions · Answer 13

Other than that I don't see anything. Just one note make sure you are concatinating you value in I sse you have

"EXEC db.dbo.proc_name + @parameter")

should be

''EXEC db.dbo.proc_name ' + @parameter + ''')

and instead of using a double quote make sure you are using 2 single quotes. I forget that it isn't easy to see when posted here.

skylon SSC Veteran Points: 213 More actions · Answer 14

Continuing on with this topic, what do you do if you want to use Windows authentication with OPENDATASOURCE?