x

how to invoke objects in another server databse?

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

more ▼

asked Apr 14, 2010 at 04:15 AM in Default

venkatreddy gravatar image

venkatreddy
562 28 29 32

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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] 
more ▼

answered Apr 14, 2010 at 04:19 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

+1 beaten by seconds!
Apr 14, 2010 at 04:23 AM Kev Riley ♦♦
+1,Thanks Matt.What permission should we need? at server level,database level and schema level.
Apr 14, 2010 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, 2010 at 05:09 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Apr 14, 2010 at 04:22 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

+1,thanks Kev...
Apr 14, 2010 at 04:41 AM venkatreddy
+1 because your comment was worth rep :)
Apr 14, 2010 at 06:33 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x990
x242

asked: Apr 14, 2010 at 04:15 AM

Seen: 1136 times

Last Updated: Apr 14, 2010 at 04:23 AM