Applying aggregate function to whole table

Here is my problem and I suspect this is a simple question for the
experts.
My query attempts to get the names of players that played in a game
during the past x minutes and then the average scores of that player
for ALL games played by that player.
The query I have right now looks something like this (its from an
Access query but you get the point):
SELECT players.name, count(game.gameno) as gamesplayed,
Avg(game_players.score) AS avgscore
FROM players INNER JOIN (game INNER JOIN game_players ON game.gameno =
game_players.gameno) ON players.playerno = game_players.playerno
WHERE (((game.date_played) Between DateAdd('n',-100000,Now()) And
Now()))
As you see the Avg function will only return the average scores in the
games played that meet the "WHERE" condition. How can I get the
average score to be the Average for ALL scores for that player?
Thanks in advance.youretoast@.gmail.com,
This "select" statement is not related to sql server. There is not function
"now" in T-SQL, so I do not know if this can help. The idea is calculating
the avg for all rows and just counting the ones that meet the predicate.
SELECT
players.name,
count(
case when game.date_played Between DateAdd('n',-100000,Now()) And Now()
then game.gameno end
) as gamesplayed,
Avg(game_players.score) AS avgscore
FROM
players
INNER JOIN
(
game
INNER JOIN
game_players
ON game.gameno = game_players.gameno
) ON players.playerno = game_players.playerno
group by
players.name
AMB
"youretoast@.gmail.com" wrote:

> Here is my problem and I suspect this is a simple question for the
> experts.
> My query attempts to get the names of players that played in a game
> during the past x minutes and then the average scores of that player
> for ALL games played by that player.
> The query I have right now looks something like this (its from an
> Access query but you get the point):
> SELECT players.name, count(game.gameno) as gamesplayed,
> Avg(game_players.score) AS avgscore
> FROM players INNER JOIN (game INNER JOIN game_players ON game.gameno =
> game_players.gameno) ON players.playerno = game_players.playerno
> WHERE (((game.date_played) Between DateAdd('n',-100000,Now()) And
> Now()))
> As you see the Avg function will only return the average scores in the
> games played that meet the "WHERE" condition. How can I get the
> average score to be the Average for ALL scores for that player?
> Thanks in advance.
>

Application Roles across databases in SQL Server 2000

Hello
I have 2 databases that run application role security
(different role names and passwords), users access these
databases only from within different Visual Basic
applications.
I require to be able to request data from both
databases. I have read in SQL Server help that if you
enable the guest user account and then give it the
relevant permissions the system will only allow the other
database to get to these objects.
I have created a stored procedure on one of the databases
that calls a table in the database with the guest account
enabled. I have not given the guest account access to
this table but I can still get to the data in the table.
Please can someone explain why this is and what I need to
do to prevent this.
Thank you
Caroline> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
This is due to ownership chaining behavior. As long as all objects are
owned by the same login, permissions are not checked on indirectly
referenced objects. Additionally, you need to enable cross database
chaining for ownership chains to apply to cross-database access. This
appears to be the case in your environment.
As long as you access data only via views and procedures, you don't need to
grant any permissions to guest. This allows you to leverage ownership
chains as a security mechanism. See Ownership Chains in the Books Online
for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Caroline" <anonymous@.discussions.microsoft.com> wrote in message
news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
> Hello
> I have 2 databases that run application role security
> (different role names and passwords), users access these
> databases only from within different Visual Basic
> applications.
> I require to be able to request data from both
> databases. I have read in SQL Server help that if you
> enable the guest user account and then give it the
> relevant permissions the system will only allow the other
> database to get to these objects.
> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
> Thank you
> Caroline|||> and what I need to do to prevent this.
Only grant execute permissions on the procedure to those users/roles whom
you want to access the underlying data.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23fcOSPDYEHA.3596@.tk2msftngp13.phx.gbl...
> This is due to ownership chaining behavior. As long as all objects are
> owned by the same login, permissions are not checked on indirectly
> referenced objects. Additionally, you need to enable cross database
> chaining for ownership chains to apply to cross-database access. This
> appears to be the case in your environment.
> As long as you access data only via views and procedures, you don't need
to
> grant any permissions to guest. This allows you to leverage ownership
> chains as a security mechanism. See Ownership Chains in the Books Online
> for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Caroline" <anonymous@.discussions.microsoft.com> wrote in message
> news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
>

apostrophe

i'm using delphi 7 and have a query in which i'm trying to find names
that have an apostrophe in them, i.e. "o'mally". my problem is that
when i write my select statement i can't get the quotes right. i get
all types of errors no matter what i try. i get "missing right quote",
"invalid token" etc. i've tried using QuotedStr and nothing works.
here is my current attempt in which i get an "invalid use of keyword.
token: n'%'"
sSQL := 'SELECT statenotified, notary_id, LastName, FirstName,' +
' MiddleInitial, Indep, Book_number, Page_number,' +
' CloseRec, TermBegins, TermEnds, DatePickedUp,
FailedToQualify,' +
' Notes, SOSLtrSent FROM notaries WHERE LastName LIKE '''+
''+ edtLastName.Text+'+''%'' AND CloseRec = 1 order by
lastname';Do a search-and-replace using whatever intrinsic function is present in your
language.
Replace all single-quotes with two single quotes.
I.E. - to find
O'Malley,
Search where Last_Name = 'O''Malley'
Betwixt the O and M, there lies not a 'double-quote', but two times a
single.
James Hokes
"Michael Sterling" <stermic@.gw.co.jackson.mo.us> wrote in message
news:f7359f44.0312180837.7167d3aa@.posting.google.com...
> i'm using delphi 7 and have a query in which i'm trying to find names
> that have an apostrophe in them, i.e. "o'mally". my problem is that
> when i write my select statement i can't get the quotes right. i get
> all types of errors no matter what i try. i get "missing right quote",
> "invalid token" etc. i've tried using QuotedStr and nothing works.
> here is my current attempt in which i get an "invalid use of keyword.
> token: n'%'"
> sSQL := 'SELECT statenotified, notary_id, LastName, FirstName,' +
> ' MiddleInitial, Indep, Book_number, Page_number,' +
> ' CloseRec, TermBegins, TermEnds, DatePickedUp,
> FailedToQualify,' +
> ' Notes, SOSLtrSent FROM notaries WHERE LastName LIKE '''+
> ''+ edtLastName.Text+'+''%'' AND CloseRec = 1 order by
> lastname';

API calls from within a Stored Procedure?

Folks, I would like to convert some macros into a stored procedure. The ide
a
is to read file names out of a database and copy them from their current
location to specified directories. Can an API call be made from a SP to
prompt for a root directior (via file dialog), create a directory, and copy
a
file as its steping through a record set?
Suggestions will be greatly appreciated on how to do this.You can use extended stored procedures, although I believe you're not
supposed to have any UI on an XP. You could write a stored proc to read the
filenames from your database, and take advantage of the xp_cmdshell stored
procedure to move them to a destination directory. UI implementation is
really a whole 'nother layer from SQL Server, and you'd probably do better
to write a little .NET or other program to select the directory; although
you could create a stored proc to do the actual copy for you.
"Crisp" <Crisp@.discussions.microsoft.com> wrote in message
news:5E816E07-01D6-43F6-8E6C-E54E02BDB943@.microsoft.com...
> Folks, I would like to convert some macros into a stored procedure. The
> idea
> is to read file names out of a database and copy them from their current
> location to specified directories. Can an API call be made from a SP to
> prompt for a root directior (via file dialog), create a directory, and
> copy a
> file as its steping through a record set?
> Suggestions will be greatly appreciated on how to do this.|||You want to use SQL Server to move files around ?
including prompting a user for input?
This would be a gross misuse of the tool, it would be more appropriate and
much easier to do this in any ordinary programming language. You can still
put the file list in SQL if you want, and have application code get the list
from the SQL database, but having SQL do the File operations, or prompt user
s
for input, would be much too difficult, and inappropriate.
In fact, the only solution I can think of, is really a combination of having
SQL kick off an external process that does the actual "prompting and gather
user input" function, and then does the FIle IO... to do that you need to
write a COM-Component tool in a COM-Capable language, and call it from SQL
using built-in specially designed System procesdures. (Investigate the set
of built-in Stored Procs called sp_OACreate, sp_OAMethod, etc. that allow
you to create, use, and destroy COM components from inside SQL.)
But don't use these for any application where scaleability is a concern,
because they are notoriously NOT scaleable.
"Crisp" wrote:

> Folks, I would like to convert some macros into a stored procedure. The i
dea
> is to read file names out of a database and copy them from their current
> location to specified directories. Can an API call be made from a SP to
> prompt for a root directior (via file dialog), create a directory, and cop
y a
> file as its steping through a record set?
> Suggestions will be greatly appreciated on how to do this.|||I agree with you that the user input function and prompting would be a
misuse of SQL Server. The OP didn't specify this, but I was thinking along
the lines of administering the file move from a client machine? I.e., on my
desktop computer I want to move files on my SQL Server from C:\test_data to
D:\new_data. SQL Server makes it relatively easy to get a directory listing
and perform command shell operations via extended proc's, without setting up
and administering shares all over your network, or creating your own
specialized client/server app.
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:D04DB710-B550-4F04-A98D-19FA8F2B3688@.microsoft.com...
> You want to use SQL Server to move files around ?
> including prompting a user for input?
> This would be a gross misuse of the tool, it would be more appropriate and
> much easier to do this in any ordinary programming language. You can
> still
> put the file list in SQL if you want, and have application code get the
> list
> from the SQL database, but having SQL do the File operations, or prompt
> users
> for input, would be much too difficult, and inappropriate.
> In fact, the only solution I can think of, is really a combination of
> having
> SQL kick off an external process that does the actual "prompting and
> gather
> user input" function, and then does the FIle IO... to do that you need to
> write a COM-Component tool in a COM-Capable language, and call it from SQL
> using built-in specially designed System procesdures. (Investigate the
> set
> of built-in Stored Procs called sp_OACreate, sp_OAMethod, etc. that allow
> you to create, use, and destroy COM components from inside SQL.)
> But don't use these for any application where scaleability is a concern,
> because they are notoriously NOT scaleable.
>
> "Crisp" wrote:
>|||Well then it is certainly possible to write a SP that you would call from
your desktop, passing in acomplete source FileSpec, and destination FileSpec
,
as Local (Local to SQL Serevr) File Specfications, that would copy the file
from one folder \FileName to aspecified Folder\Filename... S(Sounds like yo
u
already know about xp_cmdShell)
You would still need to ensure that the Process ID the SQL Server was
running under had the appropriate permissions on the local file system.
The only thing this approach saves you is creating of user access
permissions and controlled network shares on the SQL Server across the
network...
"Michael C#" wrote:

> I agree with you that the user input function and prompting would be a
> misuse of SQL Server. The OP didn't specify this, but I was thinking alon
g
> the lines of administering the file move from a client machine? I.e., on
my
> desktop computer I want to move files on my SQL Server from C:\test_data t
o
> D:\new_data. SQL Server makes it relatively easy to get a directory listi
ng
> and perform command shell operations via extended proc's, without setting
up
> and administering shares all over your network, or creating your own
> specialized client/server app.
> "CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
> news:D04DB710-B550-4F04-A98D-19FA8F2B3688@.microsoft.com...
>
>|||"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:12A44395-01F8-44C7-996E-EDAC39490BCF@.microsoft.com...
> Well then it is certainly possible to write a SP that you would call from
> your desktop, passing in acomplete source FileSpec, and destination
> FileSpec,
> as Local (Local to SQL Serevr) File Specfications, that would copy the
> file
> from one folder \FileName to aspecified Folder\Filename... S(Sounds like
> you
> already know about xp_cmdShell)
> You would still need to ensure that the Process ID the SQL Server was
> running under had the appropriate permissions on the local file system.
> The only thing this approach saves you is creating of user access
> permissions and controlled network shares on the SQL Server across the
> network...
Designated network shares restrict the OP's solution to copying files from
pre-defined Point A's to pre-defined Point B's, and he seemed to hint that
he wanted to be able to have a choice of source and destination; although
I'm not sure exactly how many choices he wants. For only one or two
destinations, the network shares solution would definitely be the way to go.
But if you know there are going to be a lot of sources and destinations,
like each user has his/her own set of source and destination folders, a more
dynamic approach might be needed. A front-end file selection utility like
the File Attach interface in EM is not that hard to create in VB.NET or
C#.NET using SQL Server xp's. I guess it all really depends on a more
specific set of requirements from the OP to decide what solution would be
best for his/her needs. I think we both agree that trying to use SQL Server
as a UI engine is a horrible idea.

Anything that you find in SQL object scripts, you can also find them in system tables?

I tried all the INFORMATION_SCHEMA on SQL 2000 and
I see that the system tables hold pretty much everything I am
interested in: Objects names (columns, functions, stored procedures, ...)
stored procedure statements in syscomments table.

My questions are:

If you script your whole database everything you end up having
in the text sql scripts, are those also located in the system tables?
That means i could simply read those system tables to get any information
I would normally look in the sql script files?

Can i quickly generate a SQL statement of all the indexes on my database?

I read many places that Microsoft
says not to modify anything in those tables and not query them since their
structure might change in future SQL versions.

Is it safe to use and rely the system tables?

I basically want to do at least fetching of information i want from the
system tables rather than the SQL script files.
I also want to know if it's pretty safe for me to make changes in these
tables.
Can i rename an object name for example an Index name, a Stored Procedure
name?
Can i add a new column in the syscolumns table for a user table?

Thank youThe SQLDMO script method can be used to script any SQL Server object. You
can also generate scripts from Enterprise, Query Analyzer or from the
command line using the scptxfr.exe utility.

> Is it safe to use and rely the system tables?

Yes and no. The INFORMATION_SCHEMA views are a better source for lots of the
metadata and they are usually recommended as the preferred method whenever
possible. However, the information schema doesn't cover everything (no
indexes for example) so you may still need to make use of system tables for
some things. If you rely only on the features documented in Books Online and
avoid referencing the stuff that isn't explained or that's marked as
"reserved" then you should be fairly safe. Be sensible though and don't use
system tables when you don't have to. Check out the "Meta Data Functions"
topic in Books Online for other alternatives.

In SQL Server 2005 the old system tables are superceded by a new set of
views that give you much more comprehensive and convenient view of the
metadata. The old-style system tables are still supported for backwards
compatibility although they aren't being extended to support new features.
In theory, most things will work in 2005 as in 2000 but it's not going to be
100% so assume some things may need to be fixed if and when you upgrade.

> I also want to know if it's pretty safe for me to make changes in these
> tables.

Never. Not if you value the integrity of your server and your database. All
the things you need to do are supported through procs and DDL statements.
That includes renaming objects and adding new columns. Don't mess with
updates against the system tables.

--
David Portas
SQL Server MVP
--|||>> Is it safe to use and rely the system tables?

> Yes and no. The INFORMATION_SCHEMA views are a better source for lots of
> the metadata and they are usually recommended as the preferred method
> whenever possible. However, the information schema doesn't cover
> everything (no indexes for example) so you may still need to make use of
> system tables for some things. If you rely only on the features documented
> in Books Online and avoid referencing the stuff that isn't explained or
> that's marked as "reserved" then you should be fairly safe. Be sensible
> though and don't use system tables when you don't have to. Check out the
> "Meta Data Functions" topic in Books Online for other alternatives.

I started reading about Meta Data last week in SQL Books Online and I
am interested to learn more. However I can only do one thing at a time so
I am trying to understand the system tables first and find out ways
how/when/why I could and should use them.
I'll check out "Meta Data Functions" to see what they do.

>> I also want to know if it's pretty safe for me to make changes in these
>> tables.

> Never. Not if you value the integrity of your server and your database.
> All the things you need to do are supported through procs and DDL
> statements. That includes renaming objects and adding new columns. Don't
> mess with updates against the system tables.

Understood. I won't make any changes in these tables. You are right if I can
use the supported procs and DDL statements then I'll use them.

Thank you|||>> Is it safe to use and rely the system tables?

> Yes and no. The INFORMATION_SCHEMA views are a better source for lots of
> the metadata and they are usually recommended as the preferred method
> whenever possible. However, the information schema doesn't cover
> everything (no indexes for example) so you may still need to make use of
> system tables for some things. If you rely only on the features documented
> in Books Online and avoid referencing the stuff that isn't explained or
> that's marked as "reserved" then you should be fairly safe. Be sensible
> though and don't use system tables when you don't have to. Check out the
> "Meta Data Functions" topic in Books Online for other alternatives.

I started reading about Meta Data last week in SQL Books Online and I
am interested to learn more. However I can only do one thing at a time so
I am trying to understand the system tables first and find out ways
how/when/why I could and should use them.
I'll check out "Meta Data Functions" to see what they do.

>> I also want to know if it's pretty safe for me to make changes in these
>> tables.

> Never. Not if you value the integrity of your server and your database.
> All the things you need to do are supported through procs and DDL
> statements. That includes renaming objects and adding new columns. Don't
> mess with updates against the system tables.

Understood. I won't make any changes in these tables. You are right if I can
use the supported procs and DDL statements then I'll use them.

Thank you|||serge (sergea@.nospam.ehmail.com) writes:
> I tried all the INFORMATION_SCHEMA on SQL 2000 and
> I see that the system tables hold pretty much everything I am
> interested in: Objects names (columns, functions, stored procedures, ...)
> stored procedure statements in syscomments table.
> My questions are:
> If you script your whole database everything you end up having
> in the text sql scripts, are those also located in the system tables?

Scripting does not place anything in system tables. Creating objects
does. The source code of stored procedures, views, functions, constraints
and a few more objects are in syscomments. However, they are stored in
8000 character slices, and non-trivial to decode.

Tables, indexes and user-defined types are not stored as text as such
in SQL Server, but as scattered pieces which the scripting tools
reassemble.

In any case, you should not really script your databases more than at
most once. View the database as a storage for binary objects, and keep
your source code under version control. (But if you started without
version control, you may need to script once to get yourself a baseline.)

> I read many places that Microsoft
> says not to modify anything in those tables and not query them since their
> structure might change in future SQL versions.
> Is it safe to use and rely the system tables?

As David said, there are new means for retrieving meta data in SQL 2005,
and the old system tables in SQL 2005 become views implemented on top
of these, known as "Compatibility views". They are also marked as
deperecated, and they will surely disappear from a future version of
SQL Server, but this is not likely to happen this decade.

Important is to only use documented columns, and only use columns what
they are documented for. A column which has the short description of
"Reserved", will very likely always have a value of 0 or NULL in SQL 2005.

Personally, I use only the system tables for meta-data access in SQL 2000,
and this is also what I recommend. The INFORMATION_SCHEMA views are not
whole-covering, so mixing both means that you must know two paradigms.
There are also some traps with the views which can cause you to not
get data you expect, or columns may have names which makes promises
they don't live up to. SQL Server MVP Tony Rogerson also liks to point
out that they have scalability problems. Finally, all that uppercase
is ugly. :-)

> I basically want to do at least fetching of information i want from the
> system tables rather than the SQL script files.
> I also want to know if it's pretty safe for me to make changes in these
> tables.

Never make changes directly to system tables, unless you are told to so
by a Microsoft support professional.

> Can i rename an object name for example an Index name, a Stored Procedure
> name?

To rename an index use sp_rename. To rename a stored procedure, drop
the procedure and reload it under the new name.

> Can i add a new column in the syscolumns table for a user table?

Absolutely not! You will corrupt your database.

Please keep in mind that there is a whole lot of internal structures
in SQL Server that are not exposed. When you add a column to a table,
SQL Server may need to update all pages in the tables, to create space
for the column. Query plans for procedures that has SELECT * may need
to be flushed etc.

In fact, in SQL 2005, you don't even have read access to the real system
tables. All you see is views.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp