When implementing a solution for ePO, you might need to execute a SQL script against the ePO database. This article provides instructions on how to run a script in SQL using either SQL Server Management Studio (SSMS) or OSQL. SSMS is the preferred option because the interface is easier to use. Without SQL Management Studio, you must use OSQL/SQLCMD commands to run the script. OSQL/SQLCMD is a command-line interface (CLI) for Microsoft SQL databases and is the default for Express versions.

Contents:
Click to expand the section you want to view:

Collect SQL connectivity information from the ePO server.

Regardless of the method used to run the script, you need to know how to connect to the SQL Server hosting the ePO database. This section gives you guidance on how you can pull this information from ePO assuming ePO can successfully connect to the SQL Server.

Use the URL syntax below to access the Database Configuration page on your ePO server:

• https://:/core/config-auth
• https://:/core/config-auth

NOTES:

• The Default SecureHttpPort is 8443.
• Do not include the chevrons (< >) when typing the URL.

1. Start Internet Explorer.
2. Type the appropriate URL and log on.
   
   Example URL: https://ePOServer:8443/core/config-auth
    
3. Make a note of the following entries:
   • Database server name – The name of the SQL Server that hosts the ePO database.
   • Database server instance – The name of the SQL instance for the ePO database.
   • Database name – The name of the ePO database.
   • User Name – The account used to authenticate to SQL
   • User Domain – The domain that the account used to authenticate to SQL is part of. If this field is blank, ePO is using SQL Server Authentication to authenticate to SQL. If it is populated, ePO is using Windows Authentication. 
     
     NOTE: The Database server name is usually the computer name of the server (Example: AVSERVER). But, if you are using a named instance of SQL 2005 or 2008 Express, another entry is listed in the Database server instance field. For example: EPOSERVER.
4. Close the ePO Database Configuration Page.

Connection to the SQL Server hosting the ePO database using SQL Server Management Studio

Usually the SQL Server is already installed. But, if you do not have access to the SQL Server, you can install SSMS on another system and connect to the database remotely, if you have the proper credentials. Or, you can install it on the SQL Server. 

If you do not already have SSMS installed, you can download it for free from Microsoft:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15

When you have SQL Management Studio, you can follow the instructions below to connect to the SQL instance hosting the ePO database:

1. Open SQL Management Studio. The Connect To Server window appears. 
2. Select Database Engine in the Server Type field.
3. Enter the Server name for the server hosting the ePO database in the Server Name field. If you are using a named instance, you need to specify the instance name as well using the format ServerName\InstanceName. See the previous section of this article, if you do not know what to put in this field. 
4. Select the appropriate authentication type in the Authentication field. If you select Windows Authentication, you must be logged on to the computer you are running SSMS from. The account must be using an account that has access to the SQL Server. The account must also have local administrator permissions to both the system SSMS is installed on, and the server hosting SQL (if they are different). This action is not needed if you are using SQL Server Authentication.
5. Populate the Logon and Password fields if you are using SQL Server Authentication. These fields are grayed out and populated with your currently logged on user account if using Windows Authenticatin. 
6. Click Connect.

You can now be successfully connected to the SQL instance hosting the ePO database. See the next section if you require assistance executing a script. 

Execute a script using SQL Server Management Studio

To execute a script using SSMS, use the following steps:

1. Start SSMS and connect to the SQL Server and instance hosting the ePO database. See the previous two sections of this article for instructions. 
2. Expand Databases in Object Explorer.
3. Right-click on the database name for your ePO server and select New Query.
4. Paste the script provided into the query window.

NOTE: If you are running the script to capture the results and provide them to support, you must capture the results in SQL RPT format. This format is readable. To do this click Query, Results to, and File. 

5. Click Execute or press the F5 key​.

NOTE: If you are capturing results to a file, a save to dialog box appears. In the Save as type field, select Report files (*.rpt).Give the file an appropriate name in the File Name field, and note the location of the saved file.

6. Review the Messages tab in SSM for the results of the query. If any errors are displayed in the Messages table, capture the exact error and supply it to support. 

Executing a SQL script file with OSQL/SQLCMD commands

If for some reason you can't use SSMS to execute a script, you can follow these instructions to do the same using OSQL.

NOTES: 

• Scripts are easier to run if they are included in a script file with the extension .SQL. If only the script is provided, copy the text into a text editor such as Notepad and save the file with .SQL as the file extension.
• Make sure that you have sufficient rights to run the script.
• To make sure the successful running of the script, make sure that:
  • You are logged into the SQL Server using an NT account.
  • The NT Accounbt is a member of the local Administrators group on the SQL Server.
• Make sure that you have access to the script file, if available.
• For convenience, save the script file on the root of the C: Drive on the SQL Server.

To run the script file:

1. Click Start, Run, type cmd and click OK.
2. Change directories to the root of drive (C:). Type either of the commands below, and then press Enter:
   
   C:
   cd\
    
3. To run the script file, type the appropriate command below, and then press Enter:

C:\>OSQL -E -S Database_Server_Name -d Database_Name -i c:\script_file
C:\>SQLCMD -E -S Database_Server_Name -d Database_Name -i c:\script_file

Where:

• Database_Server_Name and Database_Name - are the entries previously noted in step 1.
• script_file - is the name of the script file.

IMPORTANT: The OSQL/SQLCMD commands are case sensitive and must be typed exactly as they are displayed.
 

Example: For a script file named testscript.sql to be run on the database ePO4_AVSERVER, the command line would be either:

C:\>OSQL -E -S AVSERVER -i c:\testscript.sql -d ePO4_AVSERVER
Or
C:\>SQLCMD -E -S AVSERVER -i c:\testscript.sql -d ePO4_AVSERVER

 

If you have a named instance of SQL 2005 or SQL 2008 Express, indicated by an entry in the Database server instance field noted in step 1, you must use:

-S Database_server_name\:

Example: For an instance named EPOSERVER, the command would be:

C:\>OSQL -E -S AVSERVER\EPOSERVER -i c:\testscript.sql -d ePO4_AVSERVER
Or
C:\>SQLCMD -E -S AVSERVER\EPOSERVER -i c:\testscript.sql -d ePO4_AVSERVER"
 

NOTE: Different scripts return varying results and can take different lengths of time to run. So, it is beyond the scope of this article to describe output. After the script has executed, you will be returned to the C:\> prompt.