Can I connect to SQL Views in addition to tables using the database connection?

OL Connect Professional DataMapper
1

Hello,

Just starting with planet press. I want to bring in data from an SQL view instead of just tables. Is this possible? When I connect to our SQL database, it only shows me tables.

Thanks,

Bonnie

2

It should be possible to connect to an SQL View from the Data Mapper

First connect to a Database Table. Then. in the Settings tab of the Data Mapper, click on the Database or Custom SQL icon next to the Table

A new SQL Query Designer window will open, where you can write a custom SQL query and SELECT data from an SQL View.

In the Workflow you may use the Database Query plugin to write custom Dynamic SQL code and also pull data from an SQL View and save it to either a CSV or XML file; which you can then load in the Data Mapper.

3

Thanks Rod! That worked. You are awesome!

-Bonnie

4

My pleasure…Enjoy it :wink:

5

Hi Rod, I had same issue, tried your solution but it didn’t work. My view’s name is view_stampa and when I typed query " SELECT * FROM [view_stampa] " I got message “error occured while executing custom SQL query”. Did I do anything wrong or did I miss somenthing? Please help!

image
image1290×786 39.9 KB

6

With an Access database (which is what you are using), views should be listed in Connect’s SQL Query Designer. I just created a view in a test MDB:

image
image691×206 6.85 KB

In the DataMapper, notice how the MyView query appears along with the tables in the database:

image
image922×592 14.5 KB

So… I am not sure how your view was created in Access. Can you show a screenshot of the Design View in Access (i.e. the first screenshot above)?

7

Hi Phil,

thank you for your reply, I know it should have been accessed but I can not see views at all, only tables. Here is screenshot of database:

Forum
Forum938×628 199 KB

8

Ivan,

I have no idea why it doesn’t work on your end. A few pointers:

  • Make sure you run the latest version of OL Connect
  • Remove the brackets around the view name (i.e. SELECT * from view_stampa)
  • Make sure you can open a regular Table in the DataMapper

If you can’t resolve the issue, then you should contact our Support team to see if they can help you out.

Note: please be careful when posting screenshots. I had to blur parts of the image because they contained identifiable information.

9

Thank you Phil,
I did that with brackets first and it didn’t work, then I tried other things. I will contact the support.
I will be more careful.

Best regards
Ivan