Answer:
MS SQL Server supports the connection to different OLE DB on an ad hoc basis. This persistent connection is referred as Linked Server.
The following are the steps to use Linked Server for any OLE DB. I refer this to use an MS-Excel workbook.
Open SQL Server Management Studio in SQL Server 2005
Expand Server Objects in Object Explorer.
Right-click on Linked Servers. Click on New Linked Server.
Select General page in the left pane and
i. Type any name for the linked server in the first text box
ii. Select the Other Data Source option.
iii. Click on Microsoft Jet 4.0 OLE DB Provider from the Provider list.
iv. Type the Excel as the name of the OLE DB data source.
v. Type the full path and file name of the Excel file in Data Source box.
vi. Type the Excel version no. (7.0, 8.0 etc) in the Provider String. Use Excel 8.0 for Excel 2000, Excel 2002 or Excel 97.
vii. To create a linked server click on OK.
The following are the steps to use Linked Server for any OLE DB. I refer this to use an MS-Excel workbook.
Open SQL Server Management Studio in SQL Server 2005
Expand Server Objects in Object Explorer.
Right-click on Linked Servers. Click on New Linked Server.
Select General page in the left pane and
i. Type any name for the linked server in the first text box
ii. Select the Other Data Source option.
iii. Click on Microsoft Jet 4.0 OLE DB Provider from the Provider list.
iv. Type the Excel as the name of the OLE DB data source.
v. Type the full path and file name of the Excel file in Data Source box.
vi. Type the Excel version no. (7.0, 8.0 etc) in the Provider String. Use Excel 8.0 for Excel 2000, Excel 2002 or Excel 97.
vii. To create a linked server click on OK.
| Previous Question | Next Question |
| Do you know concepts and capabilities of SQL Server? | Do you know how to make remote connection in database? |