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
