Fetch records from remote server without linked server

We can use OPENROWSET for this case if we have credential details of remote SQL Server.

T-SQL

SELECT     *
FROM OPENROWSET('SQLNCLI',
'DRIVER={SQL Server};SERVER=ServerName;UID=sa;
PWD=XXXX;database=DBName;',
'SELECT * FROM dbo.TableName')

That’s It.
Enjoy Learning.