how to invoke objects in another server databse?

Question

how to invoke in objects(tables,stored procs,views etc..) in anathor server database from my current server database.THANKS IN ADVANCE..

Answer 1

0

SQL Server supports 4 part object naming:

[<server>].[<database>].[<schema>].[<object>]

So, you can call things in other servers (linked servers) or databases just by including the relevant identifier.

For example, calling a procedure in another database on the same server:

EXEC [OtherDB].[MySchema].[MyProc]

answered Apr 14 '10 at 04:19 AM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

+1 beaten by seconds!

Apr 14 '10 at 04:23 AM Kev Riley ♦♦

+1,Thanks Matt.What permission should we need? at server level,database level and schema level.

Apr 14 '10 at 04:40 AM venkatreddy

permission to add a linked server needs ALTER ANY LINKED SERVER, then you manage the security by configuring mappings between the source server logins and target server logins, or using existing Windows Authentication

Apr 14 '10 at 05:09 AM Kev Riley ♦♦

(comments are locked)

Answer 2

0

You could use Linked Servers and then reference the objects by:

server.database.schema.object

For more ad-hoc requests, you can also use OPENROWSET and OPENDATASOURCE

answered Apr 14 '10 at 04:22 AM

Kev Riley ♦♦
46.1k ● 39 ● 43 ● 69

+1,thanks Kev...

Apr 14 '10 at 04:41 AM venkatreddy

+1 because your comment was worth rep :)

Apr 14 '10 at 06:33 AM Matt Whitfield ♦♦

(comments are locked)

how to invoke objects in another server databse?

Follow this question

Related Questions