Is that the whole script that you show below? Is there anything before it like an IF EXISTS?
I recall having issues with this too. Have you tried delimiter=Normal? IF this is SQL server have you watched profiler to see what got sent to the server? BOb > -----Original Message----- > From: LLCard [mailto:llyo...@cox.net] > Sent: Tuesday, July 07, 2009 11:10 AM > To: nant-users@lists.sourceforge.net > Subject: [NAnt-users] Nant SQL Task limitations / problems > > > Hi, > > I am trying to use the NAnt sql task to run a set of sql scripts. I > am > getting errors with the sql task not parsing the sql script properly. > Any > help would be very much appreciated (lance_ly...@gordian-health.com). > Is > NAnt SQL Task the way to go? Should I be using sqlcmds which will run > fine > but not return as much error information. Thanks > > Lance > > > The errors are... > > Must declare the scalar variable "@RecallType". > Must declare the scalar variable "@SOC_SEC". > Must declare the scalar variable "@RecallType". > Must declare the scalar variable "@RecallType". > Must declare the scalar variable "@SOC_SEC". > Must declare the scalar variable "@RecallType". > Must declare the scalar variable "@SOC_SEC". > Must declare the scalar variable "@RecallType". > Must declare the scalar variable "@RecallType". > Must declare the scalar variable "@SOC_SEC". > Must declare the scalar variable "@RecallType". > Must declare the scalar variable "@RecallType". > Must declare the scalar variable "@Soc_Sec". > Must declare the scalar variable "@RecallType". > Must declare the scalar variable "@RecallType". > Incorrect syntax near the keyword 'AND'. > Must declare the scalar variable "@RecallType". > Must declare the scalar variable "@RecallType". > Must declare the scalar variable "@DateQueued". > Must declare the scalar variable "@DateQueued". > Must declare the scalar variable "@RecallType". > Must declare the scalar variable "@Soc_Sec". > Incorrect syntax near the keyword 'PROCEDURE'. > Must declare the scalar variable "@InternalId". > Incorrect syntax near the keyword 'PROCEDURE'. > Incorrect syntax near the keyword 'PROCEDURE'. > 'CREATE/ALTER PROCEDURE' must be the first statement in a query > batch. > Incorrect syntax near 'GO'. > > > > > the NAnt script (SQL Task) used is shown below. > > > <sql > connstring="Provider=SQLOLEDB;Data Source=${sql.server}; Initial > Catalog=${sql.database};User Id=${sql.user}; Password=${sql.password};" > transaction="true" > delimiter="GO" > delimstyle="Line" > print="true" > batch="true" > source="${sqlscriptname}" > output="${build.dbfolder}\results.txt" > /> > > > > The sql script is a synchronization script created by REDGate's sql > compare. > The stored procedure that is causing the issues above is shown below. > > > > CREATE PROCEDURE [dbo].[Recall_WorkflowVerification] > ( > @Soc_Sec varchar(9), > @RecallType varchar(10), > @DateQueued datetime = null > ) > AS > BEGIN > /* > Recall_WorkflowVerification > Created: 6/29/2009 by Sean Lowery > Purpose: DEFECT 441, create a way to verify recall is accurate before > queuing. > Paramenters: Soc_Sec = Social Security identifier of participant > (varchar(9)) > RecallType = Type from Recall table (Recall Code) (varchar(10)) > Important Note: At the time this was created, no clients are using > custom > workflows for these recalls. We will need to create either new > verification > procedures or add on to this one for future custom workflows. > Please see the procedure that calls this one, Recall_Create for more. > I HIGHLY recommend we move all workflows to the database, and out of > qdfs. > That way this procedure can become much more dynamic and accurate as > time > progresses and workflow changes. > TEST SCRIPT: > EXEC Recall_WorkflowVerification 'GHS123456', 'SM-NBFUR2' > */ > DECLARE @Retval bit, @Error varchar(100), @InternalID int, @DateEngaged > datetime > SET @Retval = 1 > SELECT @InternalID = InternalID from MEMBER where Soc_Sec = @Soc_Sec > SELECT @DateEngaged = MAX(DateEngaged) from NBP_Engagement where > InternalID > = @InternalID > IF @RecallType IN ('SM-NBFUR', 'SM-NBFUR2', 'SM-NBSCI', 'SM-NBSC', > 'SM-NBPR', 'L-NBPR','SM-NBPRS', 'SM-NBFRS','SM-NBCRS','SM-NBPRTX', > 'SM-NBFRT', 'L-NBPRTX','L-RENG', 'SM-RENG', 'SM-RENFU', 'SM-NBPRT', > 'L-NBPRT' ) > BEGIN > IF @DateEngaged IS NULL > BEGIN > SET @Error = 'No engagement for this participant.' > SET @Retval = 0 > END > IF @DateQueued IS NULL > SET @DateQueued = getdate() > IF EXISTS(SELECT Recall_ID FROM Recall WHERE [Type] = @RecallType AND > COMPLETE IS NULL) > BEGIN > SET @Retval = 0 > SET @Error = 'Same recall already pending for this user.' > END > IF @RecallType = 'SM-NBSC' AND NOT EXISTS(Select InternalID FROM Member > WHERE Status = 'N' and InternalID = @Internalid) AND @Retval = 1 > BEGIN > SET @Retval = 0 > SET @Error = 'Participant''s status is not valid for this recall.' > END > IF @RecallType IN ('SM-NBFUR', 'SM-NBFUR2', 'SM-NBSCI') AND @Retval = 1 > BEGIN > IF EXISTS(SELECT Recall_ID FROM Recall WHERE [Type] IN ('SM-NBFUR', > 'SM-NBFUR2', 'SM-NBSCI') AND COMPLETE IS NULL) > BEGIN > SET @Retval = 0 > SET @Error = 'Brown Rules recall sequence already started and recalls > are > still outstanding.' > END > IF NOT EXISTS(SELECT EngagementID FROM NBP_Engagement WHERE InternalID > = > @InternalID AND Mode = 'E' AND DateClosed IS NULL) > BEGIN > SET @Retval = 0 > SET @Error = 'Brown Rules recall sequence requires an open E-mail > engagement.' > END > IF @RecallType = 'SM-NBFUR2' AND NOT EXISTS(SELECT Recall_ID FROM > Recall > WHERE [Type] = 'SM-NBFUR' AND Soc_Sec = @Soc_Sec AND DATEDIFF(day, > Complete,@DateQueued) >= 7 AND Complete > @DateEngaged) > BEGIN > SET @Retval = 0 > SET @Error = 'Previous step in Brown Rules has not completed, and will > automatically queue this recall.' > END > IF @RecallType = 'SM-NBSCI' AND NOT EXISTS(Select InternalID FROM > Member > WHERE Status = 'N' and InternalID = @Internalid) > BEGIN > SET @Retval = 0 > SET @Error = 'Participant''s status is not valid for this recall.' > END > END > IF @RecallType IN ('SM-NBPR', 'L-NBPR') AND @Retval = 1 > BEGIN > IF NOT EXISTS(SELECT Recall_ID FROM RECALL WHERE [Type] = 'C-NBCE-A2' > AND > SOC_SEC = @SOC_SEC AND COMPLETE IS NOT NULL AND DATEDIFF(day, > Complete,@DateQueued ) >= 5 AND Complete > @DateEngaged) > BEGIN > SET @Retval = 0 > SET @Error = 'Does not meet pre-requisite requirements (completed C- > NBCE-A2 > not found within 5 or more days ago).' > END > IF NOT EXISTS(SELECT EngagementID FROM NBP_Engagement WHERE InternalID > = > @InternalID AND Mode = 'T' AND DateClosed IS NULL) > BEGIN > SET @Retval = 0 > SET @Error = 'Green Rules recall sequence requires an open Portal > Telephonic > engagement.' > END > END > IF @RecallType IN ('SM-NBPRS', 'SM-NBFRS', 'SM-NBCRS') AND @Retval = 1 > BEGIN > IF NOT EXISTS(SELECT EngagementID FROM NBP_Engagement WHERE InternalID > = > @InternalID AND Mode = 'S' AND DateClosed IS NULL) > BEGIN > SET @Retval = 0 > SET @Error = 'This recall requires an open Self engagement.' > END > IF(@RecallType = 'SM-NBPRS' AND NOT EXISTS (SELECT Recall_ID FROM > RECALL > WHERE [Type] = 'SM-NBCRS' AND SOC_SEC = @SOC_SEC AND COMPLETE IS NOT > NULL > AND DATEDIFF(day, Complete,@DateQueued ) >= 6 AND Complete > > @DateEngaged)) > BEGIN > SET @Retval = 0 > SET @Error = 'Does not meet pre-requisite requirements (SM-NBCRS not > found > completed at least 6 days prior).' > END > IF(@RecallType = 'SM-NBFRS' AND NOT EXISTS (SELECT Recall_ID FROM > RECALL > WHERE [Type] = 'SM-NBPRS' AND SOC_SEC = @SOC_SEC AND COMPLETE IS NOT > NULL > AND DATEDIFF(day, Complete,@DateQueued ) >= 4 AND Complete > > @DateEngaged)) > BEGIN > SET @Retval = 0 > SET @Error = 'Does not meet pre-requisite requirements (SM-NBPRS not > found > completed at least 4 days prior).' > END > END > IF @RecallType IN ('SM-NBPRE', 'SM-NBFRE', 'SM-NBCRE') AND @Retval = 1 > BEGIN > IF NOT EXISTS(SELECT EngagementID FROM NBP_Engagement WHERE InternalID > = > @InternalID AND Mode = 'E' AND DateClosed IS NULL) > BEGIN > SET @Retval = 0 > SET @Error = 'This recall requires an open E-mail engagement.' > END > IF(@RecallType = 'SM-NBPRE' AND NOT EXISTS (SELECT Recall_ID FROM > RECALL > WHERE [Type] = 'SM-NBCRE' AND SOC_SEC = @SOC_SEC AND COMPLETE IS NOT > NULL > AND DATEDIFF(day, Complete,@DateQueued ) >= 6 AND Complete > > @DateEngaged)) > BEGIN > SET @Retval = 0 > SET @Error = 'Does not meet pre-requisite requirements (SM-NBCRE not > found > completed at least 6 days prior).' > END > IF(@RecallType = 'SM-NBFRE' AND NOT EXISTS (SELECT Recall_ID FROM > RECALL > WHERE [Type] = 'SM-NBPRE' AND SOC_SEC = @SOC_SEC AND COMPLETE IS NOT > NULL > AND DATEDIFF(day, Complete,@DateQueued ) >= 4 AND Complete > > @DateEngaged)) > BEGIN > SET @Retval = 0 > SET @Error = 'Does not meet pre-requisite requirements (SM-NBPRE not > found > completed at least 4 days prior).' > END > END > IF @RecallType IN ('SM-NBPRTX', 'SM-NBFRT', 'L-NBPRTX', 'SM-NBPRT', > 'L-NBPRT') AND @Retval = 1 > BEGIN > IF NOT EXISTS(SELECT EngagementID FROM NBP_Engagement WHERE InternalID > = > @InternalID AND Mode IN('T', 'C') AND DateClosed IS NULL) > BEGIN > SET @Retval = 0 > SET @Error = 'This recall requires an open Telephonic engagement.' > END > IF(@RecallType IN('SM-NBPRTX', 'L-NBPRTX') AND NOT EXISTS (SELECT > Recall_ID > FROM RECALL WHERE [Type] = 'C-NBPS-A2' AND SOC_SEC = @SOC_SEC AND > COMPLETE > IS NOT NULL AND DATEDIFF(day, Complete,@DateQueued ) >= 7 AND Complete > > > @DateEngaged)) > BEGIN > SET @Retval = 0 > SET @Error = 'Does not meet pre-requisite requirements (C-NBPS-A2 not > found > completed at least 7 days prior).' > END > IF(@RecallType = 'SM-NBFRT' AND NOT EXISTS (SELECT Recall_ID FROM > RECALL > WHERE [Type] = 'SM-NBPRTX' AND SOC_SEC = @SOC_SEC AND COMPLETE IS NOT > NULL > AND DATEDIFF(day, Complete,@DateQueued ) >= 10 AND Complete > > @DateEngaged)) > BEGIN > SET @Retval = 0 > SET @Error = 'Does not meet pre-requisite requirements (SM-NBPRS not > found > completed at least 10 days prior).' > END > END > IF @RecallType IN('L-RENG', 'SM-RENG', 'SM-RENFU') AND @Retval = 1 > BEGIN > IF NOT EXISTS(SELECT EngagementID FROM NBP_Engagement WHERE Mode = 'S' > AND > datediff(day, DateClosed, getdate()) >= 3 and DateEngaged = > @DateEngaged) > BEGIN > SET @Retval = 0 > SET @Error = 'This recall requires a closed Self engagement with no new > engagements.' > END > IF(@RecallType = 'SM-RENFU' AND NOT EXISTS (SELECT Recall_ID FROM > RECALL > WHERE [Type] = 'SM-RENG' AND SOC_SEC = @SOC_SEC AND COMPLETE IS NOT > NULL AND > DATEDIFF(day, Complete,@DateQueued ) >= 7 AND Complete > > @DateEngaged)) > BEGIN > SET @Retval = 0 > SET @Error = 'Does not meet pre-requisite requirements (SM-RENG not > found > completed at least 7 days prior).' > END > END > END > SELECT @Retval as OK, @Error as Error > END > GO > > > -- > View this message in context: http://www.nabble.com/Nant-SQL-Task- > limitations---problems-tp24375158p24375158.html > Sent from the NAnt - Users mailing list archive at Nabble.com. > > > ----------------------------------------------------------------------- > ------- > Enter the BlackBerry Developer Challenge > This is your chance to win up to $100,000 in prizes! For a limited > time, > vendors submitting new applications to BlackBerry App World(TM) will > have > the opportunity to enter the BlackBerry Developer Challenge. See full > prize > details at: http://p.sf.net/sfu/blackberry > _______________________________________________ > NAnt-users mailing list > NAnt-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/nant-users ------------------------------------------------------------------------------ Enter the BlackBerry Developer Challenge This is your chance to win up to $100,000 in prizes! For a limited time, vendors submitting new applications to BlackBerry App World(TM) will have the opportunity to enter the BlackBerry Developer Challenge. See full prize details at: http://p.sf.net/sfu/blackberry _______________________________________________ NAnt-users mailing list NAnt-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/nant-users
