Monthly Archives: March 2013

SQL Server xp_dirtree – Parse Directory for File names

Posted on by

I needed a way to parse the filenames in a directory and plug them into a RESTORE command. The xp_dirtree works really well for this.


IF OBJECT_ID('tempdb..#restoreFiles') IS NOT NULL
 DROP TABLE #restoreFiles

 CREATE TABLE #restoreFiles
 (
 [filename] VARCHAR(2000),
 [depth] INT,
 [file] INT
 )

INSERT INTO #restoreFiles
 EXECUTE xp_dirtree 'D:\backups',1,1

From there I was able to set a variable based off a WHERE clause. My FULL backups are pre-pended with the word FULL so I can plug that into my RESTORE command.

SET QUOTED_IDENTIFIER OFF
DECLARE @FullBackupFile VARCHAR(2000) = (SELECT TOP 1 [filename] FROM #restoreFiles WHERE [filename] like 'FULL%' ORDER BY [filename] DESC)

PRINT ('RESTORE DATABASE DbName
 FROM DISK = "D:\Backups\' + @FullBackupFile + '"
 WITH MOVE "DataFile" TO "D:\MSSQL\Data\Data.mdf",
 MOVE "LogFile" TO "D:\MSSQL\Data\log.ldf",
 NORECOVERY,
 REPLACE,
 STATS = 10
 GO')

Export CSV using BCP SQL Agent Job

Posted on by

Ever need to export a CSV from SQL Server in  a friendly easy to consume format? Typically when I try to export a CSV I have to jump through some hoops. I stumbled on this pretty straightforward method to export a CSV using BCP. The result will be a CSV file with a header row emailed to the user. One caveat is that xp_cmdshell must be enabled.


USE [DBName]
CREATE TABLE ##TempTable
(
col1 VARCHAR(20),
col2 VARCHAR(100),
)

INSERT INTO ##TempTable
SELECT col1, col2
FROM Table1

--First we create a header file, then we create the data
--file using a select on our temp table. Finally we run a
--copy command to mash em together.
EXEC xp_cmdshell 'bcp "select ''Col1'',''Col2''" queryout c:\Scripts\header.csv -c -t, -T -E -S SERVERNAME'
EXEC xp_cmdshell 'bcp "select * from ##TempTable" queryout c:\Scripts\body.csv -c -t, -T -E -S SERVERNAME'
EXEC xp_cmdshell 'copy /b c:\Scripts\header.csv+c:\Scripts\body.csv c:\Scripts\report.csv'

--Email the mashed up file
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Profile1',
@recipients = 'dude@cool.com',
@body = 'See attached report',
@subject = 'Sweet Subject Goes here',
@file_attachments = 'c:\scripts\yourfile.csv'

DROP TABLE ##TempTable

 

 

Syntax Highlighter using Pygments and Python

Posted on by

I was using this web site to highlight and format my SQL code to HTML to insert into our wiki at work on Sharepoint, when all of a sudden the website stopped working. After some searching I discovered the site was using Pygments, a Syntax Highlighting Library for Python. So I decided to try and roll my own solution. This article will detail the steps I went through to make it happen.

Download

Install

  • Installed Python 2.7 with the defaults which creates a folder called C:\Python27
  • Installed Easy Setup using the EXE taking the defaults
  • Installed Pygments by hitting a console window and running this from the C:\Python27\Scripts folder. easy_install Pygments, more info here: http://pygments.org/download/

Configure

Code

  • First I setup a basic HTML page with a simple form.The page had a single form with a TEXTAREA named code.
    • 
<html>
<head>
</head>
<body>
<form method='post' action='codedump.py'>
<TEXTAREA cols=100 rows=25 name='code'></TEXTAREA>
<br>
<select name='type'>
<option value='SqlLexer'>SQL</option>
<option value='AutoHotkeyLexer'>Autohotkey</option>
<option value='BatchLexer'>Batch</option>
<option value='PowerShellLexer'>PowerShell</option>
<option value='CSharpLexer'>CSharp</option>
<option value='VbNetLexer'>VbNet</option>
</select>
<input type='submit'>
</form>
</body>
</html>
  • Second I pieced a codedump.py Python file together using code snippets from the Pygments web site, StackOvervlow, and Python help files.
#!/usr/bin/env python
# -*- coding: UTF-8 -*-

import cgi
# enable debugging
import cgitb
cgitb.enable()

#print "Content-Type: text/plain;charset=utf-8"
print


from pygments import highlight
from pygments.lexers import SqlLexer
from pygments.formatters import HtmlFormatter
from pygments.styles import get_all_styles


form = cgi.FieldStorage()
code = form["code"].value

#code = 'SELECT * from dbo.Customers'
print "<HTML>"
print "<TEXTAREA cols=100 rows=20>"
print "<div style='border-width: 1px 1px 1px 8px; border-style: solid; border-color: #555; padding-left: 10px;'>"
print highlight(code, SqlLexer(), HtmlFormatter(noclasses=True,style='default'))
print "</div>"
print "</TEXTAREA>"
print "</HTML>"
#print HtmlFormatter().get_style_defs('.highlight')


print highlight(code,SqlLexer(),HtmlFormatter(noclasses=True,style='default'))


#print list(get_all_styles())
  • SqlLexer is hardcoded here meaning that we can only convert SQL code with this page.
  • Working on allowing for other languages
  • Pygments Formatter has several options, this code is only using the noclasses and style options.
  • There are several builtin Styles and you can create your own styles.

Next Steps

  • Allow for different types of code
  • Create a custom style (don’t like the default background color)