Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Stored Procedure help

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Stored Procedure help
 Login/Join
 
Platinum Member
posted
I am trying to use a stored procedure to create a view on a SQL Server 2008 database. The issue I am facing(no error yet, trying to avoid that) is will I be able to create the view in ONE adapter and create it by referencing tables in a SECOND adapter? Here is an example, assume GGSALES and GGORDER are on SRV1:

 
SQL SQLMSS
USE SALES;
END

CREATE VIEW Sales_View(CATEGORY, PRODUCT, STORE)
AS( SELECT DISTINCT CATEGORY, PRODUCT 
    FROM GGSALES JOIN
         GGORDER
            ON STC = STORE_CODE
);

CREATE SYNONYM BI_SOURCE/Sales_View FOR SAMPLES.dbo.Sales_View DBMS SQLMSS AT &SRV2
END



So what I would expect here is Sales_View to be created in the SALES database on SRV2, synonym to be created in the BI_SOURCE application using GGSALES and GGORDER on SRV1. Will there be any issues here? What if I had a second set of tables called GGSALES and GGORDER on SRV2, would THAT be an issue?

EDIT:
Ok, I went and ran the script that does this for me and it appears that I need to find out how to qualify the object with the correct adapter. It seems like the tool is trying to point me at my TARGET adapter when referencing the database for my SOURCE tables. I get an error:
(FOC1400) SQLCODE IS 208 (HEX: 000000D0) XOPEN: 42S02
01/24/2011 09:14:19 DEVAPPS : Microsoft OLE DB Provider for SQL Server: [42S02] Invalid object name 'G
01/24/2011 09:14:19 DEVAPPS : GSALES'.

This message has been edited. Last edited by: Kerry,


WebFOCUS: 7702
O/S : Windows
Data Migrator: 7702
 
Posts: 127 | Location: San Antonio | Registered: May 29, 2009Report This Post
Platinum Member
posted Hide Post
Wow, I really posted a doozy on this one, eh?

I think I may be able to utilize a
 ENGINE SQLMSS SET DEFAULT_CONNECTION 


command, but if I do that, I still am unsure of how I would re-set that within the CREATE statement.


WebFOCUS: 7702
O/S : Windows
Data Migrator: 7702
 
Posts: 127 | Location: San Antonio | Registered: May 29, 2009Report This Post
Expert
posted Hide Post
I never got this to work, but this thread might help you: Joining rdbms tables in different nodes or subsystems. Or this one: View with Linked Servers.

The key is "linked servers". Also look into "OPENDATASOURCE", a SQL Server function.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
Thanks Francis, I was trying to avoid the linked servers because that part is out of my hands. Looks like I have two possible solutions, linking the servers as you suggest or asking someone with write authority to that server to create the views THERE.


WebFOCUS: 7702
O/S : Windows
Data Migrator: 7702
 
Posts: 127 | Location: San Antonio | Registered: May 29, 2009Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Stored Procedure help

Copyright © 1996-2020 Information Builders