隱藏目錄

Retrieving SQL Values from a Linked Server Source

By configuring a linked server in SQL server, you can retrieve data from many external data sources such as MS Access, Excel, or other database types.

To populate Enterprise PDM lists with data contained in an Excel workbook, you would have to first configure an Excel data source as a linked server in SQL Server, then query the linked server for desired information.

For more information on linked servers and distributed queries, please consult the following Microsoft Knowledge Base article: http://support.microsoft.com/kb/306397

Excel must be installed on the server that has the SQL server installed.

To configure an Excel data source as a linked server in SQL Server 2005:

  1. In SQL Server Management Studio, in the Object Explorer pane, expand Server Objects.
  2. Right-click Linked Server and select New Linked Server.
  3. To create a new linked server, in the left pane, in the General property page:
    1. Type any name for the linked server. You should choose a name similar to the name of the Excel worksheet for easier reference. In this example the name "MY_EXCEL_WORKBOOK_LINKED_SERVER" is used.
    2. Select Other data source.
    3. In the Provider list, click Microsoft Jet 4.0 OLE DB Provider.
    4. For Product Name, type Excel for the name of the OLE DB data source.
    5. For Data Source, type the full path and file name of the Excel document, for example: C:\EngineeringData\My_Excel_Workbook.xls.
    6. For Provider String, type Excel 8.0 for an Excel 97, 2000, or 2002 workbook.
    7. Click OK.
  4. Query the Excel data source on the linked server.

    For example, to retrieve the fields in the 'PartNumber' column stored in Sheet1 of your Excel file you can use the following code: SELECT PartNumber FROM MY_EXCEL_WORKBOOK_LINKED_SERVER...Sheet1$

You can use the above SQL Query in an Enterprise PDM list with a data type "From SQL Database" and set it to refresh every X minutes to retrieve the PartNumbers from the Excel file into Enterprise PDM.

You must enter a database name (any existing database on the server will do, for example the Northwind sample database or the file vault database), and the server and login credentials of the SQL server with the linked server.

Parent topic: SQL Query Examples


提供對此主題的意見反應

SOLIDWORKS 歡迎您提供有關於文件呈現方式、正確性、及完整性的意見反應。請使用下方的表格直接將您對此主題的意見與建議傳送給我們的文件團隊。文件團隊並無法回答關於技術支援的問題。按一下此處來獲得關於技術支援的資訊。

* 必要的

 
*電子郵件:  
主旨:   說明主題的意見反應
頁面:   Retrieving SQL Values from a Linked Server Source
*意見:  
*   我承認已閱讀且僅此接受隱私權政策，Dassault Systèmes 將遵循此政策之規定使用我的個人資料

列印主題

選擇要列印的內容範圍：

x

偵測到您使用的瀏覽器版本是舊於 Internet Explorer 7。要獲得最佳的顯示，建議您升級瀏覽器到 Internet Explorer 7 或更新的版本。

 不要再次顯示此訊息
x

Web 式說明內容版本：SOLIDWORKS PDM 2010 SP05

要從 SOLIDWORKS 中停用 Web 式的說明並改用本機說明，請按一下說明 > 使用 SOLIDWORKS Web 式說明。

要報告在 Web 式說明介面及搜尋中遇到的問題，請聯絡您當地的技術支援代表。要針對個別說明主題提供意見反應，請使用個別主題頁面中的「提供對此主題的意見反應」連結。

0.20.21