Skip to main content

Posts

Showing posts from January, 2009

Linking to external databases and servers in SQL Server

In order to select tables in a stored procedure, view or trigger in an external database you must use the fully qualified name in the select statement.

For example, to select from a table in a separate database on the same SQL Server instance you might write:

SELECT AuthorID, Author
FROM BooksDb.dbo.PIC_Author

You can also select cross-databases by adding the server name:

SELECT AuthorID, Author
FROM Server2.BooksDb.dbo.PIC_Author

Until this morning the SQL Statement above had always worked for me, but this morning using a new server (running SQL2008) I received the following error message:

---------------------------
Microsoft SQL Server Management Studio
---------------------------
SQL Execution Error.

Executed SQL statement: SELECT AuthorID, Author FROM Server2.BooksDb.dbo.PIC_Author
Error Source: .Net SqlClient Data Provider
Error Message: Could not find server 'Server2' in sys.servers. Verify that the correct server name was specified. If necessary, execute the …