How to pass in parameters to SDC task sql.execute?

Apr 28, 2008 at 11:08 PM
I’m trying to pass in parameters to a SQL script to run with the SDC task Sql.Execute in MSBuild. I understand the syntax of what you do in MSBuild, but the documentation does not say how to format your sql script to accept the parameter?

Based on the documentation as downloaded from codeplex, and the link below, I have my ItemGroup configured and intend to pass in two parameters, one an integer and the other a string.

What would be a sql script to use this? The reason I ask is that I have seen one syntax for SqlCmd, another for a custom installer I use that does direct textual substitution, but I am hoping this would be real sql parameters – is it?

My objection to SqlCmd is that it does not compile as sql, so is hard to validate before deployment. Also, I am hoping this uses real sql parameters so that it is less unsafe, with respect to dynamic sql.

<Sql.Execute
Path="$(MSBuildProjectDirectory)\myDb\Scripts\Post-Deployment\MySqlFile.sql"
ServerName="$(mySQLServer)"
DatabaseName="$(myDB)"
Parameters="@(myInteger);@(myString)"
CommandTimeout="60"
/>

Handy reference link:
http://blogs.conchango.com/stevewright/archive/2006/12/20/MSBuild-using-SQL-Script-parameters.aspx

Thanks very much,

Ken

PS. Second attempt to post, first failed, hope this is not a duplicate post.
Developer
Apr 29, 2008 at 9:26 AM
Hi Ken

Looking at the code, it seems that it is simply doing a string replacement on the SQL read from the file, rather than 'real' SQL parameter population. I will update the sample and documentation to reflect this. Below is a sample:

<Project DefaultTargets="Default" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
<Import Project="c:\Work\POC\bin\Microsoft.Sdc.Common.tasks"/>
<ItemGroup>
<myString Include="true">
<name>@P1</name>
<value>'Hello'</value>
</myString>
<myInteger Include="true">
<name>@P2</name>
<value>342</value>
</myInteger>
</ItemGroup>
<Target Name="Default">
<Sql.Execute Path="C:\Work\POC\SDCParameter.sql" ServerName="." DatabaseName="master" Parameters="@(myString);@(myInteger)"/>
</Target>
</Project>

Where SDCParameter.sql contains:
DECLARE @Str1 NVARCHAR(100);
DECLARE @Int1 INT;

SET @Str1 = @P1;
SET @Int1 = @P2;

RAISERROR('Str1 is: %s', 0, 1, @Str1) WITH NOWAIT
RAISERROR('Int1 is: %d', 0, 1, @Int1) WITH NOWAIT


-- Replaced Text
DECLARE @Str1 NVARCHAR(100);
DECLARE @Int1 INT;

SET @Str1 = 'Hello'
SET @Int1 = 342

RAISERROR('Str1 is: %s', 0, 1, @Str1) WITH NOWAIT
RAISERROR('Int1 is: %d', 0, 1, @Int1) WITH NOWAIT

-- Result
Str1 is: Hello
Int1 is: 342

Since it's simply doing a 'token' replacement, I suggest you choose your 'tokens' carefully as it is very easy for the SQL to be corrupted.

Regards

Mike