SQL Execution error Table not found

From Sage Intelligence Knowledgebase
Jump to: navigation, search

ISSUE: TABLE/VIEW NOT FOUND


SYMPTOMS: When trying to run a report out the following type of error is received:


Sqlexecerror1.png


CAUSE:

There is no SQL server login/user created for connection to your Schema.


RESOLUTION/WORKAROUND:

If you are creating a manual connection in Alchemex to connect to a Database where there are generally multiple schemas. You have to create a separate login in SQL server to connect to a single schema. This will then resolve the error:


SQL SERVER CONFIGURATION: Open SQL Server Management Studio: 1. Create a New login: From the SQL Server object: Select Security Right click on Logins Select New Login Create a new login for the SQL server, selecting SQL Server authentication, Set the Default database to the database 2. Create a new User: From the database object: Select Security Right click on Users Select New User Create a new user, providing a name (make it the same name as that created in the server object above) a. Make sure “login name’ is selected and using the browse button on the right select the login name which you created in the SQL server object above

b. Default Schema – make sure you select the default schema name for the database you are working with. (dr)

  c. Do not select any schemas in the Owned Schemas box 
  d. In the Database role membership box select db_datareader 
  e. Click OK.

3. Use this new user in the properties of your connection in Alchemex.