How to call stored proc resides in another server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to call stored proc resides in another server

Author

Topic

swatib
Posting Yak Master

173 Posts

Posted - 2006-09-18 : 02:14:31

How to call stored proc resides in another server

what is linked server? when we use Linked server?

when RPC is used?

Njoy Life

swatib
Posting Yak Master

173 Posts

Posted - 2006-09-18 : 02:36:21

I've a server that is on my network. I need to call a stored proc of that server from my local server.

So I configured a linked server as that server and RPC options ON, but when i execute the statement like EXEC [myserver].[activity].[dbo].distinct_emp, it is giving "[OLE/DB provider returned message: Invalid authorization specification]" message

Tell me where i m wrong...............

Njoy Life

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-18 : 02:37:30

To execute SP that resides on another server, you have to add that server as linked server by using the system sp sp_addlinkedserver and then you can use four-part nameing syntax to execute the stored procedure as follows:

EXEC LINKEDSERVER1.DB1.DBO.PROC_TEST

Harsh Athalye
India.
"Nothing is Impossible"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-18 : 02:40:42

or you can use OPENQUERY like this:

Select * from OPENQUERY(linkedserver1, 'EXEC db1..Proc_Test')

Harsh Athalye
India.
"Nothing is Impossible"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-18 : 02:42:55

quote:
Originally posted by swatib

I've a server that is on my network. I need to call a stored proc of that server from my local server.

So I configured a linked server as that server and RPC options ON, but when i execute the statement like EXEC [myserver].[activity].[dbo].distinct_emp, it is giving "[OLE/DB provider returned message: Invalid authorization specification]" message

Tell me where i m wrong...............

Njoy Life

Do you have necessary permissions to access the specified linked server? Please check whether you can log on to that server using Query Analyzer and execute the SP !

Harsh Athalye
India.
"Nothing is Impossible"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-18 : 02:47:08

or may be you have not created mapping between your local login and remote login on the linked server.

Create mapping using sp_addlinkedsrvlogin system SP. Check BOL for information on sp_addlinkedsrvlogin.

Harsh Athalye
India.
"Nothing is Impossible"

swatib
Posting Yak Master

173 Posts

Posted - 2006-09-18 : 03:05:03

quote:
Originally posted by harsh_athalye

or may be you have not created mapping between your local login and remote login on the linked server.

Create mapping using sp_addlinkedsrvlogin system SP. Check BOL for information on sp_addlinkedsrvlogin.

Thanks Harsh!
I tried various options for sp_addlinkedsrvlogin but still no output. please help me to specify the correct string in simple words

Njoy Life

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-18 : 03:19:17

quote:
Originally posted by swatib

quote:
Originally posted by harsh_athalye

or may be you have not created mapping between your local login and remote login on the linked server.

Create mapping using sp_addlinkedsrvlogin system SP. Check BOL for information on sp_addlinkedsrvlogin.

Thanks Harsh!
I tried various options for sp_addlinkedsrvlogin but still no output. please help me to specify the correct string in simple words

Njoy Life

Let's say you currently logged on to your local server using login name 'swati' (SQL Authentication, I assume but same is applicable for Windows authentication) and you also have say 'swatib' login on the remote sql server and you want to create mapping between these two.

Then you can run following statement on your local server after adding linked server:

EXEC SP_ADDLINKEDSRVLOGIN @RMTSRVNAME = 'REMOTESERVER1', @USESELF = 'FALSE', @LOCALLOGIN = 'swati', @RMTUSER = 'swatib', @RMTPASSWORD = 'test'

Harsh Athalye
India.
"Nothing is Impossible"

swatib
Posting Yak Master

173 Posts

Posted - 2006-09-18 : 03:40:27

it worked!!!!
I've managed to do the same in EM also

Thanks a lot.......
One more question if you don't mind.....

I'm using the @@error statement in a single SQL statement but it is not showing any custom message-
I tried like

select * from divisionmaster
if @@error=0
print 'no error'

Njoy Life

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-18 : 03:44:20

quote:
Originally posted by swatib

it worked!!!!
I've managed to do the same in EM also

Thanks a lot.......
One more question if you don't mind.....

I'm using the @@error statement in a single SQL statement but it is not showing any custom message-
I tried like

select * from divisionmaster
if @@error=0
print 'no error'

Njoy Life

It should work !!

Check the Messages tab in the output. The message will not be shown in the Results grid. If you want it in Results grid, make use of SELECT statement:

select * from divisionmaster
if @@error=0
Select 'no error'

Harsh Athalye
India.
"Nothing is Impossible"

swatib
Posting Yak Master

173 Posts

Posted - 2006-09-18 : 04:37:22

Oh........

Thanks Harsh......
Plz tell me diff between Linked server and remote server. If the DB server is not on my network then can i still use the linked server?

Njoy Life

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-18 : 06:14:08

quote:
Originally posted by swatib

Oh........

Thanks Harsh......
Plz tell me diff between Linked server and remote server. If the DB server is not on my network then can i still use the linked server?

Njoy Life

Check out below link for answer:

[url]http://www.mcse.ms/archive94-2005-5-1642757.html[/url]

Harsh Athalye
India.
"Nothing is Impossible"

swatib
Posting Yak Master

173 Posts

Posted - 2006-09-18 : 08:27:59

I had already gone thru this link, could you plz elaborate the same to get the picture of the same

Njoy Life

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources