Create the PI AF SQL Server database manually

You can create the PI AF SQL Server database by running the AF SQL scripts from the command line.

To run the AF SQL scripts you must be authenticated. The default approach is Windows authentication. It requires no special action on your part. The other approach is to authenticate directly to SQL Server, which requires you to provide credentials when you execute the SQL scripts.

To run the necessary scripts from the command line, you must first install the sqlcmd utility. For information about installing sqlcmd, see the Microsoft article sqlcmd Utility.

1. Open a command prompt window.

2. Cd to the %PIHOME64%\AF\SQL folder.

   The PI AF installation process places the AF SQL scripts in the %PIHOME64%\AF\SQL folder.

3. Use the following syntax to execute the AF SQL scripts:

   GO.bat "<SQLName>[\<SQLInstanceName>][,<Port>]" <PIFD> [<SQLUserName> <SQLUserPassword>]

   where:

   <SQLName> is the name of the SQL Server that hosts the PI AF SQL Server database.

   \<SQLInstanceName> is the SQL Server instance. This value is only necessary if the SQL Server you are accessing is a non-default (or "named") instance. For example: MySQL\MySQLInstance.

   <Port> specifies a port number. If you are specifying a port number, enclose the entire string in double quotes. For example: "MySQL,4452" or "MySQL\MySQLInstance,4452".

   <PIFD> is the name of the PI AF SQL Server database. By default the name is PIFD, but beginning with release 2018 SP2 you can assign any name to this database.

   <SQLUserName> and <SQLUserPassword> are the login credentials for SQL Server. Specify these values if SQL Server authentication is required. If these values are not provided, the scripts use Windows authentication to connect to SQL Server.

   Here are examples of the syntax needed to execute the scripts:

   • Syntax using SQL Server authentication:

     GO.bat "MySQL\MyInstance,4452" PIFD-2018 MySQLLogin MySQLLoginPwd

   • Syntax using Windows authentication:

     GO.bat MySQL PIFD

The process is complete when the command line looks like:

PI AF and PI AF SQL Service SQL script execution completed successfully.
Log file:
"C:\Program Files\PIPC\AF\SQL\PIAFSqlScriptExecution_ 2016_09_15_14_33_52.txt"

C:\Program Files\PIPC\AF\SQL>_

If you run the go.bat command as an Administrator, the SQL script execution log file is saved in the same folder as the SQL scripts. Otherwise, the log file is saved in the %Temp% folder.

The log file is a .txt file that uses the following naming convention : PIAFSqlScriptExecution_[year]_[month]_[day]_[hour]_[min]_[sec].txt