SQL-92: Update Statistics failing

Posted by Martyn Reid on 07-Mar-2017 04:39

Hi all,

I'm trying to run a statistics update via an ODBC linked server in SQL Server Management Studio like so:

SELECT *

FROM OPENQUERY(openedge, '

UPDATE TABLE STATISTICS AND ALL COLUMN STATISTICS FOR pub."customer"

')

But I'm getting the following exception back from the ODBC drivers:

Cannot process the object "

UPDATE TABLE STATISTICS AND ALL COLUMN STATISTICS FOR pub."customer"

". The OLE DB provider "MSDASQL" for linked server "openedge" indicates that either the object has no columns or the current user does not have permissions on that object.

I'm not aware of any permissions I need to set and the table name is definitely correct.  (I can run a select * from the same table and I get results).  I've searched google and can't find anyone else reporting the same problem.

Does anyone have any ideas what is going on?

All Replies

Posted by kirchner on 07-Mar-2017 07:10

Hi Martyn,

It might be the case OPENQUERY is expecting a result set back from the linked server but there is none.

Even though you get an error back, have you checked on OpenEdge side that the query has not been run? A PRO_SERVER log or querying the stats tables directly might confirm this.

Posted by Martyn Reid on 07-Mar-2017 07:13

Hi Kirchner,

Yeah, I was just thinking of this - thanks.  I'll execute outside of SSMS and see what happens.  Ta

btw: is there a command to update all table statistics only across all user tables?

I want the row counts to be correct for all tables with minimal commands

Posted by kirchner on 07-Mar-2017 07:17

You'll just see an info message saying X rows updated. Do not forget to COMMIT; after you update stats.

For all tables at once, you can query pub."_file" where not "_hidden" and build your script from there. It's easier to do if you have 4GL access to the database or a copy with the same tables.

Posted by Martyn Reid on 07-Mar-2017 07:21

Thanks kirchner,

I'm comfortable querying the system tables... indeed I've found the _systblstat table and where _property = 4 I can get reasonably accurate row counts as long as the statistics are up to date...

Can I use this command to update all tables at once?..

UPDATE TABLE STATISTICS;

COMMIT;

In your experience could this take a long time on a large-ish database?  (say 100GB)

Posted by kirchner on 07-Mar-2017 07:32

Wow, I never new you could just "UPDATE TABLE STATISTICS" and left out the table name. Indeed it update stats for all tables, just checked.

It also works with other update variants:

UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS;

Yes, it can take a long time, a lot of CPU and disk bandwidth and it might trash your buffer pool, but at least I've not seen it block other users.

To relieve the stress on the server you might want to execute one or a few tables at a time, and add an interval between the runs.

Posted by Martyn Reid on 07-Mar-2017 07:37

note for others: I've found that I can 'pass-though' commands in SSMS using the following syntax:

EXECUTE ( 'UPDATE STATISTICS;

COMMIT;' ) AT <linkedserver>

Posted by Martyn Reid on 07-Mar-2017 07:39

Cheers kirchner, I suspected that would be the case; probably one to run 'out of hours' then...

And by a long-time (and I know its subjective) but are we talking minutes or hours?  Say to update stats on a 10GB table with all 10GB of the specific table in memory?

Posted by kirchner on 07-Mar-2017 13:13

No, not hours, just a few minutes if you go with TABLE STATISTICS.

Once you add INDEX and ALL COLUMNS then we are talking big times.

This thread is closed