Query help

Hello Everyone,

Please can you help me with this?

I have an audit table, so there are many id's and modified dates... I am looking to get the last updated record for each ID. This is what I have so far... "which still gives duplicate ID's"

I have see many pages about distincta and max/min... I cannot make sense of it... HELP

1Select *2FROM TabelA3WhereExists (SELECT distinct max (id)as id ,max (ModifiedDate)as ModifiedDate4FROM TabelA)5Order by id

I tried to break it down, and still I get duplicate ID's

1Select *2FROM TabelA3Where idIN (SELECT distinct id4FROM TabelA)5Order by id

Try

SELECT *FROM TableAWHERECONVERT (nvarchar,max(ModifiedDate),126 ) +' '+ IDEXISTS IN (selectCONVERT (nvarchar,max(ModifiedDate),126 ) +' '+ IDas [key]from TableAgroup by ID)ORDER BY ID

|||

Thanks for your reply,

I get the following error

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'EXISTS'.

|||

SQL Server 2005:

SELECT id, ModifiedDateFROM(SELECT id, ModifiedDate, row_number()OVER(partitionby idorderby ModifiedDateDESC)as RowNum

FROM TableA) t

WHERE t.RowNum=1

For SQL Server 2000, you can try:

SELECT id, ModifiedDateFROM(SELECT id, ModifiedDate,(SELECTcount(*)FROM TABLEA aWHERE a.id=a1.idand a1.ModifiedDate<=a.ModifiedDate)as RowNum

FROM TableA a1) t

WHERE t.RowNum=1

|||

That's why your anAll-Star!....Shot for the help... it works very well.

Thanks B

sql

Query for SQL audit

Hey everyone,

This is my first question here for a long while, so be extra nice ;)

I am doing an audit on some of my sql server 2000s and I would to know if its possible to automate the collection of some of the data.

Firstly I would like to be able to query the domain account that SQLSERVER and SQLAGENT are running on, in my case the agent and service account will alaways be the same, but the account name maybe different depending on what server it is.

Secondly I would like to be able to query whether the account SQL server is running on is local admin of the server...I know some of you will say the SQL account has to be local admin but with the right registry and folder level permissions it is not necessary for the account to be local admin. This was a requirement from a very strict security audit.You can probably find the service accounts using some of the undocumented extended stored procedures that Enterprise Manager uses. Set up a profiler trace and then use EM to get that information. Inspect the trace for the commands that EM used to get the service accounts, and you should be set there.

As for the second, you may need to resort to WMI (Windows Management Instrumentation), which is usable through VB script. I am not good enough to tell you how to get started, beyond a google search, though.|||As for the second, you may need to resort to WMI (Windows Management Instrumentation), which is usable through VB script. I am not good enough to tell you how to get started, beyond a google search, though.

You can use WMI to grab both pieces of information:

Members of Local Admins Group

Set oItems = Nothing

' Next grab the members of the Local Administrators Group
Set oItems = oWMIService.ExecQuery( "SELECT PartComponent FROM Win32_GroupUser WHERE GroupComponent = ""Win32_Group.Domain='" & sComputer & "',Name='Administrators'""", _
"WQL", wbemFlagReturnImmediately + wbemFlagForwardOnly )

For Each oItem in oItems
Set oMember = oWMIService.Get(oItem.PartComponent)

Set oComm = CreateObject("ADODB.Command")
oComm.CommandText = "spUpdateServerPermissions"
oComm.CommandType = 4
oComm.ActiveConnection = oConn

oComm.Parameters.Refresh
oComm.Parameters("@.SERVER") = sComputer
oComm.Parameters("@.GROUPNAME") = "Administrators"
oComm.Parameters("@.MEMBERNAME") = oMember.Caption

oComm.Execute
Set oComm = Nothing
Next

To grab information related to services running:

Set oWMIService = GetObject("winmgmts:{impersonationLevel=impersonate,(Security )}!\\" & sFQDN & "\root\cimv2")
Set oItems = oWMIService.ExecQuery("SELECT * FROM Win32_Service",,48)

Set oComm = CreateObject("ADODB.Command")

' Open connection to catalog database
oConn.ConnectionString = sConnectionString
oConn.Open

If Err.number = 0 Then
For Each oItem in oItems
If IsNull(oItem.DisplayName) Then
sDisplayName = oItem.Name
Else
sDisplayName = oItem.DisplayName
End If

' Bunch o' code to load results into db...
Next


The snippets are not complete; you need to dim the objects and define sFQDN, but you should be able to make some sense of them...

Regards,

hmscott|||Thanks very much guys :) that looks perfect.