Professional Web Applications Themes

SET IDENTITY_INSERT from OLEDB code - Microsoft SQL / MS SQL Server

I use OLEDB to insert data into a table, but I get an DBSTATUS_E_PERMISSIONDENIED when inserting data into an identity column. I guess this happens because the IDENTITY_INSERT is disabled (by default) for the table. Not all of my tables use identity columns, so I want to do something like this: Recordset = OpenRecordsetFromTable(TableName) set IDENTITY_INSERT TableName ON insert records into Recordset set IDENTITY_INSERT TableName OFF There are some problems with this approach. Setting IDENTITY_INSERT for each table is not a very good idea, because SQL Server generates an error when setting this option without an identity column. Because I ...

  1. #1

    Default SET IDENTITY_INSERT from OLEDB code

    I use OLEDB to insert data into a table, but I get an
    DBSTATUS_E_PERMISSIONDENIED when inserting data into an identity column. I
    guess this happens because the IDENTITY_INSERT is disabled (by default) for
    the table. Not all of my tables use identity columns, so I want to do
    something like this:

    Recordset = OpenRecordsetFromTable(TableName)
    set IDENTITY_INSERT TableName ON
    insert records into Recordset
    set IDENTITY_INSERT TableName OFF

    There are some problems with this approach. Setting IDENTITY_INSERT for each
    table is not a very good idea, because SQL Server generates an error when
    setting this option without an identity column. Because I cannot find out if
    a table uses an identity column in OLEDB the error is ignored by my code.
    The second problem is worse. Despite of setting the IDENTITY_INSERT option I
    still get DBSTATUS_E_PERMISSIONDENIED when inserting data in the identity
    column.

    Please help...


    Ramon de Klein Guest

  2. #2

    Default Re: SET IDENTITY_INSERT from OLEDB code

    Hello,
    > You forgot to tell us what version of SQL Server you're using, but can't
    you
    > access the INFORMATION_SCHEMA views and COLUMNPROPERTY()? See
    > [url]www.aspfaq.com/2177[/url]
    This is possible, but I would rather like to call one of the OLEDB
    interfaces to determine this. Now I need to create a SQL statement and this
    much less efficient. But I could use this method if nothing else helps, so
    thanks anyway :-)

    Greetings,
    Ramon de Klein


    Ramon de Klein Guest

  3. #3

    Default Re: SET IDENTITY_INSERT from OLEDB code

    You can check the Attributes property of the Field object to determine if
    the field is a key field (Identity) or not. The constant to check for is
    adFldRowID

    HTH,
    Bob Barrows

    Ramon de Klein wrote:
    > I use OLEDB to insert data into a table, but I get an
    > DBSTATUS_E_PERMISSIONDENIED when inserting data into an identity
    > column. I guess this happens because the IDENTITY_INSERT is disabled
    > (by default) for the table. Not all of my tables use identity
    > columns, so I want to do something like this:
    >
    > Recordset = OpenRecordsetFromTable(TableName)
    > set IDENTITY_INSERT TableName ON
    > insert records into Recordset
    > set IDENTITY_INSERT TableName OFF
    >
    > There are some problems with this approach. Setting IDENTITY_INSERT
    > for each table is not a very good idea, because SQL Server generates
    > an error when setting this option without an identity column. Because
    > I cannot find out if a table uses an identity column in OLEDB the
    > error is ignored by my code. The second problem is worse. Despite of
    > setting the IDENTITY_INSERT option I still get
    > DBSTATUS_E_PERMISSIONDENIED when inserting data in the identity
    > column.
    >
    > Please help...

    Bob Barrows Guest

Similar Threads

  1. Question OLEDB Exception
    By Unregistered in forum Brainstorming Area
    Replies: 0
    Last Post: August 23rd, 05:28 AM
  2. OleDb and .Net
    By jerry@fcds in forum Dreamweaver AppDev
    Replies: 2
    Last Post: April 29th, 06:06 PM
  3. OleDb May Cause A Problem?
    By Arif Çimen in forum ASP.NET Web Services
    Replies: 0
    Last Post: April 19th, 02:44 PM
  4. Problems with IBM SDK OLEDB
    By Roland Biemans in forum IBM DB2
    Replies: 0
    Last Post: September 22nd, 07:49 PM
  5. SQL Server IDENTITY_INSERT with CTable<CDynamicAccessor, CRowset>?
    By Dave in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 8th, 10:32 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139