When I join tables in different nodes or subsystems, I get the following messages in the SQL trace:

(FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
(FOC2519) THE JOINED SEGMENTS RESIDE IN MORE THAN ONE NODE OR SUBSYSTEM
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED

Is there any way to resolve this?

Can the MS SQL Server WebFOCUS Adapters be defined in such a way that the two different servers are defined within one adapter?

Or, is there a non-WebFOCUS solution - is it possible to create a view in one server that points to another server? I could then create a WebFOCUS synonym on the view.

All ideas welcome.

Thanks,

Francis.

Francis,

Are you trying to declare a WF master for a view as it sounds? I would normally resort to pulling the data back independantly and then joining / merging as necssary.

T

I agree with Tony. WF still thinks it is a cross-platform join and disables optimization.

I was just looking for a way around pulling the data from each server and then joining.

Francis,

You can only define one server per adapter.
Each new server would be a new adapter in the MS SQL adapter list.
But, what you can do is define a view within MS SQL that spans several servers.
In MS SQL the technique is called a 'distributed view'. If you google on that term you'll find enough links to guide you through that process.

Hope this helps...

quote:

You can only define one server per adapter.

GamP,

That statement is only true for some adapters such as Teradata. However, in our shop we have connect strings to many SQLMSS servers defined in our profile.

That does not solve Francis' problem however. Your suggestion sounds like a great solution for him!

GamP, I will investigate "distributed views".

Thank you.

Hi,

if you are using sql server

1/ Go to server A and define server B as a linked server
2/ On server A create the view that collecte data from tables on both servers. This is known as distributed query.
Note: if you have a table T1 on server B, when creating the view you have to reference it like this: select * from B.DATABASENAME.USER.T1
3/ On webfocus create an adapter pointing server A
4/ Create a synonym on that view

5/ Create your report.

Regards.

Hi,

If you are using sql server.

1/ Go to server A and create server B as a linked server
2/ On server A create a view that collect data from from both servers.
3/ On webfocus create an adapter that points server A.
4/ create a master file on the view you created
5/ Create the report on that view an

GamP and Majid,

I am looking into the linked or distributed server option.

It sounds so easy - aren't there any restrictions I could be faced with?

Thanks,

Hi Francis,

I am very familiar with this since we have sql server all over Europe and we are launching sql queries joining deferrent tables lacated on different servers.
All you have to do is :

1/ go to the server from you will launch the distributed query.
2/ Under security click on linked servers
3/ define there as many servers as you want.Only be sure to user a user with correct rights
4/ build your query or view

5/ create an adapter on the server where you defined the linked servers
6/ create a synonym on that view
7/ From here everything will be transparent for webfocus server and it will be in the hand of sql server.

Should you have adavanced questions regardin sql server please do not hesitate.

Majid.

quote:

Only be sure to use a user with correct rights

- that's the key to get into this club!

I will try this out sometime over the next couple of days and let you know if I succeeded.

Thanks,

Francis.

Well, that was easy!.

The linked server set-up was already done by a previous genius. I simply created a view on Server A pointing to a view on Server B. As mentioned, I had to specify the server in the view:

SELECT * FROM server.database.user.view

But of course, there's a problem. I can retrieve data from the view in Server A that points to the view in Server B when in SQL Server Enterprise Manager. I can generate the Master via the WebFOCUS server console. But when I TABLE the master, I get the following error:

(FOC1400) SQLCODE IS 7312 (HEX: 00001C90) XOPEN: 01000
: Microsoft OLE DB Provider for SQL Server: [01000] [OLE/DB provider retur
: ned message: New transaction cannot enlist in the specified transaction
: coordinator. ] [01000] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IT
: ransactionJoin::JoinTransaction returned 0x8004d00a]. [42000] The operat
: ion could not be performed because the OLE DB provider 'SQLOLEDB' was un
: able to begin a distributed transaction. [] Deferred prepare could not b
: e completed.
L (FOC1406) SQL OPEN CURSOR ERROR. : CRMTEST1

Francis,

Now that you have completed your self training course, may I draw your attention to this post and the one following it?

T

Hi Tony,

Sorry I did not understand. Do you mean what I suggested would not work. If yes then I am really surprised.

This is working for us and I will continue with Francis till it work for him.

Regards.



Regards.

Majid,

I have not made reference to your possible solution for Francis, let alone that it wouldn't work. I merely point out that my approach is to leave a supportable solution for my Clients when I leave them. Often this means that I steer well clear of setting up convoluted solutions that the existing skill set of the permanent members of staff might not cover.

When I leave a Client, for my own conscience, I have to know that what I leave works and is supportable as well as having any documentation that might be required. By doing this I know that I have supplied the best possible service that I can, and hopefully, the Client will continue to have a good opinion and will think of me first when they require WebFOCUS consultancy

T

Tony, I wouldn't really consider this a "convoluted solution". It is well documented and appears to be easily performed. The client had previously created the linked servers, I would love to use its' potential in WF. As it stands, somehow the WF data-adpater cannot access a view that SQL Server Enterprise Manager can. I tried SQL pass-through and that didn't work either.

Francis,

I will get back to you soon.

Regards.

Hi Francis,

Which sql server are you using? is it 2000 or 2005.
I did a test on sql 2005 and it is working exactly as I described it.

Also can you post the script of your view.

Regards.

Majid.

quote:

I wouldn't really consider this a "convoluted solution".

I didn't say it was. However, some folks are not confident or are really end users with access to a basic level of WebFOCUS.

T

Hi Tony,

Sorry if I misunderstood something. You know English is not my mother language and sometimes I read things which are very hard for me to understand corrcetly.
Back to webfocus. When you have 2 adpaters one is A pointing sql server A an another is B pointing sql server B. Then you create a synonym S1 on A and S2 on B then you create a report which joins both of them.
Doing so you will be impacting the performance since you are not taking advantages from the indexes on sql server.
Creating a view which selects data from s1 and joinning it to s2 will have a great impact on the performance.
This is because when you create a view, sql server compiles it,optimizes it then stores the execution plan and you can take benefit of evrentual indexes on the tables the view is based on.

Regards.

Majid,

No need to apologise at all. Your English is actually very good, and it's good that you are willing to assist someone with technical details that they need. It is a common characteristic that all forum posters share


For information, my previous comment was in response to Francis.

T

here's this.
http://msdn2.microsoft.com/en-us/library/ms188279.aspx

I've had a DBA do it in the past (nothing to do with webfocus) not sure how the optimizer works in this method because we simply were moving data between the two databases, not querying both in one statement.

I have, however, had to do this in an Oracle environment using SQL. it's really simple, CREATE DATABASE LINK and some parameters. I'm sure MS put something like this together. I do know that the Oracle optimizer will take the stats and indexes of the linked table into account when it builds the query plan. It's pretty spiffy. Anyone doing db work in peoplesoft over oracle would vouch to the value of database linking.

The strange thing is that I was able to create the view pointing to a view in a databse on the second server. I ran it successfully via SQL Server Enterprise Manager. Then I tried to create the WF meta-data and got the SQL error I posted. Later, I tried modifying the view and got the same SQL error as the one generating the meta-data. I haven't had time to follow up.

Francis, please post your entire SQL error message that sql server is generating.

I'm thinking it has something to do with permissions.

quote:

(FOC1400) SQLCODE IS 7312 (HEX: 00001C90) XOPEN: 01000
: Microsoft OLE DB Provider for SQL Server: [01000] [OLE/DB provider retur
: ned message: New transaction cannot enlist in the specified transaction
: coordinator. ] [01000] OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IT
: ransactionJoin::JoinTransaction returned 0x8004d00a]. [42000] The operat
: ion could not be performed because the OLE DB provider 'SQLOLEDB' was un
: able to begin a distributed transaction. [] Deferred prepare could not b
: e completed.
L (FOC1406) SQL OPEN CURSOR ERROR. : CRMTEST1

I don't have access to SQL Server Enterprise Manager at the moment - I'd like to post the SQL of the view that I was able to create once but not update.

Hi Francis,

If you have a chance to access the enterprise manager, try copy the source of the view to query analyzer, delete the view, do the modification you want and then launch the script to create it again.
Then login to query analyzer using the same user used in the sql adapter on webfocus and try to query the view. If you succeed then there is no reason for this not to work in webfocus.

Majid.

I know this thread was from 2007, some three years ago, but I have just had the same SQL error produced when trying to query a SQL 2008 Server linked though a SQL 2000 Server from webfocus using a master file on a view.
I also found the solution, so thought it might be handy to post it here just in case someone else has the same problem at some point.

These links are what solved the problem for me, I needed a combination of info from both:

http://support.microsoft.com/kb/816701
http://support.microsoft.com/d...scid=kb;en-us;329332