By William Fields - 2/25/2011
Hello,
I'm attempting to create my first SF BO Project and am not seeing how I can select a linked server from within the SQL Server Connection String Builder. When I browse to my local SQL instance, the list of databases only show the native SQL databases. The list of linked server objects is not shown.
Can a linked server database be entered manually within the connection string?
Can someone give me an example?
UPDATE:
After creating a data connection in VS2010 to ANY of the native SQL databases on my server instance, I can create queries against the linked server like so:
SELECT * FROM MyLinkedServerName...TableName
So, now I'm just up against how to make this linked server work from within the SF BOM.
Any suggestions?
Thanks.
|
By Michel Levy - 2/27/2011
Hello William,
BOM enumerate the Databases in the server defined by the connection string, not the linked servers.
If you want to get visible the objetcs of a linked server, you should create a view on each table you need in the linked server. The SQL view stays in the "local" SQL database, and is visible in the BO mapper. And then you'll see all the columns of the linked table.
Using that workaround, I have my VFP tables in the SQL server. My BO project is connected on one SQL database (where are real SQL tables),
You may see a BO related to a linked server, where I've configured a specific connection string to another SQL database :
This SQL database is hosting a view to a VFP table in a VFP linked server:
And the final result is a BO where all the columns I need are available:
In summary:
The data are in a linked server (whatever it is, SQL, VFP, Pervasive, ...), a SQL database hosts views to these data, and a BO maps this view.
|
By William Fields - 2/27/2011
Sweet. Thanks Michel. That makes a lot of sense.
After my post, I had started to think about setting up sprocs in the SQL server which queried the linked server tables, but I think views will be a little easier for me to implement.
|
|