Wednesday, December 18, 2013

The best laid plans of mice and men...

It would be nice if people didn't do this...

Tuesday, October 29, 2013


I've found two minor quirks within SQL Server today that, if I had hair, would have caused me to tear it (the hair) out.

1. SQL returns "*" when casting/converting an integer to data type varchar, and the data length is too short. Two two lines of code will show what I mean:

SELECT CAST(123 AS VARCHAR(3)) -- Returns 123
SELECT CAST(123 AS VARCHAR(2)) -- Returns *
This caused be issues because I (unfortunately) have to create a massive dynamic SQL statement to do some ridiculous data transformation from a flat file data source to an XLS file to importing into another database. Yes, I understand the best way to do it would be to insert directly into the necessary tables, however the vendor doesn't want us doing that.

2. You need to have the same number of columns numbered in your Excel spreadsheet destination as you  are selecting from the database.

As I mentioned above, I need to output data to an XLS file, and each column that I am placing data in has to be numbered. So if I select 61 columns of data from SQL, there has to be columns numbered 1 to 61 on the first row of the spreadsheet. 60 or 62 causes errors.

Tuesday, August 13, 2013

Using BCP to export files stored as image from SQL Server

At the moment, I'm working on retrieving files stored in a Sharepoint database and placing them in a file share, because we're no longer using that particular Sharepoint site, and the size of the database is larger than just having the images stored on disk.

One option was to use WebDAV, but that can be quite cumbersome, and it also means that the files are created temporarily (so that they exist to be copied), and then they're copied to the location they need to go to. We can avoid the middle man and send them straight from SQL Server to the file share location using BCP.

For those who don't know what BCP is, have a look at this TechNet article.

Roughly outlined, we'll be doing the following:

1. Generate a format file (used by bcp to determine how to render the data in the file)
2. Enable xp_cmdshell access
3. Write your query to retrieve the file data from the database
4. Combine the two and use bcp to export that data to a file share

1. Generate a format file

You can do this on any machine that has SQL Server installed. Open a command prompt and run this command (substituting your parameters as necessary)

C:\Documents and Settings\User>bcp DatabaseName.dbo.TableName format nul -f C:\FileLocation\format.fmt -T -S servername\instancename

The first parameter is either the table name or the query (I just use the table name). Second is the direction (these can be in, out, query or format nul), third is the format file switch (-f) and location for the file, fourth is user authentication switch (-T or -U & -P), and finally the server name (and instance name if required) -S.

When this runs, depending on how many columns you have, you'll see these three lines:

Enter the file storage type of field Id [uniqueidentifier]:
Enter prefix-length of field Id [1]:
Enter field terminator [none]:

When I create a format file, I press enter for the first two options (leaving them as their default), and then use a pipe, |, as the field terminator. Repeat for all columns.

Once this is done, the format file will be created in the location you specify. For ease of keeping everything together, I place the format file in the location I'm exporting the files in the database to.

2. Enable xp_cmdshell access

Note: xp_cmdshell is disabled for security reasons by default in SQL Server, so as soon as you're finished with it, disable it. To enable it, run this:
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'xp_cmdshell', 1

And to disable it when you're finished, run this:

EXEC sp_configure 'xp_cmdshell', 0
EXEC sp_configure 'show advanced options', 0

3. & 4. Write your SQL query & Combine with bcp

This I'll have to leave to you, but to give you an idea of what I've done to export files from a database, here's the query I've written:


 RowNumber BETWEEN 1 AND 1000

OPEN CurPhoto

FETCH NEXT FROM CurPhoto INTO @ID, @DirName, @FileName;

 SET @Sql = 'bcp "SELECT TOP 1 CAST([Content] AS VARBINARY(MAX)) AS [Content] FROM Pictures.dbo.AllDocStreams WHERE Id = ''' + @ID + '''" queryout "F:\Pictures\' + RTRIM(@DirName) + '\' + LTRIM(@FileName) + '" -T -f F:\Pictures\format.fmt -S servername\instancename'
 EXEC master.dbo.xp_cmdshell @Sql

 FETCH NEXT FROM CurPhoto INTO @ID, @DirName, @FileName

CLOSE CurPhoto

I've used a cursor for this (where I manually change the row number range) because I needed to export 41GB of image files, and SSMS started giving out of memory errors when I exported more than 7GB of data (while running on my machine). I could probably have done it in larger batches, but there were only 20 batches of 1000, and it only took 5 minutes per batch to create at little over 2GB of files each time.

When you're exporting from SQL to a data file, I found things worked properly when I converted from IMAGE to VARBINARY(MAX) data type. I haven't looked into WHY that is yet, but that's what worked for me.

The above query wasn't particularly resource intensive, and other sites running on the server experienced performance issues, so if you absolutely need to, you could run it during business hours, but I would recommend doing it outside of business hours anyway.

Thursday, December 13, 2012

Moving backwards: Moving a database from SQL Server 2008 R2 to SQL Server 2005

I came across a strange scenario today. The vSphere service running on one of our SQL servers (I know, I know... terrible) was getting constant ASYNC_NETWORK_IO waits, and the only solution was to restart the service, but after a little time, wait times would increase again.

It turns out it was a CPU resource allocation issue, whereby the cores allocated to the particular virtual machine were insufficient, as well as being an issue with the SAN.

In an effort to rule out any and all database possibilities (which I had a feeling there were none in terms of SQL Server problems), I decided to move the database onto a server with very little activity on it (and the only one of our production servers that isn't mission critical). The issue with that was that it's a SQL Server 2005 server, and the current database sits on a SQL Server 2008 R2 machine. As we all know, you can't regress in SQL.

I've found a long, but reliable way around this. In broad terms but point format, perform the following:

  1. Generate scripts for all databases concerned, making sure to script logins, bindings, collation and object permissions.
  2. Run scripts on new server.
  3. Before importing any data, run the following:
  4. EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
  5. In SSMS, right click on the source database, goto Tasks/Export Data. Select your source and destination server, the appropriate login details, Copy Data... option, and then click on the check box in the top left corner of the list of tables to select all tables (scroll down the list to uncheck any views - you don't want to copy those because they're already scripted).
  6. Click on the Edit Mappings button and select "Enable Identity Insert".
  7. Click Next a few times, and let it do its thing.
After this, all your data will be transferred. Run this script to enable all constraints again:

If, for some reason, the export fails half way through, you can run either the first or second script to remove the data (because even though constraints are disabled, truncating a table doesn't work):
EXEC sp_msforeachtable "DELETE FROM ?"

SELECT [name] FROM sys.tables WHERE [type] = 'U'

OPEN CurTables


 SET @SQL = 'DELETE FROM ' + @TableName
 EXEC sp_executesql @SQL
 FETCH NEXT FROM CurTables INTO @TableName

CLOSE CurTables

The second query is there in case you get an error talking about quoted identifiers and how they need to be set. According to Microsoft, you can't set quoted identifiers during runtime, so the second query does what the first one does, but with more visible code.

Apart from that, everything works well afterwards. Fun times.

Saturday, June 2, 2012

Helpful queries in MySQL

So I've been using MySQL for a little bit now, and I've been wanting to get at some information that's quite easily seen in SQL Management Studio for MSSQL. Turns out it's ridiculously easy - just look through the INFORMATION_SCHEMA table (if you have access to it, which I believe every account does by default - correct me if I'm wrong). To get a list of the stored procedures (or "Routines" in MySQL parlance):
To show all the foreign (referential) constraints:
I'll add some more when I need to look for them.

Thursday, April 19, 2012

Temp Tables vs Table Variables

I've been having a lot of issues in the last two days with TempDB having some ridiculously heavy usage. Some well placed (I think) indexes have seemingly helped alleviate some of the usage of TempDB. I still have one query to re-write, and it's so I stop using a table variable, and move to a temp table (I know, it sounds counter productive).

Here's a post about temp variables vs temp tables. Fun.

Tuesday, November 15, 2011

Taken down a notch or two in life

I got some feedback from an interview I had a few days ago. In my own words, I flat out sucked the big one. In their words, I didn't meet the level of technical expertise they require - which is completely understandable. Their business is far and above anything I've ever even considered, and where I'm at at present is, well, it isn't that challenging.

It's all good and well for family and friends to say "you'll be fine, you're smart, you'll ace it", but really, it's just bullshit posturing. I know what my abilities are, and while I appreciate their sentiments, I know that I need a kick in the pants to get me moving again. I've become too complacent in my role. It's all good to be the "go-to" guy where you're at, but there's ALWAYS a ton of people better than you, and you need to realise that so you can make yourself better.

So, it's back to the drawing board as such. I'm going to do a whole lot of study, and hopefully get my MCTS for Database Development. That study in itself should at least boost my knowledge a bit.