process a table of commands via a stored proc

April 3, 2018

CREATE TYPE command_list AS TABLE (id int,cmd varchar(MAX))

–SELECT * FROM sys.types WHERE is_user_defined = 1

GO

CREATE PROC dba.usp_process_command_list

@input_list command_list readonly
AS

–jlester 20180329 – takes a list of commands and processes them
–input table has 2 fields id – an int starting from 1 and incrementing by 1 for each command
–cmd – is the command to be processed
— here is a sample block of code:

/*

DECLARE @x command_list

INSERT INTO @x
SELECT 1 ,’select 1′
UNION ALL
SELECT 2 ,’select getdate()’

EXEC dba.usp_process_command_list @input_list = @x

*/

DECLARE @command_list TABLE
(id int,cmd varchar(MAX))

INSERT @command_list
SELECT * from @input_list

DECLARE @maxid INT = (SELECT MAX(id) FROM @command_list) –find the last command to be processed
DECLARE @curid INT = 1 –start from command 1
DECLARE @curcmd VARCHAR(MAX)

–PRINT @maxid

while @curid<=@maxid –loop through the list 1 record at a time
BEGIN
–PRINT @curid
SET @curcmd = (SELECT cmd FROM @command_list WHERE id =@curid) –fetch the command for the current id

–print @curcmd
exec (@curcmd)

SET @curid += @curid –increment the current id by 1
–PRINT @curid
END

–SELECT * FROM @command_list

GO

DECLARE @x command_list

INSERT INTO @x
SELECT 1 ,'select 1'
UNION ALL
SELECT 2 ,'select getdate()'

EXEC dba.usp_process_command_list @input_list = @x

GO

Advertisements

generate a comma seperated list of columns heading

January 20, 2018

alter proc xxx.usp_generate_column_name_string
@database varchar(100),@schema varchar(100), @table varchar(100)

as

declare @str varchar(max) = ‘

select *,”’+@database+”’ as database_name,”’+@schema+”’ as schema_name,”’+@table+”’ as table_name from (
select stuff((select ”,” + name as [text()]
from
(
select c.name from ‘ + @database + ‘.sys.tables a
inner join ‘ + @database + ‘.sys.schemas b on a.schema_id = a.schema_id
inner join ‘ + @database + ‘.sys.columns c on c.object_id= a.object_id
where b.name = ”’+@schema+”’ and a.name =”’+@table+”’) x

for xml path (””)),1,1,””) cols
) a


exec (@str)

go

exec xxx.usp_generate_column_name_string ‘test’ , ‘dbo’,’jl1_tmp’

Defrag Database Files

January 15, 2018

I was prevented from running CHECKDB in one of our largest databases: CheckDB fails with error: The operating system returned error 665

This is due to fragmentation of database files.

I checked the fragmentation using: https://docs.microsoft.com/en-us/sysinternals/downloads/contig

I could not get the 64 bit version to work but the 32 bit version works

Extract the exe to the c drive, not your users folder and open up a command prompt in that directory example c:\contig -a “file path” will tell you how fragmented your file is, in my case 1.7 million fragments c:\contig “file path” will defrag the file, you dont need to be offline to do this. You can check progress by opening up a new command prompt and repeating the command with the -a switch

Capturing Execution Plans for Overnight Processes

September 13, 2017

Scenario: I want to capture execution plans for a particular overnight process. Maybe I want to check what indexes a query is using at 3AM and compare its index usage at 7AM

Add the following code to your procedure:

DECLARE @xyz VARBINARY(MAX) = CAST(‘ANY TEXT HERE’ AS VARBINARY(MAX))
SET CONTEXT_INFO @xyz

Select @xyz and obtain the variable value

For example: 0x646174616D6172742E7573705F646D5F637573746F6D6572726567697374726174696F6E

Remove the initial 0x and make a note of the rest of the value

Create an Extended Event

add event “Query Post Execution Showplan”

add filter sqlserver.context_info equal binary data 646174616D6172742E7573705F646D5F637573746F6D6572726567697374726174696F6E

add an event file to store the output and start the Extended Event. Dont use ring buffer, I cant get the process to work with ring buffer although admitedly I havent really persevered.

Let the overnight process complete and the next day right click the event file and View Target Data, click on a record and change to Query Plan tab in the bottom half of your screen to view the query plan.

Detecting Database access via AD group membership

September 4, 2017

SET NOCOUNT on

SELECT id=IDENTITY (int, 1,1),’exec xp_logininfo ”’ +name +”’,”members”’ code

INTO #t

FROM sys.server_principals WHERE type = ‘G’

–SELECT * FROM #t

CREATE TABLE #r (account_name VARCHAR(100),type_desc VARCHAR(100),privilege VARCHAR(100),mapped_login_name VARCHAR(100),permission_path VARCHAR(100))

DECLARE @maxid INT = (SELECT MAX(id) FROM #t)

DECLARE @currentid INT = 1

WHILE @currentid<=@maxid

BEGIN

DECLARE @code VARCHAR(MAX) = (SELECT code FROM #t WHERE id = @currentid)

–PRINT @code

INSERT INTO #r

EXEC (@code)

SET @currentid = @currentid +1

END

IF OBJECT_id('database_name.dbo.permissions_table') IS NULL

SELECT *,GETDATE() date_captured

INTO ('database_name.dbo.permissions_table

FROM #r

IF OBJECT_id('('database_name.dbo.permissions_table_audit') IS NULL

SELECT TOP 0 *,GETDATE() date_captured, 'A' record_type

INTO ('database_name.dbo.permissions_table_audit

FROM #r

–detect additons

SELECT *,GETDATE() date_captured

INTO #additions

FROM

(

SELECT * FROM #r

EXCEPT

SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path

FROM ('database_name.dbo.permissions_table

) a

INSERT INTO ('database_name.dbo.permissions_table_audit

SELECT *,'A' FROM #additions

INSERT INTO ('database_name.dbo.permissions_table

SELECT * FROM #additions

–detect deletions

SELECT *,GETDATE() date_captured

INTO #deletions

FROM

(

SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path

FROM ('database_name.dbo.permissions_table

EXCEPT

SELECT * FROM #r

) a

INSERT INTO ('database_name.dbo.permissions_table_audit

SELECT *,'D' FROM #deletions

DELETE FROM ('database_name.dbo.permissions_table

FROM ('database_name.dbo.permissions_table

INNER JOIN (

SELECT * FROM

(SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path FROM #deletions

INTERSECT

SELECT account_name ,

type_desc ,

privilege ,

mapped_login_name ,

permission_path FROM ('database_name.dbo.permissions_table) a ) a

ON a.account_name = permissions_table.account_name AND a.permission_path = permissions_table.permission_path

sample winscp script

June 22, 2017
option batch on
option confirm off
open USERNAME:PASSWORD@SERVER
option transfer binary
get *part_of_file_name* -filemask=>24h “output_path”
close
exit
* is a wildcard
output path must have the trailing \
to call:
“file_path\WinSCP.com” /script=”file_path”
no spaces apart from potentially in filepath and before /script

Formatting HTML for send_dbmail proc

March 2, 2017

taken from: http://stackoverflow.com/questions/7070053/convert-a-sql-query-result-table-to-an-html-table-for-email

Procedure

-- Description: Turns a query into a formatted HTML table. Useful for emails. 
-- Any ORDER BY clause needs to be passed in the separate ORDER BY parameter.
-- =============================================
CREATE PROC [dbo].[spQueryToHtmlTable] 
(
  @query nvarchar(MAX), --A query to turn into HTML format. It should not include an ORDER BY clause.
  @orderBy nvarchar(MAX) = NULL, --An optional ORDER BY clause. It should contain the words 'ORDER BY'.
  @html nvarchar(MAX) = NULL OUTPUT --The HTML output of the procedure.
)
AS
BEGIN   
  SET NOCOUNT ON;

  IF @orderBy IS NULL BEGIN
    SET @orderBy = ''  
  END

  SET @orderBy = REPLACE(@orderBy, '''', '''''');

  DECLARE @realQuery nvarchar(MAX) = '
    DECLARE @headerRow nvarchar(MAX);
    DECLARE @cols nvarchar(MAX);    

    SELECT * INTO #dynSql FROM (' + @query + ') sub;

    SELECT @cols = COALESCE(@cols + '', '''''''', '', '''') + ''['' + name + ''] AS ''''td''''''
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @cols = ''SET @html = CAST(( SELECT '' + @cols + '' FROM #dynSql ' + @orderBy + ' FOR XML PATH(''''tr''''), ELEMENTS XSINIL) AS nvarchar(max))''    

    EXEC sys.sp_executesql @cols, N''@html nvarchar(MAX) OUTPUT'', @html=@html OUTPUT

    SELECT @headerRow = COALESCE(@headerRow + '''', '''') + ''<th>'' + name + ''</th>'' 
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#dynSql'')
    ORDER BY column_id;

    SET @headerRow = ''<tr>'' + @headerRow + ''</tr>'';

    SET @html = ''<table border="1">'' + @headerRow + @html + ''</table>'';    
    ';

  EXEC sys.sp_executesql @realQuery, N'@html nvarchar(MAX) OUTPUT', @html=@html OUTPUT
END
GO

Usage

DECLARE @html nvarchar(MAX);
EXEC spQueryToHtmlTable @html = @html OUTPUT,  @query = N'SELECT * FROM dbo.People', @orderBy = N'ORDER BY FirstName';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Foo',
    @recipients = 'bar@baz.com;',
    @subject = 'HTML email',
    @body = @html,
    @body_format = 'HTML',
    @query_no_truncate = 1,
    @attach_query_result_as_file = 0;

 

 

Unable to start up Server Instance

February 6, 2017

So I had a little panic situation earlier today however it is now resolved and this is how I resolved it.

The Preprod instance was down and I could not get it back up again.

When I tried to start the instance I received an error message telling me that “The request failed or the service did not respond in a timely fashion”

I checked event viewer, but there nothing helpful there “The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.”

I then tried to start the service using services.msc again no good “This service on local computer started and then stopped. some service stop automatically if then are not in use by other service or programs ”

I then checked the log file “E:\Microsoft SQL Server\MSSQL11.PREPROD\MSSQL\Log\ERRORLOG” Also unhelpful “Failed allocate pages: FAIL_PAGE_ALLOCATION 1”

Finally I rememeber changing the memory setting on preprod earlier today and allocating all it’s to memory to the default instance and so I used the startup parameter -f , which starts the instance up in minimal config mode, to quote books online “This is useful if the setting of a configuration value (for example, over-committing memory) has prevented the server from starting. Starting SQL Server in minimal configuration mode places SQL Server in single-user mode.” This was successful I got the instance up and running in min config mode :>D

I now logged in on the DAC connection and ran EXEC sys.sp_configure. max server memory (MB) had a config value of 128 and a running value of 2147483647.

I ran the following code
EXEC sys.sp_configure N’max server memory (MB)’, N’10000′
GO
RECONFIGURE WITH OVERRIDE
GO

we now had a config value of 10000 and a run value of 10000

I stopped the server, removed the minimal config startup parameter and restarted the service. Success.

Looking through the logs there is no record from when I changed the maximum memory value earlier in the day so I assume something must have gone wrong and it took a stupidly large value in error which caused the troubles.

Anyway it is now fixed. Yay.

 

Change the SQL management studio program name

January 9, 2017

At connection time in the additional connection parameters box as an example specify

application name = testing 123

 

 

get the contents of a file system

November 28, 2016

create PROC procname

@drive CHAR(1)
AS
SET NOCOUNT ON

DECLARE @dynamic_sql VARCHAR(1000)

SET @dynamic_sql = CONCAT(‘EXEC xp_cmdshell ”dir ‘,@drive,’:\ /a /s”’)
PRINT @dynamic_sql

CREATE TABLE #t (id INT IDENTITY(1,1), output VARCHAR(MAX))

INSERT INTO #t
EXEC (@dynamic_sql)

DELETE FROM #t WHERE output IS NULL
DELETE FROM #t WHERE output LIKE ‘%<DIR>%’
DELETE FROM #t WHERE output LIKE ‘%file%bytes’
DELETE FROM #t WHERE output LIKE ‘%dir%bytes’
DELETE FROM #t WHERE output LIKE ‘%dir%bytes’
DELETE FROM #t WHERE output =’ Total Files Listed:’
DELETE FROM #t WHERE output LIKE ‘%Volume in drive%’
DELETE FROM #t WHERE output LIKE ‘%Volume Serial Number%’
DELETE FROM #t WHERE output LIKE ‘%Dir%bytes free’
DELETE FROM #t WHERE output LIKE ‘%The directory name%’
DELETE FROM #t WHERE output LIKE ‘%is too long%’
DELETE FROM #t WHERE output LIKE ‘%<JUNCTION>%’
DELETE FROM #t WHERE output LIKE ‘%<SYMLINKD>%’
ALTER TABLE #t ADD data_type int

UPDATE #t SET data_type = 0 WHERE output LIKE ‘ Directory%’
UPDATE #t SET data_type = 1 WHERE data_type IS NULL

UPDATE #t SET output = LTRIM(SUBSTRING(output,18,1000)) FROM #t WHERE data_type = 1

ALTER TABLE #t ADD first_space INT

UPDATE #t SET first_space = CHARINDEX(‘ ‘,output) FROM #t WHERE data_type = 1

DELETE FROM #t WHERE first_space = 0 AND data_type = 1

ALTER TABLE #t ADD size BIGINT,filename VARCHAR(1000)
UPDATE #t SET SIZE = REPLACE(SUBSTRING(output,1,first_space-1),’,’,”),filename = SUBSTRING(output,first_space+1,1000) FROM #t WHERE data_type =1

CREATE clustered INDEX i ON #t (id)

ALTER TABLE #t ADD directory VARCHAR(1000)

DECLARE @row BIGINT = 1,@max BIGINT

SELECT @max = MAX(id) FROM #t

WHILE @row <= @max
BEGIN

DECLARE @data_type INT, @outputvalue VARCHAR(1000), @directory VARCHAR(1000)

SELECT @data_type = data_type,@outputvalue = output FROM #t WHERE id = @row

IF @data_type = 0 SET @directory = @outputvalue

IF @data_type = 1
UPDATE #t SET directory = REPLACE(@directory,’ Directory of ‘,”) FROM #t WHERE id = @row

SET @row +=1
end

DELETE FROM #t WHERE data_type = 0

IF (SELECT OBJECT_ID(‘table’)) IS NULL
EXEC (‘SELECT TOP 0 filename,directory,size,GETDATE() capture_time INTO table FROM #t ‘)

INSERT INTO table
SELECT filename,directory,size,GETDATE() capture_time FROM #t ORDER BY size DESC