ERROR: CLI cursor fetch error: [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned

[Frankbeans]

more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or
when the subquery is used as an expression.

Here's the deal.  I am getting this error when trying to run this code in SAS...

proc sql;
connect to odbc(dsn=example );
create table work.visit as select * from connection to odbc

(SELECT * FROM Tbl_Visit);
disconnect from odbc;
quit;

I have also tried to read the table interactively and get the same error message.  The thing is this is code that has been used countless times on the same data set and it ran perfectly up until about a month ago. Also, there is batch programming we use that reads in all of the tables from this "example" database and they all read in fine with the exception of this one table. Does anyone have an idea what could have happened in the last month to that specific table that would cause this? There have been approximately 25 entries made to the table since the last time it worked properly.

[ballardw]

---

@Frankbeans wrote:

I have also tried to read the table interactively and get the same error message.  The thing is this is code that has been used countless times on the same data set and it ran perfectly up until about a month ago. Also, there is batch programming we use that reads in all of the tables from this "example" database and they all read in fine with the exception of this one table. Does anyone have an idea what could have happened in the last month to that specific table that would cause this? There have been approximately 25 entries made to the table since the last time it worked properly.

---

Code that works and then fails means something changed. In this case it likely means that the content of the data set in question has changed such that when the subquery (really , show entire procedure steps it is moderately obvious that some code was removed) " (SELECT * FROM Tbl_Visit) " used to return single observations where it was used but now doesn't.

Likely fix is modify the subquery to force a single observation as the result.

Or show the whole code and perhaps other approaches can be suggested based on your complete code.

[Frankbeans]

Not sure what you mean by showing the whole code.  That is the whole code in my mind.  Again, this same code works with every other table in that database but this one and even then the same code worked up until a month ago.

[ballardw]

When  you use something like this

(SELECT * FROM Tbl_Visit)

SAS treats that as a subquery. Depending on where it appears in code the SQL rules SAS uses expects a single value.

Code like

roc sql;
connect to odbc(dsn=example );
create table work.visit as select * from connection to odbc

(SELECT * FROM Tbl_Visit);
disconnect from odbc;
quit;

is treating that subquery as an expression. So something has changed in the contents of tbl_visit or the ODBC EXAMPLE such that this no longer returns a single value. It is the content of the table, and apparently only that table that has changed. So look at the contents of the sources.

Or reconsider if that subquery expression needs to be rewritten to deal with the changes to the data sources.

Or show the the complete LOG for the step generating this error to see if there is more diagnostic information that you are skipping over. Copy the text from the LOG starting at Proc SQL and through the end of the last note, message, warning or error. On the forum open a text box using the </> icon and paste the text.

[Frankbeans]

 ok, I get that something has changed but so far, all of my trouble shooting steps seem to point to the underlying table changing in some fashion to have caused this. Here are some of the log details...

8 proc sql;
9 connect to odbc(dsn=example );
10 create table work.visit as select * from connection to odbc
11
12 (SELECT * FROM Tbl_Visit);
ERROR: CLI cursor fetch error: [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned
more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or
when the subquery is used as an expression.

13 disconnect from odbc;
14 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 1.73 seconds
cpu time 0.25 seconds

I also tried accessing the data set using a libname and data step and still receive the same error...

1 libname JSY odbc dsn="example" owner=dbo;
NOTE: Libref JSY was successfully assigned as follows:
Engine: ODBC
Physical Name: example
1 ! run;

NOTE: 7 table(s) not displayed/returned because the name is too long.
2 data visit;
3 set JSY.Tbl_visit;
4 run;

ERROR: CLI cursor fetch error: [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned
more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or
when the subquery is used as an expression.
NOTE: The DATA step has been abnormally terminated.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 7269 observations read from the data set JSY.Tbl_visit.
WARNING: The data set WORK.VISIT may be incomplete. When this step was stopped there were 7269
observations and 192 variables.
NOTE: DATA statement used (Total process time):
real time 1.83 seconds
cpu time 0.28 seconds

The last time this ran correctly the observations were 10714 so there's that.

The only way I can see the data is to view it interactively in SAS. I also had one of our SQL programmers use similar SQL code in that environment and he was able to read it in with no problems. 

[Frankbeans]

I mean I am sure we can implement a work around but understanding what has happened to have caused this would be helpful overall in case this becomes a new feature with other tables.

[ballardw]

Is TBL_VISIT a static table in the external source or the result of a query executed when called? It may be the definition of the query changed. Or even just some option in the ODBC connection.

But talk to the admin for the external data source as the data resulting from the ODBC connection has changed in some manner.