In order to automate testing of backups it’s useful to be able to query backup files to access various bits of meta-data. We can do this with the RESTORE FILELISTONLY TSQL command. In the simplest format the command is as follows; ?View Code TSQLRESTORE FILELISTONLY FROM DISK = ‘c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Backup\AdventureWorks_20110320.bak’; This doesn’t help [...]
I was recently given a du-duping task which was much more difficult than I anticipated and taxed my SQL brain to its limits. I thought of using a CTE to do this but all of the examples I could find for deleting records with a CTE wouldn’t have worked in my situation. Essentially the table [...]
I’ve always been aware of SQLCMD mode in SQL Server Management Studio but until a few days ago I never considered using it. So what is SQLCMD? SQLCMD is a command line application that comes with Microsoft SQL Server, and exposes the management features of SQL Server. It allows SQL queries to be written and [...]
This error message really irritates me. Msg 8152, Level 16, State 2, Line 1 String or binary data would be truncated. The statement has been terminated. I should probably open a Microsoft Connect item about this but would it really be that hard to tell you the column name? When you’re importing a data from [...]
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 [...]
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 [...]
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 [...]
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 [...]
