change collation on DB

[Former Member]

Hi people,

I made a copy of a r3 enterprise system in win2000 sql 2000 with a Win2003 x64 with sql 2005 and when I step on the copy of the database tools to sap sql I pass that collation server is different from the database imported:

server: SQL_Latin1_General_CP850_BIN2

Database: SQL_Latin1_General_CP850_BIN

As I change the collation of the database imported in order to continue with the processes postcopia?

Thank you very much

[Former Member · Accepted solution]

Here are more details:

1. Make sure your DB is backed up.

2. Make sure your system is at the correct SP level, check note 799058.

3. run sap_droproc at the SAP DB, this is a SQL stored procedure.

4. Do what I said above, shut down SAP and cycle SQL engine, make sure NO ONE is connected to the database. Including you, if you have SQL management studio open, close it!

5. Log into the DB server as the SAP <sid>adm account.

6. Run instcoll.exe

After this, you should have a 850_BIN2 DB on SQL 2000. Rest of upgrading to SQL 2005 should be easy enough.

[Former Member · Accepted solution]

Hi,

I got run instcoll.exe and change the collation of the database in the old system and pass it on again to the new, but I do not understand why that has not changed the collation of the database, what can I do?

Thanks

[Former Member]

Hi,

Restart SAP Server and Database Server, then you can check whether its changed or not.

NOte:- Look into SQL Server STM tools.

Regards,

Srini Nookala

[Former Member]

Hi,

Check with OSS notes given below and also read carefully,

Open a command prompt and execute InstColl.exe.

- For a named instance, enter:

InstColl.exe -S<server>\<instance> without any spaces after -S.

- For a default instance, enter:

1. Note 600027 - Installing the Corrected MS SQL Server Collation

2. Note 505906 - SQL Server: Incorrect collation

Regards,

Srini Nookala

[Former Member]

I put in cmd:

instcoll.exe-S<host> -D<db> ago and successfully changing collation.Pero in reality nothing has changed, because when I visited the enterprise manager still remains with BIN

[clas_hortien]

Hi,

can you send us the files:

%windir%\instcoll.log

%windir%\errorlog

Best regards

Clas

[Former Member]

Hi,

I found the error log

2008-07-30 15:36:23.14 server Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)

May 13 2005 18:33:17

Copyright (c) 1988-2003 Microsoft Corporation

Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

2008-07-30 15:36:23.15 server Copyright (C) 1988-2002 Microsoft Corporation.

2008-07-30 15:36:23.15 server All rights reserved.

2008-07-30 15:36:23.15 server Server Process ID is 2300.

2008-07-30 15:36:23.15 server Logging SQL Server messages in file 'E:Program FilesMicrosoft SQL ServerMSSQLlogERRORLOG'.

2008-07-30 15:36:23.17 server SQL Server is starting at priority class 'high'(2 CPUs detected).

2008-07-30 15:36:23.17 server Performance monitor shared memory setup failed: -1

2008-07-30 15:36:23.28 server SQL Server configured for thread mode processing.

2008-07-30 15:36:23.32 server Using dynamic lock allocation. Lock Blocks, Lock Owner Blocks.

2008-07-30 15:36:23.45 server Attempting to initialize Distributed Transaction Coordinator.

2008-07-30 15:36:25.70 spid3 Warning ******************

2008-07-30 15:36:25.70 spid3 SQL Server started in single user mode. Updates allowed to system catalogs.

2008-07-30 15:36:25.71 spid3 Starting up database 'master'.

2008-07-30 15:36:25.92 spid3 Server name is 'SAPR3'.

2008-07-30 15:36:25.92 spid5 Starting up database 'msdb'.

2008-07-30 15:36:25.92 spid6 Starting up database 'model'.

2008-07-30 15:36:25.92 server Using 'SSNETLIB.DLL' version '8.0.2039'.

2008-07-30 15:36:25.92 spid8 Starting up database 'pubs'.

2008-07-30 15:36:25.92 spid10 Starting up database 'Northwind'.

2008-07-30 15:36:26.00 server SQL server listening on 172.26.0.3: 1433.

2008-07-30 15:36:26.01 server SQL server listening on 127.0.0.1: 1433.

2008-07-30 15:36:26.10 spid6 Clearing tempdb database.

2008-07-30 15:36:26.82 spid6 Starting up database 'tempdb'.

2008-07-30 15:36:26.87 spid6 Analysis of database 'tempdb' (2) is 100% complete (approximately 0 more seconds)

2008-07-30 15:36:27.00 spid3 Warning ******************

2008-07-30 15:36:27.00 spid3 Attempting to change default collation to SQL_Latin1_General_CP850_BIN2.

2008-07-30 15:36:27.23 spid3 Clustered index restored for master.sysdatabases.

2008-07-30 15:36:27.26 spid3 Non-clustered index restored for master.sysobjects.

2008-07-30 15:36:27.28 spid3 Non-clustered index restored for master.sysobjects.

2008-07-30 15:36:27.32 spid3 index restored for master.syscolumns.

2008-07-30 15:36:27.34 spid3 index restored for master.systypes.

2008-07-30 15:36:27.35 spid3 index restored for master.sysusers.

2008-07-30 15:36:27.35 spid3 index restored for master.sysproperties.

2008-07-30 15:36:27.35 spid3 index restored for master.sysfulltextcatalogs.

2008-07-30 15:36:27.39 spid3 index restored for master.sysxlogins.

2008-07-30 15:36:27.40 spid3 index restored for master.sysdevices.

2008-07-30 15:36:27.42 spid3 index restored for master.sysservers.

2008-07-30 15:36:27.43 spid3 index restored for master.syslanguages.

2008-07-30 15:36:27.45 spid3 index restored for master.syscharsets.

2008-07-30 15:36:27.46 spid3 index restored for master.sysfilegroups.

2008-07-30 15:36:27.51 spid3 index restored for master.spt_values.

2008-07-30 15:36:27.51 spid3 index restored for master.spt_datatype_info_ext.

2008-07-30 15:36:27.53 spid3 index restored for master.MSreplication_options.

2008-07-30 15:36:27.53 spid3 index restored for master.spt_datatype_info.

2008-07-30 15:36:27.62 spid3 Non-clustered index restored for tempdb.sysobjects.

2008-07-30 15:36:27.62 spid3 Non-clustered index restored for tempdb.sysobjects.

2008-07-30 15:36:27.62 spid3 index restored for tempdb.syscolumns.

2008-07-30 15:36:27.64 spid3 index restored for tempdb.systypes.

2008-07-30 15:36:27.64 spid3 index restored for tempdb.sysusers.

2008-07-30 15:36:27.65 spid3 index restored for tempdb.sysproperties.

2008-07-30 15:36:27.65 spid3 index restored for tempdb.sysfulltextcatalogs.

2008-07-30 15:36:27.65 spid3 index restored for tempdb.sysfilegroups.

2008-07-30 15:36:27.68 spid3 Non-clustered index restored for model.sysobjects.

2008-07-30 15:36:27.68 spid3 Non-clustered index restored for model.sysobjects.

2008-07-30 15:36:27.70 spid3 index restored for model.syscolumns.

2008-07-30 15:36:28.28 server SQL server listening on TCP, Shared Memory, Named Pipes.

2008-07-30 15:36:28.31 server SQL Server is ready for client connections

2008-07-30 15:36:28.79 spid3 index restored for model.systypes.

2008-07-30 15:36:28.81 spid3 index restored for model.sysusers.

2008-07-30 15:36:28.82 spid3 index restored for model.sysproperties.

2008-07-30 15:36:28.82 spid3 index restored for model.sysfulltextcatalogs.

2008-07-30 15:36:28.84 spid3 index restored for model.sysfilegroups.

2008-07-30 15:36:29.03 spid3 Non-clustered index restored for msdb.sysobjects.

2008-07-30 15:36:29.04 spid3 Non-clustered index restored for msdb.sysobjects.

2008-07-30 15:36:29.09 spid3 index restored for msdb.syscolumns.

2008-07-30 15:36:29.09 spid3 index restored for msdb.systypes.

2008-07-30 15:36:29.10 spid3 index restored for msdb.sysusers.

2008-07-30 15:36:29.10 spid3 index restored for msdb.sysproperties.

2008-07-30 15:36:29.10 spid3 index restored for msdb.sysfulltextcatalogs.

2008-07-30 15:36:29.12 spid3 index restored for msdb.sysfilegroups.

2008-07-30 15:36:29.15 spid3 index restored for msdb.sysjobschedules.

2008-07-30 15:36:29.18 spid3 index restored for msdb.syscategories.

2008-07-30 15:36:29.18 spid3 index restored for msdb.systargetservers.

2008-07-30 15:36:29.18 spid3 index restored for msdb.systargetservergroups.

2008-07-30 15:36:29.23 spid3 index restored for msdb.sysalerts.

2008-07-30 15:36:29.23 spid3 index restored for msdb.sysoperators.

2008-07-30 15:36:29.25 spid3 index restored for msdb.syscachedcredentials.

2008-07-30 15:36:29.26 spid3 index restored for msdb.logmarkhistory.

2008-07-30 15:36:29.26 spid3 index restored for msdb.sysdtscategories.

2008-07-30 15:36:29.26 spid3 index restored for msdb.sysdbmaintplan_databases.

2008-07-30 15:36:29.28 spid3 index restored for msdb.mswebtasks.

2008-07-30 15:36:29.29 spid3 index restored for msdb.sysdownloadlist.

2008-07-30 15:36:29.29 spid3 index restored for msdb.sysjobs.

2008-07-30 15:36:29.32 spid3 index restored for msdb.sysjobsteps.

2008-07-30 15:36:29.39 spid3 Non-clustered index restored for pubs.sysobjects.

2008-07-30 15:36:29.40 spid3 Non-clustered index restored for pubs.sysobjects.

2008-07-30 15:36:29.40 spid3 index restored for pubs.syscolumns.

2008-07-30 15:36:29.40 spid3 index restored for pubs.systypes.

2008-07-30 15:36:29.42 spid3 index restored for pubs.sysusers.

2008-07-30 15:36:29.42 spid3 index restored for pubs.sysproperties.

2008-07-30 15:36:29.42 spid3 index restored for pubs.sysfulltextcatalogs.

2008-07-30 15:36:29.43 spid3 index restored for pubs.sysfilegroups.

2008-07-30 15:36:29.51 spid3 Non-clustered index restored for Northwind.sysobjects.

2008-07-30 15:36:29.51 spid3 Non-clustered index restored for Northwind.sysobjects.

2008-07-30 15:36:29.51 spid3 index restored for Northwind.syscolumns.

2008-07-30 15:36:29.53 spid3 index restored for Northwind.systypes.

2008-07-30 15:36:29.53 spid3 index restored for Northwind.sysusers.

2008-07-30 15:36:29.54 spid3 index restored for Northwind.sysproperties.

2008-07-30 15:36:29.54 spid3 index restored for Northwind.sysfulltextcatalogs.

2008-07-30 15:36:29.56 spid3 index restored for Northwind.sysfilegroups.

2008-07-30 15:36:29.59 spid3 index restored for Northwind.Orders.

2008-07-30 15:36:29.60 spid3 index restored for Northwind.Products.

2008-07-30 15:36:29.60 spid3 index restored for Northwind.CustomerCustomerDemo.

2008-07-30 15:36:29.62 spid3 index restored for Northwind.CustomerDemographics.

2008-07-30 15:36:29.62 spid3 index restored for Northwind.Territories.

2008-07-30 15:36:29.64 spid3 index restored for Northwind.EmployeeTerritories.

2008-07-30 15:36:29.68 spid3 index restored for Northwind.Employees.

2008-07-30 15:36:29.70 spid3 index restored for Northwind.Categories.

2008-07-30 15:36:29.73 spid3 index restored for Northwind.Customers.

2008-07-30 15:36:29.75 spid3 index restored for Northwind.Suppliers.

2008-07-30 15:36:30.95 spid3 Default collation successfully changed.

2008-07-30 15:36:30.95 spid3 Recovery complete.

2008-07-30 15:36:33.70 spid51 Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'xp_readerrorlog'.

I executed the instcoll.exe from the sale as the exe SQL_LATIN ... _BIN2 has been installed correctly and through the sale cmd as has been done the conversion of the R31, but when I entered the enterprise manager encounter with the same , Which is what I?

When I execute the SQL commando on source system

SELECT SERVERPROPERTY('Collation')

SELECT DATABASEPROPERTYEX('', 'Collation')

I can see that serverproperty is ...BIN2 and te databaseproperty is ...BIN. Why I can´t change the collation of the database??

Edited by: sergio torres on Jul 30, 2008 3:43 PM

[Former Member]

Hi people,

I want to execute instcoll.exe on the target system but i have this error at the momento:

PROVIDER FOR SQLDMO IS NOT INSTALLED PROPERLY

What can i do??

Edited by: sergio torres on Jul 31, 2008 12:47 PM

[Former Member]

resolved by myself

[Former Member]

We had the same problem, and not sure how you solved it, but our problem was that we downloaded and ran the wrong instcoll.exe per our architecture. We were using the AMD x64 version but should have used the 32-bit version.

[Former Member]

Ruben,

I am getting the same error. How did you resolved your problem?

[Former Member]

hi ,

How did you manage to resolve ...

M:\instcoll_amd64>instcoll -Seccprd -Dspd

Provider for SQLDMO is not installed properly.

NOTHING DONE!

it returns same error for me

[Former Member]

Hello,

Remove all Deadlocks and run the instcoll.exe

Regards,

Srini Nookala

[Former Member]

Could indicate as are the steps?

Thanks

[Former Member]

I´m on the homogeneous step in the case1 and I find this error

C:WINNTProfilesAdministratorDesktopinstcoll_i386>instcoll.exe -Ssapr3 -DR31

Trying to connect...

Connected to sapr3

Executing conversion of database R31.

Unable to convert R31

[ODBC SQL Server Driver][SQL Server]The database could not be exclusi

vely locked to perform the operation.

[ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC

printed error messages, contact your system administrator.

-The sap system is down

I hope your help,thanks.

[Former Member]

> I´m on the homogeneous step in the case1  and I find this error
> 
> C:WINNTProfilesAdministratorDesktopinstcoll_i386>instcoll.exe -Ssapr3 -DR31
> 
> Trying to connect...
> Connected to sapr3
> Executing conversion of database R31.
> Unable to convert R31
> [ODBC SQL Server Driver][SQL Server]The database could not be exclusi
> vely locked to perform the operation.
> [ODBC SQL Server Driver][SQL Server]DBCC execution completed. If DBCC
>  printed error messages, contact your system administrator.
> 
> -The sap system is down
> 
> 
> I hope your help,thanks.

With the SAP system down, cycle the SQL engine before running instcoll.exe to clear up all connections. Also, make sure that no one is connected to the DB via sql management studio either.

[Former Member]

And, I hope you backed up your DB first before running instcoll.exe

[Former Member]

Hi,

1. You can not change the SQL_Latin1_General_CP850_BIN2 to SQL_Latin1_General_CP850_BIN

Because SQL 2005 SQL_Latin1_General_CP850_BIN2 as default.

My Options:

1. First Install SQL 2000 with SQL_Latin1_General_CP850_BIN collation for server and database side.

2. Run The instColl.exe and convert the BIN to BIN2 collation.

3. Now install MS SQL Server 2005 and restore it BIN2 database.

Regards,

Srini Nookala

[Former Member]

I'm in the second step because when I install the instcoll.exe encounter with the error:

potential deadlocks exists on all schedulers

what I can do?

[Former Member]

Yes, I have already seen the note 600027, but explained that in order to change the colation bd can be imported back to the 2000 sql old machine and there change the collation but that step is not how to do it.

[Former Member]

Hi,

follow SAP Note 505906 - SQL Server: Incorrect collation

regards,

kaushal

[markus_doehr2]

Yes

Check 600027 - Installing the Corrected MS SQL Server Collation

Markus