Remote Stored Procedure call

Question

Remote Stored Procedure call

born2achieve

Hall of Fame

Points: 3182
More actions
February 4, 2014 at 8:03 pm

#283132

Hi,
I have two different databases in two servers(server1, server2). i would like to call the server2 database SP in server1 database SP. I know this can be achieved using linked server. but in my case i shouldn't be using linked server. but i know the domain and credentials of calling server(server2). So how to achieve this using credentials rather than creating linked server. please suggest me the way to achieve this

Viewing 5 posts - 1 through 4 (of 4 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

born2achieve (2/4/2014)
Hi,
I have two different databases in two servers(server1, server2). i would like to call the server2 database SP in server1 database SP. I know this can be achieved using linked server. but in my case i shouldn't be using linked server. but i know the domain and credentials of calling server(server2). So how to achieve this using credentials rather than creating linked server. please suggest me the way to achieve this

HUH? You would create the link server using your remote credentials. That allows you to access the stored procedure there. Why "shouldn't you be using linked server"?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

born2achieve Hall of Fame Points: 3182 More actions · Answer 2

Hi Sean,

thanks for your reply. this is my client requirement. wondering is there any way to achieve without creating linked server.please suggest me

Lowell SSC Guru Points: 323518 More actions · Answer 3

want to use a remote server, but don't want a linked server? I'm with Sean, your dismissing the #1 tool without an apparent reason.

openrowset is disabled by default, whereas linked servers are available to you, so this might not be feasible either,:

you can use openrowset to execute a proc,and return it's results.

AFAIK, you can only use a trusted connection for yourself, or a SQL login; i don't beleive you can impersonate a windows user, if any:

SELECT *

FROM OPENROWSET('SQLOLEDB','Server=yourservernamehere;Trusted_Connection=Yes;Database=Master',

'Set FmtOnly OFF; EXEC dbo.sp_Who')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

born2achieve Hall of Fame Points: 3182 More actions · Answer 4

Hi lowel,

Even i am interested in going with linked server. But as my client needs other way i posted for suggestions. Thanks for your wonderful reply and i will start digging into "Openrowset "concept.