'(-)' in list of index columns which I get after sp_helpindexes

Hi,
Does anybody know what '(-)' means in the list of index
columns when I execute sp_helpindexes for the table.
For example:
exec sp_helpindexes <table name> returns:
column1(-),column2,column3.
I saw this several times, and it gets disapeared when I
rebuild index.
Thanks,
OJDescending.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ|||Hi OJ
It means the index was build with the index keys sorted in descending order.
If you rebuild your indexes, and don't explicitly state you want to build
them in descending order, they will be built in ascending order and the (-)
will go away.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ

"The statement did not return a result set" error on SPs with exec

I'm trying to modify our application to use the SQL Server 2005 JDBC driver. After making the appropriate changes most stuff seems to work OK, except for any stored procedures that contain an exec statement and return a result set. When the stored procedure is called with executeQuery it throws a SQLServerException with the error "The statement did not return a result set". Other query SPs that don't have an exec work just fine. It doesn't matter if the exec is calling another SP, or if it's executing a constructed SQL string. Also, this worked OK with the DataDirect JDBC drivers that we used to use. I'm running this on Windows XP with SQL Server 2000.

Hi Richard,

Could you post a sample stored procedure that demonstrates this behavior and the syntax you are using to call it? It sounds like the driver thinks the stored procedure is returning an update count rather than a result set as the first result.

Thanks,

--David Olix

JDBC Development

|||

This took a while to pinpoint. It turns out that the problem doesn't have anything to do with exec, instead it looks like a problem with UpdateText. I can reproduce the problem with the following sp:

create procedure spTest
@.id_list ntext
as
declare @.txtptr binary(16),
@.list_length integer

Create Table #parse
( IDList ntext )

Insert Into #parse
Select @.id_list

Select @.txtptr = TextPtr(IDList)
from #parse with (nolock)

Select @.list_length = DataLength(@.id_list)
if ( @.list_length > 0 AND Substring(@.id_list,@.list_length,1) <> ',')
Begin
UpdateText #parse.IDList @.txtptr NULL 0 ','
End
select * from #parse -- comment out this line to have no result set

Return 0

GO

and call it with the following:

PreparedStatement stmt = null;
ResultSet rs = null;
try {
Connection con = this.getConnection();
stmt = con.prepareCall("{call spTest(?)}");
stmt.setString(1, "12,15");
rs = stmt.executeQuery();
}
catch( SQLException e )
{
logger.severe(e.getMessage());
e.printStackTrace();
}
finally {
closeVars(null,stmt,rs);
}

If I replace the "UpdateText" with anything else, no exception is thrown.

|||

I'm wondering if UpdateText is returning a value that the driver interprets as an update count. Could you try calling this sp with execute() rather than executeQuery() and let me know if you get an update count followed by the result set that you expect?

Thanks again,

--David Olix

JDBC Development

|||If I call execute followed by getUpdateCount, it returns 1 for the update count. If I call getResultSet after that, it returns null.|||

I forgot to mention it above, but you need to call getMoreResults between getUpdateCount and getResultSet. I think the result set should be there.

Regardless, this gives me enough information to start on a fix to your problem. You may want to file this as a bug through the MSDN Product Feedback Center http://lab.msdn.microsoft.com/productfeedback/default.aspx so that you can track it.

Thanks!

|||

Even after I added a getMoreResults call between getUpdateCount and getResultSet, getResultSet still returned a null ResultSet.

I'll file a bug report. Thanks for your help.

"The statement did not return a result set" error on SPs with exec

I'm trying to modify our application to use the SQL Server 2005 JDBC driver. After making the appropriate changes most stuff seems to work OK, except for any stored procedures that contain an exec statement and return a result set. When the stored procedure is called with executeQuery it throws a SQLServerException with the error "The statement did not return a result set". Other query SPs that don't have an exec work just fine. It doesn't matter if the exec is calling another SP, or if it's executing a constructed SQL string. Also, this worked OK with the DataDirect JDBC drivers that we used to use. I'm running this on Windows XP with SQL Server 2000.

Hi Richard,

Could you post a sample stored procedure that demonstrates this behavior and the syntax you are using to call it? It sounds like the driver thinks the stored procedure is returning an update count rather than a result set as the first result.

Thanks,

--David Olix

JDBC Development

|||

This took a while to pinpoint. It turns out that the problem doesn't have anything to do with exec, instead it looks like a problem with UpdateText. I can reproduce the problem with the following sp:

create procedure spTest
@.id_list ntext
as
declare @.txtptr binary(16),
@.list_length integer

Create Table #parse
( IDList ntext )

Insert Into #parse
Select @.id_list

Select @.txtptr = TextPtr(IDList)
from #parse with (nolock)

Select @.list_length = DataLength(@.id_list)
if ( @.list_length > 0 AND Substring(@.id_list,@.list_length,1) <> ',')
Begin
UpdateText #parse.IDList @.txtptr NULL 0 ','
End
select * from #parse -- comment out this line to have no result set

Return 0

GO

and call it with the following:

PreparedStatement stmt = null;
ResultSet rs = null;
try {
Connection con = this.getConnection();
stmt = con.prepareCall("{call spTest(?)}");
stmt.setString(1, "12,15");
rs = stmt.executeQuery();
}
catch( SQLException e )
{
logger.severe(e.getMessage());
e.printStackTrace();
}
finally {
closeVars(null,stmt,rs);
}

If I replace the "UpdateText" with anything else, no exception is thrown.

|||

I'm wondering if UpdateText is returning a value that the driver interprets as an update count. Could you try calling this sp with execute() rather than executeQuery() and let me know if you get an update count followed by the result set that you expect?

Thanks again,

--David Olix

JDBC Development

|||If I call execute followed by getUpdateCount, it returns 1 for the update count. If I call getResultSet after that, it returns null.|||

I forgot to mention it above, but you need to call getMoreResults between getUpdateCount and getResultSet. I think the result set should be there.

Regardless, this gives me enough information to start on a fix to your problem. You may want to file this as a bug through the MSDN Product Feedback Center http://lab.msdn.microsoft.com/productfeedback/default.aspx so that you can track it.

Thanks!

|||

Even after I added a getMoreResults call between getUpdateCount and getResultSet, getResultSet still returned a null ResultSet.

I'll file a bug report. Thanks for your help.

'sys.sp_dbcmptlevel' can only be executed at the ad hoc level

hi

i want to change the Compatibility level of the database(currently in use),autocommit is ON

as

EXEC sp_dbcmptlevel DM, 90 (OK in SQL-server 2005, no error)

Now when execute this command in my application(build using Centura) throws error as

Microsoft SQL Server:15432[Microsoft][ODBC SQL Server Driver][SQL Server]Stored procedure 'sys.sp_dbcmptlevel' can only be executed at the ad hoc level.

I check the docs, what i find this Error comes only if u try to execute this in other procedure or uncommited Transation.

there is no such thing i already checked that then

What this error mean?

any hint?

Gurpreet S. Gill

I don't know what Centura is or how it works. You are most probably using some construct that results in the use of sp_executesql to run the statement from the client like:

exec sp_executesql N'EXEC sp_dbcmptlevel DM, 90';

This will happen in ADO.NET for example if you use a parameterized statement. You need to use direct execution mode of operation. So check your documentation on how to run SQL statements in direct execution mode. You can also trace the calls from your application to see the command it is sending and that should give you a clue.

|||

I don't think that's it, Umachandar. I think it's just what he said - he wrote a proc to exec sp_dbcmptlevel and it throws the error if it's inside another proc. Run this and you'll see what I mean:

CREATE PROC dbo.testing (@.DbName SYSNAME) AS

EXEC sys.sp_dbcmptlevel @.dbname=@.DbName, @.new_cmptlevel=90

GO

EXEC testing Adventureworks

Msg 15432, Level 16, State 1, Procedure sp_dbcmptlevel, Line 28

Stored procedure 'sys.sp_dbcmptlevel' can only be executed at the ad hoc level.

If you try to execute it using EXEC() or sp_executesql, it still throws the same error:

CREATE PROC dbo.testing (@.DbName SYSNAME) AS

DECLARE @.DynamicSql NVARCHAR(255)

SET @.DynamicSql = 'EXEC sys.sp_dbcmptlevel @.dbname=' + @.DbName + ', @.new_cmptlevel=90'

EXEC (@.DynamicSql)

GO

EXEC testing Adventureworks

|||

The problem is this part of code of sp_dbcmptlevel:

if (@.@.nestlevel > 1)

begin

raiserror(15432,-1,-1,'sys.sp_dbcmptlevel')

return (1)

end

And BOL says: "

When @.@.NESTLEVEL is executed within a Transact-SQL string, the value returned is 1 + the current nesting level. When @.@.NESTLEVEL is executed dynamically by using sp_executesql the value returned is 2 + the current nesting level.

"

How it is possible to bypass this check? Why does Microsoft this check?