Problem with passing variables to SQL Step
 

Hi Folks,
When I place a SQL Server step into visual build, how do I pass Visual Build variables(MACRO's) into the script?
Here is my SQL code.


DECLARE @MaxReleasenr INT
DECLARE @TimeAndDate datetime
DECLARE @JDate char(4)
DECLARE @CReleaseCount varchar(200)
DECLARE @Approved tinyint
DECLARE @ReleaseCount tinyint


set @MaxReleasenr = (select MAX(Releasenr) from BB_SystemRelease where systemid='50')
set @MaxReleasenr = (@MaxReleasenr+1)

set @TimeAndDate = GETDATE();
set @JDate = %%JDATE%%
set @CReleaseCount = %%RELEASECOUNT%%

set @Approved=0
insert into BB_SystemRelease values
(50,@MaxReleasenr,@TimeAndDate,@JDate, convert(int,@CReleaseCount), @Approved)


When I try to put either %%JDATE%% or %JDATE%
into the script it fails with errors like %%JDATE%%
"Syntax error converting the varchar value '%RELEASECOUNT%' to a column of data type int." for %%JDATE%%
or
Msg 156, Level 15, State 1, Server PCCWD-MMURR, Line 14
Incorrect syntax near the keyword 'set'.
Msg 156, Level 15, State 1, Server PCCWD-MMURR, Line 16
Incorrect syntax near the keyword 'set'. for %JDATE%%

SQL Server is not going to know anything about VBP macros, so you should use single percents (to get the expanded macro value) instead of double percents (to insert a literal string containing percents). I believe string values passed to SQL server need to be quoted and the SQL variables are unnecessary. Something like this should be pretty close:

insert into BB_SystemRelease values
(50,@MaxReleasenr,@TimeAndDate,'%JDATE%', convert(int,'%RELEASECOUNT%'), @Approved)