Databases
StackOverflow2013
Postsdbo
POSQL Azure - Determine which tables are federated
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Azure - Determine which tables are federated
    primarykey
    data
    text
    <p>I'm new to SQL Azure and in the early stages of developing an application, so my schema is changing frequently. I started out by creating the root database and executing queries against it like these</p> <pre><code>CREATE TABLE [dbo].[Clients] ( [ClientId] UNIQUEIDENTIFIER NOT NULL primary key clustered default newid(), [ClientName] NVARCHAR (MAX) NULL ); go create federation ClientFederation(cid uniqueidentifier range) go use federation ClientFederation(cid='00000000-0000-0000-0000-000000000000') WITH RESET, FILTERING=OFF go CREATE TABLE [dbo].[Stuff] ( [StuffId] uniqueidentifier not null default newid(), [ClientId] UNIQUEIDENTIFIER NOT NULL default federation_filtering_value('cid'), [StuffName] NVARCHAR (50) NOT NULL, -- bunch (20+) of other fields primary key clustered (StuffId, ClientId ASC) ) FEDERATED ON (cid=ClientId); </code></pre> <p>And that worked out pretty well for the most part. <code>Stuff</code> is just one table among many similar tables in the federation (and not the real name either)</p> <p>Well, then like I said, my schema is changing quite frequently, so to change the schema, I'm connecting to the federation member in VS2012 and right-clicking on the table and choosing "View Code" which renders something like this:</p> <pre><code>CREATE TABLE [dbo].[Stuff] ( [StuffId] uniqueidentifier not null default newid(), [ClientId] UNIQUEIDENTIFIER NOT NULL default federation_filtering_value('cid'), [StuffName] NVARCHAR (50) NOT NULL, -- bunch (20+) of other fields primary key clustered (StuffId, ClientId ASC) ) </code></pre> <p>Note, the only thing different is that after the close parentheses, it no longer says <code>FEDERATED ON (cid=ClientId);</code>. I assumed this was because I'm already connected to a specific federation member so it already knew that information. The weird part is when I tried to run some .net code against it. I'd execute the following code from my app:</p> <pre><code>cn.Execute(string.Format("USE FEDERATION {0}({1}='{2}') WITH RESET, FILTERING={3}", federationName, distributionName, key, filtered ? "ON" : "OFF")); </code></pre> <p>and then using dapper:</p> <pre><code>cn.Query("INSERT Stuff(StuffId, StuffName) VALUES (@StuffId, @stuffName); SELECT * FROM Stuff WHERE StuffId=@stuffId", p); // p has the parameters </code></pre> <p>but then I'd get the following error message:</p> <pre><code>DML statements are not supported on non-federated tables in a filtered connection. </code></pre> <p>Wut? My table is federated, remember? Also, similar code worked wonderfully with other tables. The strange thing about <code>Stuff</code> is that its schema changed A LOT recently, so it seems to me like maybe me connecting to the federation member directly in VS2012 and making changes there somehow made it not a federated table anymore (there are 3 types of tables in a federated database: <a href="http://convective.wordpress.com/2012/03/05/introduction-to-sql-azure-federations/" rel="nofollow">http://convective.wordpress.com/2012/03/05/introduction-to-sql-azure-federations/</a>).</p> <p>So, since I'm early in development, there really isn't anything important in <code>Stuff</code> so I went ahead and copied its CREATE TABLE code and dropped it completely it from that member, went back to the root database and re-executed the code listed above at the top <em>with the <code>FEDERATED ON (ClientId=cid)</code></em> statement again and then re-ran the insert statement from my app and it worked wonderfully!!</p> <p>So, clearly something happened to make my table not be "federated" anymore. In the end my questions are pretty simple:</p> <ul> <li>Is there a query that I can run on the root database or maybe on the federation member to tell me which tables are federated and which aren't?</li> <li>Also, can anyone tell me why my once-federated table is not federated anymore? Because obviously, I may make schema changes in the far future and will not be able to just drop the table and start over, so it would be nice to know what I'm doing wrong.</li> </ul>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJorin
    1. USJorin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Azure - Determine which tables are federated
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL Azure - Determine which tables are federated
      1. USJYL
      1. VTFavorite
    3. VO
      singulars
      1. POSQL Azure - Determine which tables are federated
      1. This table or related slice is empty.
      1. VTUpMod
    1. COVery good question. Exactly the one I was going to ask :) +1.
      singulars
      1. POSQL Azure - Determine which tables are federated
      1. USJYL
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload