LIMIT problems: syntax

Showing posts with label syntax. Show all posts

Monday, March 19, 2012

Line 1: Incorrect Syntax Near.......(is this a bug?)

Hi,

Uses: Sql server 2000, asp.net 1.1;

I've the following Stored Procedure:

<SQL Code>
CREATE PROCEDURE PABX_sp_MyPABX_ShowExtWatch
@.FromDate DATETIME,
@.ToDate DATETIME,
@.PortalID INT = 1,
@.UserID INT
AS
SELECT PABX_Ext_Watch.ExtNo As ExtNo, COUNT(*) AS Calls, SUM(PABX_Calls.Rate) AS NetAmt
FROM PABX_Ext_Watch
INNER JOIN PABX_Calls
ON PABX_Ext_Watch.ExtNo = PABX_Calls.ExtNo
INNER JOIN PABX_PortalUsers
ON PABX_Ext_Watch.PortalUserID = PABX_PortalUsers.PortalUserID
AND PABX_Calls.PortalID = PABX_PortalUsers.PortalID
WHERE PABX_Calls.sTime >= CONVERT(DATETIME, @.FromDate, 102)
AND PABX_Calls.eTime <= CONVERT(DATETIME, @.ToDate, 102)
AND PABX_PortalUsers.uID = @.UserID
AND PABX_PortalUsers.PortalID = @.PortalID
GROUP BY PABX_Ext_Watch.ExtNo
GO
</SQL Code>

However if I execute this stored procedure in asp.net I get the following error, despite the stored procedure executed successfully in query analyzer:

Line 1: Incorrect Syntax Near ......

When I check the sql profiler the stored procedure is been executed in this way:

exec sp_executesql N'PABX_sp_MYPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

I couldn't figure it out though sql server management console and query analyzer showed no signs of errors. I sense that the sql is not being executed correctly with letters as "N" in the sql profiler.

Could anyone help me out on this?

Thanks & Regards,

Try and execute the procedure like this

execute PABX_sp_MYPABX_ShowExtWatch @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

|||You might try converting(smalldatetime,@.FromDate,102) instead of datetime.|||

Hi,

Thanks for both of your responses, but the problem still remains even after trying out your suggestions. This is the way I have wrote the code in asp.net:

<code>
PublicFunction ShowExtWatchList(ByVal UserID AsInteger, ByVal FromDate As DateTime, ByVal Todate As DateTime) As SqlDataReader

Dim oDr As SqlDataReader
Dim oCmd AsNew SqlCommand("PABX_sp_MyPABX_ShowExtWatch", oComConn)

With oCmd
.Parameters.Add(New SqlParameter("@.FromDate", SqlDbType.DateTime)).Value = FromDate
.Parameters.Add(New SqlParameter("@.ToDate", SqlDbType.DateTime)).Value = Todate
.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int)).Value = UserID
.Parameters.Add(New SqlParameter("@.PortalID", SqlDbType.Int)).Value = _PortalID
EndWith

If oComConn.State = ConnectionState.Open Then
oComConn.Close()
EndIf

oComConn.Open()
oDr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

If oDr.HasRows Then
Return oDr
Else
ReturnNothing
EndIf

EndFunction
</code>

However one thing really kicks me. I checked that those stored procedures which didnt give errors (another) and successfully executed without any problems showed in the profiler in this pattern (for example):

exec PABX_sp_MyPABX_DepHead_Summary @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.DepHeadID = 207, @.PortalID = 1

but the the one which gives error showed in the profiler as:

exec sp_executesql N'PABX_sp_MyPABX_ShowExtWatch', N'@.FromDate datetime,@.ToDate datetime,@.UserID int,@.PortalID int', @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 1

Look at the 2 different executions of SPs. I think ,at some point, sql server doesn't recognise this as a SP and doesn't executes in some other way instead of the usuall way or asp.net sends in the wrong way to execute....yet the T-sqls are correct. Is this a bug?

|||

Hi,

I figured it out what was the cause. It's in Asp.net itself and not in Sql server neither tsql. I've missed out this piece of code which caused the entire problem:

"SqlCommand.CommandType = CommandType.StoredProcedure"

Line 1: Incorrect Syntax Near.......

Hi,

Uses: Sql server 2000, asp.net 1.1;

I've the following Stored Procedure:

However if I execute this stored procedure in asp.net I get the following error, despite the stored procedure executed successfully in query analyzer:

Line 1: Incorrect Syntax Near ......

When I check the sql profiler the stored procedure is been executed in this way:

Could anyone help me out on this?

Thanks & Regards,

Try and execute the procedure like this

execute PABX_sp_MYPABX_ShowExtWatch @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.UserID = 207, @.PortalID = 2

|||You might try converting(smalldatetime,@.FromDate,102) instead of datetime.|||

Hi,

Thanks for both of your responses, but the problem still remains even after trying out your suggestions. This is the way I have wrote the code in asp.net:

<code>
PublicFunction ShowExtWatchList(ByVal UserID AsInteger, ByVal FromDate As DateTime, ByVal Todate As DateTime) As SqlDataReader

Dim oDr As SqlDataReader
Dim oCmd AsNew SqlCommand("PABX_sp_MyPABX_ShowExtWatch", oComConn)

If oComConn.State = ConnectionState.Open Then
oComConn.Close()
EndIf

oComConn.Open()
oDr = oCmd.ExecuteReader(CommandBehavior.CloseConnection)

If oDr.HasRows Then
Return oDr
Else
ReturnNothing
EndIf

EndFunction
</code>

exec PABX_sp_MyPABX_DepHead_Summary @.FromDate = 'Sep 1 2006 12:00:00:000AM', @.ToDate = 'Sep 30 2006 12:00:00:000AM', @.DepHeadID = 207, @.PortalID = 1

but the the one which gives error showed in the profiler as:

|||

Hi,

I figured it out what was the cause. It's in Asp.net itself and not in Sql server neither tsql. I've missed out this piece of code which caused the entire problem:

"SqlCommand.CommandType = CommandType.StoredProcedure"

Line 1: Incorrect syntax near 're'.

Hi Guys,
This is my Sql Query to insert values. All the fields are nvarchar
except the postcode which is Int. I have a condition if the postcode
field is numeric then insert values otherwise error message. Only one
of the customer gets the message on his browser
"Line 1: Incorrect syntax near 're'.
/westernsydney/register.asp, line 198 "
But other registrations are still going through with this code. Can
someone tell me why this is happening
strSql5 = "INSERT INTO tblattendeem
(attendee_firstname,attendee_surname,att
endee_location_street1,attendee_loca
tion_suburb,attendee_location_postcode,a
ttendee_company_name,attendee_phone,
attendee_email,event_id)"_
& "VALUES
('"&Request.QueryString("firstname")&"','"&Request.QueryString("surname")&"'
,"_
&"
'"&Request.QueryString("address")&"','"&Request.QueryString("suburb")&"',"_
&" '"&Request.QueryString("postcode")&"',
'"&Request.QueryString("company")&"',"_
&" '"&Request.QueryString("phone_number")&"',
'"&Request.QueryString("email")&"',"_
&" '"&Request.Cookies("event_id")&"' )"
Many ThanksHey Nachi, without looking at your sample data, it's difficult to tell
what's going on. My guess is that you have a customer with either a
quote (") or an apostrophe (') in the column.
There are other issues that you be aware of, however; building a string
like you are doing increases the chances that someone will be able to
inject another SQL statement into your code, thus doing damange to your
application. See http://aspalliance.com/385 for an example.
Also, do any of your postal codes start with a 0? If so, you may want
reconsider using an integer to store the data.
HTH,
Stu|||Hi Stu,
Thank you so much for your time & Quick reply. This Groups is really
useful. Im waiting on the Data from this specific customer. thank you
for your suggestion as well on Sql Injection. I will impl this in
future.
K Regards,
Nachi
Stu wrote:
> Hey Nachi, without looking at your sample data, it's difficult to tell
> what's going on. My guess is that you have a customer with either a
> quote (") or an apostrophe (') in the column.
> There are other issues that you be aware of, however; building a string
> like you are doing increases the chances that someone will be able to
> inject another SQL statement into your code, thus doing damange to your
> application. See http://aspalliance.com/385 for an example.
> Also, do any of your postal codes start with a 0? If so, you may want
> reconsider using an integer to store the data.
> HTH,
> Stu|||To emphasize Stu's point, there are hackers who do nothing all day but
search for web sites to exploit SQL injection vulnerabilities. If your site
is exposed to the public internet, be sure to heed Stu's advice use only
parameterized SQL queries. I hope that 'in the future' means now.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nachi" <Rengenath@.gmail.com> wrote in message
news:1148612435.121757.148630@.j33g2000cwa.googlegroups.com...
> Hi Stu,
> Thank you so much for your time & Quick reply. This Groups is really
> useful. Im waiting on the Data from this specific customer. thank you
> for your suggestion as well on Sql Injection. I will impl this in
> future.
> K Regards,
> Nachi
> Stu wrote:
>

Line 1: Incorrect syntax near getlistofspclass.

what am i doing wrong.. :(

CREATE PROCEDURE getlistofspclass
(
@.date datetime,
@.s varchar(8000) output
)

select @.s = coalesce(@.s + ' & ', '') + '"' + convert(varchar(10),dbo.calader.timestart)+ ' ' + dbo.classT.classname + '<p>'
FROM dbo.calader INNER JOIN dbo.classT ON dbo.calader.classid = dbo.classT.classcode
WHERE dbo.calader.caledardatedates = @.date
exec( @.s)
GO

The procedure's parameters should not be enclosed in parentheses. Remove the parentheses that surround the parameters and you should have better luck.|||

my god!! i spent 4 hours on that and $60 on a Que sql server programming book. :(

but now it works. i can hang myself now

|||

hello can you help me again. ...my asp.net code is not working.. it will not send the date to the pramater.

can you make a sameple code in asp.net vb of how you will send a date to that stored procuder and how you would get the returned value

please. thank you verymuch

|||

All I have is a C# version I made the other day, sorry. But it's close enough to VB you should be able to figure out how to port it.

1public int InsertNewThread(Thread ThreadToInsert,out int InternalID)2{3using(SqlConnection sqlConnection =new SqlConnection(GetConnectionString()))4{5SqlCommand sqlCommand =new SqlCommand("Thread_Insert", sqlConnection);6sqlCommand.CommandType = CommandType.StoredProcedure;7sqlCommand.Parameters.Add("@.threadSummary", SqlDbType.NVarChar, 75).Value = ThreadToInsert.ThreadSummary;8sqlCommand.Parameters.Add("@.boardID", SqlDbType.Int).Value = ThreadToInsert.BoardID;9sqlCommand.Parameters.Add("@.internalID", SqlDbType.Int).Direction = ParameterDirection.Output;10sqlConnection.Open();11int RowsEffected = sqlCommand.ExecuteNonQuery();12sqlConnection.Close();13InternalID = (int) sqlCommand.Parameters["@.internalID"].Value;14return RowsEffected;15}16}

12/****** Object: Stored Procedure dbo.Thread_Insert Script Date: 7/28/2006 7:24:19 PM ******/3CREATE PROCEDURE Thread_Insert4@.threadSummaryNVARCHAR( 75 ),5@.internalIDINT OUTPUT6AS7INSERT INTO8 threads( threadSummary )910VALUES11 ( @.threadSummary )1213SELECT14@.internalID = SCOPE_IDENTITY ( )1516GO

|||

thanks again. you know the speed of getting the return value was just the same as putting the sql statement on the webpage itself...people say storedprocedures are faster..oh well

thanks everything worked

|||

Stored procedures are faster. If you coded one page without stored procedures, and another with stored procedures, and timed them with your watch, then you won't notice much.

Take a web server (or multiple web servers) using complex queries and time the difference when they are getting hit a few hundred requests per second, and you will notice a difference, especially in older versions of SQL Server. The newer ones have added a few little things that reduce the difference dramatically in most cases (Cached query plans, even for dynamic queries). They basically turn even dynamic queries into a kind of "temporary" stored procedure, getting most of the sp's benefits, a very nice addition to SQL Server.

Line 1: Incorrect syntax near =

Hi,

I'm getting the above error when i try to fill a Dataset through a
dataAdapter.

I presume it is to do with the sql statement. Below is the relevant
code:

string strPntUnitID = patientCodeLbl.Text;
string strPntFName = fNameLbl.Text;
string strPntLName = lNameLbl.Text;

// Create DataAdapter & Dataset
SqlDataAdapter daRelateDocToPnt = new SqlDataAdapter("SELECT patientNo,
doctorNo FROM tblPatient" +
"WHERE (pntUnitID = '"+ strPntUnitID +"') AND (pntFName = '"+
strPntFName +"')"+
"AND (pntLName = '"+ strPntLName +"')", conn);

DataSet dsDocNoToPnt = new DataSet();

// Create command builder, automatically generates the update commands
SqlCommandBuilder pntCmd = new SqlCommandBuilder(daRelateDocToPnt);

// Set the MissingSchemaAction property to AddWithKey because Fill will
not cause primary
// key & unique key information to be retrieved unless AddWithKey is
specified.
daRelateDocToPnt.MissingSchemaAction = MissingSchemaAction.AddWithKey;

// Use dataAdapter to fill DataSet
daRelateDocToPnt.Fill(dsDocNoToPnt, "Patient");

// place intDocNo into the dataset schema
dsDocNoToPnt.Tables["Patient"].Rows[0]["doctorNo"] = "intDocNo";

// Update The Database
daRelateDocToPnt.Update(dsDocNoToPnt, "Patient");

Can anyone spot the problem?

ThanksAt a quick glance, it seems that you have no space between tblPatient
and WHERE, but without seeing the actual command, it's hard to say. If
you build a SQL string dynamically, it's a good idea to provide a debug
mechanism - perhaps you can build up the string first and display it,
then instantiate the adapter using the string?

You might also want to consider writing a stored procedure to return
the results, and pass the ID and names as parameters.

Simon|||Thanks Simon , your quick glance worked, the space was the problem.|||Assimalyst (c_oxtoby@.hotmail.com) writes:
> I presume it is to do with the sql statement. Below is the relevant
> code:
> string strPntUnitID = patientCodeLbl.Text;
> string strPntFName = fNameLbl.Text;
> string strPntLName = lNameLbl.Text;
> // Create DataAdapter & Dataset
> SqlDataAdapter daRelateDocToPnt = new SqlDataAdapter("SELECT patientNo,
> doctorNo FROM tblPatient" +
> "WHERE (pntUnitID = '"+ strPntUnitID +"') AND (pntFName = '"+
> strPntFName +"')"+
> "AND (pntLName = '"+ strPntLName +"')", conn);

Rather than building the entire command this way, use parameterised
commands:

"SELECT patientNo, doctorNo FROM tblPatient " +
"WHERE (pntUnitID = @.PntUnitID AND (pntFName = @.strPntFName " +
"AND (pntLName = @.PntLName "

The use the parameters collection on the command object to define the
parameter.

If you wonder why, try your current code with someone whose last name
is O'Brien.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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