Thursday, February 16, 2012

Access Linked Server system function

Could anyone shed some light on the syntax of accessing system function on a linked server?

I'm trying to get the recovery models of databases on a linked. However using databasepropertyex locally generates wrong results.

e.g.

select databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases

I tried select [server/databasename].databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases

which does not work.

Thanks.

You cannot call a function directly from a linked server using the four part naming convention. You have to use a stored procedure to get the results.

No comments:

Post a Comment