Connection to odbc sql server time out error

[devon59]

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

[FriedEgg]

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]

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]

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]

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]

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