SAP ECC6.0 cannot connect to SQL Server Database after recreating DB server

[Former Member]

We had a disaster with our ECC 6.0 Development system a few days ago.  The system is called “DEV”.  The SQL Server database server got wiped out and we didn’t have a backup.  Luckily, we did have a backup of the database.  Our system engineers rebuilt the server and our DBA reinstalled SQL Server and restored the database.  The database came up fine, but when we try to start SAP, it fails.  “R3trans –d” also fails.  The database is SQL Server 2008R2 10.50.2769.  The OS is Windows Server 2008R2.

We verified that the “devadm” and “SAPServiceDEV” accounts have permissions on the on the server. We set the following Environment Variables for the devadm account on the database server:

DBMS_TYPE = mss

MSSQL_DBNAME = DEV

MSSQL_SCHEMA = dev

MSSQL_SERVER = <database server name>

SAPLOCALHOST = <database server name>

SAPSYSTEMNAME = DEV

We also set the following Registry Key:

HKEY_LOCAL_MACHINE/Software/SAP/DEV

Type:     REG_SZ

Value:   AdmUser

String:   <database server name>\devadm

We didn’t install any kind of SAP software on the database server.

Is there any kind of SAP components we need to install on the database server?  Do we need to run the SAP installer on the database server to get everything setup correctly?  I looked at another one of our SQL Server database servers and it has SAP Management Console installed and there is a C:\Program Files\SAP\hostcntl folder.  I don't know what that is or it has anything to do with SAP being able to connect to the database.

Here is the error message in the R3trans log file:

4 ETW000  [     dev trc,00000]  Wed Apr 29 09:44:39 2015 1501385  1.531756

4 ETW000  [     dev trc,00000]  ERROR: -1 in function ExecuteAndFlush (SQLExecDirect) [line 6702]         35  1.531791

4 ETW000  [     dev trc,00000]  (102) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'execute.'.

4 ETW000 22 1.531813

4 ETW000  [     dev trc,00000]  <if user_name() != 'dev' execute as login = suser_sname(suser_sid('dev')) with no revert> failed -- connect terminated

4 ETW000 38 1.531851

4 ETW000  [    dblink ,00000]  ***LOG BY2=>sql error 102    performing CON 56  1.531907

4 ETW000  [    dblink ,00000]  ***LOG BY0=>[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'execute.'.

4 ETW000 30  1.531937

2EETW169 no connect possible: "DBMS = MSSQL ---  SERVER = '<db server name>' DBNAME = 'DEV'"

Please let me know what we need to do to get SAP to connect to the database.

Thanks,

Jerry

[former_member184473 · Accepted solution]

Hello Jerry,

After you restored the database, some authorizations are lost.
Execute the 'schema repair script' from note 1294762.

Regards,

Eduardo Rezende
SAP Support

[Former Member · Accepted solution]

Eduardo,

Thank you very much!  We ran the Schema Repair script from Note 1294762 and that solved the problem.

Best Regards,

Jerry

[Former Member]

Hi Jerry,

To make it very simple for you, follow the steps given below.

1.Install the windows operation system with the same service pack which you have installed earlier.

2.Install MSSQL server using SAP given media and also install it using the sap given script which is available in the media.

3.Install SAP using the same password which you have given during the earlier installation.

4.After successfully installing SAP restore your MSSQL old backup replacing the existing one.

5.Now the the system should work without any issue.

Regards,

Suresh Daniel.

[mamartins]

Hi Jerry,

The MSSQL Server version is the same as was before?

The logs show some kind of incompatibility between the SQL Server Native Client installed on the SAP server and the SQL Server.

Try to reinstall the SQL Server Native client.

Regards,

MM

[Sriram2009]

Hi Jerry

Is that DB host name changed? could you find / check the DB host name in default.pfl

BR

SS

[Former Member]

SS,

No, the DB host name hasn't changed.  We tried to recreate the DB server exactly like it was before.  We may have missed something. We didn't change anything on the SAP server.

Thanks

Jerry

[Sriram2009]

Hi Jerry

Could you attach the full log of r3trans -d

BR

SS

[Former Member]

Here it is: 4 ETW000 R3trans version 6.22 (release 720 - 18.11.11 - 11:29:00). 4 ETW000 unicode enabled version 4 ETW000 =============================================== 4 ETW000 4 ETW000 date&time  : 29.04.2015 - 19:33:01 4 ETW000 control file: 4 ETW000 R3trans was called as follows: R3trans -d 4 ETW000  trace at level 2 opened for a given file pointer 4 ETW000  [    dev trc,00000]  Wed Apr 29 19:33:01 2015                                                  83  0.000083 4 ETW000  [    dev trc,00000]  db_con_init called                                                        28  0.000111 4 ETW000  [    dev trc,00000]  set_use_ext_con_info(): rsdb/ssfs_connect not set => ssfs not used        31  0.000142 4 ETW000  [    dev trc,00000]  determine_block_commit: no con_hdl found as blocked for con_name = R/3 4 ETW000                                                                                                  20  0.000162 4 ETW000  [    dev trc,00000]  create_con (con_name=R/3)                                                10  0.000172 4 ETW000  [    dev trc,00000]  Loading DB library 'dbmssslib.dll' ...                                    31  0.000203 4 ETW000  [    dev trc,00000]  DlLoadLib success: LoadLibrary("dbmssslib.dll"), hdl 0, addr 000007FEEE6D0000 4 ETW000                                                                                                1865  0.002068 4 ETW000  [    dev trc,00000]      using "D:\usr\sap\DEV\SYS\exe\uc\NTAMD64\dbmssslib.dll"              14  0.002082 4 ETW000  [    dev trc,00000]  Library 'dbmssslib.dll' loaded                                            13  0.002095 4 ETW000  [    dev trc,00000]  function DbSlExpFuns loaded from library dbmssslib.dll                    15  0.002110 4 ETW000  [    dev trc,00000]  Version of 'dbmssslib.dll' is "720.00", patchlevel (0.200)                84  0.002194 4 ETW000  [    dev trc,00000]  function dsql_db_init loaded from library dbmssslib.dll                  16  0.002210 4 ETW000  [    dev trc,00000]  function dbdd_exp_funs loaded from library dbmssslib.dll                  11  0.002221 4 ETW000  [    dev trc,00000]  New connection 0 created                                                  29  0.002250 4 ETW000  [    dev trc,00000]  0: name = R/3, con_id = -000000001, state = DISCONNECTED, tx = NO , bc = NO , hc = NO , perm = YES, reco = NO , frco = NO , timeout = 000, con_max = 255, con_opt = 255, occ = NO , prog =  4 ETW000                                                                                                  29  0.002279 4 ETW000  [    dev trc,00000]  db_con_connect (con_name=R/3)                                            12  0.002291 4 ETW000  [    dev trc,00000]  determine_block_commit: no con_hdl found as blocked for con_name = R/3 4 ETW000                                                                                                  16  0.002307 4 ETW000  [    dev trc,00000]  find_con_by_name found the following connection:                          9  0.002316 4 ETW000  [    dev trc,00000]  0: name = R/3, con_id = 000000000, state = DISCONNECTED, tx = NO , bc = NO , hc = NO , perm = YES, reco = NO , frco = NO , timeout = 000, con_max = 255, con_opt = 255, occ = NO , prog =  4 ETW000                                                                                                  21  0.002337 4 ETW000  [    dev trc,00000]  Thread ID:3492                                                          594  0.002931 4 ETW000  [    dev trc,00000]  Thank You for using the SLODBC-interface                                  17  0.002948 4 ETW000  [    dev trc,00000]  Using dynamic link library 'D:\usr\sap\DEV\SYS\exe\uc\NTAMD64\dbmssslib.dll' 4 ETW000                                                                                                  21  0.002969 4 ETW000  [    dev trc,00000]  dbmssslib.dll patch info                                                  27  0.002996 4 ETW000  [    dev trc,00000]    SAP patchlevel  0                                                      12  0.003008 4 ETW000  [    dev trc,00000]    SAP patchno  200                                                        92  0.003100 4 ETW000  [    dev trc,00000]    Last MSSQL DBSL patchlevel 0                                            20  0.003120 4 ETW000  [    dev trc,00000]    Last MSSQL DBSL patchno        200                                    14  0.003134 4 ETW000  [    dev trc,00000]    Last MSSQL DBSL patchcomment Take care of warnings during database connect (1600066) 4 ETW000                                                                                                  18  0.003152 4 ETW000  [    dev trc,00000]  ODBC Driver chosen: SQL Server Native Client 10.0 native                273  0.003425 4 ETW000  [    dev trc,00000]  Network connection used from XXSAPDEV01 to XXSQLSAPDEV01 using tcp:XXSQLSAPDEV01 4 ETW000                                                                                                128  0.003553 4 ETW000  [    dev trc,00000]  Network connection used from XXSAPDEV01 to XXSQLSAPDEV01 using tcp:XXSQLSAPDEV01 4 ETW000                                                                                              20908  0.024461 4 ETW000  [    dev trc,00000]  Network connection used from XXSAPDEV01 to XXSQLSAPDEV01 using tcp:XXSQLSAPDEV01 4 ETW000                                                                                                3485  0.027946 4 ETW000  [    dev trc,00000]  Driver: sqlncli10.dll Driver release: 10.50.2769                        3695  0.031641 4 ETW000  [    dev trc,00000]  GetDbRelease: 10.50.2769.00                                              475  0.032116 4 ETW000  [    dev trc,00000]  GetDbRelease: Got DB release numbers (10,50,2769,0)                      18  0.032134 4 ETW000  [    dev trc,00000]  ERROR: -1 in function ExecuteAndFlush (SQLExecDirect) [line 6702]      6505  0.038639 4 ETW000  [    dev trc,00000]  (102) [42000] [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'execute.'. 4 ETW000                                                                                                  27  0.038666 4 ETW000  [    dev trc,00000]  failed -- connect terminated 4 ETW000                                                                                                  31  0.038697 4 ETW000  [    dblink  ,00000]  ***LOG BY2=>sql error 102    performing CON                              49  0.038746 4 ETW000  [    dblink  ,00000]  ***LOG BY0=>[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'execute.'. 4 ETW000                                                                                                  27  0.038773 2EETW169 no connect possible: "DBMS = MSSQL                            ---  SERVER = 'XXSQLSAPDEV01' DBNAME = 'DEV'"

[Former Member]

SS,

I emailed the trans.log to you.  It should be easier to read.

Thanks,

Jerry

[Sriram2009]

Hi Jerry

1. Are you executing the command "R3trans -d " on using SAPSID user id ?

2. Could you try to restart the system and than try again.

BR

SS