Professional Web Applications Themes

SQL Server IDENTITY_INSERT with CTable<CDynamicAccessor, CRowset>? - Microsoft SQL / MS SQL Server

Is it possible to use an ATL CTable to insert rows into a SQL Server table with an identity column? I can get there with a CCommand, but I would like to use CTable. This works: CCommand<CNoAccessor, CNoRowset> cmd; hr = cmd.Open(session, "SET IDENTITY_INSERT theTable ON"); cmd.Close(); hr = cmd.Open(session, "INSERT ...") cmd.Close(); This does not work: CCommand<CNoAccessor, CNoRowset> cmd; hr = cmd.Open(session, "SET IDENTITY_INSERT theTable ON"); cmd.Close(); CTable<CDynamicAccessor, CRowset> table; CDBPropSet propset(PROPSET_ROWSET); propset.AddProperty(DBPROP_IRowsetChange, true); propset.AddProperty(DBPROP_UPDATABILITY, DBPROP_UP_INSERT); hr = table.Open(session, "theTable", &propset); ... set values... hr = table.Insert(); // fails 0x80040e21, identity column dbstatus is DBSTATUS_E_PERMISSIONDEINED. The second sample will ...

  1. #1

    Default SQL Server IDENTITY_INSERT with CTable<CDynamicAccessor, CRowset>?

    Is it possible to use an ATL CTable to insert rows into a SQL Server table
    with an identity column? I can get there with a CCommand, but I would like
    to use CTable.

    This works:
    CCommand<CNoAccessor, CNoRowset> cmd;
    hr = cmd.Open(session, "SET IDENTITY_INSERT theTable ON");
    cmd.Close();
    hr = cmd.Open(session, "INSERT ...")
    cmd.Close();

    This does not work:
    CCommand<CNoAccessor, CNoRowset> cmd;
    hr = cmd.Open(session, "SET IDENTITY_INSERT theTable ON");
    cmd.Close();

    CTable<CDynamicAccessor, CRowset> table;
    CDBPropSet propset(PROPSET_ROWSET);
    propset.AddProperty(DBPROP_IRowsetChange, true);
    propset.AddProperty(DBPROP_UPDATABILITY, DBPROP_UP_INSERT);
    hr = table.Open(session, "theTable", &propset);
    ... set values...
    hr = table.Insert(); // fails 0x80040e21, identity column dbstatus is
    DBSTATUS_E_PERMISSIONDEINED.

    The second sample will work if I remove the identity property from the
    column in theTable.

    Thanks,
    Dave



    Dave Guest

  2. #2

    Default Re: SQL Server IDENTITY_INSERT with CTable<CDynamicAccessor, CRowset>?

    Dave (davemic.cm) writes:
    > Is it possible to use an ATL CTable to insert rows into a SQL Server
    > table with an identity column? I can get there with a CCommand, but I
    > would like to use CTable.
    >
    > This works:
    > CCommand<CNoAccessor, CNoRowset> cmd;
    > hr = cmd.Open(session, "SET IDENTITY_INSERT theTable ON");
    > cmd.Close();
    > hr = cmd.Open(session, "INSERT ...")
    > cmd.Close();
    >
    > This does not work:
    > CCommand<CNoAccessor, CNoRowset> cmd;
    > hr = cmd.Open(session, "SET IDENTITY_INSERT theTable ON");
    > cmd.Close();
    >
    > CTable<CDynamicAccessor, CRowset> table;
    > CDBPropSet propset(PROPSET_ROWSET);
    > propset.AddProperty(DBPROP_IRowsetChange, true);
    > propset.AddProperty(DBPROP_UPDATABILITY, DBPROP_UP_INSERT);
    > hr = table.Open(session, "theTable", &propset);
    > ... set values...
    > hr = table.Insert(); // fails 0x80040e21, identity column dbstatus is
    > DBSTATUS_E_PERMISSIONDEINED.
    I'm fairly out in the dark here since, this is not the sort of programming
    I do.

    But it seems that the cmd.Open and table.Open are not using the same
    connection to SQL Server, despite the session they have in common. Did
    you try to run Profiler, to see what happenss behind the scenes?

    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

  3. #3

    Default Re: SQL Server IDENTITY_INSERT with CTable<CDynamicAccessor, CRowset>?

    I agree, even though the doentation I've read says that the
    IDENTITY_INSERT property is associated with the session, and not something
    else.

    FYI, Visual Studio .NET (the version I am using) does not have a profiling
    tool. It was dropped after VS 6.0.

    Dave

    "Erland Sommarskog" <sommaralgonet.se> wrote in message
    news:Xns93B29D79E291Yazorman127.0.0.1...
    >
    > I'm fairly out in the dark here since, this is not the sort of programming
    > I do.
    >
    > But it seems that the cmd.Open and table.Open are not using the same
    > connection to SQL Server, despite the session they have in common. Did
    > you try to run Profiler, to see what happenss behind the scenes?
    >
    > --
    > Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]
    >
    > Books Online for SQL Server SP3 at
    > [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]

    Dave Guest

  4. #4

    Default Re: SQL Server IDENTITY_INSERT with CTable<CDynamicAccessor, CRowset>?

    Dave (davemic.cm) writes:
    > I agree, even though the doentation I've read says that the
    > IDENTITY_INSERT property is associated with the session, and not something
    > else.
    >
    > FYI, Visual Studio .NET (the version I am using) does not have a profiling
    > tool. It was dropped after VS 6.0.
    But SQL Server has a Profiler, and this is the Profiler I'm talking about!
    This is the one that permits you eavesdrop on what is being sent to SQL
    Server. (You need to have admin access to SQL Server, though.)

    You find it in the SQL Server program group.


    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

Similar Threads

  1. Replies: 0
    Last Post: November 27th, 06:14 PM
  2. Replies: 0
    Last Post: August 15th, 09:24 AM
  3. Replies: 2
    Last Post: September 12th, 10:44 PM
  4. Replies: 0
    Last Post: August 23rd, 05:27 PM
  5. SET IDENTITY_INSERT from OLEDB code
    By Ramon de Klein in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 9th, 01:44 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