Collation problem: case

Showing posts with label case. Show all posts

Thursday, March 22, 2012

combined merge + custom conflict resolution

I have what seems to be a unique case, but logically, would think that
someone out there is already doing this. I have the following configuration:
- merge replication
- column level tracking
- single pub./dist. server replicating with a single subscriber
- SQL Server 2000 Ent. on Windows 2000 Adv. Server
I have succesfully implemented replication, and data is merged properly in
both directions. However, when confilcts occurr, I want the ability to allow
those records that would merge accordingly (not in conflict), but for those
columns in conflict, I want the ability to run a stored procedure with my
proper business logic. Is there a way to do this? When using a stored proc
for conflict resolution, you are forced to return the "winning values" for
all the fields. This is where I lose the data that would have normally
replicated that was not in conflict. In my stored proc, I wish I had the
ability to see or read from a table the data values that would normally be
merged that were not in conflict, so I could supply them in my returned
recordset. I have the logic of reading both Subscriber and Publisher field
values for the current rowguid, but I don't know which of the two values (sub
or pub) has been edited so that it can merge.
Am I explaining this properly? I currently have an open instance with
Microsoft tech support ($250 !!), but they have yet to come back with a
solution. They have had the specs to my issue for a week now, and they have
not been able to resolve it. Maybe someone out there has done this. Thanks
in advance for all the help!
This type of logic is possible using merge replication, but you will have to
hack into the stored procedures that the merge agent uses to apply the
changes to the subscriber and publisher.
However, I think bi-directional transactional replication is a better choice
for implementing this form of custom business logic. When you use XCALL the
before and after images of the data will flow from the Publisher to the
Subscriber and you can incorporate logic to handle your conflicts this way.
So lets consider what a conflict is. A conflict is
1) a pk violation, trying to insert a PK value where a row with that PK
value already exists on the subscriber
2) trying to update a row on the Subscriber and instead of updating a single
row you update 0 or more than one rows.
3) trying to delete a row on the Subscriber and instead of deleting a single
row you delete 0 or more than one rows.
So, before your insert proc fires it will do an existence check. If the row
exists, it can instead do an update which will incorporate your custom
business logic. If the update ends up updating more than one row you have to
consider what is going on. This probably violates your database integrity,
but may not depending on how your database is set up or what you are trying
to accomplish. For instance consider you are replicating to audit table. A
row with a PK value of 1 may be inserted at the publisher and replicated to
the Subscriber. Then this row is deleted at the publisher, but this delete
is not replicated to the subscriber (remember the subscriber is an audit
table, so we need a record of that row). Then this row is readded with the
same PK (1) and replicate to the subscriber. If you haven't handled the
possibility of duplicate PK's you will have a problem.
Updates and deletes are simple as long as you can preserve the one to one
mapping of rows on the Publisher to the Subsriber. If you are doing a one to
many from the Publisher to the Subscriber it gets more complex, but it is
not impossible.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"deanc24" <deanc24athotmaildotcom> wrote in message
news:696CC504-19A5-4217-B12E-EEF9D181E842@.microsoft.com...
> I have what seems to be a unique case, but logically, would think that
> someone out there is already doing this. I have the following
configuration:
> - merge replication
> - column level tracking
> - single pub./dist. server replicating with a single subscriber
> - SQL Server 2000 Ent. on Windows 2000 Adv. Server
> I have succesfully implemented replication, and data is merged properly in
> both directions. However, when confilcts occurr, I want the ability to
allow
> those records that would merge accordingly (not in conflict), but for
those
> columns in conflict, I want the ability to run a stored procedure with my
> proper business logic. Is there a way to do this? When using a stored
proc
> for conflict resolution, you are forced to return the "winning values" for
> all the fields. This is where I lose the data that would have normally
> replicated that was not in conflict. In my stored proc, I wish I had the
> ability to see or read from a table the data values that would normally be
> merged that were not in conflict, so I could supply them in my returned
> recordset. I have the logic of reading both Subscriber and Publisher
field
> values for the current rowguid, but I don't know which of the two values
(sub
> or pub) has been edited so that it can merge.
> Am I explaining this properly? I currently have an open instance with
> Microsoft tech support ($250 !!), but they have yet to come back with a
> solution. They have had the specs to my issue for a week now, and they
have
> not been able to resolve it. Maybe someone out there has done this.
Thanks
> in advance for all the help!
>
|||being that I am only capable to connect at night, I was under the impression
that Merge Replication was my only option. Transaction Replication os for
constant connection, right? If I am wrong, please advise. As for the
remainder of your comments, I am not that familiar with Replication, to
understand everything you are referring to. If you know of any publications
or web sites that can help educate me, that would be great. So far, the web
has seemed to be kinda light on info that deals with the deep intricate
details regarding replication. It brushes lightly on the topic, but thats
it. Thanks so much for your assistance. I have been stuck with this for a
week now. Its frustrating.
"Hilary Cotter" wrote:

> This type of logic is possible using merge replication, but you will have to
> hack into the stored procedures that the merge agent uses to apply the
> changes to the subscriber and publisher.
> However, I think bi-directional transactional replication is a better choice
> for implementing this form of custom business logic. When you use XCALL the
> before and after images of the data will flow from the Publisher to the
> Subscriber and you can incorporate logic to handle your conflicts this way.
> So lets consider what a conflict is. A conflict is
> 1) a pk violation, trying to insert a PK value where a row with that PK
> value already exists on the subscriber
> 2) trying to update a row on the Subscriber and instead of updating a single
> row you update 0 or more than one rows.
> 3) trying to delete a row on the Subscriber and instead of deleting a single
> row you delete 0 or more than one rows.
> So, before your insert proc fires it will do an existence check. If the row
> exists, it can instead do an update which will incorporate your custom
> business logic. If the update ends up updating more than one row you have to
> consider what is going on. This probably violates your database integrity,
> but may not depending on how your database is set up or what you are trying
> to accomplish. For instance consider you are replicating to audit table. A
> row with a PK value of 1 may be inserted at the publisher and replicated to
> the Subscriber. Then this row is deleted at the publisher, but this delete
> is not replicated to the subscriber (remember the subscriber is an audit
> table, so we need a record of that row). Then this row is readded with the
> same PK (1) and replicate to the subscriber. If you haven't handled the
> possibility of duplicate PK's you will have a problem.
> Updates and deletes are simple as long as you can preserve the one to one
> mapping of rows on the Publisher to the Subsriber. If you are doing a one to
> many from the Publisher to the Subscriber it gets more complex, but it is
> not impossible.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "deanc24" <deanc24athotmaildotcom> wrote in message
> news:696CC504-19A5-4217-B12E-EEF9D181E842@.microsoft.com...
> configuration:
> allow
> those
> proc
> field
> (sub
> have
> Thanks
>
>
|||While you can use bi-directional transactional replication in a disconnected
manner it is not really advisable to do so, as the conflict tracking and
resolving mechanisms are essentially non-existent.
If you get a conflict your distribution agent will fail and you will have to
resolve the conflict and restart the agent. If you have many conflicts this
is not acceptable, but if you have few conflicts or you can design your
replication solution to minimize conflicts this might be a solution for you.
Again, I don't know your data, data flow, or topology so I can't really
advise you on it. You know it more intimately than I so you should make the
decision on this.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"deanc24" <deanc24athotmaildotcom> wrote in message
news:C223CB8B-F458-4612-9531-398B4D42D8FA@.microsoft.com...
> being that I am only capable to connect at night, I was under the
impression
> that Merge Replication was my only option. Transaction Replication os for
> constant connection, right? If I am wrong, please advise. As for the
> remainder of your comments, I am not that familiar with Replication, to
> understand everything you are referring to. If you know of any
publications
> or web sites that can help educate me, that would be great. So far, the
web
> has seemed to be kinda light on info that deals with the deep intricate
> details regarding replication. It brushes lightly on the topic, but thats
> it. Thanks so much for your assistance. I have been stuck with this for
a[vbcol=seagreen]
> week now. Its frustrating.
> "Hilary Cotter" wrote:
have to[vbcol=seagreen]
choice[vbcol=seagreen]
the[vbcol=seagreen]
way.[vbcol=seagreen]
single[vbcol=seagreen]
single[vbcol=seagreen]
row[vbcol=seagreen]
have to[vbcol=seagreen]
integrity,[vbcol=seagreen]
trying[vbcol=seagreen]
A[vbcol=seagreen]
to[vbcol=seagreen]
delete[vbcol=seagreen]
the[vbcol=seagreen]
one[vbcol=seagreen]
one to[vbcol=seagreen]
is[vbcol=seagreen]
properly in[vbcol=seagreen]
to[vbcol=seagreen]
my[vbcol=seagreen]
stored[vbcol=seagreen]
for[vbcol=seagreen]
normally[vbcol=seagreen]
the[vbcol=seagreen]
normally be[vbcol=seagreen]
returned[vbcol=seagreen]
values[vbcol=seagreen]
a[vbcol=seagreen]
they[vbcol=seagreen]
|||I appreciate your feedback Hilary. I do think that bi-directional
transactional replication is not an option for me, due to the number of
conflicts anticipated. I hope that Microsoft's support people will help me
find the right answer. Its costing me $250, so they better! :-)
Thanks again. I enjoy your particiapation on this discussion board. Its a
great educational tool!
"Hilary Cotter" wrote:

> While you can use bi-directional transactional replication in a disconnected
> manner it is not really advisable to do so, as the conflict tracking and
> resolving mechanisms are essentially non-existent.
> If you get a conflict your distribution agent will fail and you will have to
> resolve the conflict and restart the agent. If you have many conflicts this
> is not acceptable, but if you have few conflicts or you can design your
> replication solution to minimize conflicts this might be a solution for you.
> Again, I don't know your data, data flow, or topology so I can't really
> advise you on it. You know it more intimately than I so you should make the
> decision on this.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "deanc24" <deanc24athotmaildotcom> wrote in message
> news:C223CB8B-F458-4612-9531-398B4D42D8FA@.microsoft.com...
> impression
> publications
> web
> a
> have to
> choice
> the
> way.
> single
> single
> row
> have to
> integrity,
> trying
> A
> to
> delete
> the
> one
> one to
> is
> properly in
> to
> my
> stored
> for
> normally
> the
> normally be
> returned
> values
> a
> they
>
>

Saturday, February 25, 2012

Column Names cAsE SeNSitive? - DFT

Does the column names are case sensitive for the DFT? Recently in one of table, the column names were changed to Upper Case from Lower case and the package started failing in validation. Is there any solution / work around for this?

Thanks

Is the database itself case-sensitive?|||

No the database is not case sensitive. I can use mixed cases for column names in Management Studio/Query Analyzer, I still get results. Only in SSIS it fails, especially when you modify the column case after the package is created.

This is what I have:-
A simple DFT task.
OLE DB Source is Sybase, OLE DB Destination is SQL SERVER 2005. No other transformations in between.
Sql Server has columns in lowercase when the package was developed, but later somebody recreated table with columns in upper case and package started to fail in validation.

Thanks

|||

Can any one tell me if this is the behaviour?

Thanks

|||

Karunakaran wrote:

Can any one tell me if this is the behaviour?
Thanks

Yes, this is confirmed in SP2. I agree, this probably should not be designed to be case sensitive. Can you post a bug at http://connect.microsoft.com/sqlserver/feedback? Then post back here with a link to the submission?

Thanks,
Phil|||

I have submitted the bug, below is the link.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=296668

Thanks

Column Names cAsE SeNSitive? - DFT

Thanks

Is the database itself case-sensitive?|||

Thanks

|||

Can any one tell me if this is the behaviour?

Thanks

|||

Karunakaran wrote:

Can any one tell me if this is the behaviour?
Thanks

Thanks,
Phil|||

I have submitted the bug, below is the link.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=296668

Thanks

Column Names cAsE SeNSitive? - DFT

Thanks

Is the database itself case-sensitive?|||

Thanks

|||

Can any one tell me if this is the behaviour?

Thanks

|||

Karunakaran wrote:

Can any one tell me if this is the behaviour?
Thanks

Thanks,
Phil|||

I have submitted the bug, below is the link.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=296668

Thanks

Column Names cAsE SeNSitive? - DFT

Thanks

Is the database itself case-sensitive?|||

Thanks

|||

Can any one tell me if this is the behaviour?

Thanks

|||

Karunakaran wrote:

Can any one tell me if this is the behaviour?
Thanks

Thanks,
Phil|||

I have submitted the bug, below is the link.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=296668

Thanks

Sunday, February 12, 2012

Collation-any practical difference?

Case:
*SQL Server 2005 Ent (9.00.3054.00)
*One database supporting the following countries:
*Austria
*Canada
*Denmark
*Finland
*France
*Germany
*Norway
*Spain
*Sweden
*Switzerland
*United Kingdom
*database and application is fully Unicode enabled
*server collation is Danish_Norwegian_CI_AS
*database collation is the same as server collation (Danish_Norwegian_CI_AS)
Question:
Will it be any major difference between Danish_Norwegian_CI_AS and
Latin1_General_CI_AS ? If any; do You have any good examples explaining the
differens(es)?
Both are Case insensitive and Accent sensitive (required) and both supports
all the 1252 character sets, but I have'nt been able to find any major
difference between the two in any documentation other than the first giving
the special "Nordic" characters Ã¦,Ã¸,Ã¥ priority during compare and hence
sorting.
Any feedback would be highly welcome.ORDER BY is one thing to watch out for.
CREATE TABLE #t(c char(2))
INSERT INTO #t VALUES('a')
INSERT INTO #t VALUES('b')
INSERT INTO #t VALUES('z')
INSERT INTO #t VALUES('A')
INSERT INTO #t VALUES('B')
INSERT INTO #t VALUES('Z')
INSERT INTO #t VALUES('Ã¥')
INSERT INTO #t VALUES('Ã¤')
INSERT INTO #t VALUES('Ã¶')
INSERT INTO #t VALUES('Ã?')
INSERT INTO #t VALUES('Ã?')
INSERT INTO #t VALUES('Ã¦')
INSERT INTO #t VALUES('Ã¸')
INSERT INTO #t VALUES('Ã?')
INSERT INTO #t VALUES('Ã¼')
INSERT INTO #t VALUES('Ã?')
INSERT INTO #t VALUES('vb')
INSERT INTO #t VALUES('Va')
INSERT INTO #t VALUES('Wb')
INSERT INTO #t VALUES('wa')
SELECT * FROM #t ORDER BY c COLLATE Finnish_Swedish_CI_AI
SELECT * FROM #t ORDER BY c COLLATE Danish_Norwegian_CI_AS
SELECT * FROM #t ORDER BY c COLLATE Latin1_General_CI_AS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Back2Ax" <Back2Ax@.discussions.microsoft.com> wrote in message
news:E781A2FF-6446-4EC8-BBCD-08AEF1859079@.microsoft.com...
> Case:
> *SQL Server 2005 Ent (9.00.3054.00)
> *One database supporting the following countries:
> *Austria
> *Canada
> *Denmark
> *Finland
> *France
> *Germany
> *Norway
> *Spain
> *Sweden
> *Switzerland
> *United Kingdom
> *database and application is fully Unicode enabled
> *server collation is Danish_Norwegian_CI_AS
> *database collation is the same as server collation (Danish_Norwegian_CI_AS)
> Question:
> Will it be any major difference between Danish_Norwegian_CI_AS and
> Latin1_General_CI_AS ? If any; do You have any good examples explaining the
> differens(es)?
> Both are Case insensitive and Accent sensitive (required) and both supports
> all the 1252 character sets, but I have'nt been able to find any major
> difference between the two in any documentation other than the first giving
> the special "Nordic" characters Ã¦,Ã¸,Ã¥ priority during compare and hence
> sorting.
> Any feedback would be highly welcome.|||Did the same test - can't say that the results where as expected in any way
:-( I expected that a,b,c would have been sorted the same way (rank) with all
collations but even this was different (c was returned before a in all
collations and the special Norwegian/Danish characters was'nt in the right
sequence either).
Should I make the decision based on the country with the most users or
should I take the number of special characters into consideration? There are
trade offs with every collation far as I can see.
Mayby I'm missing something here...
"Tibor Karaszi" wrote:
> ORDER BY is one thing to watch out for.
> CREATE TABLE #t(c char(2))
> INSERT INTO #t VALUES('a')
> INSERT INTO #t VALUES('b')
> INSERT INTO #t VALUES('z')
> INSERT INTO #t VALUES('A')
> INSERT INTO #t VALUES('B')
> INSERT INTO #t VALUES('Z')
> INSERT INTO #t VALUES('Ã¥')
> INSERT INTO #t VALUES('Ã¤')
> INSERT INTO #t VALUES('Ã¶')
> INSERT INTO #t VALUES('Ã?')
> INSERT INTO #t VALUES('Ã?')
> INSERT INTO #t VALUES('Ã¦')
> INSERT INTO #t VALUES('Ã¸')
> INSERT INTO #t VALUES('Ã?')
> INSERT INTO #t VALUES('Ã¼')
> INSERT INTO #t VALUES('Ã?')
> INSERT INTO #t VALUES('vb')
> INSERT INTO #t VALUES('Va')
> INSERT INTO #t VALUES('Wb')
> INSERT INTO #t VALUES('wa')
>
> SELECT * FROM #t ORDER BY c COLLATE Finnish_Swedish_CI_AI
> SELECT * FROM #t ORDER BY c COLLATE Danish_Norwegian_CI_AS
> SELECT * FROM #t ORDER BY c COLLATE Latin1_General_CI_AS
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Back2Ax" <Back2Ax@.discussions.microsoft.com> wrote in message
> news:E781A2FF-6446-4EC8-BBCD-08AEF1859079@.microsoft.com...
> > Case:
> > *SQL Server 2005 Ent (9.00.3054.00)
> > *One database supporting the following countries:
> >
> > *Austria
> > *Canada
> > *Denmark
> > *Finland
> > *France
> > *Germany
> > *Norway
> > *Spain
> > *Sweden
> > *Switzerland
> > *United Kingdom
> >
> > *database and application is fully Unicode enabled
> > *server collation is Danish_Norwegian_CI_AS
> > *database collation is the same as server collation (Danish_Norwegian_CI_AS)
> >
> > Question:
> >
> > Will it be any major difference between Danish_Norwegian_CI_AS and
> > Latin1_General_CI_AS ? If any; do You have any good examples explaining the
> > differens(es)?
> >
> > Both are Case insensitive and Accent sensitive (required) and both supports
> > all the 1252 character sets, but I have'nt been able to find any major
> > difference between the two in any documentation other than the first giving
> > the special "Nordic" characters Ã¦,Ã¸,Ã¥ priority during compare and hence
> > sorting.
> >
> > Any feedback would be highly welcome.
>

Friday, February 10, 2012

Collation Properties

Hi,
I want to specify case sensitivity for my database, but at the same time i
want let customer decide the sort order.
What i mean is: take the following collation property
SQL_Latin1_general_cs_as
in the above property, i want to specify only CS, and let cusotmer decide
what to (example: what sort order) specify.
Is it possible? How to do this? I hope this is a very natural requirement,
but could find help in MS documents for this
Thanks,
VenkatThe collation determines the sort rules, so you cannot specify a Collation
and apply different sort rules.
Consider two other important issues:
- a collation applied to a single database involves problems regarding the
collation of the other databases of the same instance (eg. master, msdb, etc).
To resolve this issue is better apply a specific collation to the table
columns (very tedious but...).
the Latin1_general_cs_as sorts upper case and lower case separately but in a
different way among Latin1_General_BIN;I don't remember exactly which one,
but one sorts all the upper cases together separately among the lower case,
the other sorts instead A with a, B with b and so on.
Remeber furthermore tha the new collation does'nt convert the collation of
data already present in your tables.
"Venkat" wrote:
> Hi,
> I want to specify case sensitivity for my database, but at the same time i
> want let customer decide the sort order.
> What i mean is: take the following collation property
> SQL_Latin1_general_cs_as
> in the above property, i want to specify only CS, and let cusotmer decide
> what to (example: what sort order) specify.
> Is it possible? How to do this? I hope this is a very natural requirement,
> but could find help in MS documents for this
> Thanks,
> Venkat
>
>|||Hi,
I would like to understand your statement:
" collation applied to a single database involves problems regarding the
collation of the other databases of the same instance (eg. master, msdb,
etc)."
What is the problem if my database is in diffrent collaiton than the master
database?
I am sure this is possible and there would be no issuees with this.
There might be issues with tempdb, so you can specify collation when you are
creating temp tables.
Can you tell me what is the problems you are expecting if master database
and user database is in diffrent collation?
Other thing you said is: sorting, case sensitivity, accent sensitivity (I
call them as collaiton properties) have to be specified together only?
the reason you said is: all these properties together forms as sort rules?
If so, then when the properties: Latin1, General going to be used and what
purpose they are for? (asume that i have Unicode database)
comments?
Regards,
Venkat
"Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> wrote in
message news:7E4D942B-9CEE-46E8-805B-F64233738B04@.microsoft.com...
> The collation determines the sort rules, so you cannot specify a Collation
> and apply different sort rules.
> Consider two other important issues:
> - a collation applied to a single database involves problems regarding the
> collation of the other databases of the same instance (eg. master, msdb,
> etc).
> To resolve this issue is better apply a specific collation to the table
> columns (very tedious but...).
> the Latin1_general_cs_as sorts upper case and lower case separately but in
> a
> different way among Latin1_General_BIN;I don't remember exactly which one,
> but one sorts all the upper cases together separately among the lower
> case,
> the other sorts instead A with a, B with b and so on.
> Remeber furthermore tha the new collation does'nt convert the collation
> of
> data already present in your tables.
>
> "Venkat" wrote:
>> Hi,
>> I want to specify case sensitivity for my database, but at the same time
>> i
>> want let customer decide the sort order.
>> What i mean is: take the following collation property
>> SQL_Latin1_general_cs_as
>> in the above property, i want to specify only CS, and let cusotmer decide
>> what to (example: what sort order) specify.
>> Is it possible? How to do this? I hope this is a very natural
>> requirement,
>> but could find help in MS documents for this
>> Thanks,
>> Venkat
>>|||Hi,
i wrote about issues in generic terms: having objects in different
collations COULD be an issue, but not necessarily IS. Surely some (very
little) overhead due to internal conversion is more probable in presence of
more than one collation.
More: the sort order is implicit in the collation you select: BIN (binary)
sort is different among CS_AS or CI_AS or CI_AI etc.
So the Properties Latin1, General are "complementary" with the CS, CI, AS,
AI, KS (etc.) properties: the sort order could be different among each
available combination.
The concept of collation involves not only sort rules but also character set
consideration.
You cannot define o sort order apart from the selected collation.
"Venkat" wrote:
> Hi,
> I would like to understand your statement:
> " collation applied to a single database involves problems regarding the
> collation of the other databases of the same instance (eg. master, msdb,
> etc)."
> What is the problem if my database is in diffrent collaiton than the master
> database?
> I am sure this is possible and there would be no issuees with this.
> There might be issues with tempdb, so you can specify collation when you are
> creating temp tables.
> Can you tell me what is the problems you are expecting if master database
> and user database is in diffrent collation?
> Other thing you said is: sorting, case sensitivity, accent sensitivity (I
> call them as collaiton properties) have to be specified together only?
> the reason you said is: all these properties together forms as sort rules?
> If so, then when the properties: Latin1, General going to be used and what
> purpose they are for? (asume that i have Unicode database)
> comments?
> Regards,
> Venkat
> "Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> wrote in
> message news:7E4D942B-9CEE-46E8-805B-F64233738B04@.microsoft.com...
> > The collation determines the sort rules, so you cannot specify a Collation
> > and apply different sort rules.
> > Consider two other important issues:
> > - a collation applied to a single database involves problems regarding the
> > collation of the other databases of the same instance (eg. master, msdb,
> > etc).
> > To resolve this issue is better apply a specific collation to the table
> > columns (very tedious but...).
> >
> > the Latin1_general_cs_as sorts upper case and lower case separately but in
> > a
> > different way among Latin1_General_BIN;I don't remember exactly which one,
> > but one sorts all the upper cases together separately among the lower
> > case,
> > the other sorts instead A with a, B with b and so on.
> >
> > Remeber furthermore tha the new collation does'nt convert the collation
> > of
> > data already present in your tables.
> >
> >
> > "Venkat" wrote:
> >
> >> Hi,
> >> I want to specify case sensitivity for my database, but at the same time
> >> i
> >> want let customer decide the sort order.
> >> What i mean is: take the following collation property
> >> SQL_Latin1_general_cs_as
> >>
> >> in the above property, i want to specify only CS, and let cusotmer decide
> >> what to (example: what sort order) specify.
> >>
> >> Is it possible? How to do this? I hope this is a very natural
> >> requirement,
> >> but could find help in MS documents for this
> >>
> >> Thanks,
> >> Venkat
> >>
> >>
> >>
>
>|||Hi,
I would like to restate what you said about "complimentary" properties like
Latin, General etc.,
As per my understanding these complimentary properties are useful for non
unicode database.
Beucase as per my guess these properties (Latin, General etc.,) specifies
how the data can be stored: in which character set for non unicode database.
Where as these complimnetary properties are of no use (but we still need to
specify) for unicode database.
You have any comments on my understanding?
Regards,
Venkat
"Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> wrote in
message news:BF5A2E30-5D28-4129-B025-487DB4F6949D@.microsoft.com...
> Hi,
> i wrote about issues in generic terms: having objects in different
> collations COULD be an issue, but not necessarily IS. Surely some (very
> little) overhead due to internal conversion is more probable in presence
> of
> more than one collation.
> More: the sort order is implicit in the collation you select: BIN (binary)
> sort is different among CS_AS or CI_AS or CI_AI etc.
> So the Properties Latin1, General are "complementary" with the CS, CI, AS,
> AI, KS (etc.) properties: the sort order could be different among each
> available combination.
> The concept of collation involves not only sort rules but also character
> set
> consideration.
> You cannot define o sort order apart from the selected collation.
>
> "Venkat" wrote:
>> Hi,
>> I would like to understand your statement:
>> " collation applied to a single database involves problems regarding the
>> collation of the other databases of the same instance (eg. master, msdb,
>> etc)."
>> What is the problem if my database is in diffrent collaiton than the
>> master
>> database?
>> I am sure this is possible and there would be no issuees with this.
>> There might be issues with tempdb, so you can specify collation when you
>> are
>> creating temp tables.
>> Can you tell me what is the problems you are expecting if master database
>> and user database is in diffrent collation?
>> Other thing you said is: sorting, case sensitivity, accent sensitivity (I
>> call them as collaiton properties) have to be specified together only?
>> the reason you said is: all these properties together forms as sort
>> rules?
>> If so, then when the properties: Latin1, General going to be used and
>> what
>> purpose they are for? (asume that i have Unicode database)
>> comments?
>> Regards,
>> Venkat
>> "Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> wrote in
>> message news:7E4D942B-9CEE-46E8-805B-F64233738B04@.microsoft.com...
>> > The collation determines the sort rules, so you cannot specify a
>> > Collation
>> > and apply different sort rules.
>> > Consider two other important issues:
>> > - a collation applied to a single database involves problems regarding
>> > the
>> > collation of the other databases of the same instance (eg. master,
>> > msdb,
>> > etc).
>> > To resolve this issue is better apply a specific collation to the table
>> > columns (very tedious but...).
>> >
>> > the Latin1_general_cs_as sorts upper case and lower case separately but
>> > in
>> > a
>> > different way among Latin1_General_BIN;I don't remember exactly which
>> > one,
>> > but one sorts all the upper cases together separately among the lower
>> > case,
>> > the other sorts instead A with a, B with b and so on.
>> >
>> > Remeber furthermore tha the new collation does'nt convert the
>> > collation
>> > of
>> > data already present in your tables.
>> >
>> >
>> > "Venkat" wrote:
>> >
>> >> Hi,
>> >> I want to specify case sensitivity for my database, but at the same
>> >> time
>> >> i
>> >> want let customer decide the sort order.
>> >> What i mean is: take the following collation property
>> >> SQL_Latin1_general_cs_as
>> >>
>> >> in the above property, i want to specify only CS, and let cusotmer
>> >> decide
>> >> what to (example: what sort order) specify.
>> >>
>> >> Is it possible? How to do this? I hope this is a very natural
>> >> requirement,
>> >> but could find help in MS documents for this
>> >>
>> >> Thanks,
>> >> Venkat
>> >>
>> >>
>> >>
>>|||No comments about your understanding.
The question is:
Apart (in unicode evironment) from "localizations" given by the different
available collations (Latin, Japanese, etc), the sort rules are different
between CS, CI, AS, AI,or any combination of them you'll like to select and
the BIN option: anyway, you can make just one choice: so you will provide a
"binary" sort criteria or a case, accent and/or katana sensitivity (or
insensitivity) sorto criteria, depending on the collation you'll set for your
Server, database or column
Regards
Gilberto
"Venkat" wrote:
> Hi,
> I would like to restate what you said about "complimentary" properties like
> Latin, General etc.,
> As per my understanding these complimentary properties are useful for non
> unicode database.
> Beucase as per my guess these properties (Latin, General etc.,) specifies
> how the data can be stored: in which character set for non unicode database.
> Where as these complimnetary properties are of no use (but we still need to
> specify) for unicode database.
> You have any comments on my understanding?
> Regards,
> Venkat
> "Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> wrote in
> message news:BF5A2E30-5D28-4129-B025-487DB4F6949D@.microsoft.com...
> > Hi,
> > i wrote about issues in generic terms: having objects in different
> > collations COULD be an issue, but not necessarily IS. Surely some (very
> > little) overhead due to internal conversion is more probable in presence
> > of
> > more than one collation.
> >
> > More: the sort order is implicit in the collation you select: BIN (binary)
> > sort is different among CS_AS or CI_AS or CI_AI etc.
> > So the Properties Latin1, General are "complementary" with the CS, CI, AS,
> > AI, KS (etc.) properties: the sort order could be different among each
> > available combination.
> > The concept of collation involves not only sort rules but also character
> > set
> > consideration.
> > You cannot define o sort order apart from the selected collation.
> >
> >
> > "Venkat" wrote:
> >
> >> Hi,
> >> I would like to understand your statement:
> >> " collation applied to a single database involves problems regarding the
> >> collation of the other databases of the same instance (eg. master, msdb,
> >> etc)."
> >>
> >> What is the problem if my database is in diffrent collaiton than the
> >> master
> >> database?
> >> I am sure this is possible and there would be no issuees with this.
> >> There might be issues with tempdb, so you can specify collation when you
> >> are
> >> creating temp tables.
> >>
> >> Can you tell me what is the problems you are expecting if master database
> >> and user database is in diffrent collation?
> >>
> >> Other thing you said is: sorting, case sensitivity, accent sensitivity (I
> >> call them as collaiton properties) have to be specified together only?
> >> the reason you said is: all these properties together forms as sort
> >> rules?
> >>
> >> If so, then when the properties: Latin1, General going to be used and
> >> what
> >> purpose they are for? (asume that i have Unicode database)
> >>
> >> comments?
> >>
> >> Regards,
> >> Venkat
> >>
> >> "Gilberto Zampatti" <GilbertoZampatti@.discussions.microsoft.com> wrote in
> >> message news:7E4D942B-9CEE-46E8-805B-F64233738B04@.microsoft.com...
> >> > The collation determines the sort rules, so you cannot specify a
> >> > Collation
> >> > and apply different sort rules.
> >> > Consider two other important issues:
> >> > - a collation applied to a single database involves problems regarding
> >> > the
> >> > collation of the other databases of the same instance (eg. master,
> >> > msdb,
> >> > etc).
> >> > To resolve this issue is better apply a specific collation to the table
> >> > columns (very tedious but...).
> >> >
> >> > the Latin1_general_cs_as sorts upper case and lower case separately but
> >> > in
> >> > a
> >> > different way among Latin1_General_BIN;I don't remember exactly which
> >> > one,
> >> > but one sorts all the upper cases together separately among the lower
> >> > case,
> >> > the other sorts instead A with a, B with b and so on.
> >> >
> >> > Remeber furthermore tha the new collation does'nt convert the
> >> > collation
> >> > of
> >> > data already present in your tables.
> >> >
> >> >
> >> > "Venkat" wrote:
> >> >
> >> >> Hi,
> >> >> I want to specify case sensitivity for my database, but at the same
> >> >> time
> >> >> i
> >> >> want let customer decide the sort order.
> >> >> What i mean is: take the following collation property
> >> >> SQL_Latin1_general_cs_as
> >> >>
> >> >> in the above property, i want to specify only CS, and let cusotmer
> >> >> decide
> >> >> what to (example: what sort order) specify.
> >> >>
> >> >> Is it possible? How to do this? I hope this is a very natural
> >> >> requirement,
> >> >> but could find help in MS documents for this
> >> >>
> >> >> Thanks,
> >> >> Venkat
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>

Thursday, March 22, 2012

combined merge + custom conflict resolution

Saturday, February 25, 2012

Column Names cAsE SeNSitive? - DFT

Column Names cAsE SeNSitive? - DFT

Column Names cAsE SeNSitive? - DFT

Column Names cAsE SeNSitive? - DFT

Sunday, February 12, 2012

Collation-any practical difference?

Friday, February 10, 2012

Collation Properties

Collation Properties

Collation Properties

Collation problem

Blog Archive

About Me