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.

ODBC SQL Server Driver connection issues

Issue:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (rec
v()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
above error is recieved during a heavy SQL query from Query Analyzer & a
OLEDB application client. Other queries like sp_who2 operate fine.
Environment:
SQL Servers running both SQL 2000 & 2005 (currently disabled) patched and
hotfix'd to most recent. Windows 2003 Servers patched and hotfix'd also. MDA
C
2.8.
Interestingly, when the query is run with SQL Server Management Studio it
runs just fine. Is there a compatiblity issue running both of these SQL
server versions on the same machine? When I run the query from a different
machine without the server software, just the two client versions I don't
experience this network connectivity issue.
ThanksOn May 24, 9:33 pm, Outlook 2003 user
<Outlook2003u...@.discussions.microsoft.com> wrote:
> Issue:
> [Microsoft][ODBCSQL Server Driver][DBNETLIB]ConnectionRead (re
cv()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> above error is recieved during a heavy SQL query from Query Analyzer & aOL
EDBapplication client. Other queries like sp_who2 operate fine.
> Environment:
> SQL Servers running both SQL 2000 & 2005 (currently disabled) patched and
> hotfix'd to most recent. Windows 2003 Servers patched and hotfix'd also. M
DAC
> 2.8.
> Interestingly, when the query is run with SQL Server Management Studio it
> runs just fine. Is there a compatiblity issue running both of these SQL
> server versions on the same machine? When I run the query from a different
> machine without the server software, just the two client versions I don't
> experience this network connectivity issue.
> Thanks
Check for registry setting SynAttack in KB|||Thanks, but I added that a while ago with no success.
"M A Srinivas" wrote:

> On May 24, 9:33 pm, Outlook 2003 user
> <Outlook2003u...@.discussions.microsoft.com> wrote:
> Check for registry setting SynAttack in KB
>|||Just a follow-up to my post.
I figured out that the TOE was not operating correctly. This situation was
happening on new DELL 2950 & 1950 Servers with TCP/IP Offloading Engine
enables. After completely disabling this feature, via Microsoft & DELL
hardware the erroneous ODBC error stop occurring.
Thanks,
Outlook 2003 User (lol)
"Outlook 2003 user" wrote:
[vbcol=seagreen]
> Thanks, but I added that a while ago with no success.
> "M A Srinivas" wrote:
>

ODBC SQL Server Driver connection issues

Issue:
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken
above error is recieved during a heavy SQL query from Query Analyzer & a
OLEDB application client. Other queries like sp_who2 operate fine.
Environment:
SQL Servers running both SQL 2000 & 2005 (currently disabled) patched and
hotfix'd to most recent. Windows 2003 Servers patched and hotfix'd also. MDAC
2.8.
Interestingly, when the query is run with SQL Server Management Studio it
runs just fine. Is there a compatiblity issue running both of these SQL
server versions on the same machine? When I run the query from a different
machine without the server software, just the two client versions I don't
experience this network connectivity issue.
Thanks
On May 24, 9:33 pm, Outlook 2003 user
<Outlook2003u...@.discussions.microsoft.com> wrote:
> Issue:
> [Microsoft][ODBCSQL Server Driver][DBNETLIB]ConnectionRead (recv()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> Connection Broken
> above error is recieved during a heavy SQL query from Query Analyzer & aOLEDBapplication client. Other queries like sp_who2 operate fine.
> Environment:
> SQL Servers running both SQL 2000 & 2005 (currently disabled) patched and
> hotfix'd to most recent. Windows 2003 Servers patched and hotfix'd also. MDAC
> 2.8.
> Interestingly, when the query is run with SQL Server Management Studio it
> runs just fine. Is there a compatiblity issue running both of these SQL
> server versions on the same machine? When I run the query from a different
> machine without the server software, just the two client versions I don't
> experience this network connectivity issue.
> Thanks
Check for registry setting SynAttack in KB
|||Thanks, but I added that a while ago with no success.
"M A Srinivas" wrote:

> On May 24, 9:33 pm, Outlook 2003 user
> <Outlook2003u...@.discussions.microsoft.com> wrote:
> Check for registry setting SynAttack in KB
>
|||Just a follow-up to my post.
I figured out that the TOE was not operating correctly. This situation was
happening on new DELL 2950 & 1950 Servers with TCP/IP Offloading Engine
enables. After completely disabling this feature, via Microsoft & DELL
hardware the erroneous ODBC error stop occurring.
Thanks,
Outlook 2003 User (lol)
"Outlook 2003 user" wrote:
[vbcol=seagreen]
> Thanks, but I added that a while ago with no success.
> "M A Srinivas" wrote: