T-SQL (SS2K8) :: User Wise Query Tracking
Apr 30, 2015In my production server i created sql server user for limited permission. Now I want details of what are all the Query executing in user wise .
View 1 Replies
ADVERTISEMENT
T-SQL (SS2K8) :: Retrieve Subject Wise Highest Marks List In A Table?
Jul 18, 2011I have a requirement like, we are having two tables in our database.
Table names: student, marklist
Student table values:
id studname
------------------
1x
2y
3z
4a
5b
Marklist table values:
id maths physics English
---------------------------------
1506070
2706040
3508070
45010070
5906070
But my requirement is, I need to display the data "subject wise" highest marks for each student.
for example:
id name highestmark
---------------------------------
1 x English
T-SQL (SS2K8) :: Transactions And Tracking Within While Loop?
Apr 15, 2015I am running the following query, which deletes around 800 million rows from the table.
I would like to introduce transactions with in this code and also, if success entire deletion process should be committed and number of rows deleted, table name and success should be inserted to the log table.
If there is a failure, transaction should be rolled back and table name and error message should be inserted to the same log file
Select 1
While @@ROWCOUNT > 0
Begin
DELETE Top(100000) FROM [dbo].[Table1]
FROM [dbo].[Table2]
INNER JOIN [dbo].[Table3] ON [Table2].[PracticeID] = [Table3].[PracticeID]
INNER JOIN [dbo].[Table1] ON [Table3].[InputDevicePracticeID] = [Table1].[InputDevicePracticeID]
WHERE [Table2].PracticeID =55;
End
how to achieve this?
User Tracking..
Feb 6, 2004I have a table in my database and it holds some important information,I want to track the users
who are executing DML commands(select,update,delete) on the data in that table.
Is there any way do that?If so how could i implement that.can any one help me in this regard?
Thanks in advance...
User Ip Tracking
Jun 20, 2007Hello everyone, I have a fairly unique need :) I am trying todetermine the use/clients for databases in my corporation that I ammaintaining, but that noone seems to know what they are for. Many ofthese databases never seem to have anybody connected to them in thecurrent activity.What I'd like to do is find a way to audit the logins, so everytimesomeone connects to a database it simply logs the clients IP address,what login they used, and maybe what time. I've been searching googlefor this and have found tons of information on auditing the logins,but not the clients, such as by ip. Any help in this regard would beGREATLY appriciated!Joshua
View 2 Replies View RelatedTracking User Access To A DB
Mar 22, 2001Hi,
Does any one have any idea of how to track a user access to a spesfic DB because we have an old DB and we don't know if any body using it, I know Profiler can help but I need to know when it was the last time this DB has been accessed.
Thanks
Tracking User Activities
Oct 14, 2005Sybase and DB2 both have the capability of tracking user activities ata number of levels: invalid access attempts to databases, table, etc.;creation/deletion/modification of database objects/users/groups,grants/revokes.For MS SQLServer, the only setting that I've seen in the documentationis access attempts (none, fail only, etc.)The monitor program has the capability of tracking the events that Iwant to be monitored, but it seems as though these settings persistonly while the monitor program is running.I'd like these settings to persist permanently and the event records tobe sent to the system log.I can't seem to find the right term to get this information out of theMS Books On LIne.Help!
View 5 Replies View RelatedMax Value Row Wise Query
Jan 25, 2015I have a table having 3 coulmns.
amnt1 amnt2 amnt3
100 200 230
200 170 120
290 188 299
800 170 120
i need to get max amount row wise like
amnt
230
200
299
800
Select Query - Showing Columns Data Row Wise
Dec 28, 2011I have get 3 columns in a select query. how can show these 3 columns in row wise.
View 2 Replies View RelatedSQL Server 2012 :: Query To Get The Month Wise Data?
Sep 13, 2015I have the data in the below format.
Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201406 xxxxxx 27 1
In the above data, no record exist for 201403,201404,201405, query I wrote will give only the data for which there LeftCount exists, but I am looking for a query which get the data in the below format.
Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201403 xxxxxx 28 0
201404 xxxxxx 28 0
201405 xxxxxx 28 0
201406 xxxxxx 27 1
Query For Total Sum By Monthly / Year Wise By Reference Code
Feb 20, 2015In the Reference_Master table I have Total count based on date for each RefCode. Can I get the total count of each RefCode by monthly wise for each year
Reference_Master
RefCode Date Count
100012/18/20074
100012/19/20078
100012/20/20074
100022/18/20073
100022/19/20072
100022/20/20076
[Code] ...
I need the result as below
Result
RefCodeMonth/YearTotalCount
10001FEB-200716
10002FEB-200711
10003Mar-20077
10004Mar-20073
10005Mar-20072
10001FEB-200816
10002FEB-200811
10003Mar-20087
10004Mar-20083
10005Mar-20082
SQL Query Help - Vehicle Tracking App
Feb 21, 2008Hi,
I got 3 tables, VehicleType (VehicleTypeID is PK), Vehicle (VehicleID isPK, VehicleTypeID is FK) and Position (PositionID is PK, VehicleID is FK). Position table save all positions from all vehicles. I want from that table have latest position for all vehicles, how do I get this? PositionID is just automatic numbered 1, 2, 3, ... I use (so far) following sql query but it gives me all positions, I want only latest position for all vehicles. Can I use DateTime for having latest positions in some way? Or am I forced to use PositionID to get my wanted result?
SELECT RegNo, AltID, VehicleType, DateTime, Speed, Latitude, Longitude From Vehicle v
JOIN VehicleType vt ON v.VehicleTypeID = vt.VehicleTypeID
Join Position p ON v.VehicleID = p.VehicleID
ORDER BY DateTime DESC
Thanks in advance!
/J
Transact SQL :: Query For Month Wise Running Totals Of Sales Amount?
Nov 28, 2012I have a sales tables which looks as below.
DEPARTMENT
Barnd_Name
Item_Group
S_DATE
S_AMOUNT
Administration
IBM
[code]....
Now i need Month Wise Running Totals.but i should check the following group as show below i that order
1) DEPARTMENT
1) Brand
3) Item Group
4) Month
T-SQL (SS2K8) :: Checking User Active Directory Group
Jul 17, 2014Is it possible to check for Active Directory group.. ie see if the user running the Stored Proc, is in a specific Active Directory Group? Or if I set up Login's using Active Directory, can I get the Login that way... or will it give me the user's account?
View 6 Replies View RelatedT-SQL (SS2K8) :: Finding Rows Where User Has Access To Contents But Not Record
Mar 27, 2014I'm got a "folder" structure application which we'll be using as an in-house directory viewer. (In case you're wondering, it doesn't relate to any "real" folders, so using xp_cmdshell is out! )
Each folder and file record can have its own permissions, however these are assumed to inherit from the parent folder if no specific access rules have been set, basically in the same way file systems work. Each file record can only have one parent, and a folder can either have a parent or be at the root level.
Right now I'm having an issue with the inheritance of permissions. Say if I want to grant access to "Folder 1" to "Group A", then "Group B" shouldn't be able to see it. However, if I grant access to "File 1" in "Folder 1" to "Group B", then "Group B" should be able to see "Folder 1", but only see "File 1" and not the rest of the contents.
I thought I could do this with a CTE, but I'm having a bit of difficulty..
Here's the code:
CREATE TABLE #FileSystem (
FSIDINTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY
,ParentFSIDINTEGER NULL
,NameVARCHAR(100)
,RecordTypeVARCHAR(1)-- (F)older, or Fi(L)e
[Code] ....
T-SQL (SS2K8) :: User Defined Function That Returns Start And End Date
Oct 15, 2014Any UDF that accepts a Month and Year and returns Start Date and End Date.
For example @Month = 01 and @Year = 2014 would return a StartDate of 2014-01-01 and an EndDate of 2014-01-31.
SQL Server 2012 :: Query To Find User Who Last Modified User Roles / Access?
Dec 6, 2013I would like to know if there is a way to find out who changed a users roles/access WITHOUT using the audit function. For example, if a user account was created and given SA access then changed to read only, how can I find out who made that change? I tried searching for an answer, but kept getting no results. I'm thinking this may tie into the sys.sysusers view?
View 3 Replies View RelatedT-SQL (SS2K8) :: Generate Script For View Dependencies Of User Defined Table Types?
Aug 25, 2014T-SQL script for the following request ?
Generate script for view dependencies of user defined table types ?
T-SQL (SS2K8) :: Write Into A Table User-entered Value And Increment That Number N Times As Existing Rows
Jun 25, 2014I need to have a script where it ask the user for a value, the script will search for all records that match the value. Then it will display the numbers of records found and ask the user to enter a different value. The rest of the script will use this new value and increment by 1 n times as the number of records found. I started the script where it will ask for "HANDLE" and display the number of records found with that "HANDLE"
declare @HANDLE as varchar(30)
declare @COUNT as varchar(10)
declare @STARTINV as varchar(20)
set @HANDLE = ?C --This is the parameter to search for records with this value
set @STARTINV = ?C --User will input the starting invoice number
SELECT COUNT as OrderCount FROM SHIPHIST
where HANDLE = @HANDLE
I just can't figure out how to proceed to use the entered invoice # and increment by 1 until it reach the number of records found.
This will be the end results:
Count=5 --results from query
STARTINV=00010 --Value entered by user
Handle,Inv_Num
AAABBB,00010
AAABBB,00011
AAABBB,00012
AAABBB,00013
AAABBB,00014
T-SQL (SS2K8) :: SSIS - Extract Data From Table And Insert In Message Body And Email To User
Jun 25, 2014What I am trying to do, Extract the data from SQL table and Insert in Email Body and email to user. I got good article on Internet, I follow all steps as it is, but still I am getting error.
Here is the link : [URL] ....
But I am getting Error:
Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument list.
at System.Text.StringBuilder.AppendFormat(IFormatProvider provider, String format, Object[] args)
at System.String.Format(IFormatProvider provider, String format, Object[] args)
at ST_7f59d09774914001b60a99a90809d5c5.csproj.ScriptMain.Main()
[Code] ....
Wise Job Scheduling
Feb 22, 2007Guys,
I've been asked to properly schedule some jobs on our system for a database following a full recovery model. I realized that the order for some of the jobs might matter and came up with the solution, which is described below.
Please let me know if this ordering makes sense and/or if you have any general comments/suggestions:
1. Backup all dbs [in case defragmentation messes something up]
2. Defragmentation [this will grow the transaction log]
3. Backup transaction log [backs up trans log and truncates inactive portion of it]
4. Shrink log file
Also, I've been previously suggested not to shrink the log file, unless absolutely necessary, as it is resource intensive. In my experience, however, this operation does not seem to be resource intensive at all. It took me 1 second to shrink the transaction log file from 25GB to 2GB.
Is it regrowing of the log file that will affect performance?
Also, if I do shrink the transaction log file, is there a suggested value to shrink it to - possibly a function of the database file size?
Thanks a lot
BIT-Wise Aggregation
Oct 29, 2006Hi,
I have the following three tables :
Account (Id int, AccountName nvarchar(25))
Role (id int, Rights int)
AccountRole (AccountID, RoleID)
In Role table - Rights Column is a bit map where in each bit would refer to access to a method.
One account can be associated with multiple roles - AccountRole table is used for representing the N:N relation.
I want to develop a store procedure - which would return all AccountName and their Consolidated Rights.
Basically I want to do a BitWise OR operation for all the Rights in the Aggregation instead of the SUM as shown in the following statement.
SELECT Account.Name, SUM(Role.Rights) FROM Account WITH (NOLOCK)
JOIN RoleAccount ON RoleAccount.AccountID = Account.Id
JOIN Role ON RoleAccount.RoleId = Role.Id
GROUP BY Account.Name
Thanks,
Loonysan
Data In Column Wise
Apr 18, 2008My Table(collection) Structure consist of below:
TypeDate ItemAmount
REVENUE2007-04-01serv002***
REVENUE2007-04-01Serv004***
REVENUE2007-04-01serv005***
REVENUE2007-04-01AMC-***
ROYALTY2007-04-01serv002***
ROYALTY2007-04-01Serv004***
ROYALTY2007-04-01serv005***
ROYALTY2007-04-01AMC***
O/p should come as:
====================
Revenue:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****
Royalty:
MONTH AMC serv002 SER004 serv005
2007-04-01 *** **** *** ****
Need Weekly Wise Data
Apr 2, 2007Hi,
Need to write a query which would select data based on weekly wise based on the column Date.And week starts from Monday.
Hence would reqiure a function or a query which would select only those records which would fall under respective Weeks.
Help!!!
Regards,
Navneet
Is There Any Difference Performance Wise
Dec 4, 2007Dear All,
is there any difference performance wise using
select * from my_table
and
select mycol1,mycol2....mycoln from my_table
actually i've read from one article the there is big difference....
please clear my doubt...
thanks in advance
Vinod
Even you learn 1%, Learn it with 100% confidence.
Is There Anyway To Generate Day Wise Scripts
Feb 12, 2008Dear All,
is there any way to generate daywise scripts in database? i mean all the transactions wether insert update or delete....
i need all the changes on that particular date(with in 24 hours)....
is there any procedure or function for this?
i've almost 1200 tables in that database.
note : due to replication is not possible for my scenario, i'm thinking in different ways to achieve my purpose.
this is my requirement
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96959
Vinod
Even you learn 1%, Learn it with 100% confidence.
Sequence No Group Wise
Jul 23, 2005Hi All,I want to give sequence no group wise, as follows:Section EmpCode-----------------------A 1001A 1003A 1009B 1023B 1030C 1044-----------------------I want to set sequence no. group wise like this for above data.Seq. Section EmpCode------------------------------1 A 10012 A 10033 A 10091 B 10232 B 10301 C 1044----------------------------Suggestion in this regard will be appreciatedThanks in Advance,T.S.Negi
View 3 Replies View RelatedHaving The Issue Regarding The Group Wise SUM
Apr 25, 2008I have a group and a subgroup in a report. in the subgroup there are repetitive rows. But i want to display them at only once within the subgroup. So I hide them. Now I want to have the sum of a field viz. "quantity" group wise, having the only single value of the subgroup. But problem is in the sum the values which are hidden also included. But i don't want them to include in the sum of the group.
Can anybody give the suggestions how to resolve the problem..
I am fighting with it from a long back but i am not able to solve the issue.. plz help me out..
location invoice no. date quantity item
delhi inv/del/1 1/1/2008 3 item-1
item-2
item-3
inv/del/2 2/1/2008 2 item-4
item-5
Location Total 5
This is what i want... for invoice inv/del/1 there are 3 items, i hide rest of two rows using "Hide Duplicates", But the group sum i.e. Location Total in the report is coming out as '13' as it includes the quantities of hidden rows also. But in actual I want '5'.
thanx you..
How To Use Order By Clause Row Wise
Sep 9, 2015i want to know how to use order by clause in row wise
for example, suppose the rows is
10 20 30 40
so the output is should be
10
20
30
40
T-SQL (SS2K8) :: Query For Treeview
Sep 11, 2012I have a query which is working fine. Is it possible that if the table3's column(Child) is only related to table 1 to show it under table 1 and not under table 2, but at the same time another (Child) has a parent in table 2 (which usually is the case) it will show under table 2 as its currently doing.
In other words Child column is directly under Table2's row column name (Father), but occasionally it comes under Table1 with no relation to Table 2.
How can I out put that in a query for a treeview? I am assuming that I will have to program the out come in c# also with 3 for loops and in the second loop I can check if the column is grandchild or Child and make that as a second row or 2nd node of treeview, but I am having a problem building a query in sql.
The query below shows all Parent, then child then grand child(all well and working), but what is desired is at times child takes place of a father.
declare @x as xml
set @x =
(
SELECT distinct
Table1.AssetSysID, Table1.Asset_ID , Table1.FromLR, Table1.Asset_ID + ', ' + Table1.[Desc2] as GarndFather,
Table2.ACISysID ,Table2.PAssetSysID, Table2.FeatureName + ', ' + Table2.[DESC] AS Father,
Table3.ITMSysID ,Table3.Item_ID + ',' + Table3.[DESC] as Child
[Code] .....
T-SQL (SS2K8) :: How To Add Counts To A Query
Jun 11, 2014How to get task counts in a sales report.
The data is located in three tables: Projects, ProjectTasks, Sales.
I need to group the data by month and project. It needs to include sales per month as well as the number of each project task completed that month.
Projects:
ProjectIDName
1Project1
ProjectTasks:
ProTaskIDProjectIDTaskCodeBeginDateEndDate
11 Task11/1/20141/15/2014
21Task21/15/20141/20/2014
31Task3 1/21/20141/29/2014
Sales:
SalesIDProjectIDClosingAmount
11 1/31/2014$5000
Query Output:
ClosingDateProject TotalSalesTask1sTask2sTask3s
1/2014Project1$500011 1
My query so far is:
SELECT right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)) as ClosingDate,
p.name as Project, SUM(s.amount) as TotalSales
FROM Sales s
JOIN Project p ON p.projectID = s.projectID
WHEREs.closing >= DATEADD(mm, -12, GETDATE())
GROUP BY right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)), p.name
This will give me the grouping by month/year and project.
T-SQL (SS2K8) :: Duplicates In Sub Query
Sep 13, 2014I have the piece of sql code here below that keeps giving out duplicates. How to resolve this.
isnull((select distinct (SUM(a1.ActualDebit) - SUM(a1.ActualCredit) ) from #MainAccount a1
LEFT OUTER JOIN
#BudgetAccount bb ON aa.AccountID = bb.AccountID AND a1.PeriodStartdate = bb.PeriodStartDate and
a1.DateMonth=bb.DateMonth and a1.Budget = bb.Budget WHERE a1.AccountID = aa.AccountID and
a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE GROUP BY a1.group1, a1.Group2),0)
As Actual_CurrentMonth,
T-SQL (SS2K8) :: Query To Get Total At The End
Sep 15, 2014select col1,count(*) from client1..table1 group by col1
union
select col1,count(*) from client2..table1 group by col1
union
select col1,count(*) from client3..table1 group by col1
The results yields
33915
3405
3412
I am trying to get the following result but can't figure out how to get the total in the end.
33915
3405
3412
Total 22