Incorrect syntax near 'END' 1

[RBSTR]

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?

 

[bborissov]

Why you

GROUP BY intSINID

if that field is not presented in the field list area of the query?
Also, start SQL Profiler and see what is sent to SQL server when you try to execute that SP. Copy code and paste it in SSMS and try to execute it.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[RBSTR]

Thanks for the response

intSINID is a field I need to group on but not read so I do not include it in my select. I have tried adding it in but still get the problem.

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?

 

[gmmastros]

The SQL was upgraded from MSDE2000 to 2005 standard edition 64 bit.

run this:

sp_dbcmptlevel 'DENMAUR1'

What does it return?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

Strange:
That works for me in SSMS:

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
exec sp_cursorclose @p1

I did this:

CREATE DATABASE TestMe
GO
USE TestMe
GO
CREATE TABLE dbo.tblSINLinesExtract (vcNote varchar(20), btMandatory bit, SINExtractID int, intSINID int)
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
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
exec sp_cursorclose @p1

and everything works w/o error?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[RBSTR]

Thanks for the response it returns

The current compatibility level is 80.

 

[RBSTR]

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?

 

[gmmastros]

To add "with recompile"

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 [!]WITH RECOMPILE[/!] 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

You don't need to change the code the calls the stored procedure. When you add "with recompile" to a stored procedure, you are essentially telling SQL Server to "re-create" the procedure each time you call it.

recompiling the stored procedure takes time. Obviously, complicated stored procedures will take longer to recompile than simple ones. This procedure is pretty simple so the recompile time should be very short. If this procedure is called MANY times per second, then you may want to keep looking for a better solution. But, if this is called infrequently, I wouldn't worry about it too much.

Bottom line, don't add "with recompile" to all your procedures simply because it solves this particular problem with this particular procedure.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom