About blogging

A friend wants to get started and write a blog, and so asked me for advice. Here’s what I had to say.

Yes, write a blog!

First, understand that everything has been written about before. There is nothing new under the sun. EXCEPT for your perspective! So don’t feel like “Oh, I can’t write about XYZ because it’s been done to death.” 

Never mind that….write what you want to write.

Consistency builds a blog audience

Second, everyone always says that you need to be fairly consistent in order to get an audience. They’re right.

If, like me, you write for three months, then let it alone for a few months more [glances nervously at the last substantive blog post here], you won’t get/keep an audience.

The only reason I have a consistent audience, is that I’m obsessive about Twitter, where the vast majority of my tech audience hangs out. Everything else is really just bonus content….it’s not ideal, but it’s how it has worked out.

More for your audience

Third, an audience won’t magically appear. You must write, and you must also post about it somewhere.

Instagram is good, Twitter, is good, Facebook is good, LinkedIn is good….and hashtags are your friends. If you decided to make a blog all about organization, you’ll want to use stuff like #organization #bulletJournal (or whatever you’re writing about).

Where to write a blog?

Fourth, platform! WordPress is great. There’s a free version that’ll get you a URL like “YourName.Wordpress.com”, or you can go for a paid account and get something like YourName.com.

I couldn’t tell you much about other platforms, as I’m a WordPress girl. Perhaps other bloggers and commenters have additions….

But wait, there’s more!

You need a brand

Fifth and finally, branding is important. Your name is okay as a blog, but it’s not as memorable as things like, oh:

  1. MidnightDBA.com
  2. CaptainAwkward.com
  3. UnfuckYourHabitat.com
  4. Youtube.com/DeadMeat
  5. Youtube.com/HowToADHD

And so on. Those sites and channels are each run by one or two people, but they’ve chosen a name that’s really memorable & has to do with what they’re about

So yes, you can go with something like YourName – I mean, John Scalzi (author) gets away with scalzi.com just fine – or you can figure out what you want to write about (more or less), and figure out branding based on that.

That’s it for now! I don’t want to overburden you. But I will clearly talk about this forever and ever and ever and ever….

Top tips for PASS Data Community Summit 2021!

Here’s a quick list of top tips for the rest of the #PASSDataCommunitySummit! You can find out more in the Attendee Guide.

1 – The pressure is off

Recordings are available for 6 months after! So the pressure is off to see everything all at once.

2 – Schedule your live sessions

To join a session live, add the session to your “My Schedule” page.

3 – Q&A prerequisite!

Every speaker with an on-demand session will host a live Q&A for that session. Be sure to watch the related on-demand session FIRST! The Q&A is just the Q&A.

4 – How the Expo Hall works

Go to the Expo Lounge and visit different sponsors! Watch the videos, download the resources – we’ve got a very nice paper on SQL Server security essentials – and request a live chat!

Oh yes, and you should probably visit the Raffles and Prizes page to learn how to win things!!

5 – The Community Zone is VERY cool. Everyone says so.

The `Zone is a cool virtual space, where you can move around, see others, and talk out loud (and/or chat). What’s more, there’s a Help Desk in the Community Zone, and multiple rooms to hang out in. Just a few tips:

  1. Controls: Your Mute and Video on/off controls are at the bottom of the screen.
  2. Proximity volume: The closer “you” (your icon) are to others, the better you can hear them, and vice versa. (Just like in real life!)
  3. Talk to individuals: Hover over another person’s icon to direct message them, or mute them. (This is not like real life.)
  4. Text chat: Use the chat icon in the upper righthand corner to see the room chat, and direct messages.

6 – Connect more!

Go to the Connect page and join the SQL Discord server and/or the SQL Slack. (The Discord has rooms for organizing games, and even a “Taphouse” where we grab drinks after the conference! I’m just saying.)

SQL Server meta-development (with regular expressions and more)

Yesterday, I had about 30 existing queries (and some multi-query processes) that I needed to turn into individual stored procedures. I could, of course, go through the entire list of queries manually, crafting CREATE PROC statements and so on. But what’s the fun in that?

As much as possible, I like to use scripts on my scripts, use regular expressions, and batch actions…even my own.

Batch development

Let’s use this simple script as an example:

Note: For my particular application, I chose to modify the script so that I could separate the batches and insert each one into a temporary table, for further manipulation. If you don’t need to do additional manipulation, you could skip adding them to a table, and instead, just make your CREATE PROC scripts in the file.

My IRL “batch” process for working on this task went something like this:

Step 1: Deal with single quotes

Replace all single quotes with two single quotes. We’re going to turn the entire script into a string, so we must handle the single quotes before doing anything else.

Step 2: Separate query sets

Identify anything that consistently differentiates one query (or one group of queries) from the next. In my script, I was lucky enough to have a comment and special identifier at the start of each separate group, something like “– XYZid …”.


Use replace all with regular expressions (using that differentiator )to surround each query set with single quotes, turning each one into a UNION SELECT + string statement. In this case, I used

replace — XZYid

with ‘\n\nUNION SELECT ‘– XYZid

Note that \n is a regular expression for “newline”. That let us put a couple of lines between the (new) end quote after each batch, and the (new) UNION SELECT + start quote before each batch.

Step 3: Quick adjustments

Change the first statement into a SELECT (not UNION SELECT). Now, we have a valid SELECT…UNION SELECT statement.

Step 4: CREATE PROCEDURE

We’re going to create a table and insert all these strings into it. But first, let’s go ahead and make the statements into CREATE PROCEDURE statements.

Replace — XYZid

With — XYZid \n CREATE PROCEDURE Minion.
This isn’t perfect, because we don’t have our “AS” keyword after the procedure name. Normally, I’d use regex tagged expressions to deal with this, but SSMS is being persnickity about tagged expressions right now. Grrr. We’ll have to deal with the “AS” later, maybe manually.

Step 5: Insert into a table, continue

Now we’ll just create a temporary table and insert all these lovely strings into it!

CREATE TABLE #temp (strings varchar(max) NULL);

INSERT INTO #temp (strings)
SELECT….
UNION SELECT ….

With each separate proto-procedure in its own row, I can do all kinds of cool stuff. Not least of which is – oh hey, look at that! – figuring out the procedure name

select
	/* Where in the string is "create procedure"? */
	  charindex('create procedure', strings)

	/* everything to the left of (and including) "CREATE PROCEDURE" */
	, LEFT(strings, charindex('create procedure', strings)+16)  as [Left, with Proc]
	
	/* everything to the right of (and NOT including) "CREATE PROCEDURE" */
	, RIGHT (strings, len(strings)-charindex('create procedure', strings)-16) as [Right of Proc] 
	
	/* Pull out the procedure name based on "CREATE PROCEDURE" as the start, and the first space after the next string as the end. */
	, SUBSTRING(strings
		, charindex('create procedure', strings)+17			-- <--starting place
		, CHARINDEX(' ', RIGHT (strings, len(strings)-charindex('create procedure', strings)-16))	-- <--ending place
		)
from #temp;

Once we figured that out, we can pull that name out and edit the CREATE PROC statements:

UPDATE #temp 
SET ProcName = SUBSTRING(strings
		, CHARINDEX('create procedure', strings)+17			-- starting place
		, CHARINDEX(' ', RIGHT (strings, len(strings)-charindex('create procedure', strings)-16))	-- ending place
		);

UPDATE #temp SET ProcName = LTRIM(RTRIM(ProcName));

/* Add "AS" after the procedure name */
UPDATE #temp 
SET strings = REPLACE(strings
	, 'CREATE PROCEDURE ' + ProcName
	, 'CREATE PROCEDURE ' + ProcName + ' AS ');

Step 6: Use a cursor to print them all out

We could use PowerShell to pull out all those lovely edited scripts, or we could use a cursor and PRINT statements. There are plusses and minuses to each, but today we’re in a hurry, so:

DECLARE @SP VARCHAR(MAX);

DECLARE crs CURSOR FOR 
SELECT strings FROM #temp 
ORDER BY ProcName; 

OPEN crs;

FETCH NEXT FROM crs INTO @SP;

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @SP;
	PRINT 'GO';
	PRINT '';
	FETCH NEXT FROM crs INTO @SP;
END;

CLOSE crs;
DEALLOCATE crs;

Yes, it looks like a lot of work. But – especially considering the other fiddly things I had to do with the actual scripts I worked on – it’s far, far less work to semi-automate (or semi-batch, or meta-develop) the scripts with methods like these.


By the way, the queries in the examples – SELECT * FROM Minion.IndexSettingsDB, and so on – come from our free-and-outstanding SQL Server backup and maintenance scripts. Download, install, and configure to your heart’s content. They’re feature-rich….Availability Group-aware, to name just one!