Send Mail from SQL server 2000
in [Programming SQL]
|
From: Hemant on 9 Feb 2010 01:44 Hi, I am trying to send mail from sql server . I have done this by sql server 2005 but not getting success in sql server 2000. The error is : The configuration option 'Ole Automation Procedures' does not exist, or it may be an advanced option. Please help . Thanks, hemant
From: Tibor Karaszi on 9 Feb 2010 02:27 SQL Server 2000 doesn't have database mail. It has SQL Mail which requires MAPI (Outlook), art from that, we need to know what you are doing to help. That error suggests that you run some sp_configure option which doesn't exist in 2000 - that is all we can say with the information we have so far. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Hemant" <Hemant(a)nomail.com> wrote in message news:u4YHuLVqKHA.3832(a)TK2MSFTNGP02.phx.gbl... > Hi, > > I am trying to send mail from sql server . I have done this by sql server > 2005 but not getting success in sql server 2000. > > The error is : > The configuration option 'Ole Automation Procedures' does not exist, or it > may be an advanced option. > > Please help . > Thanks, > hemant >
From: Hemant on 9 Feb 2010 02:52 Thanks for your reply. I am doing : use master go sp_configure 'show advanced options',1 go reconfigure with override go sp_configure 'Database Mail XPs',1 --go --sp_configure 'SQL Mail XPs',0 go sp_configure 'Ole Automation Procedures', 1; GO reconfigure go than i Create SP all this run in sql server 2005 i need to do same in sql server 2000 set ANSI_NULLS ON set QUOTED_IDENTIFIER OFF GO Create PROCEDURE [dbo].[sp_SQLNotify] @From varchar(100) , @To varchar(100) , @Subject varchar(100)=" ", @Body varchar(4000) = "Test email for PSC Client" /********************************************************************* This stored procedure takes the above parameters and sends an e-mail. All of the mail configurations are hard-coded in the stored procedure. Comments are added to the stored procedure where necessary. Reference 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 --***************Configuring the Message Object ****************** -- This is to configure a remote SMTP server. -- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' -- 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("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', -- Save the configurations to the message object. EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null -- Set the e-mail parameters. EXEC @hr = sp_OASetProperty @iMsg, 'To', @To EXEC @hr = sp_OASetProperty @iMsg, 'From', @From EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL -- Sample error handling. IF @hr <>0 BEGIN EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT Insert into Testing.dbo.EmailSendMsgs (EmailTo,EmailFrom,[Message],EmailDate) values (@To,@From,isnull(@description,'Error Occured!'),GetDate()); IF @hr = 0 BEGIN SELECT @output = ' Source: ' + @source PRINT @output SELECT @output = ' Description: ' + @description PRINT @output END ELSE BEGIN PRINT ' sp_OAGetErrorInfo failed.' RETURN END END -- Do some error handling after each step if you need to. -- Clean up the objects created. EXEC @hr = sp_OADestroy @iMsg Insert into Testing.dbo.EmailSendMsgs (EmailTo,EmailFrom,[Message],EmailDate) values (@To,@From,'Mail Sent!',GetDate()); Than can you help me to send mail from sql server 2000 . "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message news:%23IJyokVqKHA.4492(a)TK2MSFTNGP05.phx.gbl... > SQL Server 2000 doesn't have database mail. It has SQL Mail which requires > MAPI (Outlook), art from that, we need to know what you are doing to help. > That error suggests that you run some sp_configure option which doesn't > exist in 2000 - that is all we can say with the information we have so > far. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > > "Hemant" <Hemant(a)nomail.com> wrote in message > news:u4YHuLVqKHA.3832(a)TK2MSFTNGP02.phx.gbl... >> Hi, >> >> I am trying to send mail from sql server . I have done this by sql server >> 2005 but not getting success in sql server 2000. >> >> The error is : >> The configuration option 'Ole Automation Procedures' does not exist, or >> it may be an advanced option. >> >> Please help . >> Thanks, >> hemant >>
From: Tibor Karaszi on 9 Feb 2010 04:04 Where did you get that scrip from? The scrip is designed to work on 2005 or later, and you can't just assume such code work as-is on an earlier version of SQL Server. I.e., you need to adapt the script for your version of SQL Server. The first section with the sp_configure part you can remove, since there's no database mail on 2000 and ole automation procedures are always enabled in 2000. Then you would have to take it from there. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Hemant" <Hemant(a)nomail.com> wrote in message news:#S41ixVqKHA.4284(a)TK2MSFTNGP04.phx.gbl... > Thanks for your reply. > > I am doing : > use master > go > sp_configure 'show advanced options',1 > go > reconfigure with override > go > sp_configure 'Database Mail XPs',1 > --go > --sp_configure 'SQL Mail XPs',0 > go > > sp_configure 'Ole Automation Procedures', 1; > > GO > reconfigure > go > > than i Create SP > > all this run in sql server 2005 i need to do same in sql server 2000 > > set ANSI_NULLS ON > set QUOTED_IDENTIFIER OFF > GO > > Create PROCEDURE [dbo].[sp_SQLNotify] > @From varchar(100) , > @To varchar(100) , > @Subject varchar(100)=" ", > @Body varchar(4000) = "Test email for PSC Client" > /********************************************************************* > > This stored procedure takes the above parameters and sends an e-mail. > All of the mail configurations are hard-coded in the stored procedure. > Comments are added to the stored procedure where necessary. > Reference 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 > > --***************Configuring the Message Object ****************** > -- This is to configure a remote SMTP server. > -- > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp > EXEC @hr = sp_OASetProperty @iMsg, > 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' > -- 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("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', > > -- Save the configurations to the message object. > EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null > > -- Set the e-mail parameters. > EXEC @hr = sp_OASetProperty @iMsg, 'To', @To > EXEC @hr = sp_OASetProperty @iMsg, 'From', @From > EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject > > -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. > EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body > EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL > > -- Sample error handling. > IF @hr <>0 > > BEGIN > EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT > > Insert into Testing.dbo.EmailSendMsgs > (EmailTo,EmailFrom,[Message],EmailDate) values > (@To,@From,isnull(@description,'Error Occured!'),GetDate()); > > IF @hr = 0 > BEGIN > SELECT @output = ' Source: ' + @source > PRINT @output > SELECT @output = ' Description: ' + @description > PRINT @output > END > ELSE > BEGIN > PRINT ' sp_OAGetErrorInfo failed.' > RETURN > END > END > > -- Do some error handling after each step if you need to. > -- Clean up the objects created. > EXEC @hr = sp_OADestroy @iMsg > > Insert into Testing.dbo.EmailSendMsgs > (EmailTo,EmailFrom,[Message],EmailDate) values (@To,@From,'Mail > Sent!',GetDate()); > > > > > > > > > > Than can you help me to send mail from sql server 2000 . > "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote > in message news:%23IJyokVqKHA.4492(a)TK2MSFTNGP05.phx.gbl... >> SQL Server 2000 doesn't have database mail. It has SQL Mail which >> requires MAPI (Outlook), art from that, we need to know what you are >> doing to help. That error suggests that you run some sp_configure option >> which doesn't exist in 2000 - that is all we can say with the information >> we have so far. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://sqlblog.com/blogs/tibor_karaszi >> >> >> >> "Hemant" <Hemant(a)nomail.com> wrote in message >> news:u4YHuLVqKHA.3832(a)TK2MSFTNGP02.phx.gbl... >>> Hi, >>> >>> I am trying to send mail from sql server . I have done this by sql >>> server 2005 but not getting success in sql server 2000. >>> >>> The error is : >>> The configuration option 'Ole Automation Procedures' does not exist, or >>> it may be an advanced option. >>> >>> Please help . >>> Thanks, >>> hemant >>> > >
From: Hemant on 9 Feb 2010 06:20 Thanks, I have removed the upper part but i am still getting error: the error is Source: CDO.Message.1 Description: The transport failed to connect to the server. Thanks, hemant "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote in message news:e5NWEbWqKHA.1800(a)TK2MSFTNGP02.phx.gbl... > Where did you get that scrip from? The scrip is designed to work on 2005 > or later, and you can't just assume such code work as-is on an earlier > version of SQL Server. I.e., you need to adapt the script for your version > of SQL Server. The first section with the sp_configure part you can > remove, since there's no database mail on 2000 and ole automation > procedures are always enabled in 2000. Then you would have to take it from > there. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > > "Hemant" <Hemant(a)nomail.com> wrote in message > news:#S41ixVqKHA.4284(a)TK2MSFTNGP04.phx.gbl... >> Thanks for your reply. >> >> I am doing : >> use master >> go >> sp_configure 'show advanced options',1 >> go >> reconfigure with override >> go >> sp_configure 'Database Mail XPs',1 >> --go >> --sp_configure 'SQL Mail XPs',0 >> go >> >> sp_configure 'Ole Automation Procedures', 1; >> >> GO >> reconfigure >> go >> >> than i Create SP >> >> all this run in sql server 2005 i need to do same in sql server 2000 >> >> set ANSI_NULLS ON >> set QUOTED_IDENTIFIER OFF >> GO >> >> Create PROCEDURE [dbo].[sp_SQLNotify] >> @From varchar(100) , >> @To varchar(100) , >> @Subject varchar(100)=" ", >> @Body varchar(4000) = "Test email for PSC Client" >> /********************************************************************* >> >> This stored procedure takes the above parameters and sends an e-mail. >> All of the mail configurations are hard-coded in the stored procedure. >> Comments are added to the stored procedure where necessary. >> Reference 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 >> >> --***************Configuring the Message Object ****************** >> -- This is to configure a remote SMTP server. >> -- >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp >> EXEC @hr = sp_OASetProperty @iMsg, >> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2' >> -- 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("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', >> >> -- Save the configurations to the message object. >> EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null >> >> -- Set the e-mail parameters. >> EXEC @hr = sp_OASetProperty @iMsg, 'To', @To >> EXEC @hr = sp_OASetProperty @iMsg, 'From', @From >> EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject >> >> -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. >> EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body >> EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL >> >> -- Sample error handling. >> IF @hr <>0 >> >> BEGIN >> EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT >> >> Insert into Testing.dbo.EmailSendMsgs >> (EmailTo,EmailFrom,[Message],EmailDate) values >> (@To,@From,isnull(@description,'Error Occured!'),GetDate()); >> >> IF @hr = 0 >> BEGIN >> SELECT @output = ' Source: ' + @source >> PRINT @output >> SELECT @output = ' Description: ' + @description >> PRINT @output >> END >> ELSE >> BEGIN >> PRINT ' sp_OAGetErrorInfo failed.' >> RETURN >> END >> END >> >> -- Do some error handling after each step if you need to. >> -- Clean up the objects created. >> EXEC @hr = sp_OADestroy @iMsg >> >> Insert into Testing.dbo.EmailSendMsgs >> (EmailTo,EmailFrom,[Message],EmailDate) values (@To,@From,'Mail >> Sent!',GetDate()); >> >> >> >> >> >> >> >> >> >> Than can you help me to send mail from sql server 2000 . >> "Tibor Karaszi" <tibor_please.no.email_karaszi(a)hotmail.nomail.com> wrote >> in message news:%23IJyokVqKHA.4492(a)TK2MSFTNGP05.phx.gbl... >>> SQL Server 2000 doesn't have database mail. It has SQL Mail which >>> requires MAPI (Outlook), art from that, we need to know what you are >>> doing to help. That error suggests that you run some sp_configure option >>> which doesn't exist in 2000 - that is all we can say with the >>> information we have so far. >>> >>> -- >>> Tibor Karaszi, SQL Server MVP >>> http://www.karaszi.com/sqlserver/default.asp >>> http://sqlblog.com/blogs/tibor_karaszi >>> >>> >>> >>> "Hemant" <Hemant(a)nomail.com> wrote in message >>> news:u4YHuLVqKHA.3832(a)TK2MSFTNGP02.phx.gbl... >>>> Hi, >>>> >>>> I am trying to send mail from sql server . I have done this by sql >>>> server 2005 but not getting success in sql server 2000. >>>> >>>> The error is : >>>> The configuration option 'Ole Automation Procedures' does not exist, or >>>> it may be an advanced option. >>>> >>>> Please help . >>>> Thanks, >>>> hemant >>>> >> >>
|
Next
|
Last
Pages: 1 2 Prev: Which columns are in what tables Next: Stored procedure, delete from multiple tables |