Read through an SQL recordset

Feb 12, 2014 at 7:13 PM
Is there a way to use MSBuild Extensions to interate through a recordset returned from an SQL query. Each record will conatin name value pairs.

If this is an old question can you please navigate me to the response.

Feb 16, 2014 at 5:40 PM
Yes I think so.... something like the sample just don't use *
    <!-- Execute SQL and return the result in an Item. Each column is available as metadata --> 
    <MSBuild.ExtensionPack.SqlServer.SqlExecute TaskAction="ExecuteReader" Sql="Select * from sys.tables" ConnectionString="Data Source=desktop\Sql2008;Initial Catalog=;Integrated Security=True"> 
        <Output ItemName="RResult" TaskParameter="ReaderResult"/> 
    <Message Text="%(RResult.Identity) - %(RResult.object_id)"/> 
Feb 21, 2014 at 5:22 PM
Mike, I was actually asking how to interate through rows in a returned recordset. My recordset will have more than one row. I need to advance to the next row after getting the value from the current row.

Is this possible?
Feb 23, 2014 at 3:42 PM
The code returns a collection. you can iterate through it using %.

Apr 20, 2014 at 1:40 PM
Hi Nick, did you succeed here?

Apr 21, 2014 at 1:13 PM
No Mike, I don;t quite uinderstand how your sample works. I see the multiple return from the SQL but I don't understand how to interate through each row and each column of each row. The return for me is 2 columns, a name value pair. I want to check the name and apply the value to a local variable for each row returned. I'm not sure how this works in your sample.


May 26, 2014 at 5:00 PM
Each row is an item, each item has the columns as metadata. Below is a longer example where I do a select, then iterate of each item, then assign one of the items to a property.
<Project ToolsVersion="4.0" xmlns="">
<Import Project="$(MSBuildExtensionsPath)\ExtensionPack\4.0\MSBuild.ExtensionPack.tasks"/>
    <Target Name="Default">

    <!-- Execute SQL and return the result in an Item. Each column is available as metadata --> 
    <MSBuild.ExtensionPack.SqlServer.SqlExecute TaskAction="ExecuteReader" Sql="SELECT TOP 10 [name], [category_id] FROM [msdb].[dbo].[syscategories]" ConnectionString="Data Source=.;Initial Catalog=;Integrated Security=True"> 
        <Output ItemName="RResult" TaskParameter="ReaderResult"/> 
    <Message Text="%(RResult.Identity) - %(RResult.category_id)"/>
        <MyPropForLogShipping Condition="%(RResult.Identity) == 'Log Shipping'">%(RResult.category_id)</MyPropForLogShipping>
    <Message Text="MyPropForLogShipping == $(MyPropForLogShipping)" Importance="High"/>
This project is now maintained on Github. Please log an issue there to discuss further. Thanks!
Marked as answer by mikeFourie on 5/26/2014 at 10:01 AM
May 26, 2014 at 5:04 PM

Thanks Mike, that’s a perfect example. Really really appreciate it. Again thanks.


Nicholas Tullett | Consultant

Insurance Information Services | CGI

150 Commerce Valley Dr. West, Markham, Ontario, L3T 7Z3

T: 905-762-2800 ext. 1215107 [email removed]


Insurance Information Services

CONFIDENTIALITY NOTICE: Proprietary/Confidential Information belonging to CGI Group Inc. and its affiliates may be contained in this message. If you are not a recipient indicated or intended in this message (or responsible for delivery of this message to such person), or you think for any reason that this message may have been addressed to you in error, you may not use or copy or deliver this message to anyone else. In such case, you should destroy this message and are asked to notify the sender by reply e-mail.