An Agile process evaluation

In the recent past, I have been working as a scrum master for one of the projects. And since there were two folds to this project, a portion being handled onsite as well as a portion being handled offshore, there were initial hiccups that gradually deteriorated away. But here comes the crux of the problem and where an Agile process has to be evaluated.

        We had weekly sprints where a subset of tasks were to be completed and we have a daily scrum between the offshore and onsite team. But the onsite team were persistent on a daily mail sent with the changes being addressed. Now the point of the scrum is to take into account who, what and where the changes have to be made based on the product backlog. Now having status mails as part of the Agile process is indeed a flaw in the fundamental flow. After pointing out this fact, I was counter attacked with several points (some of which were valid to have a status update email) and finally left to comply. Now here comes the secondary flaw and something which had a major impact in sprint. The onsite team demanded daily code reviews of the work done offshore. Now this caused a major butterfly effect in the scrum process. First the offshore team needs to comply to the tasks that need to be performed and then perform a code merge of all the developer's changes (again done by one of the team members) which put a lot of stress on the team member doing the merge. I tried to stream line this by asking different members to perform this on a daily basis, but eventually it could not be done in this fashion as well as even suggested a bi-weekly drop which was given a thumbs down. This led to a lot of down time and a morale let down as even I could not prevent this from happening as the scrum master of the team. I could not shield the team from this negative impact. And the other intake I could make was that the code sent for review was not being reviewed on a daily basis (this is the icing on the cake). I on the other hand had to ensure that a few deliverables on my plate did meet the deadline and could not help the offshore team in making changes to the Agile workflow we had in place. Then came the impact of managing the project with TFS (which I definitely had my eyes on) since it became easier to track down the deficits as well as generate charts to figure out where we were heading towards each sprint. But this was turned down by let's say the new product owner who wanted to do this via project plans. With all this and all my say not being looked at, we struggled to get the project to production ready with very minimal glitches.

                      I think the biggest mistake was my accepting responsibility as the scrum master. The scrum master must be the most strong willed member of the team and should say "NO" when the moment calls for it. But then comes to adapting to the organization's work culture which ultimately led to the undoing of the Agile process. I gave in more to the culture of the organization than the needs of the project which might happen in the future as well. But the next time I will more geared and in case I see the red lights propping up, I am going to step down as the scrum master if I were made one. Also in the case of the pigs and chickens analogy, the chickens had the major say in this case which again is an Agile flaw. But this has left me with quite a few questions unanswered:

1. Corporate Culture VS Agile project management. Which needs to be given more priority and Why?
2. Do all the team members have equal status in the Agile process or are there scenario's where certain team members should have more pull?
3. If point 2 holds, what could be scenarios this could occur?
4. Product owner VS Scrum Master in making product design changes based on the sprint timing. Who should have more hold?

Based on my understanding of how Agile works and some of the books I read, I get varying answers to these questions. I think it something that I need to ponder on and figure out an approach myself. Stay tuned to a future article about my insights into such situations….

Presidential elections and its impact on the s/w industry

Been following the recent political scenario in the U.S for the past couple of weeks. Obama's re-election as the most powerful man on the planet does have a lot of impact across various industries. Kudos to Romney for giving a great fight. There were a couple of aspects related to Romney's foreign policies that I personally did not like but overall the man would have made a good president. Obama was definitely my pick to win the presidential elections and take things over for a second term. I just hope he does not get blocked while promoting his various plans to rebuild the U.S. The only problem is that he cannot start afresh, he has to ensure that the policies that had created such a massive fiscal deficit in U.S history is keenly looked into and changes be made to promote more growth especially in sectors that had taken the brunt of the beating while the republicans were at the helm. In the software industry barring innovation, it is important that skilled expertise is continued to be sought rather than try and improve the skill sector in the U.S. This thought is coming along because in order to get that workforce in place especially for all the Americans, it is a lot of time and effort and certainly not possible to do so in the next 4 years. Also putting additional pressure on the services industry where China and India are the key players will not improve the U.S economy. It needs to constantly get expertise from these countries for a small period of time till the software industry gets into its stride. Also promoting local manufacturing for hardware is extremely important for the U.S. Regardless of the cost factor involved companies like Apple, Google and Microsoft should ensure that majority of the manufacturing takes place locally rather than exporting these jobs outside the U.S.  Companies like Samsung, Lenovo etc... should also try and start creating manufacturing jobs inside the U.S, it does not have to be the entire hardware but some aspects of the hardware which can be outsourced from these countries. India has to seriously put some thought into ensuring more innovation continues in its market space rather than focusing on only services. If this is going to be the trend for India, then its shelf life will come to an end sooner than later. Innovation is one of the key factors for the software industry. Also competition in various markets globally for different software products will also improve the drive and consistency among software makers. The software life cycle is changing at a rapid pace and we software professionals need to gear up for the ride!!!!

PowerPivot in Excel 2013

Man O man!!!! I have recently started playing around with Office 2013 and I love it. It has a lot more facets that make regular excel user's lives easier. But this post is about an issue of using PowerPivot in Excel 2013. I started opening existing PowerPivot workbooks (created in Excel 2010) in Excel 2013 and whenever I navigated to the PowerPivot ribbon, all the options were always disabled. I thought for a second that there might have been a problem with the installation and I might have missed an option while installing Office 2013. But then barring the fact that Visio 2013 is still a seperate installation, there was  absolutely no problem with my Office 2013 installation. Then I tried reopening the workbooks in Excel 2010 to just verify that these were not corrupted in any fashion and thank the gods above!!! they weren't. So that got me thinking that the consumer preview of Office 2013 might not have PowerPivot enabled. Was just about to ping someone in the Office team when a post by Jason Volpe caught my eye..... It seems that PowerPivot for Excel 2010 and 2013 are two different add-ins. I probably would never have guessed this. So to enable PowerPivot in Office 2013....just follow the steps in Jason's post as mentioned in the URL below:

http://www.jasonvolpe.com/2012/07/how-to-activate-powerpivot-in-excel-2013/

I am now in the process of evaluating PowerView in Office 2013 (oh yeah!!! No longer only integrated with SharePoint) making it easier for offline users to create powerful visualizations with PowerPivot data.

So when you try and open a PowerPivot workbook (from 2010) without a pivot table or a pivot chart in it, you might run into a couple of issues with Excel 2013. Save the PowerPivot workbook (in 2010) with a Pivot chart or Pivot table in the workbook and the migration will be successful. The next issue while creating a Power View dashboard within 2013 might lead to a memory exception the first time. Close all the instances of Office 2013 and then open Excel 2013. Create a PowerPivot and a Pivot table or chart associated with the same or open an existing PowerPivot workbook. (Prior to this ensure that Microsoft PowerPivot for Office 2013 and PowerView is checked as shown in the screenshot below)

 After this when you click on the Power View option it will take a while for the Power View to jet start but once it does ......... lets get ready to Ruuuuuuuuumble!!!!!!!

Metro Style Apps in Win 8

Metro style apps in Win 8 is going to become a big hit especially with the developer community and especially with Microsoft promoting these apps in a major way through its Windows store....only good things are in store for most of us. Metro apps basically works seamlessly on top of the following languages which MS is trying to revolve the developer community with. Those languages are C#/VB, C/C++ and Javascript. The structure of  metro apps is in the following fashion:

If we were to design an website to perform some sort of task such as displaying information from a database. The HTML and CSS page can be developed with any sort any editor (prefer Visual Studio 2012 to display the info.... or even Blend). But once the page is developed you will have to explicitly define function calls to the WIN RT layer which provides a host of API's to seamlessly integrate the application with the OS. Somewhat of a framework on top of a framework.

The next aspect is about the projections that these Metro Apps make. Like I had explained once a long time back on how the .net framework was like an onion with layers and layers of skin and each layer signifying a specific functionality within the framework.Once a language projection is created you consume the WINRT API's. But what is this projection???? I hope the below diagram gives a more reasonable explanation. The C/C++/C#/VB.Net run in the same manner in which they are created, however once it is projected onto the WinRT, the WinRT contains metadata to understand more about the type of projection it has been provided and has a seperate metadata layer to interact with. Well a projection can be defined as hologram (or virtual entity) of your program on top of the WinRT framework. Basically it is similar to a projector projecting an image on a screen. Let me show this with an image (an image is worth a 1000 words.... hopefully it should be the case with this one as well :) )

Redundancy has been removed from the WINRT library so that it does not perform the same operations as the base class libraries. Also portable class library allows fluidity of moving and sharing code between various platforms based on the view model design pattern. Extension methods bridge the gap between WINRT and the native base class library.
There is the new concept of winmd files that target the .net runtime directly. So there is no requirement for applications to refer to dll's but they can refer to winmd files directly. The winmd file represents the WINRT API. Also this benefits smoother deployment between different environments (environments in this case involve dev/test/prod etc...). Well that sums up my post on Win 8 Metro style apps for now. And by the way won an XBox game at the Win 8 Hackathon yahooooooo!!!!!!

Duplication of KPI's within PPS

Has anyone ever come up with the of duplication of KPI related information after dragging and dropping two seperate KPI's within a scorecard......... well! I have. Now the issue is quite simple, I define two separate KPI's called KPI1 and KPI2. I define KPI1 in the following manner:

After this step I define KPI2 in the following manner:

Once I perform the following operations I basically define a scorecard dropping a dimension (eg: Brand) for this purpose and then drag KPI1 and then KPI2 (Note: KPI2 is the sibling of KPI1). The scorecard gets generated in the following fashion:

Now if you notice the scorecard the Actual and Target is repeated for KPI2 even though KPI2 has the names defined as Test1 and Test2 respectively and the default values for KPI2 is empty for all rows of the slicing dimension. This issue is due to the fact that PPS did not recognize KPI2 as a valid sibling of KPI1 (This is indeed an issue with PPS and needs to be changed as soon as possible in the future version) because of the name given to the Actual and Target in KPI2 (defined as Test1 and Test2 respectively). To overcome this issue in the scorecard, the best option to follow is to keep the names the same across for all the KPI's. In this case let us change the KPI2 in the following manner:

Once this is done after adding both the KPI's to the scorecard, right click on the KPI name within the scorecard and navigate to the metric setting and change the name to Test1 and Test2 respectively for Actual and Target as shown in the following screenshots:

(Once the metric setting is selected change the name as shown below)

Change Actual to Test1 and Target to Test 2 respectively and as the saying goes--> when the going gets tough, the tough get going. The scorecard is perfectly represented as it ought to.

Another Issue I run into is the connection problem with Excel services as a source. Some excel connections work with the same service account and others do not even though the permissions are the same. Well I sort of know a way to solve the issue.... but let me tell this story some other time.

SQL Server Best Practices

Recently was discussing with a colleague of mine as to the best practices in SQL. So this post is dedicated to that chat. The first thing we discussed about is the usage of NOLOCK's for tables in non highly transactional based databases with a wide user audience. Now the question of dirty reads does come to mind but at the end of the day the Read Uncommited transaction allows multiple end users to query the underlying table. NOLOCK is nothing more than a table hint that allows the query to be performed in a read uncommited fashion but is still a great practice to follow even while creating views. I have been using NOLOCK's for a large period of time (and sometimes implement it out of habit for my views i.e. SELECT C1,C2 FROM V1 (NOLOCK) as well which does not make any sense but is more of a force of habit except for my last post where I removed it on purpose) but I recently came across another table hint called READPAST but I am strictly avoiding this topic as this prevents the end user from reading locked rows as well. Another thing we discussed about was the usage of transactions within stored procedures. It is a good practice for SQL developers to always wrap SP's within transactions. Creation of an SP should have a BEGIN TRAN statement. The reason is quite simple Dr. Watson, you would want all the transactions that take place within the SP to be commited at one point in time. But there are exceptions to this practice and let us say that you are pulling information from table X to table Y and then going to update X or Y based on some logic. We would want the inserts to go ahead as planned and would not want to roll back the inserts unless it is a very small data load (roughly a couple of 100 records). The transaction wrapping allows us to follow the "ACI" of the ACID properties of an RDBMS. The next aspect to follow is the usage of ANSI standard SQL while writing basic standard SQL statements. I totally disagreed with this aspect, first and foremost using the standard t-sql everywhere is not going to downgrade the performance of your SQL. Example:
ANSI standard: SELECT T1.C1,T1.C2 from T1 INNER JOIN T2 on T1.C3=T2.C1 T-SQL: SELECT T1.C1,T1.C2 FROM T1,T2 WHERE T1.C3=T2.C1
The performance in SQL server is going to be the same.... I do agree from a readability perspective that the ANSI standard is better but I am not going to support a standard just because the readability is a bit better. I do however use t-sql on a regular basis for majority of my queries but in case if the queries is to be made available to productionand the users are more familiar with a specific standard then I would definitely go ahead with the standard that is followed. Usage of cursors is not good..... I agree but it cannot be avoided everywhere and besides I think CURSORS even though non performant does have a lot of benefits.
Benefits:

• Iterative looping (start from i and go on to n-1 once)
• Ensuring that every row is taken into consideration
• More hands on with the row cell than set based operations
• Primary Keys are not required in the underlying data set or for that matter even generating a ROW_NUMBER() for the dataset

Usage of index hints should be made a common practice while writing SQL. Figuring out the number of indexes on the base tables and which indexes will improve the performance of the query and targeting that index should be made a practice. Usage of filtered indexes is also a good practice and roughly one that I would recommend. More information on filtered indexes can be gathered @ http://msdn.microsoft.com/en-us/library/cc280372.aspx My colleague did suggest knowing the DBCC commands as well as underlying base sp's to the T. Well it does help to have mastery over the DBCC and internal SP's but what is BING or Google for. I do not want to memorize these commands as they are not pertinent for my day to day work but everybody has their own opinion. Using unicode related datatypes like NVARCHAR,NCHAR etc.... sparingly. Setting the NOCOUNT on will not decrease the performance of your SQL but does add an additional routine to the query results. Having the NOCOUNT on is also a good practice. This is where the conversation changed to a different track but still was quite interesting while this track lasted. Also on a quick note usage of indexed views can certainly improve performance of the underlying queries to a certain extent. Let me give an example here:
I have a table TableCountry on which I have no primary keys on. I created a view on top of this called dbo.temp_view and I have another view called Test.temp_view with the same underlying SQL with a slight difference while creating the views as follows:
CREATE VIEW [dbo].Temp_View AS
SELECT Country,Margin FROM [dbo].[TableCountry]
CREATE VIEW [Test].Temp_View WITH SCHEMABINDING AS
SELECT Country,Margin FROM [dbo].[TableCountry]
The next step was for me to create a clustered index on top of the view [Test].Temp_View as follows:
CREATE UNIQUE CLUSTERED INDEX idx_temp_view ON [Test].Temp_View (Country)
Now let me execute the following queries in the order defined below:
SET SHOWPLAN_ALL ON
SELECT Country FROM [dbo].[temp_view]
SELECT Country FROM [Test].Temp_View WITH (NOEXPAND)
Now if you check out the result plan you will notice that the second query utilizes a CLUSTERED INDEX SCAN VS the TABLE SCAN used for the first query. Also the underlying CPU cost for the second is much lesser (as it is targeting a fixed segment on the page) rather than the page itself. Indexed views are really beneficial when you are targeting large tables using views and dont want the underlying MACRO EXPANSION to happen. But there is a cost of maintaining the index for the view (Speed VS Cost) which is something where one has to carefully consider while working with large OLTP systems.

Framework for Monitoring Metadata changes

It is really important for a handshake model to exist between most upstream and downstream systems. Normally there is a mode of communication between the upstream Dev and Downstream Dev teams in order to track these changes. Now I have created a simple framework. Now rather than a pull system where the downstream pulls the meta data changes from the source, I have created a push system that the upstream will send across to the destination systems. Let us see how the framework works:
Step 1: Create a table that will store the metadata changes like addition or updation of tables and views as follows-
--Table to store Metadata changes for views and tables CREATE TABLE DB_Table_View_Changes ( DB_Table_View_Changes_ID INT IDENTITY(1,1) PRIMARY KEY ,DB_Schema_Name VARCHAR(256) ,DB_TableView_Name VARCHAR(256) ,DB_TableView_Type VARCHAR(5) ,ModifiedDate DATETIME )
Step 2: The next step is to create the table that will track the Column changes made to views or tables-
--Table to track the Column MetaData Changes CREATE TABLE DB_Column_Changes ( DB_Column_Changes_ID INT IDENTITY(1,1) PRIMARY KEY ,DB_Schema_Name VARCHAR(256) ,DB_Table_Name VARCHAR(256) ,DB_Column_Name VARCHAR(256) ,Is_Nullable varchar(3) default 'YES' ,Data_Type VARCHAR(20) ,Data_Type_Length SMALLINT ,Data_Type_Precision SMALLINT ,Data_Type_Scale SMALLINT ,ModifiedDate DATETIME ,Is_Deleted TINYINT default 0 )
Step 3: We create a table to store the list of downstream MetaData Tables (one for table/view changes and the other for the column changes) that these changes have to move to from the upstream system-
--List of downstream tables that the changes need to flow into --drop table DB_Downstream_Tables CREATE TABLE DB_Downstream_Tables ( DB_Downstream_Server VARCHAR(256) ,DB_Downstream_DB VARCHAR(256) ,DB_Downstream_Schema VARCHAR(256) --Downstream table name to store table/view changes ,DB_Downstream_TableChanges VARCHAR(256) --Downstram table name to store column changes ,DB_Downstream_ColumnChanges VARCHAR(256) -- this is to verify whether the link server for downstream tables have been created ,Link_Server_Created TINYINT default 0 ,DB_DownStream_Owner VARCHAR(100) ,DB_LOGIN VARCHAR(200) ,EmailID VARCHAR(256) )
Step 4: The next step is to write up an SP (Stored Procedure) that will create the linked servers (provided the upstream Dev team has access to a metadata repository on the downstream systems. (The reason for creating a separate SP for doing this is that we cannot wrap the "master.dbo.sp_addlinkedserver" system proc within a transaction.)- --Procedure to create linked servers from DB_Downstream_Tables --DROP PROCEDURE DB_Add_Linked_Server CREATE PROCEDURE DB_Add_Linked_Server AS BEGIN DECLARE cursorTest CURSOR FOR SELECT DB_Downstream_Server FROM DB_Downstream_Tables WHERE Link_Server_Created <> 1 DECLARE @ServerVal NVARCHAR(100) OPEN cursorTest FETCH NEXT FROM cursorTest INTO @ServerVal WHILE @@FETCH_STATUS = 0 BEGIN EXEC master.dbo.sp_addlinkedserver @server = @ServerVal, @srvproduct=N'SQL Server' ; FETCH NEXT FROM cursorTest INTO @ServerVal END CLOSE cursorTest DEALLOCATE cursorTest END
Step 5: The next step would be to create the downstream tables hosted at the destination metadata DB layer (The prior ones were all at the source level)- --Tracking the Table/view changes from the source CREATE TABLE TableChange ( DB_Schema_Name VARCHAR(256) ,DB_TableView_Name VARCHAR(256) ,DB_TableView_Type VARCHAR(5) ,ModifiedDate DATETIME ) --Tracking the column changes from the source CREATE TABLE ColumnChange ( DB_Schema_Name VARCHAR(256) ,DB_Table_Name VARCHAR(256) ,DB_Column_Name VARCHAR(256) ,Is_Nullable varchar(3) ,Data_Type VARCHAR(20) ,Data_Type_Length SMALLINT ,Data_Type_Precision SMALLINT ,Data_Type_Scale SMALLINT ,ModifiedDate DATETIME ,Is_Deleted TINYINT )
Step 6: The final step is to create the Stored proc to populate the metadata changes in the destination tables at the upstream system. Note: The procedure can be modified to be made set based rather than cursor based. Configuration of the DataBase Mail at the source level is required for the "sp_send_dbmail" task. You can also pass a query parameter to the "sp_send_dbmail" task if required. All I am trying to create is some sort of notification for the downstream owners. --DROP PROCEDURE DB_SP_Verify_Metadata_Changes --EXEC DB_SP_Verify_Metadata_Changes CREATE PROCEDURE DB_SP_Verify_Metadata_Changes AS BEGIN BEGIN TRAN BEGIN TRY DECLARE @DB_Downstream_Server VARCHAR(256) DECLARE @DB_Downstream_DB VARCHAR(256) DECLARE @DB_Downstream_Schema VARCHAR(256) DECLARE @DB_Downstream_TableChanges VARCHAR(256) DECLARE @DB_Downstream_ColumnChanges VARCHAR(256) DECLARE @Link_Server_Created TINYINT DECLARE @DB_DownStream_Owner VARCHAR(100) DECLARE @DB_LOGIN VARCHAR(200) DECLARE @EmailID VARCHAR(256) DECLARE @tablechanges INT DECLARE @columnchanges INT --Populate DB_Table_View_Changes PRINT 'Inserting into DB_Table_View_Changes' INSERT INTO DB_Table_View_Changes(DB_Schema_Name,DB_TableView_Name,DB_TableView_Type,ModifiedDate) SELECT T.Table_Schema,T.Table_Name, Case when T.Table_Type='BASE TABLE' Then 'Table' when T.Table_Type='VIEW' Then 'View' end as Table_Type,getdate() as ModifiedDateTime FROM Information_Schema.Tables T where lower(T.TABLE_SCHEMA+'.'+T.TABLE_NAME) not in (SELECT lower(ISNULL(C.DB_Schema_Name,'Test')+'.'+ISNULL(C.DB_TableView_Name,'Test')) FROM DB_Table_View_Changes C) --Populate DB_Column_Changes PRINT 'Inserting into DB_Column_Changes' INSERT INTO DB_Column_Changes ( DB_Schema_Name ,DB_Table_Name ,DB_Column_Name ,Is_Nullable ,Data_Type ,Data_Type_Length ,Data_Type_Precision ,Data_Type_Scale ,ModifiedDate ,Is_Deleted ) SELECT Table_Schema,Table_Name,Column_Name,Is_Nullable,Data_Type,Character_Maximum_Length,Numeric_Precision,Numeric_Scale,getdate(),0 FROM Information_Schema.Columns Source where lower(Source.Table_Schema+'.'+Source.Table_Name+'.'+Source.Column_Name) not in ( SELECT lower(d.DB_Schema_Name+'.'+d.DB_Table_Name+'.'+d.DB_Column_Name) FROM DB_Column_Changes d ) PRINT 'Update Table Changes' UPDATE d SET Is_Deleted = 1 ,ModifiedDate=getdate() FROM DB_Column_Changes d WHERE lower(d.DB_Schema_Name+'.'+d.DB_Table_Name+'.'+d.DB_Column_Name) not in ( SELECT lower(Source.Table_Schema+'.'+Source.Table_Name+'.'+Source.Column_Name) FROM Information_Schema.Columns Source ) PRINT 'Update Column Changes' Update d SET d.Is_Nullable=Source.Is_Nullable ,d.Data_Type = Source.Data_Type ,d.Data_Type_Length=Source.Character_Maximum_Length ,d.Data_Type_Precision = Source.Numeric_Precision ,d.Data_Type_Scale= Source.Numeric_Scale ,ModifiedDate=getdate() FROM DB_Column_Changes d, Information_Schema.Columns Source WHERE lower(d.DB_Schema_Name+'.'+d.DB_Table_Name+'.'+d.DB_Column_Name)= lower(Source.Table_Schema+'.'+Source.Table_Name+'.'+Source.Column_Name) and ( d.Is_Nullable <> Source.Is_Nullable OR d.Data_Type <> Source.Data_Type OR ISNULL(d.Data_Type_Length,0) <> ISNULL(Source.Character_Maximum_Length,0) OR ISNULL(d.Data_Type_Precision,0) <> ISNULL(Source.Numeric_Precision,0) OR ISNULL(d.Data_Type_Scale,0) <> ISNULL(Source.Numeric_Scale,0) ) SELECT @tablechanges = COUNT(1) FROM DB_Table_View_Changes d WHERE CONVERT(VARCHAR, GETDATE(), 101) = CONVERT(VARCHAR, d.ModifiedDate, 101) SELECT @columnchanges = COUNT(1) FROM DB_Column_Changes d WHERE CONVERT(VARCHAR, GETDATE(), 101) = CONVERT(VARCHAR, d.ModifiedDate, 101) print 'Table Count' + convert(varchar,@tablechanges) print 'Column Count' + convert(varchar,@columnchanges) IF (@tablechanges > 1 OR @columnchanges > 1) BEGIN PRINT 'Begin Cursor Loop' DECLARE cursorTest CURSOR FOR SELECT * FROM DB_Downstream_Tables DECLARE @INSCOLSQL NVARCHAR(MAX) DECLARE @INSTABSQL NVARCHAR(MAX) OPEN cursorTest FETCH NEXT FROM cursorTest INTO @DB_Downstream_Server ,@DB_Downstream_DB ,@DB_Downstream_Schema ,@DB_Downstream_TableChanges ,@DB_Downstream_ColumnChanges ,@Link_Server_Created ,@DB_DownStream_Owner ,@DB_LOGIN ,@EmailID WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'Insert DB_Downstream_ColumnChanges' SET @INSCOLSQL='INSERT INTO ['+@DB_Downstream_Server+'].['+@DB_Downstream_DB+'].['+@DB_Downstream_Schema+'].['+@DB_Downstream_ColumnChanges+'] ( DB_Schema_Name ,DB_Table_Name ,DB_Column_Name ,Is_Nullable ,Data_Type ,Data_Type_Length ,Data_Type_Precision ,Data_Type_Scale ,ModifiedDate ) SELECT DB_Schema_Name ,DB_Table_Name ,DB_Column_Name ,Is_Nullable ,Data_Type ,Data_Type_Length ,Data_Type_Precision ,Data_Type_Scale ,ModifiedDate FROM DB_Column_Changes d WHERE CONVERT(VARCHAR, GETDATE(), 101) = CONVERT(VARCHAR, d.ModifiedDate, 101)' PRINT @INSCOLSQL EXEC sp_executesql @INSCOLSQL PRINT 'Insert DB_Downstream_TableChanges' SET @INSTABSQL='INSERT INTO ['+@DB_Downstream_Server+'].['+@DB_Downstream_DB+'].['+@DB_Downstream_Schema+'].['+@DB_Downstream_TableChanges+'] ( DB_Schema_Name ,DB_TableView_Name ,DB_TableView_Type ,ModifiedDate ) SELECT DB_Schema_Name ,DB_TableView_Name ,DB_TableView_Type ,ModifiedDate FROM DB_Table_View_Changes d WHERE CONVERT(VARCHAR, GETDATE(), 101) = CONVERT(VARCHAR, d.ModifiedDate, 101)' PRINT @INSTABSQL EXEC sp_executesql @INSTABSQL EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLSERVER2012', @recipients = @DB_LOGIN, @body = 'New Source Tables added by the Source Team', @subject = 'Column Changes from Source' ; EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQLSERVER2012', @recipients = @DB_LOGIN, @body = 'The new Source Column changes made by the Source Team', @subject = 'Column Changes from Source' ; FETCH NEXT FROM cursorTest INTO @DB_Downstream_Server ,@DB_Downstream_DB ,@DB_Downstream_Schema ,@DB_Downstream_TableChanges ,@DB_Downstream_ColumnChanges ,@Link_Server_Created ,@DB_DownStream_Owner ,@DB_LOGIN ,@EmailID END CLOSE cursorTest DEALLOCATE cursorTest END END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_MESSAGE() AS ErrorMessage; Declare @CursorStatX int select @CursorStatX=cursor_status('global','cursorTest') print 'CursorValX:' + Convert(varchar(5),@CursorStatX) If(@CursorStatX > 0) BEGIN CLOSE cursorTest DEALLOCATE cursorTest END ROLLBACK TRAN print @@ERROR END CATCH; COMMIT TRAN END
Populate the DB_Downstream_Tables with the required information as shown in the figure below-

The last and final step is to create a SQL agent job at the source level to run the Stored Procs in the following Order:
1. EXEC DB_Add_Linked_Server
2. EXEC DB_SP_Verify_Metadata_Changes