3

I was able to create an alias for a linked server using this method. While linking the server I specified default catalog so now I can execute remote SP by skipping schema and Database name e.g. this works:

EXEC REMOTE_SERVER_ALIAS...REMOTE_STORED_PROCEDURE

But for some reason I cannot use the same approach for simple select:

SELECT * FROM REMOTE_SERVER_ALIAS...REMOTE_TABLE

Attempting to execute this throws error:

An invalid schema or catalog was specified

Any idea how to make it work?

P.S. I am aware about OPENQUERY approach, but rather not use it due to its limitations

Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
  • I think different versions of SQL Server parse this differently, so the best option is to use the fully qualified name even though it's a bit more to type. – idstam Jan 24 '14 at 20:53
  • 1
    @idstam The problem is - we're creating a universal script that should run against server with unknown name and DB with unknown name (we do know that the DB will have SPs and Tables with known names). So the idea was - create aliased server with default catalog so the script just have to specify server alias and tables/SPs names – Yuriy Galanter Jan 24 '14 at 20:58

1 Answers1

1

I think you have to specify the schema for the table, so you'll have to use the full path for that table:

Select * From [Remote_Server_Alias].[Database_Name].[Schema].[Table_Name]

I know you've specified the default catalog for the linked server, but you may have to include it in there anyway. In any event, you can always try leaving one piece or another off, but the last time I used a linked server, I believe this was how I had to reference the table.

Christopher Brown
  • 2,170
  • 1
  • 13
  • 19
  • Thanks for the answer but I already know if I specify all 4 parts - it works. What bugs me is - when calling a remote SP - I can either specify all 4 parts *or* omit schema and DB name - it will still work. The question was - how to make the same work for tables. Or - if this is impossible - *why* it works for SPs and doesn't work for tables – Yuriy Galanter Jan 24 '14 at 20:39
  • Well, Stored Procedures don't have a schema associated with them so that's one piece less for them. Stored Procedures are stored at the Database level, so using the default catalog removes the need to specify the database. My first SQL mentor said all pieces to the right of the deepest level you specify are required. So if you specify server, you have to specify database, schema, and table name. If you specify database, you have to specify schema and table. Doing a quick search, I can't find anyway around this. Hopefully someone else knows more. – Christopher Brown Jan 24 '14 at 21:12