Error:
SQLException caught: [Microsoft][SQLServer 2000 Driver for JDBC]Column index
7 is out of range.
SQLException caught:.
-----
The procedure follows (slightly modified for security reasons):
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.xprHSPMilestoneData (@.templateid int, @.groupid int) AS
DECLARE @.DataSQL varchar(6000)
DECLARE @.HeadCount int
DECLARE @.CurrentColumn datetime
DECLARE @.ColorColumn varchar(11)
BEGIN
DECLARE Head_cursor CURSOR FOR
SELECT milestone
FROM dbo.vwTrackingGroupDurMS
WHERE templateid = @.templateid AND groupid = @.groupid
OPEN Head_cursor
SET @.DataSQL = 'SELECT ''DSN Workflow Milestones'' AS MSLabel,
NULL'
SET @.HeadCount = 1
FETCH NEXT FROM Head_cursor INTO @.CurrentColumn
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.DataSQL = @.DataSQL + ',''' +
convert(char(10),@.CurrentColumn,101) + ''' as Col' + CONVERT(varchar(2),
@.HeadCount) + 'MS'
SET @.HeadCount = @.HeadCount + 1
FETCH NEXT FROM Head_cursor INTO @.CurrentColumn
END
CLOSE Head_cursor
DEALLOCATE Head_cursor
--Loop thru the colors for each milestone
DECLARE Head_cursor CURSOR FOR
SELECT color
FROM dbo.vwTrackingGroupDurMX
WHERE templateid = @.templateid AND groupid = @.groupid
OPEN Head_cursor
SET @.HeadCount = 1
FETCH NEXT FROM Head_cursor INTO @.ColorColumn
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.DataSQL = @.DataSQL + ',''' + @.ColorColumn +
''' as Col' + CONVERT(varchar(2), @.HeadCount) + 'Color'
SET @.HeadCount = @.HeadCount + 1
FETCH NEXT FROM Head_cursor INTO @.ColorColumn
END
CLOSE Head_cursor
DEALLOCATE Head_cursor
EXECUTE (@.DataSQL)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Microsoft Support Information for this error:
-----
FIX: "Column index <index> out of range" error when you try to read a field
from a JDBC ResultSet object
View products that this article applies to.
Article ID : 838610
Last Review : March 29, 2005
Revision : 2.0
SYMPTOMS
You have a Java application that uses the Java Database Connectivity (JDBC)
API to retrieve a ResultSet
object by using the Microsoft SQL Server 2000 Driver for JDBC. When you try
to read a field from the
ResultSet object by using the methods in the JDBC API, you may receive an
error message
that is similar to the following:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Column
index <index> is out of range.
Note <index> is a placeholder for the index of the column that is being
retrieved.
This problem occurs when all the following conditions are true:
? The Select Transact-SQL statement that is used to retrieve the
data from the SQL Server tables to the
ResultSet object contains a JOIN between different tables in the SQL
Server database.
? The Select Transact-SQL statement uses asterisk (*) to retrieve
all fields from the respective tables.
RESOLUTION
A supported hotfix is now available from Microsoft, but it is only intended
to correct the problem that is described in
this article. Only apply it to systems that are experiencing this specific
problem. This hotfix may receive
additional testing. Therefore, if you are not severely affected by this
problem, Microsoft recommends
that you wait for the next service pack that contains this hotfix.
How do I obtain this hotfix?
Michael Benedict wrote:
> Error:
>
> SQLException caught: [Microsoft][SQLServer 2000 Driver for JDBC]Column index
> 7 is out of range.
I looked at your procedure. Are you absolutely sure
your constructed SQL string got to selecting 7 fields?
I would have the procedure return the SQL string, just for
debugging, and/or do the typical resul set metadata processing
to see how many columns were really returned. The bug you cite
seems to be about *any* row index, under conditions your
procedure doesn't have...
Joe Weinstein at BEA
> SQLException caught:.
> -----
> The procedure follows (slightly modified for security reasons):
>
> ----
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> CREATE PROCEDURE dbo.xprHSPMilestoneData (@.templateid int, @.groupid int) AS
>
> DECLARE @.DataSQL varchar(6000)
> DECLARE @.HeadCount int
> DECLARE @.CurrentColumn datetime
> DECLARE @.ColorColumn varchar(11)
>
> BEGIN
> DECLARE Head_cursor CURSOR FOR
> SELECT milestone
> FROM dbo.vwTrackingGroupDurMS
> WHERE templateid = @.templateid AND groupid = @.groupid
>
> OPEN Head_cursor
> SET @.DataSQL = 'SELECT ''DSN Workflow Milestones'' AS MSLabel,
> NULL'
> SET @.HeadCount = 1
> FETCH NEXT FROM Head_cursor INTO @.CurrentColumn
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.DataSQL = @.DataSQL + ',''' +
> convert(char(10),@.CurrentColumn,101) + ''' as Col' + CONVERT(varchar(2),
> @.HeadCount) + 'MS'
> SET @.HeadCount = @.HeadCount + 1
> FETCH NEXT FROM Head_cursor INTO @.CurrentColumn
> END
> CLOSE Head_cursor
> DEALLOCATE Head_cursor
>
> --Loop thru the colors for each milestone
> DECLARE Head_cursor CURSOR FOR
> SELECT color
> FROM dbo.vwTrackingGroupDurMX
> WHERE templateid = @.templateid AND groupid = @.groupid
>
> OPEN Head_cursor
>
> SET @.HeadCount = 1
> FETCH NEXT FROM Head_cursor INTO @.ColorColumn
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.DataSQL = @.DataSQL + ',''' + @.ColorColumn +
> ''' as Col' + CONVERT(varchar(2), @.HeadCount) + 'Color'
> SET @.HeadCount = @.HeadCount + 1
> FETCH NEXT FROM Head_cursor INTO @.ColorColumn
> END
>
> CLOSE Head_cursor
> DEALLOCATE Head_cursor
>
> EXECUTE (@.DataSQL)
> END
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> ----
> Microsoft Support Information for this error:
> -----
>
> FIX: "Column index <index> out of range" error when you try to read a field
> from a JDBC ResultSet object
> View products that this article applies to.
>
> Article ID : 838610
> Last Review : March 29, 2005
> Revision : 2.0
> SYMPTOMS
>
> You have a Java application that uses the Java Database Connectivity (JDBC)
> API to retrieve a ResultSet
> object by using the Microsoft SQL Server 2000 Driver for JDBC. When you try
> to read a field from the
> ResultSet object by using the methods in the JDBC API, you may receive an
> error message
> that is similar to the following:
>
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Column
> index <index> is out of range.
> Note <index> is a placeholder for the index of the column that is being
> retrieved.
>
> This problem occurs when all the following conditions are true:
>
> ? The Select Transact-SQL statement that is used to retrieve the
> data from the SQL Server tables to the
> ResultSet object contains a JOIN between different tables in the SQL
> Server database.
> ? The Select Transact-SQL statement uses asterisk (*) to retrieve
> all fields from the respective tables.
> RESOLUTION
>
> A supported hotfix is now available from Microsoft, but it is only intended
> to correct the problem that is described in
> this article. Only apply it to systems that are experiencing this specific
> problem. This hotfix may receive
> additional testing. Therefore, if you are not severely affected by this
> problem, Microsoft recommends
> that you wait for the next service pack that contains this hotfix.
> How do I obtain this hotfix?
