This is overcome using two methods:
- Add the SQL Server to the local (on my development machine) SQL Server linked servers list.
This is done by:
Using the Object Explorer and opening "Server Objects -> Linked Servers", right clicking and selecting "New Linked Server".
The important next step is that the "Linked server" field is filled out with the SQL Server name only, eg: SOMELIVEBOX-SQL5 and then the "SQL Server" radio button is selected.
Next, on the left, select "Security" and choose "Be made using this security context:" and fill out the SQL Server login details.
- Then the query, and this is only relevant if the SQL Server name has an illegal character (like '-') in it.
Lets say the table is called "MyTable", is in a database called "MyDB", is in the server mentioned in point 1 and we want all the records from it...
Open a new query window from inside the local database and type:
SELECT * FROM [SOMELIVEBOX-SQL5].MyDB.dbo.MyTable