Support Suite

With Microsoft Excel you have the ability to pull information from Savance Enterprise using a SQL query. This functionality will help you gather and organize information in an orderly manner. For starters, you will have to create a stored procedure that gathers the desired information. Then you will have to create an account that can execute the stored procedure. Once the stored procedure is executed, you will be able to display the results in Excel.

Creating SQL User Account:

1. Login to SQL Server Management Studio
2. Drop down the Security folder
3. Right click Logins
4. Select New Login...
   
   
   
   
5. Input your user or group into the login name box
   1. Click the Search... option
   2. Click Object Types...
   3. Check the box next to Groups
      
      
   4. Select OK
   5. Type the user or group in the box
   6. Click Check Names to verify the name
      
      
   7. Select OK
6. Make sure you are using Windows Authentication, if you do not want users to be prompted for credentials
7. Change the default database to the desired database
8. Click the User Mapping option in the left pane
9. Check the box next to your Database
10. Give the desired database db_datareader and db_denydatawriter roles
11. Check the box next to the other Databases
12. Give the other Databases db_denydatareader and db_denydatawriter roles
13. Select OK

Giving Permissions to SQL User:

A query needs to be executed on the database to grant permission to the user. Right click the database and select the New Query option. In the space that is provided, execute the following statement:

• GRANT EXECUTE ON [dbo].[StoredProcedureName] TO [NewLoginName]

"StoredProcedureName" and "NewLoginName" need to be changed to your information.

Displaying Data in Excel

Follow the steps below to gather information from the SQL server using Excel:

1. Click the Data tab
2. Click Get External Data > From Other Sources > From SQL Server
   
   
3. Type in your server name
4. Use windows authentication or type in your credentials
   
   
5. Select the database and any table
6. Click Finish
7. Click Properties
8. Go to the Definition tab
9. Change Command type to SQL
10. Change the table name to the Stored Procedure name
    
    
11. Click OK on the warning message.
12. Click OK on the Import Data window

Now you should see the data in the Excel cells. Every time you refresh the page, the data should refresh.

If you need further support on this issue, please contact Savance Enterprise support.