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 [...]
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 [...]
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 [...]
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 [...]
Steve Novoselac posted a good article about using CROSS APPLY with TSQL. This is a really useful technique for transforming data into grouped lists. It made me think of a similar feature in MySQL, a function, called GROUP_CONCAT. Here’s a demonstration of CROSS APPLY with TSQL and GROUP_CONCAT in MySQL to achieve the same thing. [...]
I do a fair bit of work with Linked Servers and cross-database queries and sometimes come across the following error when joining between databases with different collations;
Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between ‘Latin1_General_CI_AS’ and ‘SQL_Latin1_General_Pref_CP850_CI_AS’; in the equal to operation.
To replicate this error run the below TSQL to [...]
If you ever need to call a MySQL procedure from SQL Server it’s fairly simple thanks to ODBC and Linked Servers. This will allow you to reuse any logic already invested in MySQL Stored Procedures saving you from rewriting them. Here’s a simple example on how you can do it;
Create the following procedure in your [...]
Not many people seem to be aware of the WITH TIES clause introduced in SQL Server 2005+. This simple feature is worth adding to your query armoury. In the words of BOL WITH TIES…
“Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as [...]
