Service Pack
-
How can find out the latest Service Pack that was installed on SQL Server 2000??
-
Right-click on the server name in Enterprise Manager, select properties and under version it will tell you a service pack for SQL Server.
Or if connecting remotely:
select serverproperty('productlevel')
Please, note that Select Version will return a service pack for Windows.
Regards,Yelena Varsha
-
Enterprise Manager, right click the server -->> Properties then look at the "Product version"
Or run @@version, but that displays the actual build #, which is a little less clear (eg 8.00.2039 = SP4)
-
Ok, Homebrew thanks. We have installed SP3. The reason I wanted to know was that when I try to test the operators properties i get this message:Error 22022: SQLServerAgent Error The SQLServerAgent mail session is not running; check the mail profile and/or the SQLServerAgent service startup account in the SQLServerAgent Properties dialog. Somewhere I read that I should install SP 4 on the server to fix this problem. Is that the case?
I think this error occurs because their is not an Outlook client running on the server. (How can I tell in Win 2003? 
-
SQL Mail ? uugghh ........ that's a whole separate issue ... SP4 won't help
-
Charger,
when you figure out how to run SQL Mail without Exchange server on the network and with Notes client, let me know too.
I do have good test results with Database Mail in SQL Server 2005 CTP
Regards,Yelena Varsha
-
I've had SQL mail working with Notes .... and I've also had it stop working for no apparent reason. Currently I'm bypassing Notes and bouncing of a different server. We're switching to Exchange soon anyway.
-
I second the "uugghh ........ " wholeheartedly !
Been using a CDOSYS SMTP mail stored procedure for years without incident.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Ok so ughhh to SQL Server Mail!! What I need to do is be able to send an email when a certain job fails. And also I need to send an email when a DTS package fails, how do I do it? What am I missing here? Now I have tried the following ActiveX script in a DTS package:
Function Main()
Dim iMsg
set iMsg = CreateObject("CDO.Message")
Dim objMail
Set objMail = CreateObject("CDO.Message")
objMail.From = " thomasj@ministryhealth.org"
objMail.To = " thomasj@ministryhealth.org"
objMail.AddAttachment ( "c:\temp\authors.xls")
objMail.Subject="Authors Spreadsheet"
objMail.TextBody = "Spreadsheet"
objMail.Send
Set objMail = nothing
Main = DTSTaskExecResult_Success
End Function
Error Description: ActiveX component, cannot create object: CDO.message.
I need a way to send an email, what am I missing?
-
FYI ... You might want to re-post this under a SQL Mail title for better feedback.
-
Charger, do you have SMTP server installed on the system?
If you don't then you have to use a piece of code that sets some references to external SMTP server on your network. When I say External then I mean another computer on the same network. See, this works. Please replace SMTP server name SMTPserver.mydomain.mycompany.com with your external SMTP server fully qualified server name, also my email address for yours, attachment name if any and watch that lines of script would be correctly on one line or use line continuation _ sign. Let me know if you need more help. I use it as a VB script in a VBS file. from DTS you call VBScript file as OS job "cscript myfile.vbs". My co-worker did not have success if he ran this script as ActiveX script., but as OS command it worked.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Setting variables for Sending Email
Dim iMsg, iConf, Flds
Dim strFrom, strTo, strCC, strBCC, strSubject, strBody
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Setting up Constants to use SMTP
'
Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing"
Const cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
Const cdoSMTPConnectionTimeout = "http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"
Const cdoSMTPAuthenticate = "http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"
'
' Creating Mail Objects
'
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")
'
' Setting up Configuration to use Network SMTP server
'
Set Flds = iConf.Fields
With Flds
.Item(cdoSendUsingMethod) = 2 'Send the message using the network (SMTP over the network).
.Item(cdoSMTPServer) = "SMTPserver.mydomain.mycompany.com"
.Item(cdoSMTPConnectionTimeout) = 30
.Item(cdoSMTPAuthenticate) = 1
.Update
End With
'End Setting Constants for Configuration
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
strFrom = "Yelena"
strTo = yelena.varshal@mycompany.com
strCC = ""
strBCC = ""
strSubject = "Testing Sending Email With Attachments"
strBody = "This is a test.Do not reply back, this is an automated email"
'Send Email
With iMsg
Set .Configuration = iConf
.To = strTo
.Cc = strCC
.Bcc = strBCC
.From = strFrom
.Subject = strSubject
.TextBody = strBody
.AddAttachment "C:\MyAttachment.pdf"
.Send
End With
Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing
Regards,Yelena Varsha
-
Here's one of the MS links for a stored procedure version ...
http://support.microsoft.com/default.aspx?scid=kb;en-us;312839
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I created the sp and ran it with:
declare @Body varchar(4000)
select @Body = 'This is a Test Message'
exec sp_send_cdosysmail 'thomasj@ministryhealth.org','thomasj@ministryhealth.org','Test of CDOSYS',@Body
The results of the sp are No Column Name: -2147220973
Each time I run it I get 2 rows inserted into cdosysmail_failures:
row 1 = 2005-09-29 16:06:10.880 56 thomasj@ministryhealth.org thomasj@ministryhealth.org Test of CDOSYS This is a Test Message 16711422 -2147220973 NULL NULL NULL Failed at sp_OAMethod Send
row 2 = 2005-09-29 16:06:10.910 56 thomasj@ministryhealth.org thomasj@ministryhealth.org Test of CDOSYS This is a Test Message 16711422 0 CDO.Message.1 The transport failed to connect to the server. Description: The transport failed to connect to the server. sp_OAGetErrorInfo for sp_OAMethod Send
-
Have you entered your organizations SMTP server information ? Is your SMTP server set up to 'relay' messages ? Could you post your proc code just so we can make sure the appropriate changes were made ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I'll have to ask my network administrator for our organizations SMTP server information? Where do I enter it? I'll also have to ask if our SMTP server is set up to 'relay' messages ?
Here is the sp:
-- drop old cdosysmail_failures table if exists
IF (EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'cdosysmail_failures' AND type='U')) DROP TABLE [dbo].[cdosysmail_failures]
GO
-- Create new cdosysmail_failures table
CREATE TABLE [dbo].[cdosysmail_failures]
([Date of Failure] datetime,
[Spid] int NULL,
[From] varchar(100) NULL,
[To] varchar(100) NULL,
[Subject] varchar(100) NULL,
[Body] varchar(4000) NULL,
[iMsg] int NULL,
[Hr] int NULL,
[Source of Failure] varchar(255) NULL,
[Description of Failure] varchar(500) NULL,
[Output from Failure] varchar(1000) NULL,
[Comment about Failure] varchar(50) NULL)
GO
IF (EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'sp_send_cdosysmail' AND type='P')) DROP PROCEDURE [dbo].[sp_send_cdosysmail]
GO
CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
@From varchar(100) ,
@To varchar(100) ,
@Subject varchar(100)=" ",
@Body varchar(4000) =" "
/*********************************************************************
This stored procedure takes the parameters and sends an e-mail.
All the mail configurations are hard-coded in the stored procedure.
Comments are added to the stored procedure where necessary.
References to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp
***********************************************************************/
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
--************* Create the CDO.Message Object ************************
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OACreate')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OACreate')
RETURN
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/sendusing"
.Value','2'">http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty sendusing')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty sendusing')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- This is to configure the Server Name or IP address.
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("<A href="http://schemas.microsoft.com/cdo/configuration/smtpserver"
.Value'">http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', cdoSMTPServerNameIF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty smtpserver')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty smtpserver')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- Save the configurations to the message object.
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Update')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Update')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- Set the e-mail parameters.
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty To')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty To')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty From')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty From')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty Subject')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty Subject')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OASetProperty TextBody')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OASetProperty TextBody')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
IF @hr <>0
BEGIN
SELECT @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OAMethod Send')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OAMethod Send')
GOTO send_cdosysmail_cleanup
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
GOTO send_cdosysmail_cleanup
END
END
-- Do some error handling after each step if you have to.
-- Clean up the objects created.
send_cdosysmail_cleanup:
If (@iMsg IS NOT NULL) -- if @iMsg is NOT NULL then destroy it
BEGIN
EXEC @hr=sp_OADestroy @iMsg
-- handle the failure of the destroy if needed
IF @hr <>0
BEGIN
select @hr
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'Failed at sp_OADestroy')
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
-- if sp_OAGetErrorInfo was successful, print errors
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, 'sp_OAGetErrorInfo for sp_OADestroy')
END
-- else sp_OAGetErrorInfo failed
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
END
ELSE
BEGIN
PRINT ' sp_OADestroy skipped because @iMsg is NULL.'
INSERT INTO [dbo].[cdosysmail_failures] VALUES (getdate(), @@spid, @From, @To, @Subject, @Body, @iMsg, @hr, @source, @description, @output, '@iMsg is NULL, sp_OADestroy skipped')
RETURN
END
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply