Showing posts with label log. Show all posts
Showing posts with label log. Show all posts

Sunday, March 25, 2012

Calling SQL 2005 stored procedure that returns a value

I have a stored procedure on a SQL server, which is workign correctly to check some date/time parameters and then insert a row into a log table.

I am calling it from an ASP page. Initially I just called it and didn't worry about the return value. However the Stored procedure now will return a value to determine if it made the change or not and if not why (ie log entry was at incorrect time etc).

I woudl liek to capture this returned value in my code to display a message to the user but am havign problems finding the right way to get the value.

I am calling the SP as follows:

Shared Sub createlogentry(ByVal ID, ByVal tme, ByVal val)
Dim result As String
Dim cs As String = ConfigurationManager.ConnectionStrings("connecttion1").ToString
Using con As New System.Data.SqlClient.SqlConnection(cs)
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand()
cmd.Connection = con
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.CommandText = "CreateLogEntry"
cmd.Parameters.Add("@.ChID", Data.SqlDbType.Int)
cmd.Parameters("@.ChID").Value = ID
cmd.Parameters.Add("@.Value", Data.SqlDbType.Int)
cmd.Parameters("@.Value").Value = val
result = cmd.ExecuteNonQuery().ToString
End Using
End Sub

I have tried amending the ExecuteNonQuery line to ExecuteReader()

Any help appreciated

Regards

Clive

Greetings Clive

I would have made the stored procedure use an output parameter as more outputs can always be added. A return can only return one value!

So as output you would add the lines:

cmd.Parameters.Add("Fred"), Data.SqlDbType.Int)

cmd.Parameters("Fred").Direction = Output;

after the result add

Dim iResult As Integer = md.Parameters("Fred").Value

|||

Thanks for that - you are right the ability to return more than one value would be useful.

At the moment in my T-SQL I use the RETURN construct to generate the return value. How would I change that to use an output parameter instead?

regards

Clive

|||

As in

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Clive Chinery
-- Create date: 13Aug2007
-- Description: Test for records for today and tomorrow
-- =============================================
CREATE PROCEDURE dbo.DoDateTest
@.Test1 BIT OUTPUT,
@.Test2 BIT OUTPUT
AS
SET NOCOUNT ON;
IF EXISTS(SELECT * FROM tblToday WHERE Date = GetDate()) SET @.Test1 = 1 ELSE SET @.Test1 = 0
IF EXISTS(SELECT * FROM tblToday WHERE Date = DateAdd(day, 1, GetDate()))
SET @.Test2 = 1 ELSE SET @.Test2 = 0
------- This is the End ----
GO

This returns two output values. I normally put the output parameters at the end of the declaration.

Regards

Clive Chinery

Wednesday, March 7, 2012

call database Maintenance plan from Sql Server Agent->Job

Hi There
can i call database Maintenance plan from Sql Server Agent->Job
what i'm trying to do
I've to truncated records on log table,
then shrink all databases
the call maintenance plan to take all database backup
Thanks
GaneshGanesh
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Ganesh" <gsganesh@.yahoo.com> wrote in message
news:A9B19A8E-AE37-4B2B-A0C9-B800E6119472@.microsoft.com...
> Hi There
> can i call database Maintenance plan from Sql Server Agent->Job
> what i'm trying to do
> I've to truncated records on log table,
> then shrink all databases
> the call maintenance plan to take all database backup
> --
> Thanks
> Ganesh|||Probably the best advice the OP should follow, but probably not what he
wanted to hear. :)
ML
http://milambda.blogspot.com/|||Check out this. Let me know if this is what you were looking for.
http://www.dbazine.com/sql/sql-articles/cook10

Saturday, February 25, 2012

Call a stored procedure from another SP?

Hello,

I'm trying to get a dynamic Web log report out of SQL Server 2000. I am sending all of my IIS activity to a SQL Server table called inetlog. I need to get a summary of downloads for particular files, and within a certain time period. So, I start by getting unique file names from the table by building a view:

CREATE VIEW UniqueFileNames
AS
SELECT TOP 100 PERCENT COUNT (t.target) AS NumDownloads,
t.target

FROM inetlog t

GROUP BY t.target
ORDER BY t.NumDownloads DESC

GO

Now that I've got this I can run my stored procedure which passes a number representing the days within which the downloads occurred:

CREATE PROCEDURE sp_DownloadsWithinNumDays

@.NumDays INT

AS

SELECT u.NumDownloads / 3 AS Downloads,
LEFT(u.Target,(LEN(u.Target)-4)) AS DocName,
UPPER(RIGHT((RTRIM(u.Target)),3)) AS DocType,
CONVERT(CHAR(8), MAX(i.logtime), 1) AS LogDate

FROM UniqueFileNames u

INNER JOIN
inetlog i
ON u.target = i.target

WHERE DATEDIFF(DAY, i.logtime, GETDATE()) <= @.NumDays AND
(u.Target LIKE '%.pdf%' OR
u.Target LIKE '%.doc%' OR
u.Target LIKE '%.zip%')

GROUP BY
u.target,
u.NumDownloads

ORDER BY Downloads DESC

GO

This works just fine - I pipe my results to a Web page and I get a list of *.doc, *.pdf, and *.zip files downloaded within the past x days, including a count. The problem is that the count never changes. If I put 10 days in the SP, I get a list of files downloaded within the last 10 days, but the count shows ALL downloads of that file. If I put 5 days, I get the file downloads from the past 5 days, with the same counts (i.e., all downloads).

I know why this is happening: it's because the view that I join to is the count of all downloads, but what I can't figure out is how to dynamically generate this count so that the input parameter for the SP (num days) is used.

Sorry for the long post, but I'm getting myself confused, so I thought I'd be very explicit. Thanks for any help you can offer.

Dylan
dylan_thomas@.esri.comMy understanding:

You want the count of downloads in the last X days for each file downloaded in the last X days...

Forget the view and try this proc.... unless I missed something...

CREATE PROCEDURE sp_DownloadsWithinNumDays

@.NumDays INT

AS

DECLARE
@.var_FromDate as datetime

SET NOCOUNT ON

SET @.var_FromDate = DATEADD ( d , 0 - @.NumDays, GETDATE() )

SELECT COUNT(*) AS Downloads,
LEFT(i.Target,(LEN(i.Target)-4)) AS DocName,
UPPER(RIGHT((RTRIM(i.Target)),3)) AS DocType,
CONVERT(CHAR(8), MAX(i.logtime), 1) AS LogDate

FROM inetlog i

WHERE i.logtime > @.var_FromDate AND
(i.Target LIKE '%.pdf%' OR
i.Target LIKE '%.doc%' OR
i.Target LIKE '%.zip%')

GROUP BY
i.target

ORDER BY 1 DESC

GO|||Andy - Fantastic! Runs faster, and gives me the result that I need. I'm very new to SQL Server (if you couldn't tell!), so it's good to see how you've used a variable in this procedure. Opens up a whole new world of possibilities for a SQL-based doc management system that I'm working on.

Thanks :-)|||CREATE PROCEDURE sp_DownloadsWithinNumDays
@.NumDays INT
AS
SELECT
u.NumDownloads / 3 AS Downloads
,LEFT(u.Target,(LEN(u.Target)-4)) AS DocName
,UPPER(RIGHT((RTRIM(u.Target)),3)) AS DocType
,CONVERT(CHAR(8), MAX(i.logtime), 1) AS LogDate
FROM
(
SELECT Target,COUNT(Target) AS NumDownloads
FROM inetlog
WHERE DATEDIFF(DAY, logtime, GETDATE()) <= @.NumDays AND
(
Target LIKE '%.pdf%' OR
Target LIKE '%.doc%' OR
Target LIKE '%.zip%'
)
GROUP BY Target
) u
INNER JOIN inetlog i ON u.target = i.target
WHERE DATEDIFF(DAY, i.logtime, GETDATE()) <= @.NumDays AND
(
i.Target LIKE '%.pdf%' OR
i.Target LIKE '%.doc%' OR
i.Target LIKE '%.zip%'
)
ORDER BY Downloads DESC, Target ASC|||No worries!

Thursday, February 16, 2012

Calculating timespans between access log entries

Try,
select
a.changelog_id,
a.username,
a.[action],
a.changedate,
a.recordid,
b.changelog_id,
b.[action],
b.changedate,
b.recordid,
datediff(second, a.changedate, b.changedate) as [timespam]
from
#ChangeLog as a
inner join
#ChangeLog as b
on a.[action] = 'ACCESS'
and b.[action] = 'UPDATE'
and a.recordid = b.recordid
and a.changedate = (
select max(c.changedate)
from #ChangeLog as c
where c.recordid = b.recordid and c.changedate < b.changedate
)
go
AMB
"David D Webb" wrote:

> I have a log table that records when certain records are accessed and
> updated. Now someone wants a report that gives the timespan between when
> the record was accessed, and when it was updated. There can be multiple
> ACCESS entries without an UPDATE, but there will always be an ACCESS
> preceding an UPDATE. Anyway to do this in a set based solution. They
> realize the data is not perfect, nor will the results be, but are looking
> for rough usage counts.
> The report from the following data should return 3 lines with:
> username, seconds, recordid
> It should only look at UPDATE records and the ACCESS record immediately
> preceding it.
> CREATE TABLE #ChangeLog (
> [changelog_id] [int] IDENTITY (1, 1) NOT NULL,
> [username] [varchar] (50) NOT NULL,
> [action] [varchar] (50) NOT NULL,
> [changedate] [datetime] NOT NULL,
> [recordid] [int] NOT NULL
> )
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 1:41:00 PM','hareyj','ACCESS',1224)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 1:59:29 PM','hareyj','UPDATE',1224)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 2:12:00 PM','jsmith','ACCESS',4233)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 2:15:00 PM','jsmith','ACCESS',9887)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 2:25:55 PM','jsmith','UPDATE',9887)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 4:04:00 PM','hareyj','ACCESS',3432)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 4:14:00 PM','hareyj','ACCESS',3432)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 4:23:00 PM','hareyj','ACCESS',3432)
> INSERT #ChangeLog ([changedate],[username],[action],[recor
did])
> VALUES ('3/27/2006 4:44:21 PM','hareyj','UPDATE',3432)
> SELECT * FROM #ChangeLog
>
> Thanks,
> Dave
>
>Thanks Alejandro! I didn't think of doing the self join like that.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5F9F645E-79FA-4D75-B53C-FA05466D3043@.microsoft.com...
> Try,
> select
> a.changelog_id,
> a.username,
> a.[action],
> a.changedate,
> a.recordid,
> b.changelog_id,
> b.[action],
> b.changedate,
> b.recordid,
> datediff(second, a.changedate, b.changedate) as [timespam]
> from
> #ChangeLog as a
> inner join
> #ChangeLog as b
> on a.[action] = 'ACCESS'
> and b.[action] = 'UPDATE'
> and a.recordid = b.recordid
> and a.changedate = (
> select max(c.changedate)
> from #ChangeLog as c
> where c.recordid = b.recordid and c.changedate < b.changedate
> )
> go
>
> AMB
>
> "David D Webb" wrote:
>