Category Archives: Sql

Here' my helper function which I put in my master_admin database.

 

CREATE  FUNCTION fn_GetCurrentSQL (

   @sql_handle binary(20),

   @stmt_start INT,

   @stmt_end INT

)

RETURNS VARCHAR(8000)

AS

BEGIN

   RETURN (

   SELECT 

       SUBSTRING(s.TEXT,

           COALESCE(NULLIF((@stmt_start/2)+1, 0), 1),

           CASE @stmt_end 

               WHEN -1 

                   THEN DATALENGTH(s.TEXT) 

               ELSE 

                   (@stmt_end - @stmt_start) 

               END

           ) 

   FROM    ::fn_get_sql(@sql_handle) s

)

END

GO

Here’s my sp_who2 replacement script.

CREATE PROCEDURE [dbo].[sp__who2] (
 @nSortFlag INT = 0,
 @nMinAgo INT = NULL,
 @nSPID INT = NULL,
 @vcDBName VARCHAR(128) = NULL,
 @bAll BIT = 0
 )
AS /*
** Procedure

** Description

** Parameters

** Requirements

** Usage
EXEC dbo.sp_who2 0, 0
EXEC dbo.sp__who2 0, 0, 84
EXEC dbo.sp__who2 0, NULL, 84
EXEC dbo.sp__who2 NULL, NULL, NULL
EXEC dbo.sp__who2 0, NULL, NULL
EXEC dbo.sp__who2 1, NULL, NULL
EXEC dbo.sp__who2 2, NULL, NULL
EXEC dbo.sp__who2 0, 2, NULL
EXEC dbo.sp__who2 1, 2, NULL


-- DECLARE @nSortFlag bit,
-- @nMinAgo int
--
-- SELECT
-- @nSortFlag = 0,
-- @nMinAgo = 1

** Changes
 2008-02-19 John Perry

*/

DECLARE @bootjobtime DATETIME
SELECT @bootjobtime = MIN(last_batch)
FROM master.dbo.sysprocesses (NOLOCK)
WHERE spid < 20


SELECT CAST('A' AS VARCHAR(50)) SortC,
 CAST(0 AS INT) SortN,
 IDENTITY( INT, 1,1 ) AS ID_Num,
 CAST(DB_NAME(dbid) AS VARCHAR(35)) DatabaseName,
 spid,
 ecid,
 BlkBy = CASE CONVERT(CHAR(5), ISNULL(blocked, '0'))
 WHEN '0' THEN ' .'
 ELSE CONVERT(CHAR(5), ISNULL(blocked, '0'))
 END,
 open_tran AS openx,
 physical_io AS io,
 cpu,
 memusage,
 waittime AS waitMS,
 CAST(ISNULL(master_admin.dbo.fn_GetCurrentSQL(sql_handle, stmt_start, stmt_end), cmd) AS VARCHAR(1024)) cmd,
 hostname,
 program_name,
 CONVERT(SYSNAME, RTRIM(loginame)) AS loginname,
 CONVERT(VARCHAR, last_batch, 121) AS last_date,
 CASE WHEN waittype = 0x0000 THEN lastwaittype
 ELSE '*' + lastwaittype
 END WaitType,
 waitresource
INTO #tmpWHO
FROM master.dbo.sysprocesses (NOLOCK)
WHERE (ISNULL(blocked, '0') > 0
 OR open_tran > 0
 )
 AND (@nSortFlag IS NOT NULL)
ORDER BY spid,
 ecid


INSERT #tmpWHO
 (SortC,
 SortN,
 DatabaseName,
 spid,
 ecid,
 BlkBy,
 openx,
 io,
 cpu,
 memusage,
 waitMS,
 cmd,
 hostname,
 program_name,
 loginname,
 last_date,
 WaitType,
 waitresource
 )
 SELECT CASE WHEN 1 = @nSortFlag THEN 'B' + CONVERT(VARCHAR(35), DB_NAME(dbid))
 WHEN 2 = @nSortFlag THEN 'B' + CONVERT(VARCHAR(35), last_batch, 121)
 ELSE 'B'
 END SortC,
 CASE WHEN 0 = 0 THEN spid
 ELSE 99
 END SortN,
 CAST(DB_NAME(dbid) AS VARCHAR(35)) DatabaseName,
 spid,
 ecid,
 BlkBy = CASE CONVERT(CHAR(5), ISNULL(blocked, '0'))
 WHEN '0' THEN ' .'
 ELSE CONVERT(CHAR(5), ISNULL(blocked, '0'))
 END,
 open_tran AS openx,
 physical_io AS io,
 cpu,
 memusage,
 waittime AS waitMS,
 cmd,
 hostname,
 program_name,
 CONVERT(SYSNAME, RTRIM(loginame)) AS loginname,
 CONVERT(VARCHAR, last_batch, 121) AS last_date,
 CASE WHEN waittype = 0x0000 THEN lastwaittype
 ELSE '*' + lastwaittype -- (active)
 END WaitType,
 waitresource
 FROM master.dbo.sysprocesses (NOLOCK)
 WHERE ((@nSortFlag IS NULL)
 OR (ISNULL(blocked, '0') = 0
 AND open_tran = 0
 )
 )
 AND (@nSPID > 0
 OR @nMinAgo IS NULL
 OR last_batch >= DATEADD(minute, -@nMinAgo, GETDATE())
 )

SELECT DatabaseName,
 spid,
 ecid,
 BlkBy,
 openx,
 io,
 cpu,
 memusage,
 waitMS,
 WaitType,
 cmd,
 hostname,
 program_name,
 loginname,
 last_date,
 waitresource
 --, SortC,SortN,id_NUM
FROM #tmpWHO
WHERE (@bAll = 1
 OR last_date > @bootjobtime
 OR @nMinAgo IS NULL
 )
 AND (@vcDBName IS NULL
 OR DatabaseName LIKE '%' + @vcDBName + '%'
 )
 AND (@nSPID IS NULL
 OR @nSPID = 0
 OR spid = @nSPID
 )
ORDER BY SortC,
 SortN,
 spid,
 ecid

DROP TABLE #tmpWHO;
GO

Here's a tutorial site I like.

SQL Tutorial

–xp_cmdshell 'dir e:\mssql\reports'
–Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
–The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

EXEC sp__cmdshell  'dir e:\mssql\reports'

Works!

Here's the procedure:

CREATE PROCEDURE [dbo].[sp__cmdshell] (

   @vcCmd VARCHAR(8000),

   @bNoOutput bit = 0

)

AS

SET nocount ON

DECLARE @RC INT

IF @bNoOutput = 0

   EXEC @RC = master.dbo.xp_cmdshell @vcCmd

ELSE

   EXEC @RC = master.dbo.xp_cmdshell @vcCmd, no_output

IF @RC <> 0 OR @@ERROR <> 0

   PRINT 'ERROR: ' + @vcCmd

RETURN @RC

GO

You probably also need to give permissions to a login or a role to run the new procedure.

GRANT EXECUTE ON [dbo].[sp__cmdshell] TO [db_executor]



Searching for Wildcard Characters

You can search for wildcard characters. There are two methods for specifying a character that would ordinarily be a wildcard:

Use the ESCAPE keyword to define an escape character. When the escape character is placed in front of the wildcard in the pattern, the wildcard is interpreted as a character. For example, to search for the string 5% anywhere in a string, use:

WHERE ColumnA LIKE '%5/%%' ESCAPE '/'
In this LIKE clause, the leading and ending percent signs (%) are interpreted as wildcards, and the percent sign preceded by a slash (/) is interpreted as the % character.

Use square brackets ([ ]) to enclose the wildcard by itself. To search for a hyphen (-), instead of using it to specify a search range, use the hyphen as the first character inside a set of brackets:

WHERE ColumnA LIKE '9[-]5'
The following table shows the use of wildcards enclosed in square brackets.

Symbol Meaning
LIKE '5[%]'
5%

LIKE '5%'
5 followed by any string of 0 or more characters

LIKE '[_]n'
_n

LIKE '_n'
an, in, on (and so on)

LIKE '[a-cdf]'
a, b, c, d, or f

LIKE '[-acdf]'
-, a, c, d, or f

LIKE '[ [ ]'
[

LIKE ']'
]

When string comparisons are performed with LIKE, all characters in the pattern string are significant, including every leading and trailing blank (space). If a comparison to return all rows with a string LIKE 'abc ' (abc followed by a single space) is requested, a row in which the value of that column is abc (abc without a space) is not returned. The reverse, however, is not true. Trailing blanks in the expression to which the pattern is matched are ignored. If a comparison to return all rows with a string LIKE 'abc' (abc without a space) is requested, all rows that start with abc and have zero or more trailing blanks are returned.

 

A handy script to have around for a nice report.

SELECT LEFT(name,50) DatabaseName, CONVERT(VARCHAR(50),DATABASEPROPERTYEX(name, 'recovery')) RecoveryMode  
    FROM sysdatabases 
    WHERE name NOT IN ('model','tempdb') 
    ORDER BY name