Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dean.c.cummins
Starting Member
6 Posts |
 Posted - 2010-11-11 : 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_CurrencyFROM mk.dbo.ttccom010100 maUNION ALLSELECT 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_BINFROM Spares.[dickinson\enquiry].tblAccount saSo 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 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2010-11-11 : 15:37:53
|
| Just use the view to view the data. If you want to update data in the spares database then update those table(s) directly.Be One with the OptimizerTG |
 |
|
|
dean.c.cummins
Starting Member
6 Posts |
Posted - 2010-11-11 : 15:59:16
|
| hmm...well ontop of that view i have another view pulling data in from another table with my information about ALL the accounts. lol. so it would be a bit confusing and code confusing to write to the tables directly in my delphi client.so is there no way? x |
|
|
|
|
|
|
|
|
Help - Partitioned View