Team Foundation Server 2010

Execute remote SQL commands via Powershell on Amazon RDS

As I have been working with TFS2010 build automation for past few days, again I came across a dead end. The issue was all our application servers are on Amazon EC2 and the backend is on Amazon RDS. This was new to me and I had to include the script which contains the database changes to the Build Automation. i.e. The Build will execute the script from the Build server against the RDS instance. When it comes to RDS there are no servers or you can RDP to it. It is nothing but a standard database instance. So I thought of going through a step by step approach on solving this.
First followed a fundamental video tutorial on Pluralsite and got the basic understanding of the Amazon RDS for SQL Server. After that I have posted the question on AWS RDS forum. In the mean time we have also contacted the AWS support and according to the support person I could execute scripts remotely against the RDS instance using the “Sqlcmd”. So my preference was to do it via Powershell. So I did some R&D and found this excellent article which helped me to solve my issue.
The equivalent of the “SqlCmd” in powershell is “Invoke-Sqlcmd” command. This is not installed on your system by default. If you are using sql 2012, this will be installed with SQLPS modules. Follow up the document bellow on the Simple-Talk, it explains everything in detail.
Practical PowerShell for SQL Server Developers and DBAs

After doing the configuration I have used the following command to connect to the RDS instance remotely. Following example just uses a select statement.

PS C:\Users\Administrator> Invoke-Sqlcmd –ServerInstance -Database EmployeeData –Query “Select * from HoursWorked” –Username -Password

Please follow the link for more detail on “Invoke-Command”.

TFS build Get Workspace step fails with inter-method access error

I have been implementing TFS 2010 automated build for our company’ product and initially it was implemented in a workgroup environment. There were some custom Powershell scripts that has to be executed remotely and it took weeks for me to finally solve it.
I was so close to complete the whole automated process but due to the security factors we have to bring in a Domain Controller and had to add the servers to the domain. All our servers are in Amazon Cloud and during this process the computer names were changed and all the share point configurations and the TFS configurations were somewhat corrupted. So instead of battling with the trouble shooting this I decided to remove TFS and re install it.
So I did a basic installation as a standalone server and all the settings were fine. But after setting the build definition and queue a new build, I faced a whole new problem as follows. No matter what I did everything failed at the Get Workspace step as follows.

Attempt by method ‘Microsoft.TeamFoundation.Build.Workflow.Activities.TfUndo+TfUndoCore.RunCommand(Microsoft.TeamFoundation.Build.Workflow.Activities.VersionControlScope, Microsoft.TeamFoundation.VersionControl.Client.Workspace, System.String, System.String)’ to access method ‘Microsoft.TeamFoundation.VersionControl.Client.Workspace.Undo(Microsoft.TeamFoundation.VersionControl.Client.ItemSpec[], Boolean, Boolean, System.String[])’ failed.

I was trying many things and everything failed. But at the end my good old friend, Prabath helped me out by suggesting that I should install the TFS 2010 service pack 1. But I have already done that.
So I was out of options, and then I decided to re install the service packs. First I installed the Microsoft Team Foundation Server 2010, Service pack 1 using the following link: TFS 2010 Service Pack 1

After that I applied the Cumulative Update package 2 for Visual Studio Team foundation server 2010 Service Pack 1 from the following link: Cumulative Update package 2 for Visual Studio Team foundation server 2010 Service Pack 1

So this approach solved the issue for me which I was stuck in the above issue for 4 days and have to say that never give up. There is always an answer and always someone to guide you to get it fixed.