Post reply

Where to find ignore dup key in system tables

  • December 17, 2011 at 8:08 pm

    #98405

    Hello,

    Does someone know where to find 'ignore_dup_key' parameter for an index in SQL 2000 system tables ?

    For SQL 2005 an above it's in sys.indexes, but I can't found this parameter in SQL 2000 and we can use it.

    Thanks for your help

    Daniel

  • December 18, 2011 at 6:51 am

    #1422896

    Try with using INDEXPROPERTY to get the index options.

    SELECT INDEXPROPERTY(OBJECT_ID('authors'), 'indexname',

    'IsUnique')

    for more details.

    http://msdn.microsoft.com/en-us/library/aa933195(v=sql.80).aspx

    java[/url]

  • December 18, 2011 at 6:54 am

    #1422897

    Yes but ignore dup key is not one of the options for that function.

    I've spent a few minutes looking all over the place and I can't find that info anywhere. The only place I didn't look is in the information_schema views... and maybe sp_help* objects.

  • December 18, 2011 at 7:09 am

    #1422898

    INDEXPROPERTY doesn't return information for ignore_dup_key and I have seen nothing in sp_help documentation.

    But Enterprise Manager returns that information and I don't find how...

    Daniel

  • December 18, 2011 at 7:15 am

    #1422899

    start profiler and open the property window. It must be hidden in a status column or something like that.

  • December 18, 2011 at 7:22 am

    #1422900

    already done... I have seen nothing about that property

  • December 18, 2011 at 7:26 am

    #1422901

    Close EM, start profile, open the GUI to see ignore_dup_key on.

    Save the trace and send it over here (edit attachements).

  • December 18, 2011 at 7:44 am

    #1422902

    The only SQL statement I've seen is :

    EXECUTE sp_MShelpindex N'[dbo].[Client]' , NULL, 1

    I don't know what the column 'status' contains.

    When ignore_dup_key is on 'status' contains 16810243 otherwise '16810242'

    I suppose 'status' is a binary mask, but I have not found documentation on it.

    Daniel

  • December 18, 2011 at 7:51 am

    #1422903

    Go in master and open up that proc.

    Copy the code out to QA and run one statement at a time untill you find the query that does the info. It's going to be binary operations (most likely with &).

    It could look something like this CASE WHEN Status & <number> = <same number> then 1 END AS Ignore_dup_key

  • December 18, 2011 at 8:01 am

    #1422904

    Can't find it either on google.

    This is the only thing I found that may help you out.

    http://www.sqlservercentral.com/Forums/FindPost387744.aspx

    I don't have access to my 2k5 machine so I can't open the trace.

  • December 18, 2011 at 8:16 am

    #1422905

    Ok, status comes from sysindexes.status :

    select i.name, i.status, ...

    from dbo.sysindexes i ...

    and sysindexes.status is documented as 'Internal system-status information'

    So, no information about format.

    Daniel

  • December 18, 2011 at 8:19 am

    #1422906

    Thanks a lot for this link. It answer my question.

    I have seen in the stored proc that status comes from susindexes.status and the code in your link shows how to read it :

    case when (i.status & 1)<>0 then ', '+@IgnoreDuplicateKeys else @empty end

    Thanks 🙂

    Daniel

  • December 18, 2011 at 8:21 am

    #1422907

    Nice, it answers my question :

    I have seen in the stored proc that status comes from sysindexes.status

    and your link shows how to analyse it :

    case when (i.status & 1)<>0 then ', '+@IgnoreDuplicateKeys else @empty end

    Thanks a lot for your help

    Daniel

  • December 18, 2011 at 8:26 am

    #1422908

    Happy to help.

    I'm sure I've seen those maps documented in the past. I just can't find them anymore. Granted it's almost 3 fulls versions back now. So many sites will be dropping that kind of info!

Viewing 14 posts - 1 through 13 (of 13 total)

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