αρχάριος

FrancisM - The Man From Manila

marl — Mon, 09 Mar 2009 15:14:00 GMT

I remember when Rap, as a music genre, was not totally accepted in the Philippine Society. Rap music was associated with street gangs and violence. But everything has changed when Francis Magalona came in the picture. Not only has he brought Rap in the main stream, but was able to color Rap with a deep sense of Nationalism and Family Values. He used Rap as a medium to instill in the Filipino's mind the love and pride of country and of family.

This is what he had to say to the youth:

Respetuhin natin ang ina
Ilaw s'ya ng tahanan;
Bigyang galang ang ama
At ang payo n'ya ang susundan;
At sa magkakapatid,
Kelangan ay magmahalan -
Dapat lang na pag-usapan
Ang 'di nauunawaan.

I personally feel for Francis Magalona. He died of the same cause that took my mother away on December 1, 2008 -- Acute Myelogenous Luekemia (AML). My nanay underwent the same pain and battle that Francis M had fought. "Respetuhin natin ang ina; Ilaw s'ya ng tahanan". We lost an Icon. I've lost two.

How To: Report Authoring in SSRS 2005 - Part I (The Database)

marl — Sun, 18 Jan 2009 23:02:00 GMT

*NOTE - This is a Cross-Post from my other blog http://dbalink.wordpress.com - Marlon Ribunal : -)

HThis is an introduction to SQL Server Reporting Services 2005. I am not going to explain the architecture and concepts around the Reporting Services (MSDN will do a great job on that). If you're following my "How-To" articles, all the examples are outright step-by-step of the "how's" and not the "why's". This series will demonstrate how to create report from ground up. We'll start from creating our database and the tables and gradually progressing towards creating our simple report to the advanced report design. Knowledge of SQL Server and TSQL is required.

This will be a multipart series about SQL Server Reporting Services 2005.

In this first part ("The Database") we will create the database and its structure. This database is just a simple one. There's nothing fancy about it.

Here we go.

-- Create the demo database CREATE DATABASE SQLServerBlogDemo

In this demo, we will be needing a simple structure which is composed of two tables - Employee and Department. There are lots of Best Practices on how to structure your database objects. We're skipping those right now because that is not the goal of this series. So let's create those two tables:

--Create Employee Table CREATE TABLE Employee ( EmpID INT IDENTITY(1,1) NOT NULL, DeptID INT NULL, EmpFirstName NVARCHAR(50) NULL, EmpLastName NVARCHAR(50) NULL, EmpBirthDay SMALLDATETIME NULL, EmpSSN NVARCHAR(50) NULL, CONSTRAINT PK_Employee PRIMARY KEY CLUSTERED

)

-- Create Department CREATE TABLE Department ( DeptID INT IDENTITY(1,1) NOT NULL, DeptName NVARCHAR(50) NULL, CONSTRAINT PK_Department PRIMARY KEY CLUSTERED )

Let's add a Foreign Key to the Employee table referencing the Department table. We do that by modifying the Employee table

--Add FK to Employee Table ALTER TABLE Employee WITH NOCHECK

ADD CONSTRAINT FK_Employee_Department FOREIGN KEY(DeptID) REFERENCES Department (DeptID)

Now that the table structure is in place, let's insert some data that we can use for our report:

-- Insert Values to the Employee Table INSERT INTO Employee VALUES ( 1, 'Marlon', 'Ribunal', '1/1/2009', '123-45-6789' ) INSERT INTO Employee VALUES ( 2, 'Bill', 'Gates', '2/2/2007', '456-78-9101' ) INSERT INTO Employee VALUES ( 3, 'George', 'Obama', '3/3/2006', '789-10-1112' )

-- Insert Values to the Department Table
INSERT INTO Department VALUES ('IT')
INSERT INTO Department VALUES ('Sales')
INSERT INTO Department VALUES ('Marketing')

Since we're starting from basic report, we want to start from this simple requirement - as simple as this:

As you might have noticed already, this article does not only introduce you to SSRS per se, but also to basic object management in SQL Server. Anyway, We can achieve that result by issuing the following TSQL:

-- TSQL query to satisfy our report requirement SELECT e.EmpFirstName, e.EmpLastName, e.EmpBirthDay, e.EmpSSN, d.DeptName FROM Employee AS e JOIN Department AS d ON e.DeptID = d.DeptID

Now that we have created the database and defined the result that we want through the TSQL query, we're ready to move on.

Read the Part II here.

How To: Report Authoring in SSRS 2005 by Marlon Ribunal is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License.

Creating SQL Server Login From A Domain Account To Access System Directory

marl — Sat, 15 Nov 2008 08:00:00 GMT

Posted by MarlonRibunal

I was having a problem accessing a folder via an extended procedure. The extended procedure accepts a DirectoryInfo variable (File Path) and it returns the File Size and File Count within the folder (much like right-clicking a folder and choosing the “Properties” from the context menu). I need the info to monitor the file size of specific folders for a SSRS report.

Executing the extended procedure causes the SQL Server to throw the following error:

The SQL Server Error Log translates this to be a “EXCEPTION_ACCESS_VIOLATION”. Switching to Windows Authentication would not solve the problem. I could not really drill down to the error because it is apparent that this is a Windows Operating System error. My lack of familiarity with the network environment makes it hard for me to investigate the error. The Windows Event Viewer could not give me useful information pertaining to the error. I do not want to tweak the access rules on the folders that my extended procedure needs in fear of opening holes of vulnerability (these folders are being used by a ASP.NET Web application for file storage).

To circumvent a possible security violation and to keep the security compliance intact, I decided to map the Domain Account to a SQL Server Login. That solved the access violation error.

This is how you create a SQL Server Login from a Domain Account:

[Cross-Posted from my other blog: http://dbalink.wordpress.com]

How To: Moving Data Files

marl — Thu, 06 Nov 2008 23:10:00 GMT

Posted by MarlonRibunal

In this post let’s move a data file from its default location to another location. I have created a database I called “Test” for the demo. First let’s just check where the data file is currently stored:

The “Test” database needs to be put offline before we can move the data file. We do that by issuing the following ALTER DATABASE statement:

To actually move the data file, we issue another ALTER DATABASE statement:

After you issue that command, you should get the following message:

The file “TEST” has been modified in the system catalog. The new path will be used the next time the database is started.

Now, we can move the data file to the new directory we specified (Cut-and-Paste or Drag-and-Drop, your choice) . Let’s double check if we have successfully moved the data file to the new location; but, we need to put the TEST database back online first:

Let’s query the sys.master_files again to make sure we have our data file where we want it:

[Cross-posted from my other blog http://dbalink.wordpress.com ]

How To: Summarize Aggregated Data Using Pivot in TSQL

marl — Thu, 30 Oct 2008 05:00:00 GMT

This question posted on StackOverflow.com caught my attention. This is a common data requirement specially in Data Analysis. It goes like this:

I have a table like as follows:

SoftwareName    Count    Country
Project         15       Canada
Visio           12       Canada
Project         10       USA
Visio           5        USA

How do I query it to give me a summary like...

SoftwareName    Canada    USA    Total
Project         15        10     25
Visio           12        5      17

How to do in T-SQL?

Here's my solution to the problem - A Walkthrough:

Let's create our test table ("SoftwareDemo")

USE [Test] GO

CREATE TABLE [dbo].[SoftwareDemo]( [SoftwareName] [nvarchar](50) NOT NULL, [Count] [int] NOT NULL, [Country] [nvarchar](50) NOT NULL ) ON [PRIMARY]

Then, let's insert the sample data:

INSERT INTO SoftwareDemo VALUES ('Project', 15, 'Canada') INSERT INTO SoftwareDemo VALUES ('Visio', 12, 'Canada') INSERT INTO SoftwareDemo VALUES ('Project', 10, 'USA') INSERT INTO SoftwareDemo VALUES ('Visio', 5, 'USA')

Here's our PIVOT query:

SELECT Softwarename, Canada, USA, Canada + USA AS TOTAL FROM SoftwareDemo PIVOT ( SUM([Count]) FOR Country IN (Canada, USA) ) AS x

SoftwareName Canada USA Total -------------------------------------------------- ----------- ----------- ----------- Project 15 10 25 Visio 12 5 17

(2 row(s) affected)

(1 row(s) affected)

Here's our PIVOT TSQL in action:

[Cross-Posted from my other blog http://dbalink.wordpress.com - Marlon Ribunal ]

Querying the Object Catalog and Information Schema Views

marl — Fri, 24 Oct 2008 07:49:00 GMT

Okay, these are at least the two ways you can query the Metadata in SQL Server (TSQL). The first one is like what I’ve already shown here, which has the following TSQL code:

SELECT T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE],

P.MAX_LENGTH AS[SIZE],CAST(P.PRECISION AS VARCHAR) +‘/’+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]

FROM ADVENTUREWORKS.SYS.OBJECTS AS T

JOIN ADVENTUREWORKS.SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN ADVENTUREWORKS.SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC=‘USER_TABLE’;

The TSQL above is querying the Object Catalog Views. For like any other querying tasks in SQL Server, there are lots of options in doing the same thing. To achieve a similar result as what the above TSQL will return, we can query the SQL Server Information Schema Views. Here’s how:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
       COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
       DATETIME_PRECISION
FROM ADVENTUREWORKS.INFORMATION_SCHEMA.COLUMNS

The Object Catalog Views contain the information about the database objects such as partitions, procedures, constraints, events, tables, views, triggers, etc.

The Information Schema Views are also another way of looking at the same metadata. “Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA. [MSDN]“

[Crosss-Post from my other blog http://dbalink.wordpress.com/ - Marlon Ribunal]

The “ALL” Parameter In SQL Server Reporting Services 2005

marl — Sun, 19 Oct 2008 01:58:00 GMT

One of the most common business requirements for SQL Server Reporting Services is that an SSRS Report should be dynamic. Dynamic in a sense that the report consumer can query the report in a variety of ways. In this post, I will demonstrate how you can add a parameter value “ALL” to return all of the desired result.

I have four parameters in this report; all of them are optional (can be NULL). I will demonstrate the optionality in a different post. For the mean time, let me demonstrate how to add “ALL” in a parameter.

My parameter, which we alias here as “param1″, is optional. Here is how I created my statement in TSQL and wrapped it in a Stored Procedure:

As you can see, a simple “Union ALL” statement can do the job. I can include NULL value in param1 because that parameter is meant to be optional in the report.

Here is the result and the intended purpose of the value “ALL” in param1.

[A Cross-Post from my other blog http://dbalink.wordpress.com ]

-Marlon Ribunal

How To: Create Dynamic File System Directory Inside TSQL

marl — Mon, 14 Jul 2008 16:24:00 GMT

I have created a Backup Script to backup all the databases in our SQL Server 2005. I want to keep old backups for sometime for some reason; so I want to create subdirectory with the run date as the name of the folder to separate the set of backups by date. I need to use DOS Command from within my TSQL Code to create the subdirectory. This feature is turned off by default. I am making sure that I am turning this off after I run the script:

EXEC SP_CONFIGURE 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC SP_CONFIGURE 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Then here's how I execute DOS Command from within my TSQL:

DECLARE @path VARCHAR (200) -- Path of the Backup Files
DECLARE @folderdate VARCHAR (50) -- The subdir for my backups with Format YYYYMMDD
DECLARE @cmd VARCHAR (4000) -- The command to create Subdir
DECLARE -- Other Variables for my backup scripts...

SELECT @folderdate = CONVERT(VARCHAR(20), GETDATE(), 112) -- gives us YYYYMMDD

SET @path = N'C:\Backup\' + @folderdate + '\' -- Path as C:\Backup\YYYYMMDD

SELECT @cmd = 'md "' + @path + '"'

EXEC  master..xp_cmdshell @cmd, no_output

-- now I can direct all the backup file to the created subdirectory
-- like, 
-- SET @filename = @path + [other_variable/s] + '.BAK'

For security reason, I want to make sure that I turn my xp_cmdshell off. So at the very end of my script:

EXEC SP_CONFIGURE 'xp_cmdshell', 0
GO
RECONFIGURE
GO
EXEC SP_CONFIGURE 'show advanced options', 0
GO
RECONFIGURE
GO

--Marlon Ribunal Please Help Me: Here's How <Link>

[As posted in my blog at http://dbalink.wordpress.com]

How To: Rename SQL Server 2005 Database Using TSQL

marl — Thu, 10 Jul 2008 08:26:00 GMT

For some reasons, DBA's need to rename their Databases. One of the reasons might be to comply to Best Practices Policy changes. Here we are going to rename our Database named "Bad_Ass_DB" into "Best_Practice_DB".

This is how Database renaming with TSQL is done in SQL Server 2005:

First we need to put our Bad_Ass_DB offline or alter it to single-user mode. You cannot rename a database if it is online or in multi-user mode. And prior to that, there should be no active connection to the database (user, application, etc):

USE master
ALTER DATABASE Bad_Ass_DB
SET single_user
GO

Now we can rename the Database:

ALTER DATABASE Bad_Ass_DB
MODIFY NAME = Best_Practice_DB
GO

Then we can set the database back to multi-user mode. Then, real hard work begins: mapping all your codes, processess, applications, web.config, etc to the new name.

ALTER DATABASE Best_Practice_DB
SET multi_user
GO

-Marlon Ribunal Please Help Me: Here's How <Link>

[As Posted in my other blog http://dbalink.wordpress.com/]

SCREENCAST: SQL SERVER 2005 TRIGGER 101

marl — Fri, 20 Jun 2008 06:21:00 GMT

This is my first screencast ever. I don't know where to post this so I just archived and upload it to Sky Drive. You can find the dowload link below:

http://cid-7db5650814ac4824.skydrive.live.com/self.aspx/Public/DevPinoySQLServerTriggerDemo.rar

Password of the .rar file: devpinoy.org

-Marlon Ribunal

Finding All User Tables and Their Columns & Data Types In SQL Server Using Object Catalog Views

marl — Mon, 16 Jun 2008 07:09:00 GMT

Here's a quick TSQL solution that you can use to find all the User Tables and their Columns, inluding Data Types, and Column size. This is useful when you need a quick way of finding info on creating your Database's documentation. Whenever I am asked to document a new system/application or review an existing one, I always want to start from the very core of the system - mostly, this is a Database backend.

SELECT t.name AS [TABLE Name], c.name AS [COLUMN Name], p.name AS [DATA Type], p.max_length AS[SIZE], CAST(p.PRECISION AS VARCHAR) +'/'+ CAST(p.scale AS VARCHAR) AS [PRECISION/Scale]
FROM sys.objects AS t
JOIN sys.columns AS c
ON t.OBJECT_ID=c.OBJECT_ID
JOIN sys.types AS p
ON c.system_type_id=p.system_type_id
WHERE t.type_desc='USER_TABLE';

-Marlon Ribunal

[Cross-Posted From My Other Blog http://dbalink.wordpress.com]

Isolation Levels and Locks in SQL Server 2005

marl — Wed, 28 May 2008 09:18:00 GMT

One of the most popular trick questions in most DBA interviews is about Isolation Level/Locks. The same is true with any jobs related to BI and Data Warehouse.

It is a common thing that sometimes we come across errors that came out of anomalous data caused by conflicting transactions and concurrency issues. Dirty Reads only give you Dirty Data. Data inaccuracy is real dirty!

The only way to protect transactions that are prone to conflicts with other transactions is to "Isolate" them from the others. How to protect these critical transactions? Obtain "Locks".

I found this cool introductory to Isolation Level in SQl Server 2005. This article has a downloadable article that came with it. Just in case you miss it, here's the pdf that runs down the Isolation Levels in SQL Server 2005.

	Dirty Reads	Lost Updates	Nonrepeatable reads	Phantom reads	Concurrency model	Conflict Detection
Read Uncommitted	Yes	Yes	Yes	Yes	Pessimistic	No
Read Committed	No	Yes	Yes	Yes	Pessimistic	No
Repeatable Read	No	No	No	Yes	Pessimistic	No
Serializable	No	No	No	No	Pessimistic	No
Snapshot	No	No	No	No	Optimistic	Yes
Read Committed Snapshot	No	Yes	Yes	Yes	Optimistic	No

You will find the explanation of the this table in the downloadable pdf.

Thanks to Philippe Almog ("SQL Practices: RDBMS Programming") for this helpful resources. You can find related topics in his site, http://sqlpractices.wordpress.com/.

Out of topic (Bonus!), you can download a copy of the "SQL Server 2005 Failover Clustering" White Paper here. This White Paper is a "comprehensive document about implementing failover clustering for SQL Server 2005 and Analysis Services."

-Marlon Ribunal

[Cross-Posted from my other blog http://dbalink.wordpress.com]

TDD is Gaining Some Ground

marl — Wed, 14 May 2008 04:15:00 GMT

Here's what Steven Harman has found out in a recent trip: TDD is Gaining Some Ground somewhere...

[Taken by Steven Harman at Crowne Plaza front desk in West Michigan]

“SQL Server and .NET Training and Career Development” by Douglas Reilly

marl — Sat, 10 May 2008 09:52:00 GMT

[This 3-part series was written by Douglas Reilly who died early 2007. The articles were written in February to June of 2006.]

This is the Part I of the series: The Value of Conferences

And here is the Part II: The Benefit of Forum

Finally, Part III: Importance of Books and the Constant Change

Author profile: Douglas Reilly

The late Douglas Reilly was the owner of Access Microsystems Inc., a small software development company specializing in ASP.NET and mobile development, often using Microsoft SQL Server as a database. He died early in 2007 and is greatly missed by the SQL Server community as one of the industry’s personalities.

[Courtesy of http://www.simple-talk.com]

Please do read these articles with much solemnity. And I broke into tears when I read this. Here's a picture of Doug and his wife, Jean

-Marlon Ribunal

System.Net.Mail - Everything That You Need To { know }

marl — Sat, 10 May 2008 05:56:00 GMT

All the possible things you need to know about System.Net.Mail

http://www.systemnetmail.com/default.aspx