binding COUNT

hi guys,

i want to count the number of times a particular data occurs in a table and display that data in a listbox

initially i planned to count the number of occurrences and bind that number to a variable for further manipulation but i have no idea how to do the binding part

how do i solve this?

thanx

Are you trying to update a db column that contains the count? If you're trying to do that, you could use a trigger or daily/nightly processing to do this based on how often you want it updated, how frequently the table is accessed, etc. If you're trying to do it in code, you could just set your variable equal to "select count ... where..." and mess with it in the code.

Thanks,
Sam Lester (MSFT)

|||

thanx sam!

Big queries return empty result set

Hello guys,

MS SQL server 2000 behavies strange with big queries that involves relatively large number of tables:
If I just enumerate columns I want to receive, the result set is empty. Adding * to the column list without making any change to where clause seems to solve the problem but I guess it's not the best practice.
The most amazing is that this behavior is not stable, so I suppose there's something to deal with server itself, not the application.
Has anybody suffered this problem and what solution was adopted?
Thanks for any information you can provide.Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.

Can you give me a rough idea of how big your database is (in gigabytes) and how big your result set is (rows, columns, and megabytes)? I'll try to reproduce the behavior here with a known good server.

-PatP|||Definitely my queries never get this volume. I have a join of about 15 tables with 10 columns in each. Empty recordset is an ocasional behavior, i mean today the query may work perfectly, tomorrow absolutely exact query with absolutely exact parameters without any modification made to the database fails.
I was told that this could be caused by particular, localized version of SQL server that we use (sql server 2000 SP3 spanish, 8.00.760). Could you confirm that?

Originally posted by Pat Phelan
Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.

Can you give me a rough idea of how big your database is (in gigabytes) and how big your result set is (rows, columns, and megabytes)? I'll try to reproduce the behavior here with a known good server.

-PatP|||Originally posted by Pat Phelan
Most of my queries are small (less than 200 columns and 500,000 rows), so maybe I'm not hitting the kind of volume that you are, but I've never heard of that behavior! I also rarely push beyond 30-50 tables in a single query with the exception of one purchased package that sometimes can create monsters.


:D

Why not POST the query...|||Originally posted by Brett Kaiser
:D

Why not POST the query...

As you wish:
--NON-EMPTY RESULTSET--

select bb.i_object_id as bb_aa, bb.i_object_type_id as bb_ab,
<skipped about 50 columns>
bl.i_operation_id as bl_ak, bl.integra_operation_id as bl_al,
* <-- this allows me receive data
from i_sysobject bb, part_list_item bc,
i_relation bd,
i_sysobject be, part_list bf,
i_relation bg, i_sysobject bh,
production_order bi, i_relation bj,
i_sysobject bk, operation bl
where bc.i_part_list_item_id = bb.i_object_id and
bf.state = ? and
bf.i_part_list_id = be.i_object_id and
bi.i_production_order_id = bh.i_object_id and
bl.i_operation_id = bk.i_object_id and
bc.i_part_list_item_id=bd.i_relation_child_object_ id and
bd.i_relation_parent_object_id=bf.i_part_list_id and
bf.i_part_list_id=bg.i_relation_child_object_id and
bg.i_relation_parent_object_id=bi.i_production_ord er_id and
bf.i_part_list_id=bj.i_relation_child_object_id and
bj.i_relation_parent_object_id=bl.i_operation_id
order by bf.i_part_list_id asc

--EMPTY RESULTSET--
select bb.i_object_id as bb_aa, bb.i_object_type_id as bb_ab,
<skipped about 50 columns>
bl.i_operation_id as bl_ak, bl.integra_operation_id as bl_al
from i_sysobject bb, part_list_item bc,
i_relation bd,
i_sysobject be, part_list bf,
i_relation bg, i_sysobject bh,
production_order bi, i_relation bj,
i_sysobject bk, operation bl
where bc.i_part_list_item_id = bb.i_object_id and
bf.state = ? and
bf.i_part_list_id = be.i_object_id and
bi.i_production_order_id = bh.i_object_id and
bl.i_operation_id = bk.i_object_id and
bc.i_part_list_item_id=bd.i_relation_child_object_ id and
bd.i_relation_parent_object_id=bf.i_part_list_id and
bf.i_part_list_id=bg.i_relation_child_object_id and
bg.i_relation_parent_object_id=bi.i_production_ord er_id and
bf.i_part_list_id=bj.i_relation_child_object_id and
bj.i_relation_parent_object_id=bl.i_operation_id
order by bf.i_part_list_id asc

bf.state = ? receives the SAME parameter in both cases.|||Everything is identity, isn't it...

Is one of these tables a driver? Like what you want to base your result set on?

Lots of sele referencing...

Maybe you can use derived tables..

SELECT * FROM (SELECT * FROM ...join the table that relate) AS A
LEFT JOIN (SELECT * FROM ..same thing) AS B
ON A.key = B.Key

Here's your code cleaned up some..Not sure if it's doing the same thing...

I think it is...and easier to see what you're trying to do

SELECT *
FROM i_sysobject bb
LEFT JOIN part_list_item bc ON bc.i_part_list_item_id = bb.i_object_id
LEFT JOIN i_relation bd ON bc.i_part_list_item_id=bd.i_relation_child_object_ id
LEFT JOIN i_sysobject be ON bf.i_part_list_id = be.i_object_id and
LEFT JOIN part_list bf ON bd.i_relation_parent_object_id=bf.i_part_list_id
LEFT JOIN i_relation bg ON bf.i_part_list_id=bg.i_relation_child_object_id
LEFT JOIN i_sysobject bh ON bi.i_production_order_id = bh.i_object_id
LEFT JOIN production_order bi ON bg.i_relation_parent_object_id=bi.i_production_ord er_id
LEFT JOIN i_relation bj ON bf.i_part_list_id=bj.i_relation_child_object_id
LEFT JOIN i_sysobject bk ON bl.i_operation_id = bk.i_object_id
LEFT JOIN operation bl ON bj.i_relation_parent_object_id=bl.i_operation_id
WHERE bf.state = ?

Best way to move databse and tlog files from default location

Thanks in advance for all help guys;
I need to figure out the best way to move files with no data loss from the
default installed location, in my case ms crm database files. When you
install MS CRM it does not give you the option if pick an install location of
files (disk placement wise).
Thanks so much,Most applications don't care where the underlying DB files are physically
located. It probably created them on the default location. Relocating the
files will require some down time. During a time when there is no user
activity or Alter your database and set it to DBO only (use rollback
immediate if necessary). Detach the database. Copy the files. Attach the
database back. Most of this can be done with Enterprise manager by changing
the properties of the database and running tasks.
"Seth at Sherwood" <SethatSherwood@.discussions.microsoft.com> wrote in
message news:6A7AA4A6-8ECF-4CCF-837B-AB9F45709904@.microsoft.com...
> Thanks in advance for all help guys;
> I need to figure out the best way to move files with no data loss from the
> default installed location, in my case ms crm database files. When you
> install MS CRM it does not give you the option if pick an install location
> of
> files (disk placement wise).
> Thanks so much,|||In SQL Server 2005 you can also use 'alter database set offline' and 'alter
database modify file'. See BOL 'Moving Database Files', 'Planned Relocation
Procedure' section.
Ben Nevarez
"Danny" wrote:
> Most applications don't care where the underlying DB files are physically
> located. It probably created them on the default location. Relocating the
> files will require some down time. During a time when there is no user
> activity or Alter your database and set it to DBO only (use rollback
> immediate if necessary). Detach the database. Copy the files. Attach the
> database back. Most of this can be done with Enterprise manager by changing
> the properties of the database and running tasks.
>
> "Seth at Sherwood" <SethatSherwood@.discussions.microsoft.com> wrote in
> message news:6A7AA4A6-8ECF-4CCF-837B-AB9F45709904@.microsoft.com...
> > Thanks in advance for all help guys;
> >
> > I need to figure out the best way to move files with no data loss from the
> > default installed location, in my case ms crm database files. When you
> > install MS CRM it does not give you the option if pick an install location
> > of
> > files (disk placement wise).
> >
> > Thanks so much,
>
>

Best way is table/view

Hello guys,
Thanks for you kind help in advance.
Actually I want to know, which is the best way to develop a report is.
From a view or from table.
And how can I edit query (from show query)?
dipitI prefer to use views, mostly because if the database structure or table/column names are to change it is easy to modify the view so that the reports are unaffected.

I find it gives more flexibility, and selections/projections can be performed on the database side - I try to avoid using Crystal to perform joins where possible.

I haven't looked too much into the "show query" until now; however the Select [column names] probably comes from whatever has been dragged onto the report, while the Where [clauses] come from whatever you have entered into Report -> Selection Formulas -> Record.