A trusty old friend – The BCP utility

One of the requirements that springs up around data stores – regardless of if they are relational, not relational, cloud based or some other variety is (unsurprisingly) the need to access the data for various reasons such as reporting or analysis (amongst other things). Often, this requires moving the data (off-loading the data) to another environment / system where analysis or reporting can take place without the need to impact the main system.

Very often we use tools such as SQL Server Integration Services or Azure Data Factory for this sort of thing. But occasionally we might wish to fall back to a utility that’s been in SQL Server (as well as Sybase) for a long time – and that is the Bulk Copy Program, or BCP for short.

The BCP utility (bcp.exe) can be found in the SQL Server installation directory structure. On my machine it can be found at

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe

Your system may be slightly different. Regardless, the directory should be accessible via the PATH variable and so simply typing bcp or bcp -? should display a list of arguments that the bcp utility accepts.

As an aside, did you also know that you can look at the same display using the PowerShell ISE – which in some cases may provide a more pleasing display

Let’s see how easy it is. We’ll use the sys.configurations table as everybody has that.

Clear-Host;
bcp.exe "SELECT * FROM sys.configurations" queryout "C:\temp\configurations.txt" -Slocalhost -dtempdb  -T -c -t',';

If you do run the above code then you will notice that the text names and descriptions have spaces, this might be a problem. We could handle this in the bcp utility. However, I’d be more inclined to do that in the T-SQL query, but that’s just a personal choice others may prefer another method. But here’s the query I’d use – notice the use of QUOTENAME to enclose the text in double quotation marks.

USE tempdb;
GO

SELECT
	[configuration_id], 
	[name] = QUOTENAME([name], '"'),
	[value], 
	[minimum], 
	[maximum], 
	[value_in_use], 
	[description] = QUOTENAME([description] , '"'), 
	[is_dynamic], 
	[is_advanced]
FROM 
	sys.configurations AS C
ORDER BY 
	C.configuration_id ASC;

Although this is a relatively old utility, I still find it remarkably useful and use it reasonably often – hence this post.

There are other ways to accomplish the same requirement (maybe even without moving the data – but we’ll leave that for another day)

And, by the way, the data that has been extracted is now on disk – so be sure to have a plan that deals with security.

BCP can also be used to input data into a table as well. I’ll leave that example for the moment but will return to it in a later post.

Have a great day

Cheers

Marty.

Data Privacy – Playing your part

Tuesday 28th January 2020 was “Data Privacy Day 2020”

Some may refer to this as Data Protection Day, but it is really just a day to draw attention to privacy issues that exist around the use of digital data.

You can read more about this day here and here.

Ironically, on that day, I just happened to be investigating some privacy / security issues and I was reminded that some companies, individuals and even industry sectors that should be blatantly aware of the issues surrounding data privacy are either

  • Ignore of these issues
  • Playing ignorant
  • Thinking ‘it’ll never happen to me’
  • Not caring
  • putting profits above the protection of their clients.
  • All (or some of the above)

And there’s probably many more reasons that people are employing less than desirable practices to look after their data.

If you need more evidence you only have to look at the list of big global companies that have suffered data breaches of some kind in the last few years.

The damage caused by a data breach can be irreparable. Law suits may follow, reputations may be damaged, goodwill may be lost and that’ll soon be reflected in financial figures.

There are plenty of simple things that people can do to help keep their company’s (and their own) data safe. I’ll go into more details on these in later posts but initially I just wanted to start by saying (or rather reiterating) that security is everybody’s job.

Just because the word security is not in your job title does not mean you can get away with complacency.

If you see an issue – speak up. If you could suggest an improvement then speak up too. I get that there may be cultural barriers in some workplaces around these sort of things but good employers will remove these barriers and allow a physiologically safe workplace.

This is an amazingly cost effective way to help secure your environment.

A lack of data protection is not only causing embarrassment to the IT industry as whole but is costing individuals – who have trusted banks and other large institutions with their data – both time and money.

Nowadays, it is easy for a person to switch banks, insurance companies, airlines, phone / internet / TV provides or any other service provider really easily and that may have consequences for your employment.

So, whatever industry you are in I’d encourage you to play your own part – however small – in helping your company keep the data of their customers safe and secure.

Have a great day.

Cheers

Marty

Simply forgot – What’s in my path?

Simple things we often forget

I was recently doing some work that required me to look at the PATH system variable in Windows.

Nothing too hard about that, just open a command prompt and type path

and we get all the directories available in the PATH listed.

That’s sometimes a little hard to read, so say we wanted each directory on a separate line – simple, Powershell can do that for us.

In fact – I prefer using the Powershell ISE to the simple Commandline windows

If you’re looking for the Powershell ISE, just do a search directly from the start menu in windows

Then it’s just a simple matter of adding a few lines of code

and we’ll see the output listed (truncated here)

Hope this helps somebody out.

Cheers

Marty

Moving to a Modern Microsoft Platform

Melbourne Event – Monday 17th June 2019 (Free – including lunch and prizes)

PASS, in conjunction with Microsoft, Intel and community leaders are offering free training on Moving to a Modern Microsoft Platform

In case you’ve never heard of PASS, they are a not profit organisation run by, and for, a diverse data community.

PASS (formerly known as The Professional Association of SQL Server) focuses on supporting data professionals throughout the world who use the Microsoft Data Platform.

You can read more about PASS on their site

Microsoft SQL Server has been around for some 25+ years and there is a major release coming up later this year – SQL Server 2019.

Another thing that is happening this year – in fact very soon – is that Microsoft SQL Server 2008 (which includes SQL Server 2008R2) is moving out of extended supported.

So, if you are using Microsoft SQL Server 2008 / 2008R2 you’ll need to prepare for SQL Server 2008 End of support

It’s 10 years since that version was released and it was one of the most popular with a lot of organisations still relying on keeping one of their prized business assets on the 2008 platform.

To ensure that organisations stay current and to help them with some of the options that are available, PASS, Microsoft and Intel have teamed up to bring users around the globe a free in-person event in many cities to provide advice, and support to ensure that they fully aware of all options to keep their users data safe, secure and highly available in an ever changing data-driven world.

The events throughout the world are listed here.

And if you would like to register for the Melbourne event – that will run in the CBD on Monday 17th June 2019 at

Telstra Customer Insights Centre
Level 1, 242 Exhibition Street
Melbourne, VIC 3000
Australia

then please head to the EventBrite page and Register for FREE – we’d love to see you there, learn about your pain points, priorities and talk through your next steps on your data-driven journey to keep your clients data safe, secure and highly available to provide the insight that a modern business needs.

They’ll be information sessions, hands-on-labs (if that’s your thing), expert instruction and of course prizes.

There’s no charge and lunch will be provided.

We look forward to meeting you!

T-SQL Tuesday #109 – Mentors and Mentees

G’day again,

So, it’s time for T-SQL Tuesday again, the blog party 
started by Adam Machanic (b|t) that just keeps on rolling.
I just looked at the rules – and they are quite long, hopefully, I’ve
covered them all – if not, I’m sorry 🙂
This time we’re talking about people you’re influenced to get
involved in SQL, strengthen their SQL, join the community or in
any other way.
As I regional mentor in the APAC area I often talk to community
members from SQL, Power BI, Power Platform and various other forms
of the Data Platform – Microsoft or otherwise, I’m sure I’ve influenced
a few – but I’d prefer not to name any (as I’ll possibly get that wrong)
But mentors change as interests change and as we drift between
different projects and steer different courses in life – not just work related. Staying in touch and increasing our networks is a vital skill in this day and age.
I’ve sat in many Data Platform classes and events around the world and listened to some of the well know names in our industry.
Your contribution doesn’t have to be big, taking somebody along to a user group is cool, especially one like SQL Social run by Martin Cairney (t) in Melbourne – a seemingly unique format, in a local pub surrounded by friendly members with great stories and offers of help.
Greg Low (b|t) would be one of my all time favourite presenters, mentors and all round friends.
There’s a lot to name – so I won’t, or we’ll be here all day.
One confession is that I may of had a small degree of influence in releasing the hurricane that  is the @TheHybridDBA on to the world – for that, you can judge me!

Have a great mentoring day.

Cheers

Martin.

TSQL Tuesday #108 – Learning to Learn

G’day,

One attribute that I think is more relevant today than ever is to have a broad base of skills with the ability to go deep when needed.

This requires the ability to learn quickly when needed.  Which, in turn, requires that we know how to learn quickly.

When I learnt SQL, I had the luxury of time and the ability to simply absorb things slowly, to buy and read books and build a todo list that might take a while to get finished. Those days are fading and a new era of learning is upon us.

Now we are at a point where learning from books (for a lot of things – but not all) is not the best way to go. This is  more of a function of how long it takes to write and get a book published –  often the content is out of date, even at publication time.

Now we are seeing technology changing that fast that even  blog posts and other online content is quickly being out dated. This means we have to be vigilant and look at dates associated with online content and judge if this content is of value any longer.

We need to have side projects where we can experiment, we need to network harder than ever and learn form our friends and peers, we need to share our knowledge and work in teams that have great learning cultures and are happy to experiment.

In summary, I feel the biggest skill that is needed today is the ability to learn quickly – and to have a good strategy to accomplish that. To recognise good  mentors, have good foresight and look for opportunities to take on projects that will elevate our learning.

Have a great day.

Cheers

Martin

 

 

A lack of foreign keys – a bad culture example.

G’day,

One of the things that I’ve been seeing more often than I would like to lately is large databases with no foreign keys – or minimal foreign keys (and often with those minimal set of keys disabled)

By large databases I’m roughly meaning databases with several hundred tables, and I usually see a lot of these tables with several hundred GB’s of data in them.

When I generally ask about the reason for no foreign key, I’m told

  1. they add  overhead
  2. they give no benefit
  3. we can’t enter our data properly when we have them

The last one in the above list is generally down to poor modelling – an example being entering a later part of a financial transaction before the first part of the financial transaction has taken place. Once both parts of the financial transaction have taken place then the database is left in a consistent state – BUT, that generally being handled by the app NOT the database – OUCH!

My first though here is “I’m glad I don’t use this company” – and I also wonder why auditing or testing has not picked it up. Quite often it has but somebody, somewhere has decided that there’s a reasonably low chance of that occurring and so no action has been taken – until the ‘bug’ is discovered sometime later and at that point takes a long time – and a lot of money – to fix.

Points 1 and 2 are quoted more often than I would like also – usually stemming from statements that were made by somebody who worked with a long dead version of the database and didn’t quite take the time to understand fully how relational databases work – but who had tremendous influence in the company and a reputation that meant they were not to be crossed. (they have since been proved wrong but have failed to accept it)

These are simply examples of now bad culture can lead to problems years down the road.

Looking at a lot of database that lacked foreign keys, it’s usually pretty simply to find examples of inconsistent data in the tables. – caused by the three issues above.

The moral of the story here is

Always question, always learn and really try to avoid issues that are bound to come back and haunt you.

Always try to build good culture.

Have a great day.

Cheers

Martin.