More custom security questions

Still working on custom security
Since I can't get a name of file (path) from within CheckAccess method it becomes somewhat useless. Is there a way to maybe overload GetPermissions or Policies methods? Or maybe CreateReport method so that I can include some custom code there? Is there an example of something like that?

I wish the catalog tree was transparent to Authentication extension, I don't see a point in acl for a custom extension, all I want is names and I can build on top of that. Something along the lines -

Code Snippet

public bool CheckAccess(...file...)
{
string[] permissions ;
permissions = server.GetPermissions(file);
...

Maybe I am missing something simple and I can tie everything to a security descriptor but I don't see how I can if there is no information such as name, date, modified by name and so on. All we get is principal name which is not very useful since I don't use built-in security names.

Thanks
So anybody implemented CustomAccess not totally based on acecollection?
|||bumpsql

Month-to-month function

Hi I trying to find a way to determine the number of working days per month starting from the current date to the last day of the current month.And within the same store procedure determine the number of working days as normal (each month is independent from the next). For example: The store procedure is executed

September:

@.CurrentDate = 9/10/2007

@.EndDate = last working day 9/30/2007

Total# of working days = 15

October:

@.CurrentDate = 10/1/2007

@.EndDate = last working day 10/31/2007

Total# of working days = 23

November:

@.CurrentDate = 11/1/2007

@.EndDate = last working day 11/30/2007

Total# of working days = 22

etc.

Any ideas of how i can approch this?

Thanks in advance.

If we just count out weekends, you can use this script. This will not take into account holidays.

Code Snippet

DECLARE @.startDate DATETIME,

@.dateTest DATETIME,

@.workDays INT

SET @.startDate = getDate()

SET @.dateTest = @.startDate

SET @.workDays = 0

WHILE( MONTH(@.startDate) = MONTH(@.dateTest) )

BEGIN

IF( DATENAME(dw, @.dateTest) != 'SATURDAY' AND DATENAME(dw, @.dateTest) != 'Sunday')

SET @.workDays = @.workDays + 1

SET @.dateTest = @.dateTest + 1

END

SELECT @.workDays

|||

You might try to search this forum on "Working Days". Also, give a look to this article about using a "calendar table:"

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

|||

Code Snippet

create function udf_WeekdayCounter

(

@.dtFrom datetime

, @.dtThrough Datetime

)

returns smallint

as

begin

if @.dtThrough <= @.dtFrom

return 0

declare @.iCounter int

set @.iCounter = 0

/*

declare @.dtFrom datetime, @.dtThrough datetime

set @.dtFrom = '11/1/2007'

set @.dtThrough = '11/30/2007'

*/

while @.dtFrom < = @.dtThrough

begin

if datepart(weekday,@.dtFrom) not in (datepart(weekday,'December 30, 2006'), datepart(weekday,'December 31, 2006'))

set @.iCounter = @.iCounter + 1

set @.dtFrom = dateadd(d, 1, @.dtFrom)

end

return @.iCounter

end

GO

select dbo.udf_WeekdayCounter( '11/1/2007', '11/30/2007')

sql

Month's end

Hi,

I am working on a cube that contains month standings and month totals. This works fine if I select one month.

It goes wrong when I do a year to date selection because it sums up the month standings.

How can I make a cube that does sum up the month total but only returns the last month standings.

With regards,

Constantijn Enders

Hi Constantijn,

If you configure the "month standings" measure (I don't know its details) with the aggregate function: LastNonEmpty, and you use Aggregate() rather than Sum() in your "year to date" calculation, does that work?

http://msdn2.microsoft.com/en-us/library/ms175623.aspx

>>

SQL Server 2005 Books Online

Configuring Measure Properties

Measures have properties that enable you to define how the measures function and to control how the measures appear to users.

...

Property

Definition

AggregateFunction

Determines how measures are aggregated. For more information, see Aggregation Functions.

LastNonEmpty

Semiadditive

Retrieves the value of the last non-empty child member.

>>

|||Or, depending on your data, you may want to use more optimal LastChild aggregation function.sql

Monthname in SQL server

Being new to SQL server T-SQL (working with DB2 / ORACLE) is there a function like monthname() in SQL server. I.e. a function that takes date as input and returns the Monthname (like AUG or AUGUST)??This example extracts the month name from the date returned by GETDATE.

SELECT DATENAME(month, getdate()) AS 'Month Name'

Here is the result set:

Month Name
----------
February|||Sorry, where can I put the date where I can extract the monthname from?
I want to add an object that displays 'JAN' or Januari if I have a date like:
01/01/2003|||SELECT DATENAME(month, '01/01/2003') AS 'Month Name'|||Thank you very much..............

Monitoring the number of concurrent sessions in SLQ Server

HI:
Is it possible to know the number of concurrent sesions
that are working in a SQL Server?
Im talking about sessions that are doing something
in the SQLServer in a certain period of time; not only
those
sessions that may be are users that are just logged but
doing
nothing (maybe in "Awaiting Command")
Regards.You can use sp_who (or sp_who2) with the ACTIVE parameter to see only spids
that are doing something.
--
Andrew J. Kelly
SQL Server MVP
"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
news:008101c3a631$3e9ab340$a501280a@.phx.gbl...
> HI:
> Is it possible to know the number of concurrent sesions
> that are working in a SQL Server?
> Im talking about sessions that are doing something
> in the SQLServer in a certain period of time; not only
> those
> sessions that may be are users that are just logged but
> doing
> nothing (maybe in "Awaiting Command")
> Regards.
>|||Hi,
Execute the below query from Query Analyzer to identify the number of
sessions ,
select count(*) from master..sysprocesses
Thanks
Hari
MCDBA
"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
news:008101c3a631$3e9ab340$a501280a@.phx.gbl...
> HI:
> Is it possible to know the number of concurrent sesions
> that are working in a SQL Server?
> Im talking about sessions that are doing something
> in the SQLServer in a certain period of time; not only
> those
> sessions that may be are users that are just logged but
> doing
> nothing (maybe in "Awaiting Command")
> Regards.
>|||Regards,
Jose.
>--Original Message--
>Hi,
>Execute the below query from Query Analyzer to identify
the number of
>sessions ,
>select count(*) from master..sysprocesses
>Thanks
>Hari
>MCDBA
>
>"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
>news:008101c3a631$3e9ab340$a501280a@.phx.gbl...
>> HI:
>> Is it possible to know the number of concurrent sesions
>> that are working in a SQL Server?
>> Im talking about sessions that are doing something
>> in the SQLServer in a certain period of time; not only
>> those
>> sessions that may be are users that are just logged but
>> doing
>> nothing (maybe in "Awaiting Command")
>> Regards.
>
>.
>|||Regards,
Jose (Mexico City)
>--Original Message--
>You can use sp_who (or sp_who2) with the ACTIVE
parameter to see only spids
>that are doing something.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Jose Martinez" <jomapa01@.hotmail.com> wrote in message
>news:008101c3a631$3e9ab340$a501280a@.phx.gbl...
>> HI:
>> Is it possible to know the number of concurrent sesions
>> that are working in a SQL Server?
>> Im talking about sessions that are doing something
>> in the SQLServer in a certain period of time; not only
>> those
>> sessions that may be are users that are just logged but
>> doing
>> nothing (maybe in "Awaiting Command")
>> Regards.
>
>.

Monitoring agent status

All -
I'm trying to setup a way to monitor the agent status for replication. I
tried using the built in "Alerts" and they are not working. I made sure that
they are enabled, but after enabling the Alerts, it does not show that the
event had ever happened when I check the history. I've made sure that the
alerts are enabled on the distributor and no where else.
So my next question would be is there a way programatically monitor agents?
I see the system view sys.dm_qn_subscriptions, but when I select * from it,
nothing is returned.
I am trying to monitor the failures and retries.
"Paul Ibison" wrote:

> Which event re you monitoring. I recall from another poster that we couldn't
> get the on success alert running as the event couldn't get raised to the
> windows log (couldn't change the message attributes, unlike in SQL 2000) ,
> but I am not aware of any others.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||This should be ok. Are you using SQL 2000 or 2005? I'd l'll take a look later
on, but for now as a stop-gap you could use a notification on the job itself.
Cheers,
Paul
|||We are using 2005. I'll try and give the notification on the specific job a
try and see if that works.
"Paul Ibison" wrote:

> This should be ok. Are you using SQL 2000 or 2005? I'd l'll take a look later
> on, but for now as a stop-gap you could use a notification on the job itself.
> Cheers,
> Paul
>

Monitor SQL agent

Hi,
we know that SQL agent controls the job's schedule & running, but if SQL
agent stop working, all jobs will not be able to be executed, is there any
tool/method to monitor SQL agent if it stopped, hung or wrong from another
SQL server, so the other SQL server will send operator notification?
Thanks in advance.
JackNot directly. But you can code it yourself. Use a combination of xp_cmdshell and netsvc (I believe
that the utility is called with which you can check and start/stop services on another machine). You
can pull the result into a table and then check against that table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <chongh.hc@.gmail.com> wrote in message news:eHqYn73PFHA.3076@.tk2msftngp13.phx.gbl...
> Hi,
> we know that SQL agent controls the job's schedule & running, but if SQL agent stop working, all
> jobs will not be able to be executed, is there any tool/method to monitor SQL agent if it stopped,
> hung or wrong from another SQL server, so the other SQL server will send operator notification?
> Thanks in advance.
> Jack
>

Monitor SQL agent

Hi,
we know that SQL agent controls the job's schedule & running, but if SQL
agent stop working, all jobs will not be able to be executed, is there any
tool/method to monitor SQL agent if it stopped, hung or wrong from another
SQL server, so the other SQL server will send operator notification?
Thanks in advance.
JackNot directly. But you can code it yourself. Use a combination of xp_cmdshell
and netsvc (I believe
that the utility is called with which you can check and start/stop services
on another machine). You
can pull the result into a table and then check against that table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <chongh.hc@.gmail.com> wrote in message news:eHqYn73PFHA.3076@.tk2msftngp13.phx.gbl...[
vbcol=seagreen]
> Hi,
> we know that SQL agent controls the job's schedule & running, but if SQL a
gent stop working, all
> jobs will not be able to be executed, is there any tool/method to monitor
SQL agent if it stopped,
> hung or wrong from another SQL server, so the other SQL server will send o
perator notification?
> Thanks in advance.
> Jack
>[/vbcol]

Monitor SQL agent

Hi,
we know that SQL agent controls the job's schedule & running, but if SQL
agent stop working, all jobs will not be able to be executed, is there any
tool/method to monitor SQL agent if it stopped, hung or wrong from another
SQL server, so the other SQL server will send operator notification?
Thanks in advance.
Jack
Not directly. But you can code it yourself. Use a combination of xp_cmdshell and netsvc (I believe
that the utility is called with which you can check and start/stop services on another machine). You
can pull the result into a table and then check against that table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jack" <chongh.hc@.gmail.com> wrote in message news:eHqYn73PFHA.3076@.tk2msftngp13.phx.gbl...
> Hi,
> we know that SQL agent controls the job's schedule & running, but if SQL agent stop working, all
> jobs will not be able to be executed, is there any tool/method to monitor SQL agent if it stopped,
> hung or wrong from another SQL server, so the other SQL server will send operator notification?
> Thanks in advance.
> Jack
>

monitor replication

Hi,
How do I automate the successful / error working of
replication - both transactional as well as merge. Is
there any system tables based on which i can do a join?
Appreciate if someone can provide a query...
regards.
bharathIf you want to monitor replication you need to query information from the
table
sysjobs and sysjobhistory table in MSDB database.
In sysjobs the name of the job would be like this
<Remote Server Name>-<Publication name>-<Publication database>-<Local sql
server name>-<Database name>- 0
For this particular job name find the job_id and using it query the table
sysjobhistory. Watch for the columns step_name and message.
Regards,
Jagan Mohan
MCP
"bharath" <barathsing@.hotmail.com> wrote in message
news:051401c3b7e0$b3f08eb0$a401280a@.phx.gbl...
> Hi,
> How do I automate the successful / error working of
> replication - both transactional as well as merge. Is
> there any system tables based on which i can do a join?
> Appreciate if someone can provide a query...
> regards.
> bharath
>
>|||One method I have always been using and have found to be very effective and
simple is to create an end-to-end trace myself and then monitor the trace
for the end-to-end transactional replication deplay.
To create such a trace, I include a dummy table in every publication. The
dummy table has a datetime column and I schedule a job to update the dummy
table at a regular interval. Then, when the change in the dummy table has
reached the subscriber via replication, I compare the the time the datetime
is updated at the publisher and the time the same row is updated at the
subscriber. The time difference gives me the end-to-end replication deplay.
I can then send alerts if the deplay is longer than a certain threshold.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"bharath" <barathsing@.hotmail.com> wrote in message
news:051401c3b7e0$b3f08eb0$a401280a@.phx.gbl...
> Hi,
> How do I automate the successful / error working of
> replication - both transactional as well as merge. Is
> there any system tables based on which i can do a join?
> Appreciate if someone can provide a query...
> regards.
> bharath
>
>

Monitor i/o, cpu etc. without traces

Any suggestions on how I can monitor the following without using traces? I am a dba/developer working as a developer on a contract, and I'm supposed to be tuning. However, I can't run traces. I've got my own procs that monitor locking, etc. But I would like to get at least i/o and cpu throughout the day. It would also be nice to get the query executed. Basically, the type of stuff you'd normally use traces for.

I know about @.@.cpu, @.@.io etc., but these are basically useless (no?) since they only record since the server was started. There is a stored proc but it only monitors these things since the last time it was run.

Does anyone know how I could utilize the above? I tried to write a script but I couldn't get it to work. :(

I realize that in general this is a ridiculous request, but I thought I would ask anyway.

Hello, Rottengeek.

Are you using SQL Server 2005? If so, you might want to use some of the new DMVs.

sys.dm_exec_query_stats gives you I/O and execution statistics for each plan. You can use the columns in this view to relate the plan handle and statement handle back to the actual SQL text. You'll find that the DMV has lots of neat data; each of the statistics carries a total, min and max value for the plan since it was last compiled.

If you checkout the dm_exec_sql_text() function in books online, it shows how to get the statement text given a sql_handle (one of the columns in dm_exec_query_stats) and has a couple of neat sample queries.

I hope that's enough to get you going in the right direction. If you have follow-up questions, please let me know.

.B ekiM

|||

Below are some of the system functions/virtual tables that you can use in SQL Server 2000:

fn_virtualfilestats - To get I/O counters per database/file

sysprocesses

sysperfinfo - Perfmon counters

But whether you can access these or not depends on your permission levels. You can also take a look at the links below for other pointers:

http://support.microsoft.com/kb/298475/

http://support.microsoft.com/kb/243588/

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

|||

I'm on SQL Server 2000.

This was VERY helpful.

Unfortunately, I can't query sysperfinfo, however, I may be able to get a workaround by submitting a script to the DBA...who knows. At any rate, as a consultant (who is normally a DBA) these will come in VERY HANDY. One of the links instructs you how to create sp_blocker_pss80, which I will modify but will be much better than running a trace.

I'm typically pretty familiar with the system tables, but sysperfinfo I did not know about.

Thanks! If anyone else has some more ideas, I'd love to hear them...