topic Re: SAS iHistorian stored procedure in SAS Data Management

SAS iHistorian stored procedure

just1986_2 — Wed, 23 Dec 2015 12:47:00 GMT

Hello,

I never post anything on this site, but I'm pretty stuck at a problem right now, so any help would be very much appreciated:)

I'm trying to connect to a procedure called LL_ZD_iHist_one_coil and I don't know why I'm not getting the data from the server. I suspected it was just not having the rights to access it. I already called the server guys who added me to all usergroups available, but still not luck.

The procedure I'm trying to run looks like this:

proc sql;
    connect to odbc(noprompt = "server=1.130.17.85 ;DRIVER=SQL Server;  Trusted Connection=Yes; DATABASE=Technical;");
    create table work.temp_historian as
    select *
    from connection to odbc
    (
    execute Technical.dbo.LL_ZD_iHist_one_coil
    @coil_id = '10805800',
    @track_no = '300',
    @taglist = 'LLZPALSV.ROLL_FORCE.F_CV',
    @interval = '10'
    );
quit;

The result is:

ERROR: CLI prepare error: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user
       'NT AUTHORITY\ANONYMOUS LOGON'.
SQL statement: execute Technical.dbo.LL_ZD_iHist_one_coil @coil_id = '10805800', @track_no =
       '300', @taglist = 'LLZPALSV.ROLL_FORCE.F_CV', @interval = '10'.
1349  quit;

The weird thing is, if I unquote one of the parameters from the SQL statement, I get the following response:

ERROR: CLI prepare error: [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function
       'LL_ZD_iHist_one_coil' expects parameter '@interval', which was not supplied.
SQL statement: execute Technical.dbo.LL_ZD_iHist_one_coil @coil_id = '10805800', @track_no =
       '300', @taglist = 'LLZPALSV.ROLL_FORCE.F_CV'.

So SAS is able to at least communicate with the server. Combine that with the fact that I'm added to nearly any usergroup on this server and the fact that I can run other stored procedures from my SAS system leaves me out of options to try... Please let me know how to solve this problem?

Thanks,

Just

Re: SAS iHistorian stored procedure

RW9 — Wed, 23 Dec 2015 13:10:47 GMT

Hi,

Well, this isn't an SQL Server forum. Does the code you pass through:

    execute Technical.dbo.LL_ZD_iHist_one_coil
    @coil_id = '10805800',
    @track_no = '300',
    @taglist = 'LLZPALSV.ROLL_FORCE.F_CV',
    @interval = '10'

Actually work if you use the SQL Server SQL interface, i.e. does that run on the database without problems. If so then you would need to loko at your permissions. You give no logon information in the connect statement:

connect to odbc(noprompt = "server=1.130.17.85 ;DRIVER=SQL Server;  Trusted Connection=Yes; DATABASE=Technical;");

And the log is telling you that anonymous doesn't have access rights to the db.

Login failed for user
       'NT AUTHORITY\ANONYMOUS LOGON'

Re: SAS iHistorian stored procedure

just1986_2 — Thu, 24 Dec 2015 16:13:58 GMT

Thanks for your reply RW9. I just asked IT for SQL Server, due to holidays it might take them a while to get it installed.

It is correct that I haven't given any logon information, but I don't think that is necessary if I'm added to the rigth user-groups. But I'll check with the SQL Server Mgmt program.