Convert DB Collation Sort Type in SQL 2005 for MS Dynamics GP

Hi,
we have ms dynamics gp with SQL 2005, our database collation type is
Arabic_Bin
we want to change the collation to
Accent-sensitive sort type
is there any way to convert the database collation sort type without
damaging the data (specially the arabic language fields)
thanks
Hasan Afandi
--
Hasan AfandiHi
It's not easy task. I'd script out the structure with desired COLLATION and
then move the data into
"HAfandi" <HAfandi@.community.nospam> wrote in message
news:FABBDD23-F035-4AC6-B674-C790BE6D3D0F@.microsoft.com...
> Hi,
> we have ms dynamics gp with SQL 2005, our database collation type is
> Arabic_Bin
> we want to change the collation to
> Accent-sensitive sort type
> is there any way to convert the database collation sort type without
> damaging the data (specially the arabic language fields)
> thanks
>
> Hasan Afandi
> --
> Hasan Afandi|||can you please explaine more, i'm not pro in these things. how will i script
out the structure?
if it will help, i can build a new sql server with desired structure, but
then how will i transfer the compant data from the old server to new one?
thanks for reply
--
Hasan Afandi
"Uri Dimant" wrote:
> Hi
> It's not easy task. I'd script out the structure with desired COLLATION and
> then move the data into|||Hi
You can SMO or DMO object library within VB project for scripting.
http://www.karaszi.com/SQLServer/info_generate_script.asp
"HAfandi" <HAfandi@.community.nospam> wrote in message
news:C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com...
> can you please explaine more, i'm not pro in these things. how will i
> script
> out the structure?
> if it will help, i can build a new sql server with desired structure, but
> then how will i transfer the compant data from the old server to new one?
> thanks for reply
> --
> Hasan Afandi
>
> "Uri Dimant" wrote:
>> Hi
>> It's not easy task. I'd script out the structure with desired COLLATION
>> and
>> then move the data into
>|||what i understand from the url you gave me, is it provide a ways for creating
data structure. but my problem is nor implementing a different database
structure (i can use another sql server to build my preferred structure) but
what about the data itself? how can i transfer it to the new db without
getting any problems
for the vb, unfortunately i am not programming expert
--
Hasan Afandi
"Uri Dimant" wrote:
> Hi
> You can SMO or DMO object library within VB project for scripting.
> http://www.karaszi.com/SQLServer/info_generate_script.asp
>
> "HAfandi" <HAfandi@.community.nospam> wrote in message
> news:C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com...
> > can you please explaine more, i'm not pro in these things. how will i
> > script
> > out the structure?
> > if it will help, i can build a new sql server with desired structure, but
> > then how will i transfer the compant data from the old server to new one?
> >
> > thanks for reply
> > --
> > Hasan Afandi
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Hi
> >> It's not easy task. I'd script out the structure with desired COLLATION
> >> and
> >> then move the data into
> >
>
>|||You can move the data by using DTS, INSERT INTO.. commands
For examle
USE dbname1
INSERT INTO dbo.tbl1 SELECT * FROM dbname2.dbo.tbl2
"HAfandi" <HAfandi@.community.nospam> wrote in message
news:97908070-F3CC-432F-BD8D-AA2D27A6E8F8@.microsoft.com...
> what i understand from the url you gave me, is it provide a ways for
> creating
> data structure. but my problem is nor implementing a different database
> structure (i can use another sql server to build my preferred structure)
> but
> what about the data itself? how can i transfer it to the new db without
> getting any problems
> for the vb, unfortunately i am not programming expert
> --
> Hasan Afandi
>
> "Uri Dimant" wrote:
>> Hi
>> You can SMO or DMO object library within VB project for scripting.
>> http://www.karaszi.com/SQLServer/info_generate_script.asp
>>
>> "HAfandi" <HAfandi@.community.nospam> wrote in message
>> news:C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com...
>> > can you please explaine more, i'm not pro in these things. how will i
>> > script
>> > out the structure?
>> > if it will help, i can build a new sql server with desired structure,
>> > but
>> > then how will i transfer the compant data from the old server to new
>> > one?
>> >
>> > thanks for reply
>> > --
>> > Hasan Afandi
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Hi
>> >> It's not easy task. I'd script out the structure with desired
>> >> COLLATION
>> >> and
>> >> then move the data into
>> >
>>|||Dear Hasan,
Thank you for posting here.
Regarding your question, you could use the COLLATE clause on an ALTER
DATABASE to change the default collation of the database, but there are
some limitations to this as well and really should only be used if the text
data columns are Unicode types, because changing the collation of any text,
varchar and char fields that have no explicit column collation will alter
the way the data's encoding is interpreted, resulting in a form of
corruption of any characters that are out of the ASCII range. Usually, it's
not recommended to change database collation directly.
For the security reason, it is recommended that you firsts BACKUP the
Database and then change the default collation of the database via the
following SQL command:
ALTER DATABASE <Database> COLLATE <COLLATION>
To change the collation on the specific column, please run the following
command:
Alter table SysTotalizedValue
Alter column vVar <Data_type> COLLATE <COLLATION>
Alternatively, we can also attempt to use DTS/SSIS to change collation of
the database (as suggested by Urid). For the detail steps, please refer to
the following KB article:
How to transfer a database from one collation to another collation in SQL
Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;325335
NOTE: This KB article also applies to SQL Sever 2005
Additional Reference
--
SQL Server 2005 BOL: Copying Data Between Different Collations
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/c705468f-265b-450f-acc9-56ec
e5866238.htm
I hope this information is helpful. If you have further questions on the
issue, please feel free to post back.
Have a nice day!
Best regards,
Adams Qu, MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Thread-Topic: Convert DB Collation Sort Type in SQL 2005 for MS Dynamics
GP
| thread-index: Acgl/XtWefUyIcx1SM+2tXDZEF5z+g==| X-WBNR-Posting-Host: 207.46.19.197
| From: =?Utf-8?B?SEFmYW5kaQ==?= <HAfandi@.community.nospam>
| References: <FABBDD23-F035-4AC6-B674-C790BE6D3D0F@.microsoft.com>
<ev4NfteJIHA.1164@.TK2MSFTNGP02.phx.gbl>
<C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com>
<##N1b3eJIHA.484@.TK2MSFTNGP06.phx.gbl>
| Subject: Re: Convert DB Collation Sort Type in SQL 2005 for MS Dynamics GP
| Date: Tue, 13 Nov 2007 06:00:00 -0800
| Lines: 42
| Message-ID: <97908070-F3CC-432F-BD8D-AA2D27A6E8F8@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:30339
| NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| what i understand from the url you gave me, is it provide a ways for
creating
| data structure. but my problem is nor implementing a different database
| structure (i can use another sql server to build my preferred structure)
but
| what about the data itself? how can i transfer it to the new db without
| getting any problems
|
| for the vb, unfortunately i am not programming expert
|
| --
| Hasan Afandi
|
|
| "Uri Dimant" wrote:
|
| > Hi
| > You can SMO or DMO object library within VB project for scripting.
| > http://www.karaszi.com/SQLServer/info_generate_script.asp
| >
| >
| > "HAfandi" <HAfandi@.community.nospam> wrote in message
| > news:C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com...
| > > can you please explaine more, i'm not pro in these things. how will i
| > > script
| > > out the structure?
| > > if it will help, i can build a new sql server with desired structure,
but
| > > then how will i transfer the compant data from the old server to new
one?
| > >
| > > thanks for reply
| > > --
| > > Hasan Afandi
| > >
| > >
| > > "Uri Dimant" wrote:
| > >
| > >> Hi
| > >> It's not easy task. I'd script out the structure with desired
COLLATION
| > >> and
| > >> then move the data into
| > >
| >
| >
| >
||||Thanks for reply,
I am trying to follow your steps but ..
â?¢For generating the script, I right click the db >> select Tasks >> select
Generate Scripts
â?¢Following the wizard, I couldn't find where to enable the "Use Collation"
option
â?¢Also couldnâ't find the Only script 7.0 compatible features to enable it
â?¢After finishing the wizard, it gave me script a script to create a new db
with my tables selections
â?¢Now for using the DTS to transfer the data ..
â?¢My db has about 900 table. And it will be very difficult to use the
command "insert into .." for each table separately
â?¢For the script itself, is it necessary? What I mean, it's Dynamics GP
company db, and it is very easy for me to create the db using the GP utility
execution from my dynamics GP program
â?¢Just a thought, can I just use the import/export utility from SQL 2005?
So I transfer the data from tables in old db to tables in new db? Or this
will not solve my collation problem?
Forgave me for noobe, but I need this and donâ't know what to do.
Thanks
--
Hasan Afandi
"Adams Qu [MSFT]" wrote:
> Dear Hasan,
> Thank you for posting here.
> Regarding your question, you could use the COLLATE clause on an ALTER
> DATABASE to change the default collation of the database, but there are
> some limitations to this as well and really should only be used if the text
> data columns are Unicode types, because changing the collation of any text,
> varchar and char fields that have no explicit column collation will alter
> the way the data's encoding is interpreted, resulting in a form of
> corruption of any characters that are out of the ASCII range. Usually, it's
> not recommended to change database collation directly.
> For the security reason, it is recommended that you firsts BACKUP the
> Database and then change the default collation of the database via the
> following SQL command:
> ALTER DATABASE <Database> COLLATE <COLLATION>
> To change the collation on the specific column, please run the following
> command:
> Alter table SysTotalizedValue
> Alter column vVar <Data_type> COLLATE <COLLATION>
> Alternatively, we can also attempt to use DTS/SSIS to change collation of
> the database (as suggested by Urid). For the detail steps, please refer to
> the following KB article:
> How to transfer a database from one collation to another collation in SQL
> Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;325335
> NOTE: This KB article also applies to SQL Sever 2005
> Additional Reference
> --
> SQL Server 2005 BOL: Copying Data Between Different Collations
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/c705468f-265b-450f-acc9-56ec
> e5866238.htm
> I hope this information is helpful. If you have further questions on the
> issue, please feel free to post back.
> Have a nice day!
> Best regards,
> Adams Qu, MCSE, MCDBA, MCTS
> Microsoft Online Support
> Microsoft Global Technical Support Center
> Get Secure! - www.microsoft.com/security
> =====================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> | Thread-Topic: Convert DB Collation Sort Type in SQL 2005 for MS Dynamics
> GP
> | thread-index: Acgl/XtWefUyIcx1SM+2tXDZEF5z+g==> | X-WBNR-Posting-Host: 207.46.19.197
> | From: =?Utf-8?B?SEFmYW5kaQ==?= <HAfandi@.community.nospam>
> | References: <FABBDD23-F035-4AC6-B674-C790BE6D3D0F@.microsoft.com>
> <ev4NfteJIHA.1164@.TK2MSFTNGP02.phx.gbl>
> <C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com>
> <##N1b3eJIHA.484@.TK2MSFTNGP06.phx.gbl>
> | Subject: Re: Convert DB Collation Sort Type in SQL 2005 for MS Dynamics GP
> | Date: Tue, 13 Nov 2007 06:00:00 -0800
> | Lines: 42
> | Message-ID: <97908070-F3CC-432F-BD8D-AA2D27A6E8F8@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
> | Newsgroups: microsoft.public.sqlserver.server
> | Path: TK2MSFTNGHUB02.phx.gbl
> | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:30339
> | NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | what i understand from the url you gave me, is it provide a ways for
> creating
> | data structure. but my problem is nor implementing a different database
> | structure (i can use another sql server to build my preferred structure)
> but
> | what about the data itself? how can i transfer it to the new db without
> | getting any problems
> |
> | for the vb, unfortunately i am not programming expert
> |
> | --
> | Hasan Afandi
> |
> |
> | "Uri Dimant" wrote:
> |
> | > Hi
> | > You can SMO or DMO object library within VB project for scripting.
> | > http://www.karaszi.com/SQLServer/info_generate_script.asp
> | >
> | >
> | > "HAfandi" <HAfandi@.community.nospam> wrote in message
> | > news:C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com...
> | > > can you please explaine more, i'm not pro in these things. how will i
> | > > script
> | > > out the structure?
> | > > if it will help, i can build a new sql server with desired structure,
> but
> | > > then how will i transfer the compant data from the old server to new
> one?
> | > >
> | > > thanks for reply
> | > > --
> | > > Hasan Afandi
> | > >
> | > >
> | > > "Uri Dimant" wrote:
> | > >
> | > >> Hi
> | > >> It's not easy task. I'd script out the structure with desired
> COLLATION
> | > >> and
> | > >> then move the data into
> | > >
> | >
> | >
> | >
> |
>|||Dear Hasan,
Thank you for posting back.
In SQL Server 2005, we can also attempt to use the SSIS Import/Export
wizard to transfer a database from one collation to a different collation.
By default, the data will be translated to the destination collation
(unless we manually specify the COLLATE clause by "Edit SQL.." in the
Wizard).
For your information, in SQL Server SSIS (Integration Service), "Use
Collation" option is available in the "Transfer SQL Server Objects Task" ->
"Objects" option. You can refer to the SQL Server 2005 book online to get
more information. NOTE: "Use Collation" option described in KB 325335 is
applied to SQL Server 2000 DTS.
At this stage, we can try take the following method to help you covert the
collation:
NOTE: For the security reason, it is recommended that we first perform
above steps in a Test Server.
1. Back up the source database and ensure the backup file is gooda.
2. Create a new database and specify the appropriate collation. "CREATE
DATABASE <DATABASE_name> COLLATE < COLLATION NAME> "
3. Right click on the destination database ->Tasks ->Import Data.
4. Follow the wizard to import all the data into the destination database.
After that, we can manually verify some of the objects' collation by
running the "sp_help <table_name>" in the destination database.
Please don't hesitate to let me know if anything is unclear in my post.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
--
| Thread-Topic: Convert DB Collation Sort Type in SQL 2005 for MS Dynamics
GP
| thread-index: AcgmwwPgEpouNC3gS1ysTeb+oEaHVg==| X-WBNR-Posting-Host: 207.46.19.197
| From: =?Utf-8?B?SEFmYW5kaQ==?= <HAfandi@.community.nospam>
| References: <FABBDD23-F035-4AC6-B674-C790BE6D3D0F@.microsoft.com>
<ev4NfteJIHA.1164@.TK2MSFTNGP02.phx.gbl>
<C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com>
<##N1b3eJIHA.484@.TK2MSFTNGP06.phx.gbl>
<97908070-F3CC-432F-BD8D-AA2D27A6E8F8@.microsoft.com>
<KPEmvPoJIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
| Subject: Re: Convert DB Collation Sort Type in SQL 2005 for MS Dynamics GP
| Date: Wed, 14 Nov 2007 05:34:00 -0800
| Lines: 171
| Message-ID: <B46EB3F2-6147-42E0-B734-B2D568EDE5B4@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:30502
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Thanks for reply,
|
| I am trying to follow your steps but ..
|
| â?¢For generating the script, I right click the db >> select Tasks >>
select
| Generate Scripts
| â?¢Following the wizard, I couldn't find where to enable the "Use
Collation"
| option
| â?¢Also couldnâ't find the Only script 7.0 compatible features to
enable it
| â?¢After finishing the wizard, it gave me script a script to create a
new db
| with my tables selections
| â?¢Now for using the DTS to transfer the data ..
|
| â?¢My db has about 900 table. And it will be very difficult to use the
| command "insert into .." for each table separately
| â?¢For the script itself, is it necessary? What I mean, it's Dynamics
GP
| company db, and it is very easy for me to create the db using the GP
utility
| execution from my dynamics GP program
|
| â?¢Just a thought, can I just use the import/export utility from SQL
2005?
| So I transfer the data from tables in old db to tables in new db? Or this
| will not solve my collation problem?
|
| Forgave me for noobe, but I need this and donâ't know what to do.
|
| Thanks
| --
| Hasan Afandi
|
|
| "Adams Qu [MSFT]" wrote:
|
| > Dear Hasan,
| >
| > Thank you for posting here.
| >
| > Regarding your question, you could use the COLLATE clause on an ALTER
| > DATABASE to change the default collation of the database, but there are
| > some limitations to this as well and really should only be used if the
text
| > data columns are Unicode types, because changing the collation of any
text,
| > varchar and char fields that have no explicit column collation will
alter
| > the way the data's encoding is interpreted, resulting in a form of
| > corruption of any characters that are out of the ASCII range. Usually,
it's
| > not recommended to change database collation directly.
| >
| > For the security reason, it is recommended that you firsts BACKUP the
| > Database and then change the default collation of the database via the
| > following SQL command:
| >
| > ALTER DATABASE <Database> COLLATE <COLLATION>
| >
| > To change the collation on the specific column, please run the
following
| > command:
| >
| > Alter table SysTotalizedValue
| > Alter column vVar <Data_type> COLLATE <COLLATION>
| >
| > Alternatively, we can also attempt to use DTS/SSIS to change collation
of
| > the database (as suggested by Urid). For the detail steps, please refer
to
| > the following KB article:
| >
| > How to transfer a database from one collation to another collation in
SQL
| > Server
| > http://support.microsoft.com/default.aspx?scid=kb;en-us;325335
| >
| > NOTE: This KB article also applies to SQL Sever 2005
| >
| > Additional Reference
| > --
| > SQL Server 2005 BOL: Copying Data Between Different Collations
| >
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/c705468f-265b-450f-acc9-56ec
| > e5866238.htm
| >
| > I hope this information is helpful. If you have further questions on
the
| > issue, please feel free to post back.
| >
| > Have a nice day!
| >
| > Best regards,
| >
| > Adams Qu, MCSE, MCDBA, MCTS
| > Microsoft Online Support
| >
| > Microsoft Global Technical Support Center
| >
| > Get Secure! - www.microsoft.com/security
| > =====================================================| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| > =====================================================| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| > --
| > | Thread-Topic: Convert DB Collation Sort Type in SQL 2005 for MS
Dynamics
| > GP
| > | thread-index: Acgl/XtWefUyIcx1SM+2tXDZEF5z+g==| > | X-WBNR-Posting-Host: 207.46.19.197
| > | From: =?Utf-8?B?SEFmYW5kaQ==?= <HAfandi@.community.nospam>
| > | References: <FABBDD23-F035-4AC6-B674-C790BE6D3D0F@.microsoft.com>
| > <ev4NfteJIHA.1164@.TK2MSFTNGP02.phx.gbl>
| > <C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com>
| > <##N1b3eJIHA.484@.TK2MSFTNGP06.phx.gbl>
| > | Subject: Re: Convert DB Collation Sort Type in SQL 2005 for MS
Dynamics GP
| > | Date: Tue, 13 Nov 2007 06:00:00 -0800
| > | Lines: 42
| > | Message-ID: <97908070-F3CC-432F-BD8D-AA2D27A6E8F8@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| > | Newsgroups: microsoft.public.sqlserver.server
| > | Path: TK2MSFTNGHUB02.phx.gbl
| > | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:30339
| > | NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | what i understand from the url you gave me, is it provide a ways for
| > creating
| > | data structure. but my problem is nor implementing a different
database
| > | structure (i can use another sql server to build my preferred
structure)
| > but
| > | what about the data itself? how can i transfer it to the new db
without
| > | getting any problems
| > |
| > | for the vb, unfortunately i am not programming expert
| > |
| > | --
| > | Hasan Afandi
| > |
| > |
| > | "Uri Dimant" wrote:
| > |
| > | > Hi
| > | > You can SMO or DMO object library within VB project for scripting.
| > | > http://www.karaszi.com/SQLServer/info_generate_script.asp
| > | >
| > | >
| > | > "HAfandi" <HAfandi@.community.nospam> wrote in message
| > | > news:C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com...
| > | > > can you please explaine more, i'm not pro in these things. how
will i
| > | > > script
| > | > > out the structure?
| > | > > if it will help, i can build a new sql server with desired
structure,
| > but
| > | > > then how will i transfer the compant data from the old server to
new
| > one?
| > | > >
| > | > > thanks for reply
| > | > > --
| > | > > Hasan Afandi
| > | > >
| > | > >
| > | > > "Uri Dimant" wrote:
| > | > >
| > | > >> Hi
| > | > >> It's not easy task. I'd script out the structure with desired
| > COLLATION
| > | > >> and
| > | > >> then move the data into
| > | > >
| > | >
| > | >
| > | >
| > |
| >
| >
||||Dear Hasan,
How's everything going?
I'm wondering if the suggestion has helped or if you have any further
questions. Please feel free to respond to the newsgroups if you need any
additional help.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 118659455
| References: <FABBDD23-F035-4AC6-B674-C790BE6D3D0F@.microsoft.com>
<ev4NfteJIHA.1164@.TK2MSFTNGP02.phx.gbl>
<C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com>
<##N1b3eJIHA.484@.TK2MSFTNGP06.phx.gbl>
<97908070-F3CC-432F-BD8D-AA2D27A6E8F8@.microsoft.com>
<KPEmvPoJIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
<B46EB3F2-6147-42E0-B734-B2D568EDE5B4@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Thu, 15 Nov 2007 10:28:39 GMT
| Subject: Re: Convert DB Collation Sort Type in SQL 2005 for MS Dynamics GP
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <SsLLMJ3JIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 247
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:30644
| NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
|
| Dear Hasan,
|
| Thank you for posting back.
|
| In SQL Server 2005, we can also attempt to use the SSIS Import/Export
| wizard to transfer a database from one collation to a different
collation.
| By default, the data will be translated to the destination collation
| (unless we manually specify the COLLATE clause by "Edit SQL.." in the
| Wizard).
|
| For your information, in SQL Server SSIS (Integration Service), "Use
| Collation" option is available in the "Transfer SQL Server Objects Task"
->
| "Objects" option. You can refer to the SQL Server 2005 book online to get
| more information. NOTE: "Use Collation" option described in KB 325335 is
| applied to SQL Server 2000 DTS.
|
| At this stage, we can try take the following method to help you covert
the
| collation:
|
| NOTE: For the security reason, it is recommended that we first perform
| above steps in a Test Server.
|
| 1. Back up the source database and ensure the backup file is gooda.
| 2. Create a new database and specify the appropriate collation. "CREATE
| DATABASE <DATABASE_name> COLLATE < COLLATION NAME> "
| 3. Right click on the destination database ->Tasks ->Import Data.
| 4. Follow the wizard to import all the data into the destination database.
|
| After that, we can manually verify some of the objects' collation by
| running the "sp_help <table_name>" in the destination database.
|
| Please don't hesitate to let me know if anything is unclear in my post.
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu
| MCSE, MCDBA, MCTS
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| =====================================================| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| =====================================================| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
| --
| | Thread-Topic: Convert DB Collation Sort Type in SQL 2005 for MS
Dynamics
| GP
| | thread-index: AcgmwwPgEpouNC3gS1ysTeb+oEaHVg==| | X-WBNR-Posting-Host: 207.46.19.197
| | From: =?Utf-8?B?SEFmYW5kaQ==?= <HAfandi@.community.nospam>
| | References: <FABBDD23-F035-4AC6-B674-C790BE6D3D0F@.microsoft.com>
| <ev4NfteJIHA.1164@.TK2MSFTNGP02.phx.gbl>
| <C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com>
| <##N1b3eJIHA.484@.TK2MSFTNGP06.phx.gbl>
| <97908070-F3CC-432F-BD8D-AA2D27A6E8F8@.microsoft.com>
| <KPEmvPoJIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
| | Subject: Re: Convert DB Collation Sort Type in SQL 2005 for MS Dynamics
GP
| | Date: Wed, 14 Nov 2007 05:34:00 -0800
| | Lines: 171
| | Message-ID: <B46EB3F2-6147-42E0-B734-B2D568EDE5B4@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | charset="Utf-8"
| | Content-Transfer-Encoding: 8bit
| | X-Newsreader: Microsoft CDO for Windows 2000
| | Content-Class: urn:content-classes:message
| | Importance: normal
| | Priority: normal
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| | Newsgroups: microsoft.public.sqlserver.server
| | Path: TK2MSFTNGHUB02.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:30502
| | NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Thanks for reply,
| |
| | I am trying to follow your steps but ..
| |
| | â?¢For generating the script, I right click the db >> select Tasks >>
| select
| | Generate Scripts
| | â?¢Following the wizard, I couldn't find where to enable the "Use
| Collation"
| | option
| | â?¢Also couldnâ't find the Only script 7.0 compatible features to
| enable it
| | â?¢After finishing the wizard, it gave me script a script to create a
| new db
| | with my tables selections
| | â?¢Now for using the DTS to transfer the data ..
| |
| | â?¢My db has about 900 table. And it will be very difficult to use
the
| | command "insert into .." for each table separately
| | â?¢For the script itself, is it necessary? What I mean, it's Dynamics
| GP
| | company db, and it is very easy for me to create the db using the GP
| utility
| | execution from my dynamics GP program
| |
| | â?¢Just a thought, can I just use the import/export utility from SQL
| 2005?
| | So I transfer the data from tables in old db to tables in new db? Or
this
| | will not solve my collation problem?
| |
| | Forgave me for noobe, but I need this and donâ't know what to do.
| |
| | Thanks
| | --
| | Hasan Afandi
| |
| |
| | "Adams Qu [MSFT]" wrote:
| |
| | > Dear Hasan,
| | >
| | > Thank you for posting here.
| | >
| | > Regarding your question, you could use the COLLATE clause on an ALTER
| | > DATABASE to change the default collation of the database, but there
are
| | > some limitations to this as well and really should only be used if
the
| text
| | > data columns are Unicode types, because changing the collation of any
| text,
| | > varchar and char fields that have no explicit column collation will
| alter
| | > the way the data's encoding is interpreted, resulting in a form of
| | > corruption of any characters that are out of the ASCII range.
Usually,
| it's
| | > not recommended to change database collation directly.
| | >
| | > For the security reason, it is recommended that you firsts BACKUP the
| | > Database and then change the default collation of the database via
the
| | > following SQL command:
| | >
| | > ALTER DATABASE <Database> COLLATE <COLLATION>
| | >
| | > To change the collation on the specific column, please run the
| following
| | > command:
| | >
| | > Alter table SysTotalizedValue
| | > Alter column vVar <Data_type> COLLATE <COLLATION>
| | >
| | > Alternatively, we can also attempt to use DTS/SSIS to change
collation
| of
| | > the database (as suggested by Urid). For the detail steps, please
refer
| to
| | > the following KB article:
| | >
| | > How to transfer a database from one collation to another collation in
| SQL
| | > Server
| | > http://support.microsoft.com/default.aspx?scid=kb;en-us;325335
| | >
| | > NOTE: This KB article also applies to SQL Sever 2005
| | >
| | > Additional Reference
| | > --
| | > SQL Server 2005 BOL: Copying Data Between Different Collations
| | >
|
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/c705468f-265b-450f-acc9-56ec
| | > e5866238.htm
| | >
| | > I hope this information is helpful. If you have further questions on
| the
| | > issue, please feel free to post back.
| | >
| | > Have a nice day!
| | >
| | > Best regards,
| | >
| | > Adams Qu, MCSE, MCDBA, MCTS
| | > Microsoft Online Support
| | >
| | > Microsoft Global Technical Support Center
| | >
| | > Get Secure! - www.microsoft.com/security
| | > =====================================================| | > When responding to posts, please "Reply to Group" via your newsreader
| so
| | > that others may learn and benefit from your issue.
| | > =====================================================| | > This posting is provided "AS IS" with no warranties, and confers no
| rights.
| | >
| | > --
| | > | Thread-Topic: Convert DB Collation Sort Type in SQL 2005 for MS
| Dynamics
| | > GP
| | > | thread-index: Acgl/XtWefUyIcx1SM+2tXDZEF5z+g==| | > | X-WBNR-Posting-Host: 207.46.19.197
| | > | From: =?Utf-8?B?SEFmYW5kaQ==?= <HAfandi@.community.nospam>
| | > | References: <FABBDD23-F035-4AC6-B674-C790BE6D3D0F@.microsoft.com>
| | > <ev4NfteJIHA.1164@.TK2MSFTNGP02.phx.gbl>
| | > <C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com>
| | > <##N1b3eJIHA.484@.TK2MSFTNGP06.phx.gbl>
| | > | Subject: Re: Convert DB Collation Sort Type in SQL 2005 for MS
| Dynamics GP
| | > | Date: Tue, 13 Nov 2007 06:00:00 -0800
| | > | Lines: 42
| | > | Message-ID: <97908070-F3CC-432F-BD8D-AA2D27A6E8F8@.microsoft.com>
| | > | MIME-Version: 1.0
| | > | Content-Type: text/plain;
| | > | charset="Utf-8"
| | > | Content-Transfer-Encoding: 7bit
| | > | X-Newsreader: Microsoft CDO for Windows 2000
| | > | Content-Class: urn:content-classes:message
| | > | Importance: normal
| | > | Priority: normal
| | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| | > | Newsgroups: microsoft.public.sqlserver.server
| | > | Path: TK2MSFTNGHUB02.phx.gbl
| | > | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:30339
| | > | NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
| | > | X-Tomcat-NG: microsoft.public.sqlserver.server
| | > |
| | > | what i understand from the url you gave me, is it provide a ways
for
| | > creating
| | > | data structure. but my problem is nor implementing a different
| database
| | > | structure (i can use another sql server to build my preferred
| structure)
| | > but
| | > | what about the data itself? how can i transfer it to the new db
| without
| | > | getting any problems
| | > |
| | > | for the vb, unfortunately i am not programming expert
| | > |
| | > | --
| | > | Hasan Afandi
| | > |
| | > |
| | > | "Uri Dimant" wrote:
| | > |
| | > | > Hi
| | > | > You can SMO or DMO object library within VB project for scripting.
| | > | > http://www.karaszi.com/SQLServer/info_generate_script.asp
| | > | >
| | > | >
| | > | > "HAfandi" <HAfandi@.community.nospam> wrote in message
| | > | > news:C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com...
| | > | > > can you please explaine more, i'm not pro in these things. how
| will i
| | > | > > script
| | > | > > out the structure?
| | > | > > if it will help, i can build a new sql server with desired
| structure,
| | > but
| | > | > > then how will i transfer the compant data from the old server
to
| new
| | > one?
| | > | > >
| | > | > > thanks for reply
| | > | > > --
| | > | > > Hasan Afandi
| | > | > >
| | > | > >
| | > | > > "Uri Dimant" wrote:
| | > | > >
| | > | > >> Hi
| | > | > >> It's not easy task. I'd script out the structure with desired
| | > COLLATION
| | > | > >> and
| | > | > >> then move the data into
| | > | > >
| | > | >
| | > | >
| | > | >
| | > |
| | >
| | >
| |
|
||||thanks for the help
i had some difficulty to sign to newsgroup, also to try the suggestion.
i just tried now, and it worked perfect for me.
thanks
--
Hasan Afandi
"Adams Qu [MSFT]" wrote:
> Dear Hasan,
> How's everything going?
> I'm wondering if the suggestion has helped or if you have any further
> questions. Please feel free to respond to the newsgroups if you need any
> additional help.
> Have a nice day!
> Best regards,
> Adams Qu
> MCSE, MCDBA, MCTS
> Microsoft Online Support
> Microsoft Global Technical Support Center
> Get Secure! - www.microsoft.com/security
> =====================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> | X-Tomcat-ID: 118659455
> | References: <FABBDD23-F035-4AC6-B674-C790BE6D3D0F@.microsoft.com>
> <ev4NfteJIHA.1164@.TK2MSFTNGP02.phx.gbl>
> <C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com>
> <##N1b3eJIHA.484@.TK2MSFTNGP06.phx.gbl>
> <97908070-F3CC-432F-BD8D-AA2D27A6E8F8@.microsoft.com>
> <KPEmvPoJIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
> <B46EB3F2-6147-42E0-B734-B2D568EDE5B4@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain
> | Content-Transfer-Encoding: 7bit
> | From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
> | Organization: Microsoft
> | Date: Thu, 15 Nov 2007 10:28:39 GMT
> | Subject: Re: Convert DB Collation Sort Type in SQL 2005 for MS Dynamics GP
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> | Message-ID: <SsLLMJ3JIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | Lines: 247
> | Path: TK2MSFTNGHUB02.phx.gbl
> | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:30644
> | NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
> |
> | Dear Hasan,
> |
> | Thank you for posting back.
> |
> | In SQL Server 2005, we can also attempt to use the SSIS Import/Export
> | wizard to transfer a database from one collation to a different
> collation.
> | By default, the data will be translated to the destination collation
> | (unless we manually specify the COLLATE clause by "Edit SQL.." in the
> | Wizard).
> |
> | For your information, in SQL Server SSIS (Integration Service), "Use
> | Collation" option is available in the "Transfer SQL Server Objects Task"
> ->
> | "Objects" option. You can refer to the SQL Server 2005 book online to get
> | more information. NOTE: "Use Collation" option described in KB 325335 is
> | applied to SQL Server 2000 DTS.
> |
> | At this stage, we can try take the following method to help you covert
> the
> | collation:
> |
> | NOTE: For the security reason, it is recommended that we first perform
> | above steps in a Test Server.
> |
> | 1. Back up the source database and ensure the backup file is gooda.
> | 2. Create a new database and specify the appropriate collation. "CREATE
> | DATABASE <DATABASE_name> COLLATE < COLLATION NAME> "
> | 3. Right click on the destination database ->Tasks ->Import Data.
> | 4. Follow the wizard to import all the data into the destination database.
> |
> | After that, we can manually verify some of the objects' collation by
> | running the "sp_help <table_name>" in the destination database.
> |
> | Please don't hesitate to let me know if anything is unclear in my post.
> |
> | Have a nice day!
> |
> | Best regards,
> |
> | Adams Qu
> | MCSE, MCDBA, MCTS
> | Microsoft Online Support
> |
> | Microsoft Global Technical Support Center
> |
> | Get Secure! - www.microsoft.com/security
> | =====================================================> | When responding to posts, please "Reply to Group" via your newsreader so
> | that others may learn and benefit from your issue.
> | =====================================================> | This posting is provided "AS IS" with no warranties, and confers no
> rights.
> |
> | --
> | | Thread-Topic: Convert DB Collation Sort Type in SQL 2005 for MS
> Dynamics
> | GP
> | | thread-index: AcgmwwPgEpouNC3gS1ysTeb+oEaHVg==> | | X-WBNR-Posting-Host: 207.46.19.197
> | | From: =?Utf-8?B?SEFmYW5kaQ==?= <HAfandi@.community.nospam>
> | | References: <FABBDD23-F035-4AC6-B674-C790BE6D3D0F@.microsoft.com>
> | <ev4NfteJIHA.1164@.TK2MSFTNGP02.phx.gbl>
> | <C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com>
> | <##N1b3eJIHA.484@.TK2MSFTNGP06.phx.gbl>
> | <97908070-F3CC-432F-BD8D-AA2D27A6E8F8@.microsoft.com>
> | <KPEmvPoJIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
> | | Subject: Re: Convert DB Collation Sort Type in SQL 2005 for MS Dynamics
> GP
> | | Date: Wed, 14 Nov 2007 05:34:00 -0800
> | | Lines: 171
> | | Message-ID: <B46EB3F2-6147-42E0-B734-B2D568EDE5B4@.microsoft.com>
> | | MIME-Version: 1.0
> | | Content-Type: text/plain;
> | | charset="Utf-8"
> | | Content-Transfer-Encoding: 8bit
> | | X-Newsreader: Microsoft CDO for Windows 2000
> | | Content-Class: urn:content-classes:message
> | | Importance: normal
> | | Priority: normal
> | | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
> | | Newsgroups: microsoft.public.sqlserver.server
> | | Path: TK2MSFTNGHUB02.phx.gbl
> | | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:30502
> | | NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
> | | X-Tomcat-NG: microsoft.public.sqlserver.server
> | |
> | | Thanks for reply,
> | |
> | | I am trying to follow your steps but ..
> | |
> | | �For generating the script, I right click the db >> select Tasks >>
> | select
> | | Generate Scripts
> | | �Following the wizard, I couldn't find where to enable the "Use
> | Collation"
> | | option
> | | �Also couldn�t find the Only script 7.0 compatible features to
> | enable it
> | | �After finishing the wizard, it gave me script a script to create a
> | new db
> | | with my tables selections
> | | �Now for using the DTS to transfer the data ..
> | |
> | | �My db has about 900 table. And it will be very difficult to use
> the
> | | command "insert into .." for each table separately
> | | �For the script itself, is it necessary? What I mean, it's Dynamics
> | GP
> | | company db, and it is very easy for me to create the db using the GP
> | utility
> | | execution from my dynamics GP program
> | |
> | | �Just a thought, can I just use the import/export utility from SQL
> | 2005?
> | | So I transfer the data from tables in old db to tables in new db? Or
> this
> | | will not solve my collation problem?
> | |
> | | Forgave me for noobe, but I need this and don�t know what to do.
> | |
> | | Thanks
> | | --
> | | Hasan Afandi
> | |
> | |
> | | "Adams Qu [MSFT]" wrote:
> | |
> | | > Dear Hasan,
> | | >
> | | > Thank you for posting here.
> | | >
> | | > Regarding your question, you could use the COLLATE clause on an ALTER
> | | > DATABASE to change the default collation of the database, but there
> are
> | | > some limitations to this as well and really should only be used if
> the
> | text
> | | > data columns are Unicode types, because changing the collation of any
> | text,
> | | > varchar and char fields that have no explicit column collation will
> | alter
> | | > the way the data's encoding is interpreted, resulting in a form of
> | | > corruption of any characters that are out of the ASCII range.
> Usually,
> | it's
> | | > not recommended to change database collation directly.
> | | >
> | | > For the security reason, it is recommended that you firsts BACKUP the
> | | > Database and then change the default collation of the database via
> the
> | | > following SQL command:
> | | >
> | | > ALTER DATABASE <Database> COLLATE <COLLATION>
> | | >
> | | > To change the collation on the specific column, please run the
> | following
> | | > command:
> | | >
> | | > Alter table SysTotalizedValue
> | | > Alter column vVar <Data_type> COLLATE <COLLATION>
> | | >
> | | > Alternatively, we can also attempt to use DTS/SSIS to change
> collation
> | of
> | | > the database (as suggested by Urid). For the detail steps, please
> refer
> | to
> | | > the following KB article:
> | | >
> | | > How to transfer a database from one collation to another collation in
> | SQL
> | | > Server
> | | > http://support.microsoft.com/default.aspx?scid=kb;en-us;325335
> | | >
> | | > NOTE: This KB article also applies to SQL Sever 2005
> | | >
> | | > Additional Reference
> | | > --
> | | > SQL Server 2005 BOL: Copying Data Between Different Collations
> | | >
> |
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/c705468f-265b-450f-acc9-56ec
> | | > e5866238.htm
> | | >
> | | > I hope this information is helpful. If you have further questions on
> | the
> | | > issue, please feel free to post back.
> | | >
> | | > Have a nice day!
> | | >
> | | > Best regards,
> | | >
> | | > Adams Qu, MCSE, MCDBA, MCTS
> | | > Microsoft Online Support
> | | >
> | | > Microsoft Global Technical Support Center
> | | >
> | | > Get Secure! - www.microsoft.com/security
> | | > =====================================================> | | > When responding to posts, please "Reply to Group" via your newsreader
> | so
> | | > that others may learn and benefit from your issue.
> | | > =====================================================> | | > This posting is provided "AS IS" with no warranties, and confers no
> | rights.
> | | >
> | | > --
> | | > | Thread-Topic: Convert DB Collation Sort Type in SQL 2005 for MS
> | Dynamics
> | | > GP
> | | > | thread-index: Acgl/XtWefUyIcx1SM+2tXDZEF5z+g==> | | > | X-WBNR-Posting-Host: 207.46.19.197
> | | > | From: =?Utf-8?B?SEFmYW5kaQ==?= <HAfandi@.community.nospam>
> | | > | References: <FABBDD23-F035-4AC6-B674-C790BE6D3D0F@.microsoft.com>
> | | > <ev4NfteJIHA.1164@.TK2MSFTNGP02.phx.gbl>
> | | > <C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com>
> | | > <##N1b3eJIHA.484@.TK2MSFTNGP06.phx.gbl>
> | | > | Subject: Re: Convert DB Collation Sort Type in SQL 2005 for MS
> | Dynamics GP
> | | > | Date: Tue, 13 Nov 2007 06:00:00 -0800
> | | > | Lines: 42
> | | > | Message-ID: <97908070-F3CC-432F-BD8D-AA2D27A6E8F8@.microsoft.com>
> | | > | MIME-Version: 1.0
> | | > | Content-Type: text/plain;
> | | > | charset="Utf-8"
> | | > | Content-Transfer-Encoding: 7bit
> | | > | X-Newsreader: Microsoft CDO for Windows 2000
> | | > | Content-Class: urn:content-classes:message
> | | > | Importance: normal
> | | > | Priority: normal
> | | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
> | | > | Newsgroups: microsoft.public.sqlserver.server
> | | > | Path: TK2MSFTNGHUB02.phx.gbl
> | | > | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:30339
> | | > | NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
> | | > | X-Tomcat-NG: microsoft.public.sqlserver.server
> | | > |
> | | > | what i understand from the url you gave me, is it provide a ways
> for
> | | > creating
> | | > | data structure. but my problem is nor implementing a different
> | database
> | | > | structure (i can use another sql server to build my preferred
> | structure)
> | | > but
> | | > | what about the data itself? how can i transfer it to the new db
> | without
> | | > | getting any problems|||Dear Hasan,
Appreciate your update and response. I am glad to hear that the problem has
been fixed.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Convert DB Collation Sort Type in SQL 2005 for MS Dynamics
GP
| thread-index: Acg4FVV4lUBWM/goSAOhlXCJGT4zWQ==| X-WBNR-Posting-Host: 207.46.193.207
| From: =?Utf-8?B?SEFmYW5kaQ==?= <HAfandi@.community.nospam>
| References: <FABBDD23-F035-4AC6-B674-C790BE6D3D0F@.microsoft.com>
<ev4NfteJIHA.1164@.TK2MSFTNGP02.phx.gbl>
<C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com>
<##N1b3eJIHA.484@.TK2MSFTNGP06.phx.gbl>
<97908070-F3CC-432F-BD8D-AA2D27A6E8F8@.microsoft.com>
<KPEmvPoJIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
<B46EB3F2-6147-42E0-B734-B2D568EDE5B4@.microsoft.com>
<SsLLMJ3JIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
<IrWPVL#KIHA.7800@.TK2MSFTNGHUB02.phx.gbl>
| Subject: Re: Convert DB Collation Sort Type in SQL 2005 for MS Dynamics GP
| Date: Thu, 6 Dec 2007 06:36:05 -0800
| Lines: 313
| Message-ID: <E49C851E-20EE-45FC-8C67-9A59B23101B6@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| Newsgroups: microsoft.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:32214
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| thanks for the help
|
| i had some difficulty to sign to newsgroup, also to try the suggestion.
|
| i just tried now, and it worked perfect for me.
|
| thanks
| --
| Hasan Afandi
|
|
| "Adams Qu [MSFT]" wrote:
|
| > Dear Hasan,
| >
| > How's everything going?
| >
| > I'm wondering if the suggestion has helped or if you have any further
| > questions. Please feel free to respond to the newsgroups if you need
any
| > additional help.
| >
| > Have a nice day!
| >
| > Best regards,
| >
| > Adams Qu
| > MCSE, MCDBA, MCTS
| > Microsoft Online Support
| >
| > Microsoft Global Technical Support Center
| >
| > Get Secure! - www.microsoft.com/security
| > =====================================================| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| > =====================================================| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| >
| > --
| > | X-Tomcat-ID: 118659455
| > | References: <FABBDD23-F035-4AC6-B674-C790BE6D3D0F@.microsoft.com>
| > <ev4NfteJIHA.1164@.TK2MSFTNGP02.phx.gbl>
| > <C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com>
| > <##N1b3eJIHA.484@.TK2MSFTNGP06.phx.gbl>
| > <97908070-F3CC-432F-BD8D-AA2D27A6E8F8@.microsoft.com>
| > <KPEmvPoJIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
| > <B46EB3F2-6147-42E0-B734-B2D568EDE5B4@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain
| > | Content-Transfer-Encoding: 7bit
| > | From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| > | Organization: Microsoft
| > | Date: Thu, 15 Nov 2007 10:28:39 GMT
| > | Subject: Re: Convert DB Collation Sort Type in SQL 2005 for MS
Dynamics GP
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > | Message-ID: <SsLLMJ3JIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.server
| > | Lines: 247
| > | Path: TK2MSFTNGHUB02.phx.gbl
| > | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:30644
| > | NNTP-Posting-Host: TOMCATIMPORT1 10.201.218.122
| > |
| > | Dear Hasan,
| > |
| > | Thank you for posting back.
| > |
| > | In SQL Server 2005, we can also attempt to use the SSIS Import/Export
| > | wizard to transfer a database from one collation to a different
| > collation.
| > | By default, the data will be translated to the destination collation
| > | (unless we manually specify the COLLATE clause by "Edit SQL.." in the
| > | Wizard).
| > |
| > | For your information, in SQL Server SSIS (Integration Service), "Use
| > | Collation" option is available in the "Transfer SQL Server Objects
Task"
| > ->
| > | "Objects" option. You can refer to the SQL Server 2005 book online to
get
| > | more information. NOTE: "Use Collation" option described in KB 325335
is
| > | applied to SQL Server 2000 DTS.
| > |
| > | At this stage, we can try take the following method to help you
covert
| > the
| > | collation:
| > |
| > | NOTE: For the security reason, it is recommended that we first
perform
| > | above steps in a Test Server.
| > |
| > | 1. Back up the source database and ensure the backup file is gooda.
| > | 2. Create a new database and specify the appropriate collation.
"CREATE
| > | DATABASE <DATABASE_name> COLLATE < COLLATION NAME> "
| > | 3. Right click on the destination database ->Tasks ->Import Data.
| > | 4. Follow the wizard to import all the data into the destination
database.
| > |
| > | After that, we can manually verify some of the objects' collation by
| > | running the "sp_help <table_name>" in the destination database.
| > |
| > | Please don't hesitate to let me know if anything is unclear in my
post.
| > |
| > | Have a nice day!
| > |
| > | Best regards,
| > |
| > | Adams Qu
| > | MCSE, MCDBA, MCTS
| > | Microsoft Online Support
| > |
| > | Microsoft Global Technical Support Center
| > |
| > | Get Secure! - www.microsoft.com/security
| > | =====================================================| > | When responding to posts, please "Reply to Group" via your newsreader
so
| > | that others may learn and benefit from your issue.
| > | =====================================================| > | This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| > |
| > | --
| > | | Thread-Topic: Convert DB Collation Sort Type in SQL 2005 for MS
| > Dynamics
| > | GP
| > | | thread-index: AcgmwwPgEpouNC3gS1ysTeb+oEaHVg==| > | | X-WBNR-Posting-Host: 207.46.19.197
| > | | From: =?Utf-8?B?SEFmYW5kaQ==?= <HAfandi@.community.nospam>
| > | | References: <FABBDD23-F035-4AC6-B674-C790BE6D3D0F@.microsoft.com>
| > | <ev4NfteJIHA.1164@.TK2MSFTNGP02.phx.gbl>
| > | <C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com>
| > | <##N1b3eJIHA.484@.TK2MSFTNGP06.phx.gbl>
| > | <97908070-F3CC-432F-BD8D-AA2D27A6E8F8@.microsoft.com>
| > | <KPEmvPoJIHA.5204@.TK2MSFTNGHUB02.phx.gbl>
| > | | Subject: Re: Convert DB Collation Sort Type in SQL 2005 for MS
Dynamics
| > GP
| > | | Date: Wed, 14 Nov 2007 05:34:00 -0800
| > | | Lines: 171
| > | | Message-ID: <B46EB3F2-6147-42E0-B734-B2D568EDE5B4@.microsoft.com>
| > | | MIME-Version: 1.0
| > | | Content-Type: text/plain;
| > | | charset="Utf-8"
| > | | Content-Transfer-Encoding: 8bit
| > | | X-Newsreader: Microsoft CDO for Windows 2000
| > | | Content-Class: urn:content-classes:message
| > | | Importance: normal
| > | | Priority: normal
| > | | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| > | | Newsgroups: microsoft.public.sqlserver.server
| > | | Path: TK2MSFTNGHUB02.phx.gbl
| > | | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:30502
| > | | NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| > | | X-Tomcat-NG: microsoft.public.sqlserver.server
| > | |
| > | | Thanks for reply,
| > | |
| > | | I am trying to follow your steps but ..
| > | |
| > | | �For generating the script, I right click the db >> select
Tasks >>
| > | select
| > | | Generate Scripts
| > | | �Following the wizard, I couldn't find where to enable the
"Use
| > | Collation"
| > | | option
| > | | �Also couldn�t find the Only script 7.0 compatible
features to
| > | enable it
| > | | �After finishing the wizard, it gave me script a script to
create a
| > | new db
| > | | with my tables selections
| > | | �Now for using the DTS to transfer the data ..
| > | |
| > | | �My db has about 900 table. And it will be very difficult
to use
| > the
| > | | command "insert into .." for each table separately
| > | | �For the script itself, is it necessary? What I mean, it's
Dynamics
| > | GP
| > | | company db, and it is very easy for me to create the db using the
GP
| > | utility
| > | | execution from my dynamics GP program
| > | |
| > | | �Just a thought, can I just use the import/export utility
from SQL
| > | 2005?
| > | | So I transfer the data from tables in old db to tables in new db?
Or
| > this
| > | | will not solve my collation problem?
| > | |
| > | | Forgave me for noobe, but I need this and don�t know what to
do.
| > | |
| > | | Thanks
| > | | --
| > | | Hasan Afandi
| > | |
| > | |
| > | | "Adams Qu [MSFT]" wrote:
| > | |
| > | | > Dear Hasan,
| > | | >
| > | | > Thank you for posting here.
| > | | >
| > | | > Regarding your question, you could use the COLLATE clause on an
ALTER
| > | | > DATABASE to change the default collation of the database, but
there
| > are
| > | | > some limitations to this as well and really should only be used
if
| > the
| > | text
| > | | > data columns are Unicode types, because changing the collation of
any
| > | text,
| > | | > varchar and char fields that have no explicit column collation
will
| > | alter
| > | | > the way the data's encoding is interpreted, resulting in a form
of
| > | | > corruption of any characters that are out of the ASCII range.
| > Usually,
| > | it's
| > | | > not recommended to change database collation directly.
| > | | >
| > | | > For the security reason, it is recommended that you firsts BACKUP
the
| > | | > Database and then change the default collation of the database
via
| > the
| > | | > following SQL command:
| > | | >
| > | | > ALTER DATABASE <Database> COLLATE <COLLATION>
| > | | >
| > | | > To change the collation on the specific column, please run the
| > | following
| > | | > command:
| > | | >
| > | | > Alter table SysTotalizedValue
| > | | > Alter column vVar <Data_type> COLLATE <COLLATION>
| > | | >
| > | | > Alternatively, we can also attempt to use DTS/SSIS to change
| > collation
| > | of
| > | | > the database (as suggested by Urid). For the detail steps, please
| > refer
| > | to
| > | | > the following KB article:
| > | | >
| > | | > How to transfer a database from one collation to another
collation in
| > | SQL
| > | | > Server
| > | | > http://support.microsoft.com/default.aspx?scid=kb;en-us;325335
| > | | >
| > | | > NOTE: This KB article also applies to SQL Sever 2005
| > | | >
| > | | > Additional Reference
| > | | > --
| > | | > SQL Server 2005 BOL: Copying Data Between Different Collations
| > | | >
| > |
| >
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/c705468f-265b-450f-acc9-56ec
| > | | > e5866238.htm
| > | | >
| > | | > I hope this information is helpful. If you have further questions
on
| > | the
| > | | > issue, please feel free to post back.
| > | | >
| > | | > Have a nice day!
| > | | >
| > | | > Best regards,
| > | | >
| > | | > Adams Qu, MCSE, MCDBA, MCTS
| > | | > Microsoft Online Support
| > | | >
| > | | > Microsoft Global Technical Support Center
| > | | >
| > | | > Get Secure! - www.microsoft.com/security
| > | | > =====================================================| > | | > When responding to posts, please "Reply to Group" via your
newsreader
| > | so
| > | | > that others may learn and benefit from your issue.
| > | | > =====================================================| > | | > This posting is provided "AS IS" with no warranties, and confers
no
| > | rights.
| > | | >
| > | | > --
| > | | > | Thread-Topic: Convert DB Collation Sort Type in SQL 2005 for MS
| > | Dynamics
| > | | > GP
| > | | > | thread-index: Acgl/XtWefUyIcx1SM+2tXDZEF5z+g==| > | | > | X-WBNR-Posting-Host: 207.46.19.197
| > | | > | From: =?Utf-8?B?SEFmYW5kaQ==?= <HAfandi@.community.nospam>
| > | | > | References:
<FABBDD23-F035-4AC6-B674-C790BE6D3D0F@.microsoft.com>
| > | | > <ev4NfteJIHA.1164@.TK2MSFTNGP02.phx.gbl>
| > | | > <C116434B-1992-44FA-8536-594C4691DF52@.microsoft.com>
| > | | > <##N1b3eJIHA.484@.TK2MSFTNGP06.phx.gbl>
| > | | > | Subject: Re: Convert DB Collation Sort Type in SQL 2005 for MS
| > | Dynamics GP
| > | | > | Date: Tue, 13 Nov 2007 06:00:00 -0800
| > | | > | Lines: 42
| > | | > | Message-ID: <97908070-F3CC-432F-BD8D-AA2D27A6E8F8@.microsoft.com>
| > | | > | MIME-Version: 1.0
| > | | > | Content-Type: text/plain;
| > | | > | charset="Utf-8"
| > | | > | Content-Transfer-Encoding: 7bit
| > | | > | X-Newsreader: Microsoft CDO for Windows 2000
| > | | > | Content-Class: urn:content-classes:message
| > | | > | Importance: normal
| > | | > | Priority: normal
| > | | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2992
| > | | > | Newsgroups: microsoft.public.sqlserver.server
| > | | > | Path: TK2MSFTNGHUB02.phx.gbl
| > | | > | Xref: TK2MSFTNGHUB02.phx.gbl
microsoft.public.sqlserver.server:30339
| > | | > | NNTP-Posting-Host: tk2msftsbfm01.phx.gbl 10.40.244.148
| > | | > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > | | > |
| > | | > | what i understand from the url you gave me, is it provide a
ways
| > for
| > | | > creating
| > | | > | data structure. but my problem is nor implementing a different
| > | database
| > | | > | structure (i can use another sql server to build my preferred
| > | structure)
| > | | > but
| > | | > | what about the data itself? how can i transfer it to the new db
| > | without
| > | | > | getting any problems
|