Friday, October 31, 2008

EXECUTE AS and REVERT

I learned a new thing today, switching between execution/user contexts using EXECUTE AS and REVERT.
I have been lucky in that I have not encountered too many permission problems due to
  1. I am sysadmin
  2. Most applications run under 'dbo' (probably bad practice)

Books Online has the perfect examples on EXECUTE AS and REVERT

This was my testing code, on testing a Database DDL Trigger


ENABLE
TRIGGER Create_SP_Trigger ON DATABASE
--DISABLE TRIGGER Create_SP_Trigger ON DATABASE
GO

CREATE PROCEDURE Grant_Test_Proc
AS
PRINT
'should have triggered the trigger to auto-grant execute permission to user'
GO

--SELECT SUSER_NAME(), USER_NAME();

EXECUTE AS USER = 'testuser'   -- switches to a limited permission login
EXEC Grant_Test_Proc
GO

--SELECT SUSER_NAME(), USER_NAME();

REVERT;    -- switches to DBO and full permissions
--SELECT SUSER_NAME(), USER_NAME();
DROP PROCEDURE Grant_Test_Proc
GO

Thursday, October 30, 2008

HOW TO: Import/Export SQL Server Maintenance Plan

As sqlmaint Utility will be phased out in future versions of SQL Server, Maintenance Plan is Microsoft's recommended approach for Database Maintenance tasks by the DBA

  • Check Database Integrity
  • Shrink Database
  • Reorganize Index
  • Rebuild Index
  • Update Statistics
  • Clean Up History
  • Execute SQL Server Agent Job
  • Backup Database (Full, Differential, Transaction Log)
  • Maintenance Cleanup Task
Naturally, after spending the time and efforts to setup proper Maintenance Plans, you should save it and re-use elsewhere anytime you can. And here are 2 ways to export the Maintenance Plan in SQL Server 2005 and 2008

The easier GUI way
1. Open SQL Server Management Studio

2. In Object Explorer (left pane), click "Connect", choose "Integration Services..."

3. Login into the SQL Server with credentials

4. Expand SERVERNAME (Integration Services), and you'll see the below structure
  • Running Packages
  • Stored Packages
    • File Systems
    • MSDB
      • Maintenance Plans <- this is what you want
5. Right Click on the Maintenance Plan you want to export, and select Export
 
The harder script way


USE MSDB

SELECT name
,PlanXML=CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
FROM sysdtspackages90

New SQL Skills I have learned from jobs - Part 1

I initlaly thought SQL Server was all about Transact SQL (T-SQL) programming and development, as that was how I started working with SQL Server 2000, extensively.

Then, last couple years I have came across opportunities at work to expand my knowledge of SQL Server to just about everything (shows you how little I knew)

Let's take a walk down memory lane by SELECT Name, Skills FROM Memory (NOLOCK) ORDER BY [Date] DESC

SQL Server Reporting Service (SSRS)

  • Developed over 100 SSRS 2005 reports from scratch
  • Learned by reading the books (very helpful) and reading online contents
  • Deployed and integrated with SharePoint intranet
  • Email subscriptions to management
  • SSRS was quite powerful for a free product, almost can replace Crystal Reports easily
SQL Service Analysis Service (SSAS)

  • Followed the book and built a demo Datawarehouse with Cubes and Dimensions, Slice & Dice!
  • Connected SSRS report to report off the Cube and Dashboard/KPI
  • Unfortunately didn't have the opportunity to fully try the Analysis models or Decision Trees to "predict future" from the history. That would've been very cool and helpful for business sales
 
More to comment later:
  • High Availability Features - Mirroring and Log Shipping
  • XML/XQuery
  • SQL Server 2008 Spatial Index
  • SQL Server 2008 Full-Text Search
  • SQL Server Integration Service

Tuesday, October 28, 2008

HOW TO: Convert datetime stored in seconds to readable format

I needed to convert datetime stored in seconds to valid readable datetime, and came across this article
Apparently Unix timestamp range only from 1970 Jan 1 to 2038-01-19 at 3:14:08 AM, good to know


SELECT
scheduled_start_date, scheduled_end_date
,StartDate = DATEADD(s, scheduled_start_date, '19700101')
,EndDate = DATEADD(s, scheduled_end_date, '19700101')
FROM TABLE (nolock)


scheduled_start_date   scheduled_end_date  StartDate   EndDate
1219939200 1219942800  2008
-08-28 16:00:00.000  2008-08-28 17:00:00.000
1220108400 1220133600  2008
-08-30 15:00:00.000  2008-08-30 22:00:00.000
1221163200 1221163380  2008
-09-11 20:00:00.000  2008-09-11 20:03:00.000
1221768000 1221786000  2008
-09-18 20:00:00.000  2008-09-19 01:00:00.000
1221768000 1221786000  2008
-09-18 20:00:00.000  2008-09-19 01:00:00.000
1223413200 1223416800  2008
-10-07 21:00:00.000  2008-10-07 22:00:00.000
1225213200 1225215000  2008
-10-28 17:00:00.000  2008-10-28 17:30:00.000
1225206000 1225209600  2008
-10-28 15:00:00.000  2008-10-28 16:00:00.000
1225204200 1225206000  2008
-10-28 14:30:00.000  2008-10-28 15:00:00.000
1225377000 1225378800  2008
-10-30 14:30:00.000  2008-10-30 15:00:00.000
1225215000 1225216800  2008
-10-28 17:30:00.000  2008-10-28 18:00:00.000
1225627200 1225628100  2008
-11-02 12:00:00.000  2008-11-02 12:15:00.000

Friday, October 24, 2008

How to Ask The Right Questions the Right Way

As I started participating more and more in SQL Forums, it becomes clear that there are some small steps that could make everyone's task easier to help out.

This is a good article to start - Forum Etiquette: How to post data/code on a forum to get the best help

Personally these are my steps

1. Always try to include the SAMPLE data or tables (in SQL code, not just copy-and-paste data) unless it's syntax or concept question,
We DBAs are just like Web Designers, we like to work with what we can see

2. Beautify the SQL code, before any posting
Either using 3rd party tools such as Red Gate SQL Prompt/Refactor, or use the online The Simple-Talk Code Prettifier for SQL Server, Python, VB and C#

3. Always state what versions of software being used
For example, T-SQL changed drastically from 2000 to 2005 to 2008, one code may not work in every version

4. Willing to learn and try it out by actually doing it, and not hoping someone will take your code, and give you back a working code so that your problem is resolved by someone else.
Specifically, try to UNDERSTAND the actual solution and not just copy-and-paste. It hurts me to see users not trying to fix a clear syntax error when a clear error message presented; instead, they come back and say "it doesn't work"

I must say, the tasks of facing a challenge, conquer it and help someone out is very satisfying.

"A problem well stated is a problem half solved." -- Charles F. Kettering

SSMS Tools Pack (for SQL Management Studio 2005/2008)

I want to share a tool (FREE) that I have used extensively to help in where SSMS lack in features (such as Generate Insert Statements, run Custom Scripts, etc...) - SSMS Tools Pack

The author released the SQL 2008 version just recently, and it has worked beautifully just like the 2005 version.

The SSMS Tools Pack does NOT work for SSMS 2005 versions before SP2 anymore.
---------------------------------------------------------------
SSMS Tools PACK is an Add-In (Add-On) for Microsoft SQL Server Management Studio and Microsoft SQL Server Management Studio Express.

It contains a few upgrades to the IDE that I thought were missing from the Management Studio:

Query Execution History (Soft Source Control) and Current Window History.

Search Database Data.

Uppercase/Lowercase keywords.

Run one script on multiple databases.

Copy execution plan bitmaps to clipboard.

Search Results in Grid Mode and Execution Plans.

Generate Insert statements for a single table, the whole database or current resultsets in grids.

Text document Regions and Debug sections.

Running custom scripts from Object explorer's Context menu.

CRUD (Create, Read, Update, Delete) stored procedure generation.

New query template.

Thursday, October 2, 2008

SQL Server 2008 Books Online (August 2008) Released

Download Microsoft SQL Server 2008 Books Online (August 2008)
The file is 141MB (SQLServer2008_BOL_August2008_ENU.msi)

I still prefers GOOGLE to find syntax help or the Books Online ONLINE at Microsoft ;-p