I have one PS script; Basically it reads all application logs and give a report. Here is the PS script:-
$logs='C:AppLog*.log'
$ufo=Get-Date -ufo '%Y-%m-%d' # Unidentified Flying Object
$match="^$ufo.+error.+$"
sls $match $logs|
group filename -noe|%{
[pscustomobject]@{Name=$_.name;Date=$ufo;'#Errors'=$_.count}
[code]..
Now I would like to setup a SQL job and want to get the result in email.
Here below is the perfect query i made which is working fine and giving me the sql output but just only need is how to convert to excel and automate the job scheduling so that it run on everyday and send the mail with attachment .
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn
FROM
(
SELECT DN, cn, displayName, mail, objectClass, sAMAccountName, Company, givenName, sn, 1 [ordering] FROM alpha.dbo.DCADFeed
where sAMAccountName collate SQL_Latin1_General_CP1_CI_AS in
[Code] ....
Inside some TSQL programmable object (a SP/or a query in Management Studio)I have a parameter containing the name of a StoreProcedure+The required Argument for these SP. (for example it's between the brackets [])
EX1 : @SPToCall : [sp_ChooseTypeOfResult 'Water type']
EX2 : @SPToCall : [sp_ChooseTypeOfXMLResult 'TABLE type', 'NODE XML']
EX3 : @SPToCall : [sp_GetSomeResult]
I can't change thoses SP, (and i don't have a nice output param to cach, as i would need to change the SP Definition)All these SP 'return' a 'select' of 1 record the same datatype ie: NVARCHAR. Unfortunately there is no output param (it would have been so easy otherwise. So I am working on something like this but I 'can't find anything working
DECLARE @myFinalVarFilledWithCachedOutput
NVARCHAR(MAX);
DECLARE @SPToCall NVARCHAR(MAX) = N'sp_ChooseTypeOfXMLResult
''TABLE type'', ''NODE XML'';'
DECLARE @paramsDefintion = N'@CatchedOutput NVARCHAR(MAX) OUTPUT'
[code]...
Hi,
i am using xp_sendmail to notify system users when certain actions happen in the database but cannot control the format of the output. I am building a string for the message in the a trigger and then use
exec xp_sendmail @recipients='email address', @Subject='subject',@message=@msg
however in the @msg variable it would make a lot more sense if i could include linebreaks.
I have tried using html tags and setting the outlook installation on the server to send in html format but that doesnt work, the html tags are output as text in the message.
i have tried putting CHAR(13) in the @msg variable as i build it up but that doesnt work either.
does anyone have any ideas please?
Hi,
I was wondering is there was is a way in SQL 2005 to not only email job completion notifications but also include the output of a step (e.g. TSQL step) within the email. I was able to accomplish this in the past with SQL 2000 by writing output to a file and then adding an extra CMDEXEC step that exectuted BLAT (CLI SMTP engine) that sent the contents of the output file as email body out of band. Although this worked and I can do the same with 2005 it is not very elegant and I was wondering if 2005 version might have something built-in.
Any thoughts?
Thank you,
-Ed
Hello,
Is there a way that I can set up an error handler on a DataFlow so that errant rows within the DataFlow are delivered to a user via email?
I'm looking for Primary Key Violations. In this case, there are two columns of data involved in the DataFlow (AcctCode, RebateRate).
Thank you for your help!
cdun2
Everyday morning I email the sql query/stored procedure output results to the users, I was wondering if I can use some kind of t-sql code or DTS packages so that I can automate this process where I want to send the sql/stored proc results in the body of the email.
View 7 Replies View RelatedHow can I turn this query into an Email alert if one of the counts are GT 50. The Source are machines that I capture counts(Unprocessed_Cntr) from every 5 minutes and load to the Load_Time_Capture table. I also add a datetime to each capture(Unprocessed_Capture ).
If any individual source has a count > 50 or if the combined(ALL) GT 50 send an email to support person.
SELECT
CASE GROUPING([Source])
WHEN 1 THEN 'ALL'
ELSE [Source] END AS 'Input Source',
avg([Unprocessed_Cntr]) as 'Average Queue Past 15 Min'
FROM Load_Time_Capture
WHERE Unprocessed_Capture >= DateADD(mi, -15, Current_TimeStamp)
GROUP BY [source] WITH ROLLUP
I am getting email from the end client and i need to validate in sql query.
View 3 Replies View RelatedI’m running a data integrity procedure from an agent job that is a scheduled weekly maintenance task which emails the results of my query, and is working properly.
I would however like this to only receive the email it the query contains results, and not send it no records are found to prompt me to take action.
My code less the personal information
EXEC msdb.dbo.sp_send_dbmail
@profile_name
= '',
@recipients
= '',
@subject
= 'Database Integrity - Import Errors',
[Code] ....
Is it possible for to send an email if a job fails as an alert? Can the email tell me which step failed?
I know how the code to send an email, but unsure how to trigger this for an agent job failure.
Trying to find tsql for TRIGGER which will send me email, if someone rename database in test/dev environment. I found it for CREATE and DROP database trigger but could not find for RENAME database.
View 8 Replies View RelatedI have a stored procedure which will send an email notification everyday the count of names and count of logins at night. And this sends like a link so the users can directly click on the link(s) and see who all logged in. This works perfectly fine.
Now my requirement is that if there is no count for either name or logins it should send just as text and not as link.
Select count (name) from dbo.test=0 just plain 'text total count of names 0'
Select count (logins) from dbo.test1=0 just plain text 'total count of logins 0'
I am wanting to fire-off an email with the failed jobs anytime they are deposited into a table. My syntax fires off an email even when the table does not contain data, it just sends a blank email. this will only generate an email if teh table contains data?
if exists (Select from FailedJobs)
exec msdb.dbo.sp_send_dbmail
@profile_name = 'DatabaseMail'
@recipients = 'asdfasdfsdf@aafas.com'
@from_address = 'asdfasdfacasca@cc.com'
@query = 'Select * from failedjobs'
@subject = 'List Of Failed Jobs'
@attach_query_result_as_file = 1;
I have a table that gets queued up with a list of people for example email, first name, temporary login account and temporary password.How would i create a store procedure to feed these fields into the html message for each record. For example:
Dear <first name>,
Your temporary access is listed below.
Login: <temporary login>
Password: <temporary password>
I am not sure how you insert the data into the html message. It has to be in html because the message has a couple hyperlinks.
I have some records (approx 100K - 500K) in my table (Name, DOB, Zip, Phone). I want to validate each record and every field as given below.
Name - Should not have numerics
DOB - Should be in yyyy-mm-dd format, should be < 1995
Zip - Should be 5 char zip, allowed 3 or 4 char zip for some States
Phone - Should have 10 numeric digits, should be in xxxx-xxxx-xx format.
After validating these rules, If the record is valid then send email saying valid record, if invalid then send email with validation failure details like which column failed which validation rule suppose DOB might not be in specified format or it might be > 2000 all these details.
How to validate all the records in bulk without any loop or cursor.
I am trying to insert a carriage return in the select statement after the web link where I had highlighted code in bold. When I insert a record into the table, I receive the email with the message body is in single line.I need the result to look like this in the message body:
ALTER TRIGGER [dbo].[SendNotification]
ON [dbo].[TicketsHashtags]
FOR INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[code]....
I have a query running and returning 3 columns, user name, e-mail and device name
SELECT DISTINCT v_R_User.Full_User_Name0 AS 'User full Name', v_R_User.Mail0 AS 'E-Mail', _RES_COLL_DEV00144.Name
FROM v_R_System INNER JOIN
v_R_User ON v_R_System.User_Name0 = v_R_User.User_Name0 INNER JOIN
_RES_COLL_DEV00144 ON v_R_User.User_Name0 = _RES_COLL_DEV00144.UserName INNER JOIN
v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
Where v_R_User.Mail0 <> ''
ORDER BY 'User Full Name'
From here I would like to generate an e-mail to each user (like mail merge) to each user in the table an include their machine name. I can do it with PS, but rather have it run directly from SQL. Is it possible?
I have a query which sends email notification of count of pending name and suggested name.. on a daily basis. So this works fine but now I want like if the pending and suggested are ' o count' I don't want the email notification. It should not send an email, if it is o count, but if we have the number for pending and suggested this email should be sent.
View 8 Replies View RelatedI have to send updated Employee list from employee master table to a particular email ID on every last date of Month and when a new employee is added / deleted / edited. Also need to send this as an Excel file
I tried the following but "Invalid Object name dbo.tbl_EmployeeMaster" error coming while inserting a new employee.
USE [eXact]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_Email]
[Code] ....
I am using SELECT FOR XML which is working great.My problem is writing the results out to the filesystem.I am using spWriteStringToFile procedure that uses Scripting.FileSystemObject to write the file.The file gets written and all of the "xml" is there, but there are no CR/LFs and parsers, browsers and validators don't like it.What can I do to get a more usable output file?
View 11 Replies View RelatedI am trying to update a table and then also use OUTPUT clause to capture some of the columns. The code that I am using is something like the one below
UPDATE s
SET Exception_Ind = 1
OUTPUT s.Master_Id, s.TCK_NR
INTO #temp2
FROM Master_Summary s
INNER JOIN Exception d
ON d.Id = LEFT(s.Id, 8)
AND d.Barcode_Num = s.TCK_NR
WHERE s.Exception_Ind IS NULL
The above code is throwing an error as follows:
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.Master_Id" could not be bound.
Msg 4104, Level 16, State 1, Procedure Process_Step3, Line 113
The multi-part identifier "s.TCK_NR" could not be bound.
I’m thinking about the best way to run these queries, which need to be run regularly.
The first query is two tables linked, one is a data table containing a unique person identifier and three activity fields, the second a lookup table with an activity_type linked to the activity in the table data.
Data Table
PersonID
Lots of other fields
Activity1
Activity2
Activity3
The ACTIVITY fields can contain data anywhere between all being NULL and all being complete.
Lookup Table
ActivityID
ActivityDesc ( which appears in Activity 1 – 3)
ActivityType
I’d like to create a function which will create a recordset containing the Person ID and the Activity Type. I am unsure as to whether to do this in a way which will create one record for each person, or potentially 3 records for each person. This is how I have done the 3 records:
SELECT PersonID, Activity1 As Sport, ActivityType
From dbo.tblActivity
LEFT JOIN dbo.tblLUActivityType ON dbo.tblActivity.Activity1 = dbo.tblLUActivityType.ActivityDesc
UNION
SELECT PersonID, Activity2 As Sport, ActivityType
[Code] ...
And this is how I have done the 1 record:
SELECT ClientID,
Activity1,
(SELECT ActivityType from dbo.tblLUActivityType where ActivityDesc = Activity1) As ActivityType1,
Activity2,
(SELECT ActivityType from dbo.tblLUActivityType where ActivityDesc = Activity2) As ActivityType2,
Activity3,
(SELECT ActivityType from dbo.tblLUActivityType where ActivityDesc = Activity3) As ActivityType3
From dbo.tblActivity
LEFT JOIN dbo.tblLUActivityType ON dbo.tblActivity.Activity3 = dbo.tblLUActivityType.ActivityDesc
Order by PersonID
The reason I would like to do this is because I need to create a stored procedure which returns one record per person with two fields (Person Id, ActivityType) which states their ActivityType depending on certain rules:
Rule 1: If any of Activity 1 – 3 are ‘O’ report the Person ID and ActivityType = ‘O’
Rule 2: Of the rest of the recordset, if any of Activity 1 – 3 are ‘N’ but none of Activity 1-3 are ‘O’ or ‘A’ then report the Person ID and ‘N’.
Rule 3: Of the rest of the recordset, if any of Activity 1 – 3 are ‘A’ but none of Activity 1-3 are ‘O’ or ‘N’ then report the Person ID and ‘A’.
Rule 4: Of the rest of the recordset, if any of Activity 1 – 3 are ‘A’ and one of the is ‘N’ then report the Person ID and ‘AN’.
At the end this I’m looking for one recordset with two fields containing a personID and one of ‘O’, ‘A’, ‘N’ or ‘AN’. I can do the first part of this in any way necessary, as long as the second part returns the recordset I need.
I have 4 different queries in one SSMS New query window that are returning expected results in 4 resultsets. However I want to output these results to a single .js file one after the other in the order of queries. Is that possible?
View 7 Replies View RelatedI am trying to create a proc and at the end of the proc I want to call another proc and pass to one of the parameters to proc using the result from the "OUTPUT". Is it possible to use the results from the "OUTPUT" and use them as parameters?
USE [MyDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
I have a stored proc which will insert and update the table. In this stored procedure I have a output parameter called @rows which is by default 0. Now when ever I insert or update the table and it is successful, then the output parameter should be 1 i.e the out parameter should return value 1 or else default 0.
Here is my code:
ALTER PROCEDURE [dbo].[sample]
(
@TestVARCHAR(256),
@Created_by Nvarchar (256),
@name nvarchar (100),
@rows int=0 output
)
[Code] ....
I have a SP that will generate a pivot output. I want to add a grand total at the end row to sum up the counts for each column.
the SP is as below :
/* COLUMN HEADERS*/
DECLARE
@columnHeaders NVARCHAR (MAX)
SELECT
@columnHeaders = COALESCE ( (@columnHeaders) + ',[' + [Date] + ']', '[' + [Date] + ']')
[code]....
I am getting the below error:
Invalid column name 'Grand Total'.
Msg 205, Level 16, State 1, Line 16
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
We are using Confluence 5.6.5; essentially I want to output some data from a SQL Server 2012 Database onto a Confluence page; how to go about achieving this?
View 3 Replies View RelatedI used the MERGE function for the first time. Now I have to create a pipe-delimited delta file for a 3rd party client of any deltas that may exist in our database.
What is the best way to do this? I have OUTPUT to a result set of the deltas...but I have to send over the entire table to the 3rd party via a pipe-delimited file.
Using t-sql, how to list all views in dependency order? I'm trying to script out the creation of views, but need to make sure they are in the correct order when i execute them.
View 2 Replies View RelatedIn Sql Server 2008 R2, I am creating a procedure with multiple transactions in it. Try..Catch Block is used for each transaction. I use output parameter to catch the error code and message since it will be caught by the main program. But When there is errors the output parameter are not correct set to the error message and code?
create procedure xxx (@P_Return_Status VARCHAR(1) OUTPUT, @P_Error_Code INT OUTPUT,)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION TR1
.....
COMMIT TRANSACTIOn TR1
END TRY
[code].....
How to summarise the data in this table to a single row output per site (2 records for every SiteID). I am based in the UK so the data copied from SQL is in the US format. I can convert this to UK date without any issues.
CREATE
TABLE [dbo].[MRMReadings](
[SiteIncomeID] [int] IDENTITY(1,1)
NOT NULL,
[SiteID] [nchar](5)
NOT NULL,
[Code] ....
Is it possible to return the data in the following format merging 2 lines of data into one output:
SiteID ReadStartDate ReadEndDate ReadStartIncome ReadEndIncome
L0020 19/05/2015 05:00 20/05/2015 05:00 85.98 145.98
L0101 19/05/2015 22:07 20/05/2015 22:14 1,936.08 1,438.89
L0102 20/05/2015 21:16 19/05/2015 21:48 143.65 243.5
I am using SQL 2008 R2.
Table Structure
EID COLA COLB
1 name A
1 age 23
1 city hyd
1 email abc@live.in
1 mobile 45126
2 name B
2 age 43
[code]....
how to display the result where any of the mandatory fields (name,age,city,email,mobile)are missing then it should display as that field as Null