Index Creation Errors

Question

Post reply

Index Creation Errors

Robert A. Shannon III

SSC Veteran

Points: 284
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: 284 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

Sean Lambert SSC Eights! Points: 845 More actions · Answer 3

While this is not a solution, it may shed some light into the issue and lead you down a path of solving it.

http://support.microsoft.com/default.aspx?scid=kb;en-us;330307&Product=sql2k

Bert De Haes Hall of Fame Points: 3093 More actions · Answer 4

I found the same type of problem on this site http://www.sqlservercentral.com/forums/shwmessage.aspx?messageid=113712 and there is a link to a fix ...