Combining record sets from two tables

Friends,
Can anyone give me the best way to solve the following challenge.
I have two tables in my MS SQLserver database. Table A has this dataset:
code_id code operations_descr
1 M Mob/Demob
2 T Transit
3 O Operation
4 S Standby
5 W WOW
6 C Crew Change
7 B Breakdown
8 R Maintenance
Table B has this dataset:
code_id operations_duration
3 125
3 10
2 1304
I want a new recordset where the sum of the operations_duration from table B
grouped on code_id is combined with the right code_id in table A, like this:
code_id code operations_descr total_operations_duration
1 M Mob/Demob 0
2 T Transit 1304
3 O Operation 135
4 S Standby 0
5 W WOW 0
6 C Crew Change 0
7 B Breakdown 0
8 R Maintenance 0
Thank you for any assistance!
/Leif S
--
Systems AnalystI've assumed that the first table is called "Operations" and that the second
one is called "OperationsDuration". Here is the query:
Select O.Code_ID, O.Code, O.Operations_Descr,
Sum(OD.Total_Operations_Duration)
From Operations O, OperationsDuration OD
Where O.Code_ID = OD.Code_ID
Group By O.Code_ID, O.Codel O.Operations_Descr
Best regards,
Sami Samir
ITWorx
"Leif S" wrote:

> Friends,
> Can anyone give me the best way to solve the following challenge.
> I have two tables in my MS SQLserver database. Table A has this dataset:
> code_id code operations_descr
> 1 M Mob/Demob
> 2 T Transit
> 3 O Operation
> 4 S Standby
> 5 W WOW
> 6 C Crew Change
> 7 B Breakdown
> 8 R Maintenance
> Table B has this dataset:
> code_id operations_duration
> 3 125
> 3 10
> 2 1304
> I want a new recordset where the sum of the operations_duration from table
B
> grouped on code_id is combined with the right code_id in table A, like thi
s:
> code_id code operations_descr total_operations_duration
> 1 M Mob/Demob 0
> 2 T Transit 1304
> 3 O Operation 135
> 4 S Standby 0
> 5 W WOW 0
> 6 C Crew Change 0
> 7 B Breakdown 0
> 8 R Maintenance 0
> Thank you for any assistance!
> /Leif S
> --
> Systems Analyst|||Hi
Select O.Code_ID, O.Code, O.Operations_Descr,
Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
From [Table A] O
LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID
Group By O.Code_ID, O.Codel O.Operations_Descr
John
"Leif S" wrote:

> Friends,
> Can anyone give me the best way to solve the following challenge.
> I have two tables in my MS SQLserver database. Table A has this dataset:
> code_id code operations_descr
> 1 M Mob/Demob
> 2 T Transit
> 3 O Operation
> 4 S Standby
> 5 W WOW
> 6 C Crew Change
> 7 B Breakdown
> 8 R Maintenance
> Table B has this dataset:
> code_id operations_duration
> 3 125
> 3 10
> 2 1304
> I want a new recordset where the sum of the operations_duration from table
B
> grouped on code_id is combined with the right code_id in table A, like thi
s:
> code_id code operations_descr total_operations_duration
> 1 M Mob/Demob 0
> 2 T Transit 1304
> 3 O Operation 135
> 4 S Standby 0
> 5 W WOW 0
> 6 C Crew Change 0
> 7 B Breakdown 0
> 8 R Maintenance 0
> Thank you for any assistance!
> /Leif S
> --
> Systems Analyst|||Hi John,
The code you gave me works well. But it is more to it.
The data is collected from a survey vessel working in the North Sea and the
tables contains data from daily reports sent from the vessel. Table B stores
operations duration for each day where records from a given day share the
same report_id. When I take your code and add: "Where OD.report_id =
@.reportId" and supply report_id = 2, the result set exclude code lines from
table A that are not in table B like this:
code_id code operations_descr Total_Oper
ations_Duration
2 T Transit 1304
3 O Operation 135
Your advice is appreciated!
/Leif S.
--
Systems Analyst
"John Bell" wrote:
> Hi
> Select O.Code_ID, O.Code, O.Operations_Descr,
> Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
> From [Table A] O
> LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID
> Group By O.Code_ID, O.Codel O.Operations_Descr
> John
> "Leif S" wrote:
>|||See if this works...
SELECT code_id, code, operations_descr,
operations_duration = (SELECT SUM(total_operations_duration)
FROM TABLEB
WHERE code_id = TABLEA.code_id
GROUP BY code_id
)
FROM TABLEA
"Leif S" <LeifS@.discussions.microsoft.com> wrote in message
news:5E01F636-997E-47A6-A844-206B6DBBE0CA@.microsoft.com...
> Friends,
> Can anyone give me the best way to solve the following challenge.
> I have two tables in my MS SQLserver database. Table A has this dataset:
> code_id code operations_descr
> 1 M Mob/Demob
> 2 T Transit
> 3 O Operation
> 4 S Standby
> 5 W WOW
> 6 C Crew Change
> 7 B Breakdown
> 8 R Maintenance
> Table B has this dataset:
> code_id operations_duration
> 3 125
> 3 10
> 2 1304
> I want a new recordset where the sum of the operations_duration from table
> B
> grouped on code_id is combined with the right code_id in table A, like
> this:
> code_id code operations_descr total_operations_duration
> 1 M Mob/Demob 0
> 2 T Transit 1304
> 3 O Operation 135
> 4 S Standby 0
> 5 W WOW 0
> 6 C Crew Change 0
> 7 B Breakdown 0
> 8 R Maintenance 0
> Thank you for any assistance!
> /Leif S
> --
> Systems Analyst|||Hi
I am not sure where report_id comes from as it was not mentioned in the
original post. You may want to check out
http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
data.
Try:
Select O.Code_ID, O.Code, O.Operations_Descr,
Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
From [Table A] O
LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID AND OD.report_id = @.reporti
d
Group By O.Code_ID, O.Codel O.Operations_Descr
John
"Leif S" wrote:
> Hi John,
> The code you gave me works well. But it is more to it.
> The data is collected from a survey vessel working in the North Sea and th
e
> tables contains data from daily reports sent from the vessel. Table B stor
es
> operations duration for each day where records from a given day share the
> same report_id. When I take your code and add: "Where OD.report_id =
> @.reportId" and supply report_id = 2, the result set exclude code lines fro
m
> table A that are not in table B like this:
> code_id code operations_descr Total_Oper
ations_Duration
> 2 T Transit 1304
> 3 O Operation 135
> Your advice is appreciated!
> /Leif S.
> --
> Systems Analyst
>
> "John Bell" wrote:
>|||Thanks, John. So easy!
When I moved the report_id qualifyer away from the "Where" clause and into
the Join statement I got what I wanted.
/Leif S.
--
Systems Analyst
"John Bell" wrote:
> Hi
> I am not sure where report_id comes from as it was not mentioned in the
> original post. You may want to check out
> http://www.aspfaq.com/etiquette.asp?id=5006 on how to post DDL and example
> data.
> Try:
> Select O.Code_ID, O.Code, O.Operations_Descr,
> Sum(ISNULL(OD.Total_Operations_Duration,0)) as Total_Operations_Duration
> From [Table A] O
> LEFT JOIN [Table B] OD ON O.Code_ID = OD.Code_ID AND OD.report_id = @.repor
tid
> Group By O.Code_ID, O.Codel O.Operations_Descr
> John
> "Leif S" wrote:
>

Combination of English and Chinese characters in Microsoft SQLServer 2000

Hello,
We have Microsoft SQL Server 2000 with Service Pack 3a installed. In
one of our databases, we have a table REQ with one of the fields
COMMENT being defined as TEXT. Now, when we try to view the COMMENT
field, it shows us only English characters whereas the Chinese
characters are displayed as ?.
Is it possible to store both English and Chinese characters in the same
column of a table? If yes, then how?
Thanks in advance.
New user.
It still does not work. Is there any other alternative?
On Nov 19, 1:48 am, "Dejan Sarka"
<dejan_please_reply_to_newsgroups.sa...@.avtenta.si > wrote:
> --
> Dejan Sarkahttp://www.solidqualitylearning.com/blogs/
|||newsqlserveruser (trupti.pujara@.gmail.com) writes:
> We have Microsoft SQL Server 2000 with Service Pack 3a installed. In
> one of our databases, we have a table REQ with one of the fields
> COMMENT being defined as TEXT. Now, when we try to view the COMMENT
> field, it shows us only English characters whereas the Chinese
> characters are displayed as ?.
> Is it possible to store both English and Chinese characters in the same
> column of a table? If yes, then how?
Yes, it's possible, but with the information you have given, it's difficult
to give exact advice.
First of all, which character set do you use for Chinese? Unicode? Big-5?
If you use Unicode, you should use ntext, if you use Big-5 or any other
non-Unicode character set, you should use text.
Next question is find out where things go wrong. Do something like:
SELECT substring(col, 1, 20), convert(binary(20), substring(col, 1, 20))
If there are question marks in the selected substring which should have
been Chinese characters, what do you see in their place in the binary
string? If you see 3F, the Chinese characters have been stored as ?, and
there is a problem on input. If you see the codes for the Chinese
characters, it's a display problem.
If it is an input problem, it would help to know who the characters enters
the database.
Disclaimer: I have no experience of working with Chinese on my own.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I ran the query as u suggested and the output for the binary string
shows as 3F for the Chinese characters. If that means there is a
problem in input, how do I go ahead from here?
On Nov 21, 9:17 pm, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> newsqlserveruser (trupti.puj...@.gmail.com) writes:
> to give exact advice.
> First of all, which character set do you use for Chinese? Unicode? Big-5?
> If you use Unicode, you should use ntext, if you use Big-5 or any other
> non-Unicode character set, you should use text.
> Next question is find out where things go wrong. Do something like:
> SELECT substring(col, 1, 20), convert(binary(20), substring(col, 1, 20))
> If there are question marks in the selected substring which should have
> been Chinese characters, what do you see in their place in the binary
> string? If you see 3F, the Chinese characters have been stored as ?, and
> there is a problem on input. If you see the codes for the Chinese
> characters, it's a display problem.
> If it is an input problem, it would help to know who the characters enters
> the database.
> Disclaimer: I have no experience of working with Chinese on my own.
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||newsqlserveruser (trupti.pujara@.gmail.com) writes:
> I ran the query as u suggested and the output for the binary string
> shows as 3F for the Chinese characters. If that means there is a
> problem in input, how do I go ahead from here?
To start with, please answer a few questions:
1) How does the data enter the database? From a Windows client? From a
web client? Which API do you use?
2) Which character set does the client use for the data?
3) What is the collation of the text column? (You can view this with
sp_help.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thank you very much for all your help. The problem has been solved.
After changing the field from text to ntext in the database, the client
still had some problems. Even though East Asian languages were
supported, the Language for non-Unicode programs was English. When it
was changed to Chinese and the PC was rebooted, everything is all fine
now.
On Nov 26, 1:15 am, Erland Sommarskog <esq...@.sommarskog.se> wrote:
> newsqlserveruser (trupti.puj...@.gmail.com) writes:
> 1) How does the data enter the database? From a Windows client? From a
> web client? Which API do you use?
> 2) Which character set does the client use for the data?
> 3) What is the collation of the text column? (You can view this with
> sp_help.)
> --
> Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

COM access to SERVERPROPERTY values

Is it possible to get the properties retrievable with T-SQL
SERVERPROPERTY via COM access instead? Do you know some sqlserver COM
object that can provide this kind of information (e.g. LicenseType or
NumLicenses)? Any Ideas?You could use SQL DMO object model. See SQL Server Books Online for more
info. See SQLServer2 object for a start.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Holger" <atlan@.tournedos.de> wrote in message
news:1124040877.686226.191970@.g43g2000cwa.googlegroups.com...
> Is it possible to get the properties retrievable with T-SQL
> SERVERPROPERTY via COM access instead? Do you know some sqlserver COM
> object that can provide this kind of information (e.g. LicenseType or
> NumLicenses)? Any Ideas?
>|||Thank you for your reply but in COM programming it is always good to
read the manual first. The object SQLServer2 does not provide access to
the properties LicenseType or NumLicenses exposed by SERVERPROPERTY.
These properties are of special interest to me. Any further object you
can recommend?|||Another approach is simply the registry to get these values
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\M
icrosoft SQL
Server\80\MSSQLLicenseInfo\MSSQL8.00]
"ConcurrentLimit"=dword:00000006
"Mode"=dword:00000002
Mode = 2 means PerProcessor
Mode != 2 means PerSeat
ConcurrentLimit = Number of Seats OR Processors
Just to complete this topic,
Holger

Column, parameter, or variable #1: Cannot find data type SqlDatareader

Hello Everyone,

A have a Managed Stored Procedure ([Microsoft.SqlServer.SqlProcedure]). In it I would like to call a UserDefinedFunction:

public static SqlInt32 IsGetSqlInt32Null(SqlDataReader dr, Int32 index)
{
if(dr.GetSqlValue(index) == null)
return SqlInt32.Null;
else
return dr.GetSqlInt32(index)
}

I than allways get the following ErrorMessage:

Column, parameter, or variable #1: Cannot find data type SqlDatareader.

Is it not possibel to pass the SqlDatareader to a SqlFunction, do the reading there and return the result.

My original Problem is, that datareader.GetSqlInt32(3) throws an error in case there is Null in the DB. I thought SqlInt32 would allow Null.

Would appreciate any kind of help! Thanks

You may need to refer to the SqlDataReader with full qualified name if you haven't using the proper namespace:

System.Data.SqlClient.SqlDataReader

Column permissions

1. I denied some user the select permissions on some columns in a table.
However, this user is able to see these columns through the views (SQL
Server 2000). Is there a way to automatically deny the permissions on the
same columns in all the views or it's necessary to set the permissions in
every view?
2. If a column is unchecked in the permissions window, is this the same as
permission granted?
3. I've read somewhere that users should not have access to the tables, only
have access to the views. What is a reason for this recommendation?
Thanks.1) Because of ownership chaining, if the user can read the column in
the view, they can read the column in the table, despite the
permissions being denied.
2) If the column is unchecked, that is permissions revoked, if it is
checked that is permission granted and the red X is denied.
3) This recommendation is based on a few things. First, the problem you
are seeing is remedied by using only views. You don't have to manage
certain permissions twice if everything is in views. Additionally, this
creates an abstraction layer, where the table can be changed and as
long as you update the view, the user never knows about the change.
This also applies to applications. Additionally, direct table access
opens up holes that a DBA may forget to plug. For instance, adding a
new "confidential" column and not denying permission on it. If you
are using views, (not views that just select * from a table), then the
new column will not be available until you explicitly make it so.
Eric Johnson
Consortio Services
Vik wrote:
> 1. I denied some user the select permissions on some columns in a table.
> However, this user is able to see these columns through the views (SQL
> Server 2000). Is there a way to automatically deny the permissions on the
> same columns in all the views or it's necessary to set the permissions in
> every view?
> 2. If a column is unchecked in the permissions window, is this the same as
> permission granted?
> 3. I've read somewhere that users should not have access to the tables, on
ly
> have access to the views. What is a reason for this recommendation?
> Thanks.

COLUMN GRAND TOTALS ARE INCORRECT FOR CALCULATED FIELDS IN VIRTUAL CUBE

Hello,

We have migrated some cubes from sqlserver 2000 to 2005 with success. The applicaticon contains one virtual cube made up from 6 other cubes. On the virtual cube level we specified calculated fields. The calculation contains elements from different cubes which shares dimensions.

while browsing the cube from within sql server management studio or from excel using OLEDb 9.0 Provider we discovered that the column grand totals for these calculated fields are incorrect. The row grand totals are fine.

Searching the internet (google) i have found a hotfix describing the problem SQL MSAS 2005 (http://support.microsoft.com/kb/918251). We have installed this hotfix on top of SP1 but without results.

Is somebody familiar with this problem or knows how to solve this !!!!

Thanks in advance.

Dirk

When you say "totals are incorrect", can you please provide the expressions of the calculated members, the expected values and the actual values. I.e. if you expected to see NULLs, but get values - it could be because of IgnoreUnrelatedDimensions setting etc.|||

Hi Mosha,

I expect values but the results are not correct ( so as an example i expect 82.0415,00 and i get 80.0345,53)

regards,

Dirk

|||

> please provide the expressions of the calculated members

The investigation will likely to go into the values on which these calculations are based. Another reason for disrepancies could be RI problems in the data.

|||

Hi Mosha,

I will provide details for you asap. At the moment i can't log in into the remote customer sql server (machine is down). I will provide you with full details tomorrow (same time).

Regards,

Dirk

|||

Hi Mosha,

Here are the details you asked for:

Virtual Cube: Cube_Omspanning

Calculated Measures:

[KostenquotientOmzet]

'[Measures].[Kostenquotient_OMZ]/[Measures].[Teller_OMZ]'

[KostprijsOmzet]

'[Measures].[EenhedenOmzet]*[Measures].[KostenquotientOmzet]'

[KostprijsOmzet]

'[Measures].[EenhedenOmzet]*[Measures].[KostenquotientOmzet]'

[RendementOmzet]

'[Measures].[BedragOmzet]-[Measures].[KostprijsOmzet]'

[RendementOmzet]

'[Measures].[BedragOmzet]-[Measures].[KostprijsOmzet]'

Cube_Omzet_Kostenquotient

Calculated measures:

[Gemiddelde Kostenquotient] (Not Used in virtual cube calculated fields)

'[Measures].[Kostenquotient_OMZ]/[Measures].[Teller_OMZ]'

Measures:

Kostenquotient_OMZ

Teller_OMZ

Cube_Omzet

Calculated measures:

None

Measures:

EenhedenOmzet

BedragOmzet

BedragBasis

A couple of general questions:

1.) Are Grand Totals calculated during load of the cube ?

2.) Could this be a Rounding (to integer) problem?

Regards,

Dirk

|||Based on these formulas - I can tell, that the differences must exist for the real measures. Please start comparing the real measures in AS2000 vs. AS2005, and start slicing by different dimensions. As soon as you identify dimension which causes the difference, I recommend checking whether the attribute relationships are defined correctly in that dimension - this seems to be the most probable cause of the difference. The difference is way too big to be explained by rounding here.

Collation troubles with SQLServer Express 2005

Greetings,
I installed SQL Server Express 2005 in my system, and created a simple database to store mailng addresses. The database has an email field and a name field. The data to populate the database came from an Excel file saved as a Unicode Text file containing the data as:
xxx@.somemail.com; To?o Peres yyy@.someothermail.com; Iván Cárdenas Note that this data has names with accented characters.
I then tried to import the data into the SQLServer Express with the following command:
bulk insert lista
from 'c:\temp\todas.csv'
with (
DATAFILETYPE ='widechar',
FIELDTERMINATOR =';'
)
I got the following error message:
Bulk load: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.
And the data was imported as:
xxx@.somemail.com To±o Peres yyy@.someothermail.com Ivfn C?rdenas
I tried using the DATAFILETYPE ='native' and DATAFILETYPE ='widenative' options but these caused the following error:
Msg 4866, Level 16, State 7, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

I want to avoid re-installing the server and the Express 2005 edition does not come with the tools needed to rebuild the master database anyway (see this link).
When I type the data with the Server Management Studio Express program, I can insert the accented characters correctly.
Does anyone out there know how to deal with this problem? How do I tell SQL Server Express 2005 to insert the text from the text file and keep the accented characters as they were initially?
I will welcome any help.
Ivan.

Hi Ivan,

Are you sure you saved the spreadsheet as Unicode Text? The .csv filetype suggests you didn't - unless you manually changed it.

I tried using the data above in Excel, saving as Unicode Text and then bulk inserting and it worked fine.

Thanks

Collation setting

Hi
We have a sqlserver 2005 DB which is mainly used by sharepoint. We are
using "Latin1_General_CI_AS_KS_WS" collation for SharePoint Portal Server
2003 databases. When I have installed the server I have installed
"Latin1_General_CI_AS
" collation which is the default collation setting for canada.
So now we have different collation settings for the the system databases and
sharepoint databases. Which collation setting should I keep?
Thanks
--
ontario, canadaI would go with the default collation (sort id 52) that is
SQL_Latin1_General_CP1_CI_AS. (This depends on what your need)
However, in your current situation it appears that your databases are using
a different collation and the server is installed using a different
collation. you can do one of the following things:
1) Change the collation at the database level to match the collation of the
server.
2) Change the collation at the server level. (recommended)
Take a look at this tip on how to change the collation at the server level:
http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/21/Default.aspx
--
Thank you,
Saleem Hakani
HTTP://WWW.SQLCOMMUNITY.COM (World Wide SQL Server Community)
SQLTips, Scripts, Discussions, Blogs, Articles, Radio and a lot of SQL
Server Fun.
"db" wrote:
> Hi
> We have a sqlserver 2005 DB which is mainly used by sharepoint. We are
> using "Latin1_General_CI_AS_KS_WS" collation for SharePoint Portal Server
> 2003 databases. When I have installed the server I have installed
> "Latin1_General_CI_AS
> " collation which is the default collation setting for canada.
> So now we have different collation settings for the the system databases and
> sharepoint databases. Which collation setting should I keep?
> Thanks
> --
> ontario, canada|||On Oct 4, 5:40 pm, db <d...@.discussions.microsoft.com> wrote:
> Hi
> We have a sqlserver 2005 DB which is mainly used by sharepoint. We are
> using "Latin1_General_CI_AS_KS_WS" collation for SharePoint Portal Server
> 2003 databases. When I have installed the server I have installed
> "Latin1_General_CI_AS
> " collation which is the default collation setting for canada.
> So now we have different collation settings for the the system databases and
> sharepoint databases. Which collation setting should I keep?
>
Sharepoint's Collation.
If Sharepoint is the only application that server serves, then it
would be better
you rebuild the server with Sharepoint's collation. If the server
serves other
applications too, then you can maintain the server with multiple
collations,
but this is a risky business.
Regards,
Sasi.
> Thanks
> --
> ontario, canada|||Thanks saleem and sasiraj. What is the difference between
"Latin1_General_CI_AS_KS_WS" and "Latin1_General_CI_AS" collation.
ontario, canada
"sasiraj" wrote:
> On Oct 4, 5:40 pm, db <d...@.discussions.microsoft.com> wrote:
> > Hi
> > We have a sqlserver 2005 DB which is mainly used by sharepoint. We are
> > using "Latin1_General_CI_AS_KS_WS" collation for SharePoint Portal Server
> > 2003 databases. When I have installed the server I have installed
> > "Latin1_General_CI_AS
> > " collation which is the default collation setting for canada.
> >
> > So now we have different collation settings for the the system databases and
> > sharepoint databases. Which collation setting should I keep?
> >
> Sharepoint's Collation.
> If Sharepoint is the only application that server serves, then it
> would be better
> you rebuild the server with Sharepoint's collation. If the server
> serves other
> applications too, then you can maintain the server with multiple
> collations,
> but this is a risky business.
> Regards,
> Sasi.
> > Thanks
> > --
> > ontario, canada
>
>|||db wrote:
> Thanks saleem and sasiraj. What is the difference between
> "Latin1_General_CI_AS_KS_WS" and "Latin1_General_CI_AS" collation.
The difference is unimportant for most parts of the world (except
Asia). Latin1_General_CI_AS_KS_WS is Kana-sensitive and
Width-sensitive. The Latin1_General_CI_AS is much more common, because
it's the default in most installations.
"Kana-sensitive" means that it distinguishes between the two types of
Japanese kana characters: Hiragana and Katakana. If this option is not
selected, SQL Server considers Hiragana and Katakana characters to be
equal for sorting purposes.
"Width-sensitive" means that it distinguishes between a single-byte
character and the same character when represented as a double-byte
character. If this option is not selected, SQL Server considers the
single-byte and double-byte representation of the same character to be
identical for sorting purposes. In this context, double-byte characters
does not reffer to Unicode characters in general. It reffers to
Double-Byte Character Sets, such as Shift-JIS, GB2312, etc.
--
Razvan Socol
SQL Server MVP

Collation Problem

HI

I am facing a problem with SqlServer 2000,

create table test123 (
[Description] [varchar] (50) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NULL
)
go
insert into test123 ( [Description] ) values ('Prve')

The insert statement works fine from SQL query Analyzer.

However when I populate the table using osql, the collation is going wrong.. I am not getting the same string in the table.

Regards

\JoeTry and this and see if it works

create table test1234 (
[Description] [varchar] (50) COLLATE Latin1_General_CI_AS
)
go
insert into test1234 ( [Description] ) values ('Prve')

That should do it.

Tell me if it works?