belle’s sql musings

new blog at sqlbelle.com

Posted in Uncategorized by belle on February 11, 2015

My new blog is at

http://sqlbelle.com

where I blog about SQL Server, PowerShell, Power BI and Visual Analytics with Tableau

This Is Why I Teach

Posted in musings, Teaching, Uncategorized by belle on December 3, 2008

I have been teaching IT courses since 2002; I started as a TA for relational databases and Delphi. Right now I teach part time courses in web development/design, and database development and administration. Wow, it’s already been a while and yet I can still remember my first teaching gig as if it were just yesterday.

Some people ask me why I teach. This is why:

ty   ty2 ty3

 

I take pains when developing and delivering my courses. I treat teaching very seriously. When students take my class, I want them to really learn something from it. I want them to get their money’s worth (or more!). I want them to walk away with a new skill, or new knowledge, or new discovery, after each class. When they ask me questions I don’t know the answer to, I try to look for the answer – sometimes even spend sleepless nights trying out a few test cases – and present the answer in the next class.

 

Why?
Because I love doing this.

And because I want to make a difference. However small or trivial it may be.

 

My teaching style does not work for all of my students. I do not have all the answers to student questions, and no – not all the demos are flawless in front of the students.

But if I can help someone understand a concept, or look for a solution, or just be someone to bounce ideas with, then it’s all worth it. Or just encourage them to keep on going and not get discouraged …

And sometimes, some students take the time to let me know they appreciate this. And I can’t tell them how much gestures like this mean to instructors like me.

SQL Server Scalability etc – Free Ebooks

Posted in Ebooks, Resources/References by belle on November 7, 2008

Connection to target machine could not be made in a timely fashion

Posted in Issues/Troubleshooting by belle on November 6, 2008

Problem

Actually the error is (note spelling mistake on fashion):

"Connection to target machine could not be made in a timely fasion"

Scenario

One of our DBAs was testing the SQL cluster failover. After he failed over from Node 1 to Node 2, he can no longer bring up SQL Server Configuration Manager (SSCM). Whenever he tried, he will wait for about 30-40 seconds before he gets an error message : Connection to target machine could not be made in a timely fasion

Resolution

This could have been caused by a whole host of things.

In our case, all we had to do was to restart WMI service:

  1. Start > Run > type services.msc and press Enter
  2. Scroll down to Windows Management Instrumentation, right click, restart
Tagged with:

View or function ‘sys.dm_exec_sessions’ has more column names …

Posted in Issues/Troubleshooting by belle on November 5, 2008

Problem

Agent message code 4502. View or function ‘sys.dm_exec_sessions’ has more column names specified than columns defined.

Scenario

Our replication on our clustered database just started to fail. We were getting the following error:

Agent message code 4502. View or function ‘sys.dm_exec_sessions’ has more column names specified than columns defined.

Ok, this is a weird message. How can sys.dm_exec_sessions have more columns that specified?

We checked the definition:

EXEC sp_helptext 'sys.dm_exec_sessions'

And this is what we got:

CREATE VIEW sys.dm_exec_sessions AS
 SELECT *
 FROM OpenRowset(TABLE SYSSESSIONS)


Ok, we can see how this can be a problem – because of the star (*). Any column change can spell disaster. However, we weren’t very sure why this would cause any problems, because this is not any regular user view. It’s a built in SQL Server DMV.

So we checked out a few things:

SELECT 
 SERVERPROPERTY('ResourceVersion'),
 SERVERPROPERTY('ProductVersion'),
 SERVERPROPERTY('ProductLevel')    -- Service Pack

It turns out the installed Service Packs are not the same in all the nodes.

Resolution

Make sure all nodes in the cluster have the same service pack. Sometimes it’s easy to miss because you have to failover to install the SP.

Tagged with:

Ooohh! Manga Guide to Databases!

Posted in musings by belle on November 2, 2008

I’m an anime/manga/JPop/JDorama junkie. Yup, that’s my other life besides databases. There was a time when I can talk some straight Japanese sentences – because I’ve watched/listened to Japanese stuff so much and so often.

JPop

I play Utada Hikaru, Ayumi Hamasaki, Do As Infinity (Tomiko Van is lead singer) and Maaya Sakamoto quite regularly while I drive. I have most of their CDs. I even used to wait for weeks for Amazon to deliver my JPop CDs because they’re usually not in stock. I’ve also listened to BoA, Hitomi, Amuro Namie, Otsuka Ai, Gackt, TOKIO, Orange Range, Arashi …

JDorama

I watched a lot of the TOP 50 JDoramas listed in JDorama.com. Love Mukodono!, Hana Yori Dango, Hanazakari no Kimitachi e, Love Generation, GTO. Also watched Majo no Jouken (First Love) and Ichi Ritoru no Namida  (1 Liter of Tears) and cried buckets (yeah, I’m a weepy).

Anime/Manga

Anime was what started me with this whole Japanese fascination. The first ever anime that I watched was Rurouni Kenshin (marketed as Samurai X in North America) – all the episodes plus all OVAs – and absolutely loved it. My next set of anime/manga marathons Fruits Basket, Full Metal Panic, Trigun, Cowboy Bebop, Escaflowne, Bleach, Inuyasha (yes, I watched all 167 eps). Also  love Hayao Miyazaki’s works – Nausicaä of the Valley of the Wind, Castle in the Sky, Spirited Away, Pricess Mononoke, Howl’s Moving Castle.

And the point is?

BrentO mentioned on Twitter that there’s a Manga Guide to Databases on Amazon! Isn’t that uber coolness! (For me anyway).

Here’s an excerpt (from Amazon):

Princess Ruruna is stressed out. With the king and queen away, she has to manage the Kingdom of Kod’s humongous fruit-selling empire. Overseas departments, scads of inventory, conflicting prices, and so many customers! It’s all such a confusing mess. But a mysterious book and a helpful fairy promise to solve her organizational problems—with the practical magic of databases.

Haha doesn’t that problem sound all too familiar.

The cover is also super cute! Thanks Brent! I am pre-ordering it with JK Rowling’s Tales of Beedle the Bard. So excited.

PS – And if you are looking for good Japanese Tea, you may want to try out Hibiki-an. Great tea! And they deliver it so fast too.

Tagged with:

Soaking in SQLServerPedia

Posted in DBA Toolbox / T-SQL Scripts, musings, Resources/References, Tutorials by belle on October 31, 2008

One of the bloggers I follow is Brent Ozar. I tell you I’m a fan. I like the way he writes his stuff.

Anyway, he blogged about The Problem With SQL Server Training Today, and The Answer to SQL Server Training Problems a week or so ago. And this is the answer – SQLServerPedia  – SQL Server Knowledge and Advice Straight from the Experts.

So in between giving out candies to cute little Indiana Joneses, pirates of the Caribbean, vampires and dead cheerleaders, I’ve been soaking in SQLServerPedia (geeky ‘no?). So far, so good. I’ll be a frequent flyer.

 

PS – and if you’re a DBA, you’ll find great laughs in this post : Somebody Created a Halloween Monster #SQLputdowns

Tagged with:

Drop and Re-Add Server Registration When Renaming Computer

Posted in DBA Toolbox / T-SQL Scripts, Issues/Troubleshooting by belle on October 31, 2008

Problem:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, ‘SQLVM\SQL02’. (Replication.Utilities)

Scenario:

1. We configured database mirroring on 2 SQL Server instances.

2. We changed the computer name from SQLVM to SQL2K5.

3. We tried launching the Database Mirroring monitor, but we got the following error:

SQL Server replication requires the actual server name to make a connection 
to the server. Connections through a server alias, IP address, or any other 
alternate name are not supported. Specify the actual server name, 'SQLVM\SQL02'. 
(Replication.Utilities)

If you just look at the error, it sounds a bit misleading because we don’t have replication configured in either of the servers.

Resolution:

-- 1. drop old server/instance name
EXEC sp_dropserver [sqlvm\sql01] 
GO
 
-- 2. add newserver/instance name
-- local meaning we're adding the local machine
EXEC sp_addserver [mysql2k5\sql01], local 
GO
 
-- 3. Restart SQL Server service

Tagged with:

SQL Server-Related Cheat Sheets That Can Save You Time

Posted in DBA Toolbox / T-SQL Scripts, Powershell by belle on October 17, 2008

I previously posted about a SQL Server CheatSheet from DotNet4All that I thought was useful and comprehensive. If you already know what you need to do, but just forgot the syntax because you haven’t done it in eons, then  a well organized, concise, complete cheat sheet can save the day.

 

Here are a few other ones:

General SQL Server
DotNet4All

SQLAuthority
Scribd T-SQL

 

SQL Server Shortcuts
SQLArticles (really good, exhaustive list!)
SQLAuthority SSMS Shortcuts

 

Connection Strings
ConnectionStrings.com

 

Regex (especially when you’re working with SQLCLR/Regex)
.NET Regex Cheat Sheet
AddedBytes
(AddedBytes used to be known as ILoveJackDaniels, but that name has changed. Dave has a *lot* of useful cheat sheets in his site.)

 

SQL Injection
Ferruh Mavituna
ha.ckers

 

PowerShell
MSDN Blogs (PDF)
MSDN Blogs (MS Word)

 

SQL Server 2008
SQLAuthority

 

SQL Server DBA Interview Questions
SQLAuthority Interview Questions – Complete List

Tagged with: ,

What to do if you accidentally lock yourself out of SQL Server (SINGLE_USER mode)?

Posted in DBA Toolbox / T-SQL Scripts, Issues/Troubleshooting by belle on October 17, 2008

Problem:

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database <your db name> cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed."

Scenario:

Sometimes you need to have "exclusive" access to the database, let’s say,

  • when you want to force a restore, or
  • when you want to alter the database and an exclusive lock is required

To get this exclusive access, you may need to set the database to SINGLE_USER mode:

USE [master]
GO
 
ALTER DATABASE [DBName]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

However, what if you accidentally lock yourself out of the database?

What if for some reason there is some other connection or user that is using the database, you will most probably get this error :

"Msg 5064, Level 16, State 1, Line 1

Changes to the state or options of database <your db name> cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

Msg 5069, Level 16, State 1, Line 1

ALTER DATABASE statement failed."

How do you get yourself back in?

Resolution:

1. Determine which connection is actually locking you out

-- look for the connection that uses your db and note the spid
EXEC sp_who

 

2. Kill the connection

KILL <spid>


3. Change the database back to MULTI_USER

ALTER DATABASE [DBName]
SET MULTI_USER;
GO

That should do it. Happy

Follow

Get every new post delivered to your Inbox.