Help with SQL Agent Security

Hi,
I want to be able to allow certain non (SA) users to see SQL Agent
jobs. Whilst I am aware that all I need to do is add
them to the 'TargetServersRole'(and fix any sp3 changes), this does
not allow them to see the status of any jobs. This is because when
they
look at the jobs, sp_get_composite_job_info runs a check
to see if they are sysadmin before running
xp_sqlagent_enum_jobs. If they aren't sysadmin the
status gets returned as not running (4).
You will note that all jobs have a status of 'Not Running' even though
some are definitely running ie. replication tasks
The following posting is exactly what I have an issue with and would
like to resolve.
http://groups.google.com.au/groups?...80a%2540phx.gblThere is no supported way to do this. In terms of using
TargetServerRole, that's not officially supported either.
Using the role isn't documented as it's used by SQL Server
for multi-server administration and it's permissions has
changed through some of the service packs.
Essentially, the behavior you are seeing is by design.
-Sue
On 17 Feb 2005 20:50:18 -0800, jharalam@.colesmyer.com.au
(Jharalam) wrote:

>Hi,
>I want to be able to allow certain non (SA) users to see SQL Agent
>jobs. Whilst I am aware that all I need to do is add
>them to the 'TargetServersRole'(and fix any sp3 changes), this does
>not allow them to see the status of any jobs. This is because when
>they
>look at the jobs, sp_get_composite_job_info runs a check
>to see if they are sysadmin before running
>xp_sqlagent_enum_jobs. If they aren't sysadmin the
>status gets returned as not running (4).
>You will note that all jobs have a status of 'Not Running' even though
>some are definitely running ie. replication tasks
>The following posting is exactly what I have an issue with and would
>like to resolve.
>http://groups.google.com.au/groups?...80a%2540phx.gbl|||If using windows NT Authenication, make sure that you log into SQL
Server exectaly how your UseriD is defined in Windows in the CORRECT
case
e.g in Windows your user is
DOMAIN\Your.Name
sign on as DOMAIN\Your.Name not DOMAIN\your.name there is an Extended
Stored Procedure that does some sort of binary User Name comparison
to check your credentials and this can sometimes prevent you from
seeing the Job Status as it evaluates: Your.Name <> your.name
as false.
This is of course a BUG! Opps Feature of the system
Paul

Help with returning a certain # of records from a view.

I have a view that will return say 5000 records when I do a simple
select query on that view like.

select *
from vw_test_view

How can I set up my query to only return a certain # of records, say
the first 300?

Here is what is going on, we have a large amount of data that returns
in a view and we need to work with all of it eventually, However we
want to do it in chunks. So my thoughts were as follows:

1. To run a query to return X amount of the total data for us to work
with.
2. Update these records with a flag in a table that the vw_test_view
filters out.
3. The next time I run the query to pull data from the view it will
skip the records that I have already looked at (because of step 2) and
pull the next X amount of records.

Thanks in advance,
MikeOn 24 Jun 2004 08:43:30 -0700, Mike wrote:

>I have a view that will return say 5000 records when I do a simple
>select query on that view like.
>select *
>from vw_test_view
>
>How can I set up my query to only return a certain # of records, say
>the first 300?
>
>Here is what is going on, we have a large amount of data that returns
>in a view and we need to work with all of it eventually, However we
>want to do it in chunks. So my thoughts were as follows:
>1. To run a query to return X amount of the total data for us to work
>with.
>2. Update these records with a flag in a table that the vw_test_view
>filters out.
>3. The next time I run the query to pull data from the view it will
>skip the records that I have already looked at (because of step 2) and
>pull the next X amount of records.
>Thanks in advance,
>Mike

Hi Mike,

You could use the TOP clause of the SELECT statement:

SELECT TOP 300 Column1, Column2, ...
FROM MyView
WHERE ....-- if necessary
ORDER BY .....

Without the order by, you'll still get maximum 300 rows, but there's no
way predicting which 300 out of the total number of matching rows will be
selected. With the ORDER BY, you'll get the first 300 according to the
specified sort order.

An alternative is to use SET ROWCOUNT:

SET ROWCOUNT 300
SELECT Column1, Column2, ...
FROM MyView
WHERE ....-- if necessary
ORDER BY .....
SET ROWCOUNT 0-- restored default behaviour

The SET ROWCOUNT gives the maximum number of rows to affect for all future
commands from the same connection. Note that this applies to UPDATE and
DELETE as well!! To return to the default behaviour of affecting all rows,
use SET ROWCOUNT 0 or close and re-open the connection.

Note that both methods use proprietary Transact-SQL syntax. An ANSI
standard version can only be done with a specified order (you'll have to
specify by which order you want the 300 "first" rows) and requires a
correlated subquery. It will be much slower.

SELECT Column1, Column2
FROM MyView AS a
WHERE ....-- if necessary
AND (SELECT COUNT(*)
FROM MyView AS b
WHERE ....-- same as in outer join
AND b.OrderingColumn < a.OrderingColumn)
< 300
ORDER BY OrderingColumn-- may be omitted

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> How can I set up my query to only return a certain # of records
[sic], say
the first 300? <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access
or ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

You will have to get out the RDBMS world and use a cursor of some
kind.

>> Here is what is going on, we have a large amount of data that
returns
in a view and we need to work with all of it eventually, However we
want to do it in chunks. <<

1) A mere 5000 rows is not a lot of data.

2) The idea of "doing it in chunks" is dangerous; do you know anything
about transactions, isolation levels and shared data?|||
Sometimes it benefits programmers to get out of in front of their
screens for a while and see how what they do affects end users.
Unfortunately too many of them do not take the time to do this or to try
and understand things from an end users point of view. No 5000 rows is
not a lot of data from a programmers point of view, but from a user who
has to go through this and verify certain information this can seem like
a daunting task, if you can break it down either feed it to them slowly
or split it amongst several people it becomes much more manageable for
them. This by the way is not what I am trying to accomplish, nor is
5000 the # of rows that I have of total data or 300 how many that I want
to pull out at a time. All that this is are made-up scenarios to
illustrate the type of things that I am trying to accomplish.

If you want to crucify me with semantics go ahead. It doesn't matter,
all that does is that people understand my question and through their
generosity point me in the right direction.

Hugo, thanks again for the help this will give me what I need to get the
job done.
And I already have the view using an order by clause on the data and it
returns exactly what I need, so if I add in the top clause it should
give me exactly what I need.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!sql

Help with query that only returns filds with certain characters

Hi,
I need to write a sql statement that only returns records if
a certain field DOESN'T contain a letter between a and d, a number, a #,
and a asterisk. If a different character is found there, it should
return that record.
For example. If we have these records
1. 43242#
2. %3499
3. $$#
4. ak
5. abd43#
6. 4242#44z
7. abc_
8. 342#ab*
9. *(
My query should return 2, 3, 4, 6, 7 and 9
I tried the following:
select * from Table1
where MyField LIKE '%[^0-9]%'
and MyField like '%[^a-d]%'
and it works fine for the numbers and letters, but I don't how to
include the # and the *
Thanks a lot.You can negate the LIKE clause by putting NOT in front of it.
So
SELECT * FROM Table1
WHERE MyField LIKE '%[^0-9]%'
AND MyField LIKE '%[^a-d]%'
AND MyField NOT LIKE '%#%'
AND MyField NOT LIKE '%*%'
One note though, the criteria you mentioned and the examples you gave do not
seem to match up.

But you said you should return rows (and your sample code that you said
works as intended) that do NOT contain a number. Some of those rows clearly
contain a number. Just not sure where you were going with that.
HTH,
John Scragg
"Star" wrote:
> Hi,
> I need to write a sql statement that only returns records if
> a certain field DOESN'T contain a letter between a and d, a number, a #,
> and a asterisk. If a different character is found there, it should
> return that record.
> For example. If we have these records
> 1. 43242#
> 2. %3499
> 3. $$#
> 4. ak
> 5. abd43#
> 6. 4242#44z
> 7. abc_
> 8. 342#ab*
> 9. *(
>
> My query should return 2, 3, 4, 6, 7 and 9
> I tried the following:
> select * from Table1
> where MyField LIKE '%[^0-9]%'
> and MyField like '%[^a-d]%'
> and it works fine for the numbers and letters, but I don't how to
> include the # and the *
> Thanks a lot.
>|||John,
Yes, I think my explanation was a little bit confusing.
I will try to rephrase it.
The query should return rows if the field
does not contain one of these characters:
- Numbers
- Letters (a-d)
- #
- *
For example, the query should return %3499 because
there is a % symbol there and that symbol is not on that list.
I had already tried what you suggested, but doesn't work for me.
If I run it, I wouldn't get 4242#44z back, and I should because it
contains a 'z'
John Scragg wrote:
> You can negate the LIKE clause by putting NOT in front of it.
> So
> SELECT * FROM Table1
> WHERE MyField LIKE '%[^0-9]%'
> AND MyField LIKE '%[^a-d]%'
> AND MyField NOT LIKE '%#%'
> AND MyField NOT LIKE '%*%'
> One note though, the criteria you mentioned and the examples you gave do n
ot
> seem to match up.
>
>
> But you said you should return rows (and your sample code that you said
> works as intended) that do NOT contain a number. Some of those rows clear
ly
> contain a number. Just not sure where you were going with that.
> HTH,
> John Scragg
>
> "Star" wrote:
>|||
You may also need to consider the escape character
e.g.
select * from (select 'a%b' col1 union select 'cde') x
where col1 LIKE '%\%%' ESCAPE ''
returns only 'a%b'|||Thanks, Steven. I will keep in mind.
However, I still haven't found a solution for this problem...
If I do this
select myfield from mytable
where myfield LIKE '%[^0-9]%'
and myfield like '%[^a-d]%'
and myfield not LIKE '%*%'
and myfield not LIKE '%#%'
and myfield LIKE '%\%%' ESCAPE ''
I only get %3499 back.
I really don't know what else to try...|||> I really don't know what else to try...
I've finally worked out (I think) what it is that you need
All records that contain one (or more) characters that are not in
(1234567890abcd#*)
Would that be a fair assumption ?|||
> I really don't know what else to try...
Is this SQL 2000 or SQL2005 ?
You could use a regex match
OR:
select col1 , patindex('%[^abcd1234567890#*]%',col1)
from
(
select
'43242#' col1
UNION SELECT
'%3499' UNION SELECT
'$$#' UNION SELECT
'ak' UNION SELECT
'abd43#' UNION SELECT
'4242#44z' UNION SELECT
'abc_' UNION SELECT
'342#ab*' UNION SELECT
'*('
) x
where patindex('%[^abcd1234567890#*]%',col1) <>0|||On Wed, 02 Nov 2005 14:05:46 -0500, Star wrote:

>John,
>Yes, I think my explanation was a little bit confusing.
>I will try to rephrase it.
>The query should return rows if the field
>does not contain one of these characters:
>- Numbers
>- Letters (a-d)
>- #
>- *
>For example, the query should return %3499 because
>there is a % symbol there and that symbol is not on that list.
Hi Star,
You write "does not contain one of these characters", but your example
suggests that you mean "contains at least one character not in this
list". For '%3499' does contain a number (even four!), yet you want it
returned.
SELECT MyField, other columns
FROM MyTable1
WHERE MyField LIKE '%[^0-9a-d#*]%'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes... I apologize again. I had a really bad day. I should have thought
twice my question.
Sorry about that and thanks for your help.|||Awesome!
That worked. To be honest, I didn't know about that patindex function. I
won't forget next time.
I really really appreciate your help and time.

Help with query

Hi,

I have a problem to write this query. I need to show a report which have employeeName, OrderTotal based on certain date.

However, eventhough that employee doesn't have an order yet, I will still show their name on the report with zero as orderTotal.

I have this query:

Select
EmployeeName,
Count(OrderID) as TotalOrder

from Employee WHERE InvoiceDT BETWEEN '1/1/2006' AND '1/31/2006'

But, this query would not return employeeName which are not between those invoiceDT.

So, how to get all employeeName, but filter the Order based on the invoiceDT.

Thanks in advance.

Anyone please...|||

Are you seeking for something like this?:

SELECT EmployeeName,Sum(TotalOrder) FROM

(SELECT EmployeeName,TotalOrder=CASE WHEN InvoiceDT BETWEEN '1996-08-06' AND '1998-01-05'
THEN Count(OrderID)
ELSE 0
END

FROM Employees
GROUP BY EmployeeName,InvoiceDT) as tmp
GROUP BY EmployeeName

|||Thanks lori, it works.|||Thanks lori, it works.