cancel
Showing results for 
Search instead for 
Did you mean: 

LOG BZA=>table SVERS does not exist on database

Amarnath
Participant
0 Kudos

Hello Experts,

In the process of Migration from oracle to mssql/2008 we are facing an error with r3trans -d with error "LOG BZA=>table SVERS does not exist on database"

I was referring other threads as well for resolution. I got through one to try

1294762 - SCHEMA4SAP.VBS


Even above script did not work for me. Below is the output of r3trans -d executed with SIDADM.


Also just to give a try I have searched for SVERS in SQL as


use SID

go

select * from SID.sid.SVERS

go


Output: Invalid object name SID.sid.SVERS


Tried the same like select * from SID.SVERS ,,, sid.SVERS,,, SVERS etc but the result was same.


Trans.log


4 ETW000 R3trans version 6.24 (release 721 - 12.04.16 - 20:16:02).

4 ETW000 unicode enabled version

4 ETW000 ===============================================

4 ETW000

4 ETW000 date&time   : 23.06.2016 - 01:44:12

4 ETW000 control file: <no ctrlfile>

4 ETW000 R3trans was called as follows: R3trans -d

4 ETW000  trace at level 1 opened for a given file pointer

4 ETW000  [     dev trc,00000]  Thu Jun 23 01:44:12 2016

4 ETW000  [     dev trc,00000]  Loading DB library 'dbmssslib.dll' ...

4 ETW000  [     dev trc,00000]  Library 'dbmssslib.dll' loaded

4 ETW000  [     dev trc,00000]  Version of 'dbmssslib.dll' is "721.02", patchlevel (0.700)

4 ETW000  [     dev trc,00000]  Thread ID:4008

4 ETW000  [     dev trc,00000]  Thank You for using the SLODBC-interface

4 ETW000  [     dev trc,00000]  Using dynamic link library 'D:\usr\sap\SID\SYS\exe\uc\NTAMD64\dbmssslib.dll'

4 ETW000  [     dev trc,00000]  7210 dbmssslib.dll patch info

4 ETW000  [     dev trc,00000]    SAP patchlevel  0

4 ETW000  [     dev trc,00000]    SAP patchno  700

4 ETW000  [     dev trc,00000]    Last MSSQL DBSL patchlevel 0

4 ETW000  [     dev trc,00000]    Last MSSQL DBSL patchno         700

4 ETW000  [     dev trc,00000]    Last MSSQL DBSL patchcomment Support Package Stack Kernel 721 (EXT) Patch Level 700 (2316471)

4 ETW000  [     dev trc,00000]  ODBC Driver chosen: SQL Server Native Client 10.0 native

4 ETW000  [     dev trc,00000]  Local connection used on HOSTNAME to named instance: lpc:HOSTNAME\SID

4 ETW000  [     dev trc,00000]  Thu Jun 23 01:44:13 2016

4 ETW000  [     dev trc,00000]  Local connection used on HOSTNAME to named instance: lpc:HOSTNAME\SID

4 ETW000  [     dev trc,00000]  Local connection used on HOSTNAME to named instance: lpc:HOSTNAME\SID

4 ETW000  [     dev trc,00000]  Driver: sqlncli10.dll Driver release: 10.50.6000

4 ETW000  [     dev trc,00000]  GetDbRelease: 10.50.6000.34

4 ETW000  [     dev trc,00000]  GetDbRelease: Got DB release numbers (10,50,6000,34)

4 ETW000  [     dev trc,00000]  ERROR: -1 in function CheckCodepageType (SQLExecDirect failed) [line 22233]

4 ETW000  [     dev trc,00000]  (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'DBPROPERTIES'.

4 ETW000  [     dev trc,00000]  comm. rd. spid 53

4 ETW000  [     dev trc,00000]  unc. rd. spid 54

4 ETW000  [     dev trc,00000]  Connection 0 opened (DBSL handle 0)

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'SVERS'.

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

4 ETW000  [     dev trc,00000]  Error 103 (dbcode 208) in StartSelect

4 ETW000  [     dev trc,00000]  StartSelect: stmt of NULL_STMTID

4 ETW000  [     dev trc,00000]  SELECT VERSION FROM SVERS

4 ETW000                        /* R3::0 T:SVERS */

4 ETW000  [    dblink  ,00000]  ***LOG BZA=>table SVERS does not exist on database

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'DDNTT'.

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

4 ETW000  [     dev trc,00000]  Error 103 (dbcode 208) in StartSelect

4 ETW000  [     dev trc,00000]  StartSelect: stmt of NULL_STMTID

4 ETW000  [     dev trc,00000]  SELECT COMPCNT, UNICODELG FROM "DDNTT" WHERE TABNAME = 'SVERS'

4 ETW000                        /* R3::0 T:DDNTT

4 ETW000  [     dev trc,00000]   */

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'DDNTT'.

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

4 ETW000  [     dev trc,00000]  Error 103 (dbcode 208) in StartSelect

4 ETW000  [     dev trc,00000]  StartSelect: stmt of NULL_STMTID

4 ETW000  [     dev trc,00000]  SELECT VERSION FROM "DDNTT" WHERE TABNAME = 'SVERS'

4 ETW000                        /* R3::0 T:DDNTT */

4 ETW000  [dbntab.c    ,00000]  *** ERROR => NTAB: SELECT VERSION FROM DDNTT WHERE TABNAME = 'SVERS' failed, dbsl_rc=103

4 ETW000  [    dbntab  ,00000]  ***LOG BZY=>unexpected return code 103 calling NTAB

4 ETW000  [     dev trc,00000]  NTAB: db_ntab(): init_ntab failed, returning 2

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'XXXXT'.

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

4 ETW000  [     dev trc,00000]  Error 103 (dbcode 208) in StartSelect

4 ETW000  [     dev trc,00000]  StartSelect: stmt of NULL_STMTID

4 ETW000  [     dev trc,00000]  SELECT COMPCNT, UNICODELG FROM "XXXXT" WHERE TABNAME = 'SVERS'

4 ETW000                        /* R3::0 T:XXXXT

4 ETW000  [     dev trc,00000]   */

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'XXXXT'.

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

4 ETW000  [     dev trc,00000]  Error 103 (dbcode 208) in StartSelect

4 ETW000  [     dev trc,00000]  StartSelect: stmt of NULL_STMTID

4 ETW000  [     dev trc,00000]  SELECT VERSION FROM "XXXXT" WHERE TABNAME = 'SVERS'

4 ETW000                        /* R3::0 T:XXXXT */

4 ETW000  [dbntab.c    ,00000]  *** ERROR => NTAB: SELECT VERSION FROM XXXXT WHERE TABNAME = 'SVERS' failed, dbsl_rc=103

4 ETW000  [    dbntab  ,00000]  ***LOG BZY=>unexpected return code 103 calling NTAB

4 ETW000  [     dev trc,00000]  NTAB: db_ntab(): init_ntab failed, returning 2

2EETW000 sap_dext called with msgnr "2":

2EETW000 ---- db call info ----

2EETW000 function:   db_ntab

2EETW000 fcode:      NT_RDTDESCR

2EETW000 tabname:    TADIR

2EETW000 len (char): 5

2EETW000 key:        TADIR

2EETW000 retcode:    2

4 ETW690 ROLLBACK "0" "0"

Can you guide me from this?

Best Regards,

Amarnath

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hello Amartnath,

I've faced the same error before. Here are the steps that I had executed for the solution:

Change the schema user in the profile files of SIDadm and oraSID user from source system to target system. Profile files: .*.sh files in Linux. I don't know your OS type, but the logic is similar.

Check your schema user from source system once again.

It should seem like like this:

hostname:TARGETSIDadm> grep -i sapSID .*sh

.dbenv_hostname.csh:if ( "sapSID" != "" ) then

.dbenv_hostname.csh:setenv dbs_db6_schema sapSID

.dbenv_hostname.csh:if ( "sapSID" != "" ) then

.dbenv_hostname.csh:setenv dbs_db6_user sapSID

.dbenv_hostname.sh:dbs_db6_schema=sapSID

.dbenv_hostname.sh:dbs_db6_user=sapSID

.dbenv.csh:if ( "sapSID" != "" ) then

.dbenv.csh:setenv dbs_db6_schema sapSID

.dbenv.csh:if ( "sapSID" != "" ) then

.dbenv.csh:setenv dbs_db6_user sapSID

.dbenv.sh:dbs_db6_schema=sapSID

.dbenv.sh:dbs_db6_user=sapSID

hostname:oraTARGETSID> grep -i sapSID .*sh

.dbenv_hostname.csh:if ( "sapSID" != "" ) then

.dbenv_hostname.csh:setenv dbs_db6_schema sapSID

.dbenv_hostname.csh:if ( "sapSID" != "" ) then

.dbenv_hostname.csh:setenv dbs_db6_user sapSID

.dbenv_hostname.sh:dbs_db6_schema=sapSID

.dbenv_hostname.sh:dbs_db6_user=sapSID

.dbenv.csh:if ( "sapSID" != "" ) then

.dbenv.csh:setenv dbs_db6_schema sapSID

.dbenv.csh:if ( "sapSID" != "" ) then

.dbenv.csh:setenv dbs_db6_user sapSID

.dbenv.sh:dbs_db6_schema=sapSID

.dbenv.sh:dbs_db6_user=sapSID

And then update the password via brtools. Example command:

brconnect -c -p initSID.sap -l E -f chpass -o SAPSR3 -password ******

Regards,

Serhat

karthikeyan_natarajan4
Active Contributor
0 Kudos

Hi Amarnath,

Is the Migration done? Did you run the  SCHEMA4SAP with correct user ID, if your SID is XXX, u should run the repair schema as Domain\XXXadm not as domain\xxxadm (means SID should be in UPPERCASE)

Regards,

kartik

Amarnath
Participant
0 Kudos

Hello Kartik,

Nope Migration has stopped in phase MSS Post load activities.

We are executing SWPM with user domain\username, stopped with error.

I ran the script with hostname\sidadm Can you correct me if this is not rigth? Also we do not have user domain\sidadm or domain\SIDadm.

Best Regards,

Amarnath

karthikeyan_natarajan4
Active Contributor
0 Kudos

If you execute the repair script as XXXadm, what is the result?

Usually repair schema script will run with domain details. Is the sidadm user is created or not.

is your target system in windows? Kindly reply

regards

Kartik

Amarnath
Participant
0 Kudos

Hello Kartik,

Script executed successfully. Yes sidadm was created. Yes its windows.

Best Regards,

Amarnath

karthikeyan_natarajan4
Active Contributor
0 Kudos

Hi Amarnath,

While running the repair script did you choose your domain before enter SIDadm user?

Amarnath
Participant
0 Kudos

Hello Kartik,

No I did not choose any domain. It has shown me the hostname if this is a local account. So I have proceed with hostname for sidadm and sapservicesid.

Best Regards,

Amarnath

Amarnath
Participant
0 Kudos

Hello All,

Can any one help on this issue?

Best Regards,

Amarnath

karthikeyan_natarajan4
Active Contributor
0 Kudos

Hi Amarnath,

1. During System copy import - SAP System windows domain - domain mode = local installation or domain installation?

2. Login to database and check the user SIDadm roles - does it have sysadmin and public roles assigned.

Regards,

Kartik

karthikeyan_natarajan4
Active Contributor
0 Kudos

Check the parameter dbms_type = mss is set properly in instance profile and OS environment variable "dbms_type=mss" is properly set for OS user <sid>adm.

karthikeyan_natarajan4
Active Contributor
0 Kudos

Hi Amarnath,

Are you using kernel 7.21 64 bit unicode patch 700. If yes, please try to update the R3tans to patch 625 and dbsl to patch 700.

Regards,

kartik

Amarnath
Participant
0 Kudos

Hello Kartik,

We have selected local installation type.

Yes it has roles sysadmin and public roles.

We do not have instance profile created as of now and only default profile was created. We have seen "dbms/type = mss" in it.

Best Regards,

Amarnath

Amarnath
Participant
0 Kudos

Hi Kartik,

Ok I will try to update R3trans to 625 and dbsl patch to 700. I will let you know the result soon.

Best Regards,

Amarnath

Amarnath
Participant
0 Kudos

Hi Kartik,

I have tried as you mentioned. Used SAR files R3trans_625-10011176.SAR and lib_dbsl_700-10011175.sar. Rebooted the server and executed r3trans -d. But the result was same.

trans.log

4 ETW000 r3trans version 6.24 (release 721 - 15.03.16 - 20:16:01).

4 ETW000 ===============================================

4 ETW000

4 ETW000 date&time   : 24.06.2016 - 00:48:54

4 ETW000 control file: <no ctrlfile>

4 ETW000 R3trans was called as follows: r3trans -d

4 ETW000  trace at level 1 opened for a given file pointer

4 ETW000  [     dev trc,00000]  Fri Jun 24 00:48:55 2016

4 ETW000  [     dev trc,00000]  Loading DB library 'dbmssslib.dll' ...

4 ETW000  [     dev trc,00000]  Library 'dbmssslib.dll' loaded

4 ETW000  [     dev trc,00000]  Version of 'dbmssslib.dll' is "721.02", patchlevel (0.700)

4 ETW000  [     dev trc,00000]  Thread ID:3784

4 ETW000  [     dev trc,00000]  Thank You for using the SLODBC-interface

4 ETW000  [     dev trc,00000]  Using dynamic link library 'D:\usr\sap\SID\SYS\exe\uc\NTAMD64\dbmssslib.dll'

4 ETW000  [     dev trc,00000]  7210 dbmssslib.dll patch info

4 ETW000  [     dev trc,00000]    SAP patchlevel  0

4 ETW000  [     dev trc,00000]    SAP patchno  700

4 ETW000  [     dev trc,00000]    Last MSSQL DBSL patchlevel 0

4 ETW000  [     dev trc,00000]    Last MSSQL DBSL patchno         700

4 ETW000  [     dev trc,00000]    Last MSSQL DBSL patchcomment Support Package Stack Kernel 721 (EXT) Patch Level 700 (2316471)

4 ETW000  [     dev trc,00000]  ODBC Driver chosen: SQL Server Native Client 10.0 native

4 ETW000  [     dev trc,00000]  Local connection used on HOSTNAME to named instance: lpc:HOSTNAME\SID

4 ETW000  [     dev trc,00000]  Local connection used on HOSTNAME to named instance: lpc:HOSTNAME\SID

4 ETW000  [     dev trc,00000]  Local connection used on HOSTNAME to named instance: lpc:HOSTNAME\SID

4 ETW000  [     dev trc,00000]  Driver: sqlncli10.dll Driver release: 10.50.6000

4 ETW000  [     dev trc,00000]  GetDbRelease: 10.50.6000.34

4 ETW000  [     dev trc,00000]  GetDbRelease: Got DB release numbers (10,50,6000,34)

4 ETW000  [     dev trc,00000]  ERROR: -1 in function CheckCodepageType (SQLExecDirect failed) [line 22233]

4 ETW000  [     dev trc,00000]  (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'DBPROPERTIES'.

4 ETW000  [     dev trc,00000]  comm. rd. spid 52

4 ETW000  [     dev trc,00000]  unc. rd. spid 53

4 ETW000  [     dev trc,00000]  Connection 0 opened (DBSL handle 0)

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'SVERS'.

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

4 ETW000  [     dev trc,00000]  Error 103 (dbcode 208) in StartSelect

4 ETW000  [     dev trc,00000]  StartSelect: stmt of NULL_STMTID

4 ETW000  [     dev trc,00000]  SELECT VERSION FROM SVERS

4 ETW000                        /* R3::0 T:SVERS */

4 ETW000  [    dblink  ,00000]  ***LOG BZA=>table SVERS does not exist on database

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'DDNTT'.

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

4 ETW000  [     dev trc,00000]  Error 103 (dbcode 208) in StartSelect

4 ETW000  [     dev trc,00000]  StartSelect: stmt of NULL_STMTID

4 ETW000  [     dev trc,00000]  SELECT COMPCNT, UNICODELG FROM "DDNTT" WHERE TABNAME = 'SVERS'

4 ETW000                        /* R3::0 T:DDNTT

4 ETW000  [     dev trc,00000]   */

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'DDNTT'.

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

4 ETW000  [     dev trc,00000]  Error 103 (dbcode 208) in StartSelect

4 ETW000  [     dev trc,00000]  StartSelect: stmt of NULL_STMTID

4 ETW000  [     dev trc,00000]  SELECT VERSION FROM "DDNTT" WHERE TABNAME = 'SVERS'

4 ETW000                        /* R3::0 T:DDNTT */

4 ETW000  [dbntab.c    ,00000]  *** ERROR => NTAB: SELECT VERSION FROM DDNTT WHERE TABNAME = 'SVERS' failed, dbsl_rc=103

4 ETW000  [    dbntab  ,00000]  ***LOG BZY=>unexpected return code 103 calling NTAB

4 ETW000  [     dev trc,00000]  NTAB: db_ntab(): init_ntab failed, returning 2

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'XXXXT'.

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

4 ETW000  [     dev trc,00000]  Error 103 (dbcode 208) in StartSelect

4 ETW000  [     dev trc,00000]  StartSelect: stmt of NULL_STMTID

4 ETW000  [     dev trc,00000]  SELECT COMPCNT, UNICODELG FROM "XXXXT" WHERE TABNAME = 'SVERS'

4 ETW000                        /* R3::0 T:XXXXT

4 ETW000  [     dev trc,00000]   */

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (208) [42S02] [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'XXXXT'.

4 ETW000  [     dev trc,00000]  ERROR: -1 in function StartSelect (execute) [line 15183]

4 ETW000  [     dev trc,00000]  (8180) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Statement(s) could not be prepared.

4 ETW000  [     dev trc,00000]  Error 103 (dbcode 208) in StartSelect

4 ETW000  [     dev trc,00000]  StartSelect: stmt of NULL_STMTID

4 ETW000  [     dev trc,00000]  SELECT VERSION FROM "XXXXT" WHERE TABNAME = 'SVERS'

4 ETW000                        /* R3::0 T:XXXXT */

4 ETW000  [dbntab.c    ,00000]  *** ERROR => NTAB: SELECT VERSION FROM XXXXT WHERE TABNAME = 'SVERS' failed, dbsl_rc=103

4 ETW000  [    dbntab  ,00000]  ***LOG BZY=>unexpected return code 103 calling NTAB

4 ETW000  [     dev trc,00000]  NTAB: db_ntab(): init_ntab failed, returning 2

2EETW000 sap_dext called with msgnr "2":

2EETW000 ---- db call info ----

2EETW000 function:   db_ntab

2EETW000 fcode:      NT_RDTDESCR

2EETW000 tabname:    TADIR

2EETW000 len:        5

2EETW000 key:        TADIR

2EETW000 retcode:    2

4 ETW690 ROLLBACK "0" "0"

Best Regards,

Amarnath

karthikeyan_natarajan4
Active Contributor
0 Kudos

Hi Amarnath, How did you installed your target database SQL, using SQL4SAP or Manual installation.

is the collation is set to SQL_Latin1_General_CP850_BIN2, is the TCP/IP protocols enabled. Please check the SQL server installation guide for SAP and make sure database is correct or not.

Also, try to remove the sysadmin role for SIDadm user and see what you get.

Regards,

kartik

karthikeyan_natarajan4
Active Contributor
0 Kudos

Can you paste the output of SchemaRepair-***-SAP***DB-******.SQL from SQL server management studio.

Amarnath
Participant
0 Kudos

Hello Kartik,

Yes We have used the same collation you have mentioned. And we did manual installation.

Here is the log of schema repair and output : Query executed successfully.

/****************************************************************************

SCHEMA REPAIR SQL-script created by Schema4SAP.VBS

Version 1.09, Jan 2009: removed HASHED option

=================================================

SQL script for SQL Server 2005 (SQL 9.0) or newer

=================================================

SQL Instance: HOSTNAME\IVP

SID:           IVP

database:     IVP

ABAP schema:   ivp

domain:       HOSTNAME

creation day: 6/23/2016 1:33:40 AM

=================================================

To solve connection problems with this system:

1) check that the Windows-accounts exist:

  HOSTNAME\ivpadm

  HOSTNAME\SAPServiceIVP

2) set the user environment for HOSTNAME\ivpadm:

  DBMS_TYPE   = mss

  MSSQL_DBNAME = IVP

  MSSQL_SCHEMA = ivp

  MSSQL_SERVER = <dbserver> or <dbserver>\<instance>

3) set the registry key:

  Key:   HKEY_LOCAL_MACHINE/Software/SAP/IVP

  Type:   REG_SZ

  Value: AdmUser

  String: HOSTNAME\ivpadm

4) set the SAP default profile parameters:

  dbms/type     = mss

  dbs/mss/server = <dbserver> or <dbserver>\<instance>

  dbs/mss/schema = ivp

  dbs/mss/dbname = IVP

5) run this SQL script:

****************************************************************************/

-- check SQL instance name

declare @mySQLinstance sysname;

declare @error sysname;

set @mySQLinstance = cast(serverproperty('ServerName') as sysname);

set @error = 'This script is intended for SQL Server instance ''HOSTNAME\IVP'', ';

set @error = @error + 'but you are connected to ''' + @mySQLinstance + '''';

if upper(@mySQLinstance) != upper('HOSTNAME\IVP')

  raiserror(@error ,11,1);

else begin

use [master];

-- generate random password

declare @cmd nvarchar(999);

declare @passwd sysname;

set @passwd = 'RAND-pwd.' + cast(abs(checksum(cast(getdate() as float)+ rand()*1234567)) as sysname);

set @passwd = @passwd     + cast(abs(checksum(cast(getdate() as float)+ rand()*1234567)) as sysname);

-- recreate Windows Login HOSTNAME\ivpadm (Windows account must already exist)

if exists (select * from sys.syslogins where name = 'HOSTNAME\ivpadm')

  drop login [HOSTNAME\ivpadm];

create login [HOSTNAME\ivpadm] from windows with default_database = [IVP];

exec sp_addsrvrolemember 'HOSTNAME\ivpadm', 'sysadmin';

-- recreate Windows Login HOSTNAME\SAPServiceIVP (Windows account must already exist)

if exists (select * from sys.syslogins where name = 'HOSTNAME\SAPServiceIVP')

  drop login [HOSTNAME\SAPServiceIVP];

create login [HOSTNAME\SAPServiceIVP] from windows with default_database = [IVP];

exec sp_addsrvrolemember 'HOSTNAME\SAPServiceIVP', 'sysadmin';

-- if not exists: create SQL Login ivp (random password, disabled)

set @cmd = 'create login [ivp] with password=''' + @passwd + ''', check_policy = off;';

if not exists (select * from sys.syslogins where name = 'ivp')

  execute(@cmd);

alter login [ivp] with default_database = [IVP], check_expiration = off, check_policy = off;

alter login [ivp] disable;

exec sp_addsrvrolemember  'ivp', 'serveradmin';

exec sp_addsrvrolemember  'ivp', 'dbcreator';

exec sp_addsrvrolemember  'ivp', 'bulkadmin';

exec sp_dropsrvrolemember 'ivp', 'sysadmin';

use [IVP];

-- change database owner to sa

exec sp_changedbowner [sa];

-- create user and schema ivp

if not exists (select * from sys.database_principals where name='ivp' and type = 'S')

  create user [ivp] for login [ivp];

alter user [ivp] with default_schema = [ivp];

exec sp_addrolemember 'db_owner', 'ivp';

exec sp_droprolemember 'db_denydatareader', 'ivp';

exec sp_droprolemember 'db_denydatawriter', 'ivp';

exec sp_change_users_login 'Update_One', 'ivp', 'ivp';

if not exists (select * from sys.schemas where name = 'ivp')

  exec('create schema [ivp]');

alter authorization on schema::[ivp] to [ivp];

use [msdb];

-- create user and schema ivp

if not exists (select * from sys.database_principals where name='ivp' and type = 'S')

  create user [ivp] for login [ivp];

alter user [ivp] with default_schema = [ivp];

exec sp_addrolemember 'db_owner', 'ivp';

exec sp_droprolemember 'db_denydatareader', 'ivp';

exec sp_droprolemember 'db_denydatawriter', 'ivp';

exec sp_change_users_login 'Update_One', 'ivp', 'ivp';

if not exists (select * from sys.schemas where name = 'ivp')

  exec('create schema [ivp]');

alter authorization on schema::[ivp] to [ivp];

use [master];

-- create user and schema ivp

if not exists (select * from sys.database_principals where name='ivp' and type = 'S')

  create user [ivp] for login [ivp];

alter user [ivp] with default_schema = [ivp];

exec sp_addrolemember 'db_owner', 'ivp';

exec sp_droprolemember 'db_denydatareader', 'ivp';

exec sp_droprolemember 'db_denydatawriter', 'ivp';

exec sp_change_users_login 'Update_One', 'ivp', 'ivp';

if not exists (select * from sys.schemas where name = 'ivp')

  exec('create schema [ivp]');

alter authorization on schema::[ivp] to [ivp];

grant execute on xp_cmdshell to [ivp];

grant control server to [ivp];

-- end of 'check SQL instance name'

end;

Best Regards,

Amarnath

karthikeyan_natarajan4
Active Contributor
0 Kudos

Hi Amarnath,

Check the sidadm user in your system, is it ivpadm or IVPadm

Amarnath
Participant
0 Kudos

Hello Kartik,

I can see we have ivpadm in MSSQL mgmt studio not IVPadm. Dose it make any difference? If so do I need to take any steps to correct.

Best Regards,

Amarnath

karthikeyan_natarajan4
Active Contributor
0 Kudos

Check at OS level

Amarnath
Participant
0 Kudos

Hi Kartik,

Just checked it is ivpadm.

Best Regards,

Amarnath