Tuesday, 27 January 2009

OLE Automation : Create file folder from TSQL

Create a folder on the file system from TSQL via OLE.

Usage :
DECLARE @destinationpath NVARCHAR(1000)
SET @destinationpath = N'C:\Sql Scripts\' + @@SERVERNAME
exec Utils.usp_OLECreateFolder @newfolder = @destinationpath

Proc :
CREATE PROCEDURE Utils.usp_OLECreateFolder (@newfolder varchar(1000)) AS
DECLARE @OLEfilesytemobject INT
DECLARE @OLEdescription  VARCHAR(255)

-- create file system object (will fail if OLE automation not enabled)
EXEC @OLEresult=sp_OACreate 'Scripting.FileSystemObject', @OLEfilesytemobject OUT
IF @OLEresult <> 0
EXEC sp_OAGetErrorInfo @OLEfilesytemobject
-- check if folder exists
EXEC @OLEresult=sp_OAMethod @OLEfilesytemobject, 'FolderExists', @OLEfolder OUT, @newfolder
-- if folder doesnt exist, create it
IF @OLEfolder=0
EXEC @OLEresult=sp_OAMethod @OLEfilesytemobject, 'CreateFolder', @OLEfolder OUT, @newfolder
-- if error has occured, report it!  
IF @OLEresult <> 0
EXEC sp_OAGetErrorInfo @OLEfilesytemobject, @OLEsource OUT, @OLEdescription OUT
SELECT @OLEdescription='Could not create folder: ' + @OLEdescription
RAISERROR (@OLEdescription, 16, 1) 
EXECUTE @OLEResult = sp_OADestroy @OLEfilesytemobject

Note : Remember to enable OLE Automation for this to work.
Also bear in mind that the service account SQL is running under will need the correct permissions to be able to create a folder!

Enabling OLE Automation via TSQL

How to enable OLE Automation via TSQL
sp_configure 'show advanced options', 1;
sp_configure 'Ole Automation Procedures', 1;
sp_configure ‘Agent XPs’, 1;
sp_configure 'show advanced options', 1;
I had to re-enable 'Agent XPs (hence that step above) as enabling OLE somehow disabled it :(

OLE Automation : Write a text file from TSQL

Create a text file on the file system from TSQL via OLE.

Usage :
DECLARE @destinationpath NVARCHAR(1000)
DECLARE @destinationfilename NVARCHAR(1000)
SET @destinationpath = N'C:\SQL Scripts' 
SET @destinationfilename = @destinationpath + '\' + N'filename.txt'

exec utils.usp_OLEwritefile  @FileName =@destinationfilename
, @TextData ='blah! blah! blah!'
, @FileAction = 'APPEND' -- CREATENEW or APPEND (to allow appends if the file exists, both options create the file if it doesn't).

Proc :
CREATE PROCEDURE [Utils].[usp_OLEWriteFile] (@FileName varchar(1000), @TextData NVARCHAR(MAX),@FileAction VARCHAR(12)) AS

DECLARE @OLEfilesytemobject INT

sp_OACreate 'Scripting.FileSystemObject', @OLEfilesytemobject OUT
IF @OLEResult <> 0 
PRINT 'Error: Scripting.FileSystemObject'

-- check if file exists
EXEC sp_OAMethod @OLEfilesytemobject, 'FileExists', @OLEresult OUT, @FileName 
-- if file esists
IF (@OLEresult=1 AND @FileAction = 'APPEND') OR (@OLEresult=0) 

IF (@FileAction = 'CREATENEW')
PRINT 'New file specified, creating...'
IF (@OLEresult=1 AND @FileAction = 'APPEND') 
PRINT 'File exists, appending...'
IF (@OLEresult=0 AND @FileAction = 'APPEND') 
PRINT 'File doesnt exist, creating...' 

-- open file
EXECUTE @OLEResult = sp_OAMethod @OLEfilesytemobject, 'OpenTextFile', @FileID OUT,
@FileName, 8, 1
IF @OLEResult <>0 PRINT 'Error: OpenTextFile'

-- write Text1 to the file
EXECUTE @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @TextData
IF @OLEResult <> 0 
PRINT 'Error : WriteLine'
PRINT 'Success' 
IF (@OLEresult=1 AND @FileAction = 'CREATENEW')
PRINT 'File Exists, specify APPEND if this is the desired action'

EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @OLEfilesytemobject


Note : Remember to enable OLE Automation for this to work.
Also bear in mind that the service account SQL is running under will need the correct permissions to be able to write a file in the specified location.
See also Creating a file folder from TSQL

Monday, 26 January 2009

SQL 2008 : Scripting All Database Objects

Unchanged from SQL 2005, but I've never has cause to reverse generate files for a source code repository before...

1) Right click database > Tasks > Generate Scripts >

2) Wizard front screen >

3) Select Database >

4) Script Options (defaults left here) >

5) Output file options.

Note the 'File per object' button I've selected here >

6) Summary screen (review options if you wish, then click next) >

7) Watch progress (or get a coffee if the db is a large one) >

8) The output! A folder full of files!

TSQL : List Columns and Data types for a given table

select tables.name , columns.column_id, columns.name,types.name
from sys.tables tables
inner join sys.columns columns on tables.object_id = columns.object_id
inner join sys.types types on columns.user_type_id = types.user_type_id
where tables.name= 'jts_event'
order by columns.column_id

Friday, 16 January 2009

Database Skills Checklist

So what does a DBA / Database Administrator do? A skills checklist...

Database Administration Skills

All tasks can be done via Management studio UI & Wizards as well as TSQL,
Operating Systems checks can be performed (& automated) using VB Script

SQL Server installation
  • SQL Server components (SSMS, Profiler, Query Analyzer, SSAS, SSRS, SSIS)
  • SQL Server Versions (7,2000,2005,2008) & Editions (Dev, Standard, Enterprise)
  • SQL Server requirements (hardware, OS, network protocols etc)
  • Systems (Hardware, OS Installation, Networking, Security, Domains)
  • Storage (Raid Levels, Disk types)
SQL Server Maintenance
  • SQL Service Packs & Cummulative Updates
  • OS Patches & Security Updates
  • Database Mail
  • Linked Servers
  • MDW (Management Data Warehouse)
  • Resource Governor
  •  ...

  • Recovery models, backup / restore tools and SQL statements for backup / restore,
  • Appropriate choice of recovery model
  • Perform Backups at appropriate Intervals and Schedule them!
  • Perform test Restores of the Backups!
  • Take Backups offsite at regular intervals
  • Have a documented DR (disaster recovery) plan
Monitoring (System Health)
  • Activity - memory, blocking / locking, caching, disk usage, transactions, wait types, execution statistics
  • Change Control - Database Objects
  • Growth - dbs & tables
  • Jobs - Import, Data Processing, System Health
  • Query Performance
  • Logins - NT / SQL
  • Authentication modes - Basic / NTLM / Kerberos
  • Roles - Server / Database / Application
  • Permissions - Server / Database / Schema / Object
  • Keep it simple!
Database Maintenance
  • Activity Monitor
  • Performance Monitor
  • SQL Profiler (trace)
  • TSQL Commands - sp_who, sp_kill
High Availability Technologies
  • Replication
  • Clustering
  • Mirroring
  • Log Shipping
Performance Tuning
  • Server Config
  • Database Options
  • Session Options
  • TSQL Code - Execution Plans, table and index choices
Automate & Innovate
  • Recurring tasks
  • Reporting
  • Record Statistics (disk usage etc)
  • Predict trends
  • Identify projects yourself
DBAs - see also '10 Ultimate Rules for Effective System Administration'

Database Development skills

Data Modeling / Database Design
  • Logical Modeling
  • Physical Modeling (3NF)
  • Modeling tool (Visio, Erwin, can even generate Basic Diagrams in Management Studio)
Database Development
  • TSQL (Transact SQL) for RDBMS Development
  • CLR (Common Language Runtime) for .NET Integration
  • SSIS (Integration Services) for ETL Development
  • SSAS (Analysis Services) for Cube Development
Application Design
  • Business logic location - Application?, Stored Procedures?, Triggers?, Scheduled Jobs?

Non Technical Essential Skills

Soft Skills
  • Communication (Verbal & Written)
  • Leadership
  • Negotiation
  • Project Management
  • Time management
  • Configurations
  • Decisions - Record the reasons behind anything important (design changes etc)
  • Plans
  • Projects

Thursday, 8 January 2009

Google Mail for Applications

Have transferred multiple domains recently to the excellent free email service.
Things I've found >

There will be a temporary email address to forward email to, e.g.
This will mean you can forward emails from exchange to google and they will be there waiting when the domain switches over.

Gmail’s excellent ‘Plus Addressing’ system works on Google Apps accounts and can be used with their filtering system.
It is explained in the link below, but the short of it is you can use a ‘+’ sign to create email variants and control spam.
They all go to your email but you can filter on the ‘to’ address.

For example me@mydomain.com (me)
me+facebook@mydomain.com (address i give for facebook)
me+amazon@mydomain.com (ditto amazon)

Then, when you start getting Viagra adverts, you know who has passed on your email address!


Monday, 5 January 2009

sp_monitor Error

sp_monitor returns information on cpu, network, disk activity and connections.
Sometimes when sp_monitor is run (EXEC sp_monitor;), it errors!

"Arithmetic overflow error converting expression to data type int."

Given it is an internal sql function, it's a bit bizarre.

This chap has a theory...

"I think this might be due to the fact that sp_monitor displays the interval between runs in seconds and if there has been too long a period between runs the amount of seconds is too large to be stored as an int."


Serial Operations Error

" Executed as user: Domain\SQLServiceAgent. DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. "

The error message is pretty self explanatory. You have to wait for the first ALTER DATABASE statement to complete before executing another. In our case a scheduled job tried to execute at the same time as currently running one and caused this, after the former took an extraordinaryly long time to complete.


SQL 2005 access via TDS from Linux

Came across a weird situation today...

The situation :
A Stored Procedure i've written which references global Temporary tables (populated using dynamic sql)

The scenario :
Procedure ran fine and returned results from Management Studio when logged in as my login (sysadmin).
Procedure ran fine and returned results from Management Studio when logged in as 'sa'.
Procedure ran fine and returned results from Management Studio when logged in as application user.

Failed when run from Linux TSQL interface with the error >

" Msg 515, Level 16, State 2, Server SERVERNAME, Line 1
Cannot insert the value NULL into column 'MaxFileSizeinMb', table 'tempdb.dbo.##Results'; column does not allow nulls. INSERT fails. "

2 ways to fix >

1) Explicitly declare column in temp table as allowing NULL (bizarre but it worked) >
( DataFile VARCHAR,
MaxFileSizeinMB INT NULL
DataSizeinMB INT)

or (the systems team solution we've implemented)

2) Make sure the right drivers are being used!

In this case, SQL 2005 is being accessed from linux via FreeTDS

(TDS = Tabular Data Stream)

The version of TDS being used was incorrect
TDS versions refer to the SQL Server build number, therefore >
7.0 for Sql 7
8.0 for Sql 2000
9.0 for Sql 2005

To verify the settings >
Open /etc/freetds.conf
Locate the [global] section.
Find the line that says tds version = 4.2
Change to tds version = 9.0
Restart Apache > service httpd restart

Links :

FreeTDS http://www.freetds.org/
FreeTDS Versions http://www.freetds.org/tds.html
TSQL http://linux.die.net/man/1/tsql

Gartner Magic Quadrant

Start the year on a high...

Microsoft in the leaders section of the 2008 Gartner Magic Quadrant for Data Warehouse Database Management Systems


Found via :

Database Statuses

select name as DatabaseName,databasepropertyex(name,'Status') as DatabaseStatus
from sys.databases
where databasepropertyex(name,'Status') <> 'Online'

Possible Statuses >
OFFLINE (Manually taken offline)
RESTORING (Self explanatory)
RECOVERING (Self explanatory)
SUSPECT (Recovery failed)
EMERGENCY (Sysadmin access only, data is read only)


I/O Delays

" SQL Server has encountered n occurrence(s) of I/O requests taking longer than 15 seconds to complete on file d:\path\datafile.mdf "

This is SQL's way of saying the I/O Subsystem is not coping with the data throughput.

" When you see this message the first action should still be to have a look at the physical disk counters in sysmon to ensure that the disks are servicing IOs in a reasonable period of time. If those appear to fine then start looking at what filter drivers might be installed on your system, and if there are any known issues with them, or disable them if you don’t need them. "


Saturday, 3 January 2009

Agent Login Errors

Symptoms :
Agent Login Errors on a DPM 2007 box
4 errors, in this sequence, throughout the logs >

Date 03/01/2009 01:12:54
Log SQL Agent (Current - 11/25/2008 12:25:00 PM)
[298] SQLServer Error: 258, TCP Provider: Timeout error [258]. [SQLSTATE 08001]

Date 03/01/2009 01:12:54
Log SQL Agent (Current - 11/25/2008 12:25:00 PM)
[165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]

Date 03/01/2009 01:12:54
Log SQL Agent (Current - 11/25/2008 12:25:00 PM)
[298] SQLServer Error: 258, Unable to complete login process due to delay in prelogin response [SQLSTATE 08001]

Date 03/01/2009 01:12:54
Log SQL Agent (Archive #2 - 05/01/2009 09:50:00)
[382] Logon to server SERVERNAME\MS$DPM2007$' failed (SaveAllSchedules)

Actions Taken :
Disabling & Re-enabling TCP/IP & restarting SQL. Will monitor.


Thursday, 1 January 2009

SQL 2008 : Activity Monitor

SQL 2008's Activity Monitor looks a lot like Windows 2008's Resource Monitor.

To access it, right-click a server name and select 'Activity Monitor' (yes this has changed since previous versions).