SQL Server 2008 :: Converting Job Duration From Job History And Use It On Email Notifications?

Apr 20, 2015

I am trying to convert the @runtime to hh/mm/ss format as it's currently printing milliseconds.

Declare @runtime varchar(100)
Select @runtime = sum(run_duration)
From msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
where j.name = 'Accounts_From_App03'
and run_date = CONVERT(varchar(8), getdate(), 112)
Declare @Body varchar(100)
Set @Body = 'The Job completed successfully. Duration:' + @runtime

exec master.dbo.sp_SQLNotify 'App02@email.com', 'team@email.com', 'Scheduled Job Success' ,@Body

View 9 Replies


ADVERTISEMENT

Duration Coloum In The View Job History

Sep 25, 2007

Hello,

I have a critical job.
When i take the view job history of that job here it has a colum named as 'duration'. It gives the exact duration of each step it has taken in its last run.
But i have the same job in test environment.
But when i take take the history here i dont see any duration column. how can i add that column.

Thank you
ichayan

View 2 Replies View Related

Email Notifications

Oct 4, 2007

Some packages I develop have a lot of steps, and some people like myself can go crazy with email notifications, depending on the graph, I sometimes add notifications (success/fail) at the component level, so I can know what happened to that SSIS package from my blackberry and immediately log into the VPN and fix the issue.

The only problem that I have with that is that sometimes, like right now, I see myself adding 6 "send mail tasks" to SSIS packages, one pass/fail for each component.

Basically, I was wondering if anyone had any ideas or suggestions, ideally I wish I could create one single send mail task that could be reused by other components on the package and just update the message/subject through expression and variables.

View 6 Replies View Related

Email Notifications Scheduled Jobs

May 10, 2006

In the notifications tab of the job I have e-mail operator selected. Click on the box with the elipses to enter the email addresses. It will let me enter 2 email addresses in the e-mail name field. It looks like the field has a limited length...is there a way to manually enter several addresses?

View 1 Replies View Related

DB Engine :: Email Notifications To Customers

Jul 17, 2015

I need to develop a process by which customers are notified that their 1) order was received and 2) order was shipped.

We are running Sql Server 2008 and all customers and data are located here on various servers.  It is in a Windows 7 environment. 

Has ever utilized Sql Server Database Mail for this purpose?  Is it feasible?

View 6 Replies View Related

Sending Alerts ,Email Notifications From SQL To CellPhone

Sep 4, 2001

HI,
Does any one know how I could send all the alerts, email notifications from the SQL Server to my Sprint PCS Phone. Is there any software for that. I know that it can be done by MSN mobile. Just checking whether it can be directly sent to my phone rather than going to hotmail which is then forwared to my cell phone. Let me know.
Thanks,
Cinday

View 1 Replies View Related

Transact SQL :: Email Notifications - Count For Name And Logins

Nov 6, 2015

I 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'

View 2 Replies View Related

SQL 2012 :: Detailed Deadlock Event Notifications Via Email?

Nov 1, 2015

We have (running SQL 2012 Std) and have enabled trace flag 1204 and 1222 for capturing deadlock through extended events. I have enabled deadlock notification through email .But it only send deadlock event occurred notification from the sql server error log . I was wondering if its possible to email the deadlock details they get generated in extended events via DB mail.

View 1 Replies View Related

SQL 2012 :: Database Mail - Test Email Works But Jobs Won't Send Out Notifications

Oct 19, 2015

I am trying to send out notifications when jobs complete (fail or succeed). I have database mail working fine on my DEV server, but I am having issues with it on my PROD server. I am currently having people look into if McAfee may be blocking it.

I am able to send out a test email from SSMS>Management>Database Mail, but when I set a Notification for a job, the job will complete and in the history, it will say "NOTE: Failed to notify 'User' via email."

I have created an Operator and set up Profiles and Accounts, just as I did on my DEV server.

View 2 Replies View Related

SQL Server 2008 :: How To Find History Of Blocking Batch / Query

Mar 4, 2015

I see some time email say blocking took place and but it get resolved itself. how to find the history of batch/query which blocked another SPID?

View 3 Replies View Related

SQL Server 2008 :: Cleanup History Has Extra Output Message

Apr 16, 2015

Oldest Day till I want to Purge[30 days of Old Data] : 03-17-2015

Running Cleanuphistory Procedure On Server:

NO of records are qualifying for deletion:0

Deleted Sysmail MAil Items, Old history: 1
Deleted Sysmail Log Old history: 1
0 history entries purged. <<<<<<< ----------------------- [HOW IT IS COMING ?? ??]
Deleted SQL JOB Old HISTORY: 1

*** END OF PROCEDURE. ***

[Code] .....

View 6 Replies View Related

SQL Server 2008 :: SSMS Query Execution Messages History?

Sep 9, 2015

I would like to view the previous executed query error message in SSMS. Is there any way to view the query execution error messages history in SSMS?

View 2 Replies View Related

Converting Audit Data To Create History

Apr 9, 2008

Hi

I have a table called my_history that has columns like this


column_name , old_value, new_value, key, date
bankaccount 30 50 1 01-Apr-2008
bankbalance 10 14 2 04-Apr-2008


and so on............

The history table is populated using triggers

The main table called my has a structure like


bankaccount bankbalance bankname name address key
50 50 xyz abc ford 1
30 14 abc xyz east 2


Now using this information can i reconstruct the records in my table before the update happened ?

I am finding it very difficult to do this is there a way to do this in t-sql ?

The problem is my_history table where the column_name keeps on varrying

regards
Hrishy

View 7 Replies View Related

SQL Server 2008 :: Convert A View To Excel And Then Email?

Sep 15, 2015

I was able to create a view and convert it to excel. Now I want to it to schedule it for everyday and then email the excel file as an attachment to couple of people.

Would SSRS be an option? where I can create a report of the view and schedule it?

Does any know the process I need to follow?

Do I have to uses SSIS ? and then set it up as SQl server job?

View 9 Replies View Related

SQL Server 2008 :: Date Format Not Converting To Dd / Mm / Yy

Sep 1, 2015

I have a date field stored in a character type field ( this field is a spare one in an ERP package )I am trying to validate and convert to a date format using the following;

[code="CASE WHEN isdate( arc.UserField1 )= 1 then
CAST( arc.UserField1 as DateTime )
ELSE
CAST( '01/01/1900' as Datetime )
END"] [/code]

The results are ;

Date Result
29/09/08 1900-01-01 00:00:00.000
09/06/15 2015-09-06 00:00:00.000
18/03/13 1900-01-01 00:00:00.000
09/10/14 2014-09-10 00:00:00.000

as you can see - the date 29/09/08 is a valid UK date format - however it fails the validation. 09/06/15 converts to 6th September 2015 ( mm/dd/yy ) format...What do I need to do to force this to identify as dd/mm/yy date format ?

View 9 Replies View Related

SQL Server 2008 :: Maintenance Plan Delete History Trying To Delete Wrong Files

Sep 11, 2015

I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.

It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog

how I can get this corrected so I can get the Maintenance Plans to run correctly.

I have tried deleting and recreating the Plan but to no avail

View 0 Replies View Related

SQL Server 2008 :: Converting Datatype Of A Computed Column?

Mar 10, 2015

I have a computed column that I want to cast as decimal.

The two columns it calculates from are both varchar.

Why can I cast the column as INT, but when I try to cast as decimal, I get the following error?

Arithmetic overflow error converting varchar to data type numeric.

what this error means and why I get it only when I want to cast to decimal.

View 8 Replies View Related

SQL Server 2008 :: Float Value Converting Before Concatenate Get Rounded?

Mar 30, 2015

In My table formula(varchar) and value(Float) column.

Example

formula --> (A+B)/C

I am trying to create same expression with value But my below example showing value gets rounded if it is big.

I am trying to do,

Declare @a float = 123456.235
SELECT CONVERT( varchar , @a) Result=>123456

any way to keep same value as It is?

View 5 Replies View Related

SQL Server 2008 :: Conversion Failed When Converting Nvarchar Value To INT

Apr 9, 2015

Declare @specificDB nvarchar(max) = 'AdventureWorksDW2012'
,@tablename nvarchar(max) = 'DimAccount'

declare @localtab INT
SET @localtab = (SELECT Convert(INT,('select count(*) from ' + @specificDB+'.'+'INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = ''BASE TABLE'' AND Table_name = ' + @tablename)))
Print @localtab
Print @localtab

----

Msg 245, Level 16, State 1, Line 8
Conversion failed when converting the nvarchar value 'select count(*) from AdventureWorksDW2012.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE' AND Table_name = DimAccount' to data type int.

View 2 Replies View Related

SQL Server 2008 :: Converting Inline Query Into Join

Oct 16, 2015

I have an inline query that I am trying to convert it into JOIN, results are not coming out the same:

Original query:

SELECT distinct
(select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '3') As DcountMy query:

[code]....

View 8 Replies View Related

SQL Server 2008 :: Converting Adjacency List To Hierarchy Table

Feb 10, 2015

I have been trying to convert an existing table that used adjacency list model (parentid,childid) to a table that use hierarchy Id type. So early on, I notice my original data does contains multiple roots. So I took a step to create dummy nodes to ensure all nodes fall into a single root structure. Another important fact is that each child node can have multiple parents.

My original source table contains 22461 records, when running the query below step 2 produces explosive number of records around 175,000+ records. I spent hours study the result and couldn't understand what actually causing this, I ran it against small set of test data I didn't seem the issue caused by child with multiple parents.

select * from SourceTable -- produces 22461 records

--step 1: first, get row number of child records in each parent

SELECT ChildID,ParentID, ROW_NUMBER() OVER (PARTITION BY PARENTID ORDER BY PARENTID) as Num
INTO #RelationshipTmp
FROM SourceTable;

[Code] ....

View 1 Replies View Related

SQL Server 2008 :: Conversion Failed When Converting Varchar Value - Error

Sep 24, 2015

I am getting the following message when trying to run the below sql

Msg 245, Level 16, State 1, Line 15

Conversion failed when converting the varchar value 'Select COUNT(*) From Adventures.dbo.tblLibrary' to data type int.

Declare @Datestart as Date
Declare @CntBefore as int
Declare @CntNow as int
Declare @Database as varchar(30)
Declare @TableName as varchar(60)

[Code] ....

View 9 Replies View Related

SQL Server 2008 :: Unzip Using Execute Process Task Success But Getting Error Message In The Email?

Jun 17, 2015

Exec Prcoess task with source : ftp
destination :ftpunzip
work directory ftpunzip
executable : c:Program FilesWinZip

i am using expressing.

It is doing the unzip but getting this error

package process on server server1 has failed within the Task 'Unzip Files' with the following errors:
>
> File/Process "WZUNZIP.EXE" does not exist in directory "c:Program FilesWinZip".

This is the error message i am getting it

View 6 Replies View Related

Transact SQL :: Get Total Request Duration From Multiple Task Duration?

Jun 4, 2015

I have the following SQL query

SELECT
[Req_ID]
,[Service_Name]
,[Req_Started_Date]
,[Task_Name]
,[Task_Status]
,[Performer_Full_Name]

[code]....

Which works fine, but what I need to calculate the total duration of a request based on the duration of the tasks completed in the request based on Req_ID. I would like to use the CASE statement I have to determine the SLA_Mins for each task and add them together to get total request SLA_Mins.

Below is the create table schema and data

GO
/****** Object: Table [dbo].[MidrangeOtherSourceControl] Script Date: 06/03/2015 18:13:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MidrangeOtherSourceControl](
[Req_ID] [float] NULL,
[Service_Name] [nvarchar](255) NULL,

[code]....

View 9 Replies View Related

SQL Server 2008 :: Converting SAS Dates In A Float Format To Datetime Values

Mar 17, 2015

I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.

View 0 Replies View Related

SQL Server 2008 :: Error Converting Data Type Varchar To Numeric

Jun 17, 2015

As per my requirment using below script to convert from varchar to numeric.

declare @stg varchar(50)
set @stg='12,000,00'
select CONVERT(NUMERIC(17,2),@stg)

Getting error : Error converting data type varchar to numeric

View 6 Replies View Related

The SQL Server Service Broker For The Current Database Is Not Enabled, And As A Result Query Notifications Are Not Supported. Please Enable The Service Broker For This Database If You Wish To Use Notifications.

Feb 16, 2008

Hello,          I receive this error  "The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported.  Please enable the Service Broker for this database if you wish to use notifications." I attach the database in Management Studio to query and enable the broker using the scrip below but to no avail. ALTER DATABASE DataName SET ENABLE_BROKER ‘''<<------successfulandSELECT is_broker_enabled FROM sys.databases WHERE name = 'Database name' ‘'''<<-------value is 1 Global.asax ...    Sub Application_Start(ByVal sender As Object, ByVal e As EventArgs)        System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("dataConnectionString1").ConnectionString)    End Sub...Web.config ...    <connectionStrings>        <add name="dataConnectionString1" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|jbp_data.mdf;Integrated Security=True;User Instance=True"         providerName="System.Data.SqlClient" />        <add name="ASPNETDBConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True"         providerName="System.Data.SqlClient" />    </connectionStrings>... Hope you could help.  cheers,imperialx 

View 1 Replies View Related

SQL Server 2008 :: Arithmetic Overflow Error Converting Nvarchar To Data Type Numeric

Oct 13, 2012

When I run the following sql query:

"update table set price = price * 1.1 "..

I get the following error : "Msg 8115, Level 16, State 8, Line 1.. Arithmetic overflow error converting nvarchar to data type numeric. The statement has been terminated."

The table is set to nvarchar, and i am just trying to make the prices go up 10%.

View 9 Replies View Related

SQL Server 2008 :: SSIS Derived Column Transformation Failing On Converting Blank Rows

Jul 30, 2015

I have flat file source from which data is imported to a Sql table.The target column is int and input column is string .The column has some numeric values and some blank values.when I tried to convert into int values it fails.

View 7 Replies View Related

Syntax Error Converting The Varchar Value 'email' To A Column Of Data Type Int.

Nov 4, 2005

Well I am doing a simple insert through a stored procedure..., but I am getting this error. In my table I have the UserID as a VARCHAR 100. I am storing from a textbox that will have number because its there email. But I am not converting anything. Do I need to convert in this scenario?? 
Server Error in '/pickem' Application.


Syntax error converting the varchar value 'johndoe@hotmail.com' to a column of data type int. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'johndoe@hotmail.com' to a column of data type int.Source Error:



Line 113:
Line 114: dbConnection.Open
Line 115: Dim Dr As System.Data.IDataReader = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Line 116:
Line 117: Dim rowsAffected As Integer = 0
Here is the code:             Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("connString"))
             Dim myCommand as New SqlCommand("prcAddTeam", dbConnection)             myCommand.CommandType = CommandType.StoredProcedure
             myCommand.Parameters.Add("@UserID", SqlDbType.Varchar, 100).Value = TRIM(emailBox.Text)             myCommand.Parameters.Add("@TeamName", SqlDbType.Varchar, 40).Value = TRIM(teamBox.Text)             myCommand.Parameters.Add("@Address1", SqlDbType.Varchar, 50).Value = TRIM(addressBox.Text)             myCommand.Parameters.Add("@Address2", SqlDbType.Varchar, 50).Value = TRIM(address2Box.Text)             myCommand.Parameters.Add("@City", SqlDbType.Varchar, 50).Value = TRIM(cityBox.Text)             myCommand.Parameters.Add("@State", SqlDbType.Varchar, 30).Value = stateList1.SelectedItem.Value             myCommand.Parameters.Add("@Zip", SqlDbType.Varchar, 11).Value = TRIM(zipBox.Text)             myCommand.Parameters.Add("@FirstName", SqlDbType.Varchar, 50).Value = TRIM(firstNameBox.Text)             myCommand.Parameters.Add("@LastName", SqlDbType.Varchar, 50).Value = TRIM(lastnameBox.Text)
             dbConnection.Open             Dim Dr As System.Data.IDataReader = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
             Dim rowsAffected As Integer = 0
             dbConnection.OpenStored procedure:CREATE                  PROCEDURE prcAddTeam                 @UserID    VARCHAR(100)                ,@TeamName  VARCHAR(40)                ,@Address1  VARCHAR(50)                ,@Address2  VARCHAR(50)                ,@City      VARCHAR(50)                ,@State     VARCHAR(30)                ,@Zip       VARCHAR(11)                ,@FirstName VARCHAR(50)                ,@LastName  VARCHAR(50)   ASSET NOCOUNT ONDECLARE @ID   INTSET @ID = 0SELECT @ID = ISNULL((SELECT UserID from tblUserInfo      where UserID = @UserID),0)IF @ID = 0    BEGIN      INSERT INTO tblUserInfo (UserID,TeamName,Address1,Address2,City,State,Zip,FirstName,LastName,CreateDate)      VALUES(@UserID,@TeamName,@Address1,@Address2,@City,@State,@Zip,@FirstName,@LastName,GetDate())             SET @ID = @@IDENTITY      END

View 5 Replies View Related

SQL Server Notifications

Jan 8, 2004

Does anyone have any idea how I would go about creating an object that can be informed of any modifications to a SQL data base. i.e. a .NET object that can detect whenever a table is updated.
It is NOT possible to modify the database in anway, so I can't add triggers etc.

View 3 Replies View Related

SQL Server 2005 Notifications

Oct 19, 2007

Hi,

I have a Windows application in C# and a SQL Server 2005 having a table "Notifications" in a DB.

This table is being updated at regular intervals and i want that my application should get a notification for this everytime a Record is inserted, updated or deleted.

How this can be achieved ?

Thanks
Kulvinder Singh

View 3 Replies View Related

SMTP Notifications In SQL Server 2000

Sep 24, 2007

Hi

How to create SMTP notifications using SQL Server 2000?
could you please gimme some usefull URLs.

Thanks & Regards

View 1 Replies View Related







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