exec sp_addlinkedserver 'LINKNAME', ' ', 'MSDASQL', 'DSNNAME',@provstr = 'UID=username;PWD=password;'
The parameter LINKNAME is the name that the link will be referred to.
The parameter DSNNAME is the name of the DSN entry that will be used to connect to the database.
The parameter @provstr is used to tell the ASA database of the user name and password to connect to the database. The user name and password is assigned by Blackbaud and used during the installation process of RE6:Open and EA:Open. RE7:Open and FE:Open uses the user name and password that was inputted by the installed during the unlock process.
An example of this for Raiser's Edge 7 would be:
exec sp_addlinkedserver 'RE7OPEN', ' ', 'MSDASQL', 'REOPEN7',@provstr = 'UID=REOPEN7;PWD=Admin;'
A database server can also be linked through the GUI in Enterprise Manager by right clicking on the Security, Linked Servers node in the tree and picking New Linked Server.
Once the database has been linked, the tables and views can be viewed by selecting Security, Linked Servers
When writing queries against the Linked database, a pass-through query must be used. To create a pass-through query in a Microsoft SQL Server the OPENQUERY statement must be used.
The following is an example of a Openquery statement:
select * from OPENQUERY(DatabaseName,'select * from SchemaName.TableName where ID = 123')
For a Raiser's Edge database it would look like:
select * from OPENQUERY(RE7OPEN,'select * from REWIN.RECORDS')
Disclaimer: We provide programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes you are familiar with Structured Query Language and the tools used to create and modify SQL statements and Crystal Reports. Our Customer Support may help explain the functionality of a particular procedure, but we will not modify, or assist you with modifying, these examples to provide additional functionality.