Datasource Error

I'm getting this message when I try to enter an SQL statement for a new
report:
"There is an error in the query. MinimumCapacity must be non-negative."
My SQL statement is:
"CALL GET_INSTALLMENTS_HEADER(?,?)"
I've tried using both IBM DB2 Provider and HiT Softwares OLEDB/400 provider
and get the same error.
Any ideas?
Thanks in advance,
MikeI am going against Sybase using OLEDB so although it is not the same
database as you are going against it is using an OLEDB provider. I do not
use the syntax you are using. I use the following syntax:
pr_test ?, ?
If you already have report parameters created then go to the parameters tab
of the dataset (click on the ...) and map the query parameter to the report
parameter. If in the generic query designer I have found I have to leave the
command type as text rather than setting it to stored procedure.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike" <MichaelLopez@.inds.com> wrote in message
news:O7%23%233%23mxEHA.3212@.TK2MSFTNGP09.phx.gbl...
> I'm getting this message when I try to enter an SQL statement for a new
> report:
> "There is an error in the query. MinimumCapacity must be
non-negative."
> My SQL statement is:
> "CALL GET_INSTALLMENTS_HEADER(?,?)"
> I've tried using both IBM DB2 Provider and HiT Softwares OLEDB/400
provider
> and get the same error.
> Any ideas?
> Thanks in advance,
> Mike
>|||Hi, Bruce. Thanks for responding.
That's how I did it, though I discovered it through empirical means.
We've used Crystal Reports for some time, and it's not a bad product at all.
But we always had to jump through hoops to gain access to an IBM AS/400
(iSeries) machine, and forget about accessing stored procedures on the 400.
We never could natively. Yesterday was my first attempt at accessing the
same system using RS and got it to work in about an hour.
That's a good sign. My next step is to figure out how to emulate CR's "Group
Tree" functionality.
Good chance this group will be hearing from me soon.
Thanks again,
Mike
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OGgUbenxEHA.824@.TK2MSFTNGP11.phx.gbl...
>I am going against Sybase using OLEDB so although it is not the same
> database as you are going against it is using an OLEDB provider. I do not
> use the syntax you are using. I use the following syntax:
> pr_test ?, ?
> If you already have report parameters created then go to the parameters
> tab
> of the dataset (click on the ...) and map the query parameter to the
> report
> parameter. If in the generic query designer I have found I have to leave
> the
> command type as text rather than setting it to stored procedure.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mike" <MichaelLopez@.inds.com> wrote in message
> news:O7%23%233%23mxEHA.3212@.TK2MSFTNGP09.phx.gbl...
>> I'm getting this message when I try to enter an SQL statement for a new
>> report:
>> "There is an error in the query. MinimumCapacity must be
> non-negative."
>> My SQL statement is:
>> "CALL GET_INSTALLMENTS_HEADER(?,?)"
>> I've tried using both IBM DB2 Provider and HiT Softwares OLEDB/400
> provider
>> and get the same error.
>> Any ideas?
>> Thanks in advance,
>> Mike
>>
>sql

dataset with parameterized top query

I have a report based on a dataset that has a "top()" statement in it.

SELECT TOP (10) name, val FROM tab ORDER BY val DESC

I wanted wanted to return a report with 10 rows as the default.

But allow the user to change the default and regenerate the report with more rows.

I tried the following;

SELECT TOP (@.N) name, val FROM tab ORDER BY val DESC

Normally in VS2005, the paraemeters work fine for things like the WHERE clause. But when i do the TOP(@.N) the query/report parameter synchronization messes up. If i manual go to the parameter tab of the dataset and configure the matching between query and report params it works. But doing anything to the dataset resets this. Its getting very tiring.

Any advice for a newbie would be appreciated.

John

Hi,
you cannot do that without building dynamic SQL which is not best practice leaving you exposed to risks.
What you could do if your TOP parameter list is a constrained list like top = 10 or 20 or 50 or 100 or ALL would be to build your query with a series of If or Case statements where you evaluate the value of the TOP parameter and then build the select top query accordingly.
Another related question I have posted in this forum was how can I get the "Other" lumped into an extra result row. No easy answer so far, I wait for the silver bullet.
Philippe|||

Lets say i opt for the constrained list of top values as you suggest and write a use if/case as you suggest.

Would this not be also be dynamic SQL?

I saw another thread where someone was looking for a column that with a rank of the sorted rows.

rank name val

1 sam 9.4

2 ted 5.8

3 bob 2.4

if i could do this in the dataset sql, i could return all rows, then i could filter the rows in the report table with rank <= @.N

|||

I tried your if suggestion. Works in the dataset designer wizard in VS2005 just after entery sql code. But then testing the report it doesnt.

entering the dataset wizard again get me this error, "The Compound statement SQL construct or statement is not supported."

code:

if (@.N = 10)

begin

SELECT TOP (10) name, val FROM tab ORDER BY val DESC

end

if (@.N = 20)

begin

SELECT TOP (20) name, val FROM tab ORDER BY val DESC

end

if (@.N = 999)

begin

SELECT name, val FROM tab ORDER BY val DESC

end

|||I guess there are many ways to do it, here is a working example:

USE AdventureWorks
DECLARE @.TOP INT
SET @.TOP = 10

SELECT TOP (CASE @.TOP WHEN 10 THEN 10 WHEN 20 THEN 20 WHEN 50 THEN 50 ELSE 2147483647 END)
c.LastName, SUM(s.SubTotal) as SubTotal
FROM Sales.SalesOrderHeader s
INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
GROUP BY c.LastName
ORDER BY SUM(s.SubTotal) DESC;

Philippe|||

That is much simpler. Thx

I have one question however;

this works in a report i configured

SELECT TOP (CASE @.TOP WHEN 10 THEN 10 WHEN 20 THEN 20 WHEN 50 THEN 50 ELSE 2147483647 END)
c.LastName, SUM(s.SubTotal) as SubTotal
FROM Sales.SalesOrderHeader s
INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
GROUP BY c.LastName
ORDER BY SUM(s.SubTotal) DESC;

why doesnt this form work

SELECT TOP (@.TOP)
c.LastName, SUM(s.SubTotal) as SubTotal
FROM Sales.SalesOrderHeader s
INNER JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
GROUP BY c.LastName
ORDER BY SUM(s.SubTotal) DESC;

it works in the dataset wizard. when i test the query the wizard prompts for @.top and the results work

however i run the run the report and the i get error. You mentioned dynamic queries previously. is you form not dynamic? The @.top param has to be evaluated at runtime either way.

john

|||

If you're using SQL 2005, I suggest looking at ranking functions. It's quite handy, with ROW_NUMBER()

http://www.aspfaq.com/sql2005/show.asp?id=11

http://www.sql-server-performance.com/ak_ranking_functions.asp

http://sqljunkies.com/Article/4E65FA2D-F1FE-4C29-BF4F-543AB384AFBB.scuk

I don't think the previous code is dynamic query, as TOP was a declared variable

not something like

EXEC 'SELECT TOP ' + @.TOP + ' * FROM table'

where you may get unwanted input (injection attack?)

DataSet has no fields

Please Help. I am calling a stored procedure which creates a SQL statement and
then runs
exec (@.SQL + @.Where + @.Order)
Is there anyway to manual create fields. The Stored procedures returns the
same column names and number of columns?
--
Thanks,
Jon AWhy don't you set the proc to just do a select of the fields you want until
the report is built, then change the proc back to the exec(string) command.
That way you'll have access to the fields in the builder.
"Jon A" wrote:
> Please Help. I am calling a stored procedure which creates a SQL statement and
> then runs
> exec (@.SQL + @.Where + @.Order)
> Is there anyway to manual create fields. The Stored procedures returns the
> same column names and number of columns?
> --
> Thanks,
> Jon A|||Alternatively, you can go into the properties of the dataset that you want
fields for (by clicking the elipsis next to the name of the dataset on the
Data tab in report design) and go to the "Fields" tab. Here you can define
your fields manually and this will enable you to use them in your report
design. Be careful to name your fields exactly as they are returned from the
database otherwise RS will run into problems.
Also, another thing to try is to click on the "Refresh Fields" button on the
Data tab in report design. This executes the query and "figures out" what
fields are returned where it may not be immediately obvious from your query
design (especially where using Dynamic SQL is concerned).
Cheers
--
Tim McOwan
"Mary Bray [SQL Server MVP]" wrote:
> Why don't you set the proc to just do a select of the fields you want until
> the report is built, then change the proc back to the exec(string) command.
> That way you'll have access to the fields in the builder.
> "Jon A" wrote:
> > Please Help. I am calling a stored procedure which creates a SQL statement and
> > then runs
> > exec (@.SQL + @.Where + @.Order)
> > Is there anyway to manual create fields. The Stored procedures returns the
> > same column names and number of columns?
> >
> > --
> > Thanks,
> > Jon Asql

DATABASEPROPERTYEX Error but not using this function...

Hi Everyone,
I'm creating a Report in Visual Studio.Net 2005 using an MS SQL 7.0 database
as the datasource. My SQL statement isn't anything crazy, just a Left Outer
Join between two tables (see below), but when I go into Preview mode on the
report I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'MyDatasource'.
'DATABASEPROPERTYEX' is not a recognized function name.
I'm not using the DATABASEPROPERTYEX function in my SQL statement... Any
suggestions?
Datasource: MS SQL 7.0
Report created on VS.Net 2003
To be published on MS SQL 2005 Reporting Server
And here is my SQL statement:
SELECT MASTER.asset_no, MASTER.b_name, MASTER.UPP, PAYHIST.post_date,
PAYHIST.effective_date, PAYHIST.amount, PAYHIST.principal,
PAYHIST.interest, PAYHIST.late_fee, PAYHIST.suspense,
PAYHIST.trans
FROM MASTER LEFT OUTER JOIN
PAYHIST ON MASTER.asset_no = PAYHIST.asset_no
ORDER BY MASTER.asset_no, PAYHIST.post_date
I used a wizard to create this report, so does VS.Net 2003 do something
wierd in the background that MS SQL 7.0 might not like? Just curious ...
Thanks --
AlexHi. Mistake in my last post, I initially said I was using Visual Studio.Net
2005, but it's actually 2003. I corrected this at the bottom of the
message, but forgot to at the top. So to correct, this is being done under
VS.Net 2003 as opposed to 2005.
Sorry 'bout that .. Alex
"Alex" <samalex@.gmail.com> wrote in message
news:%23WjB7MR2HHA.5164@.TK2MSFTNGP05.phx.gbl...
> Hi Everyone,
> I'm creating a Report in Visual Studio.Net 2005 using an MS SQL 7.0
> database as the datasource. My SQL statement isn't anything crazy, just a
> Left Outer Join between two tables (see below), but when I go into Preview
> mode on the report I get the following error:
> An error has occurred during report processing.
> Query execution failed for data set 'MyDatasource'.
> 'DATABASEPROPERTYEX' is not a recognized function name.
> I'm not using the DATABASEPROPERTYEX function in my SQL statement... Any
> suggestions?
> Datasource: MS SQL 7.0
> Report created on VS.Net 2003
> To be published on MS SQL 2005 Reporting Server
> And here is my SQL statement:
> SELECT MASTER.asset_no, MASTER.b_name, MASTER.UPP, PAYHIST.post_date,
> PAYHIST.effective_date, PAYHIST.amount, PAYHIST.principal,
> PAYHIST.interest, PAYHIST.late_fee, PAYHIST.suspense,
> PAYHIST.trans
> FROM MASTER LEFT OUTER JOIN
> PAYHIST ON MASTER.asset_no = PAYHIST.asset_no
> ORDER BY MASTER.asset_no, PAYHIST.post_date
> I used a wizard to create this report, so does VS.Net 2003 do something
> wierd in the background that MS SQL 7.0 might not like? Just curious ...
> Thanks --
> Alex
>
>|||Solution Found...
After searching more in Google Groups I found an older post with the fix.
Here it is for anyone who might run across this issue in the future:
You are accessing a SQL Server 7.0 or older, correct? Just install SP1 of RS
2000 on report server and report designer machines and it will work.
The workaround for RS 2000 _without_ SP1 is as follows: Go to the "Data
Options" tab of the "Dataset" dialog in report designer. On the Data Options
tab you will see that all settings contain "Auto" (and report server would
therefore try to auto-detect the collation settings from the database
server). Replace the Auto-settings with the following settings (e.g. if your
SQL 7.0 database collation is "SQL_Latin1_General_CP1_CI_AS"):
Collation = Latin1_General
Case sensitivity = false
Kanatype sensitivity = false
Width sensitivity = false
Accent sensitivity = true
Take care -- Alex
"Alex" <samalex@.gmail.com> wrote in message
news:%23$GDP9R2HHA.140@.TK2MSFTNGP02.phx.gbl...
> Hi. Mistake in my last post, I initially said I was using Visual
> Studio.Net 2005, but it's actually 2003. I corrected this at the bottom
> of the message, but forgot to at the top. So to correct, this is being
> done under VS.Net 2003 as opposed to 2005.
> Sorry 'bout that .. Alex
> "Alex" <samalex@.gmail.com> wrote in message
> news:%23WjB7MR2HHA.5164@.TK2MSFTNGP05.phx.gbl...
>> Hi Everyone,
>> I'm creating a Report in Visual Studio.Net 2005 using an MS SQL 7.0
>> database as the datasource. My SQL statement isn't anything crazy, just
>> a Left Outer Join between two tables (see below), but when I go into
>> Preview mode on the report I get the following error:
>> An error has occurred during report processing.
>> Query execution failed for data set 'MyDatasource'.
>> 'DATABASEPROPERTYEX' is not a recognized function name.
>> I'm not using the DATABASEPROPERTYEX function in my SQL statement... Any
>> suggestions?
>> Datasource: MS SQL 7.0
>> Report created on VS.Net 2003
>> To be published on MS SQL 2005 Reporting Server
>> And here is my SQL statement:
>> SELECT MASTER.asset_no, MASTER.b_name, MASTER.UPP, PAYHIST.post_date,
>> PAYHIST.effective_date, PAYHIST.amount, PAYHIST.principal,
>> PAYHIST.interest, PAYHIST.late_fee,
>> PAYHIST.suspense, PAYHIST.trans
>> FROM MASTER LEFT OUTER JOIN
>> PAYHIST ON MASTER.asset_no = PAYHIST.asset_no
>> ORDER BY MASTER.asset_no, PAYHIST.post_date
>> I used a wizard to create this report, so does VS.Net 2003 do something
>> wierd in the background that MS SQL 7.0 might not like? Just curious ...
>> Thanks --
>> Alex
>>
>