Possible to have livereport that query another database (both otcs and another db tables)

Hi Guys,

On sql server we can have sql to query from two different database. When i try to put it on livereport it gave some error. Is this even possible?

My scenario is to compare streamserve table (where document generated) vs document store in OTCS.

THank you.

Regards,

Christopher

Find more posts tagged with

Comments

Nizar Ghazal

Hi Christopher,

I recall performing queries against separate databases, via the TKL functionality of category attributes, so I must assume that the same is possible in LiveReports.

However, I had to first link the databases in the management studio, before this would work. Can you confirm if you've linked your databases, yet?

Thanks.

Regards,

Nizar

appuGmail

Another option might be to check "Live Report Extensions" module made by the WR side of folks.I think it is now the standard for newer CS customers.You would define the external connection

in the web forms area.But Nizar's linked table(DBlink/OpenQuery) is better as LL will think it is calling table in its schema.

Ciao,

..... appnair appunair( Livelink Rocks )

Well, if I called the wrongnumber, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

On Fri, Jul 20, 2018 at 7:49 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Possible to have livereport that query another database (both otcs and another db tables) Posted bynghazal@opentext.com (Ghazal, Nizar)On 07/20/2018 08:45 AM

Hi Christopher,I recall performing queries against separate databases, via the TKL functionality of category attributes, so I must assume that the same is possible in LiveReports.However, I had to first link the databases in the management studio, before this would work. Can you confirm if you've linked your databases, yet?Thanks.Regards,Nizar
[To post a comment, use the normal reply function]
Topic: Possible to have livereport that query another database (both otcs and another db tables)
Forum: Content Server LiveReports Forum
Content Server: My Support

christopher.choo@delawareconsulting.com

My case are same db server instance, but different database. in MS SQL we only need to create a linked db server if we have to query from DB of another server instance, right?

I have not try Livereport extention, how does it work exactly? *Cant read your screenshot Krishnankutty. I need WR to use it?

Anyways I tried to create a DB view (connecting to a different db) on my OTCS db, and it works. Thanks for the idea

Glenn Dieball

So I am seeing to have a reverse issue. I am trying to get a category from TKL on a remote table. Here is the sql statement I am populating in my category

SELECT * FROM OPENQUERY([TAPPL5_ORG_NM] ,

'SELECT * from appora.org_nm_v'

);

This works in livereports and returns the results very well. In the category, however, it gives me an error that it can not execute the SQL statement.

Any ideas?

appuGmail

We use similar queries in ADN TKL minus the semicolon in your statement. The way I look at it is if you go to SQLserver and type sp_linkedservers;

then the LL DB where you are in should resolve it our query looks somewhat like this.LR execution proves it does so in my mind the TKL also should care less.

SELECT * FROM OPENQUERY(OpenText_BLAH,'select MY_NUMBER, MY_DESCRIPTION from <DBUSER>.<DBOBJECT> WHERE '<ADN_TKL_FIELD>' = MY_NUMBER')

The OpenText_BLAH is the particulars to the Remote Source which DBA's help create. If I were you I would isolate one server run thread and connect logs

or even reduce threads and show the error to your DBA unless someone from OT can say if only ADN TKL has this and not the standard TKL.

Ciao,

..... appnair appunair( Livelink Rocks )

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

On Fri, Dec 21, 2018 at 9:41 AM eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

[EXTERNAL] - Re Possible to have livereport that query another database (both otcs and another db tables) Posted byglenn_dieball@mgic.com (Dieball, Glenn)On 12/21/2018 10:40 AM

So I am seeing to have a reverse issue. I am trying to get a category from TKL on a remote table. Here is the sql statement I am populating in my category SELECT * FROM OPENQUERY([TAPPL5_ORG_NM] , 'SELECT * from appora.org_nm_v'

);
This works in livereports and returns the results very well. In the category, however, it gives me an error that it can not execute the SQL statement. Any ideas?
[To post a comment, use the normal reply function]
Topic: Possible to have livereport that query another database (both otcs and another db tables)
Forum: Content Server LiveReports Forum
Content Server: My Support