|
sandesh27uk
Starting Member
2 Posts |
 Posted - 2006-06-06 : 08:26:11
|
| Hello All, Me saying " has any body come across such error would be underestimating". Well I am getting a very peculiar and unique error "Line 1: Incorrect syntax near 'Actions'." Explaining you the scene is the following Stored Proc. This stored proc is execute from a VB code in the .net application as like: - {Try Connection.Init_Variables() cn.ConnectionString = Connection.gstrConnection ResDb.ConnectionString = Connection.gresConnection cn.Open() With sqlCmd .Connection = cn .CommandText = "DSP_Get_Required" .CommandType = CommandType.StoredProcedure .Parameters.Add("@ActionId", SqlDbType.Int, 9).Value = ActionID .Parameters("@ActionId").Direction = ParameterDirection.InputOutput .Parameters.Add("@PersonID", SqlDbType.Int, 9).Value = PersonID .Parameters("@PersonID").Direction = ParameterDirection.InputOutput .Parameters.Add("@ReturnMessage", SqlDbType.VarChar, 1000).Value = ReturnMessage.ToString .Parameters("@ReturnMessage").Direction = ParameterDirection.InputOutput .Parameters.Add("@Exists", SqlDbType.Bit, 1).Value = Exists .Parameters("@Exists").Direction = ParameterDirection.InputOutput .Parameters.Add("@Days", SqlDbType.Int, 9).Value = 0 .Parameters("@Days").Direction = ParameterDirection.InputOutput .Parameters.Add("@StartDate", SqlDbType.DateTime).Value = Now() .Parameters("@StartDate").Direction = ParameterDirection.InputOutput .Parameters.Add("@EndDate", SqlDbType.DateTime).Value = Now() .Parameters("@EndDate").Direction = ParameterDirection.InputOutput .Parameters.Add("@OutCome", SqlDbType.VarChar, 20).Value = "Stop" .Parameters("@OutCome").Direction = ParameterDirection.InputOutput .Parameters.Add("@Evaluate", SqlDbType.Int, 9).Value = 0 .Parameters("@Evaluate").Direction = ParameterDirection.InputOutput .Parameters.Add("@DbName", SqlDbType.VarChar, 100).Value = ResDb.Database.ToString .Parameters("@DbName").Direction = ParameterDirection.InputOutput .ExecuteReader(CommandBehavior.Default) } On Execution I get the subjected Error "Line 1: Incorrect syntax near 'Actions'." Any Ideas from your all experience to get away from this error will be helpful. Look forward to read somebody soon. Stored Proc:- {SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N'dbo.DSP_Get_Required_ActionS') AND OBJECTPROPERTY(id, N'ISPROCEDURE') = 1) DROP PROCEDURE dbo.DSP_Get_Required_ActionS GO CREATE PROCEDURE DSP_Get_Required_ActionS @ActionID INT OUTPUT, @PersonID INT OUTPUT, @ReturnMessage Varchar(1000) OUTPUT, @Exists BIT OUTPUT, @Days INT OUTPUT, @StartDate DATETIME OUTPUT, @EndDate DATETIME OUTPUT, @OutCome VARCHAR(20) OUTPUT, @Evaluate INT OUTPUT, @DbName VARCHAR(100) OUTPUT AS SET NOCOUNT ON --DECLARE @PopulateSQL AS NVarchar(4000) DECLARE @Rule_ID AS NUMERIC(9) DECLARE @Curr_ActionSubType AS VARCHAR(20) DECLARE @Eval_SubType AS VARCHAR(20) -- DECLARE @OutCome AS VARCHAR(20) -- DECLARE @Evaluate AS INT -- DECLARE @Days AS INT DECLARE @Message AS VARCHAR(1000) DECLARE @Mandatory AS BIT -- This is the variable used to interpret the Precedant subtype DECLARE @Prec_Subtype AS VARCHAR(20) -- DECLARE @Exists AS BIT --this is supposed to be the deceision maker variable to be used within the precedant check. DECLARE @Precedant_SubTypes_Cnt AS INT --This is the variable used to recordcount the Precedant Subtypes to be checked DECLARE @Counter AS INT -- Counter used to loop through the Table of precedant Subtypes. DECLARE @ROWCOUNT AS INT -- Temporary ROWCOUNT SET @Counter = 1 --Process to retrive @Curr_ActionSubType Variable CREATE TABLE #Curr_ActionSubType(ActionSubType VARCHAR(100)) EXEC ('INSERT INTO #Curr_ActionSubType SELECT SubType FROM '+ @DbName +'.resadm.action WHERE '+@DbName+'.resadm.action.Actionid = '+ @ActionID+' AND ' +@DbName+'.resadm.action.status =''A''') SET @Curr_ActionSubType = (Select ActionSubType from #Curr_ActionSubType) DROP TABLE #Curr_ActionSubType --Process to retrive @StartDate Variable CREATE TABLE #StartDate(StartDate DATETIME) EXEC('INSERT INTO #StartDate SELECT CONVERT(DATETIME, '+@DbName+'.Resadm.Action.DateofAction + '' ''+ '+@DbName+'.Resadm.Action.TimeOfAction) FROM '+@DbName+'.resadm.action WHERE '+@DbName+'.resadm.action.Actionid = '+ @ActionID +' AND '+@DbName+'.resadm.action.status =''A''' ) SET @StartDate = (Select StartDate from #StartDate) DROP TABLE #StartDate SET @Rule_ID = (SELECT Rule_ID FROM Rules WHERE Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY RULE_ID) SET @Eval_SubType = (SELECT Evaluate_Subtype FROM Rules WHERE Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY Evaluate_Subtype) SET @OutCome = (SELECT OutCome FROM Rules WHERE Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY OutCome) SET @Evaluate = (SELECT Evaluate FROM Rules WHERE Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY Evaluate) SET @Days = (SELECT Days FROM Rules WHERE Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY Days) SET @Message = (SELECT Message FROM Rules WHERE Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY Message) SET @Mandatory = (SELECT Optional_Mandatory_Precedant FROM Rules WHERE Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY Optional_Mandatory_Precedant) -- create the temporary table for the Subtypes to be evaluated CREATE TABLE #Preceding_SubTypes_Details ( SubTypes_LIST_ID INT IDENTITY (1, 1) NOT NULL, RULE_ID NUMERIC(9), SubType VARCHAR(20), ) -- insert the current subtype that needs to be evaluated. INSERT INTO #Preceding_SubTypes_Details SELECT Rule_ID, Prec_Subtype FROM Rules_Details WHERE Rule_ID = @Rule_ID -- create the History table for Reference --sk/* Modified to accomodatethe need ot dynamic database name to retrive from the different Resman databases CREATE TABLE #dsHistory ( ActionID INT, PersonID INT, ActionTypeID VARCHAR(1), DateofAction DATETIME, Status VARCHAR(1), Subtype VARCHAR(6), ActionTypeName VARCHAR(30), ActionSubtypeID VARCHAR(6), EffectCandidateCurrentState VARCHAR(10), TaxCode VARCHAR(6) ) EXEC ('INSERT INTO #dsHistory SELECT '+@DbName+'.Resadm.Action.ActionID, ' + @DbName+'.Resadm.Action.PersonID, '+@DbName+'.Resadm.Action.ActionTypeID, ' + 'CONVERT(DATETIME, '+@DbName+'.Resadm.Action.DateofAction + '' ''+ '+@DbName+'.Resadm.Action.TimeOfAction)DateofAction, ' + @DbName+'.Resadm.Action.Status, '+@DbName+'.Resadm.Action.Subtype, ' + @DbName+'.ResAdm.Action_Types.ActionTypeName, '+@DbName+'.Resadm.Action_subtypes.ActionSubtypeID, ' + @DbName+'.Resadm.Action_subtypes.EffectCandidateCurrentState, '+@DbName+'.Resadm.Person.TaxCode ' + ' FROM '+@DbName+'.Resadm.Action ' + ' INNER JOIN '+@DbName+'.ResAdm.Action_Types WITH(NOLOCK) ' + ' ON '+@DbName+'.ResAdm.Action_Types.ActionTypeID = '+@DbName+'.Resadm.Action.ActionTypeID ' + ' INNER JOIN '+@DbName+'.ResAdm.Action_SubTypes WITH(NOLOCK) ' + ' ON '+@DbName+'.Resadm.Action.subtype = '+@DbName+'.ResAdm.Action_SubTypes.actionsubtypeid ' + ' INNER JOIN '+@DbName+'.Resadm.Person WITH(NOLOCK) ' + ' ON '+@DbName+'.Resadm.Person.PersonID = '+@DbName+'.Resadm.Action.PersonID ' + ' WHERE '+@DbName+'.Resadm.Action.actionID <> CONVERT(VARCHAR,'+@ActionID+')' + ' AND '+@DbName+'.Resadm.Action.PersonID = CONVERT(VARCHAR,'+@PersonID+')' + ' AND '+@DbName+'.Resadm.Action.Status =''A'' ' + 'AND (CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofAction +'' ''+ '+@DbName+'.Resadm.Action.TimeOfAction) > ' + ' ISNULL(( SELECT MAX(CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofAction + '' ''+ ' + @DbName+'.Resadm.Action.TimeOfAction)) ' + ' FROM '+@DbName+'.Resadm.Action ' + ' WHERE ('+@DbName+'.Resadm.Action.PersonID = CONVERT(VARCHAR,'+@PersonID+')) AND ' + ' ('+@DbName+'.Resadm.Action.Subtype =''ZERO'') ' + ' AND ('+@DbName+'.Resadm.Action.Status=''A'')),0)) ' + ' ORDER BY CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofAction + '' ''+ '+@DbName+'.Resadm.Action.TimeOfAction )DESC, ActionID DESC') --sk*/ SET @EndDate = (SELECT ISNULL((SELECT DateOfAction FROM #dsHistory WHERE SubType = @Eval_SubType), getdate())) -- set the rowcount to retrieve the number of check to be carried out SET @Precedant_SubTypes_Cnt = (SELECT COUNT(*) FROM #Preceding_SubTypes_Details) WHILE @Counter <= @Precedant_SubTypes_Cnt BEGIN SET @Prec_Subtype = (SELECT SubType from #Preceding_SubTypes_Details WHERE SubTypes_LIST_ID = @Counter) SET @ROWCOUNT = (SELECT COUNT(*) FROM #dsHistory WHERE Subtype = @Prec_Subtype) IF @ROWCOUNT > 0 BEGIN SET @Exists = 1 END IF @ROWCOUNT = 0 BEGIN IF @Mandatory = 1 BEGIN SET @Message = @Message + ' ' + @Prec_Subtype + ' Is Missing; ' SET @Counter = @Precedant_SubTypes_Cnt SET @Exists = 0 END ELSE IF @Mandatory = 0 BEGIN SET @Message = @Message + ' ' + @Prec_Subtype + ' Is Missing; ' SET @Exists = @Exists END END SET @Counter = @Counter+1 END IF @Exists = 0 BEGIN EXEC( ' UPDATE '+@DbName+'.Resadm.Action ' + ' SET '+@DbName+'.Resadm.Action.Status = ''I'' ' + ' WHERE '+@DbName+'.Resadm.Action.ActionID = '+@ActionID+' SET @ReturnMessage = '+@Message ) END ELSE IF @Exists = 1 BEGIN SET @ReturnMessage = @Message END IF @Rule_ID = Null BEGIN SET @ReturnMessage = 'Validation Rule Not Present' END -- Select 'Exist value : ', @Exists, 'Return message is : ', @ReturnMessage DROP TABLE #Preceding_SubTypes_Details DROP TABLE #dshistory GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO} |
|
Line 1: Incorrect syntax near