10-31-2012 02:37 AM
This is kind of weird and frustrating but I am not able to find any solution on this error.
I have a library XLSLIB created using libname --
LIBNAME XLSLIB OLEDB PROPERTIES="data source"="\\melbappp007\testing\excel\YC_TEST_EXCEL.xls"
PROVIDER_STRING="Excel 8.0" PROMPT=NO PROVIDER="Microsoft.Jet.OLEDB.4.0";
This libname is working perfectly fine when I use it in EG. It in fact reads the data in excel file as well. But when I use the same libname in DI Studio , I am getting this error.
ERROR: User cancelled operation.
ERROR: Error in the LIBNAME statement.
I tried to create a OLEDB server and connection in SMC and then created the same library which creates above libname. But when I try to register tables under this library, it gives me same error.
Haven't found much about this error on sas support site as well ....
Its SAS 9.2 with DI Studio 4.21 and EG4.3
Tried the same on couple of other machines, but same pattern is found.
Any help is appreciated.
11-08-2012 05:00 PM
I got the solution of this solution.
It was happening due to combined effect of -
1) Keeping few EG sessions on. These sessions had successfully executed the libname statements earlier. So possibly there was some kind of lock held on the excel file, by these EG threads.
2) Access issue- The location where excel file was kept, had limited access.
After I placed the file at some other location and closed all the EG sessions(and other DI studio sessions as well) referring to it, I was able to run the code in DI Studio as well register tables(Excel worksheet) from the library. Though the description of the error was really a confusing one.