Kinook Software Forum

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

Reply
 
Thread Tools Rate Thread Display Modes
  #1  
Old 12-20-2004, 09:04 AM
Guest
 
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%%
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%%
Reply With Quote
  #2  
Old 12-20-2004, 12:30 PM
kinook kinook is online now
Administrator
 
Join Date: 03-06-2001
Location: Colorado
Posts: 6,003
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
Reply

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 03:50 AM.


Copyright © 1999-2023 Kinook Software, Inc.