SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Help - Partitioned View
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dean.c.cummins
Starting Member

6 Posts

Posted - 11/11/2010 :  13:00:34  Show Profile  Reply with Quote
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

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 11/11/2010 :  15:37:53  Show Profile  Reply with Quote
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 Optimizer
TG
Go to Top of Page

dean.c.cummins
Starting Member

6 Posts

Posted - 11/11/2010 :  15:59:16  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000