That's brillant. Thank you all for your help.

I get this message:

Could not find server 'SELECT a' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server...

If I change all the quotes (") to apostrophes (') it comes back with the SQLOLEDB ERROR when I change to Print @sql

However if I run my original...

Changed all quotes(") to apostrophes (')

still get:

Incorrect syntax near 'SQLOLEDB'.

OpenRowset is configured on the server.

I have changed the code to Exec (@sql) but I now get:

Incorrect syntax near 'SQLOLEDB'.

Msg 203, Level 16, State 2, Line 20

The name 'SELECT a.* FROM OPENROWSET("SQLOLEDB","NOTREALSERVER";"NOTREALUSER";"NOTREALPASSWORD","SET FMTONLY OFF;EXEC master.dbo.xp_fixeddrives")AS a;' is not a valid identifier.

Thank you very much, this worked.

Still get the same result

Just tried this but I only seem to get the IIS Servername rather than the PC name, the same as I get when I do SP_WHO2. Is it possible to...