SQL problem during system copy

[Former Member]

Hi guys,

I have this problem when I was doing system copy. I attached a database from SQL Server 2000 to another server having SQL server 2005. While I was doing the system copy, there was collation inconsistency. Then I corrected the collation. After that I faced this error

MDB-05053 Errors when executing sql command: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'active_start_date'.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'active_start_date'.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'active_start_date'.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'active_start_time'.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'freq_type'.

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'freq_interval'.

<br>Call your SAP Support.<br><br>

Can anyone help?

[clas_hortien]

Hello,

please run this script in your SAP database and send us the result:

-------------------------------------------------------------------------------
declare @dbn sysname
set @dbn = db_name()
select 'Server' as [Type ],(select convert(varchar(30),serverproperty('collation'))) as [Collation] union
select 'Master',(select convert(varchar(30),databasepropertyex('master','collation'))) union
select 'Model',(select convert(varchar(30),databasepropertyex('model','collation'))) union
select 'TempDB',(select convert(varchar(30),databasepropertyex('tempdb','collation'))) union
select 'SID',(select convert(varchar(30),databasepropertyex(@dbn,'collation')))
exec sp_dbcmptlevel @dbn
exec sp_dbcmptlevel 'master'
exec sp_dbcmptlevel 'tempdb'
-------------------------------------------------------------------------------

Best regards

Clas

[Former Member]

TESTSVR SQL_Latin1_General_CP850_BIN2

Master SQL_Latin1_General_CP850_BIN2

Model SQL_Latin1_General_CP850_BIN2

TempDB SQL_Latin1_General_CP850_BIN2

CBW SQL_Latin1_General_CP850_BIN2

[clas_hortien]

Hi,

the lower part of the script is missing. Can you please run this script instead:

(Please execute Ctrl-T right before the execution to get the output in textform.

Use Ctrl-D to switch back to Grid view)

-

---

use master
exec sp_dbcmptlevel 'CBW'
exec sp_dbcmptlevel 'master'
exec sp_dbcmptlevel 'tempdb'
exec sp_dbcmptlevel 'msdb'
exec sp_dbcmptlevel 'model'
select convert(varchar(30),databasepropertyex('msdb','collation'))
go
use msdb
exec sp_help sysjobschedules

Have you installed the correct basis support pack for SQL 2005 and

have you executed the Report MSSPROCS after the upgrade ?

Regards

Clas

[Former Member]

The current compatibility level is 90.

The current compatibility level is 90.

The current compatibility level is 90.

The current compatibility level is 90.

The current compatibility level is 90.

-

---

SQL_Latin1_General_CP850_BIN2

(1 row(s) affected)

Name Owner Type Created_datetime

sysjobschedules dbo user table 2005-10-14 01:54:13.113

Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation

schedule_id int no 4 10 0 yes (n/a) (n/a) NULL

job_id uniqueidentifier no 16 yes (n/a) (n/a) NULL

next_run_date int no 4 10 0 no (n/a) (n/a) NULL

next_run_time int no 4 10 0 no (n/a) (n/a) NULL

Identity Seed Increment Not For Replication

No identity column defined. NULL NULL NULL

RowGuidCol

No rowguidcol column defined.

Data_located_on_filegroup

PRIMARY

index_name index_description index_keys

clust clustered, unique located on PRIMARY job_id, schedule_id

constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys

DEFAULT on column next_run_date DF__sysjobsch__next___286302EC (n/a) (n/a) (n/a) (n/a) ((0))

DEFAULT on column next_run_time DF__sysjobsch__next___29572725 (n/a) (n/a) (n/a) (n/a) ((0))

FOREIGN KEY FK__sysjobsch__job_i__276EDEB3 No Action No Action Enabled Is_For_Replication job_id REFERENCES msdb.dbo.sysjobs (job_id)

FOREIGN KEY FK__sysjobsch__sched__267ABA7A No Action No Action Enabled Is_For_Replication schedule_id

REFERENCES msdb.dbo.sysschedules (schedule_id)

No foreign keys reference table 'sysjobschedules', or you do not have permissions on referencing tables.

No views with schema binding reference table 'sysjobschedules'.

I'm sorry the table kinda distorted when posted here. Do you mind if I have your email so that I can send you in textfile? If you dont wish to disclose your email, please email me at teckyong@gmail.com and I shall reply you. Thanks

No I did not execute the mssprocs nor installed the basis support pack. I moved the database from another server, and now I needed to execute the installation master for this system copy, while I couldnt do so because of the above mentioned problem. Well, in this case I guess I wouldnt be able to install the support pack for SQL 2005 nor execute MSSPROCS rite?

[clas_hortien]

Hi,

the problem is, that your basis support pack doesn't support the changed SQL 2005 systemtables.

You have to install special basis support packs, BEFORE you can upgrade to SQL 2005:

Release 4.6C > 53

Release 6.20 > 57

Release 6.40 > 15

You have to follow the note 799058 and the installation and upgrade guide

"Installation of and upgrade to SQL Server 2005 in an SAP environment".

You can find the guide at the SAP Service Marketplace under:

http://service.sap.com/instguides -> Other documentation -> Database

Upgrades -> MS SQL Server.

Regards

Clas

[Former Member]

Hi Clas,

Really appreciate it. I shall test it out and let you know if it works.

Thanks alot man!

[Former Member]

Really appreciate your help on this issue.

I've installed the basis support package (Release 6.40 support package

15) and by following note 799058, I came to step 9 which is to run

sapinst.exe for the database copy.

However I face the new error at the same stage which i faced the error

last time (Create SAP stored procedures -> procedure 062 of 114

sap_list_sch_agent_jobs).

Now the new error is:

ERROR 2007-09-25 10:18:47

MDB-05053 Errors when executing sql command: <p nr="0"/> If this message

is displayed as a warning - it can be ignored. If this is an error - call

your SAP support.

Hope to get your further guidance on this problem.

Thanks