Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    8

    Unanswered: Append query from Access Table to Linked SQL Server Table Failing

    Strange one here - I am posting this in both SQL Server and Access forums

    Access is telling me it can't append any of the records due to a key violation.

    The query:

    INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
    SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
    FROM Colors_Access;

    Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.

    There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.

    I can manually append the records using cut and paste with no problems.

    I have tried re-linking the tables.

    Any ideas?
    Thanks,
    Brad

  2. #2
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    I'd guess that the SQL Server db you think you are linking to in dbo_Colors isn't really the one you think. Perhaps the login/password in your datasource is connecting to a different database than the one you expect?

    To check, get the name of the constraint being violated and check in the sql server table to see if that constraint exists

    Also, try running the sql server profiler to see what sql server db ms access is trying to insert data into

  3. #3
    Join Date
    Jun 2004
    Posts
    8
    mattrevs,

    It does appear that I am linking the right table. There is only one Colors table and only one database that has a colors table.

    Could you tell me how to check which constraint is being violated? After I manually pasted the data into dbo_Colors, I ran checkconstraints() and no error were reported.

    I also tried running the query with implicit_transaction OFF and still no joy.

    I ran the trace and here are the last two lines. The first one was simply duplicated for each record:

    RPC:Completed exec sp_executesql N'INSERT INTO "dbo"."Colors" ("NameColorID","Application","Red","Green","Blu e") VALUES (@P1,@P2,@P3,@P4,@P5)', N'@P1 nvarchar(38),@P2 nvarchar(30),@P3 int,@P4 int,@P5 int', N'{FFC28EAD-1134-40BB-9723-7D88A0B0AC7A}', N'Tile1', 197, 183, 156 Microsoft® Access sa 0 11 0 0 2564 54 2004-06-21 10:35:14.170

    SQL:BatchCompleted IF @@TRANCOUNT > 0 ROLLBACK TRAN Microsoft® Access sa 0 0 0 0 2564 54 2004-06-21 10:35:19.403

    - Brad

  4. #4
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    You say that pasting the data one row at a time from within ms access works ok?

    If so, maybe you could also perform a sql trace on this and see what is different?

  5. #5
    Join Date
    Jun 2004
    Posts
    8
    I got it figured out. (BTW, I did the trace with the pasting but the results were ... confusing)

    Apparently a bit field in SQL server can be Null???

    There are two additional fields in the SQL version of the table. The NVarChar field I had set to allow nulls. I never bothered with the Bit field.

    When I checked Allow Nulls on the bit field and re-linked, my query worked.

    I guess my lesson her is never assume anything. I have learned that lesson many times and will probably learn it again in the (near) future.

    Thanks for your help,
    Brad

Posting Permissions

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