SQL Server Performance Forum – Threads Archive

SQL Server Version

Hi, my company is using MsSQL 2000 and might upgrade to MsSQL2005 ( not confirm yet ) and i got an assignment need to provide the mail function by using Transact SQL. I found below
store procedure can detect what version of SQL i am using but i don’t know how to store
the value into variable. eg declare @vcSQLVersion varchar(50) Set @vcSQLVersion = exec xp_msver ‘ProductVersion’ <— How to do it ? if @vcSQLVersion = ‘8.00……’ then is SQL2000 and ‘9.00….’ is SQL 2005.
if SQL 2005, then i will use EXEC msdb.dbo.sp_send_dbmail else i will use
master.dbo.xp_smtp_sendmail. or other way to do it ??? like check whether the sp_send_dbmail exists, if yes then use
sp_send_dbmail instead of xp_smtp_sendmail ??? thanks in advance.

---

SELECT SERVERPROPERTY(‘productversion’) will give you sql server version
http://support.microsoft.com/kb/321185
—————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

---

ServerProperty is the best option as dineshasanka mentioned…
But you can try the following too without checking the sql version… If exists (select 1 from mastert..sysobjects where name = ‘sp_send_dbmail’
begin
exec sp_send_dbmail ……
end
else
begin
exex xp_sendmail
end
MohammedU.
Moderator
SQL-Server-Performance.com

---

]]>