Expand my Community achievements bar.

SOLVED

Hello, is it possible for Adobe Campaign access external database views instead of extrenal database tables. Currently I'm getting an error.

Avatar

Level 2

Hello,

Is it possible to get access to external views of a database by FDA instead of tables ?

Currently I'm getting an error.FDA_TestConnection.PNG

1 Accepted Solution

Avatar

Correct answer by
Employee

Hi tristanr13398296,

Keeping your requirement in mind we performed a simple test and here are our findings.

  1. Our local campaign instance is using PostgreSQL as database and the FDA is SQL Server with the version as SQL Server 2012.
  2. We attached a test database on SQL Server, the very famous AdventureWorks DB.
  3. For our test, we chose the table Person.Address. It has following columns. FDA table.png
  4. Created a View on top of it and named it dbo.vAddress. Its structure looks like as shown here:FDA View.png
  5. As you can see we have picked a subset of columns for our purpose.
  6. Using the FDA connection inside Adobe Campaign, we deployed a new schema based on the new view just created.FDA Schema.png
  7. Now, if we take a look at the structure tab it shows only the columns we had in the database view.View structure.png
  8. As soon as we switch to Data tab to see if the data is being extracted or not we get the following error. error.png

    The reason for this error is that Campaign assumes a view to being a table and hence it is requesting for a Primary key to be defined on it. Inside SQL Server, a view is not supposed to have a PK.
  9. To resolve this issue, we will perform a small hack. We will make campaign believe that there is a PK involved. To do so, in our view we have a column rowguid that is the primary key and hence inside schema we will make it be primary key of this view. Please mind, that we will make modification only on the campaign schema and not on the actual database view.hack.png
  10. Logout from the instance and log back in. This time if you preview the data the problem will be resolved.data.png

Hope this answers your query.

Regards,

Vipul

View solution in original post

2 Replies

Avatar

Correct answer by
Employee

Hi tristanr13398296,

Keeping your requirement in mind we performed a simple test and here are our findings.

  1. Our local campaign instance is using PostgreSQL as database and the FDA is SQL Server with the version as SQL Server 2012.
  2. We attached a test database on SQL Server, the very famous AdventureWorks DB.
  3. For our test, we chose the table Person.Address. It has following columns. FDA table.png
  4. Created a View on top of it and named it dbo.vAddress. Its structure looks like as shown here:FDA View.png
  5. As you can see we have picked a subset of columns for our purpose.
  6. Using the FDA connection inside Adobe Campaign, we deployed a new schema based on the new view just created.FDA Schema.png
  7. Now, if we take a look at the structure tab it shows only the columns we had in the database view.View structure.png
  8. As soon as we switch to Data tab to see if the data is being extracted or not we get the following error. error.png

    The reason for this error is that Campaign assumes a view to being a table and hence it is requesting for a Primary key to be defined on it. Inside SQL Server, a view is not supposed to have a PK.
  9. To resolve this issue, we will perform a small hack. We will make campaign believe that there is a PK involved. To do so, in our view we have a column rowguid that is the primary key and hence inside schema we will make it be primary key of this view. Please mind, that we will make modification only on the campaign schema and not on the actual database view.hack.png
  10. Logout from the instance and log back in. This time if you preview the data the problem will be resolved.data.png

Hope this answers your query.

Regards,

Vipul

Avatar

Level 2

Thanks you very much, it has worked for my issue.

Cheers

Tristan