Could not create connection from RS to MSSQL

[former_member280303]

Hi all,

I am trying to create a replication environment from ASE to MSSQL

When I execute following command:

create connection to CLUTSMTYRCI.Catalogos

using profile rs_ase_to_msss;standard

set username to "Par4rsc"

set password to "CowdJtis4"

set error class to scotia_msss_error_class

I receive following errors:

I. 2015/09/21 12:07:07. SQM starting: 108:0 CLUTSMTYRCI.Catalogos

E. 2015/09/21 12:07:07. ERROR #1028 USER(uslibm) - seful/cm.c(3910)

  Message from server: Message: 18456, State 0, Severity 19 -- '[[Message Iteration=1|Data Source Name=CLUTSMTYRCI|SQLState=28000|Native Error=18456|Message=[Microsoft][SQL Native Client][SQL Server]Login failed for user 'Par4rsc'.[Message Iteration=2|SQLState=IM006|Native Error=|Message=[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]'.

E. 2015/09/21 12:07:07. ERROR #1028 USER(uslibm) - seful/cm.c(3910)

  Message from server: Message: 30000, State 0, Severity 16 -- 'Login failure. <SMR>'.

E. 2015/09/21 12:07:07. ERROR #1027 USER(uslibm) - seful/cm.c(3910)

  Open Client Client-Library error: Error: 67175468, Severity 4 -- 'ct_connect(): protocol specific layer: external error: The attempt to connect to the server failed.'.

E. 2015/09/21 12:07:07. ERROR #13045 USER(uslibm) - seful/cm.c(3914)

  Failed to connect to server 'CLUTSMTYRCI' as user 'Par4rsc'. See CT-Lib and/or server error messages for more information.

W. 2015/09/21 12:07:07. WARNING #15542 USER(uslibm) - dl/ddldb.c(5655)

  Connection to server <CLUTSMTYRCI> failed. Command batch not executed. See the Replication Server error log for details.

T. 2015/09/21 12:07:07. (97): Command(s) intended for 'CLUTSMTYRCI.Catalogos':

T. 2015/09/21 12:07:07. (97): 'drop table rs_info'

E. 2015/09/21 12:07:08. ERROR #1028 USER(uslibm) - seful/cm.c(3910)

  Message from server: Message: 18456, State 0, Severity 19 -- '[[Message Iteration=1|Data Source Name=CLUTSMTYRCI|SQLState=28000|Native Error=18456|Message=[Microsoft][SQL Native Client][SQL Server]Login failed for user 'Par4rsc'.[Message Iteration=2|SQLState=IM006|Native Error=|Message=[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]'.

E. 2015/09/21 12:07:08. ERROR #1028 USER(uslibm) - seful/cm.c(3910)

  Message from server: Message: 30000, State 0, Severity 16 -- 'Login failure. <SMR>'.

E. 2015/09/21 12:07:08. ERROR #1027 USER(uslibm) - seful/cm.c(3910)

  Open Client Client-Library error: Error: 67175468, Severity 4 -- 'ct_connect(): protocol specific layer: external error: The attempt to connect to the server failed.'.

E. 2015/09/21 12:07:08. ERROR #13045 USER(uslibm) - seful/cm.c(3914)

  Failed to connect to server 'CLUTSMTYRCI' as user 'Par4rsc'. See CT-Lib and/or server error messages for more information.

W. 2015/09/21 12:07:08. WARNING #15542 USER(uslibm) - dl/ddldb.c(5655)

  Connection to server <CLUTSMTYRCI> failed. Command batch not executed. See the Replication Server error log for details.

T. 2015/09/21 12:07:08. (97): Command(s) intended for 'CLUTSMTYRCI.Catalogos':

T. 2015/09/21 12:07:08. (97): 'create table rs_info (rskey varchar (20), rsval varchar (20))'

E. 2015/09/21 12:07:08. ERROR #1028 USER(uslibm) - seful/cm.c(3910)

  Message from server: Message: 18456, State 0, Severity 19 -- '[[Message Iteration=1|Data Source Name=CLUTSMTYRCI|SQLState=28000|Native Error=18456|Message=[Microsoft][SQL Native Client][SQL Server]Login failed for user 'Par4rsc'.[Message Iteration=2|SQLState=IM006|Native Error=|Message=[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]'.

E. 2015/09/21 12:07:08. ERROR #1028 USER(uslibm) - seful/cm.c(3910)

  Message from server: Message: 30000, State 0, Severity 16 -- 'Login failure. <SMR>'.

E. 2015/09/21 12:07:08. ERROR #1027 USER(uslibm) - seful/cm.c(3910)

  Open Client Client-Library error: Error: 67175468, Severity 4 -- 'ct_connect(): protocol specific layer: external error: The attempt to connect to the server failed.'.

E. 2015/09/21 12:07:08. ERROR #13045 USER(uslibm) - seful/cm.c(3914)

  Failed to connect to server 'CLUTSMTYRCI' as user 'Par4rsc'. See CT-Lib and/or server error messages for more information.

W. 2015/09/21 12:07:08. WARNING #15542 USER(uslibm) - dl/ddldb.c(5655)

  Connection to server <CLUTSMTYRCI> failed. Command batch not executed. See the Replication Server error log for details.

T. 2015/09/21 12:07:08. (97): Command(s) intended for 'CLUTSMTYRCI.Catalogos':

T. 2015/09/21 12:07:08. (97): 'insert into rs_info values ('charset_name', 'iso_1')'

E. 2015/09/21 12:07:08. ERROR #1028 USER(uslibm) - seful/cm.c(3910)

  Message from server: Message: 18456, State 0, Severity 19 -- '[[Message Iteration=1|Data Source Name=CLUTSMTYRCI|SQLState=28000|Native Error=18456|Message=[Microsoft][SQL Native Client][SQL Server]Login failed for user 'Par4rsc'.[Message Iteration=2|SQLState=IM006|Native Error=|Message=[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]'.

E. 2015/09/21 12:07:08. ERROR #1028 USER(uslibm) - seful/cm.c(3910)

  Message from server: Message: 30000, State 0, Severity 16 -- 'Login failure. <SMR>'.

E. 2015/09/21 12:07:08. ERROR #1027 USER(uslibm) - seful/cm.c(3910)

.

.

.

Message from server: Message: 18456, State 0, Severity 19 -- '[[Message Iteration=1|Data Source Name=CLUTSMTYRCI|SQLState=28000|Native Error=18456|Message=[Microsoft][SQL Native Client][SQL Server]Login failed for user 'Par4rsc'.[Message Iteration=2|SQLState=IM006|Native Error=|Message=[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed]'.

E. 2015/09/21 12:07:11. ERROR #1028 USER(uslibm) - seful/cm.c(3910)

Message from server: Message: 30000, State 0, Severity 16 -- 'Login failure. <SMR>'.

E. 2015/09/21 12:07:11. ERROR #1027 USER(uslibm) - seful/cm.c(3910)

Open Client Client-Library error: Error: 67175468, Severity 4 -- 'ct_connect(): protocol specific layer: external error: The attempt to connect to the server failed.'.

E. 2015/09/21 12:07:11. ERROR #13045 USER(uslibm) - seful/cm.c(3914)

Failed to connect to server 'CLUTSMTYRCI' as user 'Par4rsc'. See CT-Lib and/or server error messages for more information.

W. 2015/09/21 12:07:11. WARNING #15542 USER(uslibm) - dl/ddldb.c(5655)

Connection to server <CLUTSMTYRCI> failed. Command batch not executed. See the Replication Server error log for details.

T. 2015/09/21 12:07:11. (97): Command(s) intended for 'CLUTSMTYRCI.Catalogos':

T. 2015/09/21 12:07:11. (97): 'commit'

I. 2015/09/21 12:07:11. The DSI thread for database 'CLUTSMTYRCI.Catalogos' is started.

E. 2015/09/21 12:07:11. ERROR #13113 DSI(108 CLUTSMTYRCI.Catalogos) - seful/cm.c(4079)

Failed to obtain a reference to the indicated Connector. See messages from Connector factory for details. Data server make: mssql. Connector type: msnative. Data server: CLUTSMTYRCI. Database: Catalogos.

I. 2015/09/21 12:07:11. No Sybase library information is available.

I. 2015/09/21 12:07:11. The DSI thread for database 'CLUTSMTYRCI.Catalogos' is shutdown.

The connection is created but is down.

If I connect thru DC I reach the server with the same user and password without error.

Thanks for your help.

Luz Rodriguez

[former_member89972 · Accepted solution]

Hi Luz Rodriguez

Drop the old connection completely to remove any traces.

There is a "display_only" option for "create connection"  RS command with  a profile . 

If you run that it will show the actual commands RS server tries to run.

And it tells you where it tries to run those.

You can then take the command content and try running these by hand.

E.g. While setting up Oracle connection I used that feature to find what was failing and why.

So the full create connection command with display option for you will be :

create connection to CLUTSMTYRCI.Catalogos

using profile rs_ase_to_msss;standard

set username to "Par4rsc"

set password to "CowdJtis4"

set error class to scotia_msss_error_class

display_only

go

HTH

Avinash

[former_member280303 · Accepted solution]

Hi Avinash,

We executed the command with following result:

1> create connection to CLUTSMTYRCI.Catalogos

2> using profile rs_ase_to_msss;standard

3> set username to "Par4rsc"

4> set password to "CowdJtis4"

5> set error class to scotia_msss_error_class

6> display_only

7> go

Display only using Connection Profile rs_ase_to_msss;standard.

Command(s) intended for: RSCATA

create connection to CLUTSMTYRCI.Catalogos

set error class to scotia_msss_error_class

set function string class to rs_msss_function_class

set username to Par4rsc

set password to ********

set batch to off

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

drop table rs_info

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

create table rs_info (rskey varchar (20), rsval varchar (20))

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

insert into rs_info values ('charset_name', 'iso_1')

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

insert into rs_info values ('sortorder_name', 'bin_iso_1')

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

drop table rs_lastcommit

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

create table rs_lastcommit (origin int, origin_qid binary(36), secondary_qid

binary(36), origin_time datetime, dest_commit_time datetime)

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

create unique clustered index rs_lastcommit_idx on rs_lastcommit(origin)

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

drop procedure rs_update_lastcommit

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

create procedure rs_update_lastcommit @origin int,@origin_qid

binary(36),@secondary_qid binary(36),@origin_time datetime as update

rs_lastcommit set origin_qid = @origin_qid, secondary_qid =

@secondary_qid,origin_time = @origin_time,dest_commit_time = getdate() where

origin = @origin if (@@rowcount = 0) begin insert rs_lastcommit (origin,

origin_qid, secondary_qid,origin_time, dest_commit_time) values (@origin,

@origin_qid, @secondary_qid, @origin_time, getdate()) end

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

drop table rs_ticket_history

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

create table rs_ticket_history (cnt numeric(8,0) identity,h1 varchar(10),h2

varchar(10),h3 varchar(10),h4 varchar(50),pdb varchar(30),prs varchar(30),rrs

varchar(30),rdb varchar(30),pdb_t datetime,exec_t datetime,dist_t datetime,rsi_t

datetime,dsi_t datetime,rdb_t datetime default getdate(),exec_b

numeric(22,0),rsi_b numeric(22,0),dsi_tnx numeric(22,0),dsi_cmd

numeric(22,0),ticket varchar(1024))

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

create unique index rs_ticket_idx on rs_ticket_history(cnt)

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

grant all on rs_ticket_history to public

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

commit

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

drop procedure rs_send_repserver_cmd

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

CREATE PROCEDURE rs_send_repserver_cmd @rs_api VARCHAR(8000) AS declare @cmd

VARCHAR(8000), @sql varchar(50); BEGIN if (patindex('rs_rcl', lower(@rs_api)) >

0) begin print 'The Replication Server command should not contain the keyword

''rs_rcl'''; return(1); end select @cmd = 'rs_rcl ''' + replace(@rs_api, '''',

'''''') + ''' rs_rcl'; if ('rs_rcl' != substring (@cmd, datalength(@cmd) - 5,

6)) begin print 'The Replication Server command is too long.'; print 'Please

split it into two or more commands'; return (1); end set @sql = 'rs_marker';

exec @sql @cmd; END

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

commit

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

drop table rs_threads

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

create table rs_threads(id int,seq int CONSTRAINT PK_rs_threads PRIMARY KEY

CLUSTERED(id ASC) WITH (ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF))

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

grant select on rs_threads to public

Command(s) intended for 'CLUTSMTYRCI.Catalogos':

commit

1>

This is the first time that I run see this output do you know what is wrong?

Regards,

Luz Rodriguez

[former_member89972 · Accepted solution]

Hi Luz Rodriguez

Nothing is wrong. 

Display option shows what commands will be  executed behind the scene.

Now you can take one command -- as shown in the output -- at a time and see if executes without error.

Command(s) intended for: RSCATA are for execution on the RS server.

Command(s) intended for: CLUTSMTYRCI.Catalogos are on the target Server + database.

Copy each command carefully and run it on the required server.

With isql / sqlcmd session as needed.

I suspect some of the commands will return error.

Those you can fix by looking at the error message and re-run.

If each commands  when run individually runs without error, then the profile command should have run OK. 

HTH

Avinash

[former_member280303 · Accepted solution]

Hi Avinash,

The creation of the rs_send_repserver_cmd is not working. Can you give me a clue on what is wrong?

CREATE PROCEDURE rs_send_repserver_cmd @rs_api VARCHAR(8000)

AS

declare @cmd VARCHAR(8000),

@sql varchar(50);

BEGIN

if (patindex('rs_rcl', lower(@rs_api)) > 0)

                 begin

                print 'The Replication Server command should not contain the keyword ''rs_rcl''';

                return(1);

                end

                select @cmd = 'rs_rcl ''' + replace(@rs_api, '''','''''') + ''' rs_rcl'; if ('rs_rcl' != substring (@cmd, datalength(@cmd) - 5, 6))

                begin

                print 'The Replication Server command is too long.';

                print 'Please split it into two or more commands';

                return (1);

                end

set @sql = 'rs_marker';

exec @sql @cmd;

END

[[Message Iteration=1|Data Source Name=CLUTSMTYRCI|SQLState=42000|Native

Error=137|Message=[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Must

declare the scalar variable "@rs_api".[Message Iteration=2|SQLState=42000|Native

Error=|Message=[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect

syntax near 'The Replication Server command should not contain the

keyword

'rs_rcl''.]

Regards,

Luz

[former_member89972 · Accepted solution]

Hi Luz Rodriguez

How are you connecting to target/replicate MSSQL server ?

Did you use MSSQL Studio ? or SQLCMD ? And as what login ?

I took your code above and ran in my test MSSQL database with management studio.

It ran OK with no errors !  

NOTE : I did connect to the server as sysadmin. 

So if you have sysadmin login you can try with that first to check the code at your end.  For me it ran fine.

If you are connecting with user name Par4rsc make sure that that user has enough permissions to create objects etc. 

HTH

Avinash

[former_member280303 · Accepted solution]

Avinash,

We first created the rs_update_lastcommit, without error. The login is alias of dbo user in that database and we ran the command from isql.

Regards

[former_member89972 · Accepted solution]

Hi Luz Rodriguez

The SQL code itself is OK as I verified. 

Your use of isql  is confusing. 

Is this isql from SAP/Sybase or some other vendor ?

Normally for connecting to MSSQL I use sqlcmd (command line) or MSSQL Studio (GUI).

sqlcmd is similar ( but not same as ) isql from  SAP/Sybase

If this does not take you further,  you will need to open an incident with SAP.

HTH

Avinash

[former_member280303 · Accepted solution]

Hi Avinash,

We finally created all the objects in the MSSQL Server. But when we start the connection in the Replication Server I received following error:

I. 2015/09/22 16:31:21. The DSI thread for database 'CLUTSMTYRCI.Catalogos' is shutdown.

I. 2015/09/24 14:33:52. Trace enabled 'dsi', 'dsi_buf_dump'.

I. 2015/09/24 14:34:02. The DSI thread for database 'CLUTSMTYRCI.Catalogos' is started.

E. 2015/09/24 14:34:02. ERROR #13113 DSI(111 CLUTSMTYRCI.Catalogos) - seful/cm.c(4079)

        Failed to obtain a reference to the indicated Connector. See messages from Connector factory for details. Data server make: mssql. Connector type: msnative. Data server: CLUTSMTYRCI. Database: Catalogos.

I. 2015/09/24 14:34:02. No Sybase library information is available.

I. 2015/09/24 14:34:02. The DSI thread for database 'CLUTSMTYRCI.Catalogos' is shutdown.

I do not find information related. Do you have any idea?

Regards

Luz

[former_member89972 · Accepted solution]

Good. Some progress there. It verifies that when you run 'create connection' it should run OK.

What OS platform your RS and DC are on ?

The replication flow is typically as follows :

PDS.PDB ==> RS ==> DC/ECDA ==> RDS.RDB

Primary                                                Replicate

So test connection between each link first by hand and make sure that it works.

ON AIX Unix connector issue points to LIBPATH variable where it is not able to get the needed connector. E.g. for me the Oracle connector is in "/sybase/15/REP-15_5/connector/lib" folder in AIX.

I do not have MSSQL set up so can not help with exact scenario.

HTH

Avinash

[former_member280303 · Accepted solution]

Avinash,

PDS is ASE 15.5 on AIX

RS is 15.5 on AIX

DC/ECDA on Windows 2005

RDS is on WIndows

I do not find the connector directory but it is referenced in the LIBPATH variable. Maybe something was missing in the RS installation.

Do you know in which step this is installed in order to verify the installation log?

Regards,

Luz

[former_member89972 · Accepted solution]

OK. 

As far I remember RS and DC/ECDA were required to be on same host.

Beacuse your RS is on AIX, you will (most probably) need ODBC drivers on AIX to connect to MSSQL.

Parallel to this conversation do open an incident with SAP to verify latest requirements for

RS + DC/ECDA (.i.e. can they be on different hosts and platforms  OR they must be on same host )

Also note that testing/making ODBC connection work from AIX to MSSQL itself will be challenging.

For me the LIBPATH folder has all necessary native connectors for Oracle.

So this is not through ECDA but directly connecting to Oracle --- i.e. no need for ODBC drivers.

HTH

Avinash

[former_member280303 · Accepted solution]

If I send a connect command from isql i can connect to the Windows server. We configured ODBC to get this. DIrectConnect is working. I can access the objects on the remote database using DC.

We are changing the servers, so we have an actual productive system configured in this way.

But we need to setup the new machine with newer versions but same configuration squema.

As I did not find tne connector subdirectory I am going to install the RS in other host to see if the installation process is complete or not.

I will let you know the result.

Regards

Luz

[former_member89972 · Accepted solution]

Cool. You are way ahead then 🙂

I am really curios to know if you have a working production set up with RS on AIX and DC/ECDA on Windows.   Last where I worked for replication to MSSQL both RS and DC were on Solaris and it needed ODBC drivers on Solaris to connect to MSSQL.

Let us keep making good and steady progress.

Avinash

[former_member89972 · Accepted solution]

Hi Luz

Since you mentioned that you have working RS on AIX and DC on windows, my interest has perked up.

So I want to do and test a set up on similar lines.

Would you please share the SAP link from where you downloaded DC/ECDA component ?

I do not see that in a obvious place.

Thanks and regards

Avinash

[former_member280303 · Accepted solution]

Hi Avinash,

We downloaded Replication Server Option for MS SQLServer 15.0.1 from the download center in the archive software.

Regards,

[former_member89972 · Accepted solution]

Hi Luz Rodriguez

Did you get this working ? 

If so will please let me know what made it work ?

I have download the DC and DC Manager binaries

But it will take time for me to find a sandbox/lab MSSQL server to play around.

Regards

Avinash

[former_member280303 · Accepted solution]

Hi Avinash,

Technical Support helped us. They detected CR # 651385 Fail to create connection to MSSQL/UDB/DB2/Oracle with connection profile rs_ase_to_msss. The WA is

alter connection to dataserver.database set dsi_dataserver_make to 'ase'

alter connection to dataserver.database set dsi_connector_type ='ctlib'

We are on the process to configure the replication.

I will let you know the result.

[former_member280303 · Accepted solution]

Hi Avinash,

After we executed the alter connection command, the replication worked, only we got error 13110:

W. 2015/10/07 14:09:35. WARNING #13110 DSI EXEC(111(1) CLUTSMTYRCI.Catalogos) - ul/cmapp.c(2017)

        'CLUTSMTYRCI.Catalogos' does not return native errors. Error action mapping may not be correct.

This error is generated by CR 664491, The workaround is included in document 2090946.

Thanks a lot for your help

Luz rodriguez