CDO Email

For those who are interested in using SQL cdosysmail to email embedded HTML follow this code as a model. Keep in mind that you are required to have the Stored Procedure sp_send_cdosysmail.

SET NOCOUNT ON
— SETUP VARIABLES
 DECLARE @body1 VARCHAR(MAX)
 DECLARE @subjecttitle VARCHAR(100)
 DECLARE @email VARCHAR(200)
DECLARE @today DATETIME

SET @today = (SELECT CONVERT(VARCHAR,GETDATE()-1,101))
SET @subjecttitle = ‘Daily Sales Summary Report for ‘ + CONVERT(VARCHAR,@today)

— PROCESS EMAIL DATA
 SET @body1=’


SELECTTOP 20 @body1 = @body1 +’

FROM dbo.TransactionHeader
WHERE DIMENSION = 1001 AND RECEIPTDATE = @today
GROUP BY DIMENSION— SEND THE EMAIL
exec master.dbo.sp_send_cdosysmail
@From=’cbuisr@gmail.com’,
@To =’cbuisr@gmail.com’,
@Subject = @subjecttitle,
@Body =  @body1

Act Plan Var $ Var % LY Comp

‘ + CONVERT(VARCHAR,DIMENSION) + ‘ ‘+ CONVERT(VARCHAR,SUM(CONVERT(DECIMAL(12,2),TOTALBEFORETAX))) + ‘ ‘ + ‘VAR $’ + ‘ ‘+ ‘Var %’ + ‘ ‘ + ‘LY’ + ‘ ‘ + ‘Comp’ + ‘
Advertisements

Maximum Connection Limit

The Maximum Connection Limit Has Been Reached

This article refers to any weird cases and in this case it’s making a Linked Server Connection to DB2.

To make nice with SQL run these steps.

  1. sp_configure 'user connections', 0 GO
  2. reconfigure with override
  3. Go to services and restart SQL Server

Now when you need to make or test the connection, it should work.

Find Columns in Table

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%EncTotChgAmt%’
ORDER BY schema_name, table_name;

Find a String in all Tables

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET NOCOUNT ON;

DECLARE    @SearchStr NVARCHAR(100)

DROP TABLE #Results

CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630))

DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(128), @SearchStr2 NVARCHAR(110)

SET  @TableName = ”

SET @SearchStr2 = QUOTENAME(‘%’ + ‘Muneeb’ + ‘%’,””)

WHILE @TableName IS NOT NULL

BEGIN

SET @ColumnName = ”

SET @TableName =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME))

FROM     INFORMATION_SCHEMA.TABLES

WHERE         TABLE_TYPE = ‘BASE TABLE’

AND    QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME) > @TableName

AND    OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + ‘.’ + QUOTENAME(TABLE_NAME)

), ‘IsMSShipped’

) = 0

)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN

SET @ColumnName =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM     INFORMATION_SCHEMA.COLUMNS

WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)

AND    TABLE_NAME    = PARSENAME(@TableName, 1)

AND    DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘NVARCHAR’, ‘int’, ‘decimal’)

AND    QUOTENAME(COLUMN_NAME) > @ColumnName

)

IF @ColumnName IS NOT NULL

BEGIN

INSERT INTO #Results

                EXEC

(

SELECT ”’ + @TableName + ‘.’ + @ColumnName + ”’, LEFT(‘ + @ColumnName + ‘, 3630)

FROM ‘ + @TableName + ‘ (NOLOCK) ‘ +

WHERE ‘ + @ColumnName + ‘ LIKE ‘ + @SearchStr2

)

END

        END   

    END

     SELECT ColumnName, ColumnValue FROM #Results

Get Table Unused Space

To find out how much space is being used in a table you’ll need to use the system’s sp_spaceused stored procedure.

EXEC sp_spaceused N’dbo.orders’

The following are the options:

name: Table name for which space usage information was requested

rows: Number of rows existing in the table

reserved: Total amount of reserved space for table data and indexes

data: Amount of space used by table data

index_size: Amount of space used by table indexes

unused: Total amount of space reserved for table but no yet used

Find a String in a Stored Procedure

If you every decided to do something ridiculous and whatever you did requires that  all SP needs to be updated, here is how you can thumb through the SP in search of the string.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ‘%AS400%’
AND ROUTINE_TYPE=‘PROCEDURE’

Creating Temp Variable Table

Using temp variables is great if you’re using it for SSRS. Why? Because SSRS does not allow temp table i.e. (#mytable, ##myothertable) but it will allow temp variables. Although SQL will discard the temp variable when it’s done with it, always explicitly drop the temp. Furthermore, if you use GO; anywhere after your declared table, the table will go bye-bye!

DECLARE @customer TABLE  (ID INT IDENTITY, FirstName VARCHAR(20), LastName VARCHAR(20))

INSERT @customer (FirstName, LastName)

SELECT ‘Chris’, ‘Bui’

SELECT * FROM @customer

DROP TABLE @customer