Problem with openquery and Progress odbc
-
A basic Sum()/Group By within an OPENQUERY returns a precision error (see bottom of this message), but only when the group by is done within the Progress query...
select * from
openquery(clca,'select acct, acct-unit1, sum(dom-amount)
from ledger where trans-date between 09/03/2006 and 10/07/2006 group by acct, acct-unit1')
A workable alternative is to take the sum/group by out of the Progress query and put it in the SQL Server portion, as shown here...
select select acct, [acct-unit1], sum([dom-amount])
from openquery(clca,'select acct, acct-unit1, dom-amount
from ledger where trans-date between 09/03/2006 and 10/07/2006')
group by acct, [acct-unit1]
However, this second approach is too ineffecient because there are so many rows that are returned to SQLServer that it takes to long to run. It would be better to do the grouping first (in Progress) and then pull the results to SQL Server. However, this causes the error...
Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' supplied invalid metadata for column ''. The precision exceeded the allowable maximum.
OLE DB error trace [Non-interface error: Column '' (ordinal 3) of object 'select acct, acct-unit1, sum(dom-amount) from ledger where trans-date between 09/03/2006 and 10/07/2006 group by acct, acct-unit1' reported an unsupported value for PRECISION of 50].
Anybody know how to get this to work? If I connect directly to Progress, the query works fine. The problem is SQL Server using the ODBC to translate the results.
Thanks
smv929 -
You could try returning:- SUM(CAST(dom-amount * 100 AS INT)) AS dom-amount
from the remote query and then dividing by 100.0
-
I'm having this problem as well. Have you found a solution to this?
Thanks in advance,
mel
-
no, I haven't. I'm having to pull the fields (without the aggregate functions) from Progress and use SQL Server to perform the Sum/Group By.
smv929 -
Have you run that query directly on the progress server? Meaning - is the error a Progress error (like - the amount of the Total is too large for the data type), or is SQL not correctly identifying what data type to associate it to (like int for something too big to be an int).
I'd try putting the cast around the sum, and possibly move it up to a bigint (i.e. Cast(sum(<field>
as BigInt)) , or whatever notation progress uses to denote a bigint. Or cast it to a double in Progress, and cast it back to an int/bigint once it's over in SQL.----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Thank you very much for your fast replies.
I tried casting the sum as you suggested and it worked - the error is gone.
But now the statement takes even more time! I have to admit that I'm a bit confused now.
If I use the Progress SQL Explorer Tool (jdbc) to execute it, it takes about a second - with linked server + odbc it takes 10 minutes!
Does anybody have an idea why there is such a great difference?
-
Well, I forgot to mention that I am not using openquery because the statement is generated by the erp system in my case and that's why I am using the four-part naming in combination with an sql view (the where-clause would otherwise always be outside the openquery-statement). I tested it with openquery and then the statement is fast, so at least this problem should be eliminated. But why is there such a difference between openquery and the four-part naming?
-
Could you please tell me the exact CAST syntax. I'm still having trouble. I'm using...
select *
from openquery(cvsc,'select cast(sum(price * qty-ordered) as bigint) from coitem price where ship-date =8/20/2007')
Also, how can I make Progress return a column name for the SUM? It doesn't seem to allow aliases as in SQL Server.
Thanks!
smv929 -
Mel - not using the OPENQUERY syntax means the data is being pulled across the wire unprocessed, and you're having SQLSERVER doing the work. OpenQuery should "delegate" the query execution to progress and only get the results back.
SMV - you've got the right syntax. What i think is going on is that bigint is a MS SQL data type (or the name MSSQL gives it). Microsoft has 4 (TINYINT, SMALLING, INT, BIGINT). Not sure how many progress uses. More likely than not - you should be able to use INT, or if it's currency values, look at float/double/money/currency types. Again - those are the MS names - you might need to hit the progress support site to get clarity on what the right name is to use.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
SMV,
the syntax should be correct - try it that way:
select * from openquery(cvsc, 'select cast(sum(price * qty-ordered) as integer) as aliasname from ...')
the alias should be working - i had no problems with it (which version of progress are you using?). if integer is still too small try numeric instead of it.
Matt,
I thought that if I use collation compatible a remote query should be made as well, which seems to be the case (looking at the execution plan in query analyzer) because I created a view on progress and just want to use this and apply a where-clause. The difference in the execution plan is that it shows a remote scan for the openquery case and a remote query for the case without openquery.
-
Thanks, guys, for the syntax confirmation. Our Progress version is 9.1C and were usign the pre-SQL92 version of the Merant ODBC drivers. This combination does not allow the CAST function or aliases.
I was able to cheat and wrap the aggregate in a STRING function. For example...
select * from openquery(db,'select string(sum(price * qty)) from coitem...')
At some point, we're going to upgrade, but it's beyond my power. Thanks again!
smv929 -
Mel - I might be misunderstanding you, but unless the view, defined on the progress server, has the WHERE clause - everything's going to pull "over the wire", and the where clause willl be applied SQLServer-side?
They say it better than I can, so here's a bit from sql-server-performance.
<quote>
By default, when you run a distributed query using a linked server, the query is processed locally. This may or may not be efficient, depending on how much data must be sent from the remote server to the local server for processing. Sometimes it is more efficient to pass through the query so that it is run on the remote server. This way, if the query must process many rows, it can process them on the remote server, and only return to the local server the results of the query. The OPENQUERY function is used to specify that a distributed query be processed on the remote server instead of the local server. [7.0, 2000, 2005] Updated 8-7-2006
</quote>
And - collation compatible has to deal with character sets and default ordering, formatting, etc. Not setting that might mean that all of the data AND all of the formatting/collation info has to be sent down the wire, so that SQL server knows how to handle the data (so that, for example, SQLServer knows whether dates are being stored as mm/dd/yyyy or dd/mm/yyyy). It really doesn't have to do with having the query process remotely.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
If the option is selected, (which is always recommended if the collations are the same on both servers), then the WHERE clause is applied on the remote server. This, of course, means that much less data is transmitted over the network, often greatly speeding up the distributed query.
(this is from sql-server-performance as well)
This lead me to thinking that the query would be executed on the remote server, but this seems to be wrong.
If I use a simple "select * from table where ..."-statement then it speeds up the query because less data is sent.
If I use a view, sql server simply seems to take the statement from the view and execute that one - the tables in it which do not include the where-clause-column seem to cause the long runtime. At least that is what I think. But then the execution plan in sql query analyzer seems to be wrong.
-
I always use dynamic sql to build an OPENQUERY so that the statement it sends has the where clause in order to ensure that the less data is retrieved. I don't think there's a way around having to pass the where clause unless you're willing to bring the entire table over.
smv929 -
Hi Team,
Select tmp.* from openquery( [LinkedServerName], 'select * from viewname1' ) as tmp. When I executed this query, I am getting below error. Could you please help?
Msg 7354, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "LinkedServerName" supplied invalid metadata for column "columnname". The precision exceeded the allowable maximum.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply