Sunday, 13 March 2011

Importing data into SQL Server from a text file using BULK INSERT

As a .Net / SQL Server developer it is often necessary to import data in to your databases from other sources, such as text files. Ideally you would leave this type of data manipulation activity to the kind of weirdos who enjoy it (DBAs). Sadly however this is not always possible.

Therefore, in the old days of SQL Server 2000 I often created DTS packages to suck in my data. DTS was OK, especially the funky grey arrows, but it was a little time consuming. Then - disaster - DTS was phased out in favour of SSIS. At No Frills Corp our DBAs have not been able to give me a server that SSIS actually works on, so I was a bit stuck.

It was then that I came across the beatifully simple SQL command, BULK INSERT.

Just point a BULK INSERT command at your text file, tell it where to put the data, and you are all set. It can't be that easy can it? Yes, it really can:

   FROM 'D:\primates\chimps.csv'
         FIELDTERMINATOR =',',
         ROWTERMINATOR ='\n'

Friday, 4 March 2011

Finding the active node in a SQL Server Cluster

DBAs are an eccentric breed aren't they?

At my place of work, No Frills Corp, we have 2 types of DBA: DBAs who are perpetually on the edge of a nervous breakdown and will happily go a whole day without saying anything except "No", "I'm busy" and "Must be a problem with your app", and DBAs whom you should not engage in the simplest of conversations unless you have a couple of hours to spare.

I much prefer the taciturn DBAs, but in both cases the best policy is not to talk to them unless you really have to. So, what if you just need to know something simple but important, like which node of our active-passive cluster is currently active?

The other day I came across the very simple answer to this question:

SELECT ServerProperty('ComputerNamePhysicalNetBIOS')