Ability to send an email from a MSSQL Stored Procedure http:www.sqldev.net/xp/xpsmtp.htm Download the attached .zip and extract Copy the xpsmtp80.dll to C:\Program Files\Microsoft SQL Server\MSSQL\Binn Run this procedure on the servers master database _ exec sp_addextendedproc 'xp_smtp_sendmail', 'xpsmtp80.dll' Use this syntax for a simple email _ DECLARE @rc INT EXEC @rc = master.dbo.xp_smtp_sendmail @FROM = N'from@email.com', @FROM_NAME = N'fromname', @replyto = N'replyto@email.com', @TO = N'...

This query will display a list of all tables along with disk sizes and index sizes -- Get all tables rows count, sizes, index size in MB -- mReschke 2012-10-16 SET NOCOUNT ON DECLARE @cmdstr VARCHAR(100) CREATE TABLE #TempTable ( VARCHAR(150), ROW_COUNT INT, Table_Size VARCHAR(150), Data_Space_Used VARCHAR(150), Index_Space_Used VARCHAR(150), Unused_Space VARCHAR(150)) CREATE TABLE #TempTable2 (TableName VARCHAR(150), INT, DataMB INT, IndexMB INT, TotalMB INT, UnusedMB INT) SELECT @cmdstr = 'sp_...

How to return a comma separated list, in one column, from a field in multiple rows. If you have, for example, email addresses on multiple rows in a table but want to return them as one column, comma separated. Example: Database table ID | report_id | email | 1 | 1 | email1@email.com 2 | 1 | email2@email.com 3 | 1 | email3@email.com I want a return of report_id | email -| 1 | email1@emailcom,email2@email.com,email3@email.com To achieve this, you must create a user defined function which will conc...

SQL Server 2000 Maintenance > The database administrator works like a doctor, keeping databases healthy. Learn the routine maintenance tasks that can help your databases live a long and active life. SQL Server 2000 eliminates the need to run DBCC CHECKDB or DBCC CHECKALLOC. 99% of database failures and corruption with SQL Server 2000 happen due to hardware failures. It is still a good Idea to run DBCC CHECKDB to ensure the overall health of your database prior to backing it up. DBCC C...

If you have a column that contains some alpha and some numeric data, SQL sees the entire column as alpha and sorts the data differently. Here is an example of an alpha column order by: 105k 10k 120k 20k Another Field Yet Another Field This is obvious since 5 (in 105k) is less than k (in 10k). This is the logical, correct way to sort alpha-numeric data. But I want the order to be 10k, 20k, 105k... In SQL, the column type is defined by the table you are pulling the data from, so if the table colu...

<p>There are three ways to avoid the "Division by zero encountered" error in your SELECT statement and these are as follows:</p> <ul> <li>CASE statement</li> <li>NULLIF/ISNULL functions</li> <li>SET ARITHABORT OFF and SET ANSI_WARNINGS OFF</li> </ul>

Date and time function tutorials and examples in MSSQL This topic contains a few of the MSSQL date functions and some useful snippets. Dates can get a little confusing in SQL but a good knowledge of their functions is critical for most data queries. Since the date manipulation queries are quite large, its best to set a @variable and reference that variable throughout the code. Remember that the SQL data type has hours, minutes, seconds and sometimes milliseconds also, so you must account for th...

<ol> <li>Goto Administration Tools -> Component Services<ol> <li>Under Services (local), start the 'Distributed Transaction Coordinator'</li> <li>Goto properties on 'Distributed Transaction Coordinator', Log On tab, make sure account is NetworkServices</li> </ol></li> <li>Under Component Services, expand to My Computer, goto properties on My Computer<ol> <li>MSDTC tab, then click Security Configuration</li> <li>Check 'Network DTC Access'</li> <li>Check 'Allow Remote Administration'</li> <li>Check 'Allow Inbound'</li> <li>Check 'Allow Outbound'</li> <li>Check 'No Authentication Required'</li> <li>Check 'Enable Transaction Internet Protocol (TIP) Transactions'</li> <li>Check 'Enable XA TRansactions'</li> <li>The Acount should be NetworkServices here also</li> </ol></li> <li>Open MSSQL Enterprise Manager, expand to localhost<ol> <li>Open Security</li> <li>Right Click Linked Servers, click New Linked Server...</li> <li>Enter each servers information, on each server</li> </ol></li> </ol>

CREATE TABLE #month0to6 (vin nvarchar(20)) DECLARE @startDate as nvarchar(10), SCF as nvarchar SET @startDate = '10/31/2007' --Insert 0 to 6 month data INSERT INTO #month0to6 (vin) SELECT DISTINCT tblVehicles.VEH_vin FROM tblROTotal --INNER JOIN tblApplications ON tblROTotal.ROH_DLR_APP_ID = tblApplications.APP_ID INNER JOIN tblVehicles ON tblROTotal.ROH_VEH_ID = tblVehicles.VEH_ID INNER JOIN tblMakeModel ON tblVehicles.VEH_MM_ID = tblMakeModel.MM_ID INNER JOIN tblCustomer ON tblCustomer.Cust_I...

DECLARE @server as nvarchar(50), @sql as nvarchar(4000) DECLARE SharedServers CURSOR FORWARD_ONLY FOR SELECT server FROM .ebis_prod.dbo.SharedServerList OPEN SharedServers FETCH NEXT FROM SharedServers INTO @server BEGIN TRANSACTION SET @sql = '' WHILE FETCH_STATUS = 0 BEGIN IF (@server) '' BEGIN --One itteration of SQL goes HERE SET @sql = @sql INSERT INTO .dyna.dbo.TestTable (val1, val2) VALUES (''mReschke'', ''hi'') ' END FETCH NEXT FROM SharedServers INTO @server END exec sp_executesql @sql...