#1
|
|||
|
|||
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%% |
#2
|
|||
|
|||
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) |
|
|