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
January 24, 2011, 05:10 PM
T.Peters
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.
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
January 24, 2011, 06:33 PM
T.Peters
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.