Kinook Software Forums

Go Back   Kinook Software Forums > Visual Build Professional > [VBP] Third Party Tools

Thread Tools Rate Thread Display Modes
Old 12-20-2004, 10:04 AM
Posts: n/a
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%%
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%%
Reply With Quote
Old 12-20-2004, 01:30 PM
kinook kinook is online now
Join Date: 03-06-2001
Location: Colorado
Posts: 5,837
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)
Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

All times are GMT -5. The time now is 09:01 PM.

Copyright 1999-2021 Kinook Software, Inc.