Hide Table of Contents

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.



Provide feedback on this topic

SOLIDWORKS welcomes your feedback concerning the presentation, accuracy, and thoroughness of the documentation. Use the form below to send your comments and suggestions about this topic directly to our documentation team. The documentation team cannot answer technical support questions. Click here for information about technical support.

* Required

 
*Email:  
Subject:   Feedback on Help Topics
Page:   Retrieving SQL Values from a Linked Server Source
*Comment:  
*   I acknowledge I have read and I hereby accept the privacy policy under which my Personal Data will be used by Dassault Systèmes

Print Topic

Select the scope of content to print:

x

We have detected you are using a browser version older than Internet Explorer 7. For optimized display, we suggest upgrading your browser to Internet Explorer 7 or newer.

 Never show this message again
x

Web Help Content Version: SOLIDWORKS PDM 2012 SP05

To disable Web help from within SOLIDWORKS and use local help instead, click Help > Use SOLIDWORKS Web Help.

To report problems encountered with the Web help interface and search, contact your local support representative. To provide feedback on individual help topics, use the “Feedback on this topic” link on the individual topic page.

0.20.26