1

Connect to PATSTAT

To connect to PATSTAT you will need to follow these steps

1 Installing MS SQL Server Express ..................................................................................................... 2

2 Setting the ODBC connection .......................................................................................................... 6

Alternative 1: To be used if you are connecting from your desktop at ODY. ..................................... 6

Alternative 2: To be used if you are connecting from a VPN connection (or without integrated Windows authentication, i.e. Mac OS) .............................................................................................. 11

3 Setting a SQL Client ....................................................................................................................... 16

3.1 Example 1: MS Access ........................................................................................................... 16

3.2 Example 2: MS Excel .............................................................................................................. 19

3.3 Example 3: STATA .................................................................................................................. 22

2

1 Installing MS SQL Server Express

1.1. Go fetch the installer in the shared folder: \\cdmsrv6\CDM-IT_Utilitaires\Applications Utilitaires\SQL Server Express 2008 R2

1.2. Choose according to your Operating System one of the following files : SQLEXPRWT_x64_ENU.exe OR SQLEXPRWT_x86_ENU.exe (x86 for 32 bits OS, x64 for 64 bits OS)

1.3. Copy it locally (optional, but recommended)

1.4. Execute it (i.e. double click).

3

1.5. Click on Yes button.

1.6. Choose New installation or add features…

4

1.7. Check the option “I accept the license terms“ and then click Next

1.8. Choose ONLY the option "SQL Client Connectivity SDK" and then click Next

5

1.9. Click Next again.

1.10. Finally, conclude the installation by pressing Close.

6

2 Setting the ODBC connection

Alternative 1: To be used if you are connecting from your desktop at ODY.

2.1. Go to the Control Panel (Start->Control Panel)

2.2. Choose "Administrative Tools"

2.3. Choose Data Sources (ODBC)

7

2.4. Click Add

2.5. Choose SQL Server Native Client 10.0

(it might appear also as SQL Server only if you installed the x64 version)

and then click Finish.

8

(Attention!!!: It might look slightly different if you installed for the x64 version)

2.6. Write Patstat in the Name field and choose CDMSRV5 in the drop down menu as Server.

Then press Next.

2.7. Choose With integrated Windows authentication (in the x64 case it might say With Windows NT

authentication using network login ID) and then click Next.

9

2.8. Change the default database for “patstatsept2009” in the drop down menu.

2.9. Just click Finish to conclude the ODBC setup.

10

2.10. To test the ODBC connection click on Test Data Source… (This optional but recommended).

2.11. If everything went well, you should see the above message and just click Ok. If you don’t

repeat the full process since the beginning to see what you missed.

2.12. Click Ok in the ODBC Data source Administrator window.

11

Alternative 2: To be used if you are connecting from a VPN connection (or without integrated Windows authentication, i.e. Mac OS)

2.1b. Start your VPN connection. 2.2b. Go to the Control Panel (Start->Control Panel)

2.3b. Choose "Administrative Tools"

2.4b. Choose Data Sources (ODBC)

12

2.5b. Click Add

2.6b. Choose SQL Server Native Client 10.0

(it might appear also as SQL Server only if you installed the x64 version)

and then click Finish.

13

(Attention!!!: It might look slightly different if you installed for the x64 version)

2.7b. Write PatStat_Local in the Name field and write cdmsrv5.intranet.epfl.ch in the Server field.

Then press Next.

2.8b. Choose With SQL Server authentication using a login ID and password entered by the user,

write your username and password and then click Next.

14

2.9b. Change the default database for “patstatsept2009” in the drop down menu.

2.10b. Just click Finish to conclude the ODBC setup.

15

2.11b. To test the ODBC connection click on Test Data Source… (This optional but recommended).

2.12b. If everything went well, you should see the above message and just click Ok. If you don’t

repeat the full process since the beginning to see what you missed.

2.13b. Click Ok in the ODBC Data source Administrator window.

16

3 Setting a SQL Client

3.1 Example 1: MS Access

3.1.1. Open MS Access and select Blank database. Choose a path and filename for the database.

3.1.2. Select the External Data Tab in the Ribbon and then click on ODBC Database.

3.1.3. Important!! Choose Link to the data source and click on OK.

17

3.1.4. Click the Tab Machine Data Source, choose the PatStat data source and then click Ok.

3.1.5. Select only the tables dbo.tls201_appln to dbo.tls219_inpadoc_fam and then click on Ok.

18

3.1.6. MS Access will ask you to select an unique record identifier for table For table

dbo_tls207_pers_appln. Choose only Person_id AND appln_id and then click Ok.

3.1.7. This is a how it should look if everything went well. You are set to start using the data.

19

3.2 Example 2: MS Excel

3.2.1. Open MS Excel Access.

3.2.2. Select the Data Tab in the Ribbon, choose the From Other Sources pull down menu and click on From Microsoft Query.

3.2.3. In the Databases Tab, choose the PatStat data source and click Ok.

3.2.4. Select the table and columns you want to use in your query and see in the output. Then click Next.

20

3.2.5. Select the columns and filters you want to apply in your query. Then click Next.

3.2.6. Select any sorting criteria (optional, as you can do this later much efficiently in MsExcel)

Then click Next.

3.2.7. Select Return Data to Microsoft Excel and then click Next.

21

3.2.8. Chose where to import the data and then click Ok.

3.2.9. This is how it should look if you succesfully imported the data.

22

3.3 Example 3: STATA

The connection with STATA is by far the simplest case. Just start STATA and use the odbc built-in command.

Here it follows some useful examples of STATA’s odbc command (you can copy and paste them in the STATA command editor):

* ODBC connection command description

help odbc

* List of existing ODBC connections in current CPU

odbc list

* Lists tables of a given ODBC source

odbc query "PatStat"

* Lists columns in given table (only after: odbc query...)

odbc desc "tls201_appln"

* executes a SQL statement an loads the results to memory

odbc load, exec("SELECT t.* FROM tls201_appln t where appln_auth='AR'") dsn("PatStat") clear

* A handy T-SQL command is to add 'SELECT top X from (...SQL query...) c' which limits the results to the first X rows

odbc load, exec("SELECT top 100 * from (select t.* FROM tls201_appln t where appln_auth='AR') c") dsn("PatStat") clear

* If you are using the VPN connection then you should pass on every command the user name * and password as it follows:

odbc query "PatStat", user(MyUserName) password(MyPassword)

* or…

odbc load, exec("SELECT t.* FROM tls201_appln t where appln_auth='AR'") dsn("Patstat_VPN") u(MyUserName) p(MyPassword) clear