But how are you importing the data. What encoding are you using when you read the data in? Just because the database is storing these values doesn't mean that the problem is at the database end - it's entirely possible that the problem is at the data population end.
I have CDO, it's OCD with the letters in the right order; just as they ruddy well should be
Hi, am designing a database that expedite collection of data from the insurance companies by insurance body. members are required to upload daily transaction in xml format.the insurance policy no is the primary key but other information can change. the data will be query by users. should i store the data as xml datatype or in split the column as use cdc to audit the canges or plit the column with history table . like below
I'd also add to the above comment to create a RealProperty view that queries on only those columns that deal with RealProperty and like wise for the HomeOwner data as well. Just make sure that each view uses the index fields defined at the table level and everything should work fine.
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
I would not count on that. Besides, as a developer, you would be better off learning to handle the more general situation sooner rather than later.
I would take a slightly different tack and either have the RealProperty table refer to the HomeOwner table (as it's more common for one owner to own multiple properties) or (more likely) allow many-to-many relationships via a third table. But that's just me.
I would not use a trigger. In the DAL, I would have a method to add a property and a method to add an owner -- and another method to add a relationship. In the API I would have a method that calls both (all three) and handles transactioning.
I would also use SQL Server .
Edit: Fixed relationship .
Edit 2: Oh, yeah, you had it that way...
I do believe your data structure is incorrect. Your constraint of 1:1 between owner and property fails when someone own more that 1 property or a property has more than 1 owner or a property is sold (I assume you wish to retain history of the property).
As Piebald suggested a many:many link table is what you need to implement.
What happens if you have a company own the property, now you are in the position of needing an address table so you need a many:many link between owner and address...
Designing a data structure is not a trivial excercise and needs time and effort to get it right. It is guaranteed that you will not get it right the first time. Be prepared to admit an error and redesign ASAP, allowing an incorrect design to persist only deepens the cost of fixing the problem.
Never underestimate the power of human stupidity
I agree with the statement that I think your reasoning is flawed.
More often then not you are going to see multiple owners to a home (or any other entity) simply because of things like marriage, partnerships, trusts, etc... Also, keeping history now requires that you set up 2 tables that contain the same data linked to a building. Keeping it all in one table and adding an attribute to show current vs. past ownership I think is the better option.
In fact, you are probably really going to want to separate out the table that tracks the data about the building, the tables that track the address data of all the possible owners and then use a join table to crate the linking between the two. That I think is the best way to achieve a clean design. When you consider what else you can store in this third reference table (IE: the data about the sales agent(s) involved, the inspectors used durring the sales, etc...) you start to see how it works cleaner.
For an example, using Sql Server - let's say I have three tables:
Each record in Data can be linked back to each Client record through the File Record. I want the DataField value to be unique only per client - so two values of "42" in the DataField is ok as long as they link to different clients.
I could put the ClientID in the Data table, but that would then mean I would need to be sure the ClientID in the related File record always matches.
Can an index be set on the Data table using the DataField field and the Client ID field?
If so, how would you do this? If not, is there an alternative?
I think you can add two unique constaint in File..ClientID and Data..FileID to achieve your goal. By your data design, if you want the DataField value to be unique only per client, the infomation in [File] should also unique match one [Client] record.
We have a linked server on SQL 2005 to database on AS400. the linked server is made using Microsoft DB2OLEDB provider.
when we are using the linked server, we are getting the following error:
OLE DB provider "DB2OLEDB" for linked server "AS400PROD" returned message "".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "AS400PROD".
When we restart the SQL server machine, the linked server works fine for about 5 minutes then we receive the same error as described before,
The script of the linked server is :
EXEC master.dbo.sp_addlinkedserver @server = N'AS400PROD', @srvproduct=N'AS400', @provider=N'DB2OLEDB', @datasrc=N'S6595D7F', @provstr=N'Provider=DB2OLEDB;Password=ISA108;Persist Security Info=True;User ID=ASI;Initial Catalog=S6595D7F;Data Source=AS400;Network Address=220.127.116.11;Package Collection=KFILFNB;DBMS Platform=DB2/AS400;Process Binary as Character=True'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AS400PROD',@useself=N'False',@locallogin=NULL,@rmtuser=N'LOGINRPORT',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'AS400PROD', @optname=N'use remote collation', @optvalue=N'true'
What iam sure of is that it is not an issue from SQL server. i have tried to ping the AS400 server and the reply is <1 ms then i wrote a query to check the linked server and it is giving an ok reply b4 it stops suddenly. i have seen soo many possible issues and read articles abt the same error Msg 7303 and i will be trying to update the server with hotfixes. Are there any fixes that came to your mind?
MKG Ex Du A918731765L
TVN Ex Du A919721354L
NCL Ex Du A901713191L
Bin Ex Du A948606458L
--- -- -- ----------
--- -- -- ----------
etc etc etc(table is having hundreds of data like this and it is a expanding table.So i have to split all of them not only the mentioned data.So a common way to split this column is required.)
I am writing a stored procedure to get this columns as
SK1 SK2 SK3 SK4 SK5
MKG EX Du A91873176 5L
TVN Ex Du A91972135 4L
The values will be always in the above format.
Thanks in advance
CREATE FUNCTION [dbo].[Split]
) RETURNS @t TABLE
id int identity(1,1),
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'
insert into @t(val)
r.value('.','varchar(15)') as item
from @xml.nodes('//root/r') as records(r)
Then you cross apply to your data table, pivot, then manipulate the display data.
Here is an example:
declare @tmp table (data varchar(50))
insert into @tmp (data) values ('MKG Ex Du A918731765L')
insert into @tmp (data) values ('TVN Ex Du A919721354L')
insert into @tmp (data) values ('NCL Ex Du A901713191L')
insert into @tmp (data) values ('Bin Ex Du A948606458L')
select  as'SK1',  as'SK2',  as'SK3', substring(, 0, 10) as'SK3', substring(, 10, 2) as'SK4'from
select  ,  ,  , 
select * from
cross apply dbo.Split(t.data,' ')
) as sourcetb
(max(val) for id in (,,,)
) as pivottable
) as t