Execute a Stored Procedure on Linked Server ? 1

[MarkB2]

I am having no luck in executing a stored procedure on a linked server. Is this possible? I am trying the following in SQL Query Analyzer.

exec MXW4.CMSTEST.sp_diaryalerts
--------------------------------------
[red]Server: Msg 911, Level 16, State 1, Line 1[/red]
Could not locate entry in sysdatabases for database 'MXW4'. No entry found with that name. Make sure that the name is entered correctly.

Yet I can see the linked server and tables. I can also access the linked tables with a "SELECT" without a problem.

A second question is if I can get this to work, if the stored procedure references tables without the [server.database] qualifier, is it going to access the local server or the linked server?

TIA
Mark

 

[NoCoolHandle]

You seem to be missing the owner of the object..

ie try exec MXW4.CMSTEST[red].dbo[/red].sp_diaryalerts

 

[donutman]

Nice thought, but I don't think that you can get that to work. If you could, it seems logical that it wouldn't need the server qualifier once it was executing on the link server.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Didn't see NCH's post. Hope it works, let me know because I was looking for something like that a few months back and didn't think of doing it that way...finally gave up! :-(
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[NoCoolHandle]

if you look at "MXW4.CMSTEST.sp_diaryalerts"
because it is only 3 part naming the server is thinging the database name =mxw4
owner = cmstest
object = sp_dialyalerts

the key is 4 part naming is necessary!

with access you go linkedservername...table the two missing dots are for the databasename (not applicable) and owner (not applicable)

with sql you have NEED to fill in all 4 parts

HTH


Rob

 

[donutman]

Even though I've used the 4-part naming before with tables, I just didn't think of doing it with a SP. Sometimes I can be really stupid. (I know there's something I can blame it on...but at my age I forget what it is!)

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[MarkB2]

Thanx NCH, I added the owner as follows and all worked as needed.

exec MXW4.CMSTEST.dbo.sp_diaryalerts

If the stored procedure references a table named 'XYZ' without using the [server.database] qualifier, which is defined to the local database and the linked database, which table will be referenced?

 

[NoCoolHandle]

The stored proc runs on the remote server, so it will see the remote table.

 

[MarkB2]

Thanx again NCH, now on to the task of building the procedures.... ;-)