Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Archives: September 2013

The MERGE statement doesn’t have a WHERE clause.

What the heck? Even indexes have WHERE clauses these days. I can’t remember what I was reading when I saw this but it completely flabbergasted me. If you go look at MERGE in BOL you will see what I mean. So does this mean you can’t restrict data in a… Read more

5 comments, 324 reads

Posted in SQLStudies on 30 September 2013

Detaching a database

In my general quest to figure out the T-SQL to do common GUI tasks I did some browsing through BOL to find out how to detach a database. Dropping a database is easy enough.

DROP DATABASE <databasename>

But that also deletes the database files (unless the database or one of… Read more

1 comments, 1,180 reads

Posted in SQLStudies on 26 September 2013

DBA Myths: You can’t use an alias in a DELETE statement

Recently I wrote about the myth that you can’t use an alias in an UPDATE statement. You can of course, and the trick is to make sure that the alias is used in the FROM clause of the statement. That brought up the question “So how about the DELETE… Read more

0 comments, 335 reads

Posted in SQLStudies on 24 September 2013

sp_DBPermissions V2.0

Last month I posted my stored procedures sp_SrvPermissions and sp_DBPermissions. I’m posting V2.0 of each with a few fixes. The fixes are listed at the bottom of the comments block.

sp_DBPermissions is a stored procedure that will output 3 record sets containing information on the database level principals, what roles… Read more

1 comments, 1,217 reads

Posted in SQLStudies on 18 September 2013

sp_SrvPermissions V2.0

Last month I posted my stored procedures sp_SrvPermissions and sp_DBPermissions. I’m posting V2.0 of each with a few fixes. The fixes are listed at the bottom of the comments block.

sp_SrvPermissions is a stored procedure that will output 3 record sets containing information on the server level principals, what… Read more

1 comments, 1,397 reads

Posted in SQLStudies on 18 September 2013

Brad Shultz’s blog on reviewing your database

This is possibly the best blog I have every read on the subject of reviewing a database. It is witty, insightful, and mentions a number of very common problems. The descriptions of each problem are well written and easy to understand. There are multiple scripts and links throughout the post… Read more

1 comments, 278 reads

Posted in SQLStudies on 17 September 2013

DBA Myths: You can’t use an alias in an UPDATE statement.

I’ve found a very common belief among users of T-SQL (both DBAs and Developers) is that you can’t use an alias with the UPDATE statement. As best I can tell the reason for this is because of a simple misunderstanding of the UPDATE command syntax.

Here is an example of… Read more

1 comments, 434 reads

Posted in SQLStudies on 16 September 2013

Certifications: What are they good for?

Since Microsoft decided to drop the MCM/MCA program there has been an enormous amount of discussion about the program and certifications in general. Thomas LaRock even suggested Rebooting the MCM Program Using an RV and an Endless Supply of Chicken Wings. Which I have to say sounds like a… Read more

4 comments, 186 reads

Posted in SQLStudies on 11 September 2013

A T-SQL query to get current job activity

I recently had the task of collecting job activity for the purposes of turning it into a report for some business users. After some searching on the web and BOL I found the stored procedure msdb.dbo.sp_help_jobactivity that returns the data I need (with some work) and a lot more data… Read more

10 comments, 4,768 reads

Posted in SQLStudies on 5 September 2013

When I run “Script as” it isn’t (or is) … How do I fix it?

When I run “Script (say table) as” it isn’t (or is) (say generating scripts for indexes). How do I fix it? This is a common enough question with a fairly simple solution. In the Options screen there are dozens of options for the scripting function of Object Explorer. These options… Read more

0 comments, 234 reads

Posted in SQLStudies on 3 September 2013