Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Answered: Autoamtically duplicating table data in 2 databases - Newbie but not Stupid

    Please assist - I am very well versed on manipulating data using passthrough queries through access or the command object in ADO/VBA but this problem requires a sql server side solution and I have been assigned the task

    Scenario

    Access via an ODBC link cannot view Sql table data that uses a BigInt as a primary key. Access can update the data, insert data ands delete data to these tables – but viewing (by design) returns the actual count of the rows but each field is populated with #deleted.

    I can view the table data if I use a passtyhrough query but performing row level and field level edits from there is impossible. Not all people have Studio manager so that is not an option.

    We are moving away from our legacy application and the development company has already developed the schema using the BigInt so changing that to a numeric or integer is out of the question. My boss has determined that using a set of bridge tables with identical tables in a different database where we replace the bigint with int will work – we can view the data in the “bridged” database and even update it from select queries and simply by opening the table in view mode from Access.

    Here is the hard part (for me) because I have never done this. I can write SQL and understand the security/roles etc, but I have never administered SQL server regarding server side triggers and stored procedures – I have always used the command object or passthrough queries to deal with sql stored procedures. I need to either

    A) Create some trigger behind the tables in my bridge table that will fire a stored procedure to replicate the data to the bigint database. They are both on the same sever.
    B) Somehow create a “mirror” that always replicates from my bridge database tables (5 of them) to the new database.


    Any ideas or pointers would be of great help – my deadline is Friday LOL
    Dale Houston, TX

  2. Best Answer
    Posted by axsprog

    "RBarryYoung gave the best answer from StackOverflow – awesome

    USE [Fulcrum_Xfer]
    GO
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TRIGGER dbo.trOrders_Insert
    ON dbo.Orders
    AFTER INSERT AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Just INSERT everything from the [inserted] pseudotable into
    --the target table
    INSERT INTO [Fulcrum UAT].dbo.Orders
    (OrderNo, OrderDate, ApplicationTenantLinkId, OrderStatus)
    SELECT OrderNo, OrderDate, ApplicationTenantLinkId, OrderStatus
    FROM inserted;

    END
    GO"


  3. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    So what's the problem? The data looks funny in Access so we are going to build a data copy and synchronization process because the data looks funny in Access? It would probably be faster and cheaper to get you a licensed copy of management studio. What happens if your values start exceeding the limitations of integer and you need the extra scale of a big integer? Something will cease working.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    really

    I told you the problem - the client is not giving all users Management studio to just start poking around in the backend. we have to give them visibility to see field data in the "orders" table.

    int field goes to some 2 point whatever billion - will never see this max reached . Big int goes to
    nine quintillion two hundred twenty three quadrillion three hundred seventy two trillion thirty six billion eight hundred fifty four million seven hundred seventy five thousand eight hundred seven

    I do not even think that there are that many stars in the galaxy - and I would agree that the easiest way is to change that data type - but for what ever the reason is - that is not an option.

    I need to work in the solution - and that is how to create a row level (if possible ) trigger behind any update (other than an insert into) that says basically

    update table1 set fieldname = databasename.tablename.fieldname , etc
    where
    tablename.bigintfieldname = databasename.tablename.fieldname

    I can do these things from code but I have never done this from the SQL side -m hence the reason I am asking how to do it. I said I was a newbiw to this - but I also said I am not stupid.
    Dale Houston, TX

  5. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    great code best answer

    RBarryYoung gave the best answer from StackOverflow – awesome

    USE [Fulcrum_Xfer]
    GO
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TRIGGER dbo.trOrders_Insert
    ON dbo.Orders
    AFTER INSERT AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Just INSERT everything from the [inserted] pseudotable into
    --the target table
    INSERT INTO [Fulcrum UAT].dbo.Orders
    (OrderNo, OrderDate, ApplicationTenantLinkId, OrderStatus)
    SELECT OrderNo, OrderDate, ApplicationTenantLinkId, OrderStatus
    FROM inserted;

    END
    GO
    Dale Houston, TX

Posting Permissions

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