in Search

αρχάριος

greek, noun, pron. "archarios" - beginner, learner, neophyte, novice
  • Creating SQL Server Login From A Domain Account To Access System Directory

    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:

    errordirectoryinfo

    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:

    windowslogin

     

     kick it on DotNetKicks.com

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

  • How To: Moving Data Files

    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:

    datafilemove11

    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:

    datafilemove2

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

    datafilemove31

    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:

    datafilemove4

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

    datafilemove5

     

     

    kick it on DotNetKicks.com

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

  • How To: Summarize Aggregated Data Using Pivot in TSQL

    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] [intNOT 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

    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]“

    kick it on DotNetKicks.com

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

     

  • The “ALL” Parameter In SQL Server Reporting Services 2005

    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

    kick it on DotNetKicks.com

     

  • How To: Create Dynamic File System Directory Inside TSQL

    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:

    1. EXEC SP_CONFIGURE 'show advanced options', 1
    2. GO
    3. RECONFIGURE
    4. GO
    5. EXEC SP_CONFIGURE 'xp_cmdshell', 1
    6. GO
    7. RECONFIGURE
    8. GO

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

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

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

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

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

    8. EXEC master..xp_cmdshell @cmd, no_output

    9. -- now I can direct all the backup file to the created subdirectory
    10. -- like,
    11. -- 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:

    1. EXEC SP_CONFIGURE 'xp_cmdshell', 0
    2. GO
    3. RECONFIGURE
    4. GO
    5. EXEC SP_CONFIGURE 'show advanced options', 0
    6. GO
    7. RECONFIGURE
    8. GO

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

    kick it on DotNetKicks.com

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

  • How To: Rename SQL Server 2005 Database Using TSQL

    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):

    1. USE master
    2. ALTER DATABASE Bad_Ass_DB
    3. SET single_user
    4. GO

    Now we can rename the Database:

    1. ALTER DATABASE Bad_Ass_DB
    2. MODIFY NAME = Best_Practice_DB
    3. 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.

    1. ALTER DATABASE Best_Practice_DB
    2. SET multi_user
    3. GO

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

     kick it on DotNetKicks.com

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

  • Help Me Raise Fund For My Mother With Leukemia

    Donation Through PayPal is now accepted at: http://www.digitalcharity.com/m.php?id=31896

     See my related posts:

    http://dbalink.wordpress.com/2008/07/02/help-me-raise-fund-for-my-mother/

    http://dbalink.wordpress.com/2008/06/24/i-need-help-to-save-my-dying-mother/

     

    DONORS

    ***Special Thanks to Brent Ozar (http://www.brentozar.com/) for the Help. Thank you Brent for responding to my call for help. May God continue to shower forth His Blessings unto you and your family now and always!

    ***Thank you to my CDG Family. Thank you for the outpouring of support that is beyond my personal expectations. Thank you for the gift and Prayers. God Bless us all!

    ***Estebat Family of Long Beach, CA. Thank you for the Love and Prayers

    ***Thank you Curtis Pilon for responding to my call for help.

     -Marlon Ribunal

    mribunal@verizon.net

  • SCREENCAST: SQL SERVER 2005 TRIGGER 101

     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

    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.

    1. 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]
    2. FROM sys.objects AS t
    3. JOIN sys.columns AS c
    4. ON t.OBJECT_ID=c.OBJECT_ID
    5. JOIN sys.types AS p
    6. ON c.system_type_id=p.system_type_id
    7. WHERE t.type_desc='USER_TABLE';

    -Marlon Ribunal

     kick it on DotNetKicks.com

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

  • Isolation Levels and Locks in SQL Server 2005

     

    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
    kick it on DotNetKicks.com

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

     

  • TDD is Gaining Some Ground

     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]

    Posted May 13 2008, 09:15 PM by marl with 2 comment(s)
    Filed under: ,
  • “SQL Server and .NET Training and Career Development” by Douglas Reilly

    [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

    kick it on DotNetKicks.com
  • System.Net.Mail - Everything That You Need To { know }

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

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

  • Following Keith Rull on twitter

    Follow the master's steps... @ http://twitter.com/keithrull

    Cool stuff!

    Posted Apr 17 2008, 06:01 PM by marl with 3 comment(s)
    Filed under:
More Posts Next page »