2.71 FAQ-800 How to set up the SQL?

Last Update: 7/18/2018

Origin provides several tutorials which introduce the easy-to-use tools for database access. To practice these tutorials on your own computer, you will use the AdventureWorks database, and the following section will introduce how to configure your SQL Server, and how to attach an AdventureWorks database on your SQL Server and connect it to Origin.

Download the AdventureWorks database

Download the AdventureWorks database from this link, please select Adventure Works 2014 Full Database Backup.zip for downloading, and unzipped in your computer disk, eg: E:\. The unzipped file will be AdventureWorks2014.bak.

Set up your SQL Server

Before you install the AdventureWorks database, you must have a SQL Server instance installed on your local machine. The following steps will introduce how to install Microsoft SQL Server 2014 Express:

Note: You operating system should be Windows 7 or higher version.

  1. Open the download website, you may need to log in with your Microsoft account, then click DOWNLOAD.
  2. select the file ExpressAdv 32BIT\SQLEXPRADV_x86_ENU.exe(for 32 bit system) or ExpressAdv 64BIT\SQLEXPRADV_x64_ENU.exe (for 64 bit system), then click Next to start download.
  3. There is an short Install Instructions in the downloading page, for your reference. You can accept all default settings when installing.
  4. When the installation finished, please go to the Windows Start menu, click All Programs, and browse to Microsoft SQL Server 2014: SQL Server 2014 Management Studio to open the SQL Server.
  5. A Connect to Server dialog will pop up for you to specify the connection setting, please select Server type and Authentication as graph below, then click Connect.
    SQL initialize.png
  6. Right click the Database in Object Explorer panel, and select Restore Database to open the dialog.
    SQL restore.png
  7. Select the device in source section, browse and add the file AdventureWorks2014.bak we have downloaded in previous section. Click OK to apply the settings.

SQL database.png 8. Now we have imported the database successfully.

Configure the Data Sources (ODBC)

  1. Click the Start button in Windows, type in Data Sources in Search programs and files box, then you will find this application in the list, click the item to open the dialog.
    SQL source.png
  2. Go to System DSN tab in the dialog, click Add to open the Create New Data Source dialog, select ODBC Driver 11 for SQL Server
    SQL odbc.png
  3. click Finish. Then a new dialog will pop up, please specify the Name, say: New_test and select (local) as Server.
    SQL odbc2.png
  4. Click Finish, then ODBC Microsoft SQL Server Setup dialog will pop up, you can click the Test Data Source button
    SQL odbc3.png
    If everything goes well, the message dialog will say that: Test completed Successfully!. Click OK in each dialog to apply the settings.
    SQL odbc4.png

Connect the database in Origin

  1. Open Origin, click Button Open SQL Editer.png in the Data Access toolbox to open the SQL Editor. Click Db editor connect.png to open the Data Link Properties dialog, select Microsoft OLE DB Provider for ODBC Drivers:
    SQL odbc5.png

click Next to specify the Connection, select New_test in Use data source name, and select AdventureWorks2014 in Enter the initial catalog to use.

  1. SQL odbc6.png
  2. You can click the Test Connection button, if the connection succeed, the message will say: Test Connection Succeed!
  3. Click OK to apply the settings. Just click OK in Login window. You will see a group of table shown in the right panel of the dialog.
    SQL odbc7.png
  4. Enter the SQL query in the left Input box for a test:
SELECT 
    e.BusinessEntityID, p.FirstName, p.MiddleName, p.LastName
FROM 
   HumanResources.Employee AS e INNER JOIN 
   Person.Person AS p ON p.BusinessEntityID = e.BusinessEntityID
  1. Click the Preview the result data button SQL editor preview.png to preview the data. If the preview is fine, click the Import data to worksheet button Button db Import Data.png to import these data. The results will be like this:
    SQL odbc8.png