Now, try this

SELECT Inventory.Descr, Inventory.InvtID, Inventory.User6
FROM   Inventory
          INNER JOIN OPENROWSET('SQLOLEDB',
          '(local)';'x';'123';'select * from medex..inventory') LocalInv ON
          Inventory.InvtID = LocalInv.solomonid

>well, it looks a little better. now I am only getting the message:
>
>[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'emnsql02'
>
>I assume the user name and password are for the database and not the server.
>Do I need single quotes around the user id and password? And, if the user name and password are based on Windows Authentication, does it make a difference if you are logging in locally or onto a domain? Also, what does 'emnsql02' mean?
>
>SELECT Inventory.Descr, Inventory.InvtID, Inventory.User6
>FROM Inventory
> INNER JOIN OPENROWSET('SQLOLEDB',
> 'server=(local);User id=x;password=123;
> database=MEDex', inventory) LocalInv ON
> Inventory.InvtID = LocalInv.solomonid