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,

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.

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.

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.