Accessing Federations in SQL Azure using Entity Framework

Accessing Federations in SQL Azure using Entity Framework

Rate This
  • Comments 4

Entity Framework is a popular these days in many web applications and I get EF support in federations question a few times a week these days… Here is a quick collection of of articles on the topic;

This post just came out on the Entity Framework and Federations on the ADO.Net team blog;

SQLCAT folks, James, Rick and others;

A few of the limitations and workarounds are listed here;

  • Hi Guys,

                    I have created a table named  'MyStable1' using federation 'MyStable_Fed'  and distribution name 'StableID' . I want to use it in a stored procedure  in original database (In which federation is created).

    create procedure MyProc

    as

    EXEC sp_executesql N'USE FEDERATION MyStable_Fed(StableID=''00000000-0000-0000-0000-000000000000'') WITH RESET,FILTERING = OFF';

     GO

     exec  sp_executesql N'SELECT MyStableID,TypeID,TypeName FROM MyStable1'    

    but it is not working.

    Any help will be greatly appreciated !

  • Hi Ranjeet, I am trying to get the documentation clarified on this. You cannot use USE FEDERATION statement in dynamic sql or inside a stored procedure. You need to execute USE FEDERATION in its own batch. This is a restriction for other distributed statements like CREATE DATABASE, CREATE USER, CREATE LOGIN, CREATE FEDERATION etc.

  • Hi Cihan,

                      Thanks for reply. After executing 'create federation..' statement  we can see the federation database from original database using the statement like

    USE FEDERATION MyStable_Fed(StableID=''00000000-0000-0000-0000-000000000000'') WITH RESET,FILTERING = OFF

    GO

    Select db_name()

    Isn't there any way to use federated table in original database as we don't want to create all the tables that references federated table ('MyStable1') in federated database.  

  • If I understand what you are asking the answer is no. Basically root and members all have private schemas. there is not object that is globally visable across federation members and root. when you connect to root you see object there and when you connect to member you only see objects created in that member. Feel free to email me with more questions at cihan.biyikoglu@microsoft.com.

Page 1 of 1 (4 items)