SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Create Index Syntax
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TQMF_SQL
Starting Member

USA
2 Posts

Posted - 10/30/2013 :  15:04:40  Show Profile  Reply with Quote
I'm embarrassed to post question regarding a syntax error but I cannot figure out what's wrong with the following code snippet...

CREATE UNIQUE INDEX [IX_TESTINDEX] on dbo.TS_RAY_BOGUS (TYPE, BASE_ID, LOT_ID, SPLIT_ID, SUB_ID, SEQUENCE_NO)
WITH (IGNORE_DUP_KEY = ON); --<< This is the line generating syntax error, why?
GO

Returns the following warning message:
Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '('.


I need to create the index in a manner that when data is mass-loaded with the known data duplicates, the process will not halt, but rather complete loading the data and give a count of records not added due to duplicate keys.

I appreciate the time you are taking to help me figure out this problem. Best regards.

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 10/30/2013 :  15:12:59  Show Profile  Reply with Quote
I don't get any errors when running that statement. Is the server 2008 and database in the 100 compatibility mode?

or perhaps the error is for a different line?

EDIT:
this is what I ran:

go
create table #butt (type int, base_id int, lot_id int, split_id int, sub_id int, sequence_no int)
go
CREATE UNIQUE INDEX [IX_TESTINDEX] on #butt (TYPE, BASE_ID, LOT_ID, SPLIT_ID, SUB_ID, SEQUENCE_NO)
WITH (IGNORE_DUP_KEY = ON);
GO 
drop table #butt
go


Be One with the Optimizer
TG

Edited by - TG on 10/30/2013 15:14:22
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 10/30/2013 :  15:14:35  Show Profile  Reply with Quote
The syntax seems fine to me - don't know why it is generating a syntax error for you. I tried to copy it and parse it - it does parse correctly.

One thing you could do though, is to use the user interface to set up the index - i.e., you would right-click on indexes node in the SSMS object explorer, select new index, and set up everything exactly as you want it. Then instead of clicking OK, click the script button at the top left of the right panel. That will give you the script that would have run had you clicked ok. Compare that with your script.
Go to Top of Page

TQMF_SQL
Starting Member

USA
2 Posts

Posted - 10/30/2013 :  15:32:56  Show Profile  Reply with Quote
Thanks to both TG and James K

Although the server itself is SQL Server 2008, the database I was working with is at the SQL Server 2000(80) level and I'll bet that's why I'm getting the syntax error, it is most likely validating against a SQL Server 2000(80) standard. Based upon your replies, I now have a couple of options of how to proceed, thanks. I wouldn't have thought to check the database level on my own.

I consider the issue now resolved. Thanks again for your time and expertise!!
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6059 Posts

Posted - 10/30/2013 :  15:56:43  Show Profile  Reply with Quote
you're welcome.

From Books Online:
"In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON."

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000