Linked Servers Tips

Every DBA is faced with the task of occasionally refreshing their databases from Staging to QA to Development. A while back I started working on a project where we were building out a new environment and our DBA team was frequently tasked with refreshing the new environment with data from the existing one. In this environment there were quite a few linked servers and every time we would restore a database to the new environment testers would start to get errors similar to the following:

You have a foreign database, different than SQL Server. This database can be Oracle, DB2, Progress DB, Sybase or any other database platform that can be connected to by using ODBC or OLE DB providers. You need to import data from many tables that reside in the remote database into your SQL Server database. The list of tables is dynamic (tables may be added or removed from the list or from the remote/destination databases) and the schema could also change. It is possible that the tables on the SQL Server side also include additional column, such as the datetime when the row was imported or a timestamp. In this article, I show how this can be done by using Linked Servers.

Creating linked servers is generally a pretty easy thing to do, but the creation of a link server with DB2  is not as simple. To create a linked server to DB2, it requires the SQL DBA to know or guess a lot of parameters that are not familiar or intuitive. In this tip we will walk through the process of setting iup a linked server to DB2.

We have two SQL 2005 64-bit clusters in our organization: a four-node production cluster and a two-node dev/test cluster. We recently migrated a database from SQL 2000 that relied on an Oracle linked server for some computations. No big deal, right? Install the Oracle client software and create the linked server just like in SQL 2000 right? Be sure to repeat the process on both nodes for failover-sake, right? Wrong. In the case of 64-bit SQL instances we have a few extra steps we need to take to get an Oracle linked server configured and working properly.

I am trying to create a stored procedure which creates a linked server connection, performs a query against the linked server, and then tears down the linked server connection. I do not want the connection to be up at all times, hence the reason I want to create the connection in the stored procedure. However, when I try to create the stored procedure, I get an error. How do I get around this and create the stored procedure?

I am trying to create a stored procedure which creates a linked server connection, performs a query against the linked server, and then tears down that connection. I don't want the connection to be up at all times, hence the reason I want to create the connection in the stored procedure. Based on a previous tip, I know how to create the linked server within the stored procedure, but I need to make a connection via a SQL Server login. However, I don't want to store the credentials in the stored procedure as I'll have to change the stored procedure every time the password changes. How can I store the credentials securely, but in a way which makes them easier to change?

My boss is asking for a list of email addresses and phone numbers for all users in the company. I know this data exists in Active Directory, so how can I access this data from SQL Server?  In this tip we walk through how you can query Active Directory from within SQL Server Management Studio.

You need to quickly check all linked server connections for a given instance or list of instances for SQL Server. In this tip I present a PowerShell script that can be used to quickly check all linked servers.

We have two SQL 2005 64-bit clusters in our organization: a four-node production cluster and a two-node dev/test cluster. We recently migrated a database from SQL 2000 that relied on an Oracle linked server for some computations. No big deal, right? Install the Oracle client software and create the linked server just like in SQL 2000 right? Be sure to repeat the process on both nodes for failover-sake, right? Wrong. In the case of 64-bit SQL instances we have a few extra steps we need to take to get an Oracle linked server configured and working properly.

My boss is asking for a list of email addresses and phone numbers for all users in the company. I know this data exists in Active Directory, so how can I access this data from SQL Server?  In this tip we walk through how you can query Active Directory from within SQL Server Management Studio.

Every DBA is faced with the task of occasionally refreshing their databases from Staging to QA to Development. A while back I started working on a project where we were building out a new environment and our DBA team was frequently tasked with refreshing the new environment with data from the existing one. In this environment there were quite a few linked servers and every time we would restore a database to the new environment testers would start to get errors similar to the following:

You need to quickly check all linked server connections for a given instance or list of instances for SQL Server. In this tip I present a PowerShell script that can be used to quickly check all linked servers.

Creating linked servers is generally a pretty easy thing to do, but the creation of a link server with DB2  is not as simple. To create a linked server to DB2, it requires the SQL DBA to know or guess a lot of parameters that are not familiar or intuitive. In this tip we will walk through the process of setting iup a linked server to DB2.

I am trying to create a stored procedure which creates a linked server connection, performs a query against the linked server, and then tears down the linked server connection. I do not want the connection to be up at all times, hence the reason I want to create the connection in the stored procedure. However, when I try to create the stored procedure, I get an error. How do I get around this and create the stored procedure?

You have a foreign database, different than SQL Server. This database can be Oracle, DB2, Progress DB, Sybase or any other database platform that can be connected to by using ODBC or OLE DB providers. You need to import data from many tables that reside in the remote database into your SQL Server database. The list of tables is dynamic (tables may be added or removed from the list or from the remote/destination databases) and the schema could also change. It is possible that the tables on the SQL Server side also include additional column, such as the datetime when the row was imported or a timestamp. In this article, I show how this can be done by using Linked Servers.

I am trying to create a stored procedure which creates a linked server connection, performs a query against the linked server, and then tears down that connection. I don't want the connection to be up at all times, hence the reason I want to create the connection in the stored procedure. Based on a previous tip, I know how to create the linked server within the stored procedure, but I need to make a connection via a SQL Server login. However, I don't want to store the credentials in the stored procedure as I'll have to change the stored procedure every time the password changes. How can I store the credentials securely, but in a way which makes them easier to change?

My boss is asking for a list of email addresses and phone numbers for all users in the company. I know this data exists in Active Directory, so how can I access this data from SQL Server?  In this tip we walk through how you can query Active Directory from within SQL Server Management Studio.

Every DBA is faced with the task of occasionally refreshing their databases from Staging to QA to Development. A while back I started working on a project where we were building out a new environment and our DBA team was frequently tasked with refreshing the new environment with data from the existing one. In this environment there were quite a few linked servers and every time we would restore a database to the new environment testers would start to get errors similar to the following:

Creating linked servers is generally a pretty easy thing to do, but the creation of a link server with DB2  is not as simple. To create a linked server to DB2, it requires the SQL DBA to know or guess a lot of parameters that are not familiar or intuitive. In this tip we will walk through the process of setting iup a linked server to DB2.

You need to quickly check all linked server connections for a given instance or list of instances for SQL Server. In this tip I present a PowerShell script that can be used to quickly check all linked servers.

You have a foreign database, different than SQL Server. This database can be Oracle, DB2, Progress DB, Sybase or any other database platform that can be connected to by using ODBC or OLE DB providers. You need to import data from many tables that reside in the remote database into your SQL Server database. The list of tables is dynamic (tables may be added or removed from the list or from the remote/destination databases) and the schema could also change. It is possible that the tables on the SQL Server side also include additional column, such as the datetime when the row was imported or a timestamp. In this article, I show how this can be done by using Linked Servers.

I am trying to create a stored procedure which creates a linked server connection, performs a query against the linked server, and then tears down that connection. I don't want the connection to be up at all times, hence the reason I want to create the connection in the stored procedure. Based on a previous tip, I know how to create the linked server within the stored procedure, but I need to make a connection via a SQL Server login. However, I don't want to store the credentials in the stored procedure as I'll have to change the stored procedure every time the password changes. How can I store the credentials securely, but in a way which makes them easier to change?

I am trying to create a stored procedure which creates a linked server connection, performs a query against the linked server, and then tears down the linked server connection. I do not want the connection to be up at all times, hence the reason I want to create the connection in the stored procedure. However, when I try to create the stored procedure, I get an error. How do I get around this and create the stored procedure?

We have two SQL 2005 64-bit clusters in our organization: a four-node production cluster and a two-node dev/test cluster. We recently migrated a database from SQL 2000 that relied on an Oracle linked server for some computations. No big deal, right? Install the Oracle client software and create the linked server just like in SQL 2000 right? Be sure to repeat the process on both nodes for failover-sake, right? Wrong. In the case of 64-bit SQL instances we have a few extra steps we need to take to get an Oracle linked server configured and working properly.