Line 1: Incorrect syntax near

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)DateofActio n, '
+ @.DbName+'.Resadm.Action.Status, '+@.DbName+'.Resadm.Action.Subtype, '
+ @.DbName+'.ResAdm.Action_Types.ActionTypeName,
'+@.DbName+'.Resadm.Action_subtypes.ActionSubtypeID , '
+ @.DbName+'.Resadm.Action_subtypes.EffectCandidateCu rrentState,
'+@.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.Dateof Action +''
''+ '+@.DbName+'.Resadm.Action.TimeOfAction) > '
+ ' ISNULL(( SELECT
MAX(CONVERT(DATETIME,'+@.DbName+'.Resadm.Action.Dat eofAction + '' ''+ '
+ @.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.DateofA ction +
'' ''+ '+@.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}Sandesh (sandesh27uk@.gmail.com) writes:
> 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'."

You will have to excuse me, but the error looks very standard and
commonplace to me.

This is what you get when you entagle yourself into a web of dynamic
SQL. Look at http://www.sommarskog.se/dynamic_sql.html#Dyn_DB for
suggestions of alternative strategies.

My bets goes on the UPDATE statement at the end. Overall, including
the database name in the column qualifications is overkill.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx