topic Connection to odbc sql server time out error in SAS Programming

Connection to odbc sql server time out error

devon59 — Wed, 28 Sep 2011 20:29:02 GMT

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

Connection to odbc sql server time out error

FriedEgg — Wed, 28 Sep 2011 22:01:56 GMT

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?

Connection to odbc sql server time out error

devon59 — Wed, 28 Sep 2011 22:24:33 GMT

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.

Connection to odbc sql server time out error

FriedEgg — Wed, 28 Sep 2011 22:29:22 GMT

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.

Connection to odbc sql server time out error

art297 — Wed, 28 Sep 2011 22:37:25 GMT

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

Connection to odbc sql server time out error

devon59 — Wed, 28 Sep 2011 22:53:30 GMT

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.