Saturday, July 9, 2011

PowerShell 32 and 64 bit have different execution policy settings

I use PowerShell to automate many repetitive tasks. And build automation is one of the areas I like most.
I faced a stiuation when I get this error with a PowerShell script running in visual studio project post build event:
File XXX.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about _signing" for more details.
I know this error is usually caused by an execution policy that denies execution of scripts. So I made sure the Execution Policy is set to RemoteSigned. But This did not work!!

I added this to the batch file that calls the PowerShell script:

powershell "Get-ExecutionPolicy -List"

And the result was:
Scope                         ExecutionPolicy

----- ---------------

MachinePolicy Undefined

UserPolicy Undefined

Process Undefined

CurrentUser Undefined

LocalMachine Undefined

After some research I found that since the machine is 64-bit, there were 2 versions of PowerShell, 32 and 64-bit. Again I edited the batch file adding :

Powershell.exe "Get-Variable PSHOME"

And ran a build from visual studio, the result was:

Name Value
---- -----
PSHOME C:\Windows\SysWOW64\WindowsPowerShell\v1.0

This shows that the version invoked was the 32-bit version, while the version I used to Set-ExecutionPolicy was the 64-bit version. I determined the paths from start menu shortcuts to Powershell:

Windows PowerShell (x86): %SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe
And Windows PowerShell: %SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe

So I opened Windows PowerShell (x86) and executed:

Set-ExecutionPolicy RemoteSigned

And it worked.

So, did Visual Studio post build event call the 32-bit version because it's a 32-bit application?

Wednesday, July 6, 2011

SSMS and Deleting all records from a self referencing table

Clearing all data from a self referencing table using SSMS can be tricky. Selecting all records in SSMS results grid and pressing DEL just won't work in many times.

Take the common example of Employee - Manager relationship:

If you try using SSMS results grid to delete all records, you may get this error:

No rows were deleted.

A problem occurred attempting to delete row 1.
Error Source: .Net SqlClient Data Provider.
Error Message: The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_Employee_Employee". The conflict occurred in database "test", table "dbo.Employee", column 'ManagerId'.

The statement has been terminated.

Using the Query window and running:
DELETE employee
Will work however.

The difference is that SSMS actually tries to delete row by row, which will violate the constraint. But when using a query to delete all records. SQL Serve is smart enough to clear all data.