SELECT TOP n rows

\ by

I love how top can accept a variable. You could pass it into a procedure.

DECLARE @vcOut VARCHAR(MAX),
    @nTop INT

SELECT  @nTop = 5
SELECT TOP (@nTop)
        @vcOut = COALESCE(@vcOut, '') + NAME + CHAR(10)
FROM    sys.sysdatabases

PRINT @vcOut
--
Posted in: Coding, SQL

SQL Processes, sys.dm_exec_connections and last sql statement

\ by

Last SPIDs, their statement, network packet size.

SELECT  c.session_id,
        s.host_name,
        s.login_name,
        [LastStatement].[[text]],
        c.net_packet_size,
        s.login_time,
        s.status,
        d.name
FROM    sys.dm_exec_connections c
INNER JOIN sys.dm_exec_sessions s
ON      c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS LastStatement
LEFT OUTER JOIN
	sys.databases d on d.database_id = [LastStatement].dbid
ORDER BY s.[status],
        s.login_time DESC,
        s.[host_name]
  
--

Source

Posted in: SQL

xp_cmdshell error: ‘C:\Program’ is not recognized as an internal or external command,

\ by

Apparently in my 64-bit environment I can’t execute the following command.

DECLARE @vcCmd VARCHAR(8000)
SELECT @vcCmd = '"C:\Program Files\Winzip\WZZIP.exe" -es -a "E:\DATA\test.zip" "E:\DATA\test.dat"'
EXEC   master..xp_cmdshell @vcCmd = @vcCmd
/*
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
*/

The solution is I can execute other DOS commands, so let’s concatenate them.

DECLARE @vcCmd VARCHAR(8000)
SELECT @vcCmd = 'C: && CD "C:\Program Files\Winzip\" && WZZIP.exe -es -a "E:\DATA\test.zip" "E:\DATA\test1.dat"'
EXEC   master..xp_cmdshell @vcCmd = @vcCmd
Posted in: DOS, SQL

Check SQL jobs which have run during daylight saving’s change hours.

\ by

What has run, during the last month, on Sunday’s between 1:59 AM and 3?
Use the to check on daylight saving’s jobs which might be missed.


SELECT MAX(master_admin.dbo.fn_INTtoDateTime(run_date, run_time) ),
j.name
FROM msdb.dbo.sysjobhistory h WITH (NOLOCK)
JOIN msdb.dbo.sysjobs j WITH (NOLOCK)
ON j.job_id = h.job_id
WHERE datename(dw, convert(varchar(10),run_date, 112) ) = 'Sunday'
AND run_time BETWEEN 015900 AND 030000
AND step_id = 0
AND run_date >= CONVERT(VARCHAR(8), DATEADD(day, -31, GETDATE()), 112)
GROUP BY j.name ,DATENAME(weekday, CONVERT(VARCHAR(10), run_date, 112))
ORDER BY
name

This uses one of my functions. fn_INTtoDateTime

Posted in: SQL

[master_admin].[dbo].[fn_INTtoDateTime]

\ by

I use this when querying msdb.dbo.sysjobhistory.


USE [master_admin]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_INTtoDateTime] (
@vcDate VARCHAR(8),
@vcTime VARCHAR(6)
)
RETURNS DATETIME
AS
BEGIN
SET @vcDate = RIGHT(RTRIM('00000000' + CONVERT(CHAR(8), @vcDate) + ' '), 8)
SET @vcTime = RIGHT(RTRIM('00000000' + CONVERT(CHAR(6), @vcTime) + ' '), 6)

IF @vcDate = '00000000'
SET @vcDate = LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), CAST(0 AS SMALLDATETIME), 121),
'-', ''), ':', ''),
SPACE(1), ''), 8)

RETURN (
CAST(SUBSTRING(@vcDate,1,4) + '-' + SUBSTRING(@vcDate,5,2) + '-' + SUBSTRING(@vcDate,7,2) + ' ' +
SUBSTRING(@vcTime,1,2) + ':' + SUBSTRING(@vcTime,3,2) + ':' + SUBSTRING(@vcTime,5,2)
AS DATETIME)
)
END

GO
Posted in: SQL

SQL – All databases users all their roles, except reader

\ by

For every database return non-db_datareaders, dbo, and ## users database permissions.

-- Declaration of table variable for the storage of results
DECLARE @result AS TABLE (
 database_name SYSNAME,
 role_name SYSNAME,
 principal_name SYSNAME
 );

-- Insert the resultset from each database
INSERT INTO @result
 EXEC sp_msforeachdb N'USE [?]
SELECT CASE WHEN DB_NAME() IN (''master'',''msdb'',''model'',''tempdb'') THEN ''*'' + DB_NAME() ELSE DB_NAME() END AS database_name,
 USER_NAME(role_principal_id) AS [Role],
 USER_NAME(member_principal_id) AS [User]
FROM sys.database_principals p
INNER JOIN sys.database_role_members m
ON (p.principal_id = m.member_principal_id)
INNER JOIN sys.database_principals r
ON (m.role_principal_id = r.principal_id)
WHERE r.name NOT IN (''db_datareader'')
 AND USER_NAME(member_principal_id) <> ''dbo''
 AND USER_NAME(member_principal_id) NOT LIKE ''##%''
ORDER BY [Role],
 [User]
;'
-- Display the result
SELECT *
FROM @result
ORDER BY database_name,
 principal_name;
Posted in: Permissions, SQL

SQL Msg 15138: The database principal owns a schema in the database, and cannot be dropped.

\ by

Here’s my script to fix this error. This occurs when I restore a database to another server and want to remove the source server users.


DECLARE @vcSchemaName VARCHAR(128);
SET @vcSchemaName = 'schema name goes here'
SELECT s.name,
 'ALTER AUTHORIZATION ON SCHEMA::[' + @vcSchemaName + '] TO dbo;'
FROM sys.schemas s
WHERE s.principal_id = USER_ID(@vcSchemaName
Posted in: ERRORs, SQL

TSQL – Create an ASCII chart

\ by

This code shows the Symbol and HEX as well as DEC. Still to go is OCT.


WITH Symbols
 AS (
 SELECT -1 code,
 CHAR(ASCII(' ')) Symbol
 UNION ALL
 SELECT code + 1,
 CHAR(code + 1)
 FROM Symbols
 WHERE code + 1 <= 255
 )
 SELECT Symbol,
 ASCII(Symbol) AS [DEC],
 master.dbo.fn_varbintohexstr(CAST(Symbol AS VARBINARY)) AS HEX
 --,(ASCII(Symbol) % 8) AS [OCT]
 FROM Symbols
 ORDER BY [DEC]
OPTION (MAXRECURSION 256)
Posted in: SQL

SQL Agent Jobs – Listing job owners even those in AD Groups

\ by

I sometimes find job owners are from an Active Directory group. Here’s a report on how to find these. (The example code also shows one way to delete the jobs.)

USE msdb
go

SELECT 'EXEC sp_delete_job @job_name = ''' + sj.name + ''';' AS RunSQL,
 sj.name AS 'job',
 SUSER_SNAME(owner_sid) AS 'owner',
 spr.NAME AS 'proxy'
 --,sj.*
FROM msdb.dbo.sysjobs sj
LEFT JOIN msdb.dbo.sysjobsteps sjt
ON sj.job_id = sjt.job_id
LEFT JOIN msdb.dbo.sysproxies spr
ON spr.proxy_id = sjt.proxy_id
ORDER BY sj.name
Posted in: SQL, SQLAgent