How to Check a Database Link
Microsoft SQL Server comes with the option to link two databases together. When two databases are linked, a programmer can query data on a separate server from the main server. This is ideal for large organizations that have multiple databases. It pools all database queries onto one server, making it easier to maintain and secure. Checking the link between two servers involves writing a snippet of code on the main database console and ensuring that no errors are returned.
Other People Are Reading
Instructions
-
-
1
Open the Microsoft SQL Server Management Studio. This application is used to query directly to the server. It is included with the SQL Server software package. When it's opened, it automatically defaults to a command console where the database link can be tested.
-
2
Use the following syntax in the command console:
EXEC [RemoteServerName].DB_Name.DB_Owner.StoredProc
The "RemoteServerName" is the name of the linked database. Note that this name is different than the local SQL Server name. The next parameter, named "DB_Name," is the name of the linked database. "DB_Owner" is normally "dbo" for most SQL setups, but some administrators create different database owner permissions, so this name may vary. Finally, the "StoredProc" parameter identifies the stored procedure. If this call fails, then the database link is not functioning properly. -
-
3
Use the following command to run direct queries on the server:
SELECT * FROM OPENQUERY([RemoteServerName],"SELECT * from customer")
This command is different than the one in Step 2. This tests the database link for permission to use direct queries on the server. This is a method used to create dynamic queries on database servers, which can be a security issue if not implemented properly. If the link does not have permissions or is improperly set up, an error will be returned by the SQL Server.
-
1
