Finding Slow Running Queries in ASE 15
Every application encounters the occasional performance problem. With ASE 12.5, Sybase introduced the MDA tables to identify all server activity. ASE 15 introduced a variety of ways to identify session metrics, SQL, and query plans to help identify issues as they crop up.
A More Advanced Pie Chart for Analysis Services Data
MSAS Architect Bill Pearson leads the hands-on creation of a somewhat more advanced Reporting Services pie chart, using dual category groups and a series group based upon an Analysis Service data source
Performance Testing SQL Server 2008's Change Data Capture Functionality
Part 24 of "Developing a Complete SQL Server OLTP Database Project" tests the performance of SQL Server 2008's Change Data Capture functionality. How does it perform compared to a system with no change-tracking functionality?
How to Migrate from BasicFiles to SecureFiles Storage
This article - second in a three-part series - explores how to migrate most efficiently from BasicFiles to SecureFiles storage, how to measure the relative storage efficiency of SecureFile vs. BasicFile LOBs, and how to utilize the different compression and deduplication options for SecureFile LOBs.
User Personalization with PHP: Beginning the Application
In this tutorial we will build an online bookmark system. This will be used to create a database for storing all our URL's and their descriptions. By creating our own, we have full control over our application, allowing us to tweak it to suit our needs.
Whats in an Oracle Schema?
A fairly common question on Oracle Q&A forums is how to identify schemas within a database. Many times, a quite misleading answer is to query owner names from DBA_TABLES. This answer is only correct for those schemas (owners) who own tables.
Making Your Own Access 2007 Ribbon
This article provides inspiration to get into Microsoft Access 2007 ribbons, including a review of a tool that will get you started.
The LEVEL_UNIQUE_NAME Intrinsic Member Property
SSAS Architect Bill Pearson examines another intrinsic member property, LEVEL_UNIQUE_NAME, which allows us to exercise a great deal of presentation "sleight of hand" in working with MDX in Analysis Services, as well as within Reporting Services and various other reporting applications that can access an Analysis Services cube.
Finding the Worst Performing T-SQL Statements in an Instance
Greg Larsen introduces some Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) to help monitor your application's T-SQL performance. In addition, he provides a Stored Procedure that utilizes the DMVs and DMFs to produce a report that quickly identifies poorly performing T-SQL statements.
Service Broker Transactional Support in SQL Server 2005 Express Edition
This article provides an overview and presents an example illustrating the role of transactions in processing Service Broker dialogs usage.
Restoring An Analysis Services Database
Learn how to restore an Analysis Services database to the same or different location, from a database backup file with password protection.
Working with FILESTREAM using VB .NET
FILESTREAM is a useful new feature in SQL Server 2008 that allows you to work more efficiently and easily with unstructured data. This article discusses the benefits of FILESTREAM, and demonstrates how to enable FILESTREAM on a SQL Server instance and create a FILESTREAM-enabled database and table.
More Exposure to Settings and Properties in Analysis Services Attribute Relationships
This article concludes our overview of Attribute Relationships within an extended examination of the dimensional model of the integrated Microsoft Business Intelligence solution.
Advanced MySQL Replication - Improving Performance
MySQL Replication can be made quite reliable and robust if the right tools are used to keep it running smoothly--but what if enormous loads on the primary server are overloading the slave server. Are there ways to speed up performance, so the slave can keep up?
Changing the Word Size in Oracle
Changing the word size in Oracle appears to be pretty simple, but this is one of those situations where doing some additional digging into support notes can alleviate to outright prevent some problems down the road.
Spicing Up Your Web Services with XSLT
The first thirteen parts of this series introduced some of the many features available within the IBM Data Studio integrated development environment (IDE) that's available for use with the IBM data servers. This installment explains how to apply Extensible Stylesheet Language Transformations (XSLT) to your Web services.
Security Context of Service Broker Internal Activation
The previous installment of "SQL Server 2005 Express Edition" discussed Service Broker's internal activation, which allows you to automate communication between initiator and target. Unfortunately, there are some caveats related to its security context. This article describes their specifics and provides a couple of methods to eliminate any undesirable side effects they introduce.
Intrinsic Member Properties: The HIERARCHY_UNIQUE_NAME Property
This month's installment of "MDX Essentials" examines the HIERARCHY_UNIQUE_NAME intrinsic member property. Join SSAS Architect Bill Pearson in an introduction of this intrinsic member property, which Bill complements with hands-on exercises and sample uses.
Restoring an SSAS database using Windows PowerShell and SQL Server 2008 AMO
Learn how to restore an Analysis Service database from a backup file.
Cloud Computing with Amazon Simple DB
Simple DB is a database web service offered by Amazon.com. This article introduces the Simple DB service itself as well as the concepts needed to work with it.
Simple Pie Chart for Analysis Services Data
MSAS Architect Bill Pearson leads the hands-on creation of a simple pie chart based upon an Analysis Service data source.
SQL Server Re-indexing Tips
Defragment or rebuild? This guide will help you decide and assist you in developing a database maintenance plan that balances performance, uptime and ease of management.
SecureFiles in Oracle Database 11g
With the introduction of SecureFiles, Oracle Database 11g dramatically expands the storage capabilities for Large Objects (LOBs) within an Oracle database. This article the first in a two-part series discusses how LOBs have expanded their domain within databases over the past decade, looks at several potential future uses for LOBs, and introduces the new SecureFile LOB object model.
Let's Get Creative! Making Your Own Custom Data Collections
Continuing his discussion on SQL Server 2008 Data Collections, Greg Larsen explains how to create your own custom Data Collections.
Oracle Support Notes: "Complete Guides" Generally Aren't
In the same way that friendly fire really isn't, it is not uncommon to encounter "Complete Guides" on MetaLink which aren't.
Service Broker Activation in SQL Server 2005 Express Edition
Recent installments of our series dedicated to SQL Server 2005 Express Edition have discussed Service Broker functionality, focusing on methods of securing its conversations. This article presents a mechanism, known as activation, which eliminates the need for manual intervention into communication between Service Broker partners.
SQL Server 2008's Change Data Capture Tracking the Moving Parts
Continuing his series on "SqlCredit Developing a Complete SQL Server OLTP Database Project," Rob Garrison begins a discussion on tracking the changes that take place when SQL Server 2008s Change Data Capture feature is started on a user table.
Attribute Relationships: Settings and Properties
Bill Pearson continues his overview of Attribute Relationships within his extended examination of the dimensional model within the integrated Microsoft Business Intelligence solution. In this article, we begin a hands-on overview of the properties underlying attribute relationships, along with a review of the respective settings associated with each property.
Backing Up Analysis Service Database Using Windows PowerShell and SQL Server 2008 AMO
Part 5 of our series illustrates how to backup an Analysis Service database using Windows PowerShell and SQL Server 2008 AMO.
SQL Server Database File I/O Report
Yan Pan explains how to produce a simple SQL Server database file I/O report that helps resolve disk I/O bottleneck by identifying the files to move off busy drives and to estimate the I/O activities after file moves.
Intrinsic Member Properties: The DIMENSION_UNIQUE_NAME Property
SSAS Architect Bill Pearson exposes the DIMENSION_UNIQUE_NAME intrinsic member property, as he leads a hands-on exercise providing sample uses.
Intro to MySQL Proxy
It's no surprise that the concept of a proxy has made its way into the database arena. The MySQL Proxy sits between your application and your MySQL database. Future articles will discuss the myriad of uses for this technology.
Oracle Concepts: Splitting Hairs
Learn how to tell the difference between Oracle concepts that appear to be the same at one level, but are in fact different.
Access forms and DB2 Data Server - A Perfect Match
Paul Zikopoulos continues his series on DB2 9 and Microsoft Access 2007, explaining in this installment how to build forms that run against DB2 data servers.
Use Access 2007 to Get Started in Data Mining
Learn how you can use Microsoft Access 2007 as a basic data mining tool for exploring your valuable data. This article illustrates how data filters, pivot graphs, queries in graphs and filters in reports can help this cause.
Configuring Anonymous Dialog Security in SQL Server 2005
Recent installments of our series have discussed configuring Service Broker full dialog level security, which required a mechanism to facilitate the exchange of certificates between communication partners. In this article, we will present an alternative approach, which eliminates this requirement by relying on anonymous dialog security.
What Is Your Definition of Database Workload?*
Providing a definition for database workload can be a daunting task...unfortunately, it depends on what youre trying to accomplish.
Using Windows PowerShell and AMO to create an SSAS Database
Learn how to create an Analysis Service Database using Windows PowerShell and AMO
FileMaker Pro 10 Dresses Up for 2009
A new interface designed to improve productivity and ease-of-use, without sacrificing familiarity, tops the many changes in this venerable database application.
Grouping with SQL Server 2008
Don Schlichting examines several SQL Server 2008 grouping functions including GROUP BY, GROUPING SETS, CUBE, and ROLLUP. In addition, he demonstrates how to use SUM and COUNT in a WHERE clause after rows have been grouped.
Conducting Service Broker Conversation Using Full Dialog Security in SQL Server 2005 Express Service
In the previous installment of our series covering the most relevant features of SQL Server 2005 Express Edition, we started an overview of Service Broker full dialog-level security. In this article, we will conclude this subject by describing the remaining prerequisites and a method to invoke a secure dialog.
Simple Bar Chart for Analysis Services Data
Join MSAS Architect Bill Pearson as he leads the hands-on creation of a simple bar chart based upon an Analysis Service data source.
Oracle Database 11g: Flashback Transaction Backout
Oracle Database 10g offered two new Flashback features that allowed an authorized user to see all versions of any changes made to one or more rows in a table. Database 11gR1 provides the ability to back out one or more independent or dependent transactions with Flashback Transaction Backout.
Multilingual linguistic searching and sorting in Oracle
How well do you know the sorting rules of your own language? Chances are, Oracle knows them much better than you, in addition to knowing all the rules for all of the languages Oracle supports. Take a tour through Oracle's multilingual linguistic searching and sorting features.
Testing your Web services using the Data Web Services Test Client
Paul Zikopoulos introduces you to the Data Web Services Test Client that's available in IBM Data Studio Version 1.2 or later.
Introduction to Attribute Relationships in MSSQL Server Analysis Services
Join BI Architect Bill Pearson as he introduces Attribute Relationships into his extended examination of the dimensional model within the integrated Microsoft Business Intelligence solution
Setting Up Right Click Menus for Access 2007
Learn how to create right click (Short Cut) Menus in Microsoft Access 2007.
Proxy Accounts in SQL Server
Yan Pan explains how to set up proxies in SQL Server 2000, 2005 and 2008, and compares the differences between them.
Administering MySQL Databases on the Web Using PHP
This week you'll learn how to access a database from the Web using PHP. Inserting new items into the database is similar to getting items out of the database. You follow the same basic steps: make a connection, send a query, and check the results.
Accessing Your MySQL Database from the Web with PHP
This week you'll find out how to access the Book-O-Rama database from the Web using PHP. A sampling of key topics covered include: how Web database architectures work, choosing a database to use, querying the database, using other PHP-database interfaces and more.
Configuring Transport Encryption in SQL Server 2005 Express Service Broker Conversation
In the recent installments of our "SQL Server 2005 Express Edition" series we have been discussing mechanisms that facilitate authentication of distributed systems participating in Service Broker conversations. This article discusses use of digital certificates in encrypting their content on the transport level.
Combine BottomCount() with Other MDX Functions to Add Sophistication
Discover, through a hands-on practice exercise, a more sophisticated business use for the MDX BottomCount() function, in combination with the Generate(), Descendants(), Ancestor() and other functions we have explored within our series. Join BI Architect Bill Pearson in this, the second part, of an extended examination of the MDX BottomCount() function.
Searching and Sorting Strings in Oracle
Steve Callan discusses what takes place inside the Oracle database when you're searching for something "LIKE" the value of interest. How does Oracle approach the problem of determining if the string 'ABCD' appears in the string 'ABCABDABCDAB' and if it does, how many times?
Preparing To Upgrade Access Tables to SQL Server 2005/2008
Garry Robinson discusses some of the subtleties that you can apply to your database tables prior to upsizing your tables to SQL Server. In this article, I will show you how to fix up some of your Access backend before you make the conversion.
Microsoft Windows PowerShell and SQL Server 2008 AMO
The first installment of this series illustrating the power of Windows PowerShell in conjunction with SQL Server 2008 demonstrates how to install and use a simple PowerShell command and a simple AMO command.
Transforming Business Logic into Web Services Using DB2 9.5 and IBM Data Studio
Learn how to take business logic such as stored procedures, functions, or SQL statements and turn them into a Web service.
SQL Server: Measuring Space Allocation and Index Distribution
One of the housekeeping challenges DBAs face is to keep track of their databases' growth and how much space is being used by each table and index in those databases. SpaceUsedAnalyzer extends the functionality of the spaceused system stored procedure to present information that is more detailed.
MySQL Replication Pitfalls
Sean Hull examines some of the issues and problems with MySQL replication, offering examples to show why they are issues.
Installing a Two-node SQL Server 2008 Cluster - Advanced Option
In Part 2 of this series, we showed you a step-by-step example of Integrated installation of a SQL Server 2008 cluster. In this article, we will move onto the other installation option - Advanced/Enterprise installation.
Joining Disparate Data in an Oracle Database
Learn how to join disparate or seemingly unrelated data using an Oracle-provided pseudo column to create the linkage.
SQL Server Express' Service Broker Conversations
In the previous installment of our series, we started discussing the use of certificates in authenticating Service Broker conversations in a distributed environment This installment presents the remaining steps necessary to establish Service Broker dialog in the distributed environment while leveraging certificate-based authentication.
SQL Server 2008 Recovery Models and Backups
This article examines the Recovery Model options used by SQL Server 2008 and the various methods that can be used to backup a database such as Differential and Transaction Logging. In addition, Transaction Logs, the mechanism that makes Recovery Models possible will be introduced.
Using PL/SQL or ADR to Check Oracle's Health
Learn how to use health checks to stay on top of corruption in your Oracle database.
Using PowerShell to Get SQL Server Connection Information
Part 8 of our discussion on how to check SQL Server using Windows PowerShell examines how to get the SQL Server connection information.
Managing Multiple Databases on a Single Server
Today's hardware can handle the demands of a couple of SQL databases on a single server. But peaceful coexistence requires some thoughtful planning if you are to maintain those SLAs.
Attribute Member Values in Analysis Services
Learn how the appropriate use of attribute member Value can support the selection and delivery of enterprise data in a more focused and consumer-friendly manner. Join BI Architect Bill Pearson in a hands-on examination of the attribute member Value property and its underlying settings in Analysis Services.
Top 10 Cool New Features In SQL Anywhere 11
It's time for the SQL Anywhere Oscars, when folks tune in to see which of their favorite features were picked, which weren't and to see some weird stuff... it's all here.
ADDM Enhancements in Oracle Database 11g
This article illustrates how Oracle Database 11gR1 has expanded ADDM to encompass Real Application Clusters (RAC) database tuning at the global level and provided the ability to filter out previously identified performance issues from any ADDM analysis report.
Introducing Reporting Services Charts for Analysis Services
BI Architect Bill Pearson introduces a group of related articles surrounding chart reports for Analysis Services data sources. In this article, we examine chart types that are available, and look forward to coming articles that specify details involving the use of each in reporting Analysis Services data.
Successful Database Migration
One of the issues that companies are facing in their operations is database migration to new hardware, or a different operating system platform, or even a new database vendor. This article describes the overall process.
Reports for SQL Server 2008 System Data Collections
With SQL Server 2008's new Data Collection feature and the Management Data Warehouse (MDW), came three system Data Collection sets. This article covers the different data collection standard reports that have been provided to display information related to the statistics collected by the system data collections
Exploring SQL 2005's Ranking Functions NTILE() and ROW_NUMBER()
Part 20 of the "SqlCredit Developing a Complete SQL Server OLTP Database Project" examines SQL 2005s NTILE() and ROW_NUMBER() Ranking Functions.
Hidden Treasures in Oracle's STATSPACK Utility
STATSPACK is still very much a viable option for monitoring and diagnosing database performance. Even though it seems as if AWR and ADDM have voted to send STATSPACK to Exile Island, treasures and rewards can still be found, but it's up to you to find them and make the most of what's available.
Configuring Certificate-based Authentication
Implementing a dialog between two services residing in a distributed environment requires the presence of an authentication mechanism. Windows-based Kerberos protocol limits the scope of systems participating in a Service Broker dialog to those residing in the same or trusted Active Directory domains. This article provides an overview of how to eliminate this limitation by employing certificates.
Oracle with PHP: A Developer's View
This article is for the developer who wants to use the PHP scripting language and an Oracle database to develop their application. There are different methods of using PHP with Oracle, but we will concentrate on using OCI8 and the ADOdb abstraction library.
Searching For Text Anywhere In a Database
Thanks to internet search engines, users are used to searching for text but unfortunately even the desktop search tools can't look into tables (easily). This article outlines a simple tool to search Access tables or linked tables to find if a text string(s) exists in those tables.
Optimizer Hints in Oracle 11g
Oracle has many hints to choose from; know what they are and how they are different from one another before tossing them into production.
Check your SQL Server Using Windows PowerShell - Part 7
This installment discusses how to get information on the TOP 10 queries based on the CPU usage.
Death of the Relational Database?
A panel at this week's Web 3.0 Conference & Expo in Santa Clara will explore the issue. Heres a preview.
Intro to Oracle's Automatic Workload Repository (AWR)
Oracle's Automatic Workload Repository improves quite a lot on the previous statspack; it installs automatically, has some fairly reasonable default settings, and brings us Active Session History as well.
MSSQL Analysis Services - Attribute Member Names
Business Intelligence Architect Bill Pearson continues his exploration of Attribute Members, in another member of a subseries surrounding significant components of the Analysis Services dimensional model. In this article we examine the attribute member Name property, whose appropriate use is important to good model design and overall system performance.
SQL 2008 Backup and Restore - Part 1
This article examines backing up and restoring SQL Server databases. In addition to covering standard backup options such as Full Backups and Differential Backups, new SQL Server 2008 features such as Backup Compression will be introduced.
Oracles ADR Command Interpreter (ADRCI) - Part 3
Oracles new ADR with command interface shows promise for DBAs to quickly investigate diagnostic information. This article takes a deeper look at ADRCIs commands.
Check your SQL Server using Windows PowerShell Part 6
Part 6 of this series examines how to check all of the databases available in the SQL Server instance and query the database properties.
Oracle Database 11g: Adaptive Cursor Sharing
This article discusses a new Oracle Database 11gR1 feature - Adaptive Cursor Sharing - and illustrates how to use it in practical situations to limit hard parsing of SQL statements with bind variables while increasing the relative execution efficiency of those statements.
SQL Server 2008 Data Collections and the Management Data Warehouse
Data Collections are a great tool that allows DBAs to gather different statistics, and the Management Data Warehouse (MDW) is the repository for storing those statistics. This article offers an overview of Data Collections and how to configure the MDW.
SqlCredit - Part 19: Exploring SQL 2005s Ranking Functions
Part 19 digs deep into two of SQL 2005's ranking functions: RANK() and DENSE_RANK(). It includes performance considerations and using CTEs to avoid table variables.
The Missing Sync
This article offers an overview of what is needed in a robust data synchronization system.
The DBMS_PROFILER package has been around since at least Oracle8i days, yet it seems to get short shrift in many of the tuning books and online discussion forums. Learn how easy it is to implement and perhaps you'll add this built-in to your tuning arsenal.
Mastering OLAP Reports: Parameterized Grouping
The ability to choose grouping criteria at run time can mean the selection and delivery of enterprise data in a more focused and consumer-friendly manner. It also means doing more with fewer reports. BI Architect Bill Pearson leads a hands-on demonstration of a way to parameterize grouping with runtime sorting options.
SQL Server 2005 Express Edition - Part 31: Distributed Service Broker Environment - Routing
The previous installment of our series began an overview of a distributed Service Broker environment. This article describes the remaining steps necessary to facilitate communication between services residing on separate computers in the same Windows domain, including assigning permissions to execute Service Broker-specific tasks and defining Adjacent Broker Protocol routes.
Attribute Member Keys - Part 2: Composite Keys
Business Intelligence Architect Bill Pearson continues his exploration of Attribute Member Keys in another member of a group of articles surrounding significant components of the Analysis Services dimensional model. In this article we resume our examination of Attribute Member Keys, focusing upon composite keys and their properties.
Check your SQL Server using Windows PowerShell - Part 5
This fifth installment of the Check your SQL Server using Windows PowerShell series illustrates how to access SQL Server instance properties and SQL Server configuration details using Windows PowerShell.
Setting up a Two-node SQL Server 2008 Cluster from the Command Prompt - Preparation
Part 1 of this series discusses the prerequisites for SQL Server cluster installation. Subsequent installments will provide step-by-step examples of SQL Server 2008 cluster installation.
MS Access and MySQL
Learn how to set up an off-site copy of a working MySQL database, an Access version of the same back-end, and a front-end that could link to either.
Intrinsic Member Properties: The MEMBER_VALUE Property
MSAS Architect Bill Pearson leads hands-on exercises providing exposure to the use of the MEMBER_VALUE intrinsic member property. Join us in generating simple lists, as well as datasets to support report parameter picklists.
Free Hands-on Oracle E-Business Suite Training
Join Steve Callan as he shares several ways to get your hands on E-Business Suite.
DBA Call to Action: How You Do What You Do
Problem solving is high on the skill set that all DBAs should have. A DBA doesn't necessarily need to know how to solve everything but they should have the ability to define a problem, research solutions, and ultimately take action.
SQL Server 2005 Express Edition - Part 30: Distributed Service Broker Environment - Endpoints
In the latest articles of our series dedicated to SQL Server 2005 Express Edition, we have been discussing Service Broker. This article describes the initial setup of components required to demonstrate a distributed implementation, with initiator and target services residing on two separate computers.
What is SQL Server
This refresher on SQL Server examines when to use a database as well when not to, along with some of the key terms used with SQL Server.
Terminate User processes in SQL Server
This article illustrates how to create a simple procedure to kill many sessions at the same time, kill a range of sessions and kill all of the sessions connecting to a database.
Nested Stored Procedure Calls with SQL Server Transactions
Discover how you can utilize SQL Server transactions when nesting calls to stored procedures
Oracle's ADR Command Interpreter (ADRCI) - Part 2
Oracle's new ADR with command interface shows promise for DBAs to quickly investigate diagnostic information.
Attribute Member Keys - Part 1: Introduction and Simple Keys
Join Business Intelligence Architect Bill Pearson as he kicks off an exploration of Attribute Member Keys, a continuation of a body of articles surrounding significant components of the Analysis Services dimensional model. In this article we introduce Attribute Member Keys, focusing upon the simple keys and their properties.
Creating Native Web Services in SQL Server
Expose your SQL Server data directly over the Web. Learn to create SOAP-based native web services in SQL Server 2005/2008 that can be consumed from client applications over HTTP.
Installing Oracle E-Business Suite R12 on Windows 2003
Learn how to install EBS, starting from scratch and walk away with an up and running EBS instance using the Vision Enterprises database.
Oracle Database 11g: PLSQL and OCI Result Set Caching
Oracle Database 11gR1 offers several new performance enhancements that limit "round trips" between database server, application servers, and applications when the identical data is required. The final article in this series explores how the latest release of Oracle extends the ability to cache result sets to applications and PL/SQL functions.
What Kind of DBA Are You?
Depending on the environment in which they work, a DBA might perform a number of different tasks. Greg Larsen identifies these different tasks, categorized by work environments.
SQL Server 2005 Express Edition - Part 29: Implementing Service Broker Conversation
Recent installments of our series discussed the functionality incorporated into the asynchronous messaging framework of Service Broker. We described how to initiate a dialog between two services residing in the same database and exchange messages between them. This article focuses on the process of ending an existing conversation.
SqlCredit, Part 18: Exploring the Performance of SQL 2005s OUTPUT Clause
Part 17 of this series introduced SQL Server 2005's OUTPUT clause. The OUTPUT clause is very useful for pulling "automatic" values out of an INSERT statement (or UPDATE). This article looks at how it performs compared to other options.
Accessing Business Data in WPF Applications
Many WPF demos focus on slick graphics and designer user interfaces. While this is all good, those regular business database applications will eventually make the transition from WinForms to WPF. Learn how to get started accessing business data in your WPF applications.
Using the TFS Build Process to Deploy Sharepoint Custom Applications
Learn how to customize your TFS build process to deploy Sharepoint custom applications. Delve into the pros and cons of using TFS to create these builds.
Audit your Windows domain DBA group using PowerShell
Add or removing a login from a dedicated domain group is easy compared to adding individual DBA logins to every SQL Server box. This article illustrates how to audit such changes on the domain group.
Discover SQL Server TCP Port
Learn how to discover the TCP port for a SQL Server instance from the registry using Windows Powershell.
Why LINQ to SQL is a Better Option than Straight SQL
LINQ to SQL eliminates SQL Injection Attacks and is easier to use than straight SQL to boot.
Mastering OLAP Reports: Parameterizing Number of Top Items with the MDX TopCount() Function, Part 2
BI Architect Bill Pearson continues his hands-on demonstration of an approach to the parameterization of the highly useful TopCount() function.
SQL Server 2008: First Impressions
Pulling your hair out trying to get the SQL Server 2008 Sample AdventureWorks databases installed? If so, you've come to the right place; this article makes it easy(ier).
Intro to PHP and Oracle
Sean Hull points you on the right track to get started with PHP and Oracle.
Business Intelligence Publisher The Other Oracle Reports
BI Publisher is a tremendously powerful reporting tool. It is based on a widely accepted standardized open source language (XML), it is feature rich in terms of what it can produce and it empowers users to query, view, and format their own reports.
Creating Charts in an Access 2007 Database
Garry Robinson fills the gap found in the Access 2007 help manual by providing some much needed help on how to use the charting object in Access 2007.
Intrinsic Member Properties: The MEMBER_UNIQUE_NAME Property
Join MSAS Architect Bill Pearson in hands-on exercises, where we gain exposure to the use of the MEMBER_UNIQUE_NAME property in generating simple lists, as well as datasets to support report parameter picklists.
Oracles ADR Command Interpreter - Part 1
Oracles new ADR with command interface shows promise as a way for DBAs to quickly investigate diagnostic information.
Check your SQL Server using Windows PowerShell: Part 4
Earlier installments of this series illustrated how to ping the host, how to check all of the windows services related to SQL Server and how to check the hardware and operating system information. Part 4 of this series demonstrates how to get hard disk and network adapter information from the host machine.
SQL Server 2005 Express Edition - Part 28: Implementing Service Broker Conversation
Part 28 of this series continues our overview of Service Broker, examining how to initiate a dialog, send a sample message between initiator and target services, process it using a T-SQL RECEIVE statement, and finally observing how the queues and system views change as the result of these events
DB2 9 and Microsoft Access 2007 - Part 3: DB2 Reports
Earlier installments of this series discussed how to use Microsoft Access 2007 as a graphical front end to a back-end IBM DB2 data server and how to leverage Access 2007 and DB2 as an integration layer. This installment examines how to build reports that run against DB2 data servers.
Create Your First SQL Server Database in 3 Quick Steps
The goal of this article is to create an Employees database in SQL Server and populate it with test data in three quick steps.
Oracle Database 11g: SQL Query Result Set Caching
This article - the first in this series - delves into how the result set from a SQL query result set can be retained in the database's Shared Pool for later access by a similar or identical query in the immediate future
Parameterizing Number of "Top" Items with the MDX TopCount() Function - Part 1
Writing high-performance reports against Analysis Services cubes means learning MDX to some extent. Parameterization of MDX functions is one of those things that cannot be delivered solely via graphical MDX Editor. BI Architect Bill Pearson demonstrates a way to parameterize the highly useful TopCount() function.
Working with BLOBs Using SQL Server and ADO.NET
Want to store documents or photos in SQL server? Learn how SQL Server and ADO.NET together can provide an elegant solution for storing and retrieving BLOBs.
SQL Server 2005 Express Edition - Part 27: Implementing Basic Service Broker Objects
Marcin Policht explains the interaction of the primary elements of Service Broker's asynchronous messaging framework and describes a process of setting up the database objects necessary to demonstrate their sample implementation (which will be the subject of our next article).
SqlCredit - Part 17: Exploring SQL 2005s OUTPUT Clause
SQL Server 2005 introduces the OUTPUT clause, a bit of magic that allows us to get the identity value, plus a number of other values, from an INSERT, UPDATE, or DELETE, which all have different interaction with OUTPUT. This article concentrates on INSERT.
SQL Server 2008 MERGE Statement
With a single MERGE statement, you can easily implement insert, update, and delete logic to handle criteria for maintaining a table. Read on to learn how to use the MERGE statement.
Oracle Reports for DBAs
As an Oracle DBA you might think that Oracle Reports have nothing to do with you, and you might be missing out on a simple means of generating reports to identify a source file, create an external table, write an SQL script and more.
Intrinsic Member Properties: The MEMBER_NAME Property
MSAS Architect Bill Pearson introduces the intrinsic MEMBER_NAME property. In hands-on exercises, we gain exposure to the use of the property in generating simple lists, as well as datasets to support report parameter picklists.
Focus on Popular Objects to Speed up an Access 2007 Database
Garry Robinson outlines how usage data can make the quest to speed up an Access database more focused. The article also delves into an Access 2007 ACCDB only feature called TempVars
Check your SQL Server using Windows PowerShell - Part 3
Part 3 of this series illustrates how to acquire operating system and hardware related information from a host machine.
Oracle: MOVE vs SHRINK Commands
This article discusses re-organizing a table using the move and shrink commands, then compares how the rows are compacted within Oracle blocks and how row chaining is resolved
Introducing Visual Studio Team System 2008 Database Unit Testing
Learn how to build Unit tests and generate sample data for your database development projects.
SQL Server Audit in SQL Server 2008: Part 2
Learn how to monitor database-level actions and write audit results to files
SQL Server 2005 Express Edition - Part 26: Service Broker
This article is a brief overview of Service Broker's core features, which are available (in a somewhat limited capacity) in SQL Server 2005 Express Edition. The next installment will describe a sample application illustrating its operations and discuss its management, security, and routing characteristics.
DBA Insider - Useful PL/SQL Packages
Oracle supplies quite a large complement of pl/sql stored procedures, packages, and functions, which can help with some of a DBA's day-to-day needs. This article looks at some of the useful ones that we can add to our DBA or Developer toolbox.
Hands-on Oracle: Backup and Recovery Games - Creating Datafiles
Learn how to recover from the loss of a datafile, when you don't have a backup of that file
Dimension Attributes: Introduction and Overview, Part 5
Join Business Intelligence Architect Bill Pearson as he continues his subseries surrounding components of the Analysis Services dimensional model. In this article we conclude our introduction to dimension attributes, focusing upon the Source group of attribute properties.
Looking at Memory Usage for Oracle Processes
When an Oracle instance starts, or an application uses the Oracle database, the associated Oracle processes are allocated and consume memory. Learn how to use the V$PROCESS view and V$PROCESS_MEMORY to look at this memory usage
SQL Server Profiler - Part 2
Part 1 of this series discussed the difference between a Trace and Profiler. This installment examines additional preconfigured templates, how to synchronizing Profiler and Perfmon and how to run Profiler as a scheduled task
Check your SQL Server using Windows PowerShell - Part 2
Part two of this series illustrates how to access the Windows service on a remote machine using Windows PowerShell and WMI-Object to see if all of the windows services related to SQL Server are up and running.
Oracle Database 11g: Database Replay - Part 3
The final article in this series illustrates how to use Oracle Database 11gR1's new Database Replay (DBR) features to capture and prepare a workload from a current Oracle 10gR2 single-instance database environment and then replay that identical workload in an Oracle 11gR1 Real Application Clusters (RAC) testing environment
Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function, Part 2
BI Architect Bill Pearson continues his hands-on demonstration of an approach to the parameterization of the highly useful LastPeriods() function.
SQL Server 2008 Date Functions - Part 2
To manage and use the new date data types introduced in SQL Server 2008, Microsoft has provided a number of new system functions. In this article, Greg Larsen explores the new system functions and some of the old date functions that were available in older releases of SQL Server.
Working with the XML Data Type of SQL Server
Store XML data directly in SQL Server. Use the power of the XML data type to store and manipulate XML data efficiently and easily.
LINQ to Entities Preview
ADO.NET 3.0 and the ADO.NET Entity Framework are designed to let you spend less time writing SQL and database plumbing code and more time working in the space of the problem you are trying to solve.
Product Review: Toad Data Modeler 3
Quest Software has hit the ball out of the park with its Toad Data Modeler 3 (TDM) application. Ease of use, a huge amount of functionality, and an outstanding support base all combine to make TDM a superb product.
Enhancement in Variable Declaration SQL Server 2008
Enhanced variable declaration, one of the many enhancements that Microsoft added to SQL Server 2008 allows you to declare and initialize a value at the same time. This article shows you how to do it.
Implementing Upgrade of SQL Server 2005 Express Edition
In the previous installment of our series, we presented a variety of reasons that might influence your decision to upgrade a full-fledged member of the SQL Server 2005 family. This article discusses the actual implementation of this process, pointing out additional factors that might affect its complexity.
Intrinsic Member Properties: The MEMBER_KEY Property
Join BI Architect Bill Pearson in an introduction to the intrinsic MEMBER_KEY property. In hands-on exercises, we gain exposure to the use of the property in generating simple lists, as well as datasets to support report parameter picklists.
Check your SQL Server using Windows PowerShell - Part 1
This new series examines methods and procedures to check the status of the Operating system, SQL Server instances and databases, using Windows PowerShell. Part One illustrates how to create a PowerShell script to ping the host machine and how to source the PowerShell function and call the function.
Secure Microsoft Access Passwords and Encryption in Access 2007
Learn how to improve on what already is a very significant security improvement in Access 2007--database encryption.
A Database-Driven Reporting Application
Discover how reporting applications can adapt to changing reports by shifting its report-related information into a database.
Mastering OLAP Reports: Parameterizing Number of "Look Back" Periods with the MDX LastPeriods() Function
Parameterization of MDX functions is one of those things that cannot be delivered solely via graphical MDX Editor but then, you really didnt think that you could avoid actually touching MDX at all, did you? BI Architect Bill Pearson demonstrates a way to parameterize the highly useful LastPeriods() function.
Beginner's Guide to SQL Server Database Development with VSTS Database Edition
Learn how to make Visual Studio Team Suite Database Edition part of your development process.
Oracle Automatic Storage Management
ASM provides many benefits, but also requires learning new concepts, commands, utilities, and administration tasks. Sean Hull examines what ASM solves, and what it takes to manage and weigh the pros and cons.
DB2 and Visual Studio 2008: Getting Started
Paul Zikopoulos gets you started on the path to DB2 9 and Visual Studio 2008 for .NET developers including hints and tips to get you in the express lane when it comes to enabling .NET developers to build DB2 data-bound applications.
Oracle Applications Reference Books
Aside from Oracle's own documentation covering Oracle Apps and a few books covering specific modules, the published books landscape regarding EBS is fairly barren. There are two notable generalist coverage books available that can help you get a good jump on learning Oracle Applications.
SQL Server 2005 Express Edition - Part 24
Part 24 of SQL Server 2005 Express Edition reviews the most common reasons that influence a decision to upgrade to a Workgroup, Standard, or Enterprise member of SQL Server 2005 family.
An Oracle Tune-Up: Tuning Your SQL for Maximum Performance
Nothing impacts application performance like a badly running SQL query. Learn how to optimize query performance to make your Oracle applications faster.
Database Development in Rational Application Developer 7
Learn how to establish a database connection, set up a data development project, and work with SQL Builder.
SQL Server Audit in SQL Server 2008 - Part 1
SQL Server 2008 February CTP introduces a new feature, SQL Server Audit. This feature facilitates auditing of Database Engine events by providing simple T-SQL statements to enable, store, and view audits on server and database objects. Part 1 of this series focuses on the server level events.
Controlling SQL Text Indexing Services with SQL Managed Objects and VB.NET
Control the SQL Server service from your VB code using SQL Management Objects (SMO).
Oracle Database 11g Common Background Processes
To understand Oracle background processes is to understand how Oracle works--offering opportunity to tune effectively. Learn about the processes that are typically running after a fresh, common, and basic installation.
Compound Assignment Operators in SQL Server 2008
MAK illustrates the use of Compound Assignment Operator that is introduced in SQL Server 2008.
Introduction to SQL 2005 Profiler - Part 1
Profiler is a tool that monitors the events and activity running on a SQL Server. Using profiler, this monitoring can be viewed, saved, and replayed. This article focuses on using Profiler with SQL Server 2005, but the tool is also included with SQL Server 2000 and SQL Server 2008.
Oracle Database 11g: Database Replay, Part 2
Part 2 of this series - demonstrates how Oracle 11g Database Replay can be used to capture and prepare a workload from a current Oracle 11g production database environment (P+0) and then replay that identical workload in an Oracle 11g testing environment that represents the next iteration (P+1) of the database system.
Dimension Attributes: Introduction and Overview, Part 4
Business Intelligence Architect Bill Pearson continues his introduction to dimension attributes, focusing upon the Parent-Child group of attribute properties. This article continues our subseries surrounding components of the Analysis Services dimensional model.
Oracle DBA in an Oracle Applications World - Part 2
Steve Callan presents a simple but effective means of replicating your production environment for the first time on a new machine.
New Date Data Types with SQL Server 2008
Greg Larsen examines the new SQL Server 2008 date data types, comparing them to the existing data types that are available within older versions of SQL Server.
SqlCredit - Part 16: The Cost of Bloat
Database designers, developers, and administrators deal with a database that continues to grow over time, often at an alarming rate. In this series' current quest to understand costs of different architectures, we will look at what cost is paid in query performance as a table grows.
Intrinsic Member Properties: The MEMBER_CAPTION Property
Architect Bill Pearson introduces the intrinsic MEMBER_CAPTION property. In hands-on exercises, we gain exposure to the use of the property in generating simple lists, as well as datasets to support report parameter picklists.
SQL Server 2005 Express Edition - Part 23
Part 23 of this series reviews manual workarounds to migrate from MSDE-based installations, when running an in-place upgrade of individual instances isn't possible. Additional factors that should be considered during migration to SQL Server 2005 Express Edition are also covered.
Table-valued parameters SQL Server 2008
In previous versions of SQL Server, it has not been possible to pass a table variable, as a parameter, to a stored procedure. Microsoft introduces table-valued parameters, along with other features, in SQL Server 2008. This article illustrates the function and usage of table-valued parameters.
Oracle 10g Cloning Guide: A Small County Government Approach
This document offers one approach to database refreshes for Oracle 10g databases on Windows 32 and 64 bit servers.
DB2 9.5 and IBM Data Studio - Part 10: Building Stored Procedures
Learn how to take an SQL statement that you built using either the SQL Editor or the SQL Builder and quickly turn it into a deployed SQL/PL stored procedure. In addition, this article explains how to move a data development project between servers.
Dimension Attributes: Introduction and Overview - Part 3
Join Business Intelligence Architect Bill Pearson as he continues his subseries surrounding components of the Analysis Services dimensional model. In this article we continue our introduction to dimension attributes, focusing upon the Misc group of attribute properties.
Oracle Warehouse Builder Automated ETL Processing
Steve Callan offers a high level overview of what it takes to automate a load, covering each and every project item you need to address for this to work.
Policy-based Management in SQL Server 2008 - Part 2
Learn how to use SQL Server Agent alerts and jobs to fix policy incompliance automatically.
DB2 9.5 and IBM Data Studio - Part 9: The SQL Editor Development Accelerators
The last article of this series noted that you can write SQL using both the SQL Builder and SQL Editor. This installment touches on the differences between SQL Builder and SQL Editor by showing you the capabilities of the SQL Editor in more depth.
Upgrading from Microsoft SQL Server Desktop Engine (MSDE)
This installment focuses on upgrading from Microsoft Desktop Engine (MSDE) to SQL Server 2005 Express Edition.
Multi Table Loop
This article examines how to loop through multiple tables to process the data.
Row Value Constructor in SQL Server 2008
This article illustrates different methods to insert data into a table, including the new Row Value Constructor, which simplifies the data insertion.
Programming, Perl + Oracle
Perl is a powerful scripting language that can meet your day-to-day scripting needs as a database administrator, and if used prudently, and responsibly, as a language to build large web-based applications. Here's a quick introduction to using Perl with Oracle.
SQL Server Management Studio Reports and Dashboard
This article examines the built in reports called SQL Server Management Studio Reports and an additional add-on called Performance Dashboard.
The Flash Guru: Serving FLV Files Over Slow Connections
In this latest installment of our popular monthly series, we again turn to our favorite Flash Video expert, Stefan Richter, to answer a question from our forum. This time: How to serve video to users with slow Internet connections.
Create Your Own Reports with Oracles Snapshot Information
If you are tired of running the same old Oracle AWR reports, not getting to the data quickly, or just need to write your own, then this article will get you started.
Oracle Database 11g: Database Replay - Part 1
This article provides a primer for using Oracle 11g Database Replay to effectively predict the performance of rapidly changing applications within increasingly fluid database environments.
Support Parameterization from Analysis Services Parameter Defaults
BI Architect Bill Pearson demonstrates a straightforward solution from the Analysis Services layer - for supporting parameter defaults that represent the last periods of data entry within our cube.
DB2 9 and Microsoft Access 2007: Working with your DB2 Data in Access 2007
Part one of this series discussed using Access 2007 as a graphical front end to a back-end DB2 data server. Part two examines how to leverage DB2 as an integration layer, and demonstrates some of the business rules that Access 2007 maintains when presenting DB2 data to information workers.
SqlCredit - Part 15: The Cost of Distribution
Part 15 of this series examines what cost is paid when tables are separated into multiple databases on the same server.
SQL Server 2005 Express Edition - Part 21: Using Replication Management Objects
Recent installments of this series have demonstrated SQL Server 2005 Express Edition's replication characteristics by taking advantage of replication-specific executables and T-SQL code combined with Windows Synchronization Manager and Web Synchronization technologies. This article explores another method of reaching the same goal, which involves Replication Management Objects (RMO).
Connection Strategy for Multiple Database Environments
Greg Larsen looks at one way to design your database connection strategy to simplify changing application connections so you can plug-n-play databases with less administrative overhead when the need arises.
Oracle DBA in an Oracle Applications World
Steve Callan takes a look at what is behind the smoke, mirrors, and curtains of Oracle Applications.
DB2 9 and Microsoft Access 2007 Part 1: Getting the Data
Paul Zikopoulos explores how to configure Access 2007 as a graphical front-end for DB2 data operations.
Dimension Attributes: Introduction and Overview - Part 2
Join Business Intelligence Architect Bill Pearson as he continues his subseries surrounding components of the Analysis Services dimensional model. In this article we continue our introduction to dimension attributes, focusing upon the Basic group of attribute properties.
Policy-based Management in SQL Server 2008 Part 1
Policy-base Management is a new feature in SQL Server 2008 that helps Database Administrators manage SQL Server instances by intent through clearly defined policies. This article introduces the basics of Policy-based management and shows an example of using this new feature to prevent incompliance.
UPSERT Functionality in SQL Server 2008
This article illustrates the functionality of UPSERT via the MERGE command in SQL Server 2008.
Using Microsoft Access to Query and Update Web Sites
Learn how you can use an Access Database and VBA to manipulate a Web 2 application.
Implementing a Left Join with LINQ
Learn how to implement a Left Outer Join with LINQ.
Authenticating Merge Web Synchronization
We have been discussing SQL Server 2005 Express Edition replication-specific features, investigating authentication mechanisms that are available in order to accommodate connection attempts from remote clients interacting with SQL Server-based distributor via Internet Information Services. We will continue coverage of this topic here.
Oracle Indexing - What, Where, When?
If you are new to databases, or perhaps new to Oracle, you may find the discussion on indexes and indexing strategy complicated. Don't fret. To get started it's fairly straightforward, and as long as you pay attention to the options relevant to day-to-day DBA needs, it should remain fairly simple.
Recovering from Loss of All Control Files
Follow these easy steps to restore a database when the control files haVe been lost.
Set Functions: The StripCalculatedMembers() Function
Business Intelligence Architect Bill Pearson continues his examination of MDX functions, this time introducing StripCalculatedMembers(). In this article, we expose the function, and then lead a hands-on practice session with examples that reinforce the concepts.
Storing Images and BLOB files in SQL Server - Part 4
Part 4 of this series focuses on storing BLOBs on the Windows File System and using Microsoft SQL Server to organize them from a web page.
Measuring Disk I/OOracles ORION Tool
Oracles ORION workload tool enables architects to effectively develop a workload that can mimic and stress a storage array in the same manner as planned applications with an Oracle backend database.
Top Queries in SQL Server 2005
This article illustrates how to query dynamic management views to find the TOP queries based on average CPU and average I/O.
Understanding DB2: Learning Visually with Examples
This excerpt from "Understanding DB2: Learning Visually with Examples" covers the the history of DB2, the information management portfolio of products, how DB2 is developed, DB2 server editions and clients, how DB2 is packaged for developers and syntax diagram conventions.
Exam 70-443 Practice Test from uCertify.com
Greg Larsen reviews the M70-443 PrepKit exam software that helps you prepare for the "Designing a Database Server Infrastructure by using Microsoft SQL Server 2005" exam (70-443), which is one of the exams you need to pass as part of the Microsoft IT Professional: Database Administrator certification.
SqlCredit - Part 14: The Cost of Translation
SQL Server allows us to create a facade using views and triggers so that a table that originally had a certain design can be replaced by a view that looks like the original table to (almost) any code that accesses it. The question answered here is how this translation affects performance.
Using Vertical and Horizontal Table Structures in Oracle
Compare different ways to persist data in a database table. A vertical data layout is one option to consider, as is horizontal tables. Decide which is best for you.
Exposing a Database as a Web Service
Gain a good understanding on how to expose databases as a Web Service using Axis2.
Licensing and Auditing in Oracle
Steve Callan discusses what can take place during the Oracle licensing or renewal process.
Parameterization from Analysis Services Cascading Picklists
BI Architect Bill Pearson adds refinements to Analysis Services- based parameterization within Reporting Services. In this article, we add support for cascading picklists.
Oracle: Unusable Indexes
JP Vijaykumar examines what happens to an index when it becomes unusable.
Authenticating Merge Web Synchronization
SQL Server 2005 Express Edition - Part 19: So far, we have presented a simplified scenario that illustrated Web Synchronization's basic setup and discussed several options. This article will further elaborate on this subject, by focusing on authentication settings that are available when configuring Web access for multi-tier SQL Server-based applications.
Access TreeView-ListView Basics
This article focuses on getting started with Access TreeView and ListView controls.
Using dtutil to Copy SSIS Packages Stored in SQL Server
Learn how to copy SSIS packages stored in SQL Server and their associated package roles using the dtutil command line utility.
DB2 9 for Linux, UNIX, and Windows: DBA Guide, Reference, and Exam Prep
This excerpt from "DB2 9 for Linux, UNIX, and Windows" introduces the DB2 family of products that run on the Linux, UNIX, and Windows operating systems.
Find Space Usage by Table, Schema in SQL Server 2005 and 2008
Learn how to identify which schemas and tables are using the most space by querying system catalogs.
The SQL Builder Development Accelerators: The Rest of the Story
Part 8 of this series rounds out the features of the SQL Builder and finishes off with a great feature that is part of the SQL Editor -- SQL templates.
Dimension Attributes: Introduction and Overview, Part 1
Join Business Intelligence Architect Bill Pearson as he continues his subseries surrounding components of the Analysis Services dimensional model. In this article we introduce dimension attributes, and follow up with an examination of the Advanced properties of a sample attribute.
Create a Database Manually - When & Why?
Peering into the Oracle database creation process really pulls back the curtain on the inner workings of the database, allowing us to understand all of the levers, and pieces that make up a running Oracle database.
SQL Server 2005 Express Edition - Part 18 - Merge Web Synchronization Setup
Part 18 of this series explores several scenarios in which the Merge Web Synchronization can be applied and describes their implementation details.
Programming with LINQ to SQL
You can use LINQ to SQL to define an Object Relational Mapping between SQL tables and then query those tables with LINQ.
Hands-on Oracle: Backup & Recovery Games
Learn what happens within the database after the loss of a control file and test it out yourself using a flash drive.
Storing Images and BLOB files in SQL Server - Part 3
Part three of this series walks you through building a web-based application for inserting images into SQL Server.
Measuring Disk I/OA Vendor View
Learn how to compare the workload of a database with the actual mechanical capabilities of individual disks.
Microsoft SQL Server 2008 - Change Data Capture Part 4
Part 4 of this series illustrates how to generate DML [Data Manipulation language Insert, Update and Delete] statements from the history table.
A High Availability Data Synchronization Architecture
Learn how to synchronize high availability databases and set up synchronization systems without single points of failure.
Set Functions: The AddCalculatedMembers() Function
Business Intelligence Architect Bill Pearson continues his examination of MDX functions, introducing AddCalculatedMembers(). In this article, we expose the function, reinforcing the basic concepts, as always, with hands-on practice examples.
SQL Server DBA Dashboard
This article examines a tool called the "SQL Server DBA Dashboard" that provides a number of different Custom Reports that can be used to monitor activities within a SQL Server instance.
Oracle Database 11g: SQL Plan Management, Part 2
Part 2 of this series explains how SQL Plan Management can be used during the upgrade of an existing Oracle 10gR2 database to an Oracle 11g environment, as well as during the deployment of brand new application code, to effectively limit unexpected regression of SQL statement performance.
Cascading in Oracle: Friend or Foe?
Is cascading always a good thing? Steve Callan looks at some of the more common operations where cascade is used.
Support Parameterization from Analysis Services
BI Architect Bill Pearson demonstrates a straightforward solution ? from the Analysis Services layer - for supporting parameterization within Reporting Services.
SQL Server 2005 Express Edition - Part 17: Merge Web Synchronization
Continuing a discussion on the limitations of SQL Server 2005 Express Edition's replication characteristics, this article explores Web Synchronization, with SQL Server 2005 Express Edition instances operating as merge replication subscribers connecting to a publisher via HTTPS protocol.
SqlCredit - Part 13: More on Indexed Persisted Computed Columns
Part 13 of the "Developing a Complete SQL Server OLTP Database Project" series continues an examination of persisted computed columns, this month discussing performance issues and the PCC Column DataType.
DST checking with Windows Powershell
Incorrect time on a server that hosts trading databases screws up transactions and irritates end users. Yan Pan walks through a script that can be re-used every year to report the wrong time on any SQL Server host.
Microsoft SQL Server 2008 - Change Data Capture: Part 3
Part 1 and 2 of this article illustrated how to enable Change Data Capture on a database and on a table. Part 3 continues with an illustration of what happens to Change Data Capture when the table structure is changed.
DB2 9.5 and IBM Data Studio: Part 7: The SQL Builders Development Accelerators
Paul Zikopoulos introduces his four favorite features available in the IBM Data Studio SQL Builder: SQL Assist, Content Tip, colorization, and the design-time parser.
Adding RDL-Resident Code to Reporting Services Reports
Sometimes, it's just easier to use your Visual Basic skills to incorporate complex logic in a report expression.
Dimensional Model Components: Dimensions Part 2
Business Intelligence Architect Bill Pearson continues his subseries surrounding components of the Analysis Services dimensional model. In the second half of this article, we extend our focus on dimensions to include property settings for Cube Dimensions.
Migrating MySQL to Oracle: Part 2
This two-part article discusses all of the steps involved in migrating a database and its attendant applications. Part 2 covers moving production data to development systems, Oracle Migration Workbench, database application migration and more.
Access Paired Forms
Nearly every Access developer will admit to having learned much about user interface design from the Northwind sample database but in time most of us look for ways to improve upon, or deviate from, the template we know so well. My latest idea makes its dbut in this month's article: Paired Forms.
Secrets of the MySQL Client Command Line
Uncover some of MySQL's little-known client features and manage your data more effectively than ever!
Keywords in Oracle
Some words in Oracle can be categorized as reserved words and keywords. Within the keywords category, context comes into play because a word isn't always reserved. Read on to learn how to distinguish between the two.
SQL Server 2005 Express Edition - Part 16 - Transactional and Merge Replication
Marcin Policht provides a step-through sample implementation of transactional and merge replication.
Measuring Disk I/O
Oracle has a variety of I/O types that ultimately need to be mapped, sampled, and related to storage. This article looks at extracting various I/O statistics so that you can monitor and determine just how well your disks are doing.
Microsoft SQL Server 2008 - Change Data Capture - Part 2
Part 2 of this article illustrates how to enable Change Data Capture on a database, on a table and how SQL Server tracks the data changes of the CDC enabled table.
DB2 9.5 and IBM Data Studio: Building an SQL Statement
Part six of this series discusses how to leverage the SQL editor in IBM Data Studio to quickly and easily build an SQL statement.
MDX Numeric Functions: The Min() Function
Business Intelligence Architect Bill Pearson introduces the numeric Min() function, and leads hands-on practice examples of its use.
Storing Images and BLOB files in SQL Server - Part 2
Part two of this article focuses on using the SQL Server Binary data type to store small image files.
Parameter Support Objects - Part 2: Support OLAP Parameter Defaults with Datasets
Join BI Architect Bill Pearson as he continues his examination of parameterization within Analysis Services reports. In this article, we provide a solution for supporting parameter defaults that represent the last periods of data entry within our cube.
Building Custom Reporting Services Reports for SQL Server Management Studio
Learn how to build Custom Reports, without installing Reporting Services, using a new feature found in Microsoft Service Pack 2 (SP2) for SQL Server 2005.
Oracle Database 11g: SQL Plan Management - Part 1
This article - the first in this series - provides a primer to SQL Plan Management principles and offers a simple demonstration of its performance tuning capabilities.
SQL Server 2005 Express Edition - Part 15: Snapshot Replication
SQL Server 2005 Express Edition's replication characteristics' feature set has been significantly restricted compared with its predecessor and full-fledged counterparts. This article examines the practical implications of these restrictions by illustrating the process of setting up a snapshot replication.
SqlCredit - Part 12: Exploring Indexed Persisted Computed Columns
Part 12 of the "Developing a Complete SQL Server OLTP Database Project" series quickly hits the basics of persisted computed columns and then moves on to performance comparisons and other interesting details.
Dimensional Model Components - Dimensions Part 1
Business Intelligence Architect Bill Pearson launches a new subseries surrounding components of the Analysis Services dimensional model. In this two-part article, we introduce dimensions, and focus upon property settings for Database Dimensions.
Managing Oracle's Undo Tablespaces
With the advent of Automatic Undo Management (AUM), Oracle abstracts the management of undo space for you. Read along as Steve Callan explains the intricacies of undo tablespaces.
MS Windows PowerShell and SQL Server 2005 WMI Providers - Part 2
Part 2 of this series covers the WMI Provider for Server Events explaining how to monitor DDL and trace events in a SQL Server instance by leveraging this provider and Windows PowerShell.
Multiple Selection Through Bitmasks
Ever wish you could store multiple selections to a single table column? The solution, Bitmask Fields, have been in use for decades. Follow along as Danny Lesandrini shows you how to unlock the power of two.
Oracle RAC 64 bit for Windows: Preparing the Virtual Environment
Part 4 of this series covers installing Oracle 11g RAC on a 64 bit Windows system using Windows 2003 Service Pack 2.
Reporting Services: Customize Automatically Created Parameter Support Objects
Join BI Architect Bill Pearson as he continues his examination of parameterization within Analysis Services reports. In this article, we create basic report parameters using the graphical user interface, and then customize automatically created support objects to remove the All selection that appears in picklists by default.
Oracle Security: The Big Picture
This book will help the DBA to assess their current level of risk as well as their existing security posture. It will then provide practical, applicable knowledge to appropriately secure the Oracle database.
Oracle 10g Backup Guide: A Small County Government Approach
This document offers one approach to database backups for Oracle 10g databases on Windows 32 and 64 bit servers.
Microsoft SQL Server 2008 - Change Data Capture - Part 1
Part one of this series illustrates how to enable Change Data Capture on a database, and on a table, and how to keep track of Data Definition Language changes on a table.
Migrating MySQL to Oracle - Part 1
This two part article discusses all of the steps involved in migrating a database and its attendant applications, questions to ask yourself, what to watch out for, and how to perform the whole thing within a limited window of downtime.
More on Using Tools for Tracing
There is more than one approach or tool to use in a tuning opportunity. Part two of this article covers Tracing and TKPROF.
DB2 9.5 and IBM Data Studio: Using OLE DB to Integrate Data
Part 5 of this series introduces the OLE DB function capability available in IBM Data Studio.
SQL Server 2005 Express Edition - Part 14: Replication Support
SQL Server 2005 Express Edition's small footprint and free-of-charge use has some negative implications, imposing restrictions on functionality available in the Standard or Enterprise editions. This is especially conspicuous in the case of replication, which we will explore starting with this installment.
Storing Images and BLOB files in SQL Server
This article explores the data types and methods used for storing BLOBs (Binary Large Objects), such as images and sounds, inside SQL Server.
Oracle Library CachePart 2
Part two of this article looks at issues that arise in the Library Cache, how to find those issues and offers solutions to fix them.
New datetime datatypes in SQL Server 2008
Older versions of SQL Server had only one datatype to store both the date and time values. In SQL Server 2008, Microsoft introduces a set of new datatypes that store date and time individually, and both date and time together.
RMAN Tablespace Point in Time Recovery
A user deletes data from a table and commits it. How do you retrieve that data? RMAN tablespace point in time recovery (TSPITR), of course!
MDX Numeric Functions: The Max() Function
Business Intelligence Architect Bill Pearson introduces the numeric Max()function, and leads hands-on practice examples of the basic concepts.
SQL Server 2005 - Hacking password Encryption
Part 2 of this article discusses how to hack/de-cipher the data that has been encrypted by passphrase.
DB2 9.5 and IBM Data Studio: Overview Diagrams, Continued
Part 4 of this series finishes a tour of overview diagrams and introduces some features you can use with them to make them even more useful than simple, quick visuals.
Disk Space Usage and SQL Server Performance
Every extra byte of space you waste in your database causes a performance hit to your application. This article looks at disk space usage and how it affects performance.
SQL Server 2005 Express Edition - Part 13: ClickOnce Deployment and Security
In order to fully understand all aspects of ClickOnce publishing parameters, it is important to become familiar with security mechanisms that affect its outcome. The main purpose of this article is to present their overview.
Oracle Database 11g: SQL Performance Analyzer - Part 3
This article - the final one in this series- explores how the SQL Performance Analyzer can effectively analyze changes in SQL statement performance due to modified database initialization parameters.
Oracle Library Cache - Part 1
Gaining an understanding of internal Oracle structures is essential to becoming better DBAs and servicing our end user community. Oracle's library cache is one such internal structure that can help eliminate some very nasty denial of service requests originating from application users. Read on to learn more.
BACKUP compression in SQL Server 2008
Learn how to take Full, Differential and Transactional log backups with and without compression, and how to enable compression as a default.
Migrating Access Apps to SQL Server
The final article in this series covers Optimizing Client Server Queries.
Manage Unknown Members in Analysis Services 2005 - Part 2
Business Intelligence Architect Bill Pearson continues his hands-on introduction to managing Unknown Member scenarios within Analysis Services 2005.
Eight Ways to Hack Oracle - Part 2: Vulnerability, System Commands and Security
Part 2 of this series covers four vulnerable areas, the Oracle Listener, privilege escalation to get more access from a less privileged login we already have, executing operating system commands, which can be very powerful, and under appreciated, and lastly filesystem security.
Oracle Tuning Using Tracing, STATSPACK, AWR and Toad
Learn the pros and cons of different tools, Oracle-owned or otherwise, that can be effective in helping a DBA solve a performance problem.
Open Source Virtualization: Oracle VM Manager Installation
Part three of this series covers how to install the Oracle VM Manager on your Workstation skeleton.
SQL Server 2005 Express Edition - Part 12: ClickOnce Deployment and Updates
Part 11 of this series presented the publishing options of ClickOnce-capable applications that involve User Instance-based databases. This article takes a closer look at the deployment process, demonstrating the impact of your selections on its characteristics.
Set Functions: The .AllMembers Function
Business Intelligence Architect Bill Pearson introduces the useful .AllMembers function, reinforcing the basic concepts, as always, with hands-on practice examples.
Data Encryption in SQL Server 2005 - Part 1
SQL Server 2005 provides four methods of encryption. Part one of this article covers encryption and decryption by passphrase.
DB2 9.5 and IBM Data Studio - Behind the Story
Paul Zikopoulos shares the vision and the ideology behind the new IBM Data Studio toolset.
Installing SQL Server 2008
This article highlights the steps involved to install SQL Server 2008.
Scheduling Jobs in the Database
Learn about the Oracle job queue sub system and its usage in scheduling and executing PL/SQL routines at predefined times and/or repeated job execution at regular intervals.
Snapshot Reports II: SQL Server Management Studio Perspective
BI Architect Bill Pearson concludes his subseries surrounding Caching Options, examining the use of SQL Server Management Studio to configure / manage Snapshot Caching.
Oracle Database 11g: SQL Performance Analyzer - Part 2
This article - the next in a series on new Oracle Database 11g SQL performance improvement features - discusses how to use the SQL Performance Analyzer to evaluate a SQL workload generated from an Oracle 10gR2 database to detect potential performance issues for that workload if it were executed within an Oracle 11g database system.
Advanced Active Record Validations with Rails
Use Rails' Active Record validation callbacks to exercise greater control over user input.
SQL Server Security Model
Greg Larsen discusses the different options available within SQL Server 2005 for managing security.
Saving Time by Reusing Storage
While truncating a table to remove data is faster than deleting the same data, there are subtle differences that when taken into account, can help improve the overall performance of a job or process. Read on to learn the advantages and disadvantages of each.
SqlCredit Part 11: Change Tracking Using History Records
Change tracking is an important requirement of most OLTP systems. If the PrimaryCardHolder on an account was updated, you should be able to tell who made the change, when they made it, and what the old and new values are. This article discusses tracking these changes in three different types of history records.
UNIQUE Column with Multiple NULL Values
Learn how to maintain uniqueness on a column while still allowing multiple NULL values.
Windows PowerShell and SQL Server 2005 WMI Providers: Part 1
Learn how to perform administrative tasks by accessing the SQL Server 2005 WMI Providers using Window PowerShell.
SQL Server 2005 Express: Deployment and Maintenance Options
Part 11 of the series focuses on the process of deploying and maintaining user instance-based applications that employ ClickOnce technology.
SQL Server Reporting Services: Getting Started
Explore SQL Server Reporting Services and see how to get started quickly.
Avoiding Data Corruption with Rails' Active Record Validations
Use Rails' Active Record validations feature to filter user input with ease.
File Management with Scripting Object
Once you get familiar with the File Scripting Object (FSO), managing files in Access is not so bad--getting started is the painful part. To help you along, this month's download includes sample code for both Access (early bound) and VB Script (late bound).
Manage Unknown Members in Analysis Services 2005, Part 1
Manage members for which no underlying data exists to populate dimensional attributes. Business Intelligence Architect Bill Pearson provides hands-on guidance in handling these Unknown Member scenarios within Analysis Services 2005 through setting associated properties.
Product Review: Oracle PL/SQL Tuning
Books on PL/SQL, like books on other topics related to Oracle, range from the just so-so to what can be called bible status. With respect to PL/SQL, tuning included or not, Tim Halls book is one of those high return on investment purchases.
DB2 9.5 and IBM Data Studio - Part 3: Overview Diagrams - The Basics
Part 3 of this series examines more deeply the overview diagrams introduced in part 2.
Installing Oracle 11g RAC on VMware ESX 3i - Part 1
This article covers installing Oracle 11g RAC on the VMware workstation in ESX 3.x compatibility mode, in anticipation of the release of ESX 3i.
Building a BizTalk Pipeline Content Enricher with SQL Server 2005
Learn how to implement the Content Enricher integration pattern using BizTalk 2006 R2 and SQL Server 2005.
Custom Pipeline Component for the DB2 Adapter
Discover how to use BizTalk custom pipelines to more effectively pull data from the DB2 adapter.
SQL Server 2005 Express Edition - Part 10: Embedded Databases
Part 10 of our series offers more insight into the design and distribution of applications when implementing applications with embedded databases that support XCopy deployment (and depend on user instance technology).
Application Handling of Database Timeouts and Deadlocks
Learn how to minimize the occurrence of database transaction locking and how to handle situations when your database does return error codes that constitute deadlocks and timeouts.
MDX Essentials: Set Functions: The MeasureGroupMeasures() Function
Join Business Intelligence Architect Bill Pearson as he leads a hands-on introduction to the MeasureGroupMeasures() function. Put MeasureGroupMeasures() to work in your own scripting or reporting environment to exploit a list of all measures in the specified measure group.
Eight Ways to Hack Oracle
As with any computing system, there are ways to hack it, and Oracle is no exception. This series discusses some of the ways that you can get at data you're not supposed to, and how to defend against those attacks.
DB2 9.5 and the IBM Data Studio
Part 2 of this series delves into some of the enhanced and new features of the Database Explorer View in DB2 9.5, focusing on the options available from a live database connection object.
Windows PowerShell and SQL Server 2005 SMO - Part 11: Redirection
Learn how to use PowerShell cmdlets in conjunction with the SQL Server client and output redirection to export to a text file or XML file.
SQL 2008 Data Types
This article introduces the new Date and Time data types found in Microsoft SQL Server 2008, such as DATETIME2, TIME, and DATE. The traditional data types that have been carried forward from previous versions will also be reviewed.
Administering SQL Server 2005 Integration Services
This excerpt from "SQL Server 2005 Management and Administration" covers What's new in Integration Services with Service Pack 2, understanding, managing and administering Integration Services and command prompt utilities.
How to Build a Profile Script to Monitor SQL Server off Hours
Learn how to build a Profiler script that can be launched using a SQL Agent job that is scheduled to run during off hours.
Choosing the "Right" DBMS Engine
Picking the "best" database engine is not always (okay, it's rarely) easy.
Database Isolation Levels
Learn the concepts that every database programmer needs to know to develop applications that allow multi-user access to the database: Isolation levels.
Snapshot Reports 1: Report Manager Perspective
BI Architect Bill Pearson continues his subseries surrounding Caching Options, examining the use of Report Manager to configure Snapshot Caching.
SqlCredit - Part 10: MAC Performance and Updating SqlCredit
This month's installment of "Developing a Complete SQL Server OLTP Database Project" covers MAC performance results, a summary of encryption findings, and comments on updating the SqlCredit code based on those findings.
Oracle Database 11g: SQL Performance Advisor - Part 1
This article the first in a series on new Oracle Database 11g SQL performance improvement features reviews how to set up and utilize these features and provides a simple simulation of how SQL Performance Advisor can detect changes to an Oracle 11g database system.
Hashing in Oracle
Hashing is but one of many cleverly implemented functions embedded in Oracle. Learn all about it, what it is and how to use it.
Partitioning a Non-Partitioned Oracle System
Learn how to bring back manageability and responsiveness using Database Partitioning on your current Non-Partitioned system.
SQL Server 2005 Express Edition - Part 9: Managing User Instances
SQL Server 2005 Express Edition's unique functionality allows you to distribute and implement single-user databases, without the dependency of having administrative privileges or the need for a cumbersome configuration. This article illustrates the practical use of this feature, known as XCopy deployment.
DB2 Viper II and the IBM Data Studio Developer Workbench
In part 1 of this new series, Paul Zikopoulos introduces the Database Explorer that's part of the new IBM DS DWB in the DB2 Viper II open beta.
Using Oracle 10g Tuning Utilities
Starting with 10g, Oracle introduced Advisors, which are part of the ADDM (Automatic Database Diagnostic Monitor) and run in the background. This article discusses some of the more common ADDM utilities used and how they can help troubleshoot and tune the database.
Oracle RAC Benchmarking with SwingBench 2.2
Tarry Singh discusses why benchmarking is important and offers an overview of SwingBench 2.2, a free Java based utility, to stress test Oracle databases.
MS Windows PowerShell and SQL Server 2005 SMO: Part 10
This installment of the series illustrates how to use PowerShell scripts in conjunction with SMO and parameters to Generate an SQL Server Script.
SQL Server 2005 Express Edition - Part 8: XCopy Deployment
Part 8 of our series provides an overview of User Instance model (also known as Run As Normal User or simply RANU) and automatic database connectivity, which serve as two primary enablers for XCopy deployment, facilitating distribution of single-user databases.
Oracle, MySQL and PostgreSQL Feature Comparison - Part 2
In the last piece of our database comparison, we hit upon various areas such as triggers, views, and stored procedures. This time around, we'll discuss some areas where the platforms differ more significantly, most importantly in their handling of complex SQL and optimizing choices.
Transportable Tablespaces: Why the Platform Matters
Steve Callan discusses why the platform matters when using transportable tablespaces.
String Functions: The .Properties Function - Part 2
Business Intelligence Architect Bill Pearson continues his hands-on introduction to the .Properties function. In this article, we examine the use of the TYPED flag within the .Properties function to deliver a strongly typed value using .Properties.
Whats New in SQL Server 2008 - Part 3
Don Schlichting highlights some of the new features and benefits found in SQL Server 2008. Covered in Part 3 of this series is new Data Integration features, enhancements to Analysis Services, Reporting Server additions, and Office integration.
Troubleshooting ASM problems on VMware ESX 3.x: Part 2
Tarry Singh looks at some problems that can be encountered during ESX 3.x Oracle RAC setup, and how to fix those problems.
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 9
Learn how to use PowerShell in conjunction with SMO to Generate an SQL Server Script.
Preparing for the M70-444 Exam
Are you considering becoming a Microsoft Certified DBA? Where do you start? How do you prepare for getting certified? This article reviews one set of practice tests for the Microsoft M70-444 exam that is provided by "uCertify.com".
Report Execution Caching 2: Report Manager Perspective
BI Architect Bill Pearson resumes his Caching Options subseries, examining the use of Report Manager to configure Report Execution Caching.
SqlCredit Part 9: Message Authentication Codes
This month's installment of "Developing a Complete SQL Server OLTP Database Project" covers searching encrypted data, dictionary attacks, and look-ups by hashed value.
Oracle Database 11g: Automatic SQL Tuning
This article delves into a new Oracle Database 11g feature that significantly extends this powerful 10g feature by providing the ability to implement improved execution plans automatically within DBA-specified boundaries.
The Dictionary in the Data Dictionary
Take on a trip into Oracles data dictionary, specifically, the dictionary part or aspect of it.
Manage Your MySQL Database with Rails' Migrations
Manage your MySQL database with incredible ease using Rails' migrations feature.
SQL Server 2005 Express Edition - Part 7: Post Installation Configuration Tasks
Previous articles have described activities that enable remote connectivity and enhance security through encryption of network traffic between the database engine and its clients. In this installment, we will look into a few other options intended to simplify the handling of administrative responsibilities.
XML-Enabled Applications - Part 2: Building PHP Applications on Oracle
Oracle XML DB allows you to perform SQL operations on XML data as well as XML operations on relational data, thus bridging the gap between the SQL and XML worlds. You can choose between several storage options and achieve required levels of performance and scalability.
Migrating Access Apps to SQL Server
Danny Lesandrini introduce some home-made tools that may be useful to your migration process, and explain a few of the "gotcha's" associated with the move from a Microsoft Access database to SQL Server 200x.
Tuning an Oracle Procedure
Join JP Vijaykumar as he tunes a procedure that is running for eight hours, bringing the run time of the procedure down to 37 minutes.
MS Windows PowerShell and SQL Server 2005 SMO: Part 8
Part 8 of our series illustrates how to use PowerShell in conjunction with SMO to display object properties of all SQL Server Objects.
Troubleshooting ASM problems on VMware ESX 3.x
Several errors may be encountered when setting up ESX 3.x on Oracle Rac. This article covers some of those errors and how to fix them.
Report Execution Caching I: SQL Server Management Studio Perspective
BI Architect Bill Pearson continues a three-part sub-series on Caching Options within Reporting Services 2005. In this article, we focus upon Report Execution Caching, and its configuration from SQL Server Management Studio.
SQL Server 2005 Express Edition - Part 6: Post Installation Configuration Tasks (Encryption)
Part 6 of this series continues the discussion of network-based configuration tasks, describing other activities that alter default connectivity settings applied during standard installation, focusing in particular on encryption.
Oracle's Transportable Automatic Workload Repository
Oracles Automatic Workload Repository provides a whole new way of gathering, comparing and storing statistics. Read on to learn how AWR differs from the old statspack reports.
Making Database Administration Easier with Freeware
Cygwin, an OpenSSH application, helps relieve the stress of more commonly seen administration problems, including not being able to get a GUI tool (e.g., Oracle Universal Installer) to display and not being able to have it display at the correct location.
Alternatively Sorting Attribute Members in Analysis Services 2005
Go beyond sorting attribute members based upon their own name or key value. Business Intelligence Architect Bill Pearson provides hands-on guidance in sorting attribute members by the name or key of a secondary attribute in Analysis Services 2005.
Whats New in SQL 2008 - Part 2
Don Schlichting highlight some of the new features and benefits found in SQL Server 2008. Covered in Part 2 of this series is Development changes, new Business Intelligence features, Integration additions, and new Data Types.
Using Orion Calibration to Check I/O Numbers
Orion is an I/O metrics testing tool, designed specifically to simulate the I/O workload before installing Oracle RAC on the hardware. It is ideal for capacity planning and checking DAS, SAN or NAS storage.
MS Windows PowerShell and SQL Server 2005 SMO: Part 7
Part 7 of our series illustrates how to use PowerShell in conjunction with SMO to display SQL Server Objects.
String Functions: The .Properties Function
Business Intelligence Architect Bill Pearson introduces the basic .Properties function, within the first of a two-part article surrounding this important member of our MDX toolsets.
Setting Up Delegation for Linked Servers
Learn how to set up delegation on your SQL Server instances, so you can use the impersonate options when setting up the security properties of linked server definitions.
Product Review: HyperBac for Oracle
A relatively new product called HyperBac provides an excellent alternative to Oracle 10gR2 compressed backupset methodology without the need to modify any RMAN backup, restoration, and recovery scripts. This article provides an overview of HyperBac features that nicely complement Oracle 10gR2's enhancements to database backups and exports.
SQLite: The Sensible Database Solution
Take a look at SQLite, a fully featured relational database which, at just 250KB, is by itself able to easily fit on a floppy disk yet capable of managing terabyte-sized databases. You also will see how it works with PHP.
Report Session Caching in Reporting Services 2005
BI Architect Bill Pearson begins a three-part sub-series on Caching Options within Reporting Services 2005. In this article, we focus upon Report Session Caching.
SQL Server: Post-Installation Configuration Tasks
Part 5 of our series continues exploring post-setup tasks, focusing on network specific tasks.
SqlCredit Part 8: Comparing Encrypt/DecryptByCert and Encrypt/DecryptByKey
Having seen in Part 7 of this series that the performance impact of using EncryptByCert and DecryptByCert was unacceptable for any high-volume, transactional data, this month we'll compare that to EncryptByKey and DecryptByKey.
In this article you'll learn how to prevent errors in your forms. This method validates the form and provides immediate feedback to the user. If the form has been correctly filled out, the submit button is enabled, if not, the button remains grayed out.
Clearing Alerts in Enterprise Manager
Using Oracle Enterprise Manager can be one of the most frustrating experiences in Oracle because of OEMs non-intuitive, disjointed interface and OEMs inability to clean up after itself. Follow along with Steve Callan as he delves into the mystery of how to clear alerts in Enterprise Manager.
Stitching Up Time Zones
Performing brain surgery on the Registry need not be fatal.
Programming SQL Server 2005 Compact Edition with ADO
Learn how to use ADO.NET to connect to the SQL Server Compact Edition database as well as how to read, update, and merge the changes back to the SQL Server Compact Edition database in this excerpt from "Microsoft SQL Server 2005 Compact Edition".
Introduction to Linked Objects in Analysis Services 2005
Business Intelligence Architect Bill Pearson overviews Linked Object options that debut with Analysis Services 2005.
Browser Style Form Navigation
This article series features a demo app affectionately named Something Not Entirely Unlike Access, which employs a variety of methods to obfuscate the "Accessian" features. Last month, we discussed how to automatically resize subforms, the way some browser frames work. This month, we'll tackle navigation.
Clustering Oracle RAC Virtual Machines Across Physical and ESX Hosts
Part 4 of this series examines various possibilities for building clusters across several physical and ESX hosts and takes a quick look at upgrading clustered Virtual Machines in all the three scenarios.
Microsoft Windows PowerShell and SQL Server 2005 SMO - Part 6
Part 6 of "Microsoft Windows PowerShell and SQL Server 2005 SMO" illustrates how to use PowerShell and PowerShell script to backup databases.
Teach Yourself Microsoft SQL Server T-SQL: Working with SQL Server
This excerpt from Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes discusses how to connect and log into SQL Server, how to issue SQL Server statements, and how to obtain information about databases and tables.
DB2 9 and Microsoft Excel 2007 - Part 4: Creating a Pivot Table
Part 4 of this series examines how to leverage the framework created in part 3 and how to create and populate (with DB2 9 data) one of the most important and capable data analysis tools around: pivot tables.
SQL Server 2005 Express Edition - Part 4: Surface Area Configration
This article demonstrates how you can use Surface Area Configuration utility in order to modify some of the security-related settings that have been applied during installation of SQL Server 2005 Express Edition.
Hierarchical TableAdapters 301
Are you having problems setting up a TableAdapter that can be fed from stored procedures instead of base tables? Here is a road map to guide you to success.
Oracle, MySQL and PostgreSQL feature comparison - Part 1
Oracle, MySQL and PostgreSQL obviously have a lot of features, and different solutions for the same problems. This article looks at the major features, such as stored procedures, views, snapshots, table datatypes, transactions, and so on, to discover which ones do what you need.
Learning Oracle by the Numbers
It's hard enough mastering Oracle 10g, let alone trying to devote time to learning the nuances of an older version. Steven Callan offers advice on how to overcome a step or two backwards with respect to Oracle.
Logical Functions: IsGeneration(): Conditional Logic within Filter Expressions
Use IsGeneration() to support conditional logic within filter expressions. BI Architect Bill Pearson looks beyond employing IsGeneration() in calculations, and provides hand-on practice in its use within the MDX Filter() function.
DB2 9 and Microsoft Excel 2007 Part 3: Building Your Own Queries
Learn how to use the Microsoft Query function that's part of Excel 2007 to customize the data that's retrieved from the STAFF table as opposed to the entire data that's retrieved when using the DB2STAFFTABLE connection object that we built in Part 1 of this series.
What's new in SQL 2008 - Part 1
SQL Server 20008 will be released approximately February of 2008, along with a new version of Visual Studio and Windows. This article will highlight some of the new features and benefits found in SQL Server 2008.
Oracle RAC: How shared storage works on VMware - Part 3
Our previous article looked at the clustering possibilities on the ESX Server. In this article, we will examine the various possibilities for building clusters across several physical ESX hosts and the clustering possibilities across ESX hosts and physical machines.
Setting up a Linked Server for a Remote SQL Server Instance
Learn how a linked server can be used to seamlessly provide an application access to data on a different instance of SQL Server.
RMAN Disk to Disk Backup Methods
Disk to disk backups are becoming the norm. This article takes a 1,000 foot view to help evaluate your options.
SqlCredit Part 7: Performance Impact of EncryptByCert and DecryptByCert
Part 7 of our "Developing a Complete SQL Server OLTP Database Project" discusses performance issues resulting from using EncryptByCert and DecryptByCert. Read the article and download the code to run the test yourself.
Oracle RAC: How shared storage works on VMware - Part 2
Part two of this series discusses creating clustered virtual machines on VMware's ESX 3 Server.
Oracle Database 11g Release 1 New Features Summary - Part 2
Oracle Database 11g Release 1 was launched in mid-July 2007. This second article in a two-part series takes some tantalizing glimpses into the upcoming improvements to database performance and database management that expand even further the revolutionary concepts introduced in Oracle Database 10g.
DB2 9 and Microsoft Excel 2007 - Part 2: Working with the DB2 Data
The last article of this series discussed how to get DB2 data into Microsoft Excel 2007 worksheets, some nice formatting capabilities in Excel 2007 and how to apply them to your DB2 data. This article examines some additional advanced features, and analysis capabilities that are part of Excel 2007.
Perform SQL Queries Using Google-Style Syntax
How would you like to enable searching of your SQL Server data using Google-style syntax instead of Transact-SQL? Because of the commonality of the Google search syntax, most web users find it simple to construct a query using it. This article will show how to implement a .NET class library to perform a full-text search on a SQL Server table using Google-style syntax.
SQL Server 2005 Express Edition - Part 3: Installation
Part 3 of this series describes the installation process of different versions of SQL Server 2005 Express Edition in more detail, including changes introduced in its Service Pack 2.
Using Non-Standard Port for SQL Server
Greg Larsen examines setting up SQL Server to listen on a different port number than 1433.
Oracle Database: Rotating Tables
Learn how to rotate a table to present the one to many relation between two tables as a one to one relation and present all the matching records in a single row.
Auto-Resize Access Subforms
Last month's article introduced an application called 'Something Not Entirely Unlike Access', which simulates some aspects of a web browser in Microsoft Access. This month's article focuses on the process of resizing subforms on the main form.
Open-source Technologies for Oracle DBAs and Developers: Part 2
The open-source community around databases, and Oracle specifically, has literally exploded in the last couple of years. The final installment of this two -part article covers o9pen-source administration, security, benchmarking and monitoring tools.
Getting Inside the Optimizer: Part 2
You dont have to accept what Oracle (or a third party tool) does in terms of an execution plan recommendation. Learn how to distinguish between which to accept and reject.
Open-source Technologies for Oracle DBAs and Developers
The open-source community around databases, and Oracle specifically, has literally exploded in the last couple of years. Sean Hull presents some of the biggest projects in this two part series.
SQL Server 2005 Express Edition - Part 2
Express Edition remains the primary choice for deployments of a full-fledged database engine in less demanding environments. Part two of this series explores its features in more detail in order to better understand its unique role in SQL Server 2005 portfolio.
Monitoring Stored Procedure Usage
Learn how you can use a few DMV's to identify the most frequently run SPs of an instance of SQL Server, as well as those SPs that use the most CPU, I/O or run the longest.
Handling NULLS in SQL statements
For some reason the ability to handle NULLS in SQL statements can confuse some. This article takes a look at how to think of NULLs to avoid some confusion.
Oracle RAC: How shared storage works on VMware : Part 1
Tarry Singh revisits the storage option on VMware Server, VMware Workstation and ESX 3.0.1 Server for Oracle RAC.
Microsoft Windows PowerShell and SQL Server 2005 SMO : Part 3
Previous installments of this series discussed PowerShell installation and simple SMO, WMI cmdlets. This installment illustrates how to script PowerShell cmdlets and execute them.
DB2 9 and Microsoft Excel 2007 - Part 1: Getting the Data
Paul Zikopoulos demonstrates just how easy it is to access data from DB2 9 in your Excel worksheets.
The Lure of Open Source Databases
Interviews with customers and vendors of open source and commercial databases provide a look at various aspects of what open source databases are offering today at a high level and how they stack up against their commercial counterparts from a business perspective.
Monitoring Changes to Your Database Schema
With the introduction of SQL Server 2005, Microsoft now allows you to write Data Definition Language (DDL) triggers to perform actions when events occur on your server. In this article, Greg Larsen discusses (examples included) some of the things that DDL triggers can be use for.
Monitoring Changes to Your Database Schema
With the introduction of SQL Server 2005, Microsoft now allows you to write Data Definition Language (DDL) triggers to perform actions when events occur on your server. In this article, Greg Larsen discusses (examples included) some of the things that DDL triggers can be use for.
Monitoring Changes to Your Database Schema
With the introduction of SQL Server 2005, Microsoft now allows you to write Data Definition Language (DDL) triggers to perform actions when events occur on your server. In this article, Greg Larsen discusses (examples included) some of the things that DDL triggers can be use for.
Monitoring Changes to Your Database Schema
With the introduction of SQL Server 2005, Microsoft now allows you to write Data Definition Language (DDL) triggers to perform actions when events occur on your server. In this article, Greg Larsen discusses (examples included) some of the things that DDL triggers can be use for.
Wait events in Oracle RAC
Tarry Singh discusses wait events in Oracle RAC, focusing on Global Cache waits in particular.
Black Belt Administration: Reporting Services Configuration Manager
Use the Reporting Services Configuration Manager to manage a report server deployment. BI Architect Bill Pearson provides an overview of the various settings provided within this centralized maintenance tool.
Something Not Entirely Unlike Access
Have you ever wanted to create an Access application that doesn't really look and feel like Access. Follow along as Danny Lesandrini shows how to implement browser style effects in an Access application he calls Something Not Entirely Unlike Access.
Manual Standby Database under Oracle Standard Edition
Oracle's Standby technology has been rebranded as DataGuard in recent versions of Oracle. But what about the folks on Standard Edition Oracle; are they left out in the cold? It turns out that it is still possible to create a *manual* standby database on Oracle SE. Here's how you do it.
Partition Pitfalls in Oracle
There are some partition pitfalls you should avoid, and knowing what they are ahead of time can help prevent hours of down time. Read on to learn more.
Rational Data Architect and DB2 9: The Rest of the Database Explorer View
Paul Zikopoulos concludes a discussion of the features in the Database Explorer view.
Distinct Counts in Analysis Services 2005
Business Intelligence Architect Bill Pearson examines the expanded Distinct Count capabilities that debut with Analysis Services 2005.
Templates, Data Sources, and Data Source Views
Part 50 of this series dedicated to SQL Server 2005 Integration Services looks into functionality intended to improve efficiency of managing Business Intelligence Development Studio-based projects.
Oracle RAC: Checking RAC Status with SQL/OS Level Statements
Tarry Singh discusses running typical SQL/OS level statements to check the status of RAC nodes.
Microsoft Windows PowerShell and SQL Server 2005 SMO: Part 1
Discover the power of Windows PowerShell in conjunction with SQL Server 2005.
Designing Forms for Efficient and Accurate Data Entry
This excerpt from "Microsoft Office Access 2007 Forms, Reports, and Queries" introduces several techniques that serve to either make data entry less of a chore, or to reduce or eliminate data entry errors (or both).
MDX Scripting Statements: Introducing the Simple CASE Statement
Use the new CASE statement to support conditional logic within calculations. BI Architect Bill Pearson introduces the simple CASE statement, and then leads a hands-on practice session with this new addition to the MDX toolset.
SQL 2005 Backups using LightSpeed
Don Schlichting provides an overview of Quest Software's LightSpeed, an application for backing up and archiving SQL Server databases.
How Useful Are Those Indexes?
Greg Larsen discusses a Dynamic Management View (DMV) that you can obtain information from to help you understand how your indexes are being used.
Rational Data Architect and DB2 9: Storage Diagrams
Paul Zikopoulos explores Database Explorer's storage diagramming feature--a tool for visualization of the underlying storage used for physical data objects.
SQL Server 2005 Integration Services - Part 49: Maintenance Plan Tasks
Marcin Policht reviews the differences between maintenance plan tasks in Maintenance Plan designer and the Control Flow area of SSIS Designer, by examining the compatibility and level of integration between these two alternative approaches to automating routine administrative responsibilities.
SqlCredit - Part 5: Adding Card, Vendor, and Purchase, Plus Much Refactoring
In Part 5 of the SQLCredit series we fill out the application, adding new tables and their associated stored procedutes, and a new script to build the database.
Oracle Secure Backup - Part 3: Conclusion
Learn how to use an Oracle Secure Backup (OSB) configuration to perform Oracle 10gR2 Recovery Manager (RMAN) restore and recovery operations against previously-created RMAN backups stored on simulated OSB virtual libraries and tape devices.
Serving Up Server Alerts
There are several ways, some free and some hand-crafted, to expose alerts and messages. Steve Callan explores some of the database options available.
Debugging SQL Server 2005 Stored Procedures in Visual Studio
This article, by Scott Mitchell, looks at how to debug SQL Server 2005 stored procedures from within the Visual Studio IDE.
Intelligent Layering: Leverage Conditional Formatting Logic from Analysis Services
Leverage Analysis Services based Conditional Formatting in Reporting Services. BI Architect Bill Pearson demonstrates a simple alternative to reliance upon Cell Properties.
Expand Your SQL Server 2005 Dictionary Using Extended Properties
Generate a data dictionary using SQL Server 2005 metadata then take it one step further. Use Extended Properties to add additional metadata that will explain why each data object is important to the application that uses it.
Access 2007 ... For Dummies?
Access 2007 is not for dummies. Procrastinate, if you must, but sooner or later, we're all going to have to learn the new product. This is one case where the "Dummies" book isn't a bad place to start.
DBA Interview Questions
There are nearly an infinite number and combination of questions one can pose to a DBA candidate in an interview. These questions lean towards the conceptional, rather than the rote, as questions of this kind emphasize your foundation, and thorough understanding.
Upload Multiple XML Files to an XML Data Type Column
Learn how to upload multiple XML files to an SQL Server 2005 database XML data type column.
Rational Data Architect and DB2 9: Using the SQL Editor
Learn what's available and where it's located in Rational DA. This article provides a good understanding of all the capabilities available for SQL generation and editing in Rational DA, and know where to find them.
Positing the Intelligence: Conditional Formatting in the Analysis Services Layer
Support conditional formatting for enterprise reports from the Analysis Services layer of the integrated Microsoft business intelligence solution.
SQL Server 2005 Integration Services: Part 48
Maintenance Plan Designer gives you access to 11 tasks, which allow you to perform a variety of database management activities. This article provides a brief overview of each of them, focusing on their recent improvements.
Oracle RAC Monitoring: Keeping Your RAC Under Control
Proactively monitoring your Oracle RAC or even a typical single node Oracle database will keep you upwind of problems. However, that means you need to know which tools you should be using to do just that.
Row Movement in Oracle
One of the relatively newer features in Oracle concerns the moving of rows--but why would a row move and who or what controls that movement? This article looks at three common cases or situations where row movement needs to be enabled.
Rational Data Architect and DB2 9: Building an SQL Statement
Part 1 of this series introduced the IBM Rational Database Architect (Rational DA) Database Explorer view. Part 2 discusses one of the features available from the Database Explorer view, namely, the ability to build SQL statements to run against your database.
Logical Functions: IsGeneration(): Conditional Logic within Calculations
Use IsGeneration() to support conditional logic within calculations. BI Architect Bill Pearson introduces IsGeneration(), and then leads a hands-on practice session with this useful MDX function.
Using SQL 2005 with Expression Web
Join Don Schlichting as he explores various methods of accessing SQL Server from inside Microsoft Expression Web.
Oracle Extended RAC: Stretching Your RAC to the Limit
Tarry Singh offers an overview of how to extend your RAC geographically, up to 25km or even 50kms.
Query XML Data From a table with XML Data Type
Learn how to create an XML schema and a table with XML data type, as well as how to import and query XML files.
Better Entities with Nullable Types
Tired of checking database entries for null values? Learn how you can use Nullable types to make fields and properties accept nulls.
Oracle Capacity Planning
JP Vijaykumar outlines how to reclaim free space from tablespaces in the database/server that are not 100% full and allocate that space to EMP_TBSP01 tablespace, which is 100% full.
Maintaining a Log of Database Changes: Part 2
In this article we will discuss the pros and cons of using triggers and manual techniques for maintaining changes to a database table. We will also build trigger- and manual-based implementation from the ground up.
SQL for MySQL Developers: A Comprehensive Tutorial and Reference
This excerpt, from "SQL for MySQL Developers: A Comprehensive Tutorial and Reference," discusses adding aggregation functions to a select block with the use of a GROUP By clause.
SqlCredit - Part 4: Schema and Procedure Security
In this month's installment, we will add security to the existing code and add new unit tests to prove that security.
Scripting the Installation of SQL Server 2005
Having a consistent SQL Server environment is important. Gregory Larsen discusses one method of scripting your installation to ensure all of your SQL Server 2005 machines are set up the same.
Quest Code Tester for Oracle
Steve Callan reviews Quest Code Tester for Oracle, a new product from Quest that allows developers to generate test code from user descriptions, then run tests with the press of a button.
Oracle Secure Backup, Part 2: A Sample Implementation
This article the second in this series simulates the creation and administration of an Oracle Secured Backup environment to demonstrate how it can be used effectively in concert with existing RMAN backup and recovery scripts.
Maintaining a Log of Database Changes: Part 1
This article, by Scott Mitchell, looks at techniques for maintaining a log of database changes.
SQL Server 2005 Integration Services - Maintenance Plan Tasks: Part 47
In the previous installment of our series we mentioned that a variety of features have changed with the advent of SQL Server 2005 Service Pack 2. We will point out these changes as we continue our presentation, in order to bring your attention to some of the more relevant improvements.
Manage Recordsets in ADPs
Learn how to use SQL Server stored procs in an Access Data Project.
Oracle RAC: Capacity Planning for your RAC
Implementing RAC, or any clustered solution, requires planning. Tarry Singh discusses capacity planning, breaking it up into several components, such as network, storage, and infrastructure.
CASE Function in SQL Server 2005 - Part 4
Part 4 of this series discusses how to use case functions in GROUP BY clauses.
Rational Data Architect and DB2 9: The Database Explorer
Learn how to add and work with database connection objects in the Rational Data Architect Database Explorer view. This series takes you through the various interfaces of the Rational DA IDE as they relate to DB2 9 data servers and the tasks that you can accomplish with this tool.
Black Belt Administration: Performance Dashboard for Microsoft SQL Server: Part 2
BI Architect Bill Pearson continues his overview of the new Performance Dashboard for MSSQL Server. In this article, we examine the individual member reports and discuss their basic uses.
SQL Server 2005 Integration Services - Maintenance Plan Tasks: Part 46
Continuing coverage of Control Flow tasks in SQL Server 2005 Integration Services, this article focuses on several tasks, whose shared purpose is to provide the ability to perform standard database maintenance procedures.
CASE Function in SQL Server 2005: Part 3
Part 3 of this series illustrates how to use case functions in group by clauses.
Multipurpose Code in Oracle and Oracle Forms
Take charge of what Oracle provides out of the box by learning how to create a small message library to take care of many types of output messages.
Administration and Optimization: SQL Server Profiler
Use SQL Server Profiler to "look behind the scenes" within Analysis Services 2005. BI Architect Bill Pearson leads a hands-on introduction to determining resource utilization effectiveness for both processing and query performance with profiling.
Microsoft SQL 2005 Maintenance Wizard: Part 3
The Maintenance Plan Wizard is a graphical interface for creating a variety of database housekeeping tasks. This article focuses on using SQL Server 2005 Maintenance Plan Wizard for creating Database Backup operations.
Installation Gotchas for Oracle Linux, Oracle RAC 10gR2, and VMware ESX 3.0.1
Tarry Singh covers several issues that can cause problems when installing Oracle Linux 10gR2 RAC on Oracle Enterprise Linux 4.4 with VMware ESX 3.0.1.
CASE function in SQL Server: Part 2
Part 1 of this series illustrated how to use simple case functions in queries. This installment discusses how to use case functions in different types of scenarios.
Logical Functions: IsAncestor(): Conditional Logic within Filter Expressions
Use IsAncestor() to support conditional logic within filter expressions. BI Architect Bill Pearson looks beyond employing IsAncestor() in calculations, and provides hand-on practice in its use within the MDX Filter() function.
Using and Managing Database Mail
Database Mail is a great improvement over SQL Mail that came with earlier versions of SQL Server. Greg Larsen discusses setting up and managing Database Mail.
Data Manipulation Using Java in Oracle
PL/SQL can do many things well, but other languages can do some things much better, with better being measured in terms of speed. If you find that stored data must be manipulated for display type output, don't be afraid to try other languages supported by Oracle.
Data Manipulation Using Java in Oracle
PL/SQL can do many things well, but other languages can do some things much better, with "better" being measured in terms of speed. If you find that stored data must be manipulated for display type output, don't be afraid to try other languages supported by Oracle.
Four Ways to Optimize Your MySQL Database
See how you can make your MySQL database absolutely scream!
SQL Professional Toolbelt
RED Gate Software recently released a new service called SQL Professional Toolbelt, a compilation of all of their tools for an annual subscription.
SQL Server 2005 Integration Services - Part 45: Bulk Insert Task and Format Files
The previous article of our series presented a straightforward approach to importing data from delimited text files into database tables and views, using Bulk Insert Control Flow task. This approach lacks flexibility, which can be addressed by employing format files.
CASE function in SQL Server: Part 1
In SQL Server, the CASE functions evaluate a list of conditions and then return one or many results. This article illustrates the various uses of CASE functions.
Oracle 10gR2 RAC Load Balancing Features
Learn how Oracle 10gR2 takes advantage of client-side connection load balancing, server-side load balancing, and the new Load Balancing Advisor (LBA) features to improve the distribution of workloads across the appropriate nodes in an Oracle 10gR2 (RAC) clustered database environment.
Finding Foreign Key Constraints in Oracle
For a developer to identify and disable foreign key constraints is a difficult task. A brief description on the foreign key (referential integrity) constraints will go a long way in identifying and disabling these constraints.
Black Belt Administration: Performance Dashboard for Microsoft SQL Server - Part 1
Diagnose performance problems in MSSQL Server 2005 with a powerful new administration tool. BI Architect Bill Pearson provides hands-on guidance in installing and using this powerful new dashboard / report pack, freely available to MSSQL Server 2005 users everywhere.
Product Review: SQL Prompt
Learn why Danny Lesandrini thinks SQL Prompt is the Holy Grail of SQL Server utilities.
Oracle RAC Administration: Backing up your RAC with RMAN
Whether you are a single node Oracle DBA or a multi-node RAC farm DBA, you will be confronted with various backup alternatives. Tarry Singh discusses several scenarios and backup possibilities using the RMAN utility.
Using the MySQLi Interface: Basic Techniques
This article will illustrate some basic techniques for using the MySQLi interface. The MySQLi class can be very powerful for doing repetitive data tasks and makes it much easier to encapsulate robust methods into your own custom classes.
Ordering Events in Oracle
The time stamping and ordering of events within Oracle is of prime importance and goes to the heart of how transaction control and serialization are even possible. Read on to learn more about ordering events in Oracle.
Mastering Enterprise BI: Time Intelligence Part 3
BI Architect Bill Pearson continues his introduction to enhanced features in Analysis Services 2005 for Time Intelligence support. In this session, we examine new, wizard-driven features that support the easy addition of Time Intelligence within our cube.
SQL Server 2005 Command Line Tool "SQLCMD": Part 3 - Scripting and Variable Substitution
Learn how to use the SQLCMD scripting variables, startup scripts and variable substitution.
Oracle RAC Administration - Part 16: Balancing act between Server and Client
In this article, Tarry Singh takes a little detour to look at connectivity load balancing and failover.
SQL Server 2005: Using Asymmetric Encryption and Digital Signatures
This article, by Erich Peterson and Siqing Li, explores techniques for encrypting data in a Microsoft SQL Server 2005 database using asymmetric encryption. This article - the final one of the series - starts with a look at the T-SQL commands for performing asymmetric encryption and decryption.
MDX Clauses and Keywords: Use HAVING to Filter an Axis
BI Architect Bill Pearson overviews the new HAVING clause, and then leads hands-on practice with its use in filtering an axis.
Building a Weblog: Part 4
This installment is the conclusion of our series. Topics covered are blog categories, creating new blog entries and updating entries. Adding blog categories uses a page similar to the one for adding comments.
Building and Using an SQL/PL Stored Procedure with Visual Studio 2005
Learn how to create a stored procedure using the SQL/PL Stored Procedure designer and show you how you can leverage that stored procedure in a simple WinForm application.
New Ranking Functions within SQL Server 2005
Gregory Larsen discusses the new SQL Server 2005 ranking functions, which make it much easier for you to code your T-SQL to associate a ranking to your result set.
Oracle: Sorting Out Memory
Keeping track of all the different memory areas in Oracle can be taxing. With Oracle 10g, there can be fewer parameters to keep track of if you remember which parameters drive or affect other parameters.
Using Symmetric Encryption in a SQL Server 2005 Database
This article, by Erich Peterson and Siqing Li, explores techniques for encrypting data in a Microsoft SQL Server 2005 database using symmetric encryption.
SQL Server 2005 Integration Services: Part 43 - XML Task Continued
Marcin Policht concludes his coverage of the SQL Server 2005 Integration Services XML Control Flow task.
Building a Weblog: Part 3 - Building the Category Browser
This week, we're going to build the category browser. In this section, you'll create a page that allows users to browse the different categories and see which blog entries have been posted in each category.
SqlCredit - Part 2: Creating the Database, Tables, CRUD Procedures
In part 2 of our series we begin to create the database, schema and stored procedures of a complete, functioning, tested credit card database.
Oracle 10g Secure Backup, Part 1: Concepts
This article the first in this series provides an overview of Oracle Secured Backup and how it can be utilized in concert with RMAN backup, restoration, and recovery scripts.
Using the FILLBY Method to Filter DB2 9 Data Sets...with a Twist
This article discusses how to use a FILLBY method to pass dynamic parameters to the application.
SQL Server 2005 Command Line Tool "SQLCMD": Part 2
Part 2 of this series illustrates how to use the SQLCMD utility to input Transact SQL scripts to be executed on a SQL Server instance.
Mastering OLAP Reports: Extend Reporting Services with Custom Code
Extend the capabilities of your reports with embedded custom code. In this article, BI Architect Bill Pearson provides hands-on practice creating and referencing embedded custom code, to meet special presentation needs within an OLAP report.
Building a Weblog: Part 2
One of the planned features for this blog is to add comments to a blog entry. When comments have been posted, you can display the number of comments and the names of the posters, which double as hyperlinks, so when you click the poster's name, the application jumps to that poster's comment.
Queries On Steroids: Part 4
This article covers the tools available to analyze queries in both Microsoft Access and SQL Server, with a limited explanation of how the results may be interpreted.
Oracle RAC Administration - Part 15: Connection Load Balancing and FAN
Part 15 of our series continues the discussion of workload characteristics and load balancing connections in our Virtualized RAC, focusing this time on Connection Load Balancing and Fast Application Notification.
Getting a Handle on Indexes
Performance is the number one reason why indexes are needed. However, there are situations where indexes are not needed. Read on to learn when and where to index.
Building DB2 9 Reports Using Crystal Reports - Part B
Paul Zikopoulos discusses how to format default columns from the last installment, add the report to an application, and additionally add a data grid that's bound to a DB2 data server to show all the data.
Mastering Enterprise BI: Time Intelligence - Part 1
BI Architect Bill Pearson introduces enhanced features in Analysis Services 2005 for Time dimension support. Our hands-on exposure to Time dimension creation includes a focus upon automatic schema generation, with the complete creation and population of a Time dimension table in a designated target relational database.
Building a Weblog
The basic function of a blog is to store a series of blog posts (often called entries), but many blogs also include commenting, categorization and archives. This week you'll learn how to build a blog that incorporates these features.
SQL Server 2005 Integration Services: Part 42 - XML Task
Marcin Policht continues his discussion of various types of actions that can be performed using XML Control Flow task, focusing this month on the operation type called XPATH.
Increasing productivity with TOAD for Oracle
Sreeram Surapaneni discusses some of his favorite, more commonly used interfaces of TOAD 9.0.
solidDB for MySQL
Peter Gulutzan takes a look at the solidDB storage engine, comparing it to other popular storage engines.
SQLServer: Dynamic Cross-Tab
MAK illustrates how to generate a cross-tab query on the fly, using the EXEC statement and simple string concatenation.
Logical Functions: IsAncestor(): Conditional Logic within Calculations
Use IsAncestor() to support conditional logic within calculations. BI Architect Bill Pearson introduces IsAncestor(), and then leads a hands-on practice session with this valuable MDX function.
SQL 2005 Maintenance Wizard: Part 1
The SQL Server 2005 Maintenance Plan Wizard offers many core tasks and options for database housekeeping. In addition, the wizard will roll all of your selected tasks into a reusable and customizable package. Read on to learn more.
Services Architecture in Oracle Workload Management
Part 14 of the series focuses on workload management, which facilitates management and distribution of the workload to provide peak performance and high availability.
Dealing With Upper and Lower Case Data
Gregory Larsen demonstrates several examples of how to deal with different situations related to the case of character strings.
Building Reports from DB2 9 Using Crystal Reports: Part A
Paul Zikopoulos shows you how you can create a very slick cross tab report using Crystal Reports for Visual Studio 2005 to generate a report from an IBM DB2 9 data server.
SqlCredit: Developing a Complete SQL Server OLTP Database Project
The SqlCredit series demonstrates the database design and development portion of a complete, functioning, tested credit card database to illustrate the complete software development lifecycle.
Oracle 10gR2 Security - Part 4: Securing Data Extracts, Exports and Recovery Files
This article discusses and illustrates how to implement an unprecedented level of database security by encrypting DataPump Export dump sets and Recovery Manager (RMAN) backup sets.
Queries on Steroids: Part 3
This month we discuss Fancy Filtering Techniques for Queries on Steroids. Before we're done, we will have shown you three ways to return a filtered query.
Review: Oracle Performance Tuning for 10gR2, Second Edition
Steve Callan offers an in depth look into Gavin Powell's Oracle Performance Tuning for 10gR2, Second Edition. The first article of this series covers Identifying Tools, Explain Plan, Other tools and Performance views.
DB2 Security - The Starting Point
If you happen to be a DBA, the written database security plan can be a lifesaver. Chapter 2 of this book covers visualization of the security plan including participants, goals and desired outcomes.
SQL Server 2005 Integration Services: Part 41 - XML Task
This installment of our series describes the properties and functionality of the XML Control Flow task, which provides assistance with processing XML-formatted documents.
Product Review: SQL Refactor
Danny Lesandrini reviews SQL Refactor, a SQL Server Add-In that works with the new SQL Server Management Studio.
Oracle RAC Administration - Part 13: Cache Coherency
Part 13 of this series takes a break from administration to discuss performance metrics specific to the RAC environment.
SQL Server 2005 Command Line Tool SQLCMD: Part 1
This series will illustrate the various ways of using the SQL Server 2005 command line utility SQLCMD.
Mastering OLAP Reports: Parameters for Analysis Services Reporting: Part 2
Join BI Architect Bill Pearson as he continues his examination of parameterization within Analysis Services reports. In this article, we get hands-on practice creating rudimentary cascading parameters using the graphical user interface, and then examine the nature and timing of automatically created objects.
SQL Server 2005 Integration Services: Part 40 - Web Service Task
Part 40 of this series reviews a Web Service Control Flow task that exposes an infinite range of easily deployable features by leveraging capabilities present in the .NET Framework.
Product Review: EMS SQL Manager
SQL Manager for Oracle is an affordable compromise between Quest's TOAD and Oracle's SQL Developer. Read on to find out why Steve Callan gives this product two thumbs up.
The Role of the DBA Related to Insider Threats and Regulatory Compliance
This article deals specifically with insider threats to IT describing how IT professionals are implementing the necessary products, policies, and procedures to reduce insider threats and provide the necessary reporting for regulatory compliance.
Design and Documentation: Introducing the Visio 2007 PivotDiagram
Use the new Visio 2007 PivotDiagram to support and document the design of the Analysis Services component of your integrated Microsoft Business Intelligence solution. Join BI Architect Bill Pearson as he unveils this new feature, and provides a hands-on introduction to its use.
Error Handling in SQL Server 2005
Learn various methods of handling errors using the TRY and CATCH command and various ERROR_ functions.
Oracle RAC Administration - Part 12: RAC Essentials
Part 12 of this series continues its discussion of RAC specific parameters.
Using SQL for IIS Logs: Part 2
This article examines DTS and Data Import as methods for moving IIS web logs into SQL Server.
Logical Functions - IsSibling(): Conditional Logic within Filter Expressions
Use IsSibling() to support conditional logic within filter expressions. BI Architect Bill Pearson looks beyond employing IsSibling() in calculations, and provides hand-on practice in its use within the MDX Filter() function.
Oracle RAC Administration: Part 11 -The RAC DBA Continued
Part 11 of this series continues the discussion of Oracle RAC DBA essentials.
Oracle 10gR2 Security: Part 3 - Transparent Data Encryption
This article the third in this series reviews how Oracle 10gR2 protects against an intruders efforts to view the data stored within a databases physical files by implementing the impressive features of Transparent Data Encryption (TDE).
Using SQL Developer to Learn Oracle
Oracle's (free) SQL Developer 1.1 includes a tutorial which covers a little bit about a lot of things in Oracle. Read on to learn more.
Creative Ways to Use the TOP Clause
Gregory Larsen discusses how to use the TOP clause to help solve requests where you want to restrict the number of records returned based on a record count.
Black Belt Components: Support Simple Navigation with a Document Map
BI Architect Bill Pearson exposes the use of the out-of-the-box Document Map feature in Reporting Services. In this article, we get hands-on practice in providing navigation support within an OLAP report, while we consider scenarios where the Document Map can be a convenient feature.
Oracle RAC Administration: Part 10 - The RAC DBA
Part 10 of this series takes a breather, to review some RAC basics, before returning to the more advanced material in RAC administration.
Hackers in your Database
How would you know if there was a mole in your database? A mole refers to a malicious user who has hacked Oracle objects to cover his tracks, and stays hidden from view while causing damage to the system (or other users).
Oracle 10g: A Simple Security Approach: Part 2
Part 2 of this series describes setting up column level / row level security of data through custom built views, and a critical review on the creation and maintenance of procedures for truncating tables in third party schemas.
Actions in Analysis Services 2005: The URL Action
BI Architect Bill Pearson continues in-depth coverage of Analysis Services 2005 Actions. This month's session focuses upon the URL action, and includes exploitation of the MDX Condition expression as a means of restricting URL Action availability.
SQL Server 2005 Integration Services: Part 38 - Pivot Transformation
A few ETL features are intended primarily for assisting with data analysis. Part 38 of this series covers Pivot transformation, which is one of the more popular choices in this category.
Bigfile Type Tablespaces versus Smallfile Type
Learn about the benefits Bigfile Tablespace offers vs. smallfile type tablespaces.
Data partitioning in SQL Server 2005: Part 4
Learn how to partition existing data into four different parts based on certain dates.
Logical Functions: IsSibling(): Conditional Logic within Calculations
Use IsSibling() to support conditional logic within calculations. BI Architect Bill Pearson introduces IsSibling(), and then leads a hands-on practice session with this valuable MDX function.
Using SQL for IIS Web Logs: Part 1
This article explores the benefits and various methods of using Microsoft SQL Server to analyze IIS Web Server log files.
The Five W's of Database Restores
Gregory Larsen discusses the Who, What, When, Where and Why's of restoring a database.
Quest Software and Toad for Oracle
Join Steve Callan for a peek into Quest Software's database development and administration tool, Toad for Oracle.
Creating an XML table and XML-based indexes with the IBM Database add-ins for Visual Studio 2005
Learn how to create a table using an XML data type and also how to create an XML index on that table in preparation for subsequent queries.
SQL Server 2005 Integration Services: Part 37 - Derived Column Transformation
Part 37 of this series presents Derived Column Transformation, which, while serving primarily an auxiliary role, should not be ignored, since it offers significant flexibility in the areas of logical and math operations, string processing, and data conversion.
Database-enabled Ajax with PHP
Ajax has taken the Web to a new level by offering an intuitive interactive model that rivals the desktop. To compete with desktop applications, you'll learn how to create database-enabled Ajax requests using PHP and MySQL.
Automatically Stopping and Restarting SQL Server
This article will explore a number of different approaches to automate the task of stopping and restarting SQL Server.
Mastering OLAP Reports: Parameters for Analysis Services Reporting, Part 1
BI Architect Bill Pearson begins an extended examination of parameterization within Analysis Services reports. In this article, we get hands-on practice creating a parameter within the graphical user interface, and then examine the construction that Reporting Services performs behind the scenes.
Oracle: Choosing and Using the Right Code
Sometimes a programming background can make a task harder than it is, especially if you lose sight of what SQL can do independently of PL/SQL. Learn when it is to our best advantage to use a pure SQL approach rather than switching to PL/SQL.
Oracle RAC Administration: Part 9 - Hands on Administration
Tarry Singh takes a closer look at installation errors that aren't really errors, ESX host tuning for time synchronization and some SRVCTL commands.
Data partitioning in SQL Server 2005: Part 3 - Partitioning an Existing Table
Learn how to partition an existing table with data into four different file groups.
Queries On Steroids: Part 1
There are some things you just cant do with simple ANSI SQL, like display multi-valued data from a related table into a single cell. Learn how to accomplish this task using a generic function in MS Access.
Actions in Analysis Services 2005: The Drillthrough Action
BI Architect Bill Pearson provides in-depth coverage of the Drillthrough Action in Analysis Services 2005. Our hands-on exposure to Drillthrough Action creation and operation includes a focus upon the MDX Condition expression as a means of restricting Drillthrough Action availability.
SQL Server 2005 Integration Services: Part 36 - Term Extraction and Term Lookup
Part 36 of this series covers two SSIS Data Flow transformations known as Term Extraction and Term Lookup.
Very Large Databases and High Availability Evaluating Replication Options
Sreeram Surapaneni discusses various replication technologies and the need to plan how to use replication prior to choosing or developing a replication tool.
Getting Started with SQL Server Service Broker
Learn how the Service Broker provides the plumbing to let you pass messages between applications, using SQL Server as the transport mechanism.
MDX Operators: The IsLeaf() Operator
Use IsLeaf() to support conditional logic within filter expressions. BI Architect Bill Pearson looks beyond employing IsLeaf() in calculations, and provides hand-on practice in its use within the MDX Filter() function.
SQL Server 2005 Management Studio: Part 2
Part 1 of this series introduced SQL Server 2005 Management Studio; part 2 of this article covers graphically managing Indexes and Foreign Key relationships.
Oracle RAC Administration: Part 8 - Hands on Syntax Check
Part 8 of this series covers the details of the commands discussed in Parts 3 and 4, "Administering the Clusterware Components".
Data Partitioning in SQL Server 2005: Part 2
Learn how to create a table partition by dividing the table into four parts.
Relational Databases 101
Relational Databases 101 is excerpted from the Addison-Wesley title, Hitchhiker's Guide to Visual Studio and SQL Server.
Working with Comma Separated Data: Part 2
Last month's article discussed different comma separated data situations. This month we will cover a couple of additional circumstances that you might come across when working with comma separated data.
Oracle 10g Security: Part 1 - Fine-Grained Auditing
This article the first in an ongoing series on Oracle 10g Security demonstrates how to implement FGA in Oracle 10g and illustrates how to take advantage of the newest Oracle 10g Release 2 (10gR2) FGA features.
Creating a Custom RSS Feed with PHP and MySQL
RSS has become the standard technology for syndicating information to large audiences. Many people have something to say, but finding the right audience for your voice is what matters. In this article you'll learn how to syndicate your own custom RSS feeds using PHP and MySQL.
How Advances in Storage Technology Create a New Security Challenge
Simply restricting access to your database is no longer enough. The portable and removable storage devices, not to mention the new PMR hard drive with 160GB storage capacity, present a security challenge organizations must address.
Creating an XSD document using DB2 Tools
Learn how to leverage the Visual Studio 2005 XML tools to create an XML Schema Definition (XSD) document for subsequent registration into the DB2 9 XML Schema Repository (XSR).
SQL Server 2005 Integration Services: Part 35 - Fuzzy Grouping
Continuing the topic of data cleansing through applying approximate match algorithms, this article presents another Data Flow task, which does not rely on a reference table for comparison, but instead matches input rows against each other.
MS Access: Manage User Objects
Power users create queries and reports which are overwritten each time a new version of the client app rolls out. Leaern how to give users the ability to identify, save and retrieve their custom database objects.
Starting and Stopping the Database Instances and RAC Databases
Learn how to start up and shut down database instances using Enterprise Manager, SQL*Plus and SRVCTL.
MySQL and Character Sets
In spite of the dominance of English for business purposes, we still live in a multilingual world. At some point, most serious MySQL developers and DBA's will come across the need to store data from other languages and these may require other character sets or collations.
Data Partitioning in SQL Server 2005: Part 1
Learn how to make life easier by dividing large tables and indexes into smaller parts.
Black Belt Components: Interactive Sorts within a Matrix Data Region
BI Architect Bill Pearson revisits the interactive sort capability in Reporting services. In this article, we get hands-on practice in providing interactive sort support for a matrix data region.
SQL Server 2005 Integration Services: Part 34 - Fuzzy Lookup
Learn how to employ Fuzzy Lookup Data Flow transformation to perform general data cleanup.
Oracle 10g: Exploring Data Pump
Data Pump, an import/export technology for fast data movement between databases, is a welcome addition to a DBA tools in a world that constantly redefines the size of the "large database".
Oracle after Hours: Gathering Statistics
Do you know what your database is doing after dark and on the weekends? Would you be surprised to know that Oracle, by default, schedules a job to gather optimizer statistics upon creation of a database?
Oracle 10g: A Simple Security Approach: Part 1
'A Simple Security Approach' examines methods for simplifying the setup and administration of database security in an ever changing/volatile environment.
Getting Started with SQL Server Integration Services
Check out SSIS, the SQL Server 2005 replacement for the older Data Transformation Services. You might find SSIS to be a great solution for automating SQL Server.
Mastering Enterprise BI: Introducing Actions in Analysis Services 2005
BI Architect Bill Pearson kicks off a multi-article set that exposes Analysis Services 2005 Actions. In this overview, we introduce the Action Types, and discuss possible uses for each, in preparation for hands-on practice articles that follow.
MS SQL Server 2005 Management Studio: Part 1
This article introduces many of the features and options of Management Studio, a new graphical interface that is included with SQL 2005.
Administering the Database Instances and Cluster Databases
Part 6 of this series offers an overview of three tools that can be used to tame the Oracle database, whether it be a multi-node RAC farm or a single Oracle instance.
SYNONYM in SQL Server 2005
Learn how to use SYNONYM, a new SQL Server 2005 feature, to replace a two, three or four-part name in many SQL statememts.
The IsLeaf() Operator: Conditional Logic within Calculations
Use IsLeaf() to support conditional logic within calculations. BI Architect Bill Pearson introduces IsLeaf(), and then leads a hands-on practice session with this valuable MDX operator.
Dealing with Comma Delimited Strings
Comma separated data can come in many forms. It can be input, a text string stored in a column, or a number of other situations. This article deals with two different comma separated data situations.
Oracle 10g Materialized View Enhancements: Part 2 - Partition Change Tracking
The final article of this series demonstrates how to utilize the new Partition Change Tracking features of 10gR2 to speed the refresh of a partitioned materialized view and how Partition Maintenance Operations help improve performance for partitioned materialized views.
Oracle RAC Administration: Administering the Clusterware and ASM Storage
Part 5 of the Oracle RAC Administration series offers a quick look at the administration of clusterware and storage administration.
SQL Server 2005 Integration Services - Part 33: Lookup Transformation
Continuing the overview of Data Flow components in SQL Server 2005 Integration Services, this installment focuses on the Lookup transformation.
Upload Multiple Files to VarBinary Column in SQL Server 2005
MAK discusses how to upload multiple image or text files to the SQL Server database varbinary column.
SQL Server 2005 Large Value Data Types
Microsoft has come to the rescue for managing large text fields in SQL Server 2005, by creating a few new large value data types. This article explores these new data types.
BlackBelt Authoring: Conditional Drillthrough to Multiple Reports
Enable drillthrough to different reports, based upon the values in a given data field on a launch report. BI Architect Bill Pearson presents the creation of a working prototype, containing conditional drillthrough settings.
SQL Server 2005 Service Broker Plays Well with Older Relatives
You can leverage SQL Server 2005 Service Broker with existing technologies such as SQL Server 2000 and MSMQ. BizTalk and SSB external activation are viable options, but a simple polling solution is easier.
Understanding Logical Standby Databases
This article discusses the concept and setup of the logical standby database.
Access Object Enhancifier
Today's article will show you how to access and modify the properties of various Microsoft Access objects through VBA code, by building an Access Object Enhancifier.
Managing Tables: Logging versus Nologging
Using the NOLOGGING option can be a time saver, but it can also put you at risk if you do not use it wisely.
Building Tables with the DB2 Designer and Visual Studio 2005
Paul Zikopoulos discusses a new tool that is unique to DB2 9 in the Visual Studio 2005 integrated development environment (IDE) called a designer. Specifically, he examines how to create a table using the table designer.
Mastering Enterprise BI: Introduction to Translations
Support Analysis Services information consumers with diverse languages via Translations, which debut in Analysis Services 2005. BI Architect Bill Pearson leads a hands-on exposure to both types of Translations within a sample cube we construct for our purposes.
MDX Numeric Functions: The .Ordinal Function
MSAS Architect Bill Pearson introduces the MDX .Ordinal function, as a means for generating lists and for conditionally presenting calculations. We also examine the use of the function in creating datasets to support report parameter picklists.
SQL Server 2005 Integration Services - Message Queue Task
After concluding a broad overview of various aspects of SQL Server 2005 Integration Services, part 32 of this series focuses on individual components that have more specialized, but very useful characteristics, starting by discussing the Message Queue Control Flow task, and following with coverage of a number of Data Flow transformations.
Copying Database Backups to an Alternative Location
Learn how to use ALERTs, a SQL Agent job and a stored procedure (SP) to create a copy of your critical database backups on another physical machine as soon as the database backups are created.
Build a DB2 Bound Data Application using Visual Studio 2005
Part 3 of this series demonstrates another method to quickly build a data grid and bind it to your DB2 data source.
SQL Server 2005 Integration Services: Part 31 - Performance
Part 31 of this series continues the discussion of improving the efficiency of package execution.
Oracle 10g Materialized View Enhancements: Part 1
Learn about materialized views in Oracle 10gR2, including enhancements to join-only materialized views, new optimizer hints for improved QUERY REWRITE analysis, and improved materialized view debugging tools.
A Database Journal Guide to MySQL 5 Certification: The DBA Stream
Ian Gilfillan provides a reference for studying for MySQL 5 certification, including a list of topics covered in the exam, as well as resources from the MySQL site and Database Journal that you can use to enhance your studies.
Mastering OLAP Reporting: Prototype KPIs in Reporting Services
Need a quick KPI prototype, using Reporting Services / Analysis Services 2000 or 2005? BI Architect Bill Pearson leads hands-on practice in presenting simple KPIs to management with Reporting Services.
Save and Retrieve Datasheet Layout with the Windows Registry
Danny Lesandrini shares a block of code that will allow users to modify (and save) datasheet layout when they are using a compiled MDE version of your Access applications.
Oracle RAC Administration: Part 3 - Administering the Clusterware Components
This article examines two important components of Oracle Clusterware, : the voting disk and the Oracle Cluster Registry (OCR).
Database Mail in SQL Server 2005
Learn how to configure and send email with the new SQL Server 2005 mail feature, Database Mail.
How to Build an ASP.NET Web Site Using DB2 Data with Visual Studio 2005
Paul Zikopoulos shows you how easy it is to be a .NET developer and work with DB2 UDB V8 or DB2 9 databases when building a .NET-based Active Server Pages (ASP.NET) Web site that's backed by a DB2 data store.
Other MDX Entities: Perspectives
Leverage Perspectives, new to Analysis Services 2005, through MDX. BI Architect Bill Pearson leads hands-on practice with this means of visually focusing data retrieved from the UDM.
SQL Server 2005 Integration Services: Performance - Part 30
The superiority of SSIS over DTS goes beyond functionality alone, providing numerous efficiency benefits, which we will present in more detail in this article.
Oracle 10g: A Self-Managed Database?
A DBA's worst nightmare, or the answer to a prayer? Learn how Oracle's Automatic Manageability Features may affect you.
Creating Database Using DBCA on Redhat 4.2 Advanced Server
Part 15 of the RACing ahead with Oracle on VMware Series looks at the companion CD installation and DBCA, creating the ASM instances.
MS SQL Joins: Part 2 - OUTER Joins
In Part 1 of this series, the purposes and reasons for using Joins were explored. Part 2 covers using OUTER Joins to extract data for reports and viewing.
Interview Tips for Aspiring Junior DBAs
Armed with your fresh, just out of school knowledge of Oracle (and combined with virtually no workplace or on the job experience), what can you do to improve your odds of being hired? Read on to learn how to conquer the 'no experience-no job, no job-no experience' cycle.
SQL Server 2005: Part 5 - Unattended Installation
Part 5 of this series demonstrates how to install SQL server 2005 service pack 1, to update the server components using command line options.
An Introduction to LINQ: Part 2 - Querying Relational Data
In Part 1 of this series on LINQ, you learned all the basics of LINQ. With that background, this installment will now focus on the use of LINQ in querying relational data. Specifically you will learn DLINQ and its role in querying relational data.
Monitoring SQL Servers Availability
This article discusses why a DBA should monitor instance availability, and provides an approach to automating this process.
Oracle 10gR2 Adaptive Thresholds: Part 2 - Implementation
This article the last in this series demonstrates how to implement adaptive thresholds and use their metrics to effectively detect significant performance threshold violations while tuning out the "noise" from false positives.
Binding DB2 Data to Visual Studio 2005 Windows Applications
Paul Zikopoulos shows how easy it is to be a .NET developer and work with DB2 UDB V8 or DB2 9 databases. Specifically, I will show you how to quickly create a Windows application and bind DB2 UDB V8 data to various controls on the Windows form.
Tips and Tricks for Oracle in a UNIX World
The title says it all. Join Steve Callan as he shares some of his personal favorite Oracle tips.
Common Table Expressions (CTE) in SQL Server 2005
This article, by Scott Mitchell, examines a new feature in SQL Server 2005, Common Table Expressions (CTE).
SQL Server 2005 Integration Services: Part 29 - Digital Signing of Packages
Marcin Policht presents the mechanism that is used to detect unauthorized changes to packages, prevent altered packages from being launched, explain the idea behind it and demonstrate its implementation.
Installing 10gR2 Database Software
Part 14 of RACing ahead with Oracle on VMware covers the database software only installation and the listener setup.
BlackBelt Administration: Linked Reports in SQL Server Management Studio
Create Linked Reports from within SQL Server Management Studio. BI Architect Bill Pearson examines another approach for using Linked Reports to provide multiple "versions" of a single-source report.
SQL Server 2005 Integration Services: Part 28 - Security Features
Part 28 of SQL Server 2005 Integration Services series covers the remaining security features intended to protect the confidentiality and integrity of your packages.
DBA from Crisis to Confidence
This article describes some of the steps that a DBA in crisis can take for a thorough post-mortem.
Oracle: Separating Numbers and Letters
Learn how to make account "numbers" in your database real numbers or real alphanumeric strings across the board.
MS SQL Joins: Part 1
This article explores various methods of Joining, where data from multiple tables are returned in one record set by defining logical relationships.
Introduction to the Analysis Services 2005 Query Log
Business Intelligence Architect Bill Pearson leads a hands-on examination of the Analysis Services 2005 Query Log. In addition, we discuss customization of the Query Log and its use as a direct reporting source.
SQL Server 2005 - Unattended Installation: Part 3 - Using an .ini File
Part I and II of this series discussed how to install the SQL Server 2005 Client component and Data Service component respectively.This section of this series illustrates how to install SQL Server 2005 Database Services and SQL Server Analysis Services on a host machine using an .ini file.
Installing 10gR2 Clusterware on Redhat 4.2 Advanced Server
Part 13 RACing ahead with Oracle on VMware Series covers the first steps towards installing Oracle Clusterware on Redhat 4.2.
MDX Operators: The IS Operator
Compare objects with the MDX IS operator. Join Architect Bill Pearson in an examination and hands-on practice session.
MSSQL - The Apply Operator
This article discusses the APPLY operator, one of the new T-SQL enhancements, and provides a few examples of how it can be used.
Types of Tables in Oracle
Learn how to specifically identify a table's purpose or function.
A Database Journal Guide to MySQL 5 Certification
Ian Gilfillan provides a reference for studying for MySQL 5 certification, including a list of topics covered in the exam, as well as resources from the MySQL site and Database Journal that you can use to enhance your studies.
SQL Server 2005 Integration Services: Part 27 - Working with SQL Server 2000 DTS Packages
This article explores several workarounds that facilitate modifying and managing legacy DTS packages until you are ready to migrate them into SQL Server Integration Services format.
SQL Server and MOM: Part 2 - Importing Management Packs
Part 2 of this series discusses importing the MOM 2005 Management Packs and Reports and monitoring SQL Server 2005.
Oracle 10gR2 Adaptive Thresholds: Part 1 - Overview
This article the first in this series discusses how adaptive thresholds are designed to improve the detection of threshold violations, including the ability to discern a false positive from a true warning.
SQL Server 2005 - Unattended Installation: Part 2 - Command Line Switches
Part 2 of this series illustrates how to install SQL Server 2000 Database Services on a server, using various command line switches.
BlackBelt Administration: Linked Reports in Report Manager
Use Linked Reports to provide multiple "versions" of a single source report, as a mechanism for restricting consumer groups to their respective data in accordance with a "need to know."
Show Me The Tables!
Learn how to develope ASP pages that make your Microsoft Access data available to Web users in real time.
Oracle RAC Administration: Part 2 - CRS Commands
Part 2 of this series continues with a discussion of how the CRS commands can be used to fix/troubleshoot the quirkiness of Real Application Clusters.
How Does Oracle Perform Math?
Steve Callan discusses how to make your database perform calculator-type functions, focussing on the LN(x) natural logarithm function.
DB2 Universal Database: Part 6 - Productivity Features of the SQL Builder
Part 6 of this series focuses on some of the SQL Builder features that accelerate application development.
Mastering Enterprise BI: Working with Measure Groups
Leverage multiple fact tables within a cube with Measure Groups. Join Architect Bill Pearson in a hands-on exploration of Measure Groups, another exciting feature that debuts with Analysis Services 2005.
SQL Server 2005 Integration Services: Part 26 - Checkpoints
Part 26 of this series presents the checkpoint functionality. This feature, missing from Data Transformation Services in SQL Server 7.0 and 2000, can help you save you a significant amount of time and resources when rerunning packages, which partially failed during their initial execution.
Data Replication Using Oracle Downstream Capture
Vincent Chan describes how to effectively leverage and set up Oracle archived-log downstream capture technology to replicate your enterprise data.
SQL Server 2005 - Unattended Installation: Part 1
Learn how to do unattended installation of different server components, client components and tools of SQL Server 2005.
MDX Set Functions: The Distinct() Function
Remove duplicate tuples from a set with the DISTINCT() function. Architect Bill Pearson leads hands-on practice with this basic, but useful, MDX Set function.
Executing SQL Stored Procedures From Inside a Web Application
This article, the last in the Stored Procedure series, focuses on RETURN parameters.
Oracle RAC Administration - Controlling the Cluster with CRS command
Tarry Singh begins a new series on Oracle RAC Administration with a discussion on the challenges of administering a RAC database.
Dynamic Management Views and Functions
DMVs and DMFs are a mechanism to allow you to look at the internal workings of SQL Server using TSQL. Learn how to use Dynamic Management Views and Functions and how you can simplify your ability to retrieve system information.
IBM DB2 Universal Database and the Java Developer? Absolutely! Part 5
Part 5 of this series, focuses on the built-in SQL Builder in Rational AD, examining how to use it to build SQL statements that you can use in your Java programs, Web services, routines, or just stand-alone statements.
Finding and Setting SQL*Plus Settings
Learn how to set up and create customized settings files via the STORE command, and how to restrict access to these files and other commonly used scripts.
Implement User Preferences in MS Access
Learn how to provide your users with several useful preference options, including which form opens at startup, Remember and open the last record visited, identify specific Fax-Printer to use with reports and more.
Oracle 10gR2 Transportable Tablespace Enhancements: Tablespace Versioning and Data Jukeboxes
The final article in this series discusses how to utilize the capabilities of Transportable Tablespaces to create a data "jukebox," as well as how to transport tablespaces directly from Recovery Manager backup files without incurring any database unavailability.
SQL Server 2005 Integration Services: Part 25 - Transaction Support
The latest article in our series discusses improvements in the transactional support implemented in SQL Server 2005 Integration Services.
Setting up ASM on Oracle 10g
Part 12 of Racing Ahead with Oracle on VMware offers a brief look at what ASM is, and how to go about installing, configuring and stamping the ASM disks.
INTERSECT Operator in SQL Server 2005
This article offers a detailed look at the functionality of the INTERSECT operator in SQL Server 2005.
MySQL, Today's Contender
With MySQL 5.0 now well established, the prevalence of the toy database brigade has greatly diminished. MySQL has a rich feature set catering to a much greater proportion of users, and recent moves by Oracle have shown that they too take MySQL seriously. Read on to learn where MySQL stands today.
Mastering OLAP Reporting: Reporting with Analysis Services KPIs
Put Analysis Services 2005 KPIs to work in Reporting Services. Architect Bill Pearson leads hands-on practice in extending Analysis Services KPIs to the Reporting Layer.
SQL Server 2005 Integration Services: Part 24 - Package Execution
Continuing our coverage of topics dealing with various aspects of managing SQL Server 2005 Integration Services packages, we now turn our focus to their execution.
Oracle Multi-Directional Data Replication
Part one of this series illustrates the procedures for setting up bi-directional data replication and conflict resolution between two databases using Oracle 10g Streams.
Rolling Back Layers of Automatic Undo Management
This article discusses why you receive the ORA-01555 error in Oracle 10g when using Automatic Undo Management (AUM).
Mastering Enterprise BI: Introduction to Key Performance Indicators
Architect Bill Pearson introduces a powerful feature that debuts in Analysis Services 2005: Key Performance Indicators (KPIs). In this article, we create a KPI, based partially upon a calculated member we construct to support it.
RACing ahead with Oracle on VMware: Final Checks and OCFS2 Setup
Part 11 of "RACing ahead with Oracle on VMware" continues with the final checks and the installation and configuration of OCFS2.
Using a Stored Procedure to Insert a New Record
Part 3 of this series continues working with OUTPUT parameters, focusing on using a stored procedure to insert a new record from data collected on a web page, then passing an identity value created from that row back to the web page.
Except Operator in SQL Server 2005
Learn about the functionality of the EXCEPT operator, which returns all of the distinct rows from the left side and removes all of the rows from the result set that match the on the right side of the EXCEPT operator.
MDX Set Functions: The ToggleDrillState() Function
Architect Bill Pearson returns to expose the MDX TOGGLEDRILLSTATE() function, continuing his extended examination of the mechanics of drilling up and down within MDX.
Quest Capacity Manager Review
Greg Larsen reviews Quest Software's Capacity Manager, offering his opinion of it strengths and weaknesses as a tool for performing SQL Server capacity management.
Oracle 10g Transportable Tablespace Enhancements: Cross-Platform Conversion Capabilities
We begin a new series that demonstrates how Oracle's new Transportable Tablespaces enable the transfer of data between databases on different servers, across platform boundaries regardless of those platforms' operating systems.
Controlling Backups with Database Control
Steve Callan examines using a flash recovery area, and Database Control to make a high availability, easy to recover database that's a breeze to configure and use.
IBM DB2 Universal Database and the Java Developer: Using Rational AD
Part 3 of this series discussed how to use Rational AD to build a stored procedure, deploy it, and test it. In this part, we will look at how to build a user-defined function (UDF).
SQL Server 2005 Integration Services: Packages Deployment
Part 23 of this series continues exploring different ways to store SQL Server 2005 Integration Services packages with a discussion of other mechanisms for managing their storage while focusing attention on deployment procedures.
MySQL Data Methods
Virtually everything in MySQL involves data in some way or another because the purpose of a database management system is, by definition, to manage data. Here you'll learn about data types, values, categories and more.
Securing Remote Access and Copy with SSH and SCP
Part 10 of "RACing ahead with Oracle on VMware" continues the remote access set up and looks at compatible SSH and SCP support.
MSAccess: Load Subforms Dynamically
There are many things a developer can do to squeeze an incredible amount of power and performance out of an Access database. The first, and foremost, is to use subforms to avoid loading data until you need it.
OUTPUT clause in SQL Server 2005
This article demonstrates the usage of the new SQL Server 2005 clause, OUTPUT.
Connecting and Prepared Statements with the mysqli Extension
This article focuses on connecting to MySQL using PHP's mysqli extension, and takes a look at prepared statements, the exciting new feature this extension, in combination with newer versions of MySQL, makes available.
Report Builder: Creating a Report Model
Provide "self-serve" reporting and data exploration to information consumers with Report Builder. Architect Bill Pearson leads hands-on practice in creating a basic Report Model within Business Intelligence Development Studio.
SQL Server 2005 Integration Services: Package Management
Throughout our series of articles dedicated to SQL Server 2005 Integration Services, we have worked with a number of packages; however, we have not yet explored the various options related to their storage. We will cover them in this article - including implementation details and security implications of each.
Oracle Bi-Directional Data Replication
Part one of this two part series provides the procedures for setting up bi-directional schema-level replication and conflict resolution between two databases using Oracle 10g Streams.
Combining Overloading and Session Management in Oracle
One feature often times overlooked by developers is PL/SQL's ability to allow overloading. Learn how to quickly and easily build a versatile session management tool, which can manage one or many sessions.
Mastering Enterprise BI: Extend the Data Source with Named Calculations- Part 2
Architect Bill Pearson returns in the second half of a hands-on exploration of Named Calculations. In this article, we continue with using Named Calculations to support aging buckets within an Analysis Services 2005 cube.
Executing SQL Stored Procedures from Inside a Web Application: Part 2
How to pass parameters (variables) IN, OUT, and RETURN error codes from a Stored Procedure to a Web Page.
Configuring RHEL/Centos 4.2 Server for Oracle RAC 10g R2 Readiness
Part 9 of this series continues the setup of the RHEL/Centos 4.2 server, covering setting up the Swap Space, Shared Memory, Semaphores, File Handles and Hangcheck Timer.
SQL Server 2005's EXECUTE AS Statement
MAK demonstrates how SQL Server 2005's new transact SQL statement comes in handy when testing the permissions of a particular user.
Displaying the Sizes of Your SQL Server's Database's Tables
In this article we'll look at two ways to grab some data from an SQL Server and display it in a Web page.
Set Functions: The DrillUpLevel() Function
Architect Bill Pearson returns with an examination of the DRILLUPLEVEL() function. This article continues our exploration of drilling up and down within MDX, with a focus upon opportunities for parameterization.
SQL Server 2005 Upgrade Advisor
Before you start migrating your existing applications to SQL Server, you should consider looking into what the SQL Server 2005 Upgrade Advisor can do for you. Greg Larsen discusses what the Upgrade Advisor is and how to use it.
SQL Server 2005: TableDiff Utility
Learn how to take advantage of TableDiff functionality to compare tables.
IBM DB2 Universal Database and the Java Developer: Part 3 - Building Stored Procedures
This article discusses how to build stored procedures using the Data Definition view.
SQL Server 2005 Integration Services: Import and Export Wizard
This article focuses on two techniques that work around the limitations of Control Flow tasks, in particular, Transfer SQL Server Object, of SQL Server 2005 Integration Services.
Oracle 10g Scheduler Enhancements: Part 2 - Job Chains
The final article in this series illustrates how an Oracle DBA can use the new Job Chain capabilities of the Scheduler in Oracle 10g Release 2 (10gR2) to schedule and trigger multiple interrelated and dependent tasks based upon specific yet complex sets of processing rules.
Trees and Hierarchies in Oracle
An excellent book by Joe Celko (Joe Celko's Trees and Hierarchies in SQL for Smarties) shows several means of arranging hierarchical data. This article combines the familiar emp table and some of the examples shown in Celko's book.
MySQL 5.1 - The Next Generation
Ian Gilfillan examines some of the new features in MySQL 5.1.
Mastering OLAP Reporting: Meet Business Needs with Matrix Dynamics - Part 2
Generate duplicate data regions for each of multiple business territories, based upon selections chosen by the consumer at run time. In this article, Architect Bill Pearson leads hands-on practice in delivering an "off-the-beaten path" result, courtesy of the power of the matrix data region.
MS Access: Manual Table Relinking
Last month's article demonstrated how to implement table linking, with no user interaction required. At times you need to allow for manual table relinking. This article consolidates some of the tools and tricks that the author has used over the years to serve as a starting point for your solution.
Cluster Latency and Overhead Alarms in Oracle 10g RAC
The concluding installment of this series discusses the cluster latency and overhead alarms, the Global cache alarms and the ASM alarms.
COPY_ONLY Backups in SQL Server 2005
This article demonstrates how the new COPY_ONLY option in the backup command does not affect the archive point, thereby not affecting the restore process.
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Part 1
Analysis Services 2005 introduces yet another flexible capability: Named Calculations within the Data Source View. Join Architect Bill Pearson in a hands-on exploration of another means for supporting agings and other cube structures by creating a "virtual" formula column in an underlying source table.
SQL Server 2005 Integration Services: Migration Tasks
This installment completes the overview of Control Flow tasks by describing the characteristics of the remaining tasks in this category - two of which are intended specifically for server logins and master stored procedures - and one (called Transfer SQL Server Objects task) with more universal purpose.
Oracle's Automatic Storage Management
Oracle Database 10g's Automatic Storage Management (ASM) makes Linux and UNIX database environments disk space management a snap.
Database Migration - It's More than Running exp and imp
Part 2 of this series provides a checklist/plan covering steps and procedures for the before, during, and after phases.
Ten of the Biggest Mistakes Developers Make With Databases
You are probably still pumping data in and out of a database, just as we all did a decade or more ago. That makes it all the more surprising that mistakes are still being made that date back to the good old days of Windows 95 and before.
Set Functions: The DrillDownLevelTop() and DrillDownLevelBottom() Functions
Join Architect Bill Pearson as he explores the DRILLDOWNLEVELTOP() and DRILLDOWNLEVELBOTTOM() functions. This article continues our examination of drilling up and down within MDX.
Executing SQL Stored Procedures from Inside a Web Application: Part 1
There are so many good reasons to use SQL Stored Procedures from inside web applications (as opposed to passing a query string), that the benefits of working with them will far outweigh any learning curve required.
Configuring RHEL/Centos 4.2 Server for Oracle RAC 10g R2 Readiness
In part 7 of this series, we started a detailed explanation of setting up a RHEL 4.2 or Centos 4.2 OS. This article works towards our goal by configuring the Network and Linux servers for Oracle readiness.
Compairing System Tables and Catalog Views
MAK compares using catalog views in SQL Server 2005 with system tables in SQL Server 2000.
Cycling the ERRORLOG file and Deleting Backup History Information
Gregory Larsen explores how to cycle the SQL Server error log, and why you might want to do this. Then he discusses backup history information and why you would need to periodically remove some of the older history information.
The Try/Catch Block in SQL Server 2005
Gregory Larsen explores the new TRY/CATCH block, a more programmable error trapping convention in T-SQL code, and explains the new system functions for returning error codes and messages.
How to Make SQL Dependency Work Where It Doesn't
One of the most anticipated new features in ASP.NET 2.0 and SQL Server 2005 is SQL Dependency. This article will show you how to set up surrogate procedures so that you can use SQL Dependency to its fullest and bypass its limitations.
SQL Server 2005 Integration Services: Part 19 - Database Management Tasks
Marcin Policht returns to Control Flow-related topics, concentrating on tasks designed to assist with the migration of data as well as server and database objects.
Oracle 10g Scheduler Enhancements: Part 1
Learn how to utilize several enhancements added to the Scheduler in Oracle 10g Release 2 (10gR2) to create even more complex schedules with the new schedule combination features, and how to trigger a job based on an event in an event queue.
Automatic Table Relinking
Danny Lesandrini shares a bit of code that demonstrates how your database application can relink to the data tables.
Database Migration: A Planned Approach
Migrating from one version to another may be as simple as exporting the old and importing into the new, but chances are there is a lot more involved than first meets the eye.
Mastering OLAP Reporting: Using Matrix Dynamics: Part 1
Generate duplicate data regions for each of multiple business territories, based upon selections chosen by the consumer at run time. In this article, Architect Bill Pearson leads hands-on practice in delivering an "off-the-beaten path" result, courtesy of the power of the matrix data region.
Monitoring Oracle 10g RAC with Quest Spotlight on RAC: Part 2
This second article of the series takes a look at the Oracle Aggregated Alarms and the Balance Alarm.
The OPENROWSET function in SQL Server 2005
This article illustrates BULK operations of the OPENROWSET function, in SQL Server 2005.
IBM DB2 Universal Database and the Java Developer? Absolutely! - Part 2
Learn how to import the objects of a database from the Database Explorer view into the Data Definition view, in the Rational AD IDE and explore some of the data-related tasks that you can perform, as well as how to deploy the solution.
Process Analysis Services Objects with Integration Services
Process Analysis Services 2005 objects with an Integration Services package. Architect Bill Pearson leads hands-on exploration of one means of automating object processing in Analysis Services 2005.
SQL Server 2005 Integration Services - Part 18: The Destination Script Component
Marcin Policht reviews the characteristics of the Destination Script Component and provides a simple example illustrating its implementation.
Project Raptor: Oracle's New Tool for DBAs and Developers
Come along for the ride as Steve Callan test drives Oracle's new tool, Raptor.
MDX Set Functions: DrillDownLevel()
Bill Pearson continues to expand his examination of "drilling mechanics" in MDX. In This article, we explore the DrillDownLevel() function.
SQL 2005 Reporting Services: Part 2
Part 2 of this series examines how to begin creating reports using the SQL Server Business Intelligence Development Studio (BIDS).
RACing ahead with Oracle on VMware Series: Part 7
Tarry Singh examines configuration errors and issues when installing RHEL 4.2 on VMware Virtual Machines.
Using More Advanced JDBC Features
Expand your basic knowledge of connecting to a database from a Java application using JDBC. Discover how to use just about any database application you may want. In this example its a Microsoft Access database.
SQL Server 2005 - Data Definition Language Triggers
This article explores the new SQL Server 2005 feature "DDL trigger" and EVENTDATA() function.
Upgrade Oracle 9i RAC to Oracle 10g RAC
This article provides the procedures for converting Oracle 9i (126.96.36.199) RAC to Oracle 10g (10.2.0.1) RAC on Red Hat Enterprise Linux 3 (RHEL3).
SQL Server 2005 Import/Export Wizard
DTS is no more. Instead, with SQL Server 2005 you get a new transformation tool named SQL Server Integrated Services (SSIS). Greg Larsen explains what new features are available and what features have been removed.
SQL Server DTS Designer
Adding DTS to your repertoire will bring a whole new dimension to your skill set. Learn how to create a DTS Package using the DTS Designer.
Oracle 10g Automatic Storage Management: Part 3 - Advanced Features
Oracle 10g's Automatic Storage Management (ASM) features offer powerful tools to Oracle DBAs to create and manage a robust, flexible, scalable file storage system ready for access by any existing Oracle database instance. This article, the third and final in this series, navigates the myriad ASM file naming conventions and templates, provides examples of ASM storage management commands for adding and removing disks and disk groups, and covers how to convert an entire Oracle database to use ASM storage instead of regular file system storage.
Database Security and Patches: Part 5 - Testing Critical Updates
How do you validate and verify the fixes made in Oracle's critical patch updates? Sometimes you need to test on your own, but the test results may not be accurate because Oracle does not release all of the details about a bug or security flaw. What are the types of security holes present in Oracle, and where can you learn more about testing your own system?
Monitoring Oracle 10g RAC with Quest Spotlight on RAC: Part 1
Tarry Singh offers a peek into Quest Spotlight on RAC, now out of beta and soon to go GA. SoRAC provides detailed diagnostic information by drilling down individual instances and merging the appropriate calculations into one interface.
SQL Server 2005: Part 17 - Data Flow Transformation
Continuing the topic of the Script Component in SQL Server 2005 Integration Services, we will now take a closer look its usefulness as a Data Flow Transformation.
De-coding Oracle's Latest Fusion Update
Oracle has made progress in its applications, but not as much as the company would like to believe, writes our Enterprise Advisor columnist.
Book Review: Hands-On Microsoft Access
Is Hands-On Microsoft Access the book for you? If you are new to database design, then it is surely in the running. If you are looking for a book that is easy to follow and comprehensively explains object properties, then you will want to give it a look. If you want to understand native Access functions or want your clients to, then this is definitely the book you need.
Oracle Grouping with the ROLLUP Operation - Just SQL: Part 8
Grouping and summing at multiple levels often takes an application to perform. Take a look at the ROLLUP operation within Oracle and perform these operations in a single SQL statement.
The Row_Number() Function in SQL Server 2005: Part 2
Learn how to delete multiple duplicate rows using the new Row_Number() function feature, common table expression and the ANSI co-related subquery.
Date and Time in MySQL 5
MySQL 5 has significantly improved the flexibility of its date handling. There have been a few significant changes you will need to be aware of, including some potentially nasty gotchas for users more familiar with the old ways.
Mastering OLAP Reporting: Display a Dataset Field in a Report Page Header
Architect Bill Pearson leads hands-on practice in displaying a Dataset field in the Page Header of an OLAP report. In this article, we examine one of the simpler approaches for surmounting an apparent limitation within Reporting Services.
OC4J - The "Other" Oracle Application Server
This article is the first of a multi-part series focusing on Oracle's more Web-like version of Application Server, J2EE applications, and XML. Being able to differentiate the versions of Application Server , basic administration , performing a new type of installation, and acquiring an XML editing tool are some of the topics that will be covered.
Implementing the Source Script Component in SQL Server 2005
Source Script Component is useful in scenarios, where data source has a difficult to parse format. A fairly common example involves text files containing a number of multi-line records with a varying number of lines per each. In this article, we will present an implementation of the Source Script Component, which processes such files.
Usage-Based Optimization in MSSQL Server 2005
Business Intelligence Architect Bill Pearson introduces simple Usage-Based Optimization in MSSQL Server 2005 Analysis Services. In this article, we practice employing the Usage-Based Optimization Wizard to fine tune our aggregation design based upon cube usage statistics.
Preparing for a 2-node Oracle 10gR2 RAC
Tarry Singh discusses the preparation and planning for the installation of a 2-node RHEL 4.2 Linux VMware for Oracle 10gR2 RAC. Also covered in the article are groundbreaking technologies like OCFS2 and ASMlib, and an overview the ESX Server in general.
Installing and Configuring SQL 2005 Reporting Services
Don Schlichting begins a new series on SQL Server Reporting Services. This first part of the series covers licensing, installation and configuration.
The Row_Number() Function in SQL Server 2005
This article discusses two new features of SQL Server 2005, the Row_Number() function and Common Table Expression, and explains how to use both of the features together to delete duplicate rows.
MDX Set Functions: The DRILLUPMEMBER() Function
Architect Bill Pearson explores the MDX DRILLUPMEMBER() function, continuing his extended examination of the mechanics of drilling up and down within MDX.
SQL Server and MOM 2005
This first of a new series discusses the basics of installing Microsoft Operations Manager 2005 and how to use MOM to monitor SQL Server.
SQL Server 2005: Debugging Script Component - Part 15
Continuing the coverage of debugging features in SQL Server 2005 Integration Services Data Flow, we will now focus on the Script Component.
Database Security and Oracle Patches: Part 4
In part 3 of this series, a patch for an Oracle 10.2.0.1 database was obtained from MetaLink and cached in a cache repository. In Part 4, the Oracle's OPatch utility will be used to actually apply the cached patch.
Oracle 10g Automatic Storage Management: Part 2 - Sample Implementation
Oracle 10g's Automatic Storage Management (ASM) features offer powerful tools to Oracle DBAs to create and manage a robust, flexible, scalable file storage system ready for access by any existing Oracle database instance. This article -- the second in this series -- provides a simple yet practical demonstration of setting up an ASM instance in both the Linux and Windows NT environments for purposes of exploration and experimentation, including how to migrate existing tablespaces to the ASM storage environment.
The DB2 Query Patroller and Design Advisor
Learn how to pull a subset of the data from the DB2 QP historical information and pass it to the DB2 Design Advisor using the command-line interface to take advantage of this feature.
Backup Your SQL Server Analytical Database: Part 2
Part 1 of this article provided general guidelines on how to backup a SQL Server 2000 Analytical database manually. Part 2 examines how to automate this backup process of analytical databases from various servers.
Interactive Sorting Within Reporting Services
Architect Bill Pearson conducts hands-on practice in establishing interactive sort support in Reporting Services.
Consume Web Service from Access
In response to an April 2005 article, "How to Pass Access Data Across the Web," a reader expressed concern over using the Microsoft Internet Explorer library because of security issues. Appreciating that others might share the same concern, Danny Lesandrini enhanced the process by substituting a Web Service for the traditional ASP/Querystring web page approach described in the article. This article explains the process.
RACing ahead with Oracle on VMware: Part 5: The powerful SRVCTL Utility
Part 5 of this series discusses using the SRVCTLutility to see if our database is functioning as it should.
Database Security and Patches: Part 3
Part 3 of this series examines some new functionality found in Oracle10g's Enterprise Manager Console.
MySQL Stored Functions
Continuing with our series on Stored Procedures and Functions, this month we focus on Stored Functions.
Intro. to MSSQL Server Analysis Services: Named Sets Revisited
Business intelligence architect Bill Pearson introduces Named Sets within the context of MSSQL Server 2005. In this examination of the basics, we get hands-on practice defining a Named Set within the Business Intelligence Development Studio.
SQL Server 2005 Integration Services: Part 14 - Debugging Data Flow
In the previous installment of our series, we discussed various approaches to debugging Control Flow components of SQL Server 2005 Integration Services. The purpose of this article is to demonstrate debugging methods, which are relevant within the context of SSIS Data Flow.
Backup your SQL Server Analytical Database: Part 1
One of the many tasks of the SQL Server Database administrator is to take and maintain backups of SQL Server databases. This includes taking backups of analytical databases as well. Read on to learn how to backup an Analysis service database.
Using the SQL Server 2005 Integration Services Class Library
Learn how to extend SQL Server 2005 Integration Services (SSIS), the new ETL tool that ships with SQL Server 2005, to perform operations that aren't already bundled with the product.
MDX Essentials: Set Functions:Two specialized DRILLDOWNMEMBER() functions
Architect Bill Pearson continues his examination of drilling up and down within MDX, exposing two specialized DRILLDOWNMEMBER() functions, DRILLDOWNMEMBERTOP() and DRILLDOWNMEMBERBOTTOM().
Controlling Transactions and Locks Part 5: SQL 2005 Snapshots
This article explores the new Row Versioning feature in SQL 2005, and the two new transaction isolation levels, Read Committed Snapshot and Snapshot Isolation, that take advantage of it.
RACing ahead with Oracle on VMware: Part 4
The last article in this series talked about installing clusterware on two Windows 2003 nodes. Part 4 of this series discusses how to install a fully working RAC with ASM on the disk setup that was explained in Part 3.
Using SQL Server 2005 sqlcmd Utility
SQL Server 2005 provides some new command line utilities. One such utility is "sqlcmd". In this article, Gregory Larsen explains some of the features this new command line utility brings to administering SQL Server.
Restoring Your Data 101
Now that your data is backed up, you will need to learn how to restore your data when it is applicable. You might want to restore your data because of a hardware failure or restore data to a backup or standby server. The reasons are endless but the most important thing is to be prepared.
SQL Server 2005 Integration Services: Part 13
Continuing our discussion about monitoring execution of SQL Server 2005 Integration Services packages, we will now turn our attention to debugging and its considerably more sophisticated capabilities, focusing on aspects specific to SSIS.
XML Queries and Indexing in SQL Server 2005
For the first time, SQL Server 2005 offers a native XML data type. See how you can store and query XML documents as part of a SQL Server table, and how to use XML indexes to make queries against these columns more efficient.
DB2 Universal Database Support for .NET Development and Concurrent Data Readers
Learn how to leverage the ability of DB2 UDB V8.2.2 to execute multiple reader commands on a single database connection.
Oracle 10g Automatic Storage Management: Part 1 - Overview
Oracle 10g's new Automatic Storage Management (ASM) features allow an Oracle DBA to take advantage of a robust, flexible, scalable file storage system that any Oracle database instance can access. This article - the first in this series -- provides a high-level overview of the ASM architecture, and should be helpful to any DBA contemplating the adoption of ASM for storing Oracle database files as part of a high-volume storage solution.
Database Security and Patches: Part 2
Part 2 of this series covers the mechanics of installing a patch.
Intro to MSSQL Server Analysis Services: Migrating to Analysis Services 2005
Business Intelligence Architect Bill Pearson leads the hands-on migration of an old friend, the Analysis Services Database FoodMart 2000, to MSSQL Server 2005. We then verify the action of the Migration Wizard from two perspectives, the SQL Server Management Studio, and the SQL Server Business Intelligence Development Studio.
RACing ahead with Oracle on VMWare: Part 3
Part 3 of this series discusses the installation of Clusterware on a 2-node windows 2003 server with VMWare.
Export Data To Excel
Last month we explored the process of importing data from Microsoft Excel. This month, we look at how to get your data OUT of Access and back into Excel.
Monitor CPU Usage of All Running Processes: Part 2
Part 1 of this article illustrated how to monitor CPU usage of running processes on a local machine or from a remote machine. This article illustrates how to monitor the CPU usage of different running processes on different machines and collect the information in a database.
Working with Databases in ASP.NET 2.0 and Visual Studio 2005
In this article we'll look at how to connect and display data from a database in ASP.NET 2.0. Specifically, we'll see how to use both the programmatic and declarative approaches for accessing data, as well as the basics of displaying data using the GridView control. Read on to learn more!
MSSQL Server Reporting Services: Multiple Value Selection in a Parameter Picklist
Architect Bill Pearson conducts hands-on practice in creating a report parameter that allows users to enter multiple values at runtime. First, we migrate a Reporting Services 2000 report to Reporting Services 2005.
SQL Server 2005 Integration Services: Part 12 - Logging
Continuing his series on SQL Server Integration Services, Marcin Policht takes a closer look into the various characteristics of SSIS logging and their corresponding configuration options.
Database Security and Patches: Part 1
Part 1 of this series provides a review of database security and looks at a well-known security hole in Oracle 188.8.131.52.
MDX Essentials: The DRILLDOWNMEMBER() Function
Architect Bill Pearson returns to expose the MDX DRILLDOWNMEMBER() function, kicking off an extended examination of the mechanics of drilling up and down within MDX.
Controlling Transactions and Locks: Part 4
The first three installments of this series applied to both SQL 2000 and SQL 2005. Part 4 introduces Snapshots, new in MSSQL 2005.
"RACing ahead with Oracle on VMWare Series": Part 2
Part two of this series continues the discussion of Oracle RAC, how to install the VMWare tools and set up RAW disks on Windows 2003 Enterprise Server.
Monitor CPU Usage of All Running Processes Part I
MAK demonstrates how to keep track of the CPU load history on each running process that is running on SQL Server.
Setting DTS Package Properties at Runtime
Wouldn't it be nice if you could change DTS package properties at runtime? If you could then you could have a generic package that processed different files, loaded different database tables and/or connected to different servers, plus a slew of other things. In this article, Gregory Larsen discusses how to use the dynamic property task to set DTS package properties at runtime.
RACing ahead with Oracle on VMWare Series: Part 1
Part l of this series offers a brief introduction to setting up VMWare and the importance of other tools such as VNC, freeNX or NoMachine, PuTTY, etc.
Oracle 10g Tablespace Enhancements
Oracle 10g's daunting array of new features like extended FLASHBACK technology, Automated Storage Management, and Grid Computing sometimes overshadow the more mundane -- yet no less impressive! - improvements to its logical storage capabilities. This article illustrates several enhancements to Oracle's already robust logical storage structures, including the SYSAUX tablespace, BIGFILE tablespaces, tablespace groups, and tablespace renaming.
Backing up and Restoring Databases in SQL 2000
If you are a Database Administrator (DBA), it is the nature of the beast to get called away at the most inappropriate time. Aren't you glad you backed up your database(s)? Or, did you? This article introduces you to the different types of backup strategies available in SQL Server 2000.
Compress SQL Server backups using WinZip: Part 2
Part two of this series discusses how to delete the original backup file, after zipping the backup file using WinZip, and how to uncompress the compressed backup files using the WinZip command line wzunzip.exe.
Views are extremely useful for DBAs, developers, and users alike, but are you getting all you can out of what views have to offer?
SQL Server 2005 Integration Services: Part 11
Part 11 of this series focus on the characteristics of SSIS events and their handlers, and provides examples demonstrating their use.
Introduction to SQLJ
SQLJ offers quick development, with less code, ease of debugging, and automatic performance improvement.
SQL Server 2005 Integration Services: Part 10
In the previous article of this series, we started exploring methods of incorporating Windows Management Instrumentation (WMI) based data into the package flow control. Use of WMI Data Task implies that you are primarily interested in actual values. However, there might be cases in which you are more concerned about detecting object creation, deletion or modification in order to trigger an appropriate action at that time. This is where WMI Event Task comes into play.
Oracle Performance Tuning: Part 3
Part 3 of this series focuses on generating bulk data using DataFactory from Quest Software.
MySQL Stored Procedures: Part 3
Part 3 of the ongoing series about MySQL's stored procedures, looks at handlers and cursors in particular - both logical constructs that allow added functionality.
Introduction to MSSQL Server Analysis Services: Introducing Data Source Views
Business Intelligence Architect Bill Pearson introduces Data Source Views, a new feature in MSSQL Server 2005 Analysis Services.
Controlling Transactions and Locks in SQL 2000 and 2005: Part 3
Earlier articles of this series introduced Lock Granularity, Transactions, and ACID. Common lock types, such as Shared, Exclusive, and Update were explored, as well as using SP_Lock to obtain current system lock information. In this article, the normal internal SQL locking methods will be manipulated using Lock Hints in order to obtain finer lock control.
Using DDL Triggers to Manage SQL Server 2005
Get an introduction to the extended SQL Server 2005 trigger functionality that Data Definition Language (DDL) triggers provide for both databases and servers.
Just SQL - Part 7: Hierarchical Queries
Whether they are called hierarchical structures, trees, or self-referencing tables they often pose quite a challenge to traverse in any simple manner. But Oracle has a solution.
MDX Essentials: The DRILLTHROUGH Statement
MSAS Architect Bill Pearson introduces Drillthrough concepts, and then focuses on the use of the DRILLTHROUGH statement within MDX.
Core Web Application Development with PHP and MySQL: Part 2
This week we continue with learning how to write Web applications. You'll learn about the basic layout of Web applications, the user interface, 3-tier and n-tier architectures, performance, scalability applications and more.
Running Processes at SQL Server or SQL Agent Startup
Greg Larsen discusses two options to autmatically start a process when SQL Server or SQL Agent starts.
Oracle Performance Tuning: Part 2
There are several relatively easy steps you can take to improve performance. From the user's perspective, one of the most frequently used interfaces with a database involves SQL statements, so getting a handle on them is a good place to start, in terms of being able to see an immediate improvement.
Data Transformation Services
SQL Server 2000 contains a powerful set of tools called Data Transformation Services (DTS) that can help you import, export, and/or transform data from various sources to single or multiple locations. First released with SQL Server 7.0, it has been vastly improved in SQL Server 2000.
Writing Database Objects in CLR: Advanced Scenarios
When you write code to be run inside SQL Server 2005, you usually want to deal with other database objects. Learn how to write a CLR-stored procedure that uses a simple table valued function to handle that scenario in a couple of ways.
SQL Server 2005 Integration Services: Part 9
Following an extended review of various types of Foreach Loop enumerators in the SQL Server 2005 Integration Services, we are turning our attention to other types of package components, which either did not exist in the Data Transformation Services or which functionality has been significantly enhanced. This article discusses a task called WMI Data Reader, which leverages functionality built into Windows Instrumentation Management (WMI) technology.
Core Web Application Development with PHP and MySQL: Part 1
This week we're going to have a a look at writing Web applications. You'll learn about the technologies and protocols that make up the World Wide Web, how they work, how to define Web applications, how to structure them and more. By Prentice Hall PTR.
Returning a Comma-Delimited List of Related Records
In many cases it can be useful to return a comma-delimited list of related records from a database. To provide such functionality there are two basic approaches. This article will examine both approaches to help you decide which is better for your particular application.
Playing With MODELs: Oracle 10g SQL Enhancements - Part 3
Oracle 10g has extended the already robust features of Structured Query Language (SQL) with a plethora of intriguing new capabilities. This article the final in this series reviews the new features that Oracle 10g provides for advanced data modeling and inter-row calculations via the new MODEL clause.
Migration to MySQL with SQLyog ver 4.1
With the release of Webyog's flagship product, SQLyog Enterprise version 4.1, a new ODBC "Power Tool" is introduced that replaces the old "ODBC import tool." Not just a new name for an old tool, the migration tool has been completely re-written and offers a wide range of new possibilities.
Automatic Email Alerts for Low SQL Server Disk Space
While Auto Growth is a good feature, it can eat up hard disk space. This article illustrates how to set up email notification when SQL Server is running out of Hard Disk Space.
MSSQL Server Reporting Services: Relationally-Based Picklists for OLAP Reporting
Architect Bill Pearson explores an approach for supporting MDX picklists in Reporting Services: A table in a relational database to provide centralized, one-stop maintenance of filtering and row axis definition in the reporting layer.
Auto Logout Users for DB Maintenance
There is probably no gentle way to "kick" users out of an application, but when it has to be done, it has to be done. Everything you need to create an auto logout routine is included in the download for this article.
Writing Database Objects in CLR
You can use CLR to author a number of database objects. Follow a canonical example that explains the main steps involved in using and creating a CLR object inside SQL Server 2005.
Just SQL: Part 6 - Two Famous Pseudocolumns
A pseudocolumn is a column that looks like a column but really is not a column. What?! Stick around and find out what a pseudocolumn really is. James Koopmann explores two popular pseudocolumns, ROWID and ROWNUM, and their use.
Native XML Web Services in SQL Server 2005
Discover how easy it will be to expose SQL Server 2005 data as a Web Service to any SOAP 1.2 client.
Oracle Performance Tuning: Part 1
Oracle's Tuning Methodology changed when Oracle 9i was released. The approach went from top-down in 8i to that of following principles in 9i/10g. Neither methodology is absolute as each has its advantages and disadvantages. Read on to learn more.
MySQL Stored Procedures: Part 2
Part 2 of MySQL Stored Procedures covers some more advanced concepts, including conditions and loops.
Introduction to MSSQL Server Analysis Services: MS Excel 2003 and More...
Business Intelligence Architect Bill Pearson takes a look at Excel 2003 as a reporting option for Analysis Services cubes. He then provides an overview of the Microsoft Office Excel Add-in for SQL Server Analysis Services.
SQL Server 2005 Integration Services: Part 8
Marcin Policht continues his overview of different types of enumerators available in the Foreach Loop Container of SQL Server 2005 Integration Services with a look at the Foreach Item and Foreach From Variable, which further extend the scope of the data processing capabilities integrated into the Foreach Loop Container.
DB2 Universal Database and the PHP Developer? Absolutely!
In this article, Paul Zikopoulos explores the features that make programming PHP-based DB2 UDB applications as seamless as possible and ultimately shorten the development cycle for these types of applications.
Database Abstraction in PHP
Database abstraction layers--should we use them? This month's article covers reasons for using abstraction layers, and takes a look at some of the more popular ones used in this rapidily changing field.
"TOP" Clause in SQL Server 2005
One of the new enhancements in SQL 2005 provides the ability to do Data Manipulation using "TOP" clause, returning criteria results that can be displayed using the TIES option. This article illustrates the new enhancements of the TOP clause in SQL 2005.
MDX Essentials: The .UniqueName Function
MSAS Architect Bill Pearson introduces the .UniqueName function. In a hands-on exercise, we create picklist support for report parameterization within MDX queries, among other uses.
The Fundamentals of the SQL Server 2005 XML Datatype
SQL Server 2000 developers who've been yearning for better XML support in SQL Server will be pleased with the new XML datatype in SQL Server 2005. If you're one of them, learn the fundamentals of the datatype so you can get going.
Controlling Transactions and Locks in SQL 2000 and 2005: Part 2
Last month's article introduced transactions. In order for a transaction to meet the requirements of ACID, locks are employed to insure data integrity and multi-user access. This month, we will begin by introducing several different types of lock modes employed by MS SQL.
Just SQL: Part 5 - Counting with SQL
How many do I have? Do I have at least one? What is the greatest? What is the least? Give me the top 5 rows. These are just some of the types of questions that are often asked when trying to evaluate table data. This article explores how to translate these counting questions into SQL statements.
Point in Time Recovery
Every once in a while, an event might happen that corrupts a database. This article will show you how to recover your database up to the point in time when it was corrupted.
Working with the SQL Server 2000 Maintenance Plan Wizard
The SQL Server Maintenance Plan Wizard offers the easiest way to create a simple plan that protects your data. Steven Warren shows you how, with a walk-through of creating a maintenance plan for SQL Server 2000.
Oracle 10g SQL Enhancements: Part 2
Oracle 10g has extended the already robust features of Structured Query Language (SQL) with a plethora of intriguing new capabilities. This article - the second in this series - reviews the new capabilities Oracle 10g provides that, in concert with analytical functions, permit the creation of "densified" output with a few simple queries.
An Intro to CLR Integration in SQL Server 2005
Learn all you need to know about the CLR integration available in SQL Server 2005. Get a high-level introduction, followed by a discussion of the implications of writing database objects in CLR code and a comparison with existing alternatives such as T-SQL or extended stored procedures.
SQL Server 2005 Integration Services: Part 7
Part 7 of this series continues the overview of various enumerator types within the Foreach Loop Container of SQL Server 2005 Integration Services.
Encrypting Sensitive Data in a Database
Microsoft SQL Server 2000 and earlier versions do not include a way to automatically encrypt the contents of your database tables. This article examines how to use code in the .NET layer to encrypt the sensitive data before writing it to SQL Server and how to decrypt it back to its plain-text form when reading the encrypted content from SQL Server.
SQL Server 2005 XQuery and XML-DML: Part 3
This article is the third and final installment of Alex Homer's series covering the new XML support in Microsoft SQL Server 2005. In it he covers updating the contents of xml columns, comparing traditional XML update techniques with XQuery, and using XQuery in a managed code stored procedure.
Use System Tables to Manage Objects
Learn how to use Access system tables (Msys-tables) to synchronize all database objects between two databases: one with the user's custom objects and the primary client mdb, which continues to be updated and overwritten on a periodic basis.
Just SQL: Part 4 - Joining Tables
As your database grows so will the need to get information from more than one table. This article shows you the different join options and offers some simple examples to raise your familiarity.
Executing the Result Set
MAK illustrates how to use un-documented stored procedures to execute generated SQL Statements directly.
SQL Server 2005 XQuery and XML-DML - Part 2
In the second part of his series on SQL Server 2005's new XML support, Alex Homer looks at extracting data from XML columns, comparing traditional XML data access approaches with XQuery, and combining XQuery and XSL-T.
Database Multi-Sort Classic ASP Sample
This works just like our Database Sort sample except that it adds the ability to sort by multiple fields just by clicking on the field headings. The script will sort on as many fields as you want but it does remove duplicates to keep things logical.
MSSQL Server Reporting Services: SWITCH and Drilldown Defaults
Join Business Intelligence Architect Bill Pearson as he introduces the SWITCH function, along with popular number formatting options, and then extends conditional treatment to default drilldown presentation.
FREETEXT Searches with SQL Server and ADO.NET
Learn how to install, configure, and use the MS Search Engine with SQL Server. Once installed, MS Search permits you to perform fuzzy searches of small and large amounts of character-based data.
SQL Server 2005 Integration Services: Part 6
Marcin Policht continues his coverage of Foreach Loop container functionality. Part 6 of this series discusses different types of loop enumerators.
Tap into Oracle Databases with SQL Server Reporting Services
Connecting SQL Server Reporting Services (SSRS) to data sources other than SQL Server involves some additional subtleties. Learn how to create a dataset that fetches data from an Oracle data source, enabling SSRS to display the data onto a report.
Making the Case for CASE Tools
Many DBAs find themselves working with someone else's database design. Unless you are involved with building or designing a new application or database from scratch, when else would you have the opportunity to use a CASE tool? One of the points to take away from having read this article is that a CASE tool can also be used after the fact. CASE tools are an invaluable resource for a DBA and knowing how to use one (or more) of them can make your job easier.
MySQL Stored Procedures: Part 1
MySQL 5.0 finally introduces functionality for Stored Procedures. Stored procedures have been integral to Oracle, PostgreSQL, DB-2, MS-SQL server and others for years, and it has long been a sore point that MySQL has not had them. If you are a MySQL newbie, or have been using MySQL for years and want to find out what all the fuss is about, read on.
Introduction to MSSQL Server Analysis Services: Create Aging "Buckets" in a Cube
Age transactional data with derived dimensional structures. Business Intelligence Architect and CPA Bill Pearson shows a cube-based approach to creating aging "buckets" for enterprise accounts.
Controlling Transactions and Locks in SQL 2000 and 2005: Part 1
Learn how to control Transactions and Locks with Hints and Snapshots in SQL 2000 and 2005.
Just SQL: Part 3 - Where is it?
We do not always want to SELECT everything from a table. The matter of finding the information required is a function of implementing the optional WHERE clause of the SELECT statement.
MDX Essentials - String Functions: The .Name Function
MSAS Architect Bill Pearson introduces the .Name function as a means of generating lists and fine tuning the presentation of returned datasets. Also examined: the VBA Space() function in creating picklist support for report parameterization within MDX queries.
Maintaining Database Consistency with Transactions in .NET
While databases can efficiently hold and query large amounts of information, all that data is useless if its integrity is questionable. Transactions help ensure that a database's data remains consistent. This article examines how to wrap multiple SQL statements within an atomic database transaction using the SqlTransaction class in the System.Data.SqlClient namespace.
Building N-Tier Web Applications: Part 2
In the second part of his series on building N-tier web applications using ASP.NET 2.0 and SQL Server 2005, Thiru Thangarathinam covers the business logic and user interface layers. In the process, he also examines some new features in ASP.NET 2.0 that greatly simplify the development process.
Index Tuning Wizard
Gregory Larsen explains how to use SQL Server 2000's index tuning wizard to tune the indexes on your database.
Strong SQL Made Even Stronger: Oracle 10g SQL Enhancements - Part 1
Oracle 10g extends many of the already robust features of Structured Query Language (SQL). This article the first of a series illustrates some of the more intriguing new features of SQL within Oracle, including upgrades to the MERGE command, enhancements to hierarchical query capabilities, and improvements to query and access methods for nested tables.
Oracle on the Web Part 5: More on HTML DB
The final part of this series looks at some free resources for HTML DB, include training documentation, blogs and news feeds, a free workspace (hosted by Oracle), and a user forum.
DB2 Universal Database and the .NET Developer: Part 4
Part 4 of this series examines an important yet sometimes overlooked topic application deployment. No matter how great your application is, if you 'cannot deploy it with the same ease that you built it with, then 'you are going to end up with another set of problems to solve.
SQL Server 2005 Integration Services: Part 5
Marcin Policht continues his review of Foreach enumerators starting with Foreach ADO and explains how to retrieve data from flat files processed within the Foreach Loop with File Enumerator.
Just SQL Part 2: The Simple SELECT
At the core of most queries is an underlying table structure. Part II of this series discusses how we can issue the most basic of SELECT statements to extract information from a database table.
Cross-Tab reports in SQL Server 2005
Microsoft introduces new operators PIVOT and UNPIVOT in SQL Server 2005. Traditionally we create queries using the CASE statement and aggregate function in order to produce cross-tab reports. This article illustrates the usage of the new operators, PIVOT and UNPIVOT.
MSSQL Server Reporting Services: Percent of Total - Chart Presentation Nuances
Create a chart that presents independent percent - of - total values: one from the cube level, and one from a calculated value in the report. Use the chart legend to present combined values, as well, to meet a specific business need.
Database Projects in Visual Studio .NET
This article, by Scott Mitchell, looks at how to add a Database project to your existing ASP.NET application and how to import your existing database's objects into the Database project. While creating and setting up a database project does take a bit of time, the small initial investment pays rich dividends.
Introduction to SQL Server Report Builder
SQL Server 2005 is the first version to include end user reporting capabilities. See how the new Report Builder makes it easy for analysts to generate reports while avoiding the performance pitfalls of completely ad hoc reporting.
Where Did Oracle Come From?
Often times, we take for granted the functions an RDBMS performs. But where, exactly, did the concept of the relational model come from? Read on to learn the origins of the RDBMS model in general and where and how Oracle came into being.
Don't let MySQL's oddities catch you by surprise. This article introduces some MySQL oddities, and examines some points on general SQL.
Retrieving the First N Records from a SQL Query
There are often times when retrieving results from a database that you only are interested in displaying the first N records from the resulting query. In this article we'll look at both ROWCOUNT and TOP and see when to use each one. Read on to learn more!
MSSQL Server Analysis Services: Relative Time Periods - Part 2
Discover a more sophisticated approach to the replication of the prefabricated relative time periods found in Cognos PowerPlay Transformer. MSAS and Cognos Architect Bill Pearson continues his examination of ways to replicate Cognos relative time structures in Analysis Services.
SQL Server 2005 Integration Services: Loop Containers
Among features introduced in the SQL Server 2005 Integration Services are the For and ForEach loops, implemented in the form of containers that can be incorporated into the Control Flow part of a package design. Join Marcin Policht to learn about the For and ForEach loops.
Just SQL: Part 1
How many of you have asked yourself what is SQL all about? Join James Koopmann as he ventures down the road of understanding SQL and how to take advantage of the language.
Data Portion and Used Threshold
Learn how to monitor the percentage used on the data portion of every database and alert the DBA using threshold settings.
MDX Essentials: The CoalesceEmpty() Function
Discover the CoalesceEmpty() function as a means of managing nulls for presentation purposes. MSAS Architect Bill Pearson provides hands-on practice in the basics, as well as examining a more sophisticated approach with CoalesceEmpty().
SQL Pivot and Cross Tab
Don Schlichting explores various SQL methods for producing pivot style reports.
Implementing a Simulated FULL OUTER JOIN in Microsoft Access
Gregory Larsen shows how to write SQL code in Access to implement a simulated FULL OUTER JOIN.
Import Security Event Logs Using Log Parser and SQL Server
Learn how to use Microsoft Logparser 2.2, Windows Job Scheduler and SQL Server database to monitor the event logs, and extract Monthly, Weekly Low security event reports from SQL Server database.
DB2 Universal Database and the .NET Developer: Part 3
Part 3 of this series covers a mixed bag of features that make .NET developers more productive when programming to a DB2 UDB database.
SQL Server 2005 - SQL Server Integration Services: Part 4
Part 4 of this series focuses on variables and looks at using expressions - both in the context of variables and component properties - which further expand the dynamic nature of SQL Server Integration Services.
Working with the SQL Profiler
Steven Warren discusses how to use the SQL Profiler to monitor, analyze, and tune SQL server.
Oracle 10g PL/SQL Enhancements, Part 2: Utility Players
Oracle 10g has added over fifty new PL/SQL packages and enhanced many of the existing packages, thus expanding the Oracle DBA's toolkit once again. New capabilities include the ability to transfer files between servers, more robust e-mail features, improved compilation utilities, and character conversion utilities. This article - the final one in this series - delves into several new features presented in new and improved versions of Oracle's utility modules and illustrates their usefulness in some real-world situations.
IBM DB2 Universal Database and the .NET Developer: Part 2
Part 1 of this series covered the features of the IBM Explorer that help .NET application developers write applications that run against the DB2 UDB family on any platform. Part 2 discusses how easy it is to create database objects for DB2 UDB databases with the assistance of IBM Explorer and a few wizards.
Dynamic Default Date Parameters in SQL Server 2000 Reporting Services
Many business intelligence reports call for date-driven parameters. These parameters are often based on the current date. Learn how to develop a DataSet of commonly used dates and use this DataSet to populate the default parameters in a SQL Server 2000 Reporting Services report.
Oracle on the Web: Part 4 - Upgrading HTML DB
Hidden away in the depths of your Oracle installation is a valuable resource you can use to get a jump-start on learning how to combine Oracle's strength as a relational database management system and the power of the Internet. Part 4 of this series covers the steps of upgrading to version 1.6 and creating a simple Web service.
MSSQL Server Reporting Services: Mastering OLAP Reporting
Discover two approaches to the generation of percent - of - total values in Reporting Services." In this article, BI Architect Bill Pearson provides options from both cube and report layers within an integrated BI solution, contrasting and comparing approaches, and introducing "which layer" considerations.
A View of Creating an Oracle User: Part 2
After an Oracle user has been created and schema objects have been built, best practices dictate that you should not run your applications as the schema owner. Here is a shell procedure and methodology to assist you in maintaining this segregation of application user from database schema owner.
An Introduction to MySQL Permissions
The new storage engines, whilst tricky for most people to use, can be useful for those with special needs and they are bound to be included in the binaries at some point.
MSSQL Server Analysis Services: Relative Time Periods
Replicate the prefabricated relative time periods found in Cognos PowerPlay Transformer and other OLAP Design and Reporting tools. In this article, MSAS and Cognos Architect Bill Pearson shows how to provide relative time reporting capabilities, such as year-to-date, from an Analysis Services cube.
SQL Server 2005 - SQL Server Integration Services: Part 3
This third article of the SQL Server 2005 Integration Services begins a discussion of SSIS features, which simplify package maintenance and increase their flexibility.
Data Paging Using SQL Server and the Enterprise Library
Using the DataGrid's built in paging mechanism is a convenient and easy way to implement paging, but as your database grows you may notice a considerable delay while paging. The problem is that the DataGrid doesn't just get the records for the current page... it actually retrieves them all! This acticle shows you a more efficient way to handle paging using some of the new features in SQL Server 2005 and Microsoft's Enterprise Library.
Oracle on the Web: Part 3
In the first two parts of this series, we looked at the basic HTTP Server behind HTML DB (and Application Server) and the basic installation and setup of HTML DB itself. In this article, we will look at a simple example of adding a page to an existing application and detail some of the steps involved.
IBM DB2 Universal Database and the .NET Developer: Part 1
Paul C. Zikopoulos examines some of the tooling productivity benefits that can be leveraged when programming .NET applications in the Microsoft Visual Studio.NET integrated development environment.
MDX Essentials: The TopCount() Function - Part II
Join MSAS Architect Bill Pearson in an extended examination of the TopCount() function. Discover, through hands-on practice exercises, more sophisticated business uses for TopCount(), in combination with the Generate(), .Item and other functions we have explored within our series.
SQL Server 2005 System Tables and Views
Don Schllichting explores various options for obtaining SQL 2005 metadata information.
A View of Creating an Oracle User: Part 1
Before any schema objects can be created, you must first create a user that will own these objects. This somewhat simple procedure is often overlooked and it can open wide holes in security and portability.
Don't Use Select * in Database Queries
While there's technically nothing wrong with using "Select *" within a query, using that syntax could be stealing away precious performance from your application, and even it it's not now, it might someday soon.
Enumerate SQL Server Names
DBAs do not always have a list of production and/or development SQL Servers on hand, but servers that are registered in Enterprise manager are stored in the registry of the client machine. This article explains how to take advantage of Windows Scripting Host and VBScript to read the registry in order to enumerate all of the SQL servers that are registered in Enterprise Manager.
Transferring Data from One Table to Another
Gregory Larsen examines a number of methods to transfer data from one table to another.
SQL Server Performance Monitor
When you install Microsoft SQL Server, Performance monitor objects and counters are automatically installed. While you must have administrative access to your SQL Server to use these objects, SQL Server admins should find them invaluable in monitoring and tuning the database server.
Oracle 10g PL/SQL Enhancements: Part 1
With the addition of over fifty new PL/SQL packages as well as enhancements to a majority of existing DBMS packages, Oracle 10g has added a plethora of new tools to every Oracle DBA's tool belt. This article - the first is a series -- explores some of the more intriguing packages and illustrates their usefulness in real-world situations.
Oracle on the Web: Part 2
Continuing our series on Oracle 10g's HTTP Server and HTML DB, part two looks at how the HTTP server has changed with respect to how you get it and how you use it. Part of the process is very easy, and other parts are somewhat confusing. If you were hoping for a clean, 'one CD type of install and there it is' situation, the result is a mixed bag.
SQL Server 2005 Integration Services: Part 2
The first article of this series presented the basic concepts relevant to understanding SQL Server 2005 Integration Services providing a quick overview of tools to manage such activities as DTS package design, development, and storage, as well as interactive and scheduled execution. In order to gain better familiarity with each of these activities, this article examines them from a more practical perspective, using a number of fairly straightforward examples.
Executing SQL Statements in VBA Code
There are a number of ways to execute a SQL Data Manipulation Language (DML) statement from Microsoft Access, besides the obvious process of creating an Action Query and double-clicking its icon. This article demonstrates how to execute SQL using DoCmd.RunSQL, DoCmd.OpenQuery, [Querydef].Execute, [Database].Execute and dbFailOnError.
The Globalization of Language in Oracle - Index Requirements
James Koopmann takes a look at indexing requirements.
Monitor SQL Server CPU Loads
MAK illustrates how to monitor the CPU load on multiple SQL Server machines and how to collect data in Round Robin database to produce a graph.
Mastering OLAP Reporting: Ad Hoc TopCount and BottomCount Parameters
Parameterize a pair of MDX functions, and more, from a simple OLAP report. MSAS Architect Bill Pearson provides hands-on practice in providing ad hoc TopCount() and BottomCount() functions, as well as demonstrating the support of parameterization from both report and cube layers within an integrated BI solution.
SQL Server 2005 Integration Services: Part 1
The DTS equivelant in SQL Server 2005 has been rearchitectured, improved and renamed to SQL Server Integration Services (SSIS). Join Marcin Policht for an overview of this feature with a new name.
Obtain Information about IBM DB2 Universal Database the Easy Way
DB2 UDB V8.2.2 adds three new user-defined functions (UDFs) that allow authorized remote clients to get details about a DB2 UDB server, including machine, product, and instance information. Paul Zikopoulos takes you through the details of these new system information UDFs and gives examples of how to use them.
Using LogMiner: Part 2 - Striking Out
Steve Callan investigates a scenario in which LogMiner may or may not be able to recover data lost through user error.
A Database Journal Guide to MySQL Certification: Part 2
Ian Gilfillan provides an outline of the Pro certification, and a list of resources you will find invaluable in preparing for the MySQL Professional certification.
MSSQL Server: Process Analysis Services Cubes with DTS
Process a cube, as well as other Analysis Services database components, with a custom DTS task that accompanies an Analysis Services installation. MSAS Architect Bill Pearson leads hands-on exploration of the DTS Analysis Services Processing task.
Common Table Expressions (CTE) on SQL 2005
Don Schlichting examines Common Table Expressions (CTE) on SQL 2005, and compares them with related SQL 2000 methods such as Derived and Temporary Tables.
Understanding SQL Server Licensing
There's one step in deploying an application that uses SQL Server that sometimes gets neglected until the last moment: buying the licenses! Review the confusing array of editions and licensing options now, before you have to write that multi-thousand dollar check.
Oracle and Regular Expressions
UNIX comes to Oracle in the form of regular expressions to increase the power of searching.
SQL Server: Customized Calendar Tables
MAK explains how to create customized calendar tables, populate data and how to apply a custom calendar in SQL Server jobs by using the user-defined function udf_isProcessDate.
Uniform Page Size in IBM DB2 Universal Database
Paul C. Zikopoulos examines uniform page sizes, a new capability in DB2 V. 8.2.2 that allows DBAs to use a single page size for their entire database.
Explore SQL Server 2000 DTS Packages Using .NET and the DTS Class Library
Before embarking on a major database change such as upgrading to SQL Server 2005, how do you assess the scope of the change on your DTS packages? Use the DTS class library and .NET Interop.
MDX Essentials: The TopCount() Function, Part 1
Isolate best performers from the member population at large, and perform sophisticated analysis with TopCount(). Join MSAS Architect Bill Pearson in the first of a pair of articles focusing upon the powerful TopCount() function, where we experience "hands-on" some of the possible ways it can offer multi-perspective decision support.
Generate Script for Objects and Dependent Objects
DBAs often deploy object changes on production systems, requiring them to check for dependancies to ensure they don't break the system. Mak illustrates how to take advantage of SQL DMO, T-SQL and VBScript to locate dependencies and generate a script for those objects and their dependent objects.
Monitor the CPU Usage of Your SQL Servers
Learn how to take advantage of WMI and VBScript to monitor CPU Utilization of SQL Servers and to find heavy and under utilized servers.
Oracle 10g DataPump, Part 2: Implementation
Oracle 10g's new DataPump is designed as the eventual replacement for the Oracle Import and Export utilities. This article - the second and final in this series - focuses on using the DataPump's features in real-world scenarios, including creation of custom DataPump jobs with DBMS_DATAPUMP.
Identifying Long Running SQL Server Agent Jobs
Greg Larsen discusses how to use the 'xp_sqlagent_enum_jobs' XP to help identify long running jobs.
SQL Server 2005: Setup and Deployment
Marcin Policht examines improvements in the design and implementation of SQL Server 2005.
The Globalization of Language in Oracle and Case-Insensitivity
Learn how to take advantage of globalization techniques to perform case insensitivity in Oracle.
MSSQL Server Reporting Services: Ad Hoc Sorting with Parameters
Parameterize sorts within a tabular report. MSAS Architect Bill Pearson provides hands-on practice in the parameterization of sorting at run time in Reporting Services.
SQL Server: Checking File Status Prior to Processing
MAK demonstrates how to use the user defined function, udf_CheckFileStatus, to avoid waiting for a file to be copied and how to run a process more efficiently.
How to Pass Access Data Across the Web
Danny Lesandrini examines how to pass a little bit of data from an internal Access application behind a firewall out to a public Web site in real time.
Managing the "Surface Area" of SQL Server 2005
Learn about the new security feature Microsoft is focusing on with SQL Server 2005.
MSSQL Server Analysis Services: Presentation Nuances
Present a crosstab display where both axes contain the same dimension. MSAS Architect Bill Pearson leads a hands-on introduction to using this approach to meet a relatively common presentation need.
SQL Server 2005 - Management Utilities
The second article of this short series covering new and improved utilities of SQL Server 2005 Beta 2 reviews the graphical and command line programs not covered in part 1, which should be part of the toolkit of every SQL Server 2005 database administrator and programmer.
Formatted E-mails from SQL Server
Learn how to use CDOSys to send formatted emails from Query Analyzer and SQL Server Alerts.
Oracle - The NLS_LANG variable
James Koopmann zeros in on the most important variable for implementing a global database in Oracle and some lessons learned.
Using the Activity Monitor to Actively Monitor Application Locks
One of the most frustrating tasks that database administrators (DBAs) have to perform is trying to figure out when one database application's request is blocking another. This article introduces DB2 UDB Version 8.2's new Activity Monitor, a graphical view of locks and lock chains.
What's New in ADO.NET 2.0 for SQL Developers
ADO.NET 2.0 introduces enhancements to the .NET provider model and SQL Server managed provider, which go a long way towards getting better support for specific database features within the boundaries of a common object model.
MDX Essentials: Enhancing CROSSJOIN() with Calculated Members
Join MSAS Architect Bill Pearson in an extension to the previous examination of CROSSJOIN() enhancement. Discover, through a multi-step practice exercise, why NONEMPTYCROSSJOIN() proves ineffective when calculated members enter the picture, and how we can enhance performance through alternative avenues.
Microsoft SQL Server Express 2005 (MSDE)
SQL Express 2005 is a new, FREE, Microsoft SQL Server lightweight edition. This new edition is the upgrade to Microsoft's SQL Server 2000 Desktop Edition (MSDE). Targeted to developers of "simple" applications, it includes a fully functional SQL Server database engine and a Query Analyzer type management tool. And best of all, it is FREE to use and redistribute.
Incorporating SQL Server Reporting Services into your Web Site
Detecting The State of a SQL Server Agent Job
How do you determine if a SQL Server Agent Job is running, is there a way to automate the process, is there an easy way to determine all of the SQL Server Agent jobs that are currently running? Greg Larsen answers these questions, showing different methods that determine the state of an individual job, or all SQL Server Agent jobs.
The SQL Server Best Practices Analyzer
In the past, you had to tweak your SQL Server settings manually to find the ideal configuration. Now, Microsoft has released a tool called the Microsoft SQL Server Best Practices Analyzer. In this article, Steve Warren explains how to install Best Practices Analyzer and shows you how it works.
Automatic SQL Tuning using SQL Tuning Advisor
Oracle Database 10g introduces many useful and easy-to-use tuning tools and methodologies. In this article, we will examine one of these new and improved features - SQL Tuning Advisor.
Rapid J2EE Development with Oracle ADF
Learn about Oracle's Application Developer Framework (ADF) and how it integrates with J2EE. Explore the basic concepts and terminology behind this framework, and then see how ADF can help Oracle developers build quality J2EE applications in a short amount of time.
The IBM DB2 Universal DatabaseTM Spatial Extender
In previous releases, the DB2 UDB Spatial Extender was a chargeable add-on product, but now, with every server edition of DB2 UDB, you can leverage this capability for free. In this article, Paul Zikopoulos gives you some details about spatial analysis, its history, and its role in DB2 UDB.
SQL Server 2005 Part 1 - Tools
In this article, Marcin Policht shifts his attention from SQL Server 2005 authorization, encryption, and digital signatures to interface related modifications, starting with a set of tools that provide SQL server management capabilities.
Oracle 10g DataPump: Part 1- Overview
Oracle 10g's new DataPump utility is designed as the eventual replacement for the original Oracle Import and Export utilities. This article - the first in a series - provides an overview of the DataPump's suite of tools for extracting, transforming, and loading data within an Oracle database.
Building an End User Defined Data Model: Part 1
In the first article in this series, Peter Scheffler introduces the concept of a rules-based database engine that allows clients to make changes to their database structure without breaking the applications that access the database.
Doing Data Guard: Part 2
Part two of this series picks up with step-by-step coverage of preparing your environment for Data Guard.
MSSQL Server Reporting Services: Report Execution Timeout
Control demands upon critical enterprise resources by Reporting Services with Report Execution Timeouts.MSAS Architect Bill Pearson introduces one of several means for "governing" the system demands of information consumers, developers and other Reporting Services users.
An Introduction to Microsoft SQL Server 2000 Reporting Services
Microsoft recently released their own reporting solution software, Reporting Services for SQL Server 2000. This article provides a simple walk-through on creating and displaying a report in an ASP.NET Web page using Reporting Services.
Convert Access Tables Into ASP Web Pages
This little Access to ASP code generator will expose a list of tables from which you can select one to convert to ASP, create an ASP page based on that table's columns, open the ASP page in Notepad and supply a hyperlink to the newly created web page.
The Globalization of Language in Oracle: NLS_COMP and NLS_SORT Variables
How does Oracle compare and sort data. There is more to it than meets the eye.
Using Regular Expressions Groups to Isolate Sub-Matches
Learn how to extract sub-matches from your regular expression matches with the .NET Group and GroupCollection classes.
SQL Profiler Tips and Tricks
SQL Profiler is a vital tool for diagnosing SQL Server issues of all types. When you're trying to figure out what the heck is going on, especially with a heavily-loaded server, a well-chosen trace can help you pick out just the key events that you need to diagnose a problem.
All About the 'Case'
Though case insensitiveness makes the life of the database developers and database administrator's easy, there are situations where case sensitivity should be enforced just as password checking is enforced. This article discusses the various methods of comparing two words that are the same, with the exception that they are different cases, i.e. lower case, upper case or mixed cases.
MSSQL Server 2000 Analysis Services: Point-and-Click Cube Schema Simplification
Simplify an Analysis Services cube schema with point-and-click simplicity, eliminating joins between dimension and fact tables. MSAS Architect Bill Pearson leads a hands-on introduction to using this option to significantly reduce cube processing time.
Oracle: Collecting Real Time Wait Events
There are two common ways of collecting wait event information: the Oracle extended trace (10046 event) and using V$ views. Learn how to collect wait event information using V$ views.
Oracle and Pro*C: Part 1
Pro*C is another one of those tools or features from Oracle that keeps a low profile, quietly waiting its turn for you to use it when the need arises. Learn where to get Pro*C, and how to configure the environment.
Open Source Databases
Ian Gilfillan examines variety of open source database solutions.
MDX Essentials - Set and String Functions: The GENERATE() Function
Apply a set to each member of another set, and join the resulting sets; or return a concatenated string to nest with a set to meet reporting needs. MSAS Architect Bill Pearson explores the set and string versions of the powerful GENERATE() function, and leads practice in its operation.
The Globalization of Language in Oracle - National Language Support
The previous article of this series looked at external terminology around globalization. This article covers the internal parameters with which we will need to become familiar.
Checking Rebooted SQL Server Status
Learn how to take advantage of VB-scripting, the OSQL command line utility and an MS-DOS batch file to consolidate and check the status of all of the rebooted SQL Servers on the network.
Using a Correlated Subquery in a T-SQL Statement
The previous article in this series discussed what and how to use a subquery in a T-SQL statement. This month Greg Larsen explains what a correlated subquery is, and shows a number of different examples on how to use a correlated subquery in a T-SQL statement.
The SQL Server Web Data Administrator
In the age of 24 x 7 Web sites with no downtime, a DBA often needs a way to perform tasks quickly when off-site. The SQL Server Web Data Administrator is very powerful and gives you the ability to do a multitude of SQL Server tasks remotely.
Oracle 10g Availability Enhancements: Part 4 - LogMiner and Data Guard
Oracle 10g offers significant enhancements that help insure the high availability of any Oracle database, as well as improvements in the database disaster recovery arena. This article - the final in a series - focuses on new functionalities provided by the Data Guard and LogMiner tool suites.
Connecting with Oracle - Ensuring Sufficient Privileges
Be honest now, how many times have you encountered the ORA-01031 error? Without a deeper understanding of the "how do I authenticate thee, let me count the ways?" relationship between the operating system and Oracle, you are doomed to encounter this error time and time again.
SQL Server 2005 Security: Part 3 - Encryption
While some encryption functionality existed in the previous versions of SQL Server, they were relatively limited and rarely used. SQL Server 2005 provides significant improvements in this area. Part 3 of this series focuses on the freshly introduced native database encryption capabilities.
MSSQL Server 2000 Reporting Services: Execution Log Performance
MSAS Architect Bill Pearson concludes his examination of Execution Log reporting. In this article, we practice the rapid deployment of the core report set and create a sample custom report, to become familiar with basic performance and auditing reporting in Reporting Services.
The Globalization of Language in Oracle: Terminology
With today's exploding world economy, multi-national communication is essential. Databases must not only store different character sets but also present information in a comfortable format and order for individuals from every locale. This series explores how to globalize your databases and communicate effectively across the globe.
Replicating Identity Columns in SQL Server
This article discusses customizing replication to make the subscription and primary databases identical in order to failover in case of a primary server failure.
Collecting Oracle Extended Trace
The most importing thing in collecting trace data is collecting properly scoped data. Learn the pitfalls of doing it wrong and the rewards of doing it right.
MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube
MSAS Architect Bill Pearson delves deeper into DISTINCT COUNT concepts, then leads practice in a "best practice" approach for improving the performance of DISTINCT COUNTS in cubes.
A Step-by-Step Guide To Using MySQL with ASP.NET: Part 1
Back in the days of classic ASP, if you were building a database-driven Web site, your choice was either to invest a lot of money to get a copy of Microsoft SQL Server (or some other enterprise-ready database) or invest a lot of time finding a way to deal with the performance and scalability limitations of Microsoft Access. Luckily these days there's another viable alternative: MySQL.
SQL Server 2005 Security: Part 2 - Authorization
Part 2 of this series shifts from enhanced authentication-related functionality in SQL Server 2005 Beta2 to authorization features. Among topics that are covered are separation of user and schema, modifiable context of module execution, increased permission granularity, and improved catalog security.
Introduction to SQL Profiler
Looking for a troubleshooting tool to view the actual SQL that's being sent during code executions? SQL Profiler is a great tool for peering under the hood of your SQL Server installation. Learn how to use it and you're sure to save debugging time in the future.
Using Oracle's Data Guard: Part 1
Oracle Data Guard is a very useful tool to help maintain high availability and to protect your data. It is not uncommon to see 'must have experience with RAC and Data Guard' in job postings. The purpose of this series is to give you a little push to get up and running with Data Guard.
SQL Clauses: HAVING and GROUP BY
This month's article looks at the GROUP BY clause, and then the difference between conditions placed in the WHERE clause and the HAVING clause.
MDX Essentials: The CROSSJOIN() Function
Explore CROSSJOIN() considerations that arise when dealing with medium-to-large datasets. MSAS Architect Bill Pearson leads a practice session that explores ways to speed processing in queries where CROSSJOIN() tends to present bottlenecks.
Full Text Search on SQL 2000: Part 3
By default, a Full Text Search Catalog is not kept in sync with its underlying table source. This month's article explores the options available for Catalog synchronization, also known as populating.
Oracle Session Tracing: Part 6
Part six of this series discribes how to access and generate reports off of the trace files we generated in Part five, through the use of the TKPROF and trcsess utilities.
Rebuilding SQL Server Cluster Nodes
Active/Passive SQL Server 2000 clustering gives more reliability and fault tolerance to Production SQL Server environments. When a failure occurs, all of the resources fail over from the active node to the passive node and make the passive node active. This article explains how to rebuild the node that failed and attach it back to the cluster.
SQL Server 2005: Interface Overview
The second article of this series provides an overview of SQL Server 2005's new interface.
Oracle 10g: Part 3 - Flashback Enhancements
Oracle 10g offers significant enhancements that help insure the high availability of any Oracle database, as well as improvements in the database disaster recovery arena. This article - part three of a series - concentrates on the expanded capabilities of the logical Flashback command set.
Steve Callan introduces you to what the benchmark tests are, who controls or regulates them, and shows how Oracle compares to other database systems.
Connect to Lotus Domino using SQL Server Linked Server
Learn how to use SQL Server Linked Server to connect to databases that reside on the Lotus Domino server.
SQL Server 2005: Part 1 - Security (Authentication)
This installment of our series focuses on security, starting with features related to authentication. In particular, we will cover password policy implementation and management as well as endpoint-based authentication (in the context of native support for HTTTP SOAP requests), with subsequent articles covering authorization and encryption.
Oracle Session Tracing: Part 5
Part five of this series will re-acquaint you with Oracle's tracing mechanism. Read on to learn about creating trace files for TKPROF.
Finding the Port Numbers Used by SQL Server 2000
When managing many different SQL Server boxes, finding the port numbers used by all of the SQL Servers can be a tedious job. In this article, MAK discusses several methods for finding the Port numbers used by SQL Server.
MySQL Query Browser
MySQL Query Browser is a tool that allows you to execute queries and develop SQL scripts.
MSSQL Server 2000 Reporting Services: Prepare the Execution Log for Reporting
Use the Reporting Services Execution Log as a source for performance and access reporting. In this article, MSAS Architect Bill Pearson leads practice in establishing the log as a data source for administrative reporting.
SQL Server 2005, Part 5: High Availability and Scalability Enhancements
This final installment of High Availability and Scalability Enhancements in SQL Server 2005 Beta 2, examines features not previously covered, such as a new method of table and index partitioning, backup and restore improvements, and new hardware support options.
Setting up a reboot cycle for Active/Passive Cluster SQL Server
MAK explains how to reboot cluster nodes and switch the active and passive modes on a weekly basis.
Describing Oracle Syntax
Steve Callan offers a brief history on Oracle's syntax.
Introduction to MSSQL Server 2000 Analysis Services: Distinct Count Basics
MSAS Architect Bill Pearson explores DISTINCT COUNT concepts, then leads practice in putting these concepts to work from the tandem perspectives of Analysis Manager and MDX.
Full Text Search on SQL 2000: Part 2
Full Text Searching goes beyond simple LIKE string searching to provide new query possibilities. Picking up where we left off last month, this month's installment covers CONTAINS, FORMSOF and NOISE WORDS.
Oracle Session Tracing: Part 4
Part 3 of this series examined how to enable tracing of statistics through the DBMS_MONITOR package. This installment focuses on determining which internal Oracle views hold the information to our enabled statistical gathering.
Transaction Log Backups Based on Log Usage Threshold
Automatic file growth in SQL Server data and log files made the SQL Server Database Administrator's life easier. However, it introduced a new crisis: running out of hard disk space. Learn how to avoid this crisis by scheduling automatic transaction log backups based on a pre-defined log file threshold.
MDX Essentials: More on the IIF() Function
Explore the further use of the IIF() function in meeting consumer business requirements. MSAS Architect Bill Pearson leads a practice example where we use IIF() in combination with a member property and conversion function, in producing a DataSet that we then filter, order and format.
Oracle 10g Availability Enhancements: Part 2 - Flashback Database
Oracle 10g offers significant enhancements that help insure the high availability of any Oracle database, as well as improvements in the database disaster recovery arena. This article - part two of a series - explores one of the most intriguing new features of Oracle 10g: Flashback Backup and Recovery.
Oracle Migration Workbench: Part 5
Part five of this series looks at issues related to the SQL Server to Oracle migration process.
Ian Gillfillan presents a quick tour of MySQL Administrator, offers suggestions on its use and provides additional resources to help optimize your database.
MSSQL Server 2000 Reporting Services: Ad Hoc Conditional Formatting for OLAP Reports
Parameterize conditional formatting within an OLAP report. MSAS Architect Bill Pearson provides hands-on practice in parameterizing conditional formatting at run time in Reporting Services.
MS Access: Fascinating Query Tricks
Those who are new to Access and especially those new to writing SQL in specific, will be fascinated at how easy it can be to produce the results you need, with very simple SQL and some not so difficult VBA code.
Oracle Session Tracing: Part 3
Explore how to enable and disable Oracle tracing with the DBMS_MONITOR package in 10g.
Upload Multiple Files to SQL Server Image Column
There are a number of ways to upload multiple files to a SQL Server table. MAK introduces a method that takes advantage of the OSQL.exe utility and the TEXTCOPY.exe utility.
Intro. to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances
MSAS Architect Bill Pearson returns with an introduction to semi-additive measures, in a hands-on approach to meeting a common business requirement.
SQL Server 2005: Part 3 - Failover Clustering
In the previous article of this series, we started reviewing high availability and scalability enhancements in SQL Server 2005 (based on its recent Beta 2 release) by discussing database mirroring. In this article, we will continue exploring the same area of functionality, shifting our attention to failover clustering.
DB2 Tracking Database Changes: Part 2
Part one of this series explained the DB2 Audit utility, and how to use it for tracking and documenting database schema modifications. This second installment demonstrates a working example of creating a schema modification log.
Oracle Migration Workbench: Part 4
Part four of this series goes into more detail about SQL Server and how it compares to Oracle. This part of the series also covers the setup and preparation to use Oracle Migration Workbench to migrate the Northwind database from SQL Server to Oracle.
MS SQL - MDX Essentials: Introducing the IIF() Function
Return one of two values based upon the result of a logical test. MSAS Architect Bill Pearson introduces the IIF() function, and explores the options it offers.
Making a Connection from Oracle to SQL Server
SQL Server is gaining popularity and there are many shops that I know of that have this database system within their walls. Learn how to make a connection from Oracle to SQL Server using Oracles heterogeneous Services ODBC agent.
Full Text Search on MS SQL 2000: Part 1
Full Text Searching is a free, optional component of MS SQL 2000. When installed, it offers a vast array of additional string querying abilities. This article will demonstrate installing, configuring and using the Full Text Search engine.
Persisting .NET Objects to SQL Server Using SQLXML and Serialization
As a follow up to his article on retrieving objects from SQL Server using SQLXML and serialization, Gianluca Nuzzo discusses saving objects back to SQL Server using a schema definition file and updategrams.
Checking Latency on SQL Server 2000
Latency in a replication environment is the time it takes for a transaction to be transmitted from a publisher to the distributor and from the distributor to a subscriber. Learn how to find and monitor the latency frequency in a transactional replication environment.
Oracle Session Tracing: Part 2
Part two of this series shows how to set two more very important session environment variables to make tracing more effective. Read on and learn how to set the module and action names.
SQL Server Undocumented Stored Procedures
Microsoft provides two undocumented Stored Procedures that allow you to process through all tables in a database, or all databases in a SQL Server instance. This article will show you how the undocumented Stored Procedures work, and will provide you with some examples on how to use them.
ADO.NET Generates SQL for You Via the SqlCommandBuilder
ADO.NET's SQL command builder reads a schema and generates SQL for you; this can be an excellent timesaver in the appropriate circumstances. When applied with sound judgment, this technique is a nice shortcut.
SQL Server 2005 Part 2: Database Mirroring
Part 2 of this series begins with an examination of the high availability and scalability enhancements of SQL Server 2005, such as database mirroring (introduced in this version) and failover clustering, which is improved in SQL Server 2005, compared to earlier editions.
Oracle 10g Availability Enhancements: Part 1 - Backup and Recovery
Oracle 10g offers significant enhancements that help insure the high availability of any Oracle database, especially in the arena of disaster recovery. This article - the first in a series - concentrates on several new features available for backup, restoration, and recovery of Oracle databases, especially when using Oracle Recovery Manager (RMAN).
SELECT DISTINCT: A SQL Case Study
There is an understanding in the database world that using a 'SELECT DISTINCT' SQL query is not a good idea, because it is essentially getting duplicate rows out of the database and then discarding them. Usually it's better to rearrange the 'WHERE' clause in the query to only get the rows you need. Tom Copeland gives a step-by-step 'case study' of analyzing a query in PostgreSQL and how to ensure that your SQL rewrite is actually paying off.
Oracle Migration Workbench: Part Three
The end of Part Two of this series finished with loading data from the MySQL database into the Oracle database/repository. Part Three will go into more detail about the Migration Workbench console and learn how to configure some options.
Make Access Command Buttons Work Harder
Danny Lesandrini reaches into his bag of tricks and pulls out a Popup Magic Button. Follow along as he explains how to group similar functions on a single command button to open a popup menu, which includes all the functionality you require.
Oracle Session Tracing, Part 1
This is the first in a series introducing some of the new tracing concepts and options within Oracle. This installment focuses on the new CLIENT_IDENTIFIER environment variable that can be assigned to sessions.
MSSQL Server 2000 Reporting Services: Manage Nulls in OLAP Reports
MSAS Architect Bill Pearson provides hands-on guidance in managing nulls at the report level, when filtering for non-string values from an OLAP data source.
SQL Server: Compare Query Results - Part 2
Part I of this article discussed how to compare the query results executed on two different servers. In part II, we are going to discuss how to compare results of the same query on the same or different server but on different databases by taking advantage of an MS-DOS batch file and SQL Server utility, BCP.exe
Until now, MySQL has not supported subqueries, and this lack caused many to write off MySQL as not being a serious DBMS. While the lack was certainly a problem, many developers do not know that subqueries can often be rewritten as a join, sometimes giving a performance benefit in the process. Ian Gilfillan looks at subqueries, and how they can be rewritten in a more optimal way.
Oracle Migration Workbench: Part 2
At the end of the first installment, we were at the point where MySQL was ready to be installed. In part two of this series, we will go through some quick setup steps, create a user with some privileges, and load data - using several methods - into a MySQL database. Once the setup of the MySQL database is complete, we will be ready to start using Migration Workbench.
MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates
Join Bill Pearson in an introduction to incremental updates in MSAS, where we divide a logical cube into separate "containers" to provide storage flexibility and enhanced query performance.
Cursors with SQL 2000, Part 2
Part one of this series discussed cursor use and basic syntax. A select cursor was created demonstrating the keywords DECLARE, OPEN, FETCH, @@FETCH_STATUS, and DEALLOCATE. The sample cursor demonstrated that SQL could act on one record at a time, as opposed to its usual method of acting on sets of data. In this article, the sample cursor will be expanded upon to include data modification and record positioning.
Clustering for Indexes
Indexes hold the key for speed when accessing data. To ensure that you are getting to the data as fast as possible you should check to make sure the clustering is suitable for the query type.
SQL Server: Compare Query Results - Part I
In the Database administration world, it is often necessary to run a query on the production box and then run the same query on the QA or UAT box and compare the results. In this article, MAK introduces a method that takes advantage of an MS-DOS batch file and SQL Server utility, BCP.exe, to compare the query results executed on two different servers.
MDX Essentials: The IsEmpty() Logical Function
Manage empty cells in returned datasets with the ISEMPTY() function. In this article, author Bill Pearson combines ISEMPTY() with IIF(), to provide a conditional solution for the elimination of divide-by-zero error in MDX.
Submitting A Stored Procedure Asynchronously
Greg Larsen discusses why you might want to run an SP asynchronously and how to submit an SP to run asynchronously.
The Oracle 10g Scheduler, Part 3: Advanced Features
Oracle 10g's new DBMS_SCHEDULER package offers significant advanced features over its predecessor, DBMS_JOB, that any reasonably skilled Oracle DBA can use to insure that sufficient resources will always be available for processing complex business requirements. This article - the final in a three-part series - provides some practical examples of how the new Scheduler can help a DBA to manage and overcome these challenges.
The System Table, "sysperfinfo" in SQL Server
The system table, 'sysperfinfo,' is the representation of the internal performance counters of SQL Server. Learn how to retrieve and store information from 'sysperfinfo' to a CSV file, so that it can be analyzed.
Oracle Migration Workbench, Part 1
By the end of this series, not only will you be familiar with two other widely used and popular database systems, but you will also know how to migrate data from these systems into an Oracle database.
Monitor Blocking in SQL Server 2000, Part 2
Part one of this series discussed how to obtain detailed information on blocking. Part two picks up with collecting detailed information when the blocking duration is longer than a minute.
SQL Server 2000 Security, Part 14: Pre- and Post- Installation Recommendations
It is time to gather the remaining pieces of information dealing to the SQL Server 2000 Security, concluding at the same time our series dedicated to this subject.
Automate with SQL Mail
Learn how you can create triggers in your SQL code to notify administrators, including yourself, via e-mail if certain conditions occur.
Sending E-Mail From Within Oracle
Sending e-mail from within Oracle can be of great benefit. This article gives you a quick start at sending simple e-mails.
Monitor Blocking in SQL Server 2000 - Part 1
Part of Database Administrator's job is to monitor blocking in SQL Server. Learn how to monitor blocking and capture the SQL statements that are involved to a file.
Business Intelligence with Microsoft SQL Server Reporting Services - Part 1
Adnan Masood discusses Microsoft's comprehensive integrated business intelligence, data mining, analysis and reporting solution: Microsoft SQL Server Analysis services and Microsoft SQL Server Reporting services.
Everyone destroys something every now and again, a timely reminder that we are human and perhaps an untimely reminder of the importance of backups. Ian Gilfillan examines the two main methods for backing up MySQL databases - an SQL dump and a raw file copy.
MSSQL Server 2000 Reporting Services: Create a Reusable Template Report
Create a template in Reporting Services to shorten the development cycle, ensure consistent look and feel, and provide other benefits to report authors and information consumers. MSAS Architect Bill Pearson leads a hands-on exercise in creating and using templates to generate numerous advantages within a Reporting Services implementation.
Book Review: Hitchhiker's Guide to SQL Server 2000 Reporting Services
Danny Lesandrini gives us a sneek peek at the up-coming Hitchhiker's Guide to SQL Server 2000 Reporting Services.
Creating Triggers Using Managed Code in SQL Server 2005
Thiru Thangarathinam discusses taking advantage of the integation between the .NET CLR and SQL Server 2005 in order to do things like create triggers using managed code.
Oracle Response Files, Part 2
Part One of Oracle Response Files covered the basics of using a response file and provided an example of how to install Developer Suite on a Windows PC. In Part Two, we will go into more detail about the components of response files. This part will also discuss some related topics as they pertain to the Oracle Universal Installer and some Windows-analogous installation concepts.
MSSQL Server 2000 Analysis Services: Partitioning a Cube
Divide a logical cube into separate "storage "containers" for data storage flexibility and optimal query performance. Bill Pearson introduces the concepts with a hands-on practice session.
SQL Server 2000 Security, Part 13: SQL Injection attack
Marcin Policht discusses one of the most common application-based SQL Server attacks, known as SQL Injection, and explains how it can be prevented.
Oracle 10g's Undo Advisor
Oracle 10g simplifies the creation of undo records with Undo Advisor.
Restore Multiple DTS Packages
MAK examines how to take advantage of VB Script and MS-DOS batch files, to restore multiple DTS packages stored in the form of structured storage files from one folder to a SQL Server box.
Getting Started Using JDBC with MySQL
Learn how to download, install, and prepare a MySQL database as a localhost server on the Windows platform. Then, discover how to write JDBC programs to administer the database server and to manipulate data stored on the MySQL database server.
MDX Essentials: The EXTRACT() Function
Return a fresh set of tuples from specified dimension elements using MDX. In this article, author Bill Pearson explores the EXTRACT() function, and leads practice in its operation.
Cursors with SQL 2000, Part 1
Don Schlichting begins a new series that examines the purposes, uses, and optimization of cursors in SQL 2000.
Gathering Space Usage Statistics
As a DBA, one of the DBA tasks you will need to perform is disk space capacity planning. Gregory Larsen discusses how disk space usage information can be a valuable resource in helping with capacity planning.
Virtual Indexes in Oracle
Oracle's Virtual Indexes have a specific purpose and do not behave like normal indexes. Learn how to create and use these pseudo-indexes.
Using IP Addresses to Scan the Network for SQL Servers
MAK examines how to scan the entire network for a range of IP addresses to locate which boxes have SQL Server instances installed.
MSSQL Server 2000 Reporting Services: Tracking Exchange Rates With Master Chart Reports
MSAS Architect Bill Pearson continues a set of articles surrounding the rich Reporting Services chart features. In this article, we meet an illustrative business need to track exchange rates with a line chart.
The Oracle 10g DBMS_SCHEDULER, Part 2: Implementation
Oracle 10g's new DBMS_SCHEDULER package offers significant improvements over DBMS_JOB for scheduling jobs and tasks. This article - the second in a series - provides practical examples of using the Oracle Scheduler's new functionalities, including a discussion of transitioning from DBMS_JOB to DBMS_SCHEDULER.
Database Configuration, the XML Alternative
With the popularity of XML today, a common design decision is to configure business application with markup files. While it has its advantages, storing this type of information in the database brings its own benefits.
MDX in Analysis Services: Moving Averages - Another Approach
MSAS Architect Bill Pearson examines another approach for handling moving averages within Analysis Services. In this article, we focus on the hands-on construction and testing of the MDX involved in our alternative approach to generating 'rolling' averages.
SQL Server 2000 Security, Part 12: Auditing with Meta Data Services
Marcin Policht discusses DTS Meta Data Services, which provides the ability to audit creation, execution, and modification of DTS packages as well as track associated data changes.
Installation Cookbooks: Deploying Reports on the Web Server
Scalar Sub-Queries in SQL Server
MAK examines how to use 'Scalar Sub-Queries' in the CASE expression, WHERE clause, ORDER BY and SELECT statement.
MSSQL Server 2000 Analysis Services Intro:: Basic Storage Design
Design aggregations for your cube. MSAS Architect Bill Pearson provides a hands-on introduction to the design of aggregation storage size and query performance optimization.
Executing SQL Stored Procedures from Microsoft Access
The simplicity and popularity of SQL Server means that more and more developers who build applications with Microsoft Access will want to learn how to take advantage of server side processing using SQL Server Stored Procedures. In this article, Danny Lesandrini demonstrates a simple method for executing procedures from Access.
Installation Cookbooks: Deploying Forms on the Web
This installment of the Installation Cookbooks series lays out a deployment framework to address the 'now that I have these things installed, what do I do next?' questions that are appearing with greater frequency on Oracle-related websites, including Oracle's own MetaLink site.
Automating "Save DTS package"
MAK examines how to save all of the DTS packages from SQL Server to a structured storage file automatically by taking advantage of Microsoft's stored procedure, 'DTSRUN.exe.'
SQL Server 2000 Security: Part 11 - DTS Security
In part 11 of this series, Marcin Policht turns our attention towards Data Transformation Services.
Java Relational Database Management System: Why Make the Change?
Your companies information is it's life's blood. When considering what to do with your information and the way it flows a smart Project Manager has to consider Java RDBMS. With it's portability and scalability, it could be cost effective to make the change.
MDX Essentials: Introduction to the AVG() Function
Compute the average of the values populating a set with the useful AVG() function. Join MSAS Architect Bill Pearson in a practical exploration of using the AVG() function to meet examples of simple and sophisticated business needs.
Work with XML Data Type in SQL Server 2005 from ADO.NET 2.0
Learn how to work with the XML data type column in SQL Server 2005. Along the way, you also will see how to read and write values into the XML columns from ADO.NET 2.0.
The Era of Open Source: Migrate your Data from MS SQL to MySQL
Despite the fact that MS SQL and MySQL both have their own strengths, a significant number of businesses have shifted their databases to MySQL because they keep finding good reasons to take advantage of it's openness. Allen goes on to describe the best way to convert MS SQL to MySQL.
Oracle Response Files, Part 1
Oracle's response files can be harnessed and used to your advantage. Learn how to use this untapped resource that can save you time and standardize installation of your Oracle products.
Oracle's System Parameter File, Part 2
In part two of this series, Amar Padhi examines practical example of working with the SPFILE.
MS Access for the Business Environment: Display Object Dependencies
Support documentation of object dependencies within an MS Office Access 2003 database. Join Bill Pearson in a hands-on introduction to the use of the View Object Dependencies functionality in meeting an illustrative business need.
Run Scripts with BatchOSQL Utility
Don Schlichting explains and defines the OSQL utilities ability to run scripts for administration and batch work when using Microsoft SQL Server.
Automate the Sizing of your SGA in Oracle 10g
There are plenty of options available for tweaking the SGA. However, without a complete understanding of what our applications are doing in the background, our ability to guess the appropriate amount of memory to give each of these individual components is not always optimal. Oracle now has methods to determine most of the major parameters all on its own.
Automating 'Generate SQL Script'
MAK examines how to automate the 'Generate SQL Script' by using a stored procedure to take advantage of 'scptxfr.exe' provided by Microsoft.
Working With Columns That Contain Null Values
What is a NULL? Does it have a value of zero, space or something else? Gregory Larsen discusses NULLs, some programming issues to be aware of when working with NULLS, and some different techniques that can be used when dealing with NULLS.
The Oracle Database 10g Scheduler - Part 1: Overview
Oracle 10g's new DBMS_SCHEDULER package offers significant improvements over DBMS_JOB for scheduling jobs and tasks. This article - the first in a series - provides an overview of the new functionalities that the Oracle Scheduler provides for DBAs.
MSSQL Server 2000 Reporting Services: Master Chart Reports
Join MSAS Architect Bill Pearson as he begins a set of articles surrounding the rich Reporting Services chart features. In this article, we meet an illustrative business need with a pie chart.
SQL Server 2000 Security - Part 10: Auditing
Sooner or later you will have to face an attempt of unauthorized intentional or accidental data or object definition change, exploit of security vulnerability, or even a malicious modification by an unethical system administrator. Learn how to be prepared for such possibilities and ensure you can track their source and prevent them from happening in the future.
Connecting with Oracle: The Password Game
Steve Callan reviews the use of Oracle's internal password function and the use of a SQL script for verifying password complexity.
Views Work In MySQL 5.0
On July 15 2004, Oleksandr Byelkin of the Ukraine merged his "views" code into MySQL. Peter Gulutzan has verified that the MySQL version 5.0 Linux source download supports views. If you know MySQL, know what views are and have been waiting a long time for MySQL to support them, this report will be of interest.
DB2 Database Migration to Version 8, Part 2
Part 2 of this series on the migration to DB2 v.8 examines issues that appeared during post migration testing.
Oracle 10g's Redo Logfile Sizing Advisor
Improperly sized redo logs can impede the performance of your database and hamper recovery time in the event of a database crash. Oracle has given us yet another advisory that helps us to properly size redo logs, taking yet another tedious task from us.
Scanning the Network for SQL Server
The MAK explains how to scan an entire network to find which boxes have SQL Server instances running and how to identify what version they are.
MDX in Analysis Services: Mastering Time - Introduction to Moving Averages
Provide support for rolling averages for information consumers within Analysis Services. Join author Bill Pearson in the hands-on construction and testing of the MDX involved, and the creation of a calculated member to generate moving averages.
In the Trenches with SQL Server and .NET
More than a simple code review, this 600-page book expounds on on programming conventions, methodologies, soft skills and oh yeah, there are some great code solutions too.
Oracle Administration: The Oracle PartnerNetwork
Part one of this series covered some important aspects of Oracle administration (MetaLink and licensing). Part two of this mini-series highlights the Oracle PartnerNetwork (OPN) and Oracle sales/reselling.
Restoring a Backup of a SQL Database
Learn how to restore the latest full backup and all corresponding transaction logs to the same or different database.
Setting Parameters in Oracle's SPFILE, Part 1
Server Parameter File (SPFILE) is an extension of the initialization parameter storage mechanism, which allows some additional advantages as compared to the simple text based init.ora file. Learn which parameters can be dynamically modified to affect the present instance, and which require the instance to be brought down so that changes can take affect.
MySQL Transactions, Part 2 - Transaction Isolation Levels
Ian Gillfillan examines the four transaction isolation levels of InnoDB tables, and how they affect the usual locking transactional behavior.
MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures
Bill Pearson walks throught the process to create a derived measure to replace an existing calculated measure, as an option for enhancing query processing performance.
Oracle 10g's New SQLAccess Advisor
If you have ever had a difficult time tuning applications because you can never find the SQL or code behind these applications, Oracle 10g's new SQLAccess Advisor, a new tool from Oracle, is a must.
SmartDBA Recovery Management for Oracle and Sybase
BMC Software, Inc. made SmartDBA Recovery Management 5.0 available on Monday of this week. SmartDBA, a product geared towards the over-worked DBA, consists of performance monitoring and tuning tools, database administration tools and backup and recovery utilities, all integrated into a common GUI web-based interface.
Oracle Administration: Licensing and MetaLink
This two-part series provides an overview of Oracle administration, including the use and licensing of Oracle's software.
Utilize BCP with SQL Server 2000
The Bulk Copy Program (BCP) is a command-line utility that ships with SQL Server 2000. Learn how you can import and export large amounts of data in and out of SQL Server 2000 databases with BCP.
ITTIA Launches db.* Embedded Database
ITTIA announces the availablity of db.* a free, high performance, small-footprint, embedded database for open source operating systems.
SQL Server 2000 Security: Part 9 - Replication Security
Dealing with replication security is a challenging task that needs to be carefully planned and implemented. Marcin Policht examines the creation and administration of publishers, distributors, and subscribers (including enabling a database for replication).
Auto-Number and Cumulative sum in SQL Server Query results
SQL Server developers and database architects often find they have a need to sequence query results or generate a cumulative sum for a group of rows in a table. Learn how to use co-related sub-queries and/or identity functions to generate such sequential numbers and cumulative summations in query results.
Microsoft SQL Server 2000 Desktop Engine (MSDE)
Microsoft application developers have often been faced with the decision of using a database engine they liked, versus an engine that was affordable to ship with the completed product. With the release of the Microsoft SQL Server 2000 Desktop Engine (MSDE), a true transactional database server can be shipped royalty free.
If you have yet to find out about the little extended stored procedure gem known as 'xp_cmdshell', then this article is for you. Discover what 'xp_cmdshell' is and the different things you can use it for.
Instant Client in Oracle 10g
Wouldn't it be nice if you didn't have to install the full Oracle client software just to get connectivity to an Oracle instance? Well, it's now reality with Oracle's new Instant Client software offering.
MDX in Analysis Services: Mastering Time: Period - to - Date Aggregations
Accomplish year-to-date and other aggregations periods in the Time dimension using MDX. Join author Bill Pearson in a hands-on introduction to period-to-date aggregations at multiple hierarchical levels.
An IBM DB2 Universal Database "Stinger" Feature Preview: Simplifying Database Recovery
The fourth article of this series introduces the RECOVER DATABASE command and changes to the history file that, together, make it easier to recover a database from a backup image.
SQL Server 2000 Security - Part 6: Ownership and Object Permissions
Marcin Policht continues his discussion of SQL Server 2000 Security with a look into permissions from the point of view of database objects.
Oracle Certification - The Last Word on OCP for Oracle8i
If being certified in Oracle 8i is something you want to do and you have not started yet, today is the day you need to start studying - even if you are an old hand at Oracle.
So You Want to Become an Oracle DBA? Part 2 - Learning Oracle on your own
This is the second of a two-part series about how you can become an Oracle DBA in today's almost non-existent DBA job market. This article focuses on inexpensive and effective means of learning Oracle.
So You Want to Become an Oracle DBA? Part 1 - Getting Close to Oracle
This is the first of a two-part series about how you can become an Oracle DBA in today's almost non-existent DBA job market.
MSSQL Server 2000 Reporting Services: Managing Reporting Services
Build a Data-Driven Subscription with File Share Delivery for reports. Bill Pearson focuses on this largely undocumented method, versus the ubiquitous E-mail Delivery example.
Bulk Binds and Collects in PL/SQL - Part 1
This article begins a discussion of how to work with collections. Previous versions of Oracle had limitations for collection usage in regards to SQL statement processing. Bulk Bind and Collect features were introduced to reduce the SQL processing overhead by efficient use of collections in PL/SQL code.
Striking Gold with LogMiner - Part 1: Getting Started
Oracle's LogMiner utility has been around for several years now, and it originally appeared as a new feature (as opposed to being an improvement of something else). The purpose of this series is to help demystify some of the procedures, illustrate some examples of using LogMiner, and to recommend cases where LogMiner should be a DBA's primary tool for recovering lost data.
Inventorying Hardware and OS Information on All SQL Servers
Whenever database administrators begin new jobs or to administer and manage new SQL servers, the first step is to take inventory of the server. This includes information like operating system, service packs, physical memory, virtual memory etc. Learn how to collect such information using VB Script and WMI.
Creating NOT NULL Columns in Huge Oracle Tables
Databases are often taxed by applying SQL statements to enormous tables. One such activity is when we add a new NOT NULL column with default value to a huge transaction table. Amar Padhi shares several methods to add a new column with default value, comparing the advantages and disadvantages of each.
MS Access for the Business Environment: Stored Procedures from the MS Access Client
Leverage the efficiencies of Stored Procedures in MSSQL Server from an MS Access client. Join Bill Pearson in creating and executing MSSQL Server Stored Procedures from MS Access to meet the business needs of the enterprise.
Database Basics - A Primer
Small Business IT guru Beth Cohen kicks off part one of a series on everything you ever wanted to know about databases but were afraid to ask.
Surviving a Database Disaster with RMAN Backups
Follow along with this real-world disaster scenario and learn how one company recovered their databases using RMAN backups.
Control of Flow with TSQL
Don Schlichting explores the special Transact SQL Control of Flow key words used to direct the execution of statements.
Oracle's CASE Expression
Faster than a speeding bullet, more powerful than a locomotive... Perhaps not, but Oracle's CASE expression can do all that the DECODE function does, plus a whole lot more.
A Practical Guide to Data Warehousing in Oracle, Part 5
Why can a data warehouse be operated successfully and safely without integrity being enforced at the database level? Learn about creating lightweight declarative integrity constraints that avoid much of the unwelcome overhead of maintaining enforced constraints.
Unwrapping Oracle's DBMS Packages: Understanding Oracle's Random Number Generator
Beginning with later versions of Oracle8, Oracle has provided a means of generating random numbers. This built-in package, DBMS_RANDOM, is fairly simple to use, and can generate random numbers which are generally good enough for the needs of most users. Learn more as Steve Callan unwraps the first DBMS package of this series.
Parsing in Oracle
Amar Kumar Padhi discusses the process of parsing, the difference between soft and hard processing, how Oracle deals with identical statements and various methods to reduce hard parsing.
Using the SQLXML Managed Classes
Take a look at the bridges that SQLXML provides between SQL Server 2000 and the .NET Framework.
MySQL, Still tomorrow's contender?
Ian Gilfillan takes a second look at the MySQL Roadmap to see which of his predictions of a year ago saw the light of day,and which are still down the road.
Introduction to MSSQL Server 2000 Analysis Services: Actions in Virtual Cubes
Leverage the power of Actions within virtual cubes. MSAS Consultant Bill Pearson provides a hands-on introduction to using Actions to extend virtual MSAS cubes.
Product Review: SQL Compare by Red Gate Software
If you have database maintenance problems, you need to check out Red Gate's SQL Compare. This one tool can compare production databases against frozen snapshots, compile a script for documenting and migrating batch changes from development to production, and identify differences between databases. Need more? How about synchronization at the click of a button.
Tackling Slowdowns: Utility Throttling in IBM DB2 UDB V8.1.2 and Beyond
Learn how utility throttling empowers DBAs to automatically assign more computing resources during off-peak hours to some utilities and scale resources back during periods of high-resource demand.
Data Modeling, Breaking and Fixing First Normal Form
The inability to conform to the very basics of data modeling principles often causes enormous problems for data modelers, administrators and the development chain. Of those principles, adhering to 1NF is probably the most broken rule. When the model does not follow 1NF, queries are hard to develop and producing usable data from the model is even harder. This article explores some of the common mistakes and their fixes.
MS SQL Server Distributed Partitioned Views Part 2
In the first installment of this series the basics of Distributed Partitioned Views, Federated Databases, and Horizontal Partitioning were reviewed. This month Don Schlichting explores the use of Distributed Partitioned Views for Insert, Update and Delete statements.
Working with VARRAYs in Oracle Part III
The third and final installment of this series examines the performance implications of using VARRAYS.
Web Data Administration Tool from Microsoft
Are you a Database Administrator that has a desire to work remotely but don't have access to your databases while you are out of your office? Would you consider using a web interface to manage your SQL Server databases and Logins? If so, then you might want to consider checking out Microsoft's tool that will allow you to perform some SQL Server administration functions via the web.
Back Up a DB2 Database using Tivoli Storage Manager
Marin Komadina picks up where he left off last month, with an examination of the procedure for making a database backup using Tivoli Storage Manager, (TSM).
A Practical Guide to Data Warehousing in Oracle, Part 4
On a regular OLTP system, one of the perennial sources for doubt and debate is the number, type and configuration of indexes. Dave Aldridge shares some simple principles that can be applied to the business of indexing a data warehouse.
The Authoring Phase: Overview Part II
MSAS Architect and Reporting Services Consultant Bill Pearson introduces the addition of data, then grouping, sorting and more, within a basic tabular report, as he concludes his overview of the Authoring phase of MSSQL Server 2000 Reporting Services.
Oracle on Windows - Part 2
Oracle on Windows is easy to use, and even easier to use if you have a good understanding of how the Windows operating system works. With Oracle's new pricing scheme aimed at enticing small businesses to make the switch from the SQL Server arena, you almost cannot afford not to know more about the Oracle and Windows combination.
Import Multiple Files to SQL Server Using DTS
MAK discusses two different methods of importing flat files to SQL Server tables using a batch file and DTS package.
Database Search ASP.NET Sample Code
This sample code takes user input from a form and searches for matches in a database table. Need I say more?
CA-Datacom, release 11 available
Computer Associates International announces the availability of Advantage CA-Datacom, Release 11.0. Datacom is a web-enabled database platform, supporting large transaction processing systems...
The Mutation error in Oracle Database Triggers
Amar Kumar Padhi discusses the causes of the mutating table error and how to prevent it.
MDX in Analysis Services: Named Sets in MDX: An Introduction
MSAS Architect and Author Bill Pearson introduces Named Sets, focusing on their creation using the WITH statement in MDX.
Book Review: Real World Access DB Protection and Security
'Real World Microsoft Access Database Protection and Security' explores all aspects of security, not just creating a workgroup file and setting permissions. The author made a point of introducing fresh new ideas into the topic of Access security, even differentiating security methods from protection techniques.
Speeding Up Performance with Query Sampling
Today's databases (and especially data warehouses) have become so large, and their query workloads have become so complex, that it is often impractical and even unnecessary to retrieve all of the data that may be relevant to a particular query. This article outlines DB2's SQL query sampling support with the new TABLESAMPLE clause, which has been added to the FROM clause of the SQL subselect in version 8.1.2.
Oracle Database Resource Manager, Part 3: Conclusion
Oracle Database Resource Management (DRM) provides tools that allow any Oracle DBA to manage a database server's CPU resources effectively for application user groups and during different resource demand periods. This final article builds upon the concepts in the two previous articles, including the construction of more complex resource plans, monitoring DRM utilization, and general DRM maintenance.
Using Oracle's SQL Functions - Part 2
The second article of this series looks at 11 SQL-related functions commonly used in statistics: count, sum, average, standard deviation, variance and covariance (standard deviation and variance have three each; covariance has two).
Restoring lost data from the Binary Update Log
Your data has been lost and you have no backup. Learn how to save the day using the binary update log.
Import multiple Files to SQL Server using T-SQL
MAK discusses two different methods of importing flat files to SQL Server tables using XP_CMDSHELL.
Working with VARRAYs in Oracle - Part II
Part two of this three part series takes you through the steps on how to abstract the difficulty from using VARRAYs so that developers or end uses can interact with these structures through familiar table insert and select statements.
Installation Cookbook: Installing Oracle Application Server 10g (9.0.4)Forms and Reports Services
Support for versions older than Oracle 9i is coming to an end--users wanting to view data in a database via forms will have to move to Oracle 9i/10g forms. The big show stopper on that process is that you need to have Application Server installed and running to view 9i/10g forms on the web. Learn how to install Application Server and make the connection to your databases.
Use Oracle's DBMS_APPLICATION_INFO to Prevent Routines from Running Simultaneously
Learn how to use Oracle's DBMS_APPLICATION_INFO to prevent two routines or business processes from running at the same time
SQL Server 2000 DTS Part 11 - DTS Designer Tasks - the Message Queue task
Part 11 of DTS Designer Tasks examines the Message Queue task, discussing the concept of message queuing and its Microsoft-specific implementation, describes its features and provides an example demonstrating their use.
Simplifying Your Application Development with the SQL MERGE Statement
This article outlines SQL MERGE statement support in the IBM(R) DB2(R) Universal Database(TM) (DB2 UDB) products as of Version 8.1.2. The MERGE statement combines conditional update, insert and delete operations on a target table or updatable view.
MDX Essentials: Basic Numeric Functions: The Count() Function
Return the number of cells within a set. MSAS Architect Bill Pearson introduces the Count() function and leads practice in its use.
MS SQL Server Distributed Partitioned Views
Don Schlichting explores the use of Distributed Partitioned Views for accessing multiple MS SQL Severs when configured as a Federated Database.
Microsoft SQL Server 2000 command line utilities (Part 2)
Alexzander Nepomnjashiy continues his examination of how to use the Microsoft SQL Server 2000 command line utilities and what they are used for.
A Practical Guide to Data Warehousing in Oracle - Part 3
One feature that is almost ubiquitous in Oracle data warehousing is the use of the Partitioning Option. Discover how the One feature that is almost ubiquitous in Oracle data warehousing is the use of the Partitioning Option over the past few major releases.
Using Oracle's SQL Functions
Oracle provides quite an array of functions when it comes to manipulating data via SQL. Of particular interest for this new series, are the functions related to numbers. Learn how this relates to your job as a DBA.
Locking Down SQL*Plus Security
Is your data at risk? Users with valid database username/password combos can log into a database from SQL*Plus and view or edit critical data not accessible from an application itself. The question is, how do you prevent users from accessing data via SQL*Plus? Learn how to insert restrictions in the PRODUCT_USER_PROFILE (PUP) table, owned by the SYSTEM user.
DB2 Command Line Processor Tips and Tricks
DB2 UDB Version 8.1 with FixPak 1 and DB2 UDB Version 8.1.2 introduced new usability features for the DB2 interactive CLP, including customizable DB2 CLP interactive prompts and a command cache that can be accessed or modified through the new DB2 CLP HISTORY command, the RUNCMD command or the EDIT command.
JS Slot Machine
This is a slot machine script. It's a fun little code snippet to add to your web site.
Oracle Database Resource Manager, Part 2: Resource Plan Management
Oracle Database Resource Management (DRM) provides tools that allow any Oracle DBA to manage a database server's CPU resources effectively for application user groups and during different resource demand periods. This article builds upon the concepts in Part 1 to build and maintain more complex resource plans, assign users to resource consumer groups, and manage users and session utilization of DRM resources.
Microsoft SQL Server 2000 command line utilities (Part 1)
This first article in a series of two, discusses the 'hows' and 'whys' Microsoft SQL Server 2000 command line utilities.
iAnywhere releases free version of M-Business Anywhere Developer Edition
iAnywhere simplifies the development of database-powered mobile Web applications with free M-Business Anywhere Developer Edition and new integration with SQL Anywhere Studio.
DBA Call to Action: Zeroing in on Performance Problems
Have you ever been asked to help with the evaluation of a database that is not performing optimally? Here is a quick guide to help you if you just do not know where to start.
Installing Oracle9i Developer Suite
Most people can muddle through Oracle product installations on their first one or two attempts. But what happens when you run into the occasional lemon-like installation guide, such as the one for Oracle9i Developer Suite (iDS)? Steve Callan offers a detailed, step-by-step installation cookbook for installing Oracle9i Developer Suite (9.0.2, including a patch) on a Sun Solaris platform.
DDL Event Security in Oracle Database
Secure your Oracle database against structural or Data Definition Language (DDL) changes.
SQL Server 2000 DTS Part 9 - DTS Designer Tasks
The last installment of this series discussed the basic principles and applications of the Transform Data task. This article continues, focusing on its more advanced features.
MDX Essentials: Basic Set Functions: The Filter() Function
Filter out records you do not want without compromising the efficiencies of MDX sets. In this lesson, we will explore using the powerful Filter() function to return subsets of larger sets, based upon conditions we supply.
Data Mining Algorithms: Microsoft SQL Server 2000 vs. "Yukon" SQL Server
This article describes Data Mining algorithms, built into Microsoft SQL Server 2000 Analysis Services and the author's hopes and expectations in a field of new / improved data mining algorithms for 'Yukon.'
DB2 Instance and Fast Communication Manager - Part 2
The process of tuning an ESE DB2 database is automatic--almost. Several components still require human intervention and the Fast Communication Manager is one of them. Learn about the Fast Communication Manager Memory Structure, Troubleshooting and Tuning in Part 2 of this series.
A Practical Guide to Data Warehousing in Oracle, Part 2
Part 2 of 'A Practical Guide to Data Warehousing' examines the various possible attributes of tables, and seeing what use can made of them in the different table types of a data warehouse.
MSSQL Server 2000 Reporting Services: A New Paradigm for Enterprise Reporting
MSSQL Server 2000 Reporting Services: A New Paradigm for Enterprise Reporting It's here ... A New Paradigm for Enterprise Reporting. Author Bill Pearson kicks off a new series surrounding the exciting new functionalities that debut with MSSQL Server Reporting Services.
Oracle Optimizer: Moving to and working with CBO - Part 7
The final installment of this series covers the basics of using Stored Outlines and Oracle Application specific information.
Optimizing MDX: More on Location, and the Importance of Arrangement
Bill Pearson continues the Optimizing MDX sub-series with more on the use of processing location to optimize our queries. He then leads an exploration of the optimization of set operations and expression management to help us assemble efficient MDX queries.
Database Performance Philosophy
Here are some valuable tips on designing databases and applications for efficient querying.
DTS Designer Tasks - Transform Data Task Part 8
The eighth article of this series examines the default behavior of the Transform Data Task followed with a discussion of its more advanced features, providing an explanation of why this task is often referred to as the Multiphase Data Pump.
The Trigger-Happy DBA - System Triggers
The use of system triggers can greatly expand a DBA's ability to monitor database activity and events. Join Steve Callan as he examines the creation and use of system triggers.
Benchmarking Performance of a Query - Part 1 Elapsed Time
How many times have you had more than one way to do something and wanted to determine which method was faster or which one used less resources? In the first of this two-part series, Greg Larsen discusses some methods for using Query Analyzer to help make this decision.
Repairing Database Corruption in MySQL
Ah, the wonderful feeling of being hauled out of the spa/candlelit dinner/Quake game to be told in panicked tones that there is a 'database error'. Luckily, MySQL has some easy-to-use tools that can easily repair most cases of table corruption, and this article introduces you to these.
A Fresh Look at Data Striping in DB2 Universal Database Version 8.1
A key concurrency-enabling feature, introduced in version 8.1 of DB2 UDB was the ability to start a new data stripe on the underlying storage units. In this article, we introduce you to this new feature and the problems that it solves, and take you through a step-by-step example of how it works.
Reporting Options for Analysis Services Cubes: ProClarity Professional, Part I
Discover yet another option for effectively reporting from MSSQL Server Analysis Services cubes. Author Bill Pearson revisits practical reporting and analysis solutions with an introduction to ProClarity Professional for Analysis Services.
Navigate Access Forms with Faux Hyperlinks
Danny Lesandrini examines a way to simulate web browser type hyperlinks on Access Windows forms.
Monitor Disk Space on Multiple SQL Servers
Muthusamy Anantha Kumar discusses three methods to monitor disk space on a list of servers and store the output either in a .CSV file or on a database table.
MDX Essentials: Basic Set Functions: The EXCEPT() Function
Return the difference between two sets using MDX. In this lesson, Bill Pearson explores the EXCEPT() function, and leads practice in putting it to work to meet illustrative business needs.
SQL Server 2000 DTS Part 7 - DTS Designer Tasks: the ActiveX Script task
While scripting is not very popular among database administrators, its potentials are worth exploring, especially since familiarity with its concepts is required to fully understand the remaining types of tasks we will be covering in this series. This month's installment examines the ActiveX Script task, which utilizes scripting functionality extensively.
Manipulating Oracle Files with UTL_FILE
Take hold of your alert log with the use of a few new subprograms in the UTL_FILE package.
T-SQL Programming Part 5 - Using the CASE Function
Have you ever wanted to replace a column value with a different value based on the original column value? Learn how, with the T-SQL CASE function.
Linked Severs on MS SQL Part 4, Oracle
Part 4 of this series deals with using Oracle as a Linked Server target from MS SQL, and is written from a Microsoft product point of view, assuming familiarity with MS SQL, and relatively little experience with Oracle.
MS Access for the Business Environment: Access Query Techniques: Using the TOP Keyword
Find the first (n) records in an Access query. In this lesson, Bill Pearson explores the use of the TOP keyword to return a specific number or percentage of records at the top or bottom of an ordered query result dataset.
DBA Call to Action: New Year Resolutions
James Koopmann looks at ways to plan your New Year resolutions around your current DBA roles and responsibilities.
The Trigger-Happy DBA - Part 2
What a form trigger can do, and has in common with the "pure" development type of trigger, is generate the ORA-04091 mutating table error. Learn how to work your way around this error by taking your DML elsewhere.
MDX in Analysis Services: Optimizing MDX: Control Location of Processing
Discover ways to optimize performance in MDX queries. Join author Bill Pearson in a three-part mini-series that begins with a focus on control of the location of query processing.
Oracle Optimizer: Moving to and working with CBO - Part 6
Previous installments of this series have covered how the Cost Based Optimizer works and behaves. In part 6 of the series, learn how to assist the optimizer to do its job properly.
Operationally living in harmony with data warehouses
Since the building of data warehouses began, the perceived wisdom has been to maintain a separation between the data warehouse and the company's operational systems. Today, while this 'separatist' thinking still dominates among many data warehousing professionals, the approach is being questioned. Evolving wisdom is to consider alternatives to the implementation of a traditional warehouse solution, where operational systems are capable of living in harmony with the data warehouse.
Automatically Deploy a New Access Client
Learn how to manage the distribution of new versions of your Microsoft Access applications to network users, without the use of third-party tools.
Oracle Recovery Manager (RMAN) features have been expanded significantly in Oracle 9i. This article discusses several of these new features and includes examples on how to implement them to make any Oracle DBA's backup, restoration, and disaster recovery plans more effective.
Setting Variables in Calling T-SQL Code While Using sp_executesql
Occasionally you need to build dynamic T-SQL that not only requires the T-SQL code to be dynamic, but also requires the dynamic T-SQL to return values from the dynamic code to the calling T-SQL code. Learn how how to use the sp_executesql SP to allow a T-SQL coder to set variables in the calling T-SQL code when executing dynamic T-SQL.
PostgreSQL vs MySQL: Which is better?
Ian Gilfillan asks a loaded question, and then answers it with the only logical answer... 'Neither is best, and both have their place.' Read on to find out how these two databases stack up, and how they differ from each other.
Using Calculated Cells in Analysis Services , Part II
Create calculated cells via an MDX query. In this article, Bill Pearson extends his introduction of calculated cells in Analysis Services to the realm of direct MDX, where we practice hands-on calculated cell creation at the query level.
Using Calculated Cells in Analysis Services , Part II
Create calculated cells via an MDX query. In this article, Bill Pearson extends his introduction of calculated cells in Analysis Services to the realm of direct MDX, where we practice hands-on calculated cell creation at the query level.
Oracle Technical Interview Questions Answered - Part2
James Koopmann provides more answers to the questions raised in his popular article 'Technical Interview Questions.'
Troubleshooting SQL Server Jobs
If you have problems with SQL Server jobs, review this 14-point troubleshooting checklist to find potential solutions.
DB2 Instance and Fast Communication Manager
The process of tuning an ESE DB2 database is automatic--almost. Several components still require human intervention and the Fast Communication Manager is one of them. Learn about the global DB2 memory structure and FCM tuning parameters for a SUN Solaris platform.
SQL Server 2000 DTS Part 5 - DTS Designer Tasks and Global Variables
Marcin Policht continues his discussion of DTS Designer tasks, beginning with a look into the concept of DTS Global Variables.
Bridging the gap between databases and XML
Snapbridge Software reveals its patent-pending technology for real-time integration of large amounts of data from multi-data sources.
Basic Set Functions: The Intersect() Function
Return the intersection of two sets using MDX. In this lesson, author Bill Pearson explores the Intersect() function and offers practice in its operation.
Linked Severs on MS SQL Part 3
In part 3 of 'Linked Severs on MS SQL' Don Schlichting continues his discussion of linked SQL server security.
SQL Server: Calculating Running Totals, Subtotals and Grand Total Without a
Greg Larsen examines different techniques for calculating and summing information on multiple rows without using a cursor. Learn how to show detailed data of individual transactions and also keep a running total, subtotals, and grand total columns at the same time.
Configuring Disks and Managing Space in SQL Server Part 1
Something like fifty percent of system outages are caused by "Out of space" conditions. Combine this problem with the need to maintain high availability and peak performance and the consequence is that disk configuration and space allocation become one of the principle tasks for a SQL Server DBA.
MS Access for the Business Environment: Access Query Techniques: Subqueries, Part 1
Nest one query inside another, and hatch the power of subqueries. In this lesson, Bill Pearson leads a multi-step practice example in meeting a business need with a basic subquery in MS Access.
Practical Data Warehousing in Oracle, Part I
Enemy Number One--the disk subsystem. Nothing will cripple a data warehouse more effectively than a failure to manage your i/o requirement vs. your i/o capacity. Take a different look at disk capacity.
Access Report Tricks
Danny Lesendrini contends that if you don't think Access is the best reporting tool around, you haven't experienced the power of writing reports in Microsoft Access. Join him as he demonstrates a few examples of what can be done with Access Reports.
Oracle Label Security, Part 4: Conclusion
Oracle Label Security (OLS) offers a powerful implementation of row-based security that is perfect for restricting user access to specific data, especially in a data mart or data warehousing environment. This concluding article wraps up this series with a discussion of some advanced OLS features as well as mechanisms for maintaining an existing OLS security policy.
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells
Create calculated cells in Microsoft Analysis Services. In this article, Bill Pearson discusses uses for calculated cells, and walks through a hands-on setup exercise.
Oracle9iR2 Segment Level Statistics
Oracle's Segment Level Statistics gives you the power to make decisions on the configuration of structures within Oracle as well as to application code. Learn how to fine tune memory structures and access methodologies using Oracle's Segment Level Statistics.
Connecting with Oracle: Finding and Setting a Configuration File Location
You can have a 'good' tnsnames.ora file, but 'reject' its use due to not knowing where it is. You can have a 'bad' tnsnames.ora file in a location you know about, and try to 'accept' its use. Both situations are bad. Fortunately, each is easy to correct. Learn how to set a location for your tnsnames.ora file and where to look for that location.
Application Design Optimization Tips
Here are fifteen tips that you can use to ensure your Application Designs are performing in the most efficient manner possible.
MDX Essentials: Basic Set Functions: The Union() Function
Combine two sets wih the valuable Union() function. Join Bill Pearson in a hands on session that centers around Union() and its alternates.
SQL Server 2000 DTS Part 3 - DTS Designer Connections
The last installment of SQL Server 2000 DTS covered creating DTS packages with wizards. This month, Marcin Policht examines how to create and edit DTS packages using DTS Designer.
DatabaseJournal: Linked Servers on MS SQL Part 2
Part 1 of Linked Servers on MS SQL reviewed the purpose and reasons for using linked servers, when not to use them and creating a linked server to an Excel sheet containing data from the Authors table of the SQL Pubs database. This month Don Schlichting moves on to other data sources and security.
T-SQL Programming Part 2 - Building a T-SQL Loop
Greg Larsen continues his series on T-SQL Programming with an examination of building a program loop using T-SQL. Learn how to control the loop process and methods to break out of a loop.
Implementing CRUD Operations Using Stored Procedures: Part 2
Andy Novick gets down and dirty with stored procedures, exploring why you might want to use them instead of SQL script to implement CRUD operations.
Oracle: Preventing Corruption Before it's Too Late - Part 2
Marin Komadina discusses Oracle data-block corruption, describing several features of Oracle that may be used at an early stage to detect database corruption. Part 2 of this series explores Oracle soft and bug corruption.
Heterogeneous Database Environments--A Single Workbench
Quest Software, Inc. announces the release of Quest Central for Databases 4.0, the first database management solution for heterogeneous database environments within a single workbench.
SQL Server 2000 DTS Part 2 - Creating DTS Packages using Wizards
Marcin Policht explores two quick and easy ways of creating DTS packages: the DTS Export/Import Wizard and Copy Database Wizard.
Connecting with Oracle
Steve Callan discusses a common error frequently seen in Oracle Net Services. Learn how to avoid the "ORA-12154: TNS: could not resolve service name" error.
Introduction to MSSQL Server 2000 Analysis Services:
Explore more sophisticated Usage Analysis reporting options for your cubes. Author Bill explores the Query Log, including its modification and use as a direct reporting source.
SpiralStar comes as part of the Anfy application which not only configures the applet for you, but also generates the HTML code! All you do is click buttons, no programming experience required!
SQL Server 2000 DTS Part 1
As an introduction to his new series, 'SQL Server 2000 DTS,' Marcin Polict provides an overview of the main concepts of this technology.
MDX Essentials: Basic Set Functions: The Order() Function
Provide the sorts you need for reports and applications using MDX. In this lesson, we will explore using the versatile Order() function, for both hierarchical and nonhierarchical sorts within MDX.
Preventing Public Access to the DB2 Database - Part 2
Concerned about who is walking unchallenged through the Public group door into your database? Marin Komadina concludes his examination of the security aspects of the DB2 database default PUBLIC group
The comparison of SQL Server 2000 with MySQL v4.1
Alexander Chigrik compares SQL Server 2000 with Microsoft Access regarding price, platforms supported, features, and product limitations.
Oracle Optimizer: Moving to and working with CBO: Part 3
Part 3 of this series discusses the setup changes that should be considered for migrating to CBO.
SQL 2000 and Web Services
Marcin Policht concludes his XML and SQL series with a look at Web Services and their implementation in the SQL Server 2000 environment.
Simple Cube Usage Analysis
Perform simple cube usage analysis. In this article, author Bill Pearson introduces the Usage Analysis Wizard for generating "quick and dirty" cube processing statistics, then looks ahead to other, more robust analysis options.
Delete Duplicate Records From Access Tables
Danny Lesandrini examines several methods to delete duplicate records from Access tables.
Oracle Label Security, Part 2: Implementation
Oracle Label Security (OLS) offers a powerful implementation of row-based security that is perfect for restricting user access to specific data, especially in a data mart or data warehousing environment. A previous article presented a brief overview of how these features work; this article presents how a reasonably skilled Oracle DBA can configure a database for OLS, install an OLS security policy, implement security for users, and impose row-level security for data.
MDX in Analysis Services: Calculated Members: Introduction
Master the design and creation of Calculated Members to meet business needs. In this lesson, author Bill Pearson previews the creation of calculated members to set the stage for in-depth exploration of Calculated Members in subsequent sessions.
Table types in MySQL: Part 1 - HEAP tables
We all know accessing data in RAM is a lot quicker than accessing data on disk. So, with this in mind, wouldn't it make sense for MySQL to store as much data as possible in memory? The answer is of course yes, and MySQL can, in a number of ways. Learn about HEAP tables in this article from Ian Gilfillan.
SQLISAPI and XML Client-Side Processing (XML and SQL part 10)
Marcin Policht continues his XML and SQL series with a look at client-side XML processing with SQLXML 2.0 (and later).
Active Archiving Capabilities for Oracle E-Business Suite
At Oracle World today, Princeton Softech announced the availability of Archive(tm) for Servers Oracle Applications Edition. Archive for Servers Oracle Applications Edition is an out-of-the-box solution, specifically designed to deliver comprehensive active archiving capabilities for the Oracle E-Business Suite
MDX Time Series Functions, Part III: The LastPeriods() and
Join author Bill Pearson in the last of three articles surrounding the time series functions, designed to support the analysis of data within the context of time. In this lesson, we will explore the LastPeriods() and ParallelPeriod() function.
Updating SQL Server Connection Properties from the DTS Package INI File
Muthusamy Anantha Kumar steps through updating the connection properties from the DTS package INI file during run time.
MS Access for the Business Environment: Reporting in MS Access: Grouped
Create a report that mirrors a real-world business need. In the first segment of this two-part article, author Bill Pearson begins the step-by-step design of a report that presents transactional customer information, then groups that information at multiple levels with corresponding totals.
Gupta Technologies Releases SQLBase 8.5
Gupta Technologies, LLC. announces the latest release of it's embedded database, SQLBase.
Oracle Label Security, Part 1: Overview
Oracle Label Security (OLS) is a relatively new feature of Oracle 9i. It offers a powerful implementation of row-based security that's perfect for restricting user access to specific data, especially in a data mart or data warehousing environment. This article presents a high-level view of this new set of features in preparation for implementation by any reasonably skilled Oracle DBA.
Oracle Optimizer: Moving to and working with CBO - Part 2
In this months installment of 'Oracle Optimizer and how to move to CBO,' Amar Padhi covers the Initialization parameters and Hidden or Internal Oracle parameters that influence the Optimizer in choosing execution plans.
Background Properties Changer
This script changes between fixed and scrolling background. A nice little touch to add to your homepage.
Migrating a Maintenance Plan from One SQL Server to Another
Since there is no easy way to migrate a maintenance plan from one SQL Server to another, most DBAs just recreate all their maintenance plans on the new SQL Server machine. In this article Greg Larsen shows you a method to migrate a maintenance plan from one machine to another.
PostgreSQL Software Installation
Getting free software and installing can sometimes be a daunting task. Not so with PostgreSQL. James Koopmann walks through both UNIX and Windows installation procedures.
Referential Integrity in MySQL
Ian Gilfillan briefly explains the concepts of referential integrity, and delves into how MySQL enforces those concepts with its definition of foreign keys.
Using Sets in MDX Queries
Learn the fundamentals of an MDX query as we explore operations with Sets. Author Bill Pearson presents a practical overview of basic MDX queries and explores MDX sets in this high impact article.
Concatenate Column Values from Multiple Rows into a Single Column with Access
Danny Lesandrini tackles concatenating column values from multiple rows into a single column.
dbWidget is a multi-purpose database development tool which uses JDBC for connectivity. This edition supports MySQL, MS SQL Server, Sybase, and Oracle. Requires JRE 1.3 or higher.
Protecting DB2 against Non Concurrent Application Code
DBAs have learned that performance of an application can be influenced by many factors, which in turn can influence database performance. In this article, Marin Komadina explores a real-world example of an application with a locking problem.
Managing Users Permissions on SQL Server
Join Alexander Chigrik for an in-depth refresher on managing SQL Server user permissons.
Tracking Data Access Patterns in Oracle
Performance in Oracle is directly related to the SQL workload that is submitted. Often it is not the change in the SQL that degrades performance but instead is the access path to that data. This article will help you understand when new access paths have been introduced in your environment.
Sequential Numbering/Counting of Records with SQL Server
Microsoft SQL server does not support a method of identifying the row numbers for records stored on disk, although there are a number of different techniques to associate a sequential number with a row. This article will show various different methods of assigning a record sequence number to records returned from a query.
Examining SQL Server's I/O Statistics
Input/Output is at the heart of what SQL Server does. This article shows several methods for getting I/O statistics from SQL Server as an aid in diagnosing performance issues.
Macromedia announces the release of ColdFusion MX 6.1
Macromedia today announced the release of ColdFusion MX 6.1. In it's latest release, Macromedia has concentrated on simplifying the product family, simplifying the installation procedure and updating the product to support the latest operating systems.
Enhanced SQL Server Security Auditing
All of the processes in SQL Server can be viewed by querying the system table 'sysprocesses.' Learn how to use a process to capture un-authorized users logging onto your Server using production login information and SQL Query tools such as Enterprise manager and Query analyzer.
Automating ETL using Oracle warehouse Builder - Part 2: OWB architecture
In part 2 of the Oracle Warehouse Builder series, Nandeep discusses the Oracle Warehouse Builder architecture, its compliance with industry standards and is technical suitability for use in DW projects.
Scripting Traces for Performance Monitoring on SQL Server
Andy Novick presents a technique for writing a stored procedure that creates a trace, sends its output to a file on disk and then moves the data from the trace file into a SQL table for analysis. Using the file as the trace destination and loading the data overnight, minimizes resources required for peak-hour monitoring.
Oracle Safety Blankets: Insulating Your Applications Against Database Changes
Changes to Oracle database objects can wreak unexpected havoc on the applications that depend on them, especially when the database objects are invalidated even for a brief period.This article discusses some techniques that any DBA can put into practice to limit the impact of database object changes on dependent applications
Oracle Optimizer: Moving to and working with CBO
Learn about the Oracle Optimizer and various efficient ways of moving to Cost Based Optimizer. Part 1 of a five part series.
The Pros and Cons of Automatic Updates
Ask any administrator and they'll tell you that maintaining a secure network can be a juggling act. If you're considering letting your Windows server automatically download and install security updates, there are a few things you should know before either of you drops the ball.
How to Create ODBC DSN on multiple SQL server machines
A particularly tedious job for Network Administrators is the creation of ODBC DSN on multiple client machine or server for front-end applications. MAK shares a script for creating ODBC DSN (for SQL server) on multiple machines.
Reducing SQL Server Index Fragmentation
Alexander Chigrik shares various methods and tips to reduce index fragmentation.
MDX in Analysis Services: Measuring Change over Time
Explore the incorporation of time-based analysis into MDX expressions in Analysis Services. Author Bill Pearson presents a hands-on lesson in using MDX to analyze change in values from a prior period, a parallel period, and more.
Build a Web Site Traffic Analysis Cube: Part I
Discover hands-on approaches to extracting site traffic data, and loading it to a data source from which we can build a Site Traffic Analysis Cube. Author Bill Pearson kicks off a two-part article with the ETL side of the process, in preparation for cube design and construction in Part II.
How to Merge Records from Two Identical Access Databases
The process of merging relational data can get quite complicated, especially when you have numerous core-data tables as well as lookup tables. Danny Lesandrini explains the basic steps.
Dealing with MS SQL Tables that contain Duplicate Rows
Every so often, you might have to deal with tables that contain duplicate rows. Greg Larsen shares techniques for identifying and deleting those rows.
Getting Started With PostgreSQL
James Koopmann is on a quest for a database that won't cost him an arm and a leg. Join him as he looks into what PostgreSQL has to offer.
A Comparison of Oracle's DATE and TIMESTAMP Datatypes
If you want to store date and time information in Oracle, you really only have two different options for the column's datatype. Join James Koopmann as he examines the DATE and TIMESTAMP datatypes
Saved by the APAR or How to Use IBM's Self Help Web Support
IBM's Self Help Web Support is a great starting point for finding answers about DB2 database software. Marin Komadina shares tips on getting the most out of this resource.
Oh Danny Boy, The DBMS_PIPES Are Calling
Oracle pipes have been around for a long time but just how many use this powerful mechanism? James Koopmann explores this feature, reminding us all that it is not dead yet.
A Dashboard Approach to Storage Management
Storage Management Essentials: Drew Robb examines the factors in selecting a console to effectively manage increasingly complex and ever-growing networked storage environments.
XML and SQL 2000 (Part 6)
Marcin Policht continues his discussion on the Settings tab of the Virtual Directory Properties dialog box in IIS Virtual Directory Management for SQL Server tool, focusing on the next option on the Settings tab - "Allow XPath."
Create a PivotTable View in Access
Explore the creation of a PivotTable view for a custom query. Join author Bill Pearson in a step-by-step procedure for using the new PivotTable functionality in MS Access 2000, to create and navigate a PivotTable view.
Using Oracle's SYS_CONTEXT Function
The SYS_CONTEXT function is the recommended replacement method for obtaining information about users and environment variables instead of the deprecated USERENV function. This versatile function has been expanded in Oracle 9i to provide a plethora of information about the Oracle database and its processes as well as its users and sessions.
Attaching and Detaching Databases on SQL Server
Alexander Chigrik explains how to move a database, or database file to another server or disk using sp_detach_db and sp_attach_db system stored procedures to detach the database and then attach it again.
Understanding Oracle's Locally Managed Tablespaces
Locally Managed Tablespace (LMT), one of the key features in Oracle databases, has been available since Oracle 8i. Amar Padhi offers some scenarios of note for systems that are already using LMTs or planning to shift to LMTs.
Using Oracle Locks to Manages Data Concurrency and Consistency
Ever wonder what prevents the same data from being modified by two or more users at the same time? Learn how Oracle manages data concurrency and consistency by using Locks.
Returning Rows Through a Table Function in Oracle
James Koopmann introduces Oracle's Table Function. Learn how to call a table function within the FROM clause of a SQL statement and have it return a result set that mimics what we would normally expect from a traditional SQL SELECT statement.
Reporting Options for Analysis Services Cubes: Cognos PowerPlay
Discover another option for effectively reporting from MSSQL Server Analysis Services cubes. Author Bill Pearson continues his "triptych" of practical reporting solution tutorials with an Introduction to Cognos PowerPlay for Analysis Services.
Regaining Control of Storage Management
In part two of Storage Management Essentials, Drew Robb examines how management portals can help tame an organization's growing storage infrastructure and offers a look into the future of self-managing systems.
SQL Server Security Checklist
Neil Boyle shares a checklist of 24 common security gaps. Don't let your server fall under any of these lapses. You've been warned.
Using Microsoft SQL Server Constraints
Constraints are a built-in mechanism for enforcing data integrity. Learn about the new enhancements introduced in SQL Server 2000, when to use constraints, how to enable and disable constraints, and more!
XML and SQL 2000 (Part 4)
In the fourth article of the series dealing with XML related features of SQL Server 2000, Marcin Policht discusses the implications of various configuration options available when using IIS Virtual Directory Management for SQL Server.
Minimizing SQL Server Stored Procedure Recompiles
Andy Novick discusses the reasons that SQL Server 2000 decides to recompile a stored procedure and demonstrates techniques that can be used to minimize recompilation.
A Subtle Threat to Database Performance
Disk sorts are a near-silent performance vampire that can rob a database of good response times. Intelligently diagnosing the presence of disk sorts and then doing what you can to reduce or eliminate them altogether, you can remove this subtle threat to your database's performance.
Altering Oracle's SQL*Plus Help Facility
Everyone needs a little help now and then. James Koopmann finds new ways to provide information to users of SQL*Plus through the Help Facility.
So You Want to Use Oracle's SPFILE
Oracle's traditional method of storing parameters in the INIT.ORA file has given way to a more robust method-- the server parameter file (SPFILE). Learn what truly works in converting and maintaining your parameters without ever having to use an editor again.
Choosing SQL Server 2000 Data Types
Choosing an appropriate data type is very important, because the errors made in a table design can result in performance degradation. Learn about about built-in and user-defined data types and how SQL Server 2000 stores data on a data page.
Title Bar Clock Version 2
splay an updated clock in the title bar of your web site with this neat script! Easy to install, just cut, paste, and change the title!
Working with SQL Server Date/Time Variables
Greg Larsen continues his series on date/time data with a discussion on Searching for Particular Date Values and Ranges. Learn how to select records from a database table based on values in a DATETIME, or SMALLDATETIME column.
Getting ANSI About Joins
The new ANSI format for joins in Oracle 9i may appear alien at first, but they have the potential to become part of a DBA's arsenal for clarifying SQL and PL/SQL code. This article discusses the new join formats including NATIVE, CROSS, and OUTER and includes examples for review and comparison to the "traditional" join mechanisms.
MDX Member Functions: The Cousin () Function
Author Bill Pearson concludes the Member 'Family' Functions group of articles with an examination of the Cousin () Function.
Generate Scripts for SQL Server Objects
Many DBAs find a need for a script to generate object scripts for sql server objects. This short article shows how you can use Transact SQL to generate scripts.
DBA Call to Action: Make an Impact
A DBAs true value is not only to maintain database order but also to provide technical advantages to the company. Join James Koopmann as he breaks out of the cubical shell with a mission to 'make an impact.'
A Comparison of SQL Server 2000 with Access 2000
Alexander Chigrik compares SQL Server 2000 with Microsoft Access regarding price, platforms supported, features, and product limits.
XML and SQL 2000 (Part 2)
In the first article of the series, Marcin Policht covered ways of presenting relational data in XML format using the FOR XML clause of the T-SQL SELECT statement. In this installment he looks into the reverse process of inserting XML formatted data into SQL databases using OpenXML function.
Identifying Stored Procedure Recompilation Problems in SQL Server 2000
Andy Novick discusses the reasons that SQL Server 2000 decides to recompile a stored procedure and demonstrates the tools and techniques that you can use to monitor recompilation. Read the article, watch the movie!
Recovering Accidentally Lost Data Using Oracle's Flashback Query
Recent studies show that nearly 50% of system outages are due to human errors. Oracle 9i's new feature, Flashback Query allows the user to view an old image of data that has been modified and committed by the DML statement, without requiring any structural changes to the database.
IBM Utilities vs. Third Party Tools for DB2
In order to make the DBA's job quicker and more professional, many database administrators are using third party solutions. At the same time, IBM is working hard to improve their own tools. Marin Komadina lines them up, giving us the pros and cons to each.
Deleting Duplicate Rows in a MySQL Database
Your beautifully designed application is behaving oddly; you discover your database table contains duplicate rows... and now you have to delete them. Ian Gilfillan explains why there are duplicate rows, how to fix the cause of the duplicates and finally, how to delete them.
Reporting Options for Analysis Services Cubes: MS FrontPage 2002
Create reports directly from an OLAP cube with an Office PivotTable List: In this tutorial, we will design and build a PivotTable List within Microsoft FrontPage 2002, and focus upon "natural" PivotTable List strengths, including made-for-web robustness and control over user capabilities.
FalconStor Embraces Windows Server 2003
Storage software maker adds VSS and VDS support.
DDL Generation--Oracle's Answer to Save You Time and Money
The days of building your own DDL extraction utilities are almost gone; Oracle has given us a simple way to generate the DDL for objects defined in the database. Join James Koopmann as he delves into DBMS_METADATA.GET_DDL
Scheduled Task Reporting on SQL Server
Keeping track of scheduled tasks on a large server can be a bit difficult. Neil Boyle shares a script that generates a report of all the scheduled tasks on a server and offers an insight into the MSDB structure and the scheduling engine.
Oracle's DBMS_PROFILER: PL/SQL Performance Tuning
When simply inspecting the code isn't enough, and explain plan leaves you hanging, it's time to pull out Oracle's DBMS_Profiler. Learn how to identify performance bottlenecks, as well as where excess execution time is being spent in the code.
XML and SQL 2000 (Part 1)
SQL Server 2000 includes built-in support for XML. Even though some limitations of this implementation exist, conversion between relational and XML-formatted data is greatly simplified. Learn how to retrieve data as an XML document using the FOR XML clause of the SELECT statement.
A Server by Any Other Name
There are times in every DBAs life when it becomes necessary to change a server name. MAK provides a walk-through for this process with SQL Server 6.5, 7.0 and 2000.
Your standby server is at a remote site--you need to copy files directly to a disaster recovery site--you need FTP. Learn how to create an FTP batch file with a changing parameter file using MSSQL Server.
MDX Essentials: Member Functions: More "Family" Functions
Author Bill Pearson continues his MDX Essentials Series with an introduction to more Member "Family" Functions. In this lesson, we will continue our exploration of the "Family" functions with the .FirstChild, .LastChild, .FirstSibling and .LastSibling functions.
Materialized Views in Oracle
Learn how to make local copies of remotely located data & specify automatic refreshes.
Working with SQL Server Date/Time Variables
In this first article in a series, Greg Larsen will discuss various aspects of working with SQL Server date/time columns.
Database Impact of Collecting Statistics in Oracle
Oracle has given us a new parameter STATISTICS_LEVEL for collecting database statistics. James Koopmann takes a deeper look at what to expect from your database system when this parameter is set and statistics are being gathered.
A Comparison of SQL Server 2000 with DB2 v8.1
Alexander Chigrik compares SQL Server 2000 with DB2 Universal Database version 8.1 regarding price, performance, platforms supported, SQL dialects, and products limits.
The DB2 System Commands
DB2 is enhanced with full set of system commands that is extended with each new version (87 of them as of version 7.2 DB2 UDB EEE). Marin Komadina gives an overview of these system commands, with a detailed explanation of db2ptree, db2empfa, db2gov, and db2osconf .
SQL Server 2000 Active Directory Integration
SQL Server 2000 was designed to operate as an integral part of the Windows 2000 domain environment. Unfortunately, the new features that best exemplify this integration are rarely taken advantage of. Marcin Policht presents an overview of these features.
Using Object Databases in .NET
Traditional relational database management systems (RDBMS) don't lend themselves very well to object-oriented programming. Matt Culbreth of Intellinet Corporation introduces object databases and explains why they can be a valuable alternative to RDBMSs.
Get the init.ora parameter value using DBMS_UTILITY
Ajay Gursahani explains how to get the values for the parameters specified in 'init.ora' by using the GET_PARAMETER_VALUE procedure supplied with the DBMS_UTILITY package.
Synchronizing Production Data with a Test Database on SQL Server
Applications developed in a development environment need to be tested in a QA environment prior to being moved to production. The QA Group needs live data from the production server to simulate implemention on the test box. Learn how to automate this production-test data sync process.
Streamlining the Database Server Recovery Process on SQL Server
Are you tired of manually restoring each database on a new server when the original server has a melt down? Does the manual process seem slow, and prone to keystoke and mouse click errors? Would you like to have those restore scripts automatically built, so you only have to fire them off? Greg Larsen will show you one possible method for speeding up and reducing errors while performing a restore of all databases on a server.
Implications of Setting Oracle9iR2's Statistics Collection Level
Concerned about looking at statistics within Oracle? You should take a quick look at Oracles' new method of turning collections on or off.
MySQL date and time functions, Part 1
Too many applications are performing date calculations at the code level when the same can be done using built-in MySQL functions. Ian Gilfillan explians how to use a number of useful date and time functions in MySQL.
From "Try & Buy" to Production Database
Join Marin Komadina as he explains what the DBA needs to know regarding licensing, database packaging models, on a Sun Solaris platform.
Change data capture implementation in Oracle Data warehouses
In part-1 of the series, we discussed the overall view of Oracle Streams and the process of information sharing between databases. Part-2 of this article, presents a scenario to demonstrate a simple and 'straight-forward' configuration for Oracle Streams.
Scalability and High Availability of Microsoft SQL Server 2000 (Part 4)
Marcin Policht concludes his series on SQL Server 2000 scalability and high availability with a look at log shipping, a combination of both clustering and distributed partitioned views. Learn how to maintain a warm standby server, run servers on dissimilar hardware located in separate geographical locations, and resolve database scalability issues by offloading read only activities to a separate server.
The comparison of SQL Server 2000 with Oracle 9i
Alexander Chigrik compares SQL Server 2000 with Oracle 9i Database regarding price, performance, platforms supported, SQL dialects and products limits.
MDX Member Functions: The "Family" Functions
Author Bill Pearson continues his MDX Essentials Series with an introduction to the Member "Family" Functions. In this lesson, we discuss the concept of the "family" functions, and how they allow our queries to navigate cube structures. We then begin our exploration with the .Parent, .Children and Ancestor() functions.
Nightly Failed Jobs Report
Greg Larsen shares an alternative method of SQL Agent notification that allows you to have a single email report of all job failures.
Hold On There SYSter
Probably the most sacred user in all of Oracledom is the SYS user. Koopmann takes a quick look at how DBAs can take hold of this often misused account.
EXECUTE IMMEDIATE option for Dynamic SQL and PL/SQL
Amar Kumar Padhi shares usage tips and examples of EXECUTE IMMEDIATE, which replaced DBMS_SQL package in Oracle 8i and above.
Activating, Monitoring, and Tuning Automatic PGA Memory Management
Sizing the Program Global Area (PGA) is now one of many tasks made simpler under Oracle 9i. Learn how a database server utilizes the PGA, how the PGA can be automatically sized using PGA_AGGREGATE_TARGET initialization parameter, and how the PGA's size can be monitored and tuned using some new dynamic database views, now part of 9i.
iStor's iBlade on the Way
iStor is gearing up to launch its first product, a scalable iSCSI wire-speed network storage server blade for target-side IP storage.
Building Your Own Database
Are you a WISP tired of not knowing where your radio gear is? The answer is inventory management, and the ultimate way to control your list is to build it yourself.
Protecting Oracle Instance with Local Clustering
Follow along with Marin Komadina as he explains the ins and outs of Local Clustering with Sun Cluster software.
Scalability and High Availability of Microsoft SQL Server 2000 (Part 3)
Marcin Policht takes a look creating a scalable and highly available configuration by combining SQL Server 2000 clusters with Distributed Partitioned Views.
Using Open Source To Encrypt SQL Server Data
Combine a free open source C++ encryption library with SQL Server extended stored procedures to create a platform neutral, transparent encryption solution that resides at the database.
Explain Away Your Troubles
Determining when things change in a database is the first step in zeroing in on problems. One of the prime culprits to change is the very SQL that we attempt to run every day. James Koopmann shares his no-frills method to help sniff out those changes that occur.
Beware of Mixing Collations: Part 2
With SQL Server 2000 you are able to create databases or columns with a different collating setting than the server. Larsen shares his methods for successfully changing collation settings.
MySQL's Over-looked and Under-worked Slow Query Log
You say you've never heard of the Slow Query Log? That may be because it's de-activated by default. Following along with Ian Gilfillan, as he explores one of MySQL's less-used logs.
Introduction to MSSQL Server 2000 Analysis Services
Explore the drillthrough capabilities that debut in Microsoft SQL Server 2000 Analysis Services. Join Author Bill Pearson in a hands-on tutorial that examines the exciting new drillthrough capabilities, from both user and design perspectives, and discover how to make it possible for information consumers to see "what lies beneath" the OLAP cube's summary totals.
Linking SQL Server to Heterogeneous Systems
A brief look into linking SQL Server to such systems as Oracle, DB2, Sybase, etc., through Open Database connectivity.
Scalability and High Availability of Microsoft SQL Server 2000 (Part 2)
SQL Server 2000 is tightly integrated with the clustering software. The best example of this integration though is the setup process. Join Marcin Policht as he explains the process.
QUASSI Active/Active Cluster Server
It is a fact, that once we setup an Active/Passive cluster and install SQL server on a cluster server, the passive nodes will never be used unless it fails over. Learn how to make use of the unused resources and equipment, without spoiling the failover concept.
A Technology to Consider: Ultrawideband
About the time you think there are enough wireless LAN technologies and standards, another one appears. Discover how ultrawideband modulation will likely fit into the grand scheme of things with your 802.11 network.
Cooling Your Overloaded Database with New DB2 UDB EEE Partitions
Today's server hardware is so powerful that quite often we find stand-a-lone SMP (Symmetric Multiprocessing Machine) servers in different clustered and non-clustered configurations. Learn how to expand this type of system with additional hardware and DB2 UDB EEE database partitions.
MDX Essentials: MDX Members: Introducing Members and Member
Author Bill Pearson continues his MDX Essentials Series with an introduction to Members and Member Functions. This lesson sets the stage for the upcoming MDX Member Functions segment of the MDX Essentials series, and explores the .Members operator.
DBA Call to Action: The Information Chase
A key role of every DBA is to keep up-to-date on trends within the database industry. Journey along with Koopmann as he rids his desk of endless piles of documentation and articles while still staying on top of the latest news and trends.
Concerned About The (Oracle) Environment?
Jim Czuprynski offers suggestions on the perfect environment for evaluating software changes before they are run against the production Oracle database.
Change Data Capture Implementation in Oracle Data Warehouses - Part 1
In the first of this three part series, Nandeep gives an overview of Oracle Streams. Part 2 of the series will focus on a simple implementation of Oracle Streams followed by part 3, which will describe the Change Data Capture framework of Oracle.
Beware of Mixing Collation with SQL Server 2000 - Part
Gregory A Larsen explains a few things that need to be understood about mixing collations, now that it is possible to have multiple collating sequences on a single database server.
Using Fulltext Indexes in MySQL - Part 2, Boolean searches
Part 1 of this article looked at the fulltext index, and how to search on it using an ordinary MATCH() AGAINST(). Even more powerful, (although only available on the newer MySQL version 4), is the ability to do a boolean search. Part 2 of this article examines the possibilities.
SMTP Email Setup for SQL Server Cluster Server
In a Non-Cluster environment, it's a straightforward process to set up SQLMail. Now learn how to set SMTP up on an SQL Server Cluster Server.
Introduction to MSSQL Server 2000 Analysis Services
Author Bill Pearson returns in the second half of his Custom Cubes: Financial Reporting article. This lesson presents hands-on practice in building core cube structures from multiple data tables, then combining cubes containing specialized financial data into a central, custom Financial Reporting cube.
Scalability and high availability of Microsoft SQL Server 2000
Marcin Policht presents a number of solutions that provide high availability and load balancing of Microsoft SQL Server 2000, focusing on solutions available natively in the SQL Server 2000 and Windows 2000.
All About Passwords
Neil Boyle explains the mechanisms SQL Server uses to secure itself from unauthorized access, and discusses best practice for selecting and administering passwords.
Troubleshooting OLAP Problems
Alexander Chigrik shares 20 tips for solving problems with Analysis Services.
How to run a DTS VB package in the .NET framework
Many articles on the web describe how to run a Data Transformation Services (DTS) package saved in a structured storage file or as meta data in SQL Server. Now, learn how to run a DTS package that you've saved as Visual Basic, from within ASP.NET.
Killing the Oracle DBMS_JOB
If you have ever had a problem with a run-away job that did not want to end, or found that you needed to shut down the database only to find it was waiting for a job to complete, Koopmann can help. Learn how to manage those jobs that do not want to end.
SQL Server 2000 SP3 and xp_cmdshell Woes
If you have applied SQL Server 2000 SP3 only to find you have lost features that are dependant on xp_cmdshell extended stored procedure, the cause might be a change in the way that authorization for using xp_cmdshell works after the service pack is applied. Join Andrew Novick as he explains how to fix it.
Who needs DB2's Incremental Delta Backup?
Learn how to organize and perform a delta incremental database backup in DB2.
Removing Orphan Users from All databases on SQL Server
An orphan user is a user in a SQL Server database that is not associated with a SQL Server login. Learn how to identify which databases have orphan users, and how to remove the identified orphan users.
How Grid Computing Can Improve Database Performance
Nathan Segal interviews Benny Souder, Vice President of Distributed Database Development for Oracle, and Jeff Jones of IBM.
Oracle9i's Auto Segment Space Management Option
Follow along with James Koopmann and learn what you, the DBA, no longer have to manage thanks to a little feature from Oracle.
SQL Server Optimization Tips for Designing Tables
Alexander Chigrik offers 15 optimization tips for ensuring that you've constructed your SQL Server tables to perform in the most efficient manner possible.
Definer and Invoker Rights for stored routines in Oracle
Learn how to share procedures between users without sharing tables.
That Darn Alert Log
Until Oracle decides to give DBAs built-in direct access to the alert log, it will always be our responsibility to continually check and maintain it. Here is a simple solution that will help with this basic DBA task.
Using Flashback in Oracle 9i
Lost data? No problem. View or recover data with Oracle 9i's Flashback DBMS_FLASHBACK feature!
Solving SQL Server Connection Problems
Alexander Chigrik shares fifteen tips for solving SQL Server connection problems.
Leveraging the Information_schema
Checking for a particular string in every character column and user table in a database is not an enviable task. Fortunately - it is not necessary. Discover Information Schema, and learn how to get SQL Server to write the bulk of the SQL with a bit of Dynamic SQL.
SQL Server Stored Procedures Optimization Tips
In his latest article, Alexander Chigrik offers twelve useful tips for ensuring that you've constructed your SQL Server stored procedures to perform in the most efficient manner possible.
Database Performance and some Christmas Cheer
James Koopman demystifies some of the definitions surrounding the topic of database performance in his latest article. Whether a seasoned veteran or a beginner, the arena of database performance should not be confusing. Join Koopman as he puts a framework around what database performance truly is.
Introduction to Relational Databases
Large databases can easily get out of hand when badly designed, leading to poor performance, and resulting in the whole database needing to be rebuilt later. This article is a brief introduction to the topic of relational databases.
SQL Joins - Multi-table Queries
To harness the true power of relational databases it is vital to master queries using more than one table. This article will introduce you to database joins - queries using 2 or more tables.
Optimizing MySQL, Hardware and the Mysqld Variable
You have fine-tuned your indexes, and have optimized those queries to the bone. But still your MySQL database is crawling. It is time to look at tweaking the mysqld variables, what hardware improvements you can make, and how you can compile MySQL to run just that little bit faster.