February 1, 2008 at 5:46 am
Hello All! Forgive me if this is in the wrong forum but it seemed to fall between a few...
I have a SP on a SQL2005 server which I call from a SQL2000 server. Historically it has worked fine but suddenly I have started getting error messages:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I have fiddled around trying to find the simplest query that breaks, but I still can't see why it breaks. Or why it should suddenly have started behaving differently. :w00t:
Here is some sample data:
CREATE TABLE testData (test INT PRIMARY KEY)
INSERT testData VALUES (1)
INSERT testData VALUES (2)
and here are two very similar stored procedures:
CREATE PROCEDURE [dbo].[test_1] AS
CREATE TABLE #results (
col1 INT PRIMARY KEY,
col2 INT NOT NULL )
INSERT #results
SELECT i.test, MAX(j.test)
FROM testData i
CROSS JOIN testData j
WHERE i.test=1
GROUP BY i.test
SELECT * FROM #results
CREATE PROCEDURE [dbo].[test_2] AS
CREATE TABLE #results (
col1 INT PRIMARY KEY,
col2 INT NOT NULL )
INSERT #results
SELECT i.test, MAX(CASE j.test WHEN 2 THEN 2 END)
FROM testData i
CROSS JOIN testData j
WHERE i.test=1
GROUP BY i.test
SELECT * FROM #results
I try running each from both servers. test_1 runs perfectly from both servers and returns exactly the same results - as I would expect. test_2 however runs perfectly on the SQL2005 server but returns the above error from the SQL2000 server.
February 1, 2008 at 6:15 am
I've tested your scripts at SQL2005 DE sp2 CU5
and SQL2000 PE SP4
with no problem at all.
these are results:
SQL2000 PE sp4
(1 row(s) affected)
col1 col2
----------- -----------
1 2
(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
col1 col2
----------- -----------
1 2
(1 row(s) affected)
SQL2005 DE sp4 CU5
(1 row(s) affected)
col1 col2
----------- -----------
1 2
(1 row(s) affected)
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
col1 col2
----------- -----------
1 2
(1 row(s) affected)
Are there other stability issues with your sql2000 ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 1, 2008 at 6:22 am
Thanks for checking that ALZDBA.
I've checked again here and, if I run both from Query Analyser 2000 from a colleague's computer, connected to the SQL2000 server, they also both work fine. BUT when I do it from SSMS2005 on my machine or someone else's, one works and the other breaks. I'm still baffled as to why they behave differently, but at least it now means I can write the queries, I just can't test them...
Oh, and the SQL2000 server is being upgraded next week so the problem will hopefully disappear completely. But still, its weird...
February 1, 2008 at 6:31 am
The tests I performed were using SSMS2005.
At the client I also did install sp2 + CU5 !
(client installs are forgotten for updates way to much)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 1, 2008 at 7:37 am
This is probably a long shot Paul, but what is the maximum number of options you have in a single CASE construct? There's a limit, when running a query against a linked 2K server, of I think 10 options.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 1, 2008 at 7:53 am
In this example, there's just the 1 entry in the CASE statement, but I'll check my other queries in case there are more. The cross-server queries that recently broke all involve CASEs, but I'm increasingly thinking there's something buggy on the server.
February 1, 2008 at 7:59 am
It's perhaps worth a look. The error message states something about nested cases, but case nesting isn't necessary for the error - just more than 10 options in one case construct. I've had this error during data migration with SS2K, don't know if it's still an issue with SS2K5.
Good luck.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply