Padding Numbers Leading Zeros Using Transact-SQL

30-Dec-0909 Leave a comment

 
From
 
Use Str function which converts numeric data to a string.
 
SELECT replace(str(1,5),’ ‘,’0’)        — Outputs ‘00001’
SELECT replace(str(123,5),’ ‘,’0’) — Outputs ‘00123’
SELECT replace(str(12345,5),’ ‘,’0’)    — Outputs ‘12345’
SELECT replace(str(123456,5),’ ‘,’0’)   — Outputs ‘*****’
 
there is a third argument to str function which is no of decimals to keep
 
 
 

Filed under SQL Server

SQL Server Database Mail Stopped

09-Dec-0909 Leave a comment

 
 
 
USE msdb ;
GO
EXECUTE dbo.sysmail_start_sp ;
GO
 
To get it going again. Also possible to right-click on Management > Database Mail > View Database Mail Log
 
 

Filed under SQL Configuration, SQL Server Tagged with

How do I find a text in SQL Object ?

03-Dec-0909 Leave a comment

 
Started with:
 
 
Adapted to:

ALTER PROCEDURE [Testing].[uspFindText]

@DatabaseName

nvarchar(50) = ‘EFI’
, @TextToFind nvarchar(100)
–, @Type int = 0 –0 = ALL, 1 = PROCEDURE

AS

DECLARE

@SQL nvarchar(500)
SELECT @SQL = ‘SELECT ROUTINE_TYPE, SPECIFIC_SCHEMA, ROUTINE_NAME, ROUTINE_DEFINITION
FROM [‘ + @DatabaseName + ‘].INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ”%’ + @TextToFind + ‘%”’

–SELECT @SQL = @SQL + ‘ AND ROUTINE_TYPE=”PROCEDURE”’

PRINT

@SQL
EXECUTE(@SQL)

 

Filed under SQL Server, Testing