Archive

Archive for February, 2012

PowerShell get “Return Value” from StoredProcedure ExecuteNonQuery

February 27th, 2012 1 comment

The scenario is that from PowerShell you want to do the following:

1. Execute a stored Procedure
2. Check the Return Code from the stored procedure call.
3. Perform conditional logic based upon whether the stored procedure succeeded or failed

The following will show you how to:

    1. Create a PowerShell function to execute a stored procedure.
    2. Call this stored procedure and extract the return code into an integer variable
    3. Apply conditional logic based upon the return code

    I struggled with getting this to work and did not find any exact examples that explained it exactly as I wanted.

    Environment Setup

    1. SQL Server / Express – I am running SQL Sever 2008 R2 Express on the local host
    2. Create an empty Database called “TestDB”
    3. PowerShell configured with Remote-Signed Execution Policy. If you don’t know how to do this go here Set PowerShell Execution Policy

    StoredProcedure

    Create a stored procedure as follows on your TestDB

    
    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[prcTest]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[prcTest]
    GO
    
    Create Procedure prcTest 
    	@value int
    as
    begin
    	Declare @return float
    	
    	BEGIN TRY
    	
    		-- could return divide by 0 error if value = 0 
    		set @return = 1 / @value;
    	
    		Return 1 -- success
    	
    	END TRY
    	BEGIN CATCH
    		-- will return 0 when there has been a divide by zero error
    		return 0
    	END CATCH
    end
    
    

    This procedure will accept a single integer parameter and divide by it. If zero is passed in the procedure will fail with a divide by zero error and return the return code of 0.
    Any other number will succeed.

    1 = Success and 0 = Error

    (NOTE: I specifically reversed the normal codes returned from Stored Procedures because if there is a connection problem and the following powershell script cannot execute it will return 0 which needs to be treated as a failure, so 1 must be the success code)

    PowerShellScript

    Create a file called test.ps1 in your c:\Scripts directory.

    You can run this from the PowerShell ISE or you can create a .bat file and past this line in it:
    [bat]
    PowerShell.exe -noexit c:\scripts\test.ps1
    [/bat]

    The Power Shell Script is as follows:

    function ConnectionString()
    {
        return "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TestDB;Data Source=.\SQLExpress";
    }
    
    function executeStoredProcedure($value)
    {
        
        $connection = ConnectionString;
        $query = "prcTest";
        
        $sqlConnection = new-object System.Data.SqlClient.SqlConnection $connection
        $sqlConnection.Open() 
        
        $sqlCmd = new-object System.Data.SqlClient.SqlCommand("$query", $sqlConnection) 
    
        $sqlCmd.CommandType = [System.Data.CommandType]"StoredProcedure" 
        
        $sqlCmd.Parameters.AddWithValue("@value", $value)
    
        $sqlCmd.Parameters.Add("@ReturnValue", [System.Data.SqlDbType]"Int") 
        $sqlCmd.Parameters["@ReturnValue"].Direction = [System.Data.ParameterDirection]"ReturnValue" 
    
    
        $sqlCmd.ExecuteNonQuery() | out-null
        $sqlConnection.Close() 
    
        [int]$sqlCmd.Parameters["@ReturnValue"].Value
        
    }
    
    # Test 1 - Should fail
    #======================================
    Write-Host "====================="
    Write-Host "TEST 1"
    Write-Host "====================="
    $out = executeStoredProcedure(0)
    Write-Host $out
    $returnValue = $out[2]
    Write-Host $returnValue
    
    
    if($returnValue -eq $null -or $returnValue -eq 0)
    {
        Write-Host "An Error Occured"
    } 
    else 
    {
        Write-Host "Success"
    }
    
    # Test 2 success
    # ---------------
    # removed the Write-Host lines 
    #======================================
    Write-Host "====================="
    Write-Host "TEST 2"
    Write-Host "====================="
    $out = executeStoredProcedure(1)
    $returnValue = $out[2]
    
    if($returnValue -eq $null -or $returnValue -eq 0)
    {
        Write-Host "An Error occurred"
    } 
    else 
    {
        Write-Host "Success"
    }
    

    I will talk you through a few of the interesting points of above.

    ConnectionString Fuction
    I do this so I can have my connection string centralised in my script and that way if
    I need to change the SQL Server credentials I am using I only need to make the change in one place

    [int]$sqlCmd.Parameters[“@ReturnValue”].Value
    This gets the “ReturnValue” from the stored procedure and then casts it to an integer.

    Note: I did try code like;

    $rc = [int]$sqlCmd.Parameters["@ReturnValue"].Value
    return $rc
    

    But it made no difference to the output of the function.

    $returnValue = $out[2]
    I will confess I don’t now a great deal about PowerShell but I have worked through this problem to get a working solution.
    If you execute it and look at the output from the “Write-Host $out” you will see it appears to be an array type object and it prints as

    @value @ReturnValue 1
    

    I figured this is a zero based array so I just accessed the value in position 3 i.e. $out[2], which is already of type “int” due to the cast in the function.

    Conditional Logic
    So now I have the integer return value in a variable $returnValue I can use it in my conditional logic. If the value was null or == 0 then it must be in error else success.

    Implementing this means you can execute a stored procedure and if it fails , you can cancel any further processing in the Script.

    Nice 😉

    Update – Oct 2012

    I recently had to use this code again and I found that the best solution is to have the stored procedure return “non-zero” values, eg 1 for success, -1 for failure.
    The issue is that the return code from the “executeStoredProcedure” call will be zero in certain catastrophic conditions. For example, break your connection string on purpose and the stored procedure will never get executed and the return code from “executeStoredProcedure” is zero.

    By using non-zero return codes you now have 3 possible states, 1 – success, -1 failure, 0 – catastrophic failure.
    I extended my code to use “blat.exe” to send admin emails on return code zero so that if my powershell script failed to run someone is notified.

    VN:F [1.9.22_1171]
    Rating: 10.0/10 (1 vote cast)

Categories: Code Tags:

Set PowerShell ExecutionPolicy

February 27th, 2012 No comments

When trying to run a PowerShell script you may get the following error:

“scriptname.ps1 cannot be loaded because the execution of scripts is disabled on this system”

You need to do the following to set the executionPolicy.

1. Run Powershell as Administrator
2. get-exeuctionPolicy
3. set-executionPolicy RemoteSigned
4. Enter Y
5. Check by running get-exeuctionPolicy
 

 

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: Code Tags:

Enable Windows PowerShell ISE (Integrated Scripting Environment)

February 27th, 2012 No comments

On a Windows Server 2008 box you may be wondering where the Windows PowerShell ISE is.

To Enable

  1. Bring up the Server Manager (Right-Click on “Computer”, choose manage
  2. Move to the Features Tab
  3. Choose “Add Features”
  4. Click “Windows PowerShell Integrated Scripting Environment”
     
     
     

     

  5. Click Next
  6. Click Install

The IDE is now installed and can be found under:
– All Programs
– Accessories
– Windows PowerShell

Windows Power Shell ISE Menu Location

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: Code Tags:

ASUS U3S6 Review – Benchmark SSD on Sata 3 add-on card against Sata II onboard

February 23rd, 2012 17 comments

Part 2 of my SSD SATA III performance tests.

If you are here and haven’t seen part 1 “Sata 3 SSD running on onboard SATA II benchmark” go here >>>

The Question

Is there any reason to buy a SATA III expansion card to run your new SATA III SSD if your MOBO only has SATA II?

So I have a shiny new Corsair Force GT 120GB SATA III drive and an old SATA II motherboard.

Last article I benchmarked the performance to see what kind of experience you get when running on your onboard sata II connectors.

This article is the part 2 where I am going to benchmark the relative performance with a SATA III expansion card to see if there is any point is spending the money on one.

The Test Card – ASUS U3S6

 
Marvell 88SE9123 Chipset

It was a bit hard to track one down in Australia so I had to buy this second-hand off the overclockers australia forums, but I wanted this card for its x4 PCI-e v2.0 connection.

Details of the U3S6 here.

The U3 = USB 3.0 with a NEC chip set; and
The S6 = Sata III 6.0Gb/s Marvel 88SE9123 controller.

 

ASUS U3S6 Sata III PCIe expansion card

ASUS U3S6 Sata III PCIe expansion cardASUS U3S6 Default Firmware Boot Screen Shot

 

 

 

 

 

 

 

 

 

The card has yet to be flashed from its original bios which appears as 1.0.0.1012.

I decided to play it safe and rather than flash the bios and destroy the card I would start from scratch, benchmark it first. Later I will play and see if I can improve on stock performance results, buy flashing to later versions of the firmware.

At on OS software level I tested with 2 driver configurations:

1.0.0.1036 and 1.2.0.1016
Marvell Driver 1.0.0.1036 Screenshot  

with some interesting results..

The latest drivers are best found on station-drivers. It’s french but version numbers can be read easily, go to the bottom for the MV91xx drivers and firmware.

The test rig

Mother Board: Socket 775, Gigabyte EP45-DS3
CPU: Core 2 Duo E8400 3.0Ghz, running overclocked @ 4.0Ghz.
Memory: 4GB (2x2GB) Corsair CM2X2048-8500C5C (1066Mhz)
Video: MSi R6850 Storm II 1G OC (R6850 PM2DIGD5)
SSD: Corsair Force GT 120GB, (1.3.3 firmware on SATA II in AHCI mode)
SATA III Card Asus U3S6 (firware 1012)
OS: Windows 7 SP1 – 64 bit

The U3S6 is a x4 card and will run on PCI-E v2.0. The EP45-DS3 only has 3 x PCI-e 1.0 x1 ports, and has a x16 and x8 PCI-e v2.0 slot.

The x16 slot is currently running my MSi HD 6850, and the x8 slot is free.

The U3S6 is therefore running in the x8 slot.

I was interested in seeing what might happen when running something like crysis whilst having the U3S6 trying to use the same bus.

Synthetic Benchmarks

AS SSD

My original SATA II Benchmark score on SATA II was 480! I did this benchmark on day 1 of a clean Windows 7 install. Since then I have been using the computer for a few months and the drive is about 50% full so I benchmarked with AS SSD again.

I did numerous benchmarks with in multiple different driver and SATA configurations. The Read speed seemed to give consistent results across similar benchmarks but the write performance (particularly the 4K-64-Thrd) was a bit all over the place and seemed to influence heavily the overall score giving results between 395 and 450.

The following results are the best of each run:

SATA II – Rerun
SATA II Benchmark

U3S6 – 1.0.0.1036
ASUS U3S6 1036 Driver AS SSD benchmark

ASUS U3S6 1.0.0.1036 Driver Compression Benchmark

U3S6 – 1.2.0.1016

U3S6 on 1.2.0.1016 driver AS SSD benchmark

The U3S6 seems to have better sequential read of approx 340MB/s. This is only a 75MB/s improvement.

I noted that generally the benchmark results for the earlier 1.0.0.1036 driver were slightly better and more consistent than the later 1.2.0.1016 driver.

Also the Read performance was generally consistent but the 4k and 4k-64Thrd write times were up and down.

Crystal Disk mark

Given the fluctuations I also tried Crystal Disk mark which seemed to give similar results without the randomness.

SATA II – Rerun
Crysal Disk Corsair Force GT 120GB SSD Benchmark on SATA II

U3S6 – 1.0.0.1036
Crystal Disk Corsair Force GT ASUS U3S6 Benchmark Driver 1.0.0.1036

U3S6 – 1.2.0.1016
Crystal Disk Corsair Force GT ASUS U3S6 Benchmark Driver 1.2.0.1016

Again the 1.0.0.1036 driver seemed to out perform its older brother.

Windows Start Up Times

The test

Same as last time, the benchmark recorded the time from when windows started loading after the POST screen, until a working desktop was available. This was identified as the time at which the Gadgets appeared on the desktop. This was split into 3 timing points

  1. time to login screen
  2. time to login
  3. time to see the desktop and the gadgets loaded.

Windows 7 Startup times Benchmark SSD results
The fastest startup time was still SATA II and the 1.2.0.1016 driver made a difference but the 1.0.1036 was consistently a few seconds slower to boot.

Windows Experience

The only score to change when running the U3S6 was the Disk from 7.8 to 7.9. Note: 7.9 is currently the maximum rating.
Windows Experience Score on the SSD SATA III

Crysis Benchmarks

I didn’t bother with the load time tests as I figured they would be comparable.

I did however run the Benchmarks for both Crysis and Crysis 2 in the various configurations, with some interesting results.

Crysis Benchmark

The crysis benchmark was run @ 1920×1080 – 64-bit and DirectX 10
The benchmark was run with the following SSD connections

  1. SATA II
  2. U3S6 1.0.0.1036

Crysis Benchmark

The results were almost identical. No change here.

Crysis 2 Benchmark

The benchmark was run on DirectX 11 on the Times Square Map.

Crysis 2 Benchmark

The most interesting thing here is that when the video card was being pushed by crysis 2, the PCIe channel appears to be compromised by the SSD drive running on the x8 port.

This can be seen in the poor fps on the two higher settings.

USB Performance

Just to see how the USB 3.0 worked I hooked up my Western Digital 1 TB external hard drive and did a 4.14GB and 15.5 GB file copy both up to and down from the External drive.

I found the 15.5GB copy onto the external drive took 10m 1s @ USB 2.0 but only took 3m 52s on USB 3.0. The copy down from the external driver took 9m 11s @ USB 2.0 and 3m 43s @ USB 3.0.
The copy rates were approx 27.1 MB/s combined for USB 2.0 whilst USB 3.0 achieved 67.9 Mb/s or 2.5 times faster. Nice.

Why so slow?

I have gotten around to flashing the Card. At first it felt like “flushing” not “flashing” as the firmwares I tried off “Station-Drivers” broke the card. After a few hours of flashing I finally got the driver working with Firmware revision 1028 “Firmware pour U3S6 Rev 0 (MV-9123) Version:1.0.0.1028) Mod by Daoud333”

So when I start it up I see the following

ASUS U3S6 firmware 1028 Screenshot on startup

PCIe x1 5.0Gbps. x1 why x1, its a x4 card running in a x8 slot?

After some digging I found this spec document on the controller, Marvell 88SE91xx Product Brief. If you read this the spec fo the controller shows this..

Marvell 88SE 9123 x1 PCIe

The controller only supports a single x1 connection.

I feel a bit duped. I specifically got this card for its x4 connection, but given the fact the marvel controller only accepts a x1 connection the SATA will only ever be able to run at this speed. I have read other people suggest that the x4 is used to split the channel in half and use some of the x 4 bandwidth for the USB 3.0 controller, which would make sense but it seems like false advertising to me.

Conclusion

Overall I think if you are out of SATA ports on your PC and need a few more, then the card is OK as long as you are NOT using it for gaming.
The sequential read is slightly better but this is only a benchmark figure as it did not translate in the real world.
The only other nicety is the USB 3.0 which will come in handy in the future.

I think in the next few months The Marvel 92xx controllers
will start to appear and these may allow my SSD to run to its full potential as this supports PCIe x2.

Cheers

VN:F [1.9.22_1171]
Rating: 9.9/10 (37 votes cast)

Categories: Hardware Review Tags: