Another simple SQL query (MS Access)

Hi. I'm having trouble doing another SQL query. It needs to compare a height against the average height of a group. If it is larger, than it is shown. I can create the sub query but it returns an error when going to the outer query.

Select avg(height)
from Materials
group by MCode

What is an outer query that would compare a single height against that groups average height and display the rows if the single height is greater?oh yeah, another thing. My outer query select can only have 2 columns (material, height)|||select material
, height
from Materials ZZ
where height
> ( select avg(height)
from Materials
where MCode = ZZ.MCode )

another simple snapshot replication question

i have a publisher agent that generates 61 articles for publication
without a problem.
however i'm having trouble understanding exactly how the distribution
agent executes. when executed manually once, it ran successfully, and
the subscriber got the data.
is it possible to run the distribution agent again to re-push/pull data
for the subscribers without running the publisher agent? when i try to
re-run the distributor agent, i get the success message "No replicated
transactions are available." have the snapshot files become
unavailable, or is it just something where the distribution agent knows
that they are older than the subscriber last obtained?
if it's possible to have a distribution agent push/pull data from a
single collection of publisher snapshot articles multiple times, i
would greatly appreciate knowing how that's done.
thanks in advance for any help,
jason
Jason,
to have the distribution agent pick up any new snapshot files, you need to
reinitialize this particular subscriber (sp_reinitsubscription). However if
this is a regular process, perhaps you should just use snapshot replication
instead? Or are you trying to avoid table locking?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||paul,
sorry, i should have specified that this is a snapshot replication. but
because of the very reason you mentioned, the table locking, i was
hoping that i could tweak the distribution agent steps without having
to republish the data (avoiding further table locks).
does this information suggest any particular solutions as possible /
superior?
thanks for the response,
jason
|||Sounds like transactional would be more beneficial. Unless there are loads
of updates to the same rows, you'll notice an improvement in performance
versus regualr snapshots. Also you'll avoid locking issues by using the
concurrent snapshot generation option when initializing/reinitializing.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||i thought that might be the case. but doesn't transactional replication
come with a performance hit to the updates in the publisher database?
this replication is just to get "production-like" data from the
production database into the development and staging databases. we
don't want to perpetually diminish the performance of the production
database for a process we probably only need to run every couple of
weeks/months. but at the same time, the database is accessed 24-7, so
if we can do the replication without record locking, that's highly
preferable.
another option i'm looking into is a DTS task, which also seems like it
could be done without locking the tables. but i'm still sorting out the
details on DTS with regard to object copies vs. data transformations,
and how to handle identity columns, etc.
sorry for that long contextual tangent. so would you still recommend
transactional replication for a task that really only needs to
replicate data once or twice a month?
thanks,
jason
|||With no downtime of the production site allowed and representative data
required at fortnightly intervals, I'd restore full database backups - much
easier solution with no hit on the production database.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||that's an awesome idea. we use some sql-aware third party backup
software, so if i can figure out how to restore a single database file
from there in a way that sql can use, that's definitely the solution
i'll choose.
thanks for the suggestion!

Another problem with "Transfer SQL Server Objects Task"

Hi everyone,

I'm currently having some trouble with the "Transfer SQL Server Objects Task".
All I wanna do is to copy a simple database from one server (SQL2000) to another one (SQL2005).

A small excerpt from my settings:

DropObjectsFirst: true
ExistingData: Replace
All table options are set to true
.
No matter what I do the package always appends the data from the source to the destination database.

Is this a bug or am I missing something ?

Thanks in advance,
Kevin

To copy database, you should use "Transfer Database" task instead of "Transfer SQL Server Objects Task".

(I will investigate and follow-up on the issue related to appending of data in the table you are transferring.)

|||Hi Kaarthik,

I know I could use the "Transfer Database" task instead, but only in a few cases.
Most of the time I'll have to decide whether or not I want to copy certain objects (e.g. SPs) as well.

I hope this one gets fixed soon...

analysis services installation trouble.

I am posting this question here because I got absoultely no help in the olap
section.
Please excuse me for posting here but I had no choice because I got
absolutely no response there.
TIA and please help..
I am having trouble installing sql server analys services (olap) on windows
xp machine.
Here are the installation steps.
1. insert sql server 2000 cd in the cd drive.
2. I get html page listing titled 'msdn subscriptions'.
3. select Microsoft? SQL Server? 2000, Enterprise Edition
4. Windows explorer opens with following path: D:\ENGLISH\SQL2000\ENT
5. select msolap folder
6. select install folder
7. run setup program.
8. Install the service pack sp3a for analysis services which as 3 exe files:
sql2kasp3, sql2kdesksp3, and sql2ksp3.
After I install analysis services, I am able to build the cube. On the cube,
I select process, and then try to 'Browse Data'. I get an error message:
"Unable to browse the cube 'State' Unspecified error.
I have installed/un installed analysis service several time even with latest
service pack but I keep getting the above error.
Please help and thank you in advance..You can try :
http://groups.google.co.id/groups?h...r />
rg.mozill
a:en-US:official%26selm%3DefXVTXGmBHA.2416%2540tkmsftngp04%26rnum%3D1
Good luck