July 19, 2006 at 2:30 am
Hi,
I am using SQL Server 2005, Enterprise Edition.
I have a Stored Procedure which returns two SQL query (Dataset). Both of the SQL queries has COMPUTE clause. The 1st Query COMPUTE Three columns and 2nd Query COMPUTE One column. Below are the queries.
-- 1st SQL Query
SELECT Column1, Column2, Column3, Column4, Column5
FROM
(SELECT temptable.Column1, temptable.Column2,
Count(Distinct temptable.StudentID) 'Column3',
Count(Distinct CASE temptable.column_in1 WHEN 1 THEN [StudentID] ELSE NULL END) AS 'Column4',
Count(Distinct CASE temptable.column_in1 WHEN 2 THEN [StudentID] ELSE NULL END) AS 'Column5'
FROM
(SELECT ID 'Column1',
description 'Column2',
drgwpn.column_in1,
StudentID
FROM
table1 dsab Left Join
#temp_table drgwpn
On dsab.ID = drgwpn.Column1
And drgwpn.column_in1 in (1 ,2)
And drgwpn.column_in2 = 3) temptable
GROUP BY temptable.Column1, temptable.Column2) tempout
ORDER BY Column1
COMPUTE Sum(Column3), Sum(Column4) , Sum(Column5)
-- 2nd SQL Query
SELECT Column1, Column2, Column3
FROM
(SELECT temptable.Column1, temptable.Column2,
Count(Distinct temptable.StudentID) 'Column3'
FROM
(SELECT ID 'Column1',
description 'Column2',
drgwpn.Column_in1,
StudentID
FROM
tabel1 dsab Left Join
#table_temp drgwpn
On dsab.ID = drgwpn.Column1
And drgwpn.Column_in1 = 3
And drgwpn.Column_in2 = 3) temptable
GROUP BY temptable.Column1, temptable.Column2) tempout
ORDER BY Column1
COMPUTE Sum(Column3)
When I run the Stored Procedure from Query Analyser it gives me error:
An error occurred while executing batch. Error message is: Offset and length were out of bounds for the array or count is greater than the number of elements from index to the end of the source collection.
But when I COMMENT either of the Query then both of the query Individually works fine.
Please help
July 24, 2006 at 8:00 am
This was removed by the editor as SPAM
July 25, 2006 at 10:42 am
Without having tested your queries, you might surround each query in your Stored Proc with a BEGIN...END It may just need some identifiable separation in order to run them both.
Just my $0.02 worth.
Rich
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply