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 '
