MySQL out of Memory, but Only Through MSSQL Linked Server

@cicik, if you are looping through the databases at your local server like you mentioned, assuming you already have the list of remote databases stored somewhere in a local table, you could loop through the databases (e.g. cursor) and execute,

SELECT * FROM [YourLinkedServer].[YourRemoteDatabase].[dbo].[view];

This should put the remote database context at the database you specified, and hence it works for FILEPROPERTY function and sys.database_files dmv that return values only for the current database.

Also, you might want to consider extracting the list of databases on the remote server and run the script on each execution at the remote server (client side) so you won't have to create the view on each database, and won't miss any new databases created without your knowledge.

UPDATE If you want to use script to dynamically extract the data from the linked server without adding the view on every database,

DECLARE @remotecmd nvarchar(max);

SET @remotecmd = N'
DECLARE @db     sysname,
        @cmd    nvarchar(max);

CREATE TABLE #dbspace 
(
    DataFileName nvarchar(128),
    DataPhysicalName nvarchar(260),
    DataTotalSizeInMB decimal(17,6),
    DataSpaceUsedInMB decimal(17,6),
    DataAvailableSpaceInMB decimal(17,6)
);

DECLARE dbcur CURSOR
LOCAL FAST_FORWARD
FOR SELECT name
    FROM sys.databases
    WHERE [state] = 0;

OPEN dbcur;
FETCH NEXT FROM dbcur INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = N''USE '' + QUOTENAME(@db) + N''; '';
SET @cmd = @cmd + 
N''
INSERT INTO #dbspace
SELECT 
    name AS [DataFileName], 
    physical_name AS [DataPhysicalName],
    ROUND(size / 128.0, 1) AS [DataTotalSizeInMB], 
    ROUND(CAST(FILEPROPERTY(name, ''''SpaceUsed'''') AS int) / 128.0, 1) AS [DataSpaceUsedInMB], 
    ROUND(size / 128.0 - CAST(FILEPROPERTY(name, ''''SpaceUsed'''') AS int) / 128.0, 1) AS [DataAvailableSpaceInMB] 
FROM  [sys].[database_files] WHERE type = 0'';

EXEC (@cmd);

FETCH NEXT FROM dbcur INTO @db;
END

CLOSE dbcur;
DEALLOCATE dbcur;

SELECT 
    DataFileName,
    DataPhysicalName,
    DataTotalSizeInMB,
    DataSpaceUsedInMB,
    DataAvailableSpaceInMB
FROM #dbspace;

DROP TABLE #dbspace;'

EXEC (@remotecmd) AT [YourLinkedServer];

Make sure your login has the access to all the databases on the remote server.

That might be a permission issue. When executed as a job step the code runs under the SQL Server Agent Service Account. When you execute it yourself it runs under your account.

Make sure that the QSL Server Service Account has appropriate permissions on the linked server. You can also try to impersonate that account by using

EXECUTE AS LOGIN='Domain\ServiceAccount';

before executing your code. That might reveal additional error detail and should make troubleshooting easier.