I am trying to figure out how to add a time datestamp to my xp_sendmail procedure:
use master;
go
CREATE PROC pr_sendmail
AS
DECLARE @DT DATETIME
SET @DT=GETDATE()
BEGIN
EXEC xp_sendmail @recipients = 'me@work.com',
@message = 'send email from SQL Server Stored Procedure.',
@copy_recipients = 'me@work.com',
@subject = 'Job Started at ', @DT
END
How do I get this to work? Thanks!
I am populating oracle source in Sql Server Destination. after few rows it fails it displays this error:
[OLE DB Destination [16]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description:
"Invalid date format".
I used this script component using the following code in between the adapters, However after 9,500 rows it failed again giving the same above error:
To convert Oracle timestamp to Sql Server timestamp
If Row.CALCULATEDETADATECUST_IsNull = False Then
If IsDate(DateSerial(Row.CALCULATEDETADATECUST.Year, Row.CALCULATEDETADATECUST.Month, Row.CALCULATEDETADATECUST.Day)) Then
dt = Row.CALCULATEDETADATECUST
Row.CALCULATEDETADATECUSTD = dt
End If
End If
I don't know if my code is right . Please inform, how i can achieve this.
What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?
View 1 Replies View Relatedpublic void Main()
{
// TODO: Add your code here
string time = System.DateTime.Now.GetDateTimeFormats('G').ToString();
string pathD = @"C:UsersuserDesktop estFileD";
string pathR = @"C:UsersuserDesktop estFileR";
[code]...
Normally (I usually program in JAVA), I would print out the variables to see if there is an issue but I haven't figured out how to do that in SSIS yet.I have to set the Connection string for the Flat File conection using the Expressions tab instead of putting the variable in the connection string tab.Now it works but the file that is spitting out is "testFileDSystem.String[]"
i am creating a DTS package that will query a table and move data to another table daily on same DB/SERVER. i want to be able to timestamp previous date into date column in destination table. There's no date on source but i need timestamp on each import and date for previous day b/c importing data are for previous day.
View 5 Replies View Relatedhi,
I trying to call a stored procedure with parameter from xp_sendmail. I am getting syntax error. Is there anyone who can help me?
here is the code I am using...
execute master.dbo.xp_sendmail @recipients='lakshmip@oasisnetwork.com', @message='Leads Information',
@query='Execute LeadDistribution..sp_sendmailB '+@AssToID''
I'm having trouble importing data with a binary timestamp.
I have a group of datafiles with various data types mixed in
and I seem to get a lot of errors during the import attemt using
BCP.. The timestamp data is represented in the text file to be
imported as 0x0000aedc (Hexidecimal representation of timestamp)
I'm using the tilde (~) as a delimiter. What can I do to get past this
rather annoying problem?
Datetime seems to be accepted fine if I use char as the import datatype
and 26 as the length. It seems to swallow that fine. It seems to be the
timestamp field I'm tripping on.
Timestamp comes up as [binary]
Prefix length = 2
Field terminator = ~
hi, good day, i would like to convert timstamp value into date with dateformat (yyyy-MM-dd) , how to i do that ?
i have try using convert method with code 20 and 21 , but it contain the time as well , i just need the date with no time and seconds include
thank you
Good Morning,
Hate to sound so dumb, but, it's been years since I've done any SQL programming and I was bad at the time!
What is the code for taking my time stamp 2007-02-09 10:15:02.000 and just displaying the date as 02/09/2007?
Do I use variables? Are they set up before my select statement? Can anyone suggest a really good, easy, very explanable SQL (that will work in SQL Server) progamming book?
Thanks so much!
Dianne
I created a trigger in the pubs database:
create trigger test3
on authors
after update
as
exec master.dbo.xp_sendmail "joebob@yahoo.com","Hello"
I then run this script in QA:
update authors
set state = 'CA'
where state = 'KS'
and receive this error message:
Could not find stored procedure 'xp_sendmail'.
The statement has been terminated.
What do I need to do ? I realize that xp_sendmail is in the master database, but want a trigger fired to joebob@yahoo.com anytime an update script is run against the authors table in the pubs database.
I've set all the executable rights....what else do I need to do ?
Thank you
I have a problem with an Order By sort on a SubmissionDate column in my SQLSERVER DB.
I am inputing a timestamp in this format into the column above: 1/18/2005 11:03:19 AM
Problem is, once I sort this column in DESC order to return the results to a datalist dates with a time like this:
1/18/2005 1:03:19 AM
get placed out of place (lower on the return in DESC/higher on the return in ASC). I am assuming this is happening because it reads 1 as coming before 11 instead of after like it is with time. If this was in 24 hour format this wouldn't be a problem I guess because 1PM would be 13, so that is after 11.
Anyone know what I can do to get this sorted correctly?
I have a column where i have Timestamp column
Ex:
130670901526899350
I will convert this one into date column like
DD/MM/YYYY
or DDMMYYYY
Hi,
I am pulling files from the FTP site using the FTP task. I want to also capture the date and timestamp of each of these files so that I can insert the values into a database and track when are these files get created normally on the FTP server.
Any ideas?
Thanks in advance for your help.
$wapnil
I have another question guys I need to send reports to different departments using sql. would this be the xp_sendmail function in sql. Can this be made into a stored procedure? What I'm going to do is make some reports which will constantly be updated with a query. These reports need to be emailed to differnt departments every 2 weeks. Is there a trigger or stored procedure I can setup through sql to do this? Does that make any sense??
View 8 Replies View RelatedI have no idea about how can I use xp_sendmail
when I try to use it this error displayed on the screen.
There is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail ...
I have a set of data with epoch timestamps. Purely for the sake of reporting, I need to pull the last six months of data and group it by month. I have tried searching the googles for epoch/milliseconds to datetime but I only get the MySQL or Oracle results, not MSSQL.
View 14 Replies View RelatedWith this qry i need to only show in the result for ordate the date and not the time stamp. below is my qry and the first line of the results:
SELECT DISTINCT
adHock.dbo.PPVVINCE$.Corps, adHock.dbo.PPVVINCE$.House, adHock.dbo.PPVVINCE$.Cust,
IDST_EVENT_ORDERS.ORDATE,
IDST_EVENT_ORDERS.ORTIME,
IDST_EVENT_ORDERS.EVWMIN,
IDST_EVENT_ORDERS.EVTDES,
[Code] ....
result for the field trying to change
ORDATE
1/1/2014 12:00:00 AM
I Only want to see the date.
I have a server on SQL Server 2008 R2.
I have the following code:
SELECT '1' AS Join_Field, T1.ID AS T1_ID, T2.ID AS T2_ID, T1.TimeStamp AS T1_Timestamp, T2.TimeStamp AS T2_Timestamp, DATEDIFF(Minute, T1.TimeStamp, T2.TimeStamp) AS Difference_Mins, T1.eventid, T1.shiftID,
T1.Value, SD.Shift_Start_Date, T1.StopCode, T1.Status, T1.JobID, T1.StatusDesc, T1.StopDesc, T1.MachineID,
CAST(CASE WHEN CONVERT(date, T1.TimeStamp, 103) < CONVERT(date, SD.Shift_Start_Date, 103)
THEN 1 ELSE 0 END AS int) AS flag, CAST(T1.TimeStamp as DATE) AS TS_Date
FROM PolReporting.Event_M2_T1 AS T1 INNER JOIN
PolReporting.Event_M2_T2 AS T2 ON T1.RowID = T2.RowID INNER JOIN
Intouch.ShiftDates_Grouped AS SD ON T1.shiftID = SD.ShiftID
However when I run it I get a message:
I know 2008 MS SQL Server has a timestamp data type that adds date and time when the rows are inserted. Is there a way to automatically update the date and time when the rows are updated?
View 5 Replies View RelatedI have two fields DSRHADTI which is an isodate and DSRHTIME which is 8 char time field in format 10.31.00. I want to take both these fields and put them into a field that is database timestamp so I have converted DSRHDATI to 10 character field. I am then trying to use substring to put both into 18 character field using derived column transformation editor. but it does not like the below. It's red syntax error what am I missing.
(SUBSTRING(Copy of DSRHDATI,1,4) +' /' + SUBSTRING( Copy of DSRHDATI,6,2) + '/ ' + SUBSTRING(Copy of DSRHDATI,9,2)) + SUBSTRING(DSRHTIME,1,2) + '.' + SUBSTRING(DSRHTIME,4,2) + '.' + SUBSTRING(DSRHTIME,7,2)
One I get the above to work I plan on convert 18 char to datetimestamp.
Am I on the right track on how to do this?
Hi
SSIS is brand new for me.. Playing with since a few hours..
Iam trying to import a Flat File into the SQLSERV DB using SSIS..
One of the column is in this format -- "YYYYMMDDHH24MISS"
How do i get around this to import the data in a readable fashion into the Destination?
Thanks!
MKR
Is there a way to extract the date part (11/27/2012) of a datetime/time stamp column (11/27/2012 00:00:00.000) and keep it in a date format?
The code i have below extracts the date part of a timestamp column and converts it to a char field. This becomes a problem when I joing the resultant table with a SAS dataset which contains the same column but is in a date format. The join process generates an error saying the column is in different formats.
convert(char(15), process_date,112) as process_dt
I have a simple package to load data from sql server db into a flat file. I have a date field in the source data base (data type DATETIME) when i open the csv file some show the exact time stamp and some records show just the seconds like (00:00:0.7). I used CAST CONVERT bu still the same issue.
AppliedDate
00:00.6
00:00.6
10/2/2015 0:00
10/2/2015 0:00
00:00.3
00:00.3
Hi,I'm attempting to update one row using strored procedure which check timestamp field.
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'tbCharakterystyki_Update')BEGINDROP Procedure tbCharakterystyki_UpdateENDGO
CREATE Procedure dbo.tbCharakterystyki_Update@CH_ID int ,@CH_CHARAKTERYSTYKA VARCHAR(30),@CH_OPIS VARCHAR(300),@CH_TIMESTAMP timestampAS
UPDATE tbCharakterystyki SET ch_charakterystyka = @CH_CHARAKTERYSTYKA,
ch_opis = @CH_OPIS
WHERE ch_id = @CH_ID AND ch_timestamp = @CH_TIMESTAMP
GO
Function which call stored procedure:public int CallSP(DataRow dr) { try { SqlConnection conn = new SqlConnection(this.strConnection); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "dbo.tbCharakterystyki_Update";
SqlParameter p1 = new SqlParameter(); p1.ParameterName = "@CH_ID"; p1.SqlDbType = SqlDbType.Int; p1.Value = dr[0];
SqlParameter p2 = new SqlParameter(); p2.ParameterName = "@CH_CHARAKTERYSTYKA"; p2.SqlDbType = SqlDbType.VarChar; p2.Value = dr[1];
SqlParameter p3 = new SqlParameter(); p3.ParameterName = "@CH_OPIS"; p3.SqlDbType = SqlDbType.VarChar; p3.Value = dr[2];
SqlParameter p4 = new SqlParameter(); p4.ParameterName = "@CH_TIMESTAMP"; p4.SqlDbType = SqlDbType.VarChar; p4.Value = dr[3];
cmd.Parameters.Add(p1); cmd.Parameters.Add(p2); cmd.Parameters.Add(p3); cmd.Parameters.Add(p4); cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); return 0; } catch ( Exception err ) { ShowError(err.Message) return null; } }
And function which call "CallSP()":private void Update(){DataSet ds = helper.GiveDataSetCharakterystyki()DataRow dr = ds.Tables[0].Select("ch_id=51")[0];
dr[1] = "new value";CallSP(dr);}When I fire Update(), stored procedure (dbo.tbCharakterystyki_Update) is not started, Sql Profiler doesn't show it.Whilst when I modify stored procedure, removing WHERE ch_id = @CH_ID AND ch_timestamp = @CH_TIMESTAMP and put only WHERE ch_id = @CH_ID , it works perfectly.What I did wrong?
ok...i give up.....
can this be done? (sql server 2000)
all i want to do is have my stored procedure output some data to a file and i want the filename to include a time stamp.
here's my current working file output code:
EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees.txt" -c -Usa -P'
i'd like it to be something like this:
EXEC master..xp_cmdshell 'bcp "select * from CEData..employee" queryout "c:employees" + datetime + ".txt" -c -Usa -P'
but nothing seems to work.
date time s-sitename TimeTaken(Seconds)
6/8/2015 10:56:26 TestSite 100
6/8/2015 10:56:26 TestSite 500
6/8/2015 10:56:26 TestSite 800
6/9/2015 11:56:26 TestSite 700
6/9/2015 11:56:26 TestSite 200
6/12/2015 12:56:26 TestSite 700
I have a table with above values, I am looking for a sql query to find AvgTimeTaken at different time stamps and total count of each time stamp
Output
date time s-sitename TimeTaken(Seconds) Count_of_Request
6/8/2015 10:56:26 TestSite 1400 3
6/9/2015 11:56:26 TestSite 900 2
6/12/2015 12:56:26 TestSite 700 1
I have looked all over my code and can not find anywhere that I am referencing the xp_sendmail procedure! Here is all the code<code>With sqlCmdUpdateParticipants
.Parameters("@ClassID").Value = ddlClass.SelectedItem.Value
.Parameters("@Person").Value = tbName.Text()
End With
cnCapMaster.Open()
sqlCmdUpdateParticipants.ExecuteNonQuery()
cnCapMaster.Close()</code>I am just getting a couple values and and inserting them into the database. the insert works then I get darn error message. This code worked at one time but it has been about 2 years sense I worked on it so who knows what might have happened sense then.Thanks,Bryan
How to get the details of a stored proc or sql query which updated a particular table for specified time stamp or interval. Is there any query to get this?
View 3 Replies View RelatedPls help if you can. I have two dates, date1 and date2.Basically I want to set date2 = date1 + 1 day for a range of datesthat I can select out.Is there any way to do this?
View 1 Replies View RelatedDATEADD function as i'm running into an error...Below is my SQL to derive start date of a month based on deliververydate feild.I'm running into error when i try to add 25 days to the start date of the month.
DATEADD(m,DATEDIFF(m,0,convert(varchar(30),DATEADD (month , @count*3 ,SR.DeliveryDate))),0)
When i want to add 25 days to the start date of a month getting an error...Error SQL
DateAdd(day,(DATEADD(m,DATEDIFF(m,0,convert(varchar(30),DATEADD (month , @count*3 ,SR.DeliveryDate))),0)),25)
Argument data type datetime is invalid for argument 2 of dateadd function.What do i need to change to make it work
Aim – To add 60days to [Date_Approved_By_Risk__c] column
Query so far is
select
[Date_Approved_By_Risk__c]
--left([Date_Approved_By_Risk__c],10)+60 as correct_date
from #build
where [Date_Approved_By_Risk__c] is not null
[Date_Approved_By_Risk__c] is in the following format “2010-04-30T23:00:00.000Z”
When i add the following “left([Date_Approved_By_Risk__c],10)+60 as correct_date” into the query i get the following error Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '2010-07-13' to data type int.
I am trying to add integer value to the datetime to get the configurable "date".But it is not working out. Here is the code for it.
declare @Id int;
exec @Id = up_GetConfigurableDayInterval
print getdate() + @Id
The sp "up_GetConfigurableDayInterval", will return number of days to add the the current date time. But print getdate() + @Id , is not producing the updated result.But when ever i have replaced the variable "@Id" with a value, Say "5", it is producing the expected result.I have also tested the above code by the following:-
create table Temp(value datetime);
insert into Temp values(getdate() + @Id);
select * from Temp
But it is not having the new added datetime.
Hello all. I was just wondering how I could add the current date and time to the name of the .bak file. I perform a full backup daily and would like to keep a weeks worth of backups, but everytime I run the job, it just overites the previous one.
The command in the wizzard is:
BACKUP DATABASE [snl] TO DISK = N'C:Backups_for_FTPSNL_Full.bak' WITH NOINIT , NOUNLOAD , NAME = N'snl backup', SKIP , STATS = 10, DESCRIPTION = N'SNL Backup', NOFORMAT
Thanks,
Parallon