Recently I needed to setup a SQL Server box so it had access to a mapped drive to support a legacy application. I created the below stored procedure, which utilises the subst command. to get this done.

?View Code TSQLCREATE PROCEDURE usp_mapDDrive
AS
BEGIN
EXEC master.dbo.xp_cmdshell ‘Subst d: c:\’, no_output;
END
GO

I needed a way of ensuring this mapped drive was [...]

There’s few SQL techniques you seem to keep in the cupboard gathering dust. I don’t think I’ve ever needed to use RIGHT JOIN outside of the classroom. I can recall using FULL OUTER JOIN, just once, to show an employer how not-in-sync their "integrated system" was. Today I broke my professional Non-Equi JOIN cherry!
I basically [...]

I’ve blogged before about documenting databases. I’m very much a fan of extracting documentation from systems themselves so it’s as up-to-date as it can be. That’s probably why I’m such a big fan of Powershell a tool that excels at this task. This week I was thinking about how to get at the comments often [...]

If you ever encounter this SQL Server error when selecting from a view then somebody has probably dropped columns from the base table. Here’s a quick run through of the problem.

?View Code TSQLCREATE TABLE dbo.Contact
(
Id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
DOB DATETIME NOT NULL,
Phone VARCHAR(30) NULL,
Email VARCHAR(200) NULL,
Mobile [...]

Today I received the following SQL Server error which I had never encountered before.
Msg 4429, Level 16, State 1, Line 1 View or function ‘Table_1′ contains a self-reference. Views or functions cannot reference themselves directly or indirectly. Msg 4413, Level 16, State 1, Line [...]

This week I was tasked with testing a stored procedure that was meant to output data on certain days. This was over a 120 day period, so I wanted to find some automated way of doing this, rather than changing the server date manually for each execution. The method I came up with involves the [...]

Sometimes, when working with extracts of data, it can be a pain to have to load these files into a database in order to work with them. It’s easy to use OPENROWSET to save yourself a little time. Here’s a basic example;

?View Code TSQLSELECT *
FROM OPENROWSET
(’MSDASQL’,
‘Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\Users\Rhys\Desktop\csv;Extended properties=”ColNameHeader=True;Format=Delimited;”’,
‘SELECT * FROM file1.csv’);

The [...]

I’ve recently been busy documentation various systems at work and came up with these queries to get a list of databases and their sizes for each SQL Server. These queries will show the server name, database names, and their sizes in KB, MB and GB.
For SQL Server 2005 & 2008

?View Code TSQL– SQL Server 2005 [...]

Today I needed to order some data by specific column value and I recalled the really handy FIELD function in MySQL. Here’s a demo of this feature in MySQL

?View Code MYSQL# Test table
CREATE TABLE City
(
Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
City VARCHAR(50)
);
 
# Insert some test data
INSERT INTO City
(
City
)
VALUES
(
‘London’
),
(
‘Hong Kong’
),
(
‘New York’
),
(
‘Bangkok’
),
(
‘Los Angeles’
),
(
‘Paris’
),
(
‘Singapore’
),
(
‘Moscow’
),
(
‘Lagos’
),
(
‘Johannesburg’
);

Using the FIELD function we [...]

So exactly what is a computed column? MSDN has this to say
A computed column is computed from an expression that can use other columns in the same table. The expression can be a noncomputed column name, constant, function, and any combination of these connected by one or more operators. The expression cannot be a subquery.

Essentially [...]