|
dean.c.cummins
Starting Member
6 Posts |
 Posted - 11/11/2010 : 13:00:34
|
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
UNION ALL
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. :-(
EDIT: 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.
x |
Edited by - dean.c.cummins on 11/11/2010 13:03:12
|
|
Help - Partitioned View
