Collating All Job Failure Notification In One Place
Jan 15, 2001
Good afternoon one and all,
I have numerous jobs running on my SQL Server machine. Due to the fact that my company will not sanction an e-mail account for a machine (damn them!!) I have no way of knowing (quickly) whether a Job has failed. Does anyone hae any suggestions on how I can create a file or SQL table that is updated every time a job fails? (writing to NT application log is not an option beacause Operations refuse to trawl thru it).
Hi, I have scheduled a job that runs every minute. If the job does not succeed, then I would like my front end application to be notified. I am not sure if this is a reasonable way but I am thinking of somehow populating a table (Only if the scheduled job fails) in sql server and then read that table every minute from the front end application.
So, is it possible to populate a table if a job fails? I do not see any options for this in the properties of the job.
I had configured SQL Agent and SQL Mail to use the profile that I created by logging in as the SQL Agent domain admin. service account. The mail service was successfully working. Now, when I create an operator with my mail account and test it, Agent gives the message that the mail was successfully sent, but I don't actually receive any mail.
We have an application comprising of a number of SSIS packages that run every few hours. When a package fails, we would like an email to be sent to a pre-configured address with the required information. I would like to use Send Mail task with SMTP server like smtp.sbcglobal.yahoo.com, but this server requires authentication and I need to provide my user_id and password for that. I have not been able to figure out where I can configure user_id and password for getting authenticated by the SMTP server. If SMTP connection cannot be used, is there any other way to notify admin of the failure?
I tried using Query Notification on my computer at home: * Win XP Pro with all the SPs and hotfixes * SQL 2005 with SP1 qand hotfix
Query Notification worked fine.
Then I tried using it at work:
* Win XP Pro with all the SPs and hotfixes
* SQL 2005 with SP1 qand hotfix
and I see the following error in the SQL server log file and notification does not get to the client app:
---------------------------------------------------------- Date 9/1/2006 10:18:30 AM Log SQL Server (Current - 9/1/2006 10:18:00 AM)
Source spid17s
Message An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 19. Could not obtain information about Windows NT group/user 'domainmyuser', error code 0x6e. ----------------------------------------------------------
A similar error shows up in the machine's Event Log.
I am sysadmin and full OS admin on both boxes. The difference is that the computer at home is standalone while the one at work is part of a domain.
Does anybody have any experience/advice on how to ensure that SOAP service call success/failures are returned to the calling app?
Consider a client that calls a SOAP service during which the client goes down and is unable to receive the SOAP response, the work having been done by the service. Similarly, the SOAP service may perform the task but a failure in the return makes the client think the process failed.
What would be the best way to ensure that the client is notified to avoid the call having to be made again?
Are there middleware tools that can be used to provide a form of message queuing for SOAP service calls?
I use SQL Server 2000 and a Delphi 7.0 app. We have migrated our database recently to a new collation "Turkish_CI_AS". Because of problems with the "i" and "I" on the Turkish alphabet, we have to make some alterations on our app. No big deal. All working fine.
So, we send our app to partners and it begin to act "weird".
Talking to them, we discovered that the collation that they use on their database is the "Turkish_CI_AI".
Some problems that they get on their plataform when running our app are as follow:
1. When trying to open a table (using SQL via ADO components on Delphi 7) , receive the error "Item cannot be found in the collection corresponding to the correct name or ordinal".
2. When the user select one item on a DBComboBox, the information that correspond to the item are show on the DBGrid, but the item is not showed on the DBComboBox and when he try to update the information, pop up an error message "Row cannot be located for updating. Some values may have been changed since it was last read", and this is not true.
I've done some research on net for the first error, and don't understand... It appears to occour when the table name or column name that are been ask to the server donīt exist, but on our system all work well (no problems)...
Someone can say to me if this is a collation problem or is anything else? Like a wrong version on MDAC for exemple, on the other (Turkish) system?
For now, they cannot change the collation and neither can we, so, I cannot test if these errors occour on our system if we change the collation to "Turkish_CI_AI". Anyway, I don't see this like possible, because we don't use accents on table names or column names, but on the impossibility to test, it's best ask to someone that maybe knows the answer...
I am trying to create a report where the rows of the database have fields containing customer names and dates associated with the names. I want to know for each customer how many records the customer has for specified date ranges.
SELECT [DL].[Customer], Count([DL].[Date Received]) AS [CountOfDate Received1] FROM [DL] WHERE ((([DL].[Date Received]) Between #12/31/2012# And #1/1/2014#)) GROUP BY [DL].[Customer];
The idea is a list of the number of records for each customer in 2013.
Ultimately my goal is to show the customer activity over multiple years.
So, Customer Name 201120122013 Customer A123 Customer B246 Customer C543
I am trying to go down the path of a Union:
SELECT [DL].[Customer], Count([DL].[Date Received]) AS [CountOfDate 2013] FROM [DL] WHERE ((([DL].[Date Received]) Between #12/31/2012# And #1/1/2014#)) GROUP BY [DL].[Customer]
[Code] ....
This returns 2 columns only not the four I am looking for.
I am using ODBC to connect SQL Server 2000 + SP4 server runnig on Windows 2003 standard edition Server +SP1. But, sporadically my application server connectivity to DB fails and i receive the following error messages
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (WrapperWrite()). [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.) In D:de.cpp 702 Apr 12 2006 20:34:47 [Microsoft][ODBC SQL Server Driver]Communication link failure) In D:de.cpp 702 Apr 12 2006 20:34:47 20071017 08:23:10 TID (00000ff0) Sev (3) Err (0) Msg (Read failure. General SQL error.
Strange thing is that i receive this error only sporadically, Please Please advice !
Can anyone give me any input on this. Recently TempDB one of my production server came down because tempDB got so big that it chewed up all the space in it's drive. My TempDB was in drive C:, where the Operating system and the rest of the SQL systems databases are(msdb,model,master). The actual production data are located in another logical RAID 5(Drive E:) Drive. I want to prevent the problem from happening again. Is it wise or does it degrade performance if i move TEMPDB from drive C: to drive E:? Is this going to cause a major bottom neck in drive E:, where the data are located?
what happens if the physical location of a box(which had sql server 2000 on it) is chaned. what happens to the replication and distributed queries. Thanks.
Where would i place an orderby my DateCreated field...everywhere i try to place it i get this error... The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified. Any help with this issue would be greatly appreciated....
CREATE PROCEDURE GetTimeCard
( @LoginID nvarchar(50),
@DateRangeFrom datetime,
@DateRangeTo datetime
)
AS
BEGIN
IF ( @DateRangeFrom = '1/1/1753' ) AND ( @DateRangeTo = '1/1/1753' )
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated ,oe.FirstName,tc.DateEntered, oe.LastName ) x
left outer join (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, sum(tce.ExpenseAmount) as TotalExpenses
from OPS_TimeCards tc
join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated,tc.DateEntered ) x1 on x1.TimeCardID = x.TimeCardID and x1.DateCreated = x.DateCreated and x1.DateEntered = x.DateEntered
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated, tc.DateEntered) x2 on x2.TimeCardID = x.TimeCardID and x2.DateCreated = x.DateCreated and x2.DateEntered = x.DateEntered
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID And tc.DateCreated BETWEEN @DateRangeFrom AND @DateRangeTo
group by tc.TimeCardID, tc.DateCreated ,oe.FirstName,tc.DateEntered, oe.LastName ) x
left outer join (
select tc.TimeCardID, tc.DateCreated,tc.DateEntered, sum(tce.ExpenseAmount) as TotalExpenses
from OPS_TimeCards tc
join OPS_TimeCardExpenses tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated,tc.DateEntered ) x1 on x1.TimeCardID = x.TimeCardID and x1.DateCreated = x.DateCreated and x1.DateEntered = x.DateEntered
join OPS_TimeCardHours tce on tc.TimeCardID = tce.TimeCardID
join OPS_Employees oe on oe.EmployeeID = tc.EmployeeID
where oe.LoginID = @LoginID
group by tc.TimeCardID, tc.DateCreated, tc.DateEntered) x2 on x2.TimeCardID = x.TimeCardID and x2.DateCreated = x.DateCreated and x2.DateEntered = x.DateEntered
I have a design that includes articles that will be searched. Obviously its too slow to put them into fields, and impossible because some have photos or are otherwise html documents. So I want to put pointers to their location.
Two questions. For each deployment, both desktop and web, where is the best place to put the articles. In any folder, or only in an iis virtual folder?
I'm rapidly understanding that much more of my application as a whole is in SQL Server that I would have originally thought.
Stored Procedures Triggers Constrains And so on
It generally means that some of the stuff I'd have naturally done in the Business Layer might be best done in SQL - certain issues in the Business Layer might be best being triggers or constraints for example...
One thing that still puzzles me, and I'd like some references or advice now as it's a blank area in my mind is how this interfaces to your asp.net code.
Obviously I call stored procedures and the like from code, and use parameters, etc, not problem, it's more what I do when these stored procedures or associated triggers fail (or a constrain fails - though this should be less likely)?
SQL sends back an error? But what? Then what do you get your page to do, especially if SQL failed midway through a 'big' transaction? Do you have save 'where the user was somehow' so they don't start inputting again?
It's all a bit vague at the moment, some detail would be nice? :)
Hello all,I have an SQL query which retrieves a COUNT number from 2 different tables, and i want to do a division with botht he COUNT data retrieved. Trouble is I can't get it in the format that I want, my SQL query is as below :-SELECT ROUND( ((T1.Present/T2.Total ) * 100), 2) FROM(Select Count(Date) as Present from Attendance WHERE Month(Date)=12 AND Status=1) T1,(Select Count(Date) as Total from Attendance WHERE Month(Date)=12) T2The trouble here is that the result should be as below: T1.Present = 3T2.Total = 5 T1.Present/T2.Total = 3/5 = 0.6The final should be 60 after divided by 100But i am getting a zero as my result, even when I don't multiply the number by 100, the division result is still zero. I am guessing it is a conversion problem. Could anyone please offer me any advise on how to get the final result in the format I want?
Hi, I want to replace the indexes in my database to a different filegroup. How can I do that using T-SQL? I only found a way that uses the EM, but I have a lot of indexes and I hate to do it manually.
One of our customers has a production and a test database. We are loading tables via a C++ program that works on production, but not on test, which is supposed to be an exact duplicate.
The error we are getting when trying to add columns to one of the table is 80004e21 null That is it.
When we load the exact same records in the production environment we do not get the error. We have spent many hours ensuring that the tables are exactly the same on test as in production.
Does anyone know, is there an environment variable at a database level that says how to handle null values?
Does anyone know if it is possible to use a variable in place of a database..table combination in a select statement
For Example: Instead of using the following with each database hardcoded in the SP:
select @dataused = sum(b.reserved) from DBSglep..sysindexes b where b.indid in (0, 1, 255) and segment != 2
I would like to loop for every database listed in sysdatabases and do this:
select @dbname = @dataname+'..sysindexes'
select @dataused = sum(b.reserved from @dbname b where b.indid in (0, 1, 255) and segment != 2
I have got the loop working, but just can't get the name substitution working as MSSQL dosn't seem to allow a variable after the FROM statement (it only seems to work with a hardcoded specific database..table name).
Any assistance in resolving this problem would be greatly appreciated! :-)
Last 2 nights (at night) my SQL Server has locked up, first night trying to back up MSDB(20 meg) and last night ran out of memory at 6:30 AM. No users on at either time, no jobs running on the second night. I was going to turn perfmon on tonite. Any input on what best to monitor?
Hello,I run out of space on the disk, my db log is 130GB, I need to shrink db log. I made a backup through network by UNC of the database, but when I want to backup log, I get a message :BACKUP LOG cannot be performed because there is no current database backup.But the backup is reachable, I can restore from it.Any ideas ?
i'm wondering if i can use a stored procedure in place of a UDF in the case where i want a return value based on a simple calculation involving the input parameter.
i'd like to use this inline in a query somewhere else. that's why the UDF.
the UDF would be something like this ...
create function getFiscalYear (@when datetime) returns int as BEGIN declare @rv int
-- months of Oct, Nov, Dec are rolled into following year if datepart(month, @when) >= 10 select @rv = datepart(year, @when) + 1
-- whereas all other months stay in this year else select @rv = datepart(year, @when) end
END
so, the only reason i'm not using this UDF (and haven't tested it either) is because i can't find (or can't remember how :( ) to add a UDF to my database. when i run this code in Query Analyzer i get an error on the keyword "function". but that's another question altogether.
thanks in advance. (a) for helping with a stored procedure that does the equivalent or (b) nudging me in the right direction towards getting UDFs to work in my SQL 2000 install.
I have a table called Register that has the following in it
Policy_number, Policy_date_time, Portfolio_set, Status..The rule for the table is that the last record for each portfolio_set for a policy the status needs to be 'A' but there have been instances that the last record status is 'I'
I need to identify the record that is out of place..In the example below record number 2.
example
policy_number Policy_date_time Portfolio_set, status 12345 1/1/2011 1 I 12345 1/2/2011 1 A 12345 1/3/2011 1 I 12345 1/4/2011 1 I
I need to identify that the second record is in the wrong place...
. . . UPDATE Employees set depth=0, hierarchy=NULL
UPDATE Employees set depth=1, hierarchy=right(@MaxPadLength + CAST(Employees.Parent AS varchar(255)),@DisplayPadLength) where Child = Parent
WHILE EXISTS ( SELECT * FROM Employees WHERE Depth=0 ) . . . I have many tables that have the same structure as the Employees table but have different names. I would like to pass the PS a paramater with the table name I want to process. My question is what is the correct syntax to use a parameter in place of the literals for the table name?
i have a table name is HH tableit has two columns 'hhno' and hhname'HH tabelehhno hhname100 suresh101 baba103 rami want to insert a one record(102 , chandra) in HH table between(101,baba) and( 103 ,ram).how can i insert them please help ,me thanks
