Odd syntax issue - didn't happen in SQL 2000 - error in SQL 2005

I had this line in a very, very large stored procedure
If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
PenHrs<>0) Update #PY_Tbl Set After1987=1
Restored:
and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
Incorrect Syntax near â'Râ'.(-2147217900)
Changing the label RESTORED: to XYZ: make the error change to: ...near "X"
So I changed the code to this and it works now:
If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
PenHrs<>0)
Begin
Update #PY_Tbl Set After1987=1
End
Restored:
Any ideas why?Steve,
SQL Server syntax is in the process of changing and becoming more ANSI.
That means that occasionally an assumption that 2000 was willing to make,
2005 is unwilling to make. The new approved syntax for ensuring that a
command is terminated is to close with ;
If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
PenHrs<>0) Update #PY_Tbl Set After1987=1;
Restored:
FWIW,
RLF
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
>I had this line in a very, very large stored procedure
> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> PenHrs<>0) Update #PY_Tbl Set After1987=1
> Restored:
> and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
> Incorrect Syntax near "R".(-2147217900)
> Changing the label RESTORED: to XYZ: make the error change to: ...near "X"
> So I changed the code to this and it works now:
> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> PenHrs<>0)
> Begin
> Update #PY_Tbl Set After1987=1
> End
> Restored:
> Any ideas why?
>|||That look promising...
But unfortunately the ";" character did not fix the syntax error.
And keep in mind that this is during run-time of the SPROC and not compiling
into the DB.
"Russell Fields" wrote:
> Steve,
> SQL Server syntax is in the process of changing and becoming more ANSI.
> That means that occasionally an assumption that 2000 was willing to make,
> 2005 is unwilling to make. The new approved syntax for ensuring that a
> command is terminated is to close with ;
> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> PenHrs<>0) Update #PY_Tbl Set After1987=1;
> Restored:
> FWIW,
> RLF
> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
> news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
> >I had this line in a very, very large stored procedure
> >
> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> > PenHrs<>0) Update #PY_Tbl Set After1987=1
> >
> > Restored:
> >
> > and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
> >
> > Incorrect Syntax near "R".(-2147217900)
> >
> > Changing the label RESTORED: to XYZ: make the error change to: ...near "X"
> >
> > So I changed the code to this and it works now:
> >
> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> > PenHrs<>0)
> > Begin
> > Update #PY_Tbl Set After1987=1
> > End
> >
> > Restored:
> >
> > Any ideas why?
> >
>
>|||Can you provide a repro? Your code returned an *expected* error (no such table...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:1EF45CDB-E5AE-43B7-B664-BF25820FBE61@.microsoft.com...
> That look promising...
> But unfortunately the ";" character did not fix the syntax error.
> And keep in mind that this is during run-time of the SPROC and not compiling
> into the DB.
> "Russell Fields" wrote:
>> Steve,
>> SQL Server syntax is in the process of changing and becoming more ANSI.
>> That means that occasionally an assumption that 2000 was willing to make,
>> 2005 is unwilling to make. The new approved syntax for ensuring that a
>> command is terminated is to close with ;
>> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
>> PenHrs<>0) Update #PY_Tbl Set After1987=1;
>> Restored:
>> FWIW,
>> RLF
>> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
>> news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
>> >I had this line in a very, very large stored procedure
>> >
>> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
>> > PenHrs<>0) Update #PY_Tbl Set After1987=1
>> >
>> > Restored:
>> >
>> > and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
>> >
>> > Incorrect Syntax near "R".(-2147217900)
>> >
>> > Changing the label RESTORED: to XYZ: make the error change to: ...near "X"
>> >
>> > So I changed the code to this and it works now:
>> >
>> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
>> > PenHrs<>0)
>> > Begin
>> > Update #PY_Tbl Set After1987=1
>> > End
>> >
>> > Restored:
>> >
>> > Any ideas why?
>> >
>>|||I do not understand what you want me to supply? This error is easily
duplicated on the production server.
"Tibor Karaszi" wrote:
> Can you provide a repro? Your code returned an *expected* error (no such table...).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>|||Steve,
OK, I missed the boat. Which probably means that there is something going
on earlier that is at the root of the problem. My repro (comment out
whichever inserts you do not want to test) had no error on 2005, with or
without the semi-colon.
create table #Py_Tbl (After1987 int, AY INT, AM INT, PenHrs INT)
insert into #Py_Tbl values(55, 1986,7,2)
insert into #Py_Tbl values(66, 1985,7,2)
insert into #Py_Tbl values(77, 1986,7,0)
insert into #Py_Tbl values(77, 1987,0,0)
insert into #Py_Tbl values(77, 1987,0,2)
If Exists(Select * From #Py_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
PenHrs<>0)
update #PY_Tbl Set After1987=1
Restored:
SELECT * from #PY_Tbl
drop table #Py_Tbl
What next?
RLF
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:1EF45CDB-E5AE-43B7-B664-BF25820FBE61@.microsoft.com...
> That look promising...
> But unfortunately the ";" character did not fix the syntax error.
> And keep in mind that this is during run-time of the SPROC and not
> compiling
> into the DB.
> "Russell Fields" wrote:
>> Steve,
>> SQL Server syntax is in the process of changing and becoming more ANSI.
>> That means that occasionally an assumption that 2000 was willing to make,
>> 2005 is unwilling to make. The new approved syntax for ensuring that a
>> command is terminated is to close with ;
>> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
>> and
>> PenHrs<>0) Update #PY_Tbl Set After1987=1;
>> Restored:
>> FWIW,
>> RLF
>> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
>> news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
>> >I had this line in a very, very large stored procedure
>> >
>> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
>> > and
>> > PenHrs<>0) Update #PY_Tbl Set After1987=1
>> >
>> > Restored:
>> >
>> > and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
>> >
>> > Incorrect Syntax near "R".(-2147217900)
>> >
>> > Changing the label RESTORED: to XYZ: make the error change to: ...near
>> > "X"
>> >
>> > So I changed the code to this and it works now:
>> >
>> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
>> > and
>> > PenHrs<>0)
>> > Begin
>> > Update #PY_Tbl Set After1987=1
>> > End
>> >
>> > Restored:
>> >
>> > Any ideas why?
>> >
>>|||The SPROC is over 1000 lines. If calculates a persons pension.
The BEGIN/END block around the UPDATE #PY_TBL fixes the problem.
I'm feeling it's a true parsing bug in SQL - but I've got no way to
reproduce it in a smaller fashion.
"Russell Fields" wrote:
> Steve,
> OK, I missed the boat. Which probably means that there is something going
> on earlier that is at the root of the problem. My repro (comment out
> whichever inserts you do not want to test) had no error on 2005, with or
> without the semi-colon.
> create table #Py_Tbl (After1987 int, AY INT, AM INT, PenHrs INT)
> insert into #Py_Tbl values(55, 1986,7,2)
> insert into #Py_Tbl values(66, 1985,7,2)
> insert into #Py_Tbl values(77, 1986,7,0)
> insert into #Py_Tbl values(77, 1987,0,0)
> insert into #Py_Tbl values(77, 1987,0,2)
> If Exists(Select * From #Py_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
> PenHrs<>0)
> update #PY_Tbl Set After1987=1
> Restored:
> SELECT * from #PY_Tbl
> drop table #Py_Tbl
> What next?
> RLF
> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
> news:1EF45CDB-E5AE-43B7-B664-BF25820FBE61@.microsoft.com...
> > That look promising...
> >
> > But unfortunately the ";" character did not fix the syntax error.
> >
> > And keep in mind that this is during run-time of the SPROC and not
> > compiling
> > into the DB.
> >
> > "Russell Fields" wrote:
> >
> >> Steve,
> >>
> >> SQL Server syntax is in the process of changing and becoming more ANSI.
> >> That means that occasionally an assumption that 2000 was willing to make,
> >> 2005 is unwilling to make. The new approved syntax for ensuring that a
> >> command is terminated is to close with ;
> >>
> >> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
> >> and
> >> PenHrs<>0) Update #PY_Tbl Set After1987=1;
> >>
> >> Restored:
> >>
> >> FWIW,
> >> RLF
> >>
> >> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
> >> news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
> >> >I had this line in a very, very large stored procedure
> >> >
> >> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
> >> > and
> >> > PenHrs<>0) Update #PY_Tbl Set After1987=1
> >> >
> >> > Restored:
> >> >
> >> > and it worked in SQL 2000 for years - now in SQL 2005 I got this error:
> >> >
> >> > Incorrect Syntax near "R".(-2147217900)
> >> >
> >> > Changing the label RESTORED: to XYZ: make the error change to: ...near
> >> > "X"
> >> >
> >> > So I changed the code to this and it works now:
> >> >
> >> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
> >> > and
> >> > PenHrs<>0)
> >> > Begin
> >> > Update #PY_Tbl Set After1987=1
> >> > End
> >> >
> >> > Restored:
> >> >
> >> > Any ideas why?
> >> >
> >>
> >>
> >>
>
>|||Steve,
I certainly cannot argue with you about that possibility. And I understand
that it may not be worth pursuing any further.
RLF
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:6BA773B3-EF55-4B52-A379-B4961D856905@.microsoft.com...
> The SPROC is over 1000 lines. If calculates a persons pension.
> The BEGIN/END block around the UPDATE #PY_TBL fixes the problem.
> I'm feeling it's a true parsing bug in SQL - but I've got no way to
> reproduce it in a smaller fashion.
> "Russell Fields" wrote:
>> Steve,
>> OK, I missed the boat. Which probably means that there is something
>> going
>> on earlier that is at the root of the problem. My repro (comment out
>> whichever inserts you do not want to test) had no error on 2005, with or
>> without the semi-colon.
>> create table #Py_Tbl (After1987 int, AY INT, AM INT, PenHrs INT)
>> insert into #Py_Tbl values(55, 1986,7,2)
>> insert into #Py_Tbl values(66, 1985,7,2)
>> insert into #Py_Tbl values(77, 1986,7,0)
>> insert into #Py_Tbl values(77, 1987,0,0)
>> insert into #Py_Tbl values(77, 1987,0,2)
>> If Exists(Select * From #Py_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
>> and
>> PenHrs<>0)
>> update #PY_Tbl Set After1987=1
>> Restored:
>> SELECT * from #PY_Tbl
>> drop table #Py_Tbl
>> What next?
>> RLF
>> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
>> news:1EF45CDB-E5AE-43B7-B664-BF25820FBE61@.microsoft.com...
>> > That look promising...
>> >
>> > But unfortunately the ";" character did not fix the syntax error.
>> >
>> > And keep in mind that this is during run-time of the SPROC and not
>> > compiling
>> > into the DB.
>> >
>> > "Russell Fields" wrote:
>> >
>> >> Steve,
>> >>
>> >> SQL Server syntax is in the process of changing and becoming more
>> >> ANSI.
>> >> That means that occasionally an assumption that 2000 was willing to
>> >> make,
>> >> 2005 is unwilling to make. The new approved syntax for ensuring that
>> >> a
>> >> command is terminated is to close with ;
>> >>
>> >> If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987)
>> >> and
>> >> PenHrs<>0) Update #PY_Tbl Set After1987=1;
>> >>
>> >> Restored:
>> >>
>> >> FWIW,
>> >> RLF
>> >>
>> >> "Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
>> >> news:BC50C7F2-B093-45B2-9439-ACCD6BB141B6@.microsoft.com...
>> >> >I had this line in a very, very large stored procedure
>> >> >
>> >> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or
>> >> > AY>1987)
>> >> > and
>> >> > PenHrs<>0) Update #PY_Tbl Set After1987=1
>> >> >
>> >> > Restored:
>> >> >
>> >> > and it worked in SQL 2000 for years - now in SQL 2005 I got this
>> >> > error:
>> >> >
>> >> > Incorrect Syntax near "R".(-2147217900)
>> >> >
>> >> > Changing the label RESTORED: to XYZ: make the error change to:
>> >> > ...near
>> >> > "X"
>> >> >
>> >> > So I changed the code to this and it works now:
>> >> >
>> >> > If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or
>> >> > AY>1987)
>> >> > and
>> >> > PenHrs<>0)
>> >> > Begin
>> >> > Update #PY_Tbl Set After1987=1
>> >> > End
>> >> >
>> >> > Restored:
>> >> >
>> >> > Any ideas why?
>> >> >
>> >>
>> >>
>> >>
>>|||>I do not understand what you want me to supply?
Something we could execute that gives us the same error message. I see from your discussion with
Russell that it isn't that easy, though. A parsing bug seems probable, and I guess that if you want
to pursue this you would have to open a case with MS.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Steve Z" <SteveZ@.discussions.microsoft.com> wrote in message
news:FCE7ED20-94C8-4008-8484-AF93D1953941@.microsoft.com...
>I do not understand what you want me to supply? This error is easily
> duplicated on the production server.
> "Tibor Karaszi" wrote:
>> Can you provide a repro? Your code returned an *expected* error (no such table...).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>|||On Tue, 21 Aug 2007 13:46:03 -0700, Steve Z
<SteveZ@.discussions.microsoft.com> wrote:
>I had this line in a very, very large stored procedure
>If Exists(Select * From #PM_Tbl Where ((AY=1986 and AM>=7) or AY>1987) and
>PenHrs<>0) Update #PY_Tbl Set After1987=1
>Restored:
What is the *next* statement?
J.