question

tony_l avatar image
tony_l asked

sysremote objects

Hi if i do a "select [name] from master.dbo.sysobjects where type = 'S' order by [name]" I do not see any objects beginning with "sysremote" - do I have an issue with these objects being missing and how would I resolve this if so? SQL sever2008 r2 Tony
sql-server-2008linked
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Try select [name] from master.dbo.sysobjects where type = 'V' order by [name] sysremotelogins is a SQL Server 2000 system table is maintained as a view for backward compatibility. In SQL 2008R2 you should be using sys.remote_logins catalog view
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tony_l avatar image tony_l commented ·
Hi just did and did not get the objects i expected to see. here are the objects SYSREMOTE_CATALOGS SYSREMOTE_COLUMN_PRIVILEGES SYSREMOTE_COLUMNS SYSREMOTE_FOREIGN_KEYS SYSREMOTE_INDEXES SYSREMOTE_PRIMARY_KEYS SYSREMOTE_PROVIDER_TYPES SYSREMOTE_SCHEMATA SYSREMOTE_STATISTICS SYSREMOTE_TABLE_PRIVILEGES SYSREMOTE_TABLES SYSREMOTE_VIEWS any thoughts?
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
These objects have not existed since SQL 2005 was introduced. What is it you are trying to use these for?
0 Likes 0 ·
tony_l avatar image tony_l commented ·
I have been "blindly" investigating my previous question about linked servers. I found through the profiler that a system stored proc is executed and this proc uses a function that makes refernce to these object names - SYSREMOTE_CATALOGS. see prvoius post update info. I am new to this so I am following my nose - thanks for the help.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
What proc and function is it that you found using these objects?
0 Likes 0 ·
tony_l avatar image tony_l commented ·
running the profiler led me to a stored proc sp_catalogs which in turn has a function of fn_remote_catalogs. Now I cannot find this anywhere in the system databases and because it is missing I believe this is causing the eventual error. Of course i could have been completely misled. i found something to create this procedure but it refers to sysremote... and hence my question.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
@tony_l - I would suspect that the code being exposed to us for sp_catalogs is not exactly what is being executed - the fact that it is a system stored proc, and not generally meant to be de-constructed, means that MS sometimes 'hide' the true definition. I too do not have this function or those objects, but my linked server environments work fine. I do think you are going down the wrong path with this one.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
It also might exist in the Resource database (mssqlsystemresource), which is a read-only, 'invisible' database - you won't find this in SSMS.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.