Incorrect syntax near 'END'

Question

Post reply

Incorrect syntax near 'END'

bob.barker

SSC Rookie

Points: 34
More actions
November 18, 2009 at 5:21 am

#135252

Hi
I have a customer with a VB6 application with a SQL 2005 database.
The VB6 application sits on different servers to the SQL server.
They migrated their servers at the weekend from Windows Server 2003 32 bit to Windows Server 2003 64 bit. The SQL was upgraded from MSDE2000 to 2005 standard edition 64 bit.
They have reported the following error on one process:
Microsoft OLE DB Provider for SQL Server
Incorrect syntax near 'END'
Here's the line of VB code that causes the error
rstNotes.Open strSP, gcnnConn, adOpenStatic, adLockReadOnly, adCmdStoredProc
strSP is set as:
strSP = "up_parmsel_SINNotes (" & CStr(lngSINExtractID) & ")"
I find that if I rerun the SQL script that created the stored procedure it works OK without error a few times. And then it comes back. I can not pin point when it comes back. I'm pretty sure it's not data specific.
The SQL script that creates the stored procedure is
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[up_parmsel_SINNotes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.up_parmsel_SINNotes
GO
CREATE PROCEDURE dbo.up_parmsel_SINNotes
@SINExtractID INT AS
BEGIN
SELECT MAX(vcNote) AS vcNote, MAX(CASE btMandatory WHEN 1 THEN 1 ELSE 0 END) AS btMandatory
FROM dbo.tblSINLinesExtract
WHERE SINExtractID = @SINExtractID
GROUP BY intSINID
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
When the user reports the error I find I am able to execute the stored procedure without error from within SQL Management Studio on the server which hosts the SQL database itself.
They have not reported any problems with any of the other numerous database scripts called by the VB app.
Any ideas?

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

bob.barker SSC Rookie Points: 34 More actions · Answer 1

Hi

I'm not sure if this may help further.

I ran a trace. When it fails to the user I get this

declare @p1 int

set @p1=0

declare @p3 int

set @p3=557064

declare @p4 int

set @p4=98305

declare @p5 int

set @p5=0

exec sp_cursoropen @p1 output,N' EXEC up_parmsel_SINNotes 939495 ',@p3 output,@p4 output,@p5 output

select @p1, @p3, @p4, @p5

Paste this into SSMS and I do get back

Msg 102, Level 15, State 1, Procedure up_parmsel_SINNotes, Line 6

Incorrect syntax near 'END'.

Msg 16945, Level 16, State 2, Procedure sp_cursoropen, Line 1

The cursor was not declared.

However if in SSMS I query

DECLARE @RC int

DECLARE @SINExtractID int

SET @SINExtractID = 939495

EXECUTE @RC = [DENMAUR1].[dbo].[up_parmsel_SINNotes]

@SINExtractID

I get a result OK.

What may be different here?

bob.barker SSC Rookie Points: 34 More actions · Answer 2

Hi

I have been able to progress this a little.

I may be resolved by installing a hot fix/service pack

http://support.microsoft.com/kb/913371

If I also add "WITH RECOMPILE" to the failign script copied from my trace then this also seems to work.

But I'm not sure how to introduce this into my stored procedure? Or maybe I just need to try the hot fix/service pack?

bob.barker SSC Rookie Points: 34 More actions · Answer 3

I have added "WITH RECOMPILE" into my stored procedure and this appears to have fixed it.

This will buy me time whilst I plan and execute the service pack install.