Wednesday, June 16, 2010

Performing Queries With T-SQL DBs With Illegal Characters In Their Name

I've been trying to connect to a DB on a completely different server, using Microsoft SQL Server Management Studio, and discovering a big problem. The SQL Server I'm trying to query has a '-' character in it's name.

This is overcome using two methods:
  1. 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.

  2. 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

No comments:

Post a Comment