Number Events Occurring Within A Period

Mar 17, 2008

I have TableA with the following structure and Data.


CaseID ActionID StartDate
------ ------------ --------------
A232/21/2007
A212/8/2007
B2212/4/2007
B1310/12/2007
B1710/7/2007
C617/6/2007
D3311/2/2007
D5610/22/2007
D267/29/2007
E226/21/2007
E585/25/2007
E874/8/2007
E293/23/2007

Expected Results:
I want to create TableB with the following structure and data .

CaseIDX YZ
----- - - -
A001
B011
C000
D001
E111


X = 1 when a case has 4 or more actions within 180 days , 0 otherwise
Y= 1 when a case has 3 or more actions within 90 days , 0 otherwise
Z= 1 when a case has 2 or more actions within 30 days , 0 otherwise

Any help will be welcomed

View 12 Replies


ADVERTISEMENT

Let Subscription Events Fire, But Don't Act On Them During A Particular Period

May 23, 2007

As I understand it, a subscription has a SQL Agent job which fires and inserts a row into the ReportServer.dbo.Event table. Then the SSRS windows service polls for these and picks them up and runs the subscriptions.



What I'm trying to do is to prevent subscriptions from executing during a 6 hour window every day. End users may define a schedule that runs during that 6 hour window. If they do, it should run those subscriptions as soon as we complete the 6 hour window.



I thought about pausing the schedule, but I'm afraid that when you resume the schedule, it won't catch up if it was supposed to run during the 6 hour window.



So I'm thinking the best idea is to let the SQL Agent job continue to fire and insert rows into the ReportServer.dbo.Event table... but I need some way to prevent the windows service from picking up those events until we've passed the 6 hour window. How can I do this?



Thanks in advance.

View 2 Replies View Related

How To Know Number Of Users Hitting A SSRS Report Over A Time Period?

Jan 24, 2008



Hi all,
I want to raise one question which might not relevant to current discussion.
Prob:-- I have some SSRS reports. I want to know for a given time period
1. How many people hit the report.
2. Who are the users hit the report in that time interval.

Is there any query to get that data?
Or,
should I go somewhere to see the data?

NB:- There is not any table in the application which is storing the data

View 10 Replies View Related

SQL Server Error Logs: What Events Is It Logging? Can We Edit And Include DDL Events?

Jun 20, 2007

Hello experts. I have been searching for anything about this but found very little. What are the events logged in SQL Server Error Logs aside from Successful/Failed Login, Backup/Restore/Recover database and, start/init sql server? Can we configure this to log other events, like CREATE or DBCC events for example? If so, how? Thanks a lot.

View 1 Replies View Related

Events Calendar Questions: How To Find Events In The Week End

Jan 28, 2006

I have a start date, end date for each event.

I want to list all events between the start and end date comes in Saturday or Sunday.
in SQL server 2005 TSQL statement.

any insights ?

View 1 Replies View Related

Transact SQL :: Emailing Session Events For Extended Events

Feb 3, 2014

I am using this code for LongRunning Queries.

CREATE EVENT SESSION LongRunningQuery
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack)
WHERE sqlserver.sql_statement_completed.duration > 60000

[Code] ...

Here Instead of writing to XML file how can send an EMAIL if a query runs more than 1 minute in my server ?

View 2 Replies View Related

Last Period As Initial Value But Still Have Period As Parameter?

Jan 3, 2007

Hi all,

I'm using an olap cube in a report (dash board).
The last "actual" period should be viewed when the user opens the report. I'm using the MDX tail function for that.
Once the report is opened the users want to select another period. Then I want to put period as a parameter.
But that is not possible since the parameter check box is no longer available when using a MDX filter expression.

Is there any solution to this problem?

Any help will be appreciated.

View 34 Replies View Related

Updates Not Occurring When Using Execute

Apr 12, 2005

Create Procedure UpdateGameIsOverlappingFlagByIds (@gameDateIds as varchar(200) = '') as
begin
    if (@gameDateIds = '')
        raiserror('UpdateGameIsOverlappingFlagByIds: Missing parameters', 16,1)
    else
        begin
            Execute
('update games Set IsOverlapping = 1 where gameDateId IN (' +
@gameDateIds + ')')

        end
end   
return
--------------------------------------------
That is the sproc that doesn't seem to update the date when called from
asp.net but does work when done through the query analyzer.  Am I
missing something?

Thanks for any help you can give me.

View 4 Replies View Related

Replication - Several Transactions Occurring

Feb 8, 2012

I have configured a transactional replication between 2 MSSQL 2005 instances. It is set to replicate every 15 minutes. Most replications take less than a minute with about 10 - 50 transactions being replicated.

However, 3 times during the day, the replication takes about 25 minutes to complete with 500-700 transactions being replicated. This also causes the application that is accessing the DB being replicated to become unresponsive when it attempts to retrieve rows from tables in the DB.

Might there be a setting that causes the transactional replication to accumulate transactions during the day?

View 1 Replies View Related

Exception Repeately Occurring

Jul 23, 2005

HiI have a strange problem with my SQL server 2k instllation - every 10mintutes when I have the Profiler trace with the entire "Errors" Eventcategory selected, the following 5 exceptions show up in the trace andthat too for the same SPID.Error: 16955, Severity: 16, State: 2Error: 16945, Severity: 16, State: 1Error: 16955, Severity: 16, State: 2Error: 16945, Severity: 16, State: 1Error: 16955, Severity: 16, State: 2Error: 16945, Severity: 16, State: 1I have no clue why this is occurring - I tried running a trace with theSP:StmtCompleted event on, but no other stored procedures show up withthe same spid close to the time where this exception is logged.Does anyone have a clue as to why this error is occurring ?Rahul

View 1 Replies View Related

Transact SQL :: Get Max Occurring Value Using GROUP BY

May 14, 2015

I have 3 columns of data CustomerNum, Newsletter, and NumSent.

I need to return the only the CustomerNumber and Newsletter combinations having the Max(NumSent) So it should be a unique customernumber with the newletter having the most numsent.

For the data below, my result set will be:

0000000000000000101 Healthcare
0000000000000000102 Construction-Environ Svcs

How can I do this easily with GROUP BY , Max or subselect?

Sample Data:
0000000000000000101 Healthcare                       19
0000000000000000101 Construction-Environ Svcs 11
0000000000000000101 Manufacturing                   8               

0000000000000000101 Homecare                        5
0000000000000000101 Daycare                          4
0000000000000000102 Healthcare                       9
0000000000000000102 Construction-Environ Svcs 21
0000000000000000102 Manufacturing                   5              

0000000000000000102 Homecare                        11
0000000000000000102 Daycare                          1

View 6 Replies View Related

FTP Task Error Occurring

Apr 16, 2007

This is strange... just started getting this today, while it's worked for weeks. But alas, that is programming.

The error I'm getting is

[Connection manager "FTP DQ Connection"] Error: An error occurred in the requested FTP operation. Detailed error description: 200 Type set to I. 200 Command okay. 550 /usfsr/DFE_INPUT/FATest_DQ.txt: No such file or folder. .

I'm uploading a file (FATest_DQ.txt) to the folder /usfsr/DFE_INPUT/. I eventually just uploaded the file manually using IE6 to make sure the connection properties were still the same.

Any ideas?

View 1 Replies View Related

Command.ExecuteNonQuery() Error Occurring NULL Value

Apr 2, 2008

Here is the table that I am wanting to insert the information into (BTW I am wanting the UserID, EntryDate, Note) On my page i do have a text box for the UserID as well as a Note textbox. When I hite the submit button on my page I am already sending the UserID textbox information to be sent to another table (called RequestTable). However, I am wanting to take that same UserID and insert it into the RequestNote table as well. Let me know if you have any questions for me to further explain anything.
**RequestNote**RequestNoteKey (PK)  (has identity set to yes)RequestKey (allows nulls)NoteEntryDateEntryUserID  (allows Nulls)****This is my stored procedure that I am calling called "NoteInsert"***@Note nvarchar(1000),@EntryUserID nvarchar(50)AS INSERT INTO RequestNote (Note, EntryDate,EntryUserID)
VALUES (@Note,GetDate(), @EntryUserID)
RETURNGO
****THIS IS THE PAGE THAT CONNECTS THE USER INTERFACE AND STORED PROCEDURE***public static void AddRequestNote(string requestNote, string userID){using (SqlConnection connection = new SqlConnection(connRequestNote)){using (SqlCommand command = new SqlCommand("NoteInsert", connection)){command.CommandType = CommandType.StoredProcedure;command.Parameters.Add(new SqlParameter("@Note", requestNote));command.Parameters.Add(new SqlParameter("@EntryUserID", userID));connection.Open();command.ExecuteNonQuery(); <--THIS IS WHERE I GET AN ERROR THAT SAYS Cannot insert the value NULL into column 'RequestNoteKey', table 'RequestNote'; column does not allow nulls. INSERT fails}

View 5 Replies View Related

Rows Affected- But Update Not Physically Occurring

Mar 15, 2004

I have a mixed mode account setup with exec permissions granted on my stored procedures. I am running an sp that is editing a member, and if i run it using my query analyzer with the same login, my sp writes to the db. however, if i'm calling it through my web app (asp.net) it doesn't. here is my sp code:

USE DATABASE
--DROP PROC sp_EditMember
GO

--Create the stored procedure
CREATE PROCEDURE sp_EditMember
@member_id smallint,
@last_name nvarchar(50), --not nullable
@first_name nvarchar(50),
@spouse_name nvarchar(50),
@street_address nvarchar(50),
@city nvarchar(35),
@state nvarchar(5),
@zip_code nvarchar(15),
@zip_4 nvarchar(4),
@area_code nvarchar(10),
@phone_number nvarchar(20),
@email nvarchar(50),
@child_1 nvarchar(30),
@child_2 nvarchar(30),
@child_3 nvarchar(30),
@child_4 nvarchar(30),
@child_5 nvarchar(30),
@member_status nvarchar(20),
@member_exp nvarchar(10),
@plaques_st nvarchar(10)

AS
BEGIN TRAN
UPDATE Members
SET last_name = @last_name, first_name = @first_name,
spouse_name = @spouse_name, street_address = @street_address,
city = @city, state = @state, zip_code = @zip_code, zip_4 = @zip_4,
area_code = @area_code, phone_number = @phone_number, email = @email,
child_1 = @child_1, child_2 = @child_2, child_3 = @child_3,
child_4 = @child_4, child_5 = @child_5,
member_status = @member_status, member_exp = @member_exp,
plaques_st = @plaques_st

WHERE member_id = @member_id

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
END
ELSE COMMIT TRAN
GO
--------------------------------------

on my app side- i've already ensured the datatypes match up and the sizes are all ok (except for member_id, the variable is an int and i pass it as a small_int- the number is ranging from 1000-10000).

any troubleshooting on this?

thanks in advance,

sudeep.

View 1 Replies View Related

Auto Start Of SQL Server 7.0 Agent Not Occurring

Nov 16, 2000

I am not successful in getting SQL Server Agent to startup when SQL server starts up.

I believe the problem has to do with a lack of permissions or rights. I will list the steps
below which seem to me to be all that are needed to install SQL Server Agent. I used
Enterprise Manager to get this information.

If anyone can shoot any holes in the process, please do, that may be where my
problem lies.

a. Created NT accounts called MSSQLSERVER for the server and MSSQLSERVERAGENT
for SQL Agent.

b. Each of these accounts has the "right" to logon as a service.

c. Each of these accounts has "admin" rights.

d. Each of these accounts has been added to SQL SERVER under Security and Logins.

e. The properties assigned to each of the accounts are:
(1) on the general tab -- Windows NT authentication is in effect, database is master
and language is English (2) on the server roles tab -- System Administrators is checked
(3) on the database access tab -- the master database is checked

f. Under SQL Server Agent, properties, selecting the General Tab, in the frame
titled "service startup account":
(1) the circle labelled "this account" is clicked (2) the account is filled in using
domain namemssqlserveragent (3) the correct password is filled in taking case
into consideration

g. Under SQL Server Agent, properties, selecting the Connection Tab, in the frame
titled "sql server connection":
(1) the circle labelled "use windows NT authentication" is clicked


h. Right clicking the server and selecting properties show a multi-tab window
(1) the general tab shows that all three autostart boxes are checked
(2) the security tabs shows SQL Server and Windows authentication are in effect
(3) the startup service account shows "this account" selected and the account
is filled in with domain namemssqlserver and the password is filled in (taking
case into consideration)

In all cases above, the domain name is not the domain in which the server resides,
but is in a "trusted" domain.

Any help would be very much appreciated!!!!!

View 2 Replies View Related

Transact SQL :: Check No Inserts Occurring In Tables

Sep 14, 2015

I want to check that no inserts are occurring in 5 tables that are depending on each other and then drop and create those 5 tables. I have scripts to drop and recreate the tables. How do I check that no inserts are happening in these 5 tables?

Table A
Table B dependant on
Table A
Table C dependant on
Table B
Table D dependant on
Table C
Table E dependant on
Table D

View 9 Replies View Related

Replication :: Blocking And Deadlocks Are Occurring Continuously

Jul 2, 2015

We have transactional replication. After creating replication, we found that many blocking and deadlocks are occurring continuously. Will it cause any blocking on publisher db.

View 4 Replies View Related

Error Occurring In ExecuteSQL Task In OnPreExecute (and In OnPostExecute) But Package Not Failing

Jun 5, 2006

When any of
my tasks or script tasks in my control flow, or data flow, have an
error, the entire package, and then the calling package fail, as far as
I've seen, through some CTP versions, beta versions, and the release
version of SQL Server 2005.

But, I've just made a change, in an
sproc called from an ExecuteSQL Task in a PreExecute event attached to
a DataFlow, and that ExecuteSQL Task is reporting an error in the
output window, but the package execution is not being stopped. Why not?

I see something like so

Error: 0xC002F309 at MyExecuteSqlTaskName, Execute SQL Task: ....
Task failed: MyExecuteSqlTaskName
Warning:
0x80019002 at OnPreExecute: The Execution method succeeded, but the
number of errors raised (1) reached the maximum allowed (1); resulting
in failure. This occurs when the number of errors reaches the number
specified in MaximumErrorCount. Change the MaximumErrorCount or fix the
errors.

repeated many times in the output stream, and the same thing for the PostExecute event attached to the same object.

I'm disappointed, because I want errors propagated upwards, as I'm used to.

I
looked, and as far as I can tell, all my ExecuteSQL tasks (in events
and in the regular control flow) have the default settings of

FailPackageOnFailure: False
FailParentOnFailure: False
MaxErrorCount: 1


Do
I have to go revise these settings on every ExecuteSQL Task in every
event handler in every SSIS package? (That will be exceedingly
tedious.) Do I set these on the ExecuteSQL task inside the event? Do I
set these on the event handler itself?

View 3 Replies View Related

Transact SQL :: Query To Give Results Of All Configured Alerts Occurring History

May 14, 2015

We created sql alerts on all our sql servers environments. Now, i want to see each sql server which sql alerts so far got fired and which one never occurs. is there any way, we can get this information from any system database?

View 9 Replies View Related

Severe Error Occurring When Creating Assembly With External Access Permission

Jul 11, 2006

I had created a CLR function in my db and was able to execute it successfully a couple of months ago. But when I tried to execute it today it was throwing errors saying there was something wrong with the permissions on the assembly. So I decided to drop everything and recreate it except I can not longer create the assembly with EXTERNAL ACCESS permissions. Whenever I try to create the assembly I get the followng error:

Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.

I also tried to create the assembly with Unsafe permissions and got the same error. Does anyone know why this error would be occurring now? I tried creating the same assembly on a different SQL 2005 server and it creates successfully and can be executed successfully. Any help would be greatly appreciated!!



Thanks!

GN

View 3 Replies View Related

SQL Query Automatically Count Month Events B4 Today; Count Events Today + Balance Of Month

Mar 20, 2004

I would like to AUTOMATICALLY count the event for the month BEFORE today

and

count the events remaining in the month (including those for today).

I can count the events remaining in the month manually with this query (today being March 20):

SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))

Could anyone provide me with the correct syntax to count the events for the current month before today

and

to count the events remaining in the month, including today.

Thank you for your assistance in advance.

Joel

View 1 Replies View Related

SQL Trigger Events

May 29, 1999

What we are trying to do is watch a table in one database for an insert. If something is inserted into a unique ID column, we want to go to another database, on the same server, and look for that same number and place a value in another column for that record. Any ideas?

View 1 Replies View Related

Reg Delegates And Events

May 16, 2008

as asap pl give the detailed and clear description reg differences between delegates and events

vedavathi zend

View 3 Replies View Related

Reacting To DB Events

Dec 16, 2005

I'd like to build an application that will react to specific changes todata in a set of tables in a database. The application would replicatethese data changes to another database. The target database won't beSQLServer. Neither is this simple replication, at times the applicationwill need to get extra data from the source database before the targetis updated.In other DBMS systems I am involved with the DBMS has the facilty towrite to an application message queue so that the monitoring applicationonly has to monitor the queue rather than a database. What I'd like todo is something like this:1. Some application changes data in a table.2. A trigger reacts to the change and writes a message to an applicationqueue.3. A windows service/process monitors the queue and picks up themessage. It then carries out whatever replication/DB actions are necessary.This would mean defining a number of new triggers on existing tables anddeveloping the windows service/process. Existing applications and theexisting tables in the database would remain unchanged. I'm not awindows programmer but I have someone in my team who is and who willbuild the windows service/process.The bit I'm unsure about is how a trigger can write to an applicationqueue or communicate with the windows service/process. I may be usingthe wrong terminology as I have more knowledge of Unix than Windows.Could anyone help with how I can do this or suggest any alternativestrategies.Thanks In Advance.Laurence

View 3 Replies View Related

PackageStart/End Events

Aug 30, 2006

Just finishing of a large ETL system and have aquestion about the following:-

We have 164 child packages being called from a single parent package which is setup to perform logging to a SQL Server table. Anything that Errors or has Warnings is logged accordingly, however, how do you trap the PackageStart and PackageEnd events? when the child package knows nothing about logging?

My first thought was to have the Parent call the AddEvent SP with the appropriate values, but thought I may check here in case I missed something

View 5 Replies View Related

Catching Events

Aug 30, 2007

Hi,

I'm trying to catch an error and trigger a control flow to handle it. I introduce a control flow to catch "OnError" event, but , despite muy package has some errors it doesnt work...

Another issue, if i omit an error on a transformation object ( in order let the flow continue executing), can this error be managed by an event or record it in a log?

Thanks

View 6 Replies View Related

Events &&amp; Notifications

Oct 25, 2007

Hi

I would like to know how I can get hold the text for a service and event notifications.
What I need is the ability to get hold of a list of the events I'm currently monitoring.

i.e.

Server or database level

I though I would be able to get hold of this in sys.server_event_notifications but it doesn't seem to have any data although my server level event notifcation service is running.

Any help?

TIA

View 1 Replies View Related

Dynamicly Setting Events

Nov 17, 2006

I have the following code and I'm trying to set an event dynamicly in the foreach statement...
SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["BvtQueueConnectString2"]);
SqlCommand select = new SqlCommand("select top 100 b.jobid as [Job ID], bq.timesubmitted as [Time Submitted], b.timereceived as [Time Received], bq.requestid as [Request ID], bq.timecompleted as [Time Completed], bq.buginfoid as [Bug Info ID], e.eventid as [Event ID], bq.closingeventid as [Closing Event ID], et.eventtype as [Event Type], e.eventtime as [State Start Time], l.twolettercode as [Language], p.projectname as [Project Name], p.packagedesignation as [Package Designation] from bvtjobs b inner join bvtrequestsnew bq on b.jobid = bq.jobid inner join eventlog e on bq.requestid = e.bvtrequestid inner join eventtypes et on e.eventtype = et.eventtypeid inner join languages l on bq.targetlanguage = l.langid inner join projects p on bq.targetplatform = p.projectid order by b.jobid asc", conn);
SqlDataAdapter da = new SqlDataAdapter(select);
DataSet ds = new DataSet();
conn.Open();
da.Fill(ds);
conn.Close();
Table table = new Table();
 
foreach (DataRow dr in ds.Tables[0].Rows)
{
TableRow tr = new TableRow();
for (int i = 0; i < dr.ItemArray.Length; i++)
{
TableCell tc = new TableCell();
tc.Text = dr.ItemArray.GetValue(i).ToString();
tc.BackColor = Color.White;
if (dr.ItemArray.GetValue(8).ToString() == "COMPLETE")
{
tc.BackColor = Color.BlueViolet;
}
tr.Cells.Add(tc);
}
table.Rows.Add(tr);
}
foreach (TableRow tablerow in table.Rows)
{
TableCell newtc = new TableCell();
Button mybutton = new Button();
newtc.Controls.Add(mybutton);
mybutton.Text = "details";
//set the event here
tablerow.Cells.Add(newtc);
}
Panel1.Controls.Add(table);
DataBind();

View 2 Replies View Related

Events For Selected Month

May 16, 2006

I know how to get the events that start say on May, and I know how to get the events that end on May, however, How would I get the events that start on January and end in July. The month of May should display that event too.
so far, as an example, I have: SELECT
Events.startDate,
Events.endDate

FROM Events
WHERE
Events.Active = 1
AND
startDate BETWEEN convert(smalldatetime, '5/1/2006') AND convert(smalldatetime, '5/31/2006')

ORDER BY Events.startDate ASC; thank in advance. 

View 3 Replies View Related

Server Scheduled Events?!

Apr 29, 2004

I am not sure if I have posted something about this, but I can't believe there is not a single person out there that is using a procedure written in SQL in order to schedule sending of an email, if data hasn't been submitted.
I have had some hits and some help from some people but I am pretty much still stuck.

How is everybody else doing these sorts of thing,scheduling an email to be sent to a user if he/she hasn't submitted data,somebody must be doing it?
A sample, help ,anything???

Regards

View 12 Replies View Related

Lock:Timeout Events

Jul 23, 2005

I am tracing a SQL Server 2000 production server that gets a queryabout every second. The Event I chose to watch was "Lock:Timeout". Tomy surprise I see many of these come through the trace. Is this normalbehavior? What is "Lock:Timeout" showing me?Thanks,John

View 2 Replies View Related

Viewing Events As They Happen?

Mar 5, 2007

Hi,I am trying to debug some queries that are being generated by anexternal program, and I have no way of finding out what the actualsyntax of the query is from within the program itself. This means thatwhen the query fails I am left with only a very unhelpful message.So what I was wondering, was whether there is a way that I can view theactual queries that are sent to SQL server as they happen, or if areal-time solution doesn't exist, then some way I can look back afterthe event and see the syntax of the queries?Ideally, what I would like to do is see the queries in the form theywere sent to the server, ie "SELECT * FROM foo WHERE bar='foobar'", asthis would help me to figure out where the generated queries are goingwrong.Cheers,--Dylan Parryhttp://electricfreedom.org | http://webpageworkshop.co.ukThe opinions stated above are not necessarily representative ofthose of my cats. All opinions expressed are entirely your own.

View 5 Replies View Related

Equivalent To Database Events

Jul 20, 2005

Hiwe are considering porting an application from Ingres to SQL Server.Part of the application uses a feature of Ingres called databaseevents. These allow a application to monitor for an event happening inthe database, e.g. a user enters a record meeting certain conditions,the database raises an event including a record ID, and a clientapplication sees the event (without continual explicit polling), readsthe new record and starts processing it.Any idea what the equivalent functionality would be in SQL Server2000, or are applications simply not done that way?TIAChloe CrowderThe British Library

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved