sap – ROM

Create a DB Link from MSSQL to Sybase ASE 16 (running SAP)

Posted on December 8, 2017 by MaioLeave a comment

Step 1 – Install the ODBC driver on MSSQL Server

You need to install on your MSSQL box the “SDK FOR SAP ASE 16.0 for Windows” – To date you can download the SDK from SAP Markeplace looking for the package/zip number 51047970: this package contains both 64 and 32 bit ODBC drivers.

Step 2 (optional) – Define an Sybase ASE user with limited permission

If you don’t want to use the db owner to access your Sybase DB, you’ll need to define a new user with proper permission.

Connect to your Sybase DB with isql

isql -S DEV -U sapsso -P *** -X

“DEV” is your server/instance name
“sapsso” is the user
Replace *** with the proper password

The “-X” flag instruct isql to send your connection info encrypted. If it’s required and you don’t use it, you’ll get the error message “Adaptive Server requires encryption of the login password on the network.”

Open the master DB where we’re going to create the new user:

use master
go

Create the new use with:

sp_addlogin sample_username, password, default_db
go

Specify your new username, its password and the default database you want to work with. If you omit the default_db then it’ll use the master DB.

Next we need to add the new user to the proper DB. Before doing this we must connect to Sybase with “sapsa” user:

isql -S DEV -U sapsa -P *** -X

use DEV
go
sp_adduser 'sample_username', 'sample_username', 'public'
go

Now we’re going to assign to sample_user the proper grant to read or write:

Readonly grant sample:

grant select on schema_name.table_name to sample_user

Read/write grant sample:

grant select, update,insert,delete on schema_name.table_name to sample_user

Step 3 – Define the ODBC Driver on MSSQL

On your MSSQL box you need to create an ODBC driver. The ODBC architecture (32 or 64 bits) MUST match the MSSQL instance’s architecture.

You must provide:

A data source name
The name or IP address of your ASE server
The TCP port of the ASE DB (default is 4901)
Name of the ASE instance/server
Name of sample_user created above
Remember also to check the “Encrypt Password” flag in the “Connection” folder

Test your ODBC with the “Test Connection” button.

Step 4 – Create the Database Link on MSSQL Server

Now it’s time to create the DB Link on your MSSQL box: adapt the tsql instructions below to your needs and run them in a new query with SQL Management Studio.

Replace:

DBLINK_NAME with the name you want to set to your DB Link

ODBC_NAME with the name of the ODBC driver created before

ODBC_USER/ODBC_PASS with the DB user and password created before

USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'DBLINK_NAME', @srvproduct=N'SYBASE', @provider=N'MSDASQL', @datasrc=N'ODBC_NAME'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DBLINK_NAME',@useself=N'False',@locallogin=NULL,@rmtuser=N'ODBC_USER',@rmtpassword='ODBC_PASS'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLINK_NAME', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLINK_NAME', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLINK_NAME', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLINK_NAME', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLINK_NAME', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLINK_NAME', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLINK_NAME', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLINK_NAME', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLINK_NAME', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DBLINK_NAME', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLINK_NAME', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLINK_NAME', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DBLINK_NAME', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Test and troubleshooting

If you get some esoteric “conversion” errors while executing sql instructions via db link, you might try to use the openquery instrucition. Eg.:

Select sample:

SELECT * FROM OPENQUERY(DBLINK_NAME, 'SELECT * from  schema_name.table_name');

Update sample:

UPDATE OPENQUERY(DBLINK_NAME, 'SELECT * from  schema_name.table_name WHERE table_field=old_value') SET table_filed='new_value'

Just a matter of time… and timesync… and NTP

Posted on February 27, 2017 by MaioLeave a comment

Recently I spent some time migrating an SAP demo system from an old EXS 4.1 environment to a new ESXi 6.0. The chance was good to refresh some almost forgotten skills.

Right after moving and starting the SAP system on the new ESXi 6.0 environment we started to get the “ZDATE_ILLEGAL_LOCTIME” dump:

This issue is described in SAP note #2017092 and it’s quite straightforward to understand: there’s a time difference between the OS and the SAP Application Server, and that’s not good.

So here’s a few hints you can follow to setup the proper configuration:

1) Ensure your ESXi hosts have a correct NTP configuration

In my environment I set my Windows Domain Controller as the NTP for the ESXi hosts.

2) Check and disable VMWaretools “timesync” on your virtual machine

Form ESXi 5.5 onwards you can do this only via shell.

On Windows you must run:

c:\Program Files\VMware\VMware tools\VMwareToolboxCmd.exe timesync disable

To test if the settings has been applied correctly run:

vmwaretoolboxcmd.exe timesync status

On Linux, as root, run:

vmware-toolbox-cmd timesync disable

To test it:

vmware-toolbox-cmd timesync status

3) Check and adjust time on your virtual server

My suggestion is to use an internal NTP server, as Windows Domain Controller, anyway you can also rely on public/external NTP Server.

Before adjusting the clock of an SAP system remember:

If the system clock is back compared to the actual time you can simply move forward your system clock or wait for the NTP sync.

But: if your system time is ahead compared to the actual time, then adjusting back your system clock may cause problems. In this case I usually do this:

stop the SAP instance and DB
take note of the system time
adjust the system clock manually (or sync with the NTP server)
Wait until the actual time reaches the same time you had when you stopped the SAP instance, and then startup the instance

Once you’re done you can check your configuration running report “RSDBTIME” via SE38: