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.
- Open the download website, you may need to log in with your Microsoft account, then click DOWNLOAD.
- 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.
- There is an short Install Instructions in the downloading page, for your reference. You can accept all default settings when installing.
- 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.
- 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.
- Right click the Database in Object Explorer panel, and select Restore Database to open the dialog.
- 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.
8. Now we have imported the database successfully.
Configure the Data Sources (ODBC)
- 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.
- 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
- click Finish. Then a new dialog will pop up, please specify the Name, say: New_test and select (local) as Server.
- Click Finish, then ODBC Microsoft SQL Server Setup dialog will pop up, you can click the Test Data Source button
- If everything goes well, the message dialog will say that: Test completed Successfully!. Click OK in each dialog to apply the settings.
Connect the database in Origin
- Open Origin, click in the Data Access toolbox to open the SQL Editor. Click to open the Data Link Properties dialog, select Microsoft OLE DB Provider for ODBC Drivers:
click Next to specify the Connection, select New_test in Use data source name, and select AdventureWorks2014 in Enter the initial catalog to use.
- You can click the Test Connection button, if the connection succeed, the message will say: Test Connection Succeed!
- 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.
- Enter the SQL query in the left Input box for a test:
e.BusinessEntityID, p.FirstName, p.MiddleName, p.LastName
HumanResources.Employee AS e INNER JOIN
Person.Person AS p ON p.BusinessEntityID = e.BusinessEntityID
- Click the Preview the result data button to preview the data. If the preview is fine, click the Import data to worksheet button to import these data. The results will be like this: