Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2010

    Unanswered: Help - Partitioned View

    Heres the situation...

    Im working with 2 different databases that are on the same server.
    I have only basic read permissions on the database named "mk". Whilst having full permissions on the database named "Spares".

    Basically theres an account table that resides on both databases. The one on "mk" is limited by the fact that the customer_no is of type char(6) e.g. " 1234" ... This is a problem cos now there are customer numbers that are of 10 characters long e.g. " 1234-123" so... ive created a similar copy of the account table in "mk" on the "spares" database - so that the long customer numbers can be stored on there.

    So already i have a data mis match - "mk" being char(6) and "Spares" being char(10) on the customer no.

    Also the account table in "mk" has no primary key!!! Very poor but the database is HUGE and im not allowed to touch anything deep like that. My account table in "Spares" has the primary key on the customer no - as expected.

    Also what might also be a problem is that the databases are of different collations!

    So i thought that i could create a view with a UNION ALL displaying all the accounts from different databases... which works...

    SELECT ma.t_cuno AS Acc_No, ma.t_nama AS Acc_Name, ma.t_creg AS Acc_Region, ma.t_ccty AS Acc_Country, ma.t_clan AS Acc_Language,ma.t_cpls AS Acc_PriceList, ma.t_ccur AS Acc_Currency
    FROM mk.dbo.ttccom010100 ma
    SELECT sa.Acc_No COLLATE Latin1_General_BIN, sa.Acc_Name COLLATE Latin1_General_BIN ,sa.Acc_Region COLLATE Latin1_General_BIN,
    sa.Acc_Country COLLATE Latin1_General_BIN, sa.Acc_Language COLLATE Latin1_General_BIN, sa.Acc_PriceList COLLATE Latin1_General_BIN,
    sa.Acc_Currency COLLATE Latin1_General_BIN
    FROM Spares.[dickinson\enquiry].tblAccount sa

    So this view works great at displaying data.

    However trying to modify data i get errors.

    Are there any work arounds for my situation?

    Its a bit of situation with all these mix match of collations, contraints and datatypes. :-(

    And im only trying to update an account from the view which resides on the "Spares" database. I dont expect to change data thats pulled in from the "mk" database.


  2. #2
    Join Date
    Nov 2002
    please restate your problem in 1 sentence


    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2005
    1) backup and restore mk so that you have proper permissions to edit
    2) come up with a plan to fix mk
    3) get the permissions you need on mk
    4) implement changes on mk

  4. #4
    Join Date
    Nov 2010


    hi... michael

  5. #5
    Join Date
    Jun 2003
    Provided Answers: 1
    How long are you going to continue to apply hacks to the system before actually changing the datasize? Eventually you will end up with a Rube Goldberg architecture that's going to collapse like a building in Bangalore.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

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