BookmarkSubscribeRSS Feed
devon59
Calcite | Level 5

Hi,

I am trying to run a simple statement in SAS on the data that I have

connected to sql server using this line of code.

LIBNAME qb ODBC  DSN='quick' schema=da;

I have no problem connecting.  However, when I just tried to run a
simple

data temp;set qb.client;keep ID adddate;run;

I kept getting this error message.

/**********************************************/
WARNING: During read: [Microsoft][ODBC SQL Server Driver]String data,
right truncation
ERROR: CLI cursor fetch error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Could not continue
        scan with NOLOCK due to data movement.

What does it mean and how can I fix it?

Any help would be greatly appreaciated.

Thank you

5 REPLIES 5
FriedEgg
SAS Employee

This is a pretty bad SQL server error message usually caused I believe by highly fragmented tables and or indexes. If you have one consult with your SQL sever DBA. Also queries using nolock are usually not desirable.  Is this SQL server utilized as a warehouse for data? Is it denormalized?

devon59
Calcite | Level 5

I'm new to this data so I don't know exactly how SQL server DBA maintain the database.  What type of thing should I tell/recommend him to do?  He doesn't know SAS so I think he might blame it on SAS. 

Thank you so much for your reply.

FriedEgg
SAS Employee

Have him confirm that the issue is not within the sql server database itself, as SAS is reporting it is.  Have him check the table and indexes for fragmentation.  Give him the information from your log

WARNING: During read: [Microsoft][ODBC SQL Server Driver]String data,

right truncation

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

Server]Could not continue

        scan with NOLOCK due to data movement.

You query: data temp;set qb.client;keep ID adddate;run;

is equivalant to select id, adddate from client;

Check that NOLOCK should be part of this query, if not check whether this is set by database or odbc bridge or possibly SAS (but I don't think it would be).

To get more information about the query's execution from sas use the sastrace option.

devon59
Calcite | Level 5

Thank you!!  I will try to tell him that.  I tried execute some simple commande on other tables and it worked fine.  Must just be the problem with that table.

Thank you again.

art297
Opal | Level 21

Possibly the following will be of help: http://support.sas.com/kb/30/741.html

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4171 views
  • 0 likes
  • 3 in conversation