Creating Stored Procedures on a Linked 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 2005 Forums

Transact-SQL (2005)

Creating Stored Procedures on a Linked Server

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-12-19 : 04:23:37

Hi

I've just created a linked server with an Access file as the data source.
I am able to retrieve information through SELECT statements, but do not know how to create stored procedures using the linked server.
Can anyone help?

Thanks

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-19 : 04:24:58

[code]Create Procedure [proc_name]
(
@param1 int,
...
)
AS

-- SQL queries here
...

GO[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-12-19 : 04:47:10

Hi

I understand the syntax but how do I assocaite it with the linked server?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-19 : 05:04:32

What do you mean associating with Linked Server? The stored procedure will be created on the server on which you compile it, if that's what you mean.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-12-19 : 05:23:03

Hi

Basically I have created the linked server in SQL Server 2005, the reference to the server has been put under Server Objects>Linked Servers. I am unable to select the database(linked server) in the drop down list which appears near the top of the screen.

If I try using the statement
USE <Linked Server Name>
Go

I get an error message saying that the entry cannot be located.

Hope this helps a bit more.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-12-19 : 05:27:19

USE statement is used to switch to another Database not the server. You need to use four part naming convention to access linked server data like this:

Select * from <linked-server>.<database>.<owner>.<table-name>

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2007-12-19 : 06:04:49

Hi

Is there anyway to just access the server and maybe the database?

Sorry to be a pain

NeilG
Aged Yak Warrior

530 Posts

Posted - 2007-12-19 : 06:36:35

quote:
USE statement is used to switch to another Database not the server. You need to use four part naming convention to access linked server data like this:

Select * from <linked-server>.<database>.<owner>.<table-name>

That is what you require to access the linked server

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-12-19 : 14:58:14

>> how to create stored procedures using the linked server

You can't run ddl on linked server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources