Alerting Operators For All Jobs
Jan 26, 2007
Hi there,
I'm not sure which section this really comes under and I think 'availability/disaster recovery' is probably the most applicable, sorry if it isn't.
I have SQL Server 2005 set-up and about 50 jobs listed, now, these jobs were configured before an operators with alerting by email was set-up... so, none of the jobs when fail alert an operator.
Is there any way I can automate a solution so if any job fails a designated/or default operator will be emailed and told what happened? Or would I need to edit each job manually to set this?
Thanks
Ed
View 1 Replies
ADVERTISEMENT
Jul 4, 2006
Hi
I need to have a reliable alerting system for my merge replications I have setup on my MSSQL 2005 server.
The problem is that the build-in alert system the 'agent failure' alert only triggers when 'all databases' for that specific alert is selected. There is no failure alert triggered when I select a database and force a failure. The 'agent succes' alert does never get triggered at all.
I need an reliable succes and failure alert per database because I need to do specific actions per database.
Can someone help me out here?
I'm thinking of building my own alerting system if the help here is insufficient. In that case I need to know in what tables to look. Maybe someone can give me some pointers?
Thanks anyway,
Edward
View 5 Replies
View Related
Aug 13, 2014
I have a tempdb split into 4 files (5 if you include the log).
Autogrowth is disabled on the mdf/ndf files so that they can be used round robin (1 file per logical CPU).
Is there a way to be alerted when there is x% of free space left?
I know hwo to check the free space via t-sql but want to be able to be alerted. I could run a sql job that reports the free space and send a database mail message if under x% but wondered if there was a built in (or better) method?
I also have SQL Sentry.
SQL 2012 Standard
View 9 Replies
View Related
Mar 18, 2005
Hello,
I am trying to filter records according to an exact string case match and the operator = does not seem to do the job.
Executing,
SELECT * FROM Customers WHERE kUserId='admin' AND strPw='abcd'
and
SELECT * FROM Customers WHERE kUserId='admin' AND strPw='aBcD'
always fetches the same record.
So, how do I do about employing case-exact filtering?
Thank you very much.
David
View 1 Replies
View Related
Oct 28, 2007
I apologised if this has been posted before. I am having so many problems with these operators.
I can get this to work..
" Select *
from emp
where job is not 'manager';
but not
" Select *
from emp
where job is not 'manager'
or where job is not 'president' ;
View 2 Replies
View Related
Mar 19, 1999
Am I hallucinating or does SQL Server 6.5 not support the EXCEPT and INTERSECT operators?
View 1 Replies
View Related
Aug 9, 2006
Hi ,
In a stored procedure when retrieving records based on a DATETIME values in WHERE clause - can we use BETWEEN clause or Col <= AND Col >= ?
Please suggest which is the optimised way.
Thanks in Advance,
Hari Haran Arulmozhi
View 2 Replies
View Related
Mar 21, 2007
Hi I just got a paging procedure from the internet and it’s working just fine, but I would like to return the numbers of pages too. So I used the Count() function to retrieve all records on the table and divided by the page size, but even I doing with float number, this is returning a integer number. I just started with SQL Server, does some knows what is wrong with the following code? or knows a better way to do it.
Declare @Records int,
@Pages float,
@hey int
Set @Records = (Select Count(*) as 'Friends' From friends
Set @Pages = ((@Records) / (@PageSize))
Set @hey = (@Pages)
if @Pages > @hey
begin
@Pages = @Pages + 1
end
return @Pages
Thank you very much.
View 4 Replies
View Related
Jun 12, 2006
Hi ,
when
operator = then index SEEK
operator <> then index SCAN
Is normal ?
Example
SELECT *
FROM dbo.Batch
WHERE (Status = 'Batch Completed')
(1 row(s) affected)
StmtText
---------------------------------------------------------------------------------------------------------------------------------
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([PriceAvisPr].[dbo].[Batch]))
|--Index Seek(OBJECT:([PriceAvisPr].[dbo].[Batch].[IX_Batch]), SEEK:([Batch].[Status]='Batch Completed') ORDERED FORWARD)
StmtText
---------------------------------------------------------------------------------
SELECT *
FROM dbo.Batch
WHERE (Status <> 'Batch Completed')
(1 row(s) affected)
StmtText
------------------------------------------------------------------------------------------------------------------------
|--Clustered Index Scan(OBJECT:([PriceAvisPr].[dbo].[Batch].[PK_Batch]), WHERE:([Batch].[Status]<>'Batch Completed'))
View 2 Replies
View Related
Oct 28, 2005
95% of our DB Calls are in STored Procedures, however there are a few that we're finding hard to convert.
Example:
string strSQL = "Some Big *** SQL query with 8 inner joins";
If(Some Variable == something)
strSQL += "AND ...':
if(Something == somethingElse)
strSQL += " AND (this > that OR this <= other + 1);
so on and so forth.
Is it possible to put these types of statements in a Stored Procedure so I can get this out of code?
View 3 Replies
View Related
Jul 22, 2007
What am I missing? I can only select 1 operator to inform.
Should an 'operator' be redefined to have multiple addresses, if so, how should they be delimited?
View 1 Replies
View Related
Oct 13, 2006
Hi,
I have an implementation of the UDT - 3-dimentional vector. In my code I have implemented add, subtract and multiply methods for the type. I have also implemented overloaded operators for +/-/* in my C# code. Those overloaded operator are working as expected in C# tests. However when I€™m trying to use +/-/* operators in T-SQL over my UDT it returns the following error:
Invalid operator for data type. Operator equals add, type equals Vector.
The following fragment does work:
DECLARE @v1 Vector, @v2 Vector, @v3 Vector;
SELECT @v1 = CAST('1,1,1' as Vector), @v2 = CAST('2,2,2' as Vector)
SELECT @v1 'v1', @v2 'v2', @v1.[Add](@v2) 'v1 + v2'
And this fragment does not work:
DECLARE @v1 Vector, @v2 Vector, @v3 Vector;
SELECT @v1 = CAST('1,1,1' as Vector), @v2 = CAST('2,2,2' as Vector)
SELECT @v1 'v1', @v2 'v2', @v1+@v2 'v1 + v2'
I guess that SQL Server is not aware of the operators€™ overload I have implemented in the C# code. Is there any way to instruct SQL Server to use overloaded operators in the T-SQL so the code will look naturally @a + @b instead of @a.[Add](@b) and as a result use standard summary functions SUM() instead of writing user defined aggregate function for the Vector type field?
Maxim
View 4 Replies
View Related
Jun 19, 2012
I've got a subquery that keeps throwing up an error but I can’t think of another way of completing the query.
Firstly I need to find records where TestQty =3
(this would find 2 records with TestQty =3, TestNumber, 7171003 and 7088650)
The TestNumber is not unique so I would like the final set of records to include all the records with TestNumber, 7171003 and 7088650
TestNumberTestQty
7088650____________3
7088650____________1
7088650____________2
7088650____________1
7088650____________2
7171003____________1
7171003____________3
7171003____________2
Code below:
[Code SQL]
USE TestWarehouse
IF ('dbo.TestItems') IS NOT NULL
DROP TABLE [dbo].[TestItems];
GO
CREATE TABLE [dbo].[TestItems]
( TestItem int not null IDENTITY (1,1)
[Code] .....
View 5 Replies
View Related
Sep 14, 2006
I'm trying to put an expression into a view query column. I've already created a column which contains the results of a calculation in the form of an expression. What I don't know how to do is use comparison operators to return a boolean value for display. I'll give some examples of what I'm trying to do, using literal values just to simplify things...
So say I want to display the result of an expression in a view query column, as a boolean value (or BIT), as True or False. I've tried putting the following expressions into the column and none work:
4 < 5: Access converts this to the string '4 < 5' and it's just displayed as a string.
CAST( '4 < 5' AS BIT ): When run, the query displays the error 'Syntax error converting the varchar value '4 < 5' to a column of data type BIT.
Although I'm using Access 2003, It's just a front end for a SQL Server 2000 database. The actual SQL statement containing the latter SQL function is:
SELECT dbo.Tapes.No, dbo.Tapes.Status, dbo.Tapes.LastWrittenDate, dbo.Tapes.Pool, dbo.Tapes.Location, dbo.TapeJobs.No AS Expr1, dbo.TapeJobs.JobNo,
dbo.TapeJobs.TapeNo, dbo.Jobs.No AS Expr2, dbo.Jobs.Date, dbo.Jobs.Successful, dbo.Jobs.RetentionRule, dbo.RetentionRules.No AS Expr3,
dbo.RetentionRules.Name, dbo.RetentionRules.Description, dbo.RetentionRules.RetentionPeriod, dbo.RetentionRules.RetentionCycles,
dbo.Jobs.Date + dbo.RetentionRules.RetentionPeriod AS AgedJob, CAST('4 < 5' AS BIT) AS Expr4
FROM dbo.RetentionRules INNER JOIN
dbo.Jobs ON dbo.RetentionRules.No = dbo.Jobs.RetentionRule RIGHT OUTER JOIN
dbo.Tapes RIGHT OUTER JOIN
dbo.TapeJobs ON dbo.Tapes.No = dbo.TapeJobs.TapeNo ON dbo.Jobs.No = dbo.TapeJobs.JobNo
I'm new to SQL. I think the CAST function is the way forward, but I just don't know how to force it to resolve the expression '4 < 5'.
Can someone please help ?
--
Paul Anderson
View 8 Replies
View Related
Dec 29, 2007
Hi:
I am trying to create a stored procedure that filters some customers. The field in wich I am trying to apply the filter is the age field. The problem is that I need to be able to select the comparison operator =,<,>,=<,>=,<>.
I was trying to do it with the following code:
Select CustomerName From Customer Where
(CustomerAge & @Operator & @Age)
But sql Server shows an error telling me that @Operartor couldnt be converted to int.
I dont know if I am in the right track or way off, how is this done?
View 3 Replies
View Related
Apr 14, 2008
Hi everybody:
I want know where can I find information about valid expresion types for each one of de Arithmetic Operators, Bitwise Operators, Comparison Operators, Logical Operators, Unary Operators in SQL server. I want something like this.
for (+) ADD, syntaxis -> expression + expression
expression Valid? result type
-----------------------------------------------------------------
int + int ok int
int + bigint ok bigint
char + char ok char <- (+) works like concatenation
char + int ok char???
image + bit error -
binary + int ok binary???
for (*) MULTIPLY, syntaxis -> expression * expression
expression Valid? result type
-----------------------------------------------------------------
int * int ok int
int * bigint ok bigint
char * char error -
char * int error -
image + bit error -
binary + int ok binary????
for (<) LESS THAN, syntaxis -> expresion < expression
expression Valid? result type
-----------------------------------------------------------------
int < int ok bit???
char < char ok bit???
char < int ok bit???
image < bit error -
tks for help.
Jack
View 3 Replies
View Related
Jul 24, 2007
Hello, how do you setup a Profiler filter, that has mixed AND/OR operators. Thru the Profiler GUI, I don't even see how you would ask for any statement/proc with READS over 10,000 OR DURATION over 1000ms. How do you do an "OR" thru the GUI?
Forgetting the GUI and doing traces thru scripts, the @logical_operator isn't too logical, and I can't find any description for setting this parameter. If you only have one filter, it doesn't matter if you set it to 0 (AND) or to 1 (OR)... But if you want multiple filters, so one filter is Reads >= 10,000 and the other filter is Duration >= 1000ms., how do you do that? I tried it with the @logical_operator of "1" on both, but then I also got other events with a NULL value in READS.
Code Snippet
-- @traceID = 1
-- @columnID = 13 (Duration)
-- @logicalOperator = 1 (OR)
-- @comparison_operator = 4 (greater or equal)
-- @value = 1000000 (1000 ms)
sp_trace_setfilter 1, 13, 1, 4, 1000000
-- @traceID = 1
-- @columnID = 16 (Reads)
-- @logicalOperator = 1 (OR)
-- @comparison_operator = 4 (greater or equal)
-- @value = 10000 (# Reads)
sp_trace_setfilter 1, 16, 1, 4, 10000
Looks like DURATION is never NULL for these events>>>
RPC:Completed
SP:Completed
SPtmtCompleted
SQL:BatchCompleted
SQLtmtCompleted
but the READS value cane be null (for SP:Completed at least).
Any ideas on how to mix AND/OR operators in multiple filters of one trace?
Thanks, Bruce
View 1 Replies
View Related
Apr 4, 2008
Hi all, real basic question:
I can't seem to find a basic reference that tells me the syntax and allowable values of parameters in common functions such as Format or CDate when editing a Field Expression in Visual Studio (Report Definition Language?). Where would I start?
For example, I've discovered that the Format function "Returns a string formatted according to instructions contained in a format String expression.", and the string expression can have values like "D" and "d" which produce different results, but where would I find out what the allowable string expressions are and their meaning?
I have also been guessing at the syntax of the CDate command with no luck. I need a reference that tells me what the different function parameters mean.
thanks
Jac
View 1 Replies
View Related
Mar 16, 2014
I am in the midst of writing a query to return a range of product BIN LOCATIONS from a warehouse stock levels program.I know the start and end BIN LOCATIONS for the warehouse, e.g.: Start - #00000, and End - Z10000.However I cannot hard code these into the program, instead I want my operator to designate the first letter of both the start and end BINS locations, and then hit the 'go button'.Towards this end I have declared a couple of variables in my SQL, as follows -
Code:
declare @strBin varchar(10)
set @strBin = ''
if @strBin = ''
set @strBin = '#%'
declare @endBin varchar(10)
if @endBin = ''
set @endBin = 'z%'
My challenge is writing a relevant WHERE clause for my select statement, I have tried the following -
Code:
where BINLOCAT.BINLABEL between @strBin and @endBin
order by BINLOCAT.BINLABEL
Whilst the clause above does not cause any errors, neither does it return any results.My first thought is that the BETWEEN operator does not accept wildcards. But if this is correct then how do I go about allowing an operator to enter the start, and end BINS without typing the entire BIN string (e.g.: #00000)? I have tried >= @strBin and <= @endBin, but I am having a similar issue with no errors, and no data returned.
View 9 Replies
View Related
Mar 1, 2015
We can use comparison operators with strings as well. Hence, I tried to use the following query on a SQL Server 2012 instance with the sample AdventureWorks2012 database (the collation of the database and of the column is the default:
SQL_Latin1_General_CP1_CI_AS):
USE AdventureWorks2012 ;
GO
--Returns 5 records
SELECT pp.Name
FROM Production.Product AS pp
WHERE pp.Name >= N'Short' AND pp.Name <= N'Sport' ;
GO
The query only returns 5 records. This despite the fact that the search is an inclusive search and the Production.Product table contains records that begin with "Sport".
Now, when I replace "Sport" with "Sporu" (just moving one character up in the alphabet to verify whether characters after the word have any impact on the search) gives me 8 records.
USE AdventureWorks2012 ;
GO
--Returns 8 records
SELECT pp.Name
FROM Production.Product AS pp
WHERE pp.Name >= N'Short' AND pp.Name <= N'Sporu' ;
GO
What's going on inside of SQL Server that allows it to fetch "Short-Sleeve Classic Jersey" for the starting word "Short" but prevents it from fetching "Sport-100 Helmet" for the ending word "Sport" despite the search being an inclusive search?
View 3 Replies
View Related
Aug 7, 2015
I have a problem at the moment, where the client wants to be able to type in a custom algebraic formula with add/minus operators, and then to have this interpreted, so that the related datasets are then added and returned as a single dataset.
An example would be having a formula stored of [a] + [b] - [c]
and if I were to write the SQL to apply that formula, I might write something like (let's assume 1:1 relationships with the ID's)
select a.a + b.b - c.c as [result]
from z
inner join tblA a on z.id = a.id
inner join tblB b on z.id = b.id
inner join tblC c on z.id = c.id
The formula can change though, maybe things like:
[a] + [b] + [c] + [d]
[a] + [b]
The developer before me wrote something SQL-based where they parsed the string and assigned each value of the formula as either positive or negative (e.g A is positive, B is positive, C is negative, now sum the datasets to get the result), and then created one large table of values then summed them. This does (kind of) work, I'm just contemplating potential alternatives, as it is quite a slow process, and feels like it is quite convoluted, when I get into the details. If I were to do something like this in SQL, I'd normally want each part of the expression to be a column, and then to just apply the operators, but because the formula can change, then the SQL would need to be somehow dynamic for this approach.
View 5 Replies
View Related
Oct 11, 2015
DECLARE @Teams AS TABLE(Team VARCHAR(3))
INSERT INTO @Teams
SELECT 'IND'
UNION
SELECT 'SA'
UNION
SELECT 'AUS'
select Team from @Teams where Team > 'AUS'
[code]....
co-relation between comparison operators in WHERE Clause and the respective output.
View 3 Replies
View Related
Aug 2, 2005
Hi,It may sound funny but it is true. I have 2 version of same procedure, 1 is named as "update_aggre" other named as "update_aggre_2" .if I run these procedures using sql jobs the 1st procedure takes 12 seconds and the 2nd takes 2 second. I am stil surprise why is that both of them have same code.Any ideas please?and also I need to have lock mechanism within a procedure so that if some one is calling a procedure which is already called by some one ans is stil working on it the second user shld not be able to overwrite the existing data untill the first one is finished.TIAAmit
View 3 Replies
View Related
Mar 20, 2000
We're upgrading to Sql 7 from 6.5 but having problems running jobs with the following sql statement :
SQLMAINT.EXE -D master -BkUpDB e:dump -BkUpMedia DISK -DelBkUps 2 -Rpt d:salogmaster_dump.log
The error message is [Microsoft SQL-DMO (ODBC SQLState :28000}] Error 18456:[Microsoft]ODBC SQL Server Driver][SQL Server]Login failed for
user 'Domainuser'. Process Exit Code 1. The step failed.
The user is a domain account and already in local administrator group. It also starts up both Sql server & Sql server agent. It can run the same job on another server. I have compared the set up and can't find any differences.
Thanks in advance for your help.
View 2 Replies
View Related
Nov 17, 2000
If i've created myself a job on my SQL Server and then scripted it, how do i run this script and so doing create my job on another server?
View 2 Replies
View Related
Jan 18, 2001
Hi, I have a DTS package that runs correctly when executed manually, but when I schedule a job via the package, the job begins executing and nevers seems to complete the run. The Package runs in less that 1 minute, but the job sticks on executing and does not complete. I have to cancel, any ideas?
Thanks
View 1 Replies
View Related
Sep 6, 2000
I have 12 scheduled daily jobs, all the jobs are depending on one job.I want to run rest all the jobs if and only if the Job#1 gets success.Now all are running independently.Is there a way to force the sequence by using SQL.
Thanks!
View 4 Replies
View Related
Aug 30, 2000
If we have several jobs running several times daily,do we have to do any clean-up with the logs/job history so that they don't run into any problem later?Will it be o.k if we do not clear off the history frequently?
View 2 Replies
View Related
Feb 16, 2001
Hi all,
My firm has about 15 SQL Servers. 5 of them are still 6.5 and the other 10
are 7.0. I have noticed that the jobs I have setup to perform maintenance
tasks, backups etc. almost never fail in 7.0. Similar tasks routinely
fail on the 6.5 servers.
So I did a test. I deleted a database in 7.0 and ran a backup job. Although
the log for the job said that it couldn't find the db it still said the job
was successful. In 6.5 if you try to run a job on a non-existent db it
fails. What's going on in 7.0? What has to happen for a SQL Server agent job
to fail. This seems like an awfully dangerous new "feature".
If anyone could shed light on this I would appreciate it.
Thanks
JJ
View 2 Replies
View Related
Mar 5, 2001
Hi,
How do we add DTS pakages to a scheduled job ?
Ex: Lets say i have 2 DTS pakages One has got to run immediately after the other.
>Now i create a schedule for the main DTS pakage.
>Once i create a schedule, It goes into Jobs as a Scheduled job.
>Now in the propertied part of the first DTS job i need to add a second DTS
as Step2, mentioning that it should run after the First DTS.
How do we do this ?
Thanks in Advance,
Siv
View 1 Replies
View Related
Jul 23, 2001
Does anyone know how I can set up some form of alert that tells me which scheduled jobs are still running?
View 1 Replies
View Related
Sep 21, 2001
We had some jobs in Sql server 7.0 which ran together at the same time.
Now we have moved to sql 2000 and when we run those jobs together again they just sit up(keep on going without doing anything) and do nothing.I have monitored that there is no Blocking or any kind of Performance Overload.
These jobs run fine when they are executed Individually in Sql 2000
Any kind of help will be appreciated.
Thanks,
James
View 5 Replies
View Related
Sep 29, 2004
hey guys!! can someone tell me, what are jobs on MS SQL?
and what is a job scheduler? how do it function? what are the processes
involved? and additional information about this stuffs.. thanks..really need it.. tnx tnx..
p.s.
if your wondering
why i asked the questions
here..because it's really
hard to search the meaning
of it in the net..i hope you
guys could help me.. tnx
View 2 Replies
View Related
