sql reports: logged

Showing posts with label logged. Show all posts

Friday, February 24, 2012

can somebody please help me with my query?

Hi I'm creating a macro to show how many times a user has logged into our database within a month. The output should look like this :

Name

Kit
Peter
Jeny
Katie
Patricia

Last Login date
Dec 28 2004 07:12AM
Dec 28 2004 09:30AM
Dec 27 2004 10:23AM
Dec 28 2004 10:38AM
Dec 27 2004 10:30AM

Login count
12/26/04
0
0
1
0
0

Login count
12/27/04
1
0
1
1
1

Login count
12/28/04
1
1
0
1
0

Right now my query reflects Name, last login date and the current day login count:

Select a.username, a.login_dt, CASE WHEN
a.isactive = 1 and a.login_dt = current_date() THEN 1
ELSE 0
END
from cms.dbo.usagelog a, cms.dbo.sys_user c
where a.userid = c.user_id and c.group2 IN ('DIS', 'MD', 'PC', 'SYS')
ORDER BY c.group2, a.login_dt, a.username

and this is the OUTPUT:
Name

Kit
Peter
Jeny
Katie
Patricia

Last LOGIN Date
Dec 28 2004 07:12AM
Dec 28 2004 09:30AM
Dec 27 2004 10:23AM
Dec 28 2004 10:38AM
Dec 27 2004 10:30AM

Login COUNT 12/28/04 (CURRENT DAY)
1
1
0
1
0

Can somebody please show me how i can do a loop or an iteration inside my query and get it to show the current day's data and all the data from the previous days (ex. 12/23, 12/24, 12/25, 12/26, 12/27) .You could try using 'GROUP BY':
Select A.Username, A.Login_Dt, Count(*) As Login_Times
From Cms.Dbo.Usagelog A, Cms.Dbo.Sys_User C
Where A.Userid = C.User_Id And C.Group2 In ('Dis', 'Md', 'Pc', 'Sys')
Group By A.Username, A.Login_Dt
Order By C.Group2, A.Username, A.Login_Dt :D|||LKBrwn_DBA, i don't think you can ORDER BY a column in a GROUP BY query if that column isn't in the SELECT list|||LKBrwn_DBA, i don't think you can ORDER BY a column in a GROUP BY query if that column isn't in the SELECT list
True, I kind'a just copied over the ORDER BY ... should have looked more closely.
:o

Can somebody find where it fails, please?

Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'PAC_APPS2' as 'PAC01\Administrator' (trusted)
Starting maintenance plan 'DB Maintenance Plan_weekly' on 5/27/2007 2:00:01 AM
[1] Database IER: Database Backup...
Destination:
[E:\MSSQLSERVER\DATA\MSSQL\BACKUP\IER\IER_db_200705270200.BAK]
** Execution Time: 0 hrs, 0 mins, 3 secs **
[2] Database IER: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 2 secs **
[3] Database MCI: Database Backup...
[4] Database SR_PAC: Database Backup...
Destination:
[E:\MSSQLSERVER\DATA\MSSQL\BACKUP\SR_PAC\SR_PAC_db_200705270200.BAK]
** Execution Time: 0 hrs, 0 mins, 2 secs **
[5] Database SR_PAC: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
[6] Database Survey: Database Backup...
Destination:
[E:\MSSQLSERVER\DATA\MSSQL\BACKUP\Survey\Survey_db_200705270200.BAK]
** Execution Time: 0 hrs, 0 mins, 2 secs **
[7] Database Survey: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 2 secs **
[8] Database ServiceReq: Database Backup...
[9] Database whosnext: Database Backup...
Deleting old text reports... 1 file(s) deleted.
End of maintenance plan 'DB Maintenance Plan_weekly' on 5/27/2007 2:00:34 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)Hi Dave
"Dave" wrote:
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> 'PAC_APPS2' as 'PAC01\Administrator' (trusted)
> Starting maintenance plan 'DB Maintenance Plan_weekly' on 5/27/2007 2:00:01 AM
> [1] Database IER: Database Backup...
> Destination:
> [E:\MSSQLSERVER\DATA\MSSQL\BACKUP\IER\IER_db_200705270200.BAK]
> ** Execution Time: 0 hrs, 0 mins, 3 secs **
> [2] Database IER: Verifying Backup...
> ** Execution Time: 0 hrs, 0 mins, 2 secs **
> [3] Database MCI: Database Backup...
> [4] Database SR_PAC: Database Backup...
> Destination:
> [E:\MSSQLSERVER\DATA\MSSQL\BACKUP\SR_PAC\SR_PAC_db_200705270200.BAK]
> ** Execution Time: 0 hrs, 0 mins, 2 secs **
> [5] Database SR_PAC: Verifying Backup...
> ** Execution Time: 0 hrs, 0 mins, 1 secs **
> [6] Database Survey: Database Backup...
> Destination:
> [E:\MSSQLSERVER\DATA\MSSQL\BACKUP\Survey\Survey_db_200705270200.BAK]
> ** Execution Time: 0 hrs, 0 mins, 2 secs **
> [7] Database Survey: Verifying Backup...
> ** Execution Time: 0 hrs, 0 mins, 2 secs **
> [8] Database ServiceReq: Database Backup...
> [9] Database whosnext: Database Backup...
> Deleting old text reports... 1 file(s) deleted.
> End of maintenance plan 'DB Maintenance Plan_weekly' on 5/27/2007 2:00:34 AM
> SQLMAINT.EXE Process Exit Code: 1 (Failed)
None of the steps seems to have failed, so it may be the job notifications
that are causing the problem! Have you checked the SQL Server Error Log for
any messages?
John|||Thanks, I will check that
-Dave
"John Bell" wrote:
> Hi Dave
> "Dave" wrote:
> >
> > Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
> > 'PAC_APPS2' as 'PAC01\Administrator' (trusted)
> > Starting maintenance plan 'DB Maintenance Plan_weekly' on 5/27/2007 2:00:01 AM
> > [1] Database IER: Database Backup...
> > Destination:
> > [E:\MSSQLSERVER\DATA\MSSQL\BACKUP\IER\IER_db_200705270200.BAK]
> >
> > ** Execution Time: 0 hrs, 0 mins, 3 secs **
> >
> > [2] Database IER: Verifying Backup...
> >
> > ** Execution Time: 0 hrs, 0 mins, 2 secs **
> >
> > [3] Database MCI: Database Backup...
> > [4] Database SR_PAC: Database Backup...
> > Destination:
> > [E:\MSSQLSERVER\DATA\MSSQL\BACKUP\SR_PAC\SR_PAC_db_200705270200.BAK]
> >
> > ** Execution Time: 0 hrs, 0 mins, 2 secs **
> >
> > [5] Database SR_PAC: Verifying Backup...
> >
> > ** Execution Time: 0 hrs, 0 mins, 1 secs **
> >
> > [6] Database Survey: Database Backup...
> > Destination:
> > [E:\MSSQLSERVER\DATA\MSSQL\BACKUP\Survey\Survey_db_200705270200.BAK]
> >
> > ** Execution Time: 0 hrs, 0 mins, 2 secs **
> >
> > [7] Database Survey: Verifying Backup...
> >
> > ** Execution Time: 0 hrs, 0 mins, 2 secs **
> >
> > [8] Database ServiceReq: Database Backup...
> > [9] Database whosnext: Database Backup...
> > Deleting old text reports... 1 file(s) deleted.
> >
> > End of maintenance plan 'DB Maintenance Plan_weekly' on 5/27/2007 2:00:34 AM
> > SQLMAINT.EXE Process Exit Code: 1 (Failed)
> None of the steps seems to have failed, so it may be the job notifications
> that are causing the problem! Have you checked the SQL Server Error Log for
> any messages?
> John

Can somebody find where it fails, please?

Sunday, February 19, 2012

Can run sp as Administrator but not as User/dbo

Anyone any clues? TIA Simn.
I can run the following sp fine in isql (and vb app) when logged in as
Administrator, but not when logged in as a User who has dbo rights in THISDB
(would rather have less..) and public in OTHERDB. Am using Windows auth and
not allowed sql login.
I get the msgs:
=======
Server: Msg 208, Level 16, State 1, Procedure sp_MyPROC, Line (marked below
with X)
Invalid object name 'myTABLE'.
Server: Msg 266, Level 16, State 1, Procedure sp_MyPROC, Line (marked below
with XX)
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 0, current count = 1.
=======
========================================
========
ALTER PROC [dbo].[sp_MyPROC] @.myPARAM VARCHAR(30) AS
DECLARE @.error_var int
SET @.error_var = 999
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
object_id(N'[myTABLE]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DECLARE @.rowcount_var int
DECLARE @.t1 datetime, @.t2 datetime, @.t3 datetime
BEGIN TRANSACTION
SET @.t1 = GETDATE()
CREATE TABLE [myTABLE] (
[ONE] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
X [TWO] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
IF( @.@.error <> 0 ) SET @.error_var = 1
INSERT INTO [myTABLE]
SELECT o.[ONE], o.[TWO]
FROM OTHERDB.dbo.source as o
INNER JOIN THISDB.dbo.Links as l
ON o.ONE = l.ONE
WHERE l.[Name] = @.myPARAM
SELECT @.rowcount_var = @.@.rowcount, @.error_var = @.@.error
IF( @.error_var > 1 ) SET @.error_var = 2
IF( @.rowcount_var = 0 ) SET @.error_var = 3
SET @.t2 = GETDATE()
UPDATE [myTABLE] SET
[ONE]=REPLACE([SBN],'''','`'),
[TWO]=REPLACE([BNA],'''','`')
IF( @.@.error <> 0 ) SET @.error_var = 4
SET @.t3 = GETDATE()
IF( @.error_var = 0 )
BEGIN
COMMIT TRANSACTION
INSERT INTO [timing] (RT, param, t1, t2, rc) VALUES (GETDATE(), @.myPARAM
,
DATEDIFF(s,@.t1,@.t2), DATEDIFF(s,@.t2,@.t3), @.rowcount_var)
END
XX ELSE ROLLBACK TRANSACTION
END
ELSE SET @.error_var = 99
RETURN @.error_var
========================================
========The table being created would have different owners if run
under an account that is a member of sysadmin and another
account that is a member of db_owner. In the create table
statement, try qualifying the owner as dbo -
CREATE TABLE dbo.myTable
-Sue
On Wed, 27 Jul 2005 08:34:03 -0700, "Simn"
<Simn@.discussions.microsoft.com> wrote:

>Anyone any clues? TIA Simn.
>I can run the following sp fine in isql (and vb app) when logged in as
>Administrator, but not when logged in as a User who has dbo rights in THISD
B
>(would rather have less..) and public in OTHERDB. Am using Windows auth and
>not allowed sql login.
>I get the msgs:
>=======
>Server: Msg 208, Level 16, State 1, Procedure sp_MyPROC, Line (marked below
>with X)
>Invalid object name 'myTABLE'.
>Server: Msg 266, Level 16, State 1, Procedure sp_MyPROC, Line (marked below
>with XX)
>Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
>TRANSACTION statement is missing. Previous count = 0, current count = 1.
>=======
>
> ========================================
========
>ALTER PROC [dbo].[sp_MyPROC] @.myPARAM VARCHAR(30) AS
>DECLARE @.error_var int
>SET @.error_var = 999
>IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id =
>object_id(N'[myTABLE]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
>BEGIN
> DECLARE @.rowcount_var int
> DECLARE @.t1 datetime, @.t2 datetime, @.t3 datetime
> BEGIN TRANSACTION
> SET @.t1 = GETDATE()
> CREATE TABLE [myTABLE] (
> [ONE] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,
>X [TWO] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> IF( @.@.error <> 0 ) SET @.error_var = 1
> INSERT INTO [myTABLE]
> SELECT o.[ONE], o.[TWO]
> FROM OTHERDB.dbo.source as o
> INNER JOIN THISDB.dbo.Links as l
> ON o.ONE = l.ONE
> WHERE l.[Name] = @.myPARAM
> SELECT @.rowcount_var = @.@.rowcount, @.error_var = @.@.error
> IF( @.error_var > 1 ) SET @.error_var = 2
> IF( @.rowcount_var = 0 ) SET @.error_var = 3
> SET @.t2 = GETDATE()
> UPDATE [myTABLE] SET
> [ONE]=REPLACE([SBN],'''','`'),
> [TWO]=REPLACE([BNA],'''','`')
> IF( @.@.error <> 0 ) SET @.error_var = 4
> SET @.t3 = GETDATE()
> IF( @.error_var = 0 )
> BEGIN
> COMMIT TRANSACTION
> INSERT INTO [timing] (RT, param, t1, t2, rc) VALUES (GETDATE(), @.myPA
RAM,
>DATEDIFF(s,@.t1,@.t2), DATEDIFF(s,@.t2,@.t3), @.rowcount_var)
> END
>XX ELSE ROLLBACK TRANSACTION
>END
>ELSE SET @.error_var = 99
>RETURN @.error_var
> ========================================
========|||Cheers Sue.. that worked, though one might think if all being done as User
(as long User allowed to create etc.) should be OK.. ho hum!
BTW I changed user from dbo to ddladmin role which seems OK.. Is this the
min. to create/drop, run sp's and view/edit data without being dbo? I'm
having trouble finding exactly what the fixed db roles can do in the 'Help'.
"Sue Hoegemeier" wrote:

> The table being created would have different owners if run
> under an account that is a member of sysadmin and another
> account that is a member of db_owner. In the create table
> statement, try qualifying the owner as dbo -
> CREATE TABLE dbo.myTable
> -Sue
> On Wed, 27 Jul 2005 08:34:03 -0700, "Simn"
> <Simn@.discussions.microsoft.com> wrote:
>
>|||Because after the table is created, the rest of the
procedure will by default look for the table myTable being
owned by dbo. ddladmin and db_owner need to qualify the
table name for it to be owned by dbo. If it isn't qualified,
their login will own the table.
db_ddladmin can execute DDL statements - those affecting
creating, dropping, altering objects. It won't cover
executing procedures, selecting/updating data.
If you need the user to be able to execute DDL statements as
well as select and update data, you could try db_ddladmin,
db_datareader, db_datawriter. The data access and
modifications would apply to all tables though. If that's
still more than what is needed, you would probably want to
look at creating a role that covers your needs outside of
the db_ddladmin role.
-Sue
On Thu, 28 Jul 2005 05:29:04 -0700, "Simn"
<Simn@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Cheers Sue.. that worked, though one might think if all being done as User
>(as long User allowed to create etc.) should be OK.. ho hum!
>BTW I changed user from dbo to ddladmin role which seems OK.. Is this the
>min. to create/drop, run sp's and view/edit data without being dbo? I'm
>having trouble finding exactly what the fixed db roles can do in the 'Help'
.
>"Sue Hoegemeier" wrote:
>|||Ta
Simon.

Friday, February 24, 2012

can somebody please help me with my query?

Can somebody find where it fails, please?

Can somebody find where it fails, please?

Can somebody find where it fails, please?

Sunday, February 19, 2012

Can run sp as Administrator but not as User/dbo

sql reports

Blog Archive

About Me