CREATE NONCLUSTERED INDEX - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

CREATE NONCLUSTERED INDEX

Author

Topic

rpc86
Posting Yak Master

200 Posts

Posted - 2007-07-06 : 03:09:07

Hi guys. I have a table named [Check] and need to create an index for CVNumber field. The table has no primary key for the meantime. I tried this script but error occured.


BEGIN TRANSACTION
	SET QUOTED_IDENTIFIER ON
	GO
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	GO
	COMMIT
	BEGIN TRANSACTION
		CREATE NONCLUSTERED INDEX IX_Check_1 ON dbo.[Check]
		(
		CVNumber 
		) ON [PRIMARY]
	GO
COMMIT

Error message:

Server: Msg 3023, Level 16, State 2, Line 3
Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed.
Server: Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Is it because I used the table name Check which is a reserved word for SQL? But I included [ ].

Please help. Thank you.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-06 : 03:17:21

What is the need of BEGIN TRAN...COMMIT TRAN? Also why you are setting Transaction isolation level to SERIALIZABLE?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rpc86
Posting Yak Master

200 Posts

Posted - 2007-07-06 : 03:19:59

I just copied the syntax from the book I bought. Kindly correct my syntax.

thank you

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-06 : 03:22:25

Does this give you error?

CREATE NONCLUSTERED INDEX IX_Check_1 ON dbo.[Check]
		(
		CVNumber 
		) ON [PRIMARY]
	GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rpc86
Posting Yak Master

200 Posts

Posted - 2007-07-06 : 03:25:57

yes, the same error.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-06 : 03:33:05

Are any of those operations (Backup database, Checkalloc, Bulk copy, SELECT Into) going on in the background while you create index?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rpc86
Posting Yak Master

200 Posts

Posted - 2007-07-06 : 03:38:31

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-07-06 : 10:48:49

Double check processes with 'sp_who2 active'.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources