Combining two tables from different databases

I have two databases that each contain the same tables, but different data in the tables. For example, each data contains a table with the same name, arcus, that holds data on our customers. Although the data is different in each table, there is some overlap, particularly in the area of customer number since that is assigned automatically when a customer is entered and serves as the primary key for that table.

To consolidate, I need to merge the two databases. How can I import the data from one table in second database into a table in the first database and append a number to the customer number so that all data will be brought across.

To better illustrate:

database one has an arcus file with a field cusno and contains cusno 1-50
database two has an arcus file with a field cusno and contains cusno 27-58

The overlapping cusno's are not the same customer.

How can I get all the cusno from the arcus file in database two to the arcus file in database one?

Is this even possible?not without creating all new PK values.

if you are OK with a brand new value for the PK, which is sounds like you are, i'd create a staging table with an identity on the front of it and insert all the data from both, and use the new identity as your new cusno, replacing table in database1 (after renaming it with a '_BACKMEUPFOO' suffix)

in any scenario you face one big hurdle:
you will be breaking all the relationships FK'd to cusno in database #1.
all those related tables will need updating too...and the app that creates this data may not like it non-too-much, you changing its' PK and all.|||You can use either DTS or BCP...OUT. If you use DTS you can easily skip the IDENTITY field values and append from one database table to the other. If you choose BCP you'll have to create a format file during OUT operation, edit it with a text editor to specify that you are going to skip the IDENTITY field, and then BCP...IN/BULK INSERT specifying that modified format file.|||...which is why I like to use GUIDs as surrogate keys rather than incrementing identities. :D|||sounds like your need is to retain the original cusno's in some derivable fashion, and to do that you're going to need to create a surrogate or change the PK in the target database entirely - maybe compound it by adding a 'source system' character column to it. or just tack an 'a' on the end off all the original cusnos from the 1st server and a 'b' to all the second.

the relationship breaking is still gonna hurt you, without updating all the rest of the tables FK'd to cusno in your target - no matter how you pump the data or change the cusno.

DTS would be my ETL tool of choice - if i had to pick btw BCP and DTS, for this job.

Combining 2 records into 1

I have the follwing tables which contain error records and error reasons.
There are two erros entries for every error reason.
DDL
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[INPUT_ERRORS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[INPUT_ERRORS]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[INPUT_ERRORS_REASON]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[INPUT_ERRORS_REASON]
GO
CREATE TABLE [dbo].[INPUT_ERRORS] (
[Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ErrorItemErrorItemType] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AUtoRef] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[INPUT_ERRORS_REASON] (
[Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ErrorMessageMessageLine] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AUtoRef] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
SET NOCOUNT ON
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','ORIGINAL RECORD','19069',523)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','RETURNED RECORD','19069',524)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','7',537)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED RECORD','7',538)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','CONTRA',539)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED
RECORD',' BACS',540)SET NOCOUNT ON
SET NOCOUNT OFF
SET NOCOUNT ON
INSERT INTO [INPUT_ERRORS_REASON]
([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217523','16781','19069','RECI
PIENT''S SORT CODE IS INVALID',23
6)
INSERT INTO [INPUT_ERRORS_REASON]
([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','7','YOUR ACCOUNT DETAILS ARE INVALID',243)
INSERT INTO [INPUT_ERRORS_REASON]
([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','CONTRA','YOU
R
CONTRA ACCOUNT DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID',244)
SET NOCOUNT OFF
i would like to get this sresult returned for easy reading in a report
Licence ReportNumber ErrorItemErrorItemType1 ErrorItemreference1
ErrorItemErrorItemType2 ErrorItemreference2 ErrorMessageMessageLi
ne
'217523' '16781' 'ORIGINAL RECORD' '19069'
'RETURNED RECORD' '19069' 'RECIPIENT''S SORT CODE IS
INVALID'
'217993' '25194' 'ORIGINAL RECORD' '7'
'AMENDED RECORD' '7' 'YOUR ACCOUNT DETAILS ARE
INVALID'
'217993' '25194' 'ORIGINAL RECORD' 'CONTRA'
'AMENDED RECORD' ' BACS' 'CONTRA','YOUR CONTRA ACCOUNT
DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID'Peter Newman wrote:
> I have the follwing tables which contain error records and error reasons.
> There are two erros entries for every error reason.
> DDL
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[INPUT_ERRORS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[INPUT_ERRORS]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[INPUT_ERRORS_REASON]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[INPUT_ERRORS_REASON]
> GO
> CREATE TABLE [dbo].[INPUT_ERRORS] (
> [Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ErrorItemErrorItemType] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [AUtoRef] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[INPUT_ERRORS_REASON] (
> [Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ErrorMessageMessageLine] [varchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AUtoRef] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
>
> SET NOCOUNT ON
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','ORIGINAL RECORD','19069',523)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','RETURNED RECORD','19069',524)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','7',537)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED RECORD','7',538)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','CONTRA',539)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED
> RECORD',' BACS',540)SET NOCOUNT ON
> SET NOCOUNT OFF
> SET NOCOUNT ON
> INSERT INTO [INPUT_ERRORS_REASON]
> ([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217523','16781','19069','RECI
PIENT''S SORT CODE IS INVALID',
236)
> INSERT INTO [INPUT_ERRORS_REASON]
> ([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','7','YOUR ACCOUNT DETAILS ARE INVALID',243)
> INSERT INTO [INPUT_ERRORS_REASON]
> ([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','CONTRA','YOU
R
> CONTRA ACCOUNT DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID',244)
> SET NOCOUNT OFF
> i would like to get this sresult returned for easy reading in a report
> Licence ReportNumber ErrorItemErrorItemType1 ErrorItemreference1
> ErrorItemErrorItemType2 ErrorItemreference2 ErrorMessageMessage
Line
> '217523' '16781' 'ORIGINAL RECORD' '19069'
> 'RETURNED RECORD' '19069' 'RECIPIENT''S SORT CODE IS
> INVALID'
> '217993' '25194' 'ORIGINAL RECORD' '7'
> 'AMENDED RECORD' '7' 'YOUR ACCOUNT DETAILS ARE
> INVALID'
> '217993' '25194' 'ORIGINAL RECORD' 'CONTRA'
> 'AMENDED RECORD' ' BACS' 'CONTRA','YOUR CONTRA ACCOUN
T
> DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID'
Thanks for including the DDL. Unfortunately neither table has any keys!
Including an IDENTITY column means we can probably guess that that is a
key but an IDENTITY may not be enough to solve your problem. IDENTITY
should not be the only key of a table but you haven't shown us any
other so we can only guess. Here's one possibility:
SELECT licence, reportnumber,
LEFT(err1,50) AS erroritemtype1, RIGHT(err1,20) AS erroritemref1,
LEFT(err2,50) AS erroritemtype2, RIGHT(err2,20) AS erroritemref2
FROM
(SELECT licence, reportnumber,
MIN(CAST(erroritemerroritemtype AS CHAR(50))
+CAST(erroritemreference AS CHAR(20))) AS err1,
MAX(CAST(erroritemerroritemtype AS CHAR(50))
+CAST(erroritemreference AS CHAR(20))) AS err2
FROM input_errors
GROUP BY licence, reportnumber) AS T ;
David Portas
SQL Server MVP
--

Columns in database

Hi Group,
This might sound silly, but how can I find the tablenames in MSSQL2000 with which contain a certain columnname?
For example I want to get all the tablenames in a database which contain the column ProjectID.
Thanks for your helpselect * from INFORMATION_SCHEMA.COLUMNS where column_name='columnname'|||Originally posted by Satya
select * from INFORMATION_SCHEMA.COLUMNS where column_name='columnname'

Your the BEST!!
Thanks a lot.

Column encryption

How to encrypt a column in a table (i.e. which might contain passwords)?"igor" <igor@.mail.gif.com> wrote in message
news:bk9mmf$spu$1@.sunce.iskon.hr...
> How to encrypt a column in a table (i.e. which might contain passwords)?

There's nothing in MSSQL itself, so you can implement your own solution or
look for a third-party solution. Handling it in your application layer is
probably the most obvious solution.

http://www.sqlsecurity.com/ (see the FAQ)

Simon

Column constraint

Is it possible to limit a column's value to 0 or 1 and to allow zero or one
row to contain a value
of 1 for that column?
TIA, LarsNot 100% sure of your question but I think you want to look at CHECK
constraints and Unique Indexes in BooksOnLine. You can also enforce data
with triggers.
Andrew J. Kelly SQL MVP
"larzeb" <larzeb@.community.nospam> wrote in message
news:po5f811s7pvik05doqb0roide0g2uvla32@.
4ax.com...
> Is it possible to limit a column's value to 0 or 1 and to allow zero or
> one row to contain a value
> of 1 for that column?
> TIA, Lars|||> Is it possible to limit a column's value to 0 or 1
Yes, using either a Bit field or a Check constraint on a TinyInt, SmallInt,
or
Integer column.

> and to allow zero or one row to contain a value
> of 1 for that column?
This part is a bit trickier. Based purely on the information given, you coul
d
solve this in a couple of ways:
1. A trigger that raises an error when on an Insert or Update, a value alrea
dy
exists in the table with a value of 1.
2. Create a View that selects all rows that have a value of 1 and put a uniq
ue
index on that view.
There are other ways to solve this problem if you give us more information a
bout
the schema and problem you are trying to solve. For example, in building a
system for an insurance company, each insurance quote could be associated wi
th
multiple insurance brokers. However, each quote also had one and only one
"primary" broker. Thus, we solved this problem by putting a PrimaryBrokerId
column on the quote itself as well as had a table of "associated" brokers. T
his
made it easy to ensure that each quote had a primary broker and that there w
as
one and only one.
Granted, this sort of problem is much easier to solve in an object model tha
n it
is with database schema but with more infomration about the actual problem,
there might be a solution in the database design.
Thomas|||CREATE TABLE Foobar
( ..
silly_col INTEGER DEFAULT 0 NOT NULL
CONSTRAINT silly_col_range
CHECK (silly_col IN (0,1)),
CONSTRAINT silly_col_single flag
CHECK (1 - (SELECT SUM(silly_col) FROMbar)),
.);
But in SQL Server, you cannot write that second constraint like you can
in other products with better SQL-92 conformance. You would never use
bit data types because they are proprietary.
if you want to flag one row for some purpose, I would recomment that
you use a unique numeric column and select the minimum value:
CREATE TABLE Foobar
( ..
silly_col INTEGER DEFAULT 0 NOT NULL UNIQUE
CONSTRAINT silly_col_range
CHECK (silly_col >= 0),
.);
CREATE VIEW NewFoobar
AS
SELECT .. CASE WHEN silly_col
= (SELECT MIN(silly_col) FROM Foobar)
THEN 1 ELSE 0 END AS silly_flag,
FROM Foobar;|||If you just want to set the state (col =1) of exactly one row in a table
then maybe you could use a second table to do that:
CREATE TABLE YourTable (key_col INTEGER PRIMARY KEY, ... /* other cols */)
CREATE TABLE foo (key_col INTEGER NOT NULL REFERENCES YourTable (key_col), x
INTEGER DEFAULT 1 NOT NULL CHECK (x=1) UNIQUE /* Permit only one row */)
David Portas
SQL Server MVP
--

Collations

Hi,
I'm starting to build a database that will contain English and Welsh
text. Up until now I have just used the default collation settings, and
everything has been fine - but I've only ever needed to use English.
There is also the possibility that, at some point in the future, the
database will have to handle several Arabic and Asian languages.
So, my question is what collation should I used to make the database as
future-proof as possible given the statements above? Does, for example,
the default collation actually support the Welsh alphabet? (Welsh has
four extra characters that I don't think appear in other European
languages - ?, ? and their upper case equivalents - possibly more, but I
don't actually know Welsh!) I notice that there isn't a bog-standard
"UTF-8" option in the collation list...
Lastly, does anyone have any general tips regarding multi-lingual databases?
Cheers,
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.Hi Dylan,
Basically you collation should be the local environment where it is. So if
its in the UK it should be english etc.
With regards to your text, what you need to do is have the appropriate
datatype's for your tables.
For storing text that you would want as not only english, but another
language, instead of using a datatype of say char(10) you would use
nchar(10) this N, ensures that the type is unicode and not ANSI. Text
conversions of char can have mistakes, yet nchar represents the characters
as they should. One important note, using nchar instead of char, will
consume twice as much space as char, so just something to bear in mind.
hth
Barry Andrew
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d0c87$0$761$bed64819@.news.gradwell.net...
> Hi,
> I'm starting to build a database that will contain English and Welsh
> text. Up until now I have just used the default collation settings, and
> everything has been fine - but I've only ever needed to use English.
> There is also the possibility that, at some point in the future, the
> database will have to handle several Arabic and Asian languages.
> So, my question is what collation should I used to make the database as
> future-proof as possible given the statements above? Does, for example,
> the default collation actually support the Welsh alphabet? (Welsh has
> four extra characters that I don't think appear in other European
> languages - ?, ? and their upper case equivalents - possibly more, but I
> don't actually know Welsh!) I notice that there isn't a bog-standard
> "UTF-8" option in the collation list...
> Lastly, does anyone have any general tips regarding multi-lingual
> databases?
> Cheers,
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.|||Barry Andrew Hall wrote:
> Hi Dylan,
> Basically you collation should be the local environment where it is.
> So if its in the UK it should be english etc.
Ah okay - so I should simply leave it "as is", right?

> With regards to your text, what you need to do is have the
> appropriate datatype's for your tables.
> For storing text that you would want as not only english, but another
> language, instead of using a datatype of say char(10) you would use
> nchar(10) this N, ensures that the type is unicode and not ANSI.
> Text conversions of char can have mistakes, yet nchar represents the
> characters as they should.
Right, I understand now. So instead of using "text" to store several
paragraphs of writing, I should use "ntext" - but only in places that
could contain Welsh text.

> One important note, using nchar instead of char, will consume twice
> as much space as char, so just something to bear in mind.
In much the same way as saving a text file in UTF-8 instead of
ISO-8859-1 will consume more space. That's not too much of an issue for
me, although I guess it could cause degradation in performance?
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.|||Your right on the performance. But to be fair, if its designed solidly you
shouldnt have a thing to worry about.
Also, as you said, anything you want potentially in another language, have a
datatype of nvarchar(max) thats probably your best bet.
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d1330$0$757$bed64819@.news.gradwell.net...
> Barry Andrew Hall wrote:
> Ah okay - so I should simply leave it "as is", right?
>
> Right, I understand now. So instead of using "text" to store several
> paragraphs of writing, I should use "ntext" - but only in places that
> could contain Welsh text.
>
> In much the same way as saving a text file in UTF-8 instead of
> ISO-8859-1 will consume more space. That's not too much of an issue for
> me, although I guess it could cause degradation in performance?
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.

Collations

Hi,
I'm starting to build a database that will contain English and Welsh
text. Up until now I have just used the default collation settings, and
everything has been fine - but I've only ever needed to use English.
There is also the possibility that, at some point in the future, the
database will have to handle several Arabic and Asian languages.
So, my question is what collation should I used to make the database as
future-proof as possible given the statements above? Does, for example,
the default collation actually support the Welsh alphabet? (Welsh has
four extra characters that I don't think appear in other European
languages - ŵ, ŷ and their upper case equivalents - possibly more, but I
don't actually know Welsh!) I notice that there isn't a bog-standard
"UTF-8" option in the collation list...
Lastly, does anyone have any general tips regarding multi-lingual databases?
Cheers,
--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.Hi Dylan,
Basically you collation should be the local environment where it is. So if
its in the UK it should be english etc.
With regards to your text, what you need to do is have the appropriate
datatype's for your tables.
For storing text that you would want as not only english, but another
language, instead of using a datatype of say char(10) you would use
nchar(10) this N, ensures that the type is unicode and not ANSI. Text
conversions of char can have mistakes, yet nchar represents the characters
as they should. One important note, using nchar instead of char, will
consume twice as much space as char, so just something to bear in mind.
hth
Barry Andrew
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d0c87$0$761$bed64819@.news.gradwell.net...
> Hi,
> I'm starting to build a database that will contain English and Welsh
> text. Up until now I have just used the default collation settings, and
> everything has been fine - but I've only ever needed to use English.
> There is also the possibility that, at some point in the future, the
> database will have to handle several Arabic and Asian languages.
> So, my question is what collation should I used to make the database as
> future-proof as possible given the statements above? Does, for example,
> the default collation actually support the Welsh alphabet? (Welsh has
> four extra characters that I don't think appear in other European
> languages - ŵ, ŷ and their upper case equivalents - possibly more, but I
> don't actually know Welsh!) I notice that there isn't a bog-standard
> "UTF-8" option in the collation list...
> Lastly, does anyone have any general tips regarding multi-lingual
> databases?
> Cheers,
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.|||Barry Andrew Hall wrote:
> Hi Dylan,
> Basically you collation should be the local environment where it is.
> So if its in the UK it should be english etc.
Ah okay - so I should simply leave it "as is", right?
> With regards to your text, what you need to do is have the
> appropriate datatype's for your tables.
> For storing text that you would want as not only english, but another
> language, instead of using a datatype of say char(10) you would use
> nchar(10) this N, ensures that the type is unicode and not ANSI.
> Text conversions of char can have mistakes, yet nchar represents the
> characters as they should.
Right, I understand now. So instead of using "text" to store several
paragraphs of writing, I should use "ntext" - but only in places that
could contain Welsh text.
> One important note, using nchar instead of char, will consume twice
> as much space as char, so just something to bear in mind.
In much the same way as saving a text file in UTF-8 instead of
ISO-8859-1 will consume more space. That's not too much of an issue for
me, although I guess it could cause degradation in performance?
--
Dylan Parry
http://electricfreedom.org | http://webpageworkshop.co.uk
The opinions stated above are not necessarily representative of
those of my cats. All opinions expressed are entirely your own.|||Your right on the performance. But to be fair, if its designed solidly you
shouldnt have a thing to worry about.
Also, as you said, anything you want potentially in another language, have a
datatype of nvarchar(max) thats probably your best bet.
"Dylan Parry" <usenet@.dylanparry.com> wrote in message
news:460d1330$0$757$bed64819@.news.gradwell.net...
> Barry Andrew Hall wrote:
>> Hi Dylan,
>> Basically you collation should be the local environment where it is.
>> So if its in the UK it should be english etc.
> Ah okay - so I should simply leave it "as is", right?
>> With regards to your text, what you need to do is have the
>> appropriate datatype's for your tables.
>> For storing text that you would want as not only english, but another
>> language, instead of using a datatype of say char(10) you would use
>> nchar(10) this N, ensures that the type is unicode and not ANSI.
>> Text conversions of char can have mistakes, yet nchar represents the
>> characters as they should.
> Right, I understand now. So instead of using "text" to store several
> paragraphs of writing, I should use "ntext" - but only in places that
> could contain Welsh text.
>> One important note, using nchar instead of char, will consume twice
>> as much space as char, so just something to bear in mind.
> In much the same way as saving a text file in UTF-8 instead of
> ISO-8859-1 will consume more space. That's not too much of an issue for
> me, although I guess it could cause degradation in performance?
> --
> Dylan Parry
> http://electricfreedom.org | http://webpageworkshop.co.uk
> The opinions stated above are not necessarily representative of
> those of my cats. All opinions expressed are entirely your own.