Hi Guys, basically I have a scheduled job in MSSQL7, which on completion e-mails one of the operators.....
How can I get this job to email more than one operator upon completion???
Cheers....
I have configured smtp email in MS sql server and configure email to schedular job when schedular jobs become failed. Can i configure email so that email will be sent from scheduler job on both success of job and Failure of job?
View 3 Replies View RelatedExec 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
I get this error when I attempt to read from a datareader using the following sql statement:
Dim mysql As String = "SELECT PayrollTrans.PayrollID, Employees.[EmpFirstName] & ' ' & " _
& " Employees.[emplastname] AS FullName, Employees.[City] & ', ' & Employees.[State] & ' ' & Employees.[zip] AS CityState " _
& " , PayrollTrans.Date, PayrollTrans.EmployeeID, PayrollTrans.RegHours, " _
& " PayrollTrans.OTHours , PayrollTrans.RegPay, PayrollTrans.OTPay, " _
& " PayrollTrans.FedTax, PayrollTrans.FICATax, PayrollTrans.MedicareTax, " _
& " PayrollTrans.ESCTax, PayrollTrans.StateTax, PayrollTrans.ESCEMPTax, " _
& " PayrollTrans.FUTATax, PayrollTrans.NetPay, Employees.EmployeeID, " _
& " Employees.Address1, Employees.Address2, Employees.SSAN, " _
& " Employees.PayType, Employees.RegPayRate, Employees.OTPayRate, " _
& " Employees.MaritalStatus, Employees.FedExemption, Employees.StateExemption, " _
& " Employees.Active, Employees.SelectforPay, Employees.PayDate " _
& " FROM PayrollTrans, Employees where PayrollTrans.EmployeeID = Employees.EmployeeID;"
my reader command list as follows:
Dim objCM As New SqlClient.SqlCommand(mysql, SqlConnection1)
Dim objDR As SqlClient.SqlDataReader
objDR = objCM.ExecuteReader
Any ideas on where I am going wrong?
Thanks in advance
I am getting a error message saying: Invalid operator for data type. Operator equals boolean AND, type equals datetime.
I traced the pointer to @gdo and @gd, they are both dates!
INSERT INTO AdminAlerts values (CURRENT_USER, 'UPDATE', getDate(), @biui, 'Updated booking, ID of booking updated: ' & @biui & ', Booking date and time before/after update: ' & @gdo & '/' & @gd & ', Room number before/after update: ' & @rno & '/' & @rn & ' and Customer ID before/after update: ' & @cio & '/' & @ci)
If I cut that two dates out it works fine.
Could someone tell me the syntax to include a date in a string :confused:
I have a report that gets sends out through a subscription and sometimes the report has multiple pages and all those pages appear within one email.Is it possible to set the subscription in such a way that an email is sent per page when the subscription executes.
View 2 Replies View RelatedUnder IIS SMTP I can set bounced email redirect etc. how to do that with dbmail, the idea is I can get the list of bounced emails somewhere so I can create a report.
Any idea?
thanks
need help
split list of email add comma for evry email
i have tabe "tblLogin" and in this table i have field emall
like this
emall
-----------------------------------------
aaa@hhhh.mm
nnn@hhhh.mm
mmm@hhhh.mm
need to do ilke this
Code Snippet
@list_email = (SELECT emall FROM tblLogin)
--------------------------i get this
-----------------------@list_email=aaa@hhhh.mm ; nnn@hhhh.mm ; mmm@hhhh.mm
@recipients = @list_email
Code Snippet
IF EXISTS( SELECT * FROM [db_all].[dbo].[taliB] )
BEGIN
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
Date_born AS 'td','' ,
Age AS 'td','' ,
BirthdayToday AS 'td','' ,
BirthdayThisWeek AS 'td'
FROM [Bakra_all].[dbo].[taliB] ORDER BY LastName FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @body ='<html><H1 align=center>aaaaaaaaaaaaaaaaaaaaaa</H1><body ><table border = 1 align=center dir=rtl>
<tr>
<td>name</td>
<td>fname</td>
<td>date</td>
<td>age</td>
<td>aaaaaaaaa</td>
<td>bbbbbbbbbbbbbbb</td>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =N'rrr@iec.co.il',
@copy_recipients='rrrrr@iec.co.il',
@body = @body,
@body_format ='HTML',
@subject ='ggggggggggggggggggggg',
@profile_name ='ilan'
END
ELSE
print 'no email today'
oK if anyone remembers the problems with my Update/termination Trigger I found it works best if I do it as a Stored procedure instead of a trigger. Thanks again for your help guys. I really appreciate it
View 3 Replies View RelatedHi:
I create a DTS package in 'Copy SQL Objects Task' to transfer 3 stored procedures from my local SQL msdb to remote testing msdb database.
Execution is 'successful' but the destination server msdb does not have the 3 stored procedures.
Any hint on this 'vanished' copy objects?
thanks
-D
Just a thought.
If an issue is resolved please note that the problem has been resolved. Because there are "many ways to skin a cat" it would be helpful to anyone else with a similar problem (or someone trying to learn) what the solution was.
Hi expert,
I use a SQL Job that makes use of the BACKUP command. How can I check this job success or not?
Thanks
Hey all
Which system variable do I use if I would like to transfer success message into an SQL table? I'm running a task to import data into an SQL table. And I'm using the System::ErrorDescription variable (Execute SQL task) to insert error messages into a table but I would really appreciate it if someone could tell me what system variable would insert 'success' message into an SQL table. Tried a lot of searching on the net but couldn't find anything.
Many thanks,
Rupa
Has anyone here been involved in a successful commercial Data Warehouse project?
If so, what was your role? How big was the project? Did you do it all or was their one guy data modelling, one for ETL, one for Client-side, one project managing, etc...? What tools did you use? Was it in SQL2000? 2005? Did you use DTS/SSIS for your ETL or Sprocs? Did you use SSAS? Were you writing lots of MDX to meet client-side needs? Or did you use a completely different toolset for all or some of this?
Did you use the Kimball approach? Or were you in the other camp?
Was your latency nightly or did you have some type of "real-time" capability?
I would prefer to hear from people who've actually been involved in successful projects (or failed projects if you would like to share with us why you think they failed). I'm not too interested in hearing from people that have never done it but have a theory as to how it should work (like me).
Many Thanks,
Coolerbob
(Or is there some other website forum I should be asking this question on? (like mdxteam, olapteam, datawarehouseteam or something)
Is there a parameter allowing to verify successful execution of a stored procedure?
Something that can be run as the last line in a stored procedure to verify its success...?
Thanks!
Trying to delete records associated with the same contact id from all the tables used for that contact. But I'm getting an error. Strange error because the table does exist and I've been able to do single deletes from that table. So, something tells me I'm not getting something right in my SQL statement. How do I delete records from all 3 tables using the same id (as opposed to doing 3 different SQL calls, one for each)?
-----------------
DELETE names, addresses, phones
FROM names AS n, addresses AS a, phones AS p
WHERE n.id = '12' AND a.id = '12' AND p.id = '12'
Error: Unknown table 'names' in MULTI DELETE
-----------------
(BTW, how do tag my code so it shows up in a code div?)
I apologize in advance for what is probably a really dumb question.
I have an SSIS package that is executing a number of data flow tasks simultaneously, and then all are followed by a single script step. I'm trying to get all the constraint arrows to look uniform by having them exit the DFTs on the right side and enter the script step on the left. Unfortunately, BI Dev Studio is putting the exiting arrows on the bottom, which looks awful and is difficult to follow. I can only move them between specific points on the bottom of the step. How do I move those arrows to the right side?
Thanks in advance.
How to use Xquery to get the success value and the result value from the following XML.
This XML is stored in a SQL Table XML field, i want to be able to get all the success and reslut values for all the records:
Here is my XML
<SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:ns1="https://data.abc.biz/mysoappathtest/?soap:customcalls:v1"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:SOAP-ENC="http://schemas.xmlsoap.org/soap/encoding/" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
<SOAP-ENV:Body>
[Code] ....
I am trying to get the result = 15810 and the Success value = true
My ssis package errors out because one of the database connection failed. I successfully logged error but also indicated that package finished successfully. My confusion is if a sheduling software schedules this package, what would be return code sent by dtexe... . would it be success or failure? In this scnerio i want it to return failure so that appropriate team can be contacted.
thanks,
kushpaw
Hello again to everyone,
Because of aleatory problems some of our Analysis Services Tasks fails and sometimes success with not know reasons. You run the task and it fails, the next time it fails, the next time is success, and so on aleatorily.
The solution of this is to put those tasks in a loop (ore someting similar) that only ends if all of the Analysis Services Tasks succeeds. If not, those tasks starts again. Do you know what I mean? How can I build this? I have investigated with checkpoints, with For Loops ... but still I dont find the way to do it.
Does anybody can help me? I suppose it's not so difficult, but I'm stuck in finding the solution...
Thankx once again.
JohnSSIS
I have an ActiveX Script Task in SQL Server 2000. It chooses one of two
possible success-paths depending on if a file exist or not. (Part of the old ActiveX Script for choosing next step is below)
I need to rewrite this for a Script Task in SQL Server 2005 but it seems like it
doesn't have this functionality and objects.
Does anyone know how to write the code for choosing the next step in a
Script Task or knows another way to solve my problem?
The package is not suppose to fail if the file is missing, it's must succeed and that's why I need two success-paths.
Regards,
Sara
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main ()
Dim pkg
Dim stpContinuePkg
Dim stpExit
SET pkg = DTSGlobalVariables.Parent
SET stpContinuePkg = pkg.Steps("DTSStep_DTSActiveScriptTask_4")
SET stpExit = pkg.Steps("DTSStep_DTSActiveScriptTask_21")
If ...... Then
Main = DTSStepScriptResult_ExecuteTask
stpContinuePkg.DisableStep = False
stpExit.DisableStep = True
Else
.......
End If
Main = DTSTaskExecResult_Success
End Function
I export all tables from serverA databaseAA to serverB (both SQL2k sp3)databaseAA with 'Success', but without any relationships. In SQL6.5 Tools of Transfer will bring all objects include relationship.
I also tried export "All Objects" which fails with unclear message. I just don't want to do a backup and restore....
thanks
David
Hi
I am writing a package that i want to have email me on two possible success scenarios.
essentially, this is the conditions:
IF @result = 0
BEGIN
EXEC master..xp_cmdshell @copyfile, NO_OUTPUT
PRINT 'Operation Successful'
END
ELSE
BEGIN
RAISERROR ('This operation failed. Error Code:01. The source and destination files are identical.', 0, 1)
END
If the first condition is met, I want to fire off an email stating success.
If the second condition is met (the RAISERROR) then i want to fire off a different email.
Now, the problem is, I am not sure how to flow it. Both conditions are successful, thus it always fires off the success email if i use an "On Success" flow.
How do i capture the RAISERROR before the email to tell it which email to send?
Does this make sense?
Hi
I am writing a package that i want to have email me on two possible success scenarios.
essentially, this is the conditions:
IF @result = 0
BEGIN
EXEC master..xp_cmdshell @copyfile, NO_OUTPUT
PRINT 'Operation Successful'
END
ELSE
BEGIN
RAISERROR ('This operation failed. Error Code:01. The source and destination files are identical.', 0, 1)
END
If the first condition is met, I want to fire off an email stating success.
If the second condition is met (the RAISERROR) then i want to fire off a different email.
Now, the problem is, I am not sure how to flow it. Both conditions are successful, thus it always fires off the success email if i use an "On Success" flow.
How do i capture the RAISERROR before the email to tell it which email to send?
Does this make sense?
I recently upgrade my Enterprise Manager to Sql 2000 from 7.0; the databases are still SQL 7.0. Since then I've noticed that all scheduled jobs that are calling dtsrun.exe's are reporting successful but do not actually run. I think the total process time is 1 sec.The message from View History reports: "The step did not generate any output. Process Exit Code 0. The step succeeded." Every job that uses the dtsrun.exe reports the same message. I've ran the same command from DOS and it runs fine. I've executed some basic batch commands within the Scheduled Jobs and those fail as well. I can open the DTS package and execute those with success, but when these are scheduled and are executed from the Agent Jobs they fail. Can anyone shed some light on this please?
Thanks,
Jeff
Hi all,
I have a stored proc that uses xp_cmdshell to boot off a batch file on the NT side of the box (box OS is Windows 2000 Advanced Server).
Here is the pertinent code:/*----- Kick off the NT bat job to suck over the data through the web service pipe*/
SELECT @NTCommand = 'D:TradeAnalysisWondaDataStoreJobsPullFromWONDA _InstitutionalRankings.bat ' + CONVERT(varchar(10), @ReqDate, 101)
EXECUTE @e_error = master.dbo.xp_cmdshell @NTCommand
SELECT @m_error = CASE WHEN ISNULL(@e_error, 0) <> 0 THEN (@e_error + 50000) ELSE @@Error END
IF @m_error <> 0 GOTO ErrorHandlerThe trouble is that the batch file is failing (soft error, caught internally to the batch file, which then kills itself, screaming loudly all the way).
The batch file is using the following "voice" in which to scream in pain as it dies (a.k.a., using this code to terminate itself, which kills the cmd shell and returns 13 as an error code)REM WonDBService.exe says we failed
Date /T
Time /T
EXIT /B 13
Meanwhile, back at the ranch (errr...back in stored procedure), what is being returned is a ZERO (in the first code block, @e_error is being set to ZERO when the xp_cmdshell returns from the bat file.
So, now that I have ruled out the obvious *LOL* how can I get my xp_cmdshell to realize it has failed miserably at the one, tiny, simple, not-too-much-to-ask, job that it is designed to do?
Is there some example of how i can add a return status into my stored procedure to have an extra field where I can verify if it runs successfully
1 if success
-1 if error (server, etc)
0 if empty record return
Thanks
I have an ssis package with three tasks. I want to force the execution result of the package to success all the time. I execute the package from a .net application. I tried to place my tasks in the package inside a sequence container and created a new script task which would execute when the any of the tasks in the container failed. I am setting the result of the package to success in this script task. This is not correctly working for me. Even if one task in the package failed and the last script task went to success, the .net app is receiving a failure status. Is there any way to handle this in the ssis package than in the .net app. There might be an easy way, but I am unable to find it.
Thanks in advance!!
hi all,
please help,
i using Execute DTS 2000 Package Task to create a package.dtsx, and execute successful in VS2005
but when i created it as a job and it fail to run.
the package.dtsx is calling a package2000.dts, which having 2 steps to run.
i execute the job by step, and the i found that the 1st job is running successful but the 2nd job was fail.
may i know where can i found the error log? or any where to debug it?
please help......
thanks 1st.....
I have a number of stored procedures that run one after the other. How do you code to get the success or failure so that some logic can be applied accordingly? I've heard of the TRY CATCH structure, but I new and have yet to use it. How many different ways can success or failure be handled in code?
View 4 Replies View RelatedI created a SSIS package that imports a file then moves the file to a folder called processed.
I created a job that runs the package. The pack works fine when I run it from the SQL Storage Area. When I run the job I get the following message:
Started: 9:58:15 AM DTExec: The package execution returned DTSER_SUCCESS (0). Started: 9:58:15 AM Finished: 9:58:21 AM Elapsed: 6.047 seconds. The package executed successfully. The step succeeded.
showing that the job supposedly completed successfully.
I know that the package the not run because the data did not get imported and the file did not move.
Can anyone explain the package may not run but we still get a job completion status of success?
Hello,
When I run my package, a task will fail, however, the package will claim that it was successful. Why is this, and how can I trigger a failed package when one task fails?
Thanks in advance.
Hello ALL
what I want to achieve is to load a text file that has email addreses from disk and using the email addresses in the text file look it up against the email addresses in the database table then once matched delete all the users in the table whose email address were in the text file.
I also want to update some users using a different text file.
Please help me with the best way to do this
Thanks in advance