With XML being used in so many application and data exchange capacities, extracting XML from SQL Server should not be a challenge. Yet, some organizations are building complex applications to do so and overlooking some of the native SQL Server features.
Changing the name of an table once an application has been deployed has traditionally been a difficult task in SQL Server 2000. The typical solution in SQL Server 2000 for referencing a different object was to use a View. A second option was to use the
When restoring a database to a different server there is often the problem of matching up logins and users. The reason for this is that login information is stored in the master database and user information is stored in the specific database you are wor
Extended stored procedures in SQL Server 2000 were the only option to extend the capabilities of the relational engine such as access to the file system or to send and receive emails directly in SQL Server. Securing these objects is not straight forward
In this article we look at how you can automatically backup all of your SQL Server databases with a couple of T-SQL commands.
In this tip we look at a SQL Server script that we can use to lookup and replace words in a table.
There is often the need to compare both data and database structures from two databases either on the same server or on different servers. Most text editors have a built-in process to allow you to do a diff and identify any differences between the two fi
Among many other changes between SQL Server 2000 to 2005, the index creation code has some subtle changes that are important to know and handle appropriately in scripts. It is important to be aware of these changes due to the beneficial performance gains
For better or for worse, the most common tool for performing database maintenance in SQL Server 2000 is the Database Maintenance Plan Wizard, as opposed to the maintenance T-SQL commands. The Database Maintenance Plan Wizard's point and click interface e
One of the simplest security best practices is changing passwords on a regular basis. For some organizations that could be quarterly while others may have a more aggressive policy. Regardless of the policy, the issue remains the same, how can I enforce
One issue with creating backups is that the LSNs (log sequence numbers) get stored in the backup files and these LSNs are referenced when you do a restore of your database. With SQL Server 2005 a new backup option COPY_ONLY has been added that allows you
In this tip we look at an easy way to build DDL for SQL Server tables using an Excel template to generate the T-SQL for the table structure.
Have you had an issue trying to find out how to assign rights for some of the new applications (SQL Server Integration Services, Full Text, OLAP, Express, Reporting Services, etc.) in SQL Server 2005? If so, you are not alone. With SQL Server 2005, some
SQL Server 2000 and 2005 have the capability to use multiple instances of the database engine on a single server. But is going down this path really worth it? In some situations the answer should be a resounding 'yes'. So, how are they setup? When wou
The Max Degree of Parallelism or MAXDOP is a configuration indicating how the SQL Server optimizer will use the CPUs. This is a server wide configuration that by default uses all of the CPUs to have the available portions of the query executed in paralle
Sometimes there is a need to get record counts from every table in your database. One way of doing this is to do a SELECT count(*) on all of your tables, but this could create a lot of overhead especially for large databases and large tables. If you don'
Testing database applications for many organizations is a challenging task that is difficult to setup, automate and validate. Luckily, more time is getting allocated into development projects to conduct more rigorous testing. In this tip we will outline
Sometimes querying data is not that simple and there may be the need to create temporary tables or views to predefine how the data should look prior to its final output. Unfortunately there are problems with both of these approaches if you are trying to
One mantra across the industry is the need to fine tune security for all infrastructure components. In SQL Server 2005, Microsoft responded with an almost overwhelming number of granular security rights. One of these changes is with SQL Server Agent whi
SQL Server server related information is valuable for administrative scripts and very important when troubleshooting general configuration issues. In the transition from SQL Server 2000 to 2005, some of the objects and commands that we have grown to rely
Capturing performance monitor counters is of great value to understand how SQL Server is behaving. Without this data it is difficult to determine where the performance issues are occurring. Capturing the metrics has been traditionally from Performance Mo
Learn how to use the SQL INDEXPROPERTY function to return various meta data about SQL Server indexes in a database.
Accessing SQL Server system information is necessary for administrative scripts and very important when troubleshooting particular issues. Unfortunately, in the transition from SQL Server 2000 to 2005, some of the objects that we have grown to rely on ar
One thing that will you most certainly face at some time as a DBA is dealing with deadlocks. A deadlock occurs when two processes are trying to update the same record or set of records, but the processing is done in a different order and therefore SQL S