|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
XP_SENDMAIL Overload in SQL 2005got no response. I'll try programming. In SQL 2000, I could drop system extended stored procedures without any problem and replace them with my own extended stored procedures or stored procedures with the same name. One of these that I routinely dropped (on servers that I control) was XP_SENDMAIL. I replaced it with a shell procedure that then called an SMTP based solution. In SQL 2005, I can use SP_SEND_DBMAIL as my SMTP solution! Hurray. However, I don't want to go back and recode all my in-house procedures and the like to use SP_SEND_DBMAIL. Rather, I want to replace XP_SENDMAIL again with a wrapper around SP_SEND_DBMAIL. However, I can't seem to drop the SYS.XP_SENDMAIL extended stored procedure even though I have SQL Mail "turned off" in the SQL Server configuration. Am I going to be able to drop this unwanted extended stored procedure or is it stuck in the system resource database (and thus essentially undroppable)? Ah, finally I get to shed some light on the beauty of synonyms!
Create a wrapper stored procedure in msdb or some other "safe" place. Then, in your user databases, you can do this: CREATE SYNONYM [dbo].[xp_sendmail] FOR msdb.dbo.myMailWraper; Now your code that calls dbo.xp_sendmail will actually get routed to the wrapper procedure. (I assume your code doesn't call sys.xp_sendmail, and if it does, search and replace is as complicated as this should get.) A Show quote "Dr. Network" <charles.hawk***@jenzabar.net> wrote in message news:eD%236MTA2GHA.1548@TK2MSFTNGP02.phx.gbl... > Not sure whether this is a setup or a programming issue. I tried setup and > got no response. I'll try programming. > > In SQL 2000, I could drop system extended stored procedures without any > problem and replace them with my own extended stored procedures or stored > procedures with the same name. One of these that I routinely dropped (on > servers that I control) was XP_SENDMAIL. I replaced it with a shell > procedure that then called an SMTP based solution. > > In SQL 2005, I can use SP_SEND_DBMAIL as my SMTP solution! Hurray. > > However, I don't want to go back and recode all my in-house procedures and > the like to use SP_SEND_DBMAIL. Rather, I want to replace XP_SENDMAIL > again > with a wrapper around SP_SEND_DBMAIL. However, I can't seem to drop the > SYS.XP_SENDMAIL extended stored procedure even though I have SQL Mail > "turned off" in the SQL Server configuration. > > Am I going to be able to drop this unwanted extended stored procedure or > is > it stuck in the system resource database (and thus essentially > undroppable)? > > > Here is the problem. Much of my code just calls XP_SENDMAIL instead of
DBO.XP_SENDMAIL and it lives in the context of master, being for overall system admin of the server instance. Where it lives outside of master, I make explicit calls to MASTER.DBO.XP_SENDMAIL. So I'd have to go back and search and replace, which is what I want to avoid. There is a way and THIS MAY BE DANGEROUS! Don't do this lightly and MS will never, EVER support it. -- First, stop your instance and make a copy of the files -- mssqlsystemresource.mdf and mssqlsystemresource.ldf. -- start instance from the command prompt in single user mode sqlservr.exe -s instancename -c -m -- login with sa and DAC USE MASTER GO ALTER DATABASE mssqlsystemresource set READ_WRITE; GO use mssqlsystemresource GO dbcc dropextendedproc ('xp_sendmail') GO USE MASTER GO ALTER DATABASE mssqlsystemresource set READ_ONLY; GO I am doing this on a "throw away instance" that I'll continue to work on but with trepidation. Obviously after rolling in a service pack that replaces the mssqlsystemresource DB I'll have to reapply the "fix". If this works, though, I have a "customized" system resource database that I can then replace all my instances with. SCARY, I admit!!! It annoys me that MS is deprecating extended stored procedures but still put them in the system resource database. Chuck Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:OndmogA2GHA.1256@TK2MSFTNGP02.phx.gbl... > Ah, finally I get to shed some light on the beauty of synonyms! > > Create a wrapper stored procedure in msdb or some other "safe" place. > > Then, in your user databases, you can do this: > > CREATE SYNONYM [dbo].[xp_sendmail] FOR msdb.dbo.myMailWraper; > > Now your code that calls dbo.xp_sendmail will actually get routed to the > wrapper procedure. > > (I assume your code doesn't call sys.xp_sendmail, and if it does, search > and replace is as complicated as this should get.) > > A > > > > > > > > > "Dr. Network" <charles.hawk***@jenzabar.net> wrote in message > news:eD%236MTA2GHA.1548@TK2MSFTNGP02.phx.gbl... >> Not sure whether this is a setup or a programming issue. I tried setup >> and got no response. I'll try programming. >> >> In SQL 2000, I could drop system extended stored procedures without any >> problem and replace them with my own extended stored procedures or stored >> procedures with the same name. One of these that I routinely dropped (on >> servers that I control) was XP_SENDMAIL. I replaced it with a shell >> procedure that then called an SMTP based solution. >> >> In SQL 2005, I can use SP_SEND_DBMAIL as my SMTP solution! Hurray. >> >> However, I don't want to go back and recode all my in-house procedures >> and >> the like to use SP_SEND_DBMAIL. Rather, I want to replace XP_SENDMAIL >> again >> with a wrapper around SP_SEND_DBMAIL. However, I can't seem to drop the >> SYS.XP_SENDMAIL extended stored procedure even though I have SQL Mail >> "turned off" in the SQL Server configuration. >> >> Am I going to be able to drop this unwanted extended stored procedure or >> is >> it stuck in the system resource database (and thus essentially >> undroppable)? >> >> >> > > Dr. Network wrote:
> Here is the problem. Much of my code just calls XP_SENDMAIL instead of The way we avoid this kind of thing is by having our own "system" > DBO.XP_SENDMAIL and it lives in the context of master, being for overall > system admin of the server instance. Where it lives outside of master, I > make explicit calls to MASTER.DBO.XP_SENDMAIL. So I'd have to go back and > search and replace, which is what I want to avoid. > database where we store our admin sprocs, utilities, etc.. We also write our own "wrappers" for basic system functions, like sending mail. We have a sproc named "procSendEmail" that accepts parameters for recipients, message text, etc.. THAT sproc in turn calls xp_smtp_sendmail, every other procedure that sends mail refers only to procSendEmail. This allows us to drop in any low-level email component that we want, and we only have to modify one procedure to make it all work. > Here is the problem. Much of my code just calls XP_SENDMAIL instead of Why? Why not fix this once now. Point everything to your own wrapper > DBO.XP_SENDMAIL and it lives in the context of master, being for overall > system admin of the server instance. Where it lives outside of master, I > make explicit calls to MASTER.DBO.XP_SENDMAIL. So I'd have to go back and > search and replace, which is what I want to avoid. stored procedure, which you can then proceed to change as often as you want, switching from xp_sendmail to xp_smtp_sendmail to sp_send_dbmail and back again. > There is a way and THIS MAY BE DANGEROUS! So why do it? And why continue to develop and maintain your system in such a way that you will have to repeat this process every time you deploy to a new server? Doesn't sound like a very good recommendation to me, not only because of the risk and invalidation of support, but also because MS may very well catch and cancel that SET READ_WRITE call in some future version or service pack. A All of your points are well taken. It all has to do with that I could write
a single procedure that would work in any environment. Essentially I was hoping to have it work whether it was on SQL 2000 w/ SQL Mail, SQL 2000 with SMTP mail, or SQL 2005 with DB mail. Further if another application made use of XP_SENDMAIL and I wanted to overload XP_SENDMAIL on my servers with an SMTP solution this was possible. The root of the problem was that SQL Mail was so bad in its interface with Exchange server on SQL 2000. Looks like I'll bite the bullet and get totally way from the bad idea of XP_SENDMAIL. It is interesting that you can delete the thing from the system resource database. I wonder if you can add something to it that will become a system resource to every database? Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:ONc9bMB2GHA.3656@TK2MSFTNGP04.phx.gbl... >> Here is the problem. Much of my code just calls XP_SENDMAIL instead of >> DBO.XP_SENDMAIL and it lives in the context of master, being for overall >> system admin of the server instance. Where it lives outside of master, I >> make explicit calls to MASTER.DBO.XP_SENDMAIL. So I'd have to go back and >> search and replace, which is what I want to avoid. > > Why? Why not fix this once now. Point everything to your own wrapper > stored procedure, which you can then proceed to change as often as you > want, switching from xp_sendmail to xp_smtp_sendmail to sp_send_dbmail and > back again. > >> There is a way and THIS MAY BE DANGEROUS! > > So why do it? And why continue to develop and maintain your system in > such a way that you will have to repeat this process every time you deploy > to a new server? Doesn't sound like a very good recommendation to me, not > only because of the risk and invalidation of support, but also because MS > may very well catch and cancel that SET READ_WRITE call in some future > version or service pack. > > A > > All of your points are well taken. It all has to do with that I could And this can work, you just need a flag somewhere that tells the wrapper > write a single procedure that would work in any environment. Essentially I > was hoping to have it work whether it was on SQL 2000 w/ SQL Mail, SQL > 2000 with SMTP mail, or SQL 2005 with DB mail. which object to target. > Further if another application made use of XP_SENDMAIL and I wanted to ....which makes it very unlikely that any application out there would rely on > overload XP_SENDMAIL on my servers with an SMTP solution this was > possible. > > The root of the problem was that SQL Mail was so bad in its interface with > Exchange server on SQL 2000. SQL Mail being configured correctly. Most applications are better off sending mail on their own, rather than doing it through the database, anyway. I guess there is a slight chance that some narrow-minded vendors would ship stored procedures with xp_sendmail hard-coded but that seems unlikely to be a major concern here. > It is interesting that you can delete the thing from the system resource There is a saying, "curiosity killed the cat."> database. I wonder if you can add something to it that will become a > system resource to every database? If you find something like this that works for you, my advice is to keep it to yourself. If you post it in public, that makes it more visible to Microsoft, which in turn makes it more likely to become a candidate for 'fixing" in a future version or service pack. A Here is an easy way to get the databases/procedures that contain a reference
to xp_sendmail in any form. Very minor modifications required from the result of the result. Run this, copy the result to the top pane, run that, copy the result and then change CREATE to ALTER and remove the lines between each definition that say "Text"... now it *is* just about as simple as search and replace. EXEC master.dbo.sp_msForEachDB 'IF EXISTS ( SELECT 1 FROM ?.sys.sql_modules WHERE definition LIKE ''%xp[_]sendmail%'' ) SELECT ''USE ?; ''+CHAR(13)+CHAR(10) +''GO'' +CHAR(13)+CHAR(10)+''PRINT ''''USE ?'''';'' +CHAR(13)+CHAR(10)+ ''EXEC sp_helptext ''+p.name FROM ?.sys.sql_modules m INNER JOIN ?.sys.procedures p ON m.object_id = p.object_id WHERE m.definition LIKE ''%xp[_]sendmail%''; ';  |
|||||||||||||||||||||||
Other interesting topics