Tips
Configure Generic SQL Server Linked Server Names
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:
Dynamically Import data from a foreign database using a SQL Server Linked Server
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.
How to create a SQL Server link server to IBM DB2
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.
How to setup linked servers for SQL Server and Oracle 64 bit client
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.
On demand creation and destruction of a SQL Server Linked Server
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?
On Demand Linked Server Creation with a SQL Server Login
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?
Querying Active Directory Data from SQL Server
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.
Script to check all your linked server connections for SQL Server
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.
Top 10
How to setup linked servers for SQL Server and Oracle 64 bit client
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.
Querying Active Directory Data from SQL Server
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.
Configure Generic SQL Server Linked Server Names
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:
Script to check all your linked server connections for SQL Server
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.
How to create a SQL Server link server to IBM DB2
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.
On demand creation and destruction of a SQL Server Linked Server
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?
Dynamically Import data from a foreign database using a SQL Server Linked Server
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.
On Demand Linked Server Creation with a SQL Server Login
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?
Last 10
Querying Active Directory Data from SQL Server
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.
Configure Generic SQL Server Linked Server Names
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:
How to create a SQL Server link server to IBM DB2
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.
Script to check all your linked server connections for SQL Server
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.
Dynamically Import data from a foreign database using a SQL Server Linked Server
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.
On Demand Linked Server Creation with a SQL Server Login
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?
On demand creation and destruction of a SQL Server Linked Server
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?
How to setup linked servers for SQL Server and Oracle 64 bit client
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.