collation or local sensitive settings or other configuration properties

I installed the samples and tutoriales on the same server as SQL Server 2005 and VS 2005 Team Suite.

Living in Belgium, the local settings are:

language = Duch (Belgium)

code page = 850

e.g. in the database samples (AdventureWorks database – AdventureWorks Warehouse database)

my date notation is: 26-12-2005

my number notation is: 1,0013016921998599

all demo samples and tutorials are developed using

language = English (United States)

code page 1252

e.g. in SampleCurrencyData.txt

the date notation is: 12/26/2005 00:00:00

the number notation is: 1.0013016921998599

I do not have any problems to test the diferent services (Data Base Engine Services, Analysis Services, reporting & notifications services ..) but I do not succeed to deploy any samples of integration services !

e.g. for Integration Services Tutorial - lesson 1: “Creating the Project and Basic Package”

there are no error messages, 1097 rows are processed

but, checking the result in the database, no data is updated in the FactCurrencyRate table of the AdventureWorksDW database !

Why?

- is the problem related to the local language settings? how to solve this?

- what is the influence of the code page ? is there any compatibility between 1252 and 850 as code page?

- Server collation (e.g. Latin_1_General_CI_AS) is reported as key for the Unicode notation for character strings but what about notation of numbers?

- when to use float data type DT_R4 or DT_R8?

- I have remarked that the DT_DBTimeStamp is undependent from the source time notation – Is this correct?

- what is the difference between DT_Date and DT_DBDate or DT_DBTime, or DT_DBTimeStamp?

- Is Integration Services dependant of the local settings of the database engine?

- how to set / modify additional regional properties in a SSIS and SSRS package?

- how to change the default setting of the Flat File Connection Manager [starting the wizard, the local setting for the language – Dutch appears and this is OK for me but as codepage appears 1252 (ANSI Latin) and this is not OK as my server code page = 850]?

- how to work with e.g. US based data as source and Belgium settings for reports?

I am also struggling with trying to get the SSIS tutorials to work because of the text files having the dates in MM/DD/YYYY format while my local setting expects DD/MM/YYYY (English - New Zealand)

What needs to be done so that the Integration Services tutorials work in countries other than the USA?

|||

OK, got it - the answer is simple...

In the data source definition of the flat file containing the currency data, specify the locale as English(USA).

I suspect there are many users who follow the tutorial instructions to the letter and the instructions do not mention the locale setting - OK if you are in the USA.

|||

I

I have the same issue

My

Locale: Russian(Russia)

Code Page: 1251 (ANSI - кириллица)

Changing it, as you wrote it, to

Locale: English(United States)

does not correct this problem but just invokes more problems!!!!!

I aslo tried
ETL Package Problem
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603488&SiteID=1

No luck either!

|||OK, I installed just one more instance with default English(US) language and

voilà .. everything started to work...
So, the conclusion is that in order to use SSIS I should migrate everything to a new instance with English(US)!? since default language/collation cannot be reset

I'd like to hear any better solution

|||

Another solution:

on http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

you find database for case-sensitive collation and case-insensitive collation, a attached the other database and now works.

|||

This link tells that samples download had moved to codeplex

And codeplex has hundreds of samples

Which one?

collation or local sensitive settings or other configuration properties

I installed the samples and tutoriales on the same server as SQL Server 2005 and VS 2005 Team Suite.

Living in Belgium, the local settings are:

language = Duch (Belgium)

code page = 850

e.g. in the database samples (AdventureWorks database – AdventureWorks Warehouse database)

my date notation is: 26-12-2005

my number notation is: 1,0013016921998599

all demo samples and tutorials are developed using

language = English (United States)

code page 1252

e.g. in SampleCurrencyData.txt

the date notation is: 12/26/2005 00:00:00

the number notation is: 1.0013016921998599

I do not have any problems to test the diferent services (Data Base Engine Services, Analysis Services, reporting & notifications services ..) but I do not succeed to deploy any samples of integration services !

e.g. for Integration Services Tutorial - lesson 1: “Creating the Project and Basic Package”

there are no error messages, 1097 rows are processed

but, checking the result in the database, no data is updated in the FactCurrencyRate table of the AdventureWorksDW database !

Why?

- is the problem related to the local language settings? how to solve this?

- what is the influence of the code page ? is there any compatibility between 1252 and 850 as code page?

- Server collation (e.g. Latin_1_General_CI_AS) is reported as key for the Unicode notation for character strings but what about notation of numbers?

- when to use float data type DT_R4 or DT_R8?

- I have remarked that the DT_DBTimeStamp is undependent from the source time notation – Is this correct?

- what is the difference between DT_Date and DT_DBDate or DT_DBTime, or DT_DBTimeStamp?

- Is Integration Services dependant of the local settings of the database engine?

- how to set / modify additional regional properties in a SSIS and SSRS package?

- how to change the default setting of the Flat File Connection Manager [starting the wizard, the local setting for the language – Dutch appears and this is OK for me but as codepage appears 1252 (ANSI Latin) and this is not OK as my server code page = 850]?

- how to work with e.g. US based data as source and Belgium settings for reports?

I am also struggling with trying to get the SSIS tutorials to work because of the text files having the dates in MM/DD/YYYY format while my local setting expects DD/MM/YYYY (English - New Zealand)

What needs to be done so that the Integration Services tutorials work in countries other than the USA?

|||

OK, got it - the answer is simple...

In the data source definition of the flat file containing the currency data, specify the locale as English(USA).

I suspect there are many users who follow the tutorial instructions to the letter and the instructions do not mention the locale setting - OK if you are in the USA.

|||

I

I have the same issue

My

Locale: Russian(Russia)

Code Page: 1251 (ANSI - кириллица)

Changing it, as you wrote it, to

Locale: English(United States)

does not correct this problem but just invokes more problems!!!!!

I aslo tried
ETL Package Problem
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603488&SiteID=1

No luck either!

|||OK, I installed just one more instance with default English(US) language and

voilà .. everything started to work...
So, the conclusion is that in order to use SSIS I should migrate everything to a new instance with English(US)!? since default language/collation cannot be reset

I'd like to hear any better solution

|||

Another solution:

on http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

you find database for case-sensitive collation and case-insensitive collation, a attached the other database and now works.

|||

This link tells that samples download had moved to codeplex

And codeplex has hundreds of samples

Which one?

collation or local sensitive settings or other configuration properties

I installed the samples and tutoriales on the same server as SQL Server 2005 and VS 2005 Team Suite.

Living in Belgium, the local settings are:

language = Duch (Belgium)

code page = 850

e.g. in the database samples (AdventureWorks database – AdventureWorks Warehouse database)

my date notation is: 26-12-2005

my number notation is: 1,0013016921998599

all demo samples and tutorials are developed using

language = English (United States)

code page 1252

e.g. in SampleCurrencyData.txt

the date notation is: 12/26/2005 00:00:00

the number notation is: 1.0013016921998599

I do not have any problems to test the diferent services (Data Base Engine Services, Analysis Services, reporting & notifications services ..) but I do not succeed to deploy any samples of integration services !

e.g. for Integration Services Tutorial - lesson 1: “Creating the Project and Basic Package”

there are no error messages, 1097 rows are processed

but, checking the result in the database, no data is updated in the FactCurrencyRate table of the AdventureWorksDW database !

Why?

- is the problem related to the local language settings? how to solve this?

- what is the influence of the code page ? is there any compatibility between 1252 and 850 as code page?

- Server collation (e.g. Latin_1_General_CI_AS) is reported as key for the Unicode notation for character strings but what about notation of numbers?

- when to use float data type DT_R4 or DT_R8?

- I have remarked that the DT_DBTimeStamp is undependent from the source time notation – Is this correct?

- what is the difference between DT_Date and DT_DBDate or DT_DBTime, or DT_DBTimeStamp?

- Is Integration Services dependant of the local settings of the database engine?

- how to set / modify additional regional properties in a SSIS and SSRS package?

- how to change the default setting of the Flat File Connection Manager [starting the wizard, the local setting for the language – Dutch appears and this is OK for me but as codepage appears 1252 (ANSI Latin) and this is not OK as my server code page = 850]?

- how to work with e.g. US based data as source and Belgium settings for reports?

I am also struggling with trying to get the SSIS tutorials to work because of the text files having the dates in MM/DD/YYYY format while my local setting expects DD/MM/YYYY (English - New Zealand)

What needs to be done so that the Integration Services tutorials work in countries other than the USA?

|||

OK, got it - the answer is simple...

In the data source definition of the flat file containing the currency data, specify the locale as English(USA).

I suspect there are many users who follow the tutorial instructions to the letter and the instructions do not mention the locale setting - OK if you are in the USA.

|||

I

I have the same issue

My

Locale: Russian(Russia)

Code Page: 1251 (ANSI - кириллица)

Changing it, as you wrote it, to

Locale: English(United States)

does not correct this problem but just invokes more problems!!!!!

I aslo tried
ETL Package Problem
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603488&SiteID=1

No luck either!

|||OK, I installed just one more instance with default English(US) language and

voilà .. everything started to work...
So, the conclusion is that in order to use SSIS I should migrate everything to a new instance with English(US)!? since default language/collation cannot be reset

I'd like to hear any better solution

|||

Another solution:

on http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

you find database for case-sensitive collation and case-insensitive collation, a attached the other database and now works.

|||

This link tells that samples download had moved to codeplex

And codeplex has hundreds of samples

Which one?

collation or local sensitive settings or other configuration properties

I installed the samples and tutoriales on the same server as SQL Server 2005 and VS 2005 Team Suite.

Living in Belgium, the local settings are:

language = Duch (Belgium)

code page = 850

e.g. in the database samples (AdventureWorks database – AdventureWorks Warehouse database)

my date notation is: 26-12-2005

my number notation is: 1,0013016921998599

all demo samples and tutorials are developed using

language = English (United States)

code page 1252

e.g. in SampleCurrencyData.txt

the date notation is: 12/26/2005 00:00:00

the number notation is: 1.0013016921998599

I do not have any problems to test the diferent services (Data Base Engine Services, Analysis Services, reporting & notifications services ..) but I do not succeed to deploy any samples of integration services !

e.g. for Integration Services Tutorial - lesson 1: “Creating the Project and Basic Package”

there are no error messages, 1097 rows are processed

but, checking the result in the database, no data is updated in the FactCurrencyRate table of the AdventureWorksDW database !

Why?

- is the problem related to the local language settings? how to solve this?

- what is the influence of the code page ? is there any compatibility between 1252 and 850 as code page?

- Server collation (e.g. Latin_1_General_CI_AS) is reported as key for the Unicode notation for character strings but what about notation of numbers?

- when to use float data type DT_R4 or DT_R8?

- I have remarked that the DT_DBTimeStamp is undependent from the source time notation – Is this correct?

- what is the difference between DT_Date and DT_DBDate or DT_DBTime, or DT_DBTimeStamp?

- Is Integration Services dependant of the local settings of the database engine?

- how to set / modify additional regional properties in a SSIS and SSRS package?

- how to change the default setting of the Flat File Connection Manager [starting the wizard, the local setting for the language – Dutch appears and this is OK for me but as codepage appears 1252 (ANSI Latin) and this is not OK as my server code page = 850]?

- how to work with e.g. US based data as source and Belgium settings for reports?

I am also struggling with trying to get the SSIS tutorials to work because of the text files having the dates in MM/DD/YYYY format while my local setting expects DD/MM/YYYY (English - New Zealand)

What needs to be done so that the Integration Services tutorials work in countries other than the USA?

|||

OK, got it - the answer is simple...

In the data source definition of the flat file containing the currency data, specify the locale as English(USA).

I suspect there are many users who follow the tutorial instructions to the letter and the instructions do not mention the locale setting - OK if you are in the USA.

|||

I

I have the same issue

My

Locale: Russian(Russia)

Code Page: 1251 (ANSI - кириллица)

Changing it, as you wrote it, to

Locale: English(United States)

does not correct this problem but just invokes more problems!!!!!

I aslo tried
ETL Package Problem
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=603488&SiteID=1

No luck either!

|||OK, I installed just one more instance with default English(US) language and

voilà .. everything started to work...
So, the conclusion is that in order to use SSIS I should migrate everything to a new instance with English(US)!? since default language/collation cannot be reset

I'd like to hear any better solution

|||

Another solution:

on http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

you find database for case-sensitive collation and case-insensitive collation, a attached the other database and now works.

|||

This link tells that samples download had moved to codeplex

And codeplex has hundreds of samples

Which one?

collation conflict resolution

I have a number of databases with quite similar structure, but with
different COLLATION
settings. Unfortunately when the structure (including keys and constraints)
has been created
no one took care of COLLATION at all.
At the moment statements like:
--
SELECT
. . .
(case FieldA
when 1 then (select Source1 from Table1 WHERE ...)
when 2 then (select Source2 from Table2 WHERE ...)
end) as Result,
--
gives COLLATION CONFLICT because Table1 and Table2 have different collation
setting.
But for a half of number of tables it works fine.
What aa I asking about: How to change COLLATION SETTING "on fly" for
different tables.fields ?
May be someone knows any fancy tools or "script wizards" to do so?
Please don't waste this newsgroup space with offering me to insert "COLLATE
DATABASE_DEFAULT"
to each statement which causes such problem. Ok?
Thanks a lot!
GnumHi,
CREATE VIEW FROM Table2
ADD COLLATE AFTER EACH FIELDS.
> when 1 then (select Source1 from Table1 WHERE ...)
(select Source1 from View1 WHERE ...)
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2004
"Gnum" <no-email@.mail.boo> wrote in message
news:OTaDHbgvDHA.1340@.TK2MSFTNGP09.phx.gbl...
> I have a number of databases with quite similar structure, but with
> different COLLATION
> settings. Unfortunately when the structure (including keys and
constraints)
> has been created
> no one took care of COLLATION at all.
> At the moment statements like:
> --
> SELECT
> . . .
> (case FieldA
> when 1 then (select Source1 from Table1 WHERE ...)
> when 2 then (select Source2 from Table2 WHERE ...)
> end) as Result,
> --
> gives COLLATION CONFLICT because Table1 and Table2 have different
collation
> setting.
> But for a half of number of tables it works fine.
> What aa I asking about: How to change COLLATION SETTING "on fly" for
> different tables.fields ?
> May be someone knows any fancy tools or "script wizards" to do so?
> Please don't waste this newsgroup space with offering me to insert
"COLLATE
> DATABASE_DEFAULT"
> to each statement which causes such problem. Ok?
>
> Thanks a lot!
> Gnum
>|||Thank you very much for so big
MISUNDERSTANDING!
Have you ever read the question up to the end?
I've written there "
> > Please don't waste this newsgroup space with offering me to insert
> "COLLATE
> > DATABASE_DEFAULT"
> > to each statement which causes such problem. Ok?"
I need a help, but not a collection of stupid wisdom-like erudition esseys!
Thanks!
"Shinichi Yoneda" <mxl04371@.nifty.ne.jp> wrote in message
news:OOcY6TlvDHA.2880@.tk2msftngp13.phx.gbl...
> Hi,
> CREATE VIEW FROM Table2
> ADD COLLATE AFTER EACH FIELDS.
> > when 1 then (select Source1 from Table1 WHERE ...)
> (select Source1 from View1 WHERE ...)
> --
> SHINICHI YONEDA MXL04371@.nifty.ne.jp
> Microsoft Most Valuable Professional
> MVP for SQL Server 2002-2004
> "Gnum" <no-email@.mail.boo> wrote in message
> news:OTaDHbgvDHA.1340@.TK2MSFTNGP09.phx.gbl...
> > I have a number of databases with quite similar structure, but with
> > different COLLATION
> > settings. Unfortunately when the structure (including keys and
> constraints)
> > has been created
> > no one took care of COLLATION at all.
> > At the moment statements like:
> > --
> > SELECT
> > . . .
> > (case FieldA
> > when 1 then (select Source1 from Table1 WHERE ...)
> > when 2 then (select Source2 from Table2 WHERE ...)
> > end) as Result,
> > --
> > gives COLLATION CONFLICT because Table1 and Table2 have different
> collation
> > setting.
> > But for a half of number of tables it works fine.
> >
> > What aa I asking about: How to change COLLATION SETTING "on fly" for
> > different tables.fields ?
> > May be someone knows any fancy tools or "script wizards" to do so?
> >
> > Please don't waste this newsgroup space with offering me to insert
> "COLLATE
> > DATABASE_DEFAULT"
> > to each statement which causes such problem. Ok?
> >
> >
> > Thanks a lot!
> >
> > Gnum
> >
> >
>

collation & compatibility issues

1) how to check the colllation settings on SQL 7 ?
2) I have 2 SQL Srv - SQL7 and SQL2000.
A client application does the following:
- retrieves data from SQL2000 to the SQL7 for queries.
- data updated in SQL7 is also periodically syn with the SQL2000 srv.
there is no direct user acess with the tables, all is interfaced thru the
client app. So must the SQL Server 2000 compatibility settings (under server
properties) be changed to 7.0 ?
Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
SQL2000 from being accessed by other app.
The WAIT TYPE is usually networkIO for the blocking connection from SQL7 to
SQL2000.
So I suspect could it be collation settings or some other in-compatibility
between the 2 SQL Servers.
>
> 1) how to check the collation settings on SQL 7 ?
The error log will tell you this information.

> 2) I have 2 SQL Srv - SQL7 and SQL2000.
> A client application does the following:
> - retrieves data from SQL2000 to the SQL7 for queries.
> - data updated in SQL7 is also periodically syn with the SQL2000 srv.
> there is no direct user acess with the tables, all is interfaced thru the
> client app. So must the SQL Server 2000 compatibility settings (under
server
> properties) be changed to 7.0 ?
> Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
> SQL2000 from being accessed by other app.
> The WAIT TYPE is usually networkIO for the blocking connection from SQL7
to
> SQL2000.
> So I suspect could it be collation settings or some other in-compatibility
> between the 2 SQL Servers.
You should run a blocker script to identify the top of blocking chains. The
script from this article might help:
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
Eric Crdenas
SQL Server senior support professional

collation & compatibility issues

1) how to check the colllation settings on SQL 7 ?
2) I have 2 SQL Srv - SQL7 and SQL2000.
A client application does the following:
- retrieves data from SQL2000 to the SQL7 for queries.
- data updated in SQL7 is also periodically syn with the SQL2000 srv.
there is no direct user acess with the tables, all is interfaced thru the
client app. So must the SQL Server 2000 compatibility settings (under server
properties) be changed to 7.0 ?
Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
SQL2000 from being accessed by other app.
The WAIT TYPE is usually networkIO for the blocking connection from SQL7 to
SQL2000.
So I suspect could it be collation settings or some other in-compatibility
between the 2 SQL Servers.>
> 1) how to check the collation settings on SQL 7 ?
--
The error log will tell you this information.

> 2) I have 2 SQL Srv - SQL7 and SQL2000.
> A client application does the following:
> - retrieves data from SQL2000 to the SQL7 for queries.
> - data updated in SQL7 is also periodically syn with the SQL2000 srv.
> there is no direct user acess with the tables, all is interfaced thru the
> client app. So must the SQL Server 2000 compatibility settings (under
server
> properties) be changed to 7.0 ?
> Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
> SQL2000 from being accessed by other app.
> The WAIT TYPE is usually networkIO for the blocking connection from SQL7
to
> SQL2000.
> So I suspect could it be collation settings or some other in-compatibility
> between the 2 SQL Servers.
--
You should run a blocker script to identify the top of blocking chains. The
script from this article might help:
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
Eric Crdenas
SQL Server senior support professional

collation & compatibility issues

1) how to check the colllation settings on SQL 7 ?
2) I have 2 SQL Srv - SQL7 and SQL2000.
A client application does the following:
- retrieves data from SQL2000 to the SQL7 for queries.
- data updated in SQL7 is also periodically syn with the SQL2000 srv.
there is no direct user acess with the tables, all is interfaced thru the
client app. So must the SQL Server 2000 compatibility settings (under server
properties) be changed to 7.0 ?
Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
SQL2000 from being accessed by other app.
The WAIT TYPE is usually networkIO for the blocking connection from SQL7 to
SQL2000.
So I suspect could it be collation settings or some other in-compatibility
between the 2 SQL Servers.>
> 1) how to check the collation settings on SQL 7 ?
--
The error log will tell you this information.
> 2) I have 2 SQL Srv - SQL7 and SQL2000.
> A client application does the following:
> - retrieves data from SQL2000 to the SQL7 for queries.
> - data updated in SQL7 is also periodically syn with the SQL2000 srv.
> there is no direct user acess with the tables, all is interfaced thru the
> client app. So must the SQL Server 2000 compatibility settings (under
server
> properties) be changed to 7.0 ?
> Problem is there is frequent blocking on SQL2000 by SQL7, preventing the
> SQL2000 from being accessed by other app.
> The WAIT TYPE is usually networkIO for the blocking connection from SQL7
to
> SQL2000.
> So I suspect could it be collation settings or some other in-compatibility
> between the 2 SQL Servers.
--
You should run a blocker script to identify the top of blocking chains. The
script from this article might help:
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Regards,
--
Eric Cárdenas
SQL Server senior support professional