Advertisements

Adding new article to Existing Publication (Transactional Replication)

A smart guy here at work was working on being able to remove and add an article to our SQL server 2008 R2 server. (We used to do it all the time in 2000)

After much googling he finally hit on this blog that ‘actually works’ (compared to many that didn’t)

http://ansqldba.blogspot.com/2012/02/adding-new-article-to-existing.html

Adding new article to Existing Publication (Transactional Replication)

First of all I ran Exec sp_helppublication in my publication database and checked the following fields,
1. Immediate_sync
2. Allow_anonymous
Both the fields were set to ON as they showed a value 1 which is enabled. If the Immediate_sync is enabled, everytime you add a new article it will cause the entire snapshot to be applied and not the one for the particular article alone.
Usually, the immediate_sync publication property is set to true if we allowed anonymous subscriptions while creating the publication through the CreatePublication wizard. To prevent the complete snapshot, run the script below.
Step :- 1
EXEC sp_changepublication
@publication = ‘Pub_dbAmericasCitrixFarm’,
@property = N’allow_anonymous’,
@value = ‘false’
GO
EXEC sp_changepublication
@publication = ‘Pub_dbAmericasCitrixFarm’,
@property = N’immediate_sync’,
@value = ‘false’
GO
Step :- 2
I added the single article using the below command,
EXEC sp_addarticle
      @publication = Pub_dbAmericasCitrixFarm,
      @article = Table_2,
      @source_object = Table_2,
I got the following error for the above command,
Msg 20607, Level 16, State 1, Procedure sp_MSreinit_article, Line 99
Cannot make the change because a snapshot is already generated. Set @force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.
The reason behind this error message was that there was already a snapshot that was created recently. Since I added a new article it wouldn’t be able to use the existing snapshot so I need to use the option @force_invalidate_snapshot=1 to invalidate the existing snapshot and it would generate a new snapshot to be applied to the subscriber.
EXEC sp_addarticle
      @publication = Pub_dbAmericasCitrixFarm,
      @article = Table_2,
      @source_object = Table_2,
      @force_invalidate_snapshot=1
Step:-3
Now I adding the subscription to the existing publisher for the single table alone using the below command,
EXEC sp_addsubscription
@publication = ‘Pub_dbAmericasCitrixFarm’,
@subscriber = ‘FTDCCWPCTRXSQL’,
@destination_db = ‘dbAmericasCitrixFarm’,
I got the following error message while running the above command in my publication database.
“Specify all articles when subscribing to a publication using concurrent snapshot processing”
This error occurs when the existing publication was set up with concurrent snapshot option and means that you can’t synchronize subscriptions for such publications without a complete resynchronization. There are 2 workarounds: (a) By specifying @reserve = ‘internal’ when you add the subscription for the new article and the snapshot agent should generate snapshot for the new article after that and b.) Changing the sync_method from ‘concurrent’ to either ‘database snapshot’ (enterprise edition only in SQL Server 2005) or ‘native’ (which locks table during snapshot generation). Change the sync_method will force a reinitialization of all your subscriptions at this point. Alternatively you could create another publication and use this instead.
I ran this command and it worked fine,
EXEC sp_addsubscription
@publication = ‘Pub_dbAmericasCitrixFarm’,
@subscriber = ‘FTDCCWPCTRXSQL’,
@destination_db = ‘dbAmericasCitrixFarm’,
@reserved=’Internal’
Now I went ahead and started the snapshot agent in publisher, it worked perfectly. I can now see that only the particular table I added was replicated. So from now on to apply the snapshots of the entire articles we need to reinitialize the subscriptions since the immediate_sync is set to off.
Drop new article to Existing Publication (Transactional Replication)
EXEC sp_dropsubscription
  @publication = Pub_dbAmericasCitrixFarm,
  @article = N’Table_2′,
  @subscriber = ‘FTDCCWPCTRXSQL’;
GO
EXEC sp_droparticle
  @publication = Pub_dbAmericasCitrixFarm,
  @article = Table_2,
  @force_invalidate_snapshot = 1;
For Pull Subscription (Existing Publication and subscription)
First Make sure that the publisher properties “allow_anonymous” and “immediate_sync” are set to “False”, if these 2 options are set to “True” then this SP will mark all the articles for generating snapshot instead of marking only the newly added articles.To Check the publication properties, use this query.
exec sp_helppublication ‘PublicationName’
GO
If the values of the output columns “allow_anonymous” and “immediate_sync” are 0 then they are set to “False” if their values are 1 then they are set to “True”
Add Article using below command
EXEC sp_addarticle
      @publication = Pub_dbAmericasCitrixFarm,
      @article = Table_2,
      @source_object = Table_2,
      @force_invalidate_snapshot=1
–Refresh Subscriptions
exec sp_refreshsubscriptions ‘ Pub_dbAmericasCitrixFarm ‘
GO
After running the above commands, run the snapshot agent.
>> Adding Article in existing transactional replication using entire snapshot regenerate use below method.
Step 1:-
Set properties “allow_anonymous” and “immediate_sync” to true
Step 2:-
EXEC sp_addarticle
      @publication = Pub_dbAmericasCitrixFarm,
      @article = Table_2,
      @source_object = Table_2,
Step 3:-
EXEC sp_addsubscription
@publication = ‘Pub_dbAmericasCitrixFarm’,
–@article = ‘Table_2’,
@subscriber = ‘FTDCCWPCTRXSQL’,
@destination_db = ‘dbAmericasCitrixFarm’,
–@subscription_type = N’push’
 @reserved=’Internal’
Step 3:-
Run the Snapshot agent, it will genrate the entire snashot again for all the article
Advertisements

Some simple examples of #Qlikview, Response to Part 1

Thanks to RJ (@RalphJaquez) and Ralf Becher (@TIQView) I’m starting to understand this a little better.
RJ lays it out well “One way to think about it is that a chart object – no matter what it is, wants to aggregate. Even though you can write an expression that does not aggregate, really the charts power is with aggregation.
When you try to use fields that cannot be aggregated, like text fields, the chart will do things that don’t make sense.”
Following this if you create a table box with Name, Area or Area, Name it will display correctly. In fact I wonder if you can think of a table box as just containing dimensions?
As mentioned in the comments, if you really wanted to do this “I would put both fields as a dimension and then put an expression like =count(Area) or =count(Name)” and then hide the expression.
One other way I pondered, make a dimension Area & ‘-‘ & Name, Area, and expression name. Then hide the Area & ‘-‘ & Name Column.

Some simple examples of #Qlikview, Part 1

There are a lot of concepts with Qlikview that can be baffling at first. I really miss being able to purchase a Qlikview ‘Bible’ (and am surprised no one has written one yet). This means you have to learn things by trial and error.

So I’m going to ruminate and exfoliate and some things I have come across. Maybe others will be able to help clarify my thinking about ‘why’ some things happen.

[SalesPeople]:

LOAD * INLINE [
Name, Area
Raymond, North
Drew,            North
Jakeem,   South
Stewart, South
Murphy,   West
Sam,             West
John,            East
];

First, let’s make a simple chart. Let’s show the names of the Sales Person and what area they are associated with.

That’s fairly easy, create a straight chart, add Name as the Dimension, and Area as the expression.

You will notice the nice dash at the top, this is because it is trying to total the rows. So open up the Expressions tab and click No Totals in the Total Mode. And the dash will go away.

Ok, the challenge, reverse the display of the data. Show the areas that the Sales Person is associated with. Like this (once again straight chart, pivots are a whole different animal)

Now of course your initial thought is probably to set Area as your dimension, and Name as your expression. Like

And

But if you do that you will end up with

Now that’s pretty strange isn’t it 🙂

Create an ad-hoc query and then edit the rows returned sql server 2008 r2

The ‘Edit top 200’ shows an ‘editable grid’ at the bottom. If you click the ‘Show SQL pane’ while that is opened, you can alter the query, which will update the editable grid at the bottom.

Goto

Query designer, pane, sql

To get to the Show SQL Pane

Force a single session or allow multiple Remote Desktop sessions per user

  1. from http://remotedesktoprdp.com/force-single-session-allow-multiple-sessions-per-user
  2. Start Registry Editor (by default, this is located atc:\windows\regedit.exe).
  3. Go to the following registry key:

    HKEY_LOCAL_MACHINE\
    System\CurrentControlSet\Control\TerminalServer

  4. If the fSingleSessionPerUser value doesn’t exist, create a new DWORD value named fSingleSessionPerUser
  5. Open the fSingleSessionPerUser value. The possible values for this setting are as follows:
    0x0
    Allow multiple sessions per user
    0x1
    Force each user to a single session

how to setup autologon 2008 in a domain

From http://www.expta.com/2008/04/how-to-enable-autologon-for-windows.html

Once you join a server to a domain, Windows will automatically delete the AutoAdminLogon value from the HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon registry key. This causes the userpasswords2 control to hide the “Users must enter a user name and password to use this computer” check box shown above.

Here’s how to get the missing checkbox back and configure Autologon:

  • Open a CMD prompt and enter the following (all on one line):
reg add “HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon” /v AutoAdminLogon /t REG_SZ /d “1” /f


  • Click StartRun and enter control userpasswords2
  • Clear the checkbox for Users must enter a user name and password to use this computer and click OK
  • Enter the user name and password that will be used for Autologon and click OK