Skip to main content

Posts

Showing posts from May, 2011

Partition a table present in merge replication and deploy the changes on production

I have been playing with merge application of late, but for production changes and not on a sample project. Let me first share our environment with you. We have a central server and few number of clients. Server and client share same schema. We have used merge replication to replicate the changes. There were some load issues and thus few changes have to be done. I will list down the change, which I am going to talk about today. Partition a table vertically. Transfer data from the existing table into the new table. Make sure that the network usage (replication) will be as less as possible. This is a normal scenario for those who are working on distributed environment. I am sharing this post for all those who will work on such environments in future . Challenges For once it all seems so simple. We can run a data migration script which will transfer data from table A to table B on client and server and we are good. But, the problem is that then the merge replication will try

Add (Drop) a new article (table) at publisher in merge replication

Let us add the article using the GUI first. First of all create the table on publisher database. Now right click on the publisher and select properties from the menu. Click on articles. Uncheck the - "Show only checked articles in the list" checkbox, in order to see the newly added table. Select the table as shown in the figure below. Press ok The article has now been added to the publisher We now need to recreate the snapshot Right click the publication and select – “View snapshot agent status”. Click start to regenerate snapshot. Now right click on the subscription (I have both on same server you may have on different servers) and select “View synchronization status” Click on start on the agent. The schema changes will propagate to the client if you have "Replicate schema changes" property set to true in the publisher.

SQL Server JOIN basics

Yesterday I took an interview of an EXPERIENCED candidate. I was quite shocked to see the information he has on JOINs. Thus I thought about blogging about the basics of JOINs. The scenario I gave him was - CREATE TABLE #JOIN1 (     ID INT,     [TEXT] VARCHAR(100) ) CREATE TABLE #JOIN2 (     ID INT,     [TEXT] VARCHAR(100) ) INSERT INTO #JOIN1 VALUES(1,'TEXT JOIN 1 - 1') INSERT INTO #JOIN1 VALUES(2,'TEXT JOIN 1 - 2') INSERT INTO #JOIN1 VALUES(3,'TEXT JOIN 1 - 3') INSERT INTO #JOIN1 VALUES(1,'TEXT JOIN 1 - 4') INSERT INTO #JOIN1 VALUES(NULL,'TEXT JOIN 1 - 5') INSERT INTO #JOIN2 VALUES(1,'TEXT JOIN 2 - 1') INSERT INTO #JOIN2 VALUES(2,'TEXT JOIN 2 - 2') INSERT INTO #JOIN2 VALUES(4,'TEXT JOIN 2 - 3') INSERT INTO #JOIN2 VALUES(NULL,'TEXT JOIN 2 - 4') INSERT INTO #JOIN2 VALUES(5,'TEXT JOIN 2 - 5') Note: There is no primary key column. Left Outer Join MSDN defines a left outer join as - Th

Reading/importing tab delimited text file using VBA–Excel

One of my friend, who is a mechanical engineer, had to import a tab delimited text file in Excel. He gets this (these) file(s) as input for God Knows What . He asked for my help and then I realized it is worth blogging about as may be helpful for many of my civil/mech/chem…. friends. So first thing first:- How to create a tab delimited text file 1. Open notepad –> Write text in first column –> press tab –>write in second column and so on. –> save the file with .tab extension. 2. Open excel-> enter values in different column-> Open the File menu and select the Save as... command.-> In the Save as type drop-down box, select the Text (tab delimited) (*.txt) option.->Select the Save button. If you see warning messages pop up, select the OK or Yes button. Here I am going to write a macro to read all the values Sub ReadTextFile()     Dim iRow      As Long   Dim Fname     As Variant   Dim Record    As String   Dim P         As Variant   Dim iCol