Trouble linking SQL View to Smartobject

Hi Martin774,

Can you share a screenshot of what happens when you try to link to a view?

HI Gareth,

See below:

When pressing create all nothing happens.

Kind Regards,

Martin

Hi Martin,

Please can you give me the steps on how to recreate these two tables on my side?

I would like to see if I get the same issue.

Hi Gareth,

See schema below:

Table 1
LogonID
First name
Last name

Table 2
UserId
Name

SELECT
LogonID AS 'Username'
,First name + ' ' + Last name as [Name]
FROM Table1 
INNER JOIN Table3 ON Table1.Username = Table3.Username

UNION

SELECT
UserID AS 'Username'
,[Name] AS 'Name'
From Table 2

Thanks,

Martin

Hi Martin,

I created my tables as follows:

I then ran the following script:

SELECT
[LogonID] AS 'Username',[First Name] + ' ' + [Last Name] as [Name]
FROM Table1
INNER JOIN Table3 ON Table1.Username = Table3.Username

UNION

SELECT
UserID AS 'Username',[Name] AS 'Name'
From Table2

Did you follow this documentation to create the SQL Service Instance?http://help.k2.com/onlinehelp/k2five/userguide/5.0/default.htm#k2_management_site/integration/serviceinstances.htm

The SQL service instance I am using is already registered and has smartobjects linking to tables already.

Also noting the view itself is referencing another Database within the same SQL instance. 

The view is sat in DB1 but references data in DB2.

Hi Martin,

I think I misunderstood you.

1) Did you create a view of the two SQL tables?

2) And now you are trying to create a Smartobject based off the SQL View? 

In the K2 Designer, when I drag the View onto the canvas, I see the following:

I also found this link, which may be helpful:

http://help.k2.com/onlinehelp/k2five/userguide/5.0/default.htm#ServiceBrokers/SQLServer/SQL-Server-Service.htm?Highlight=Views

Hi Gareth,

I created the view which unions two tables (with a join onto a third) as seen on the code I sent in previous post, the view is sat on a database that is registered as a service instance in K2, however the two tables it calls from are within another database that is on the same SQL server instance.