Index Creation Errors

Question

Post reply

Index Creation Errors

Robert A. Shannon III

SSC Veteran

Points: 254
More actions
June 3, 2004 at 4:24 pm

#163676

Hello Everyone!

Using dynamic SQL in a SP to create indexes for a given table yields the following error:

Server: Msg 3624, Level 20, State 1, Line 1

Location: recbase.cpp:1374

Expression: m_nVars > 0

SPID: 63

Process ID: 1656

Connection Broken

This error never occurs while building the same index, but we can never build all of the indexes on this table before the error occurs.

Any ideas what's up? The CREATE INDEX statement is straight forward, no syntax errors. The only thing different about them is that they all use the SORT_IN_TEMPDB option. Enough space is present in tempdb to hold the intermediate sort runs, and enough free space in the destination filegroup to hold the new index.

Thanks!

TTUEagle

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ned Ten Centuries Points: 1214 More actions · Answer 1

Ned

Ten Centuries

Points: 1214

June 3, 2004 at 4:38 pm

#508910

Can you post your code?

Robert A. Shannon III SSC Veteran Points: 254 More actions · Answer 2

Here is the last line that caused that error (I had to remove specific names, but I assure you I can take the code and paste it into an isql session and all is well):

USE <db_name> CREATE UNIQUE NONCLUSTERED INDEX <index_name> ON <table_name>(<col_1,col_2) WITH SORT_IN_TEMPDB ON <file_group>

This string executed using the EXEC() method and results in the previously specified error. col_2 has a clustered index built on it that builds fine everytime. There are about 32 million rows of data in this table. We are running WIndows 2003 Server, SQL Server 2000.

Thanks!

TTUEagle