topic Re: PROC FEDSQL failed to initiate in SAS Data Management

PROC FEDSQL failed to initiate

myelemes — Wed, 15 Aug 2018 16:32:22 GMT

Hi,

I am trying to use PROC FEDSQL in SAS EG 7.1 but it is not working for me.

Does anyone know what is going on here?

Error messages I am seeing are as below:

28 proc fedsql;

ERROR: Duplicate catalog name, SYSTEM, encountered in connection string, DSN or file DSN

ERROR: PROC FEDSQL initialization failed.

29 select count(*) from cad.IP_ACCT_XREF_ME_V;

30 quit;

SAS code I am trying to run:

Libname cad2 sybaseiq dsn="&mbaDB" user="&officeUser" password="&officePwd";

proc fedsql;select count(*) from cad2.IP_ACCT_XREF_ME_V; 
quit; 
run;

Re: PROC FEDSQL failed to initiate

ChrisHemedinger — Wed, 15 Aug 2018 16:45:29 GMT

This is caused by duplicate names among the native catalogs in the database you're accessing. Plays up when you have multiple libname connections.

I didn't find a SAS note but I did find a tech support track with some general advice. I'll include that here, but if you need help making sense of it or have further issues, I suggest contacting Tech Support.

If using Proc FedSQL or Proc DS2 when multiple Libname connections have been assigned to a database that supports native

catalogs, this error might occur.

ERROR: Duplicate catalog name, <name>, encountered in connection string, DSN or file DSN

Native catalogs are automatically included in the connection and are not listed in the CATALOG= parameters. To prevent the

errors you will need to specify all additional native catalog entries in the connection string and you will need to rename any that

produce errors.

For example, the following Netezza server has two native catalogs named MODEL and SYSTEM. An error will occur for each

duplicate catalog name found. The error seen in this case was:

ERROR: Duplicate catalog name, MODEL, encountered in connection string, DSN or file DSN

The example below shows how the two catalog names are renamed to prevent the errors.

To write the connection strings for all of the Libnames currently assigned to the SAS session to the SAS Log add this option

prior to the Proc DS2; or Proc FedSQL; statement:

options msglevel=i ;

The connection strings below illustrate how to rename the catalog names to prevent the errors caused by duplicate names.
  proc fedsql nolibs conn="DRIVER=FEDSQL;CONOPTS=(
                    (DRIVER=NETEZZA;SERVER=daily;DATABASE=TEST;UID=net_test;PWD=*;
                       CATALOG=(X1=TEST;X1_MODEL=MODEL;X1_SYSTEM=SYSTEM;));
                    (DRIVER=NETEZZA;SERVER=daily;DATABASE=system;UID=net_test;PWD=*;
                        CATALOG=(X2=TEST2;X2_MODEL=MODEL;X2_SYSTEM=SYSTEM;));)";
    select * from X1.customer;
  quit;
To turn prevent the connection strings from being written to the SAS Log add this option:

options msglevel=n;

Re: PROC FEDSQL failed to initiate

myelemes — Wed, 15 Aug 2018 20:06:24 GMT

Thank you, I did try renaming the catalog, as suggested below, but a new error message kept saying that Catalog arguments are bad.

proc fedsql  libs=(mba);

WHen I used libs= option, it seems to work.

My new issue is that I am having difficulty resolving Macro variables in Prod FedSQl;

proc fedsql  libs=(mba mbadsa);
SELECT count(*) from mba.dsa.equifax_das_scr where mba_run_dtm > &equifax_data_date;
quit;

run;

Above gives me following error:

37         proc fedsql  libs=(mba mbadsa);
NOTE: WARNING: [DataDirect][ODBC lib] Application's WCHAR type must be UTF16, because odbc driver's unicode type is UTF16
NOTE: WARNING: [DataDirect][ODBC lib] Application's WCHAR type must be UTF16, because odbc driver's unicode type is UTF16
38         SELECT count(*) from mba.dsa.equifax_das_scr where mba_run_dtm > &equifax_data_date;
ERROR: Error occurred building the Execution Tree.
ERROR: Error occurred initiating Filter.
ERROR: General error
NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.
39         quit;

When I surround macro variable with double quotes, it resolves, but I don't need the double quotes to stay in the output... Also, sas thinks it is a column name...

37         proc fedsql  libs=(mba mbadsa);
NOTE: WARNING: [DataDirect][ODBC lib] Application's WCHAR type must be UTF16, because odbc driver's unicode type is UTF16
NOTE: WARNING: [DataDirect][ODBC lib] Application's WCHAR type must be UTF16, because odbc driver's unicode type is UTF16
38         SELECT count(*) from mba.dsa.equifax_das_scr where mba_run_dtm > "&equifax_data_date";
ERROR: Column "'2018-05-11 19:51:09.744'" not found or cannot be accessed
NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.
39         quit;

Re: PROC FEDSQL failed to initiate

ChrisHemedinger — Thu, 16 Aug 2018 12:00:08 GMT

If you really need to compare against a literal value in single quotes, use the %TSLIT macro to resolve.

%let equifax_data_date= 2018-05-11 19:51:09.744;
%put %tslit(&equifax_data_date);

Result:

'2018-05-11 19:51:09.744'

So your case would be something like:

proc fedsql  libs=(mba mbadsa);
SELECT count(*) from mba.dsa.equifax_das_scr where mba_run_dtm > %tslit(&equifax_data_date);
quit;

But I suspect your comparison needs to use a datetime value, in which case you might need to do something like:

data _null_;
 x = "11May2018:19:51:09.744"dt;
 put x=;
 call symputx('equifax_data_date',x);
run;

Re: PROC FEDSQL failed to initiate

myelemes — Thu, 16 Aug 2018 14:20:18 GMT

I tried both approaches but proc fedsql just does not understand that I am passing in a macro variable.

50         proc fedsql libs=(mbadsa);
51         SELECT
52         count(*)
53         FROM
54             MBADSA.DSA.EQUIFAX_DAS_SCR
55         where
56         MBA_run_dtm >  %tslit(&equifax_data_date);
ERROR: Error occurred building the Execution Tree.
ERROR: Error occurred initiating Filter.
ERROR: General error
NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.
57         quit;

Re: PROC FEDSQL failed to initiate

ChrisHemedinger — Thu, 16 Aug 2018 14:35:24 GMT

Can you show how you set the value of that macro variable, and %PUT the raw value so we can see what you're passing in?

Re: PROC FEDSQL failed to initiate

myelemes — Thu, 16 Aug 2018 15:00:55 GMT

I receive a timestamp from a macro, I am pulling the latest timestamp value from a netezza table:

PROC SQL NOPRINT;
connect to netezza (&fwstAIConnect);
SELECT catt("'",(cat(put(datepart(lst_mba_run_dtm),yymmdd10.),' ',put(timepart(lst_mba_run_dtm),time12.3))),"'") INTO :&TIME_VALUE_VAR
FROM CONNECTION TO netezza
(
SELECT nvl(max(mba_run_dtm), '1900-01-01') AS lst_mba_run_dtm 
from &table_name
;
);
DISCONNECT FROM netezza;
QUIT;

&equifax_data_date - is the date I receive frm the macro, please see below the %PUT statement.

33 %let equifax_data_date = &equifax_data_date;
34 %put equifax_data_date .............................. &equifax_data_date;
equifax_data_date .............................. '2018-05-11 19:51:09.744'

Re: PROC FEDSQL failed to initiate

ChrisHemedinger — Thu, 16 Aug 2018 18:08:21 GMT

It still seems like you're comparing a datetime value (mba_run_dtm), which is represented as a number, to a string literal.

In database-specific SQL, that might work. But I'm not sure that the literal value in FEDSQL is converted to a legit datetime for comparison. It feels like you still need to do something to convert that '2018-05-11 19:51:09.744' value to native value. Maybe you need to add the TIMESTAMP keyword:

proc fedsql  libs=(mba mbadsa);
SELECT count(*) from mba.dsa.equifax_das_scr where 
 mba_run_dtm > TIMESTAMP&equifax_data_date;
quit;

(Not an expert on FEDQL here...but learning...)