BookmarkSubscribeRSS Feed
just1986_2
Calcite | Level 5

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

2 REPLIES 2
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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'
just1986_2
Calcite | Level 5

 

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 930 views
  • 0 likes
  • 2 in conversation