SQL Server 2008 and 2008 R2 Integration Services - Consuming Web Services Using Script Task

The omnipresent nature of Web-based technologies expanded the concept of distributed environments with the ability to utilize services hosted on remote servers accessible via standard Internet protocols. This capability can be easily leveraged in SQL Server 2008 R2 Integration Services by employing Web Services Task, whose characteristics have already been covered in detail on this forum. However, in some cases, it might be required to reference Web Services directly from within a Script Task code. We will describe how such a need can be accommodated in this article.


http://www.databasejournal.com/features/mssql/sql-server-2008-web-services-script-task.html

Use ACE Drivers and PowerShell to Talk to Access and Excel

Quit using deprecated JET drivers to talk to Microsoft Access and Excel. Learn how to use Windows PowerShell and ACE Drivers...


http://blogs.technet.com/b/heyscriptingguy/archive/2011/05/04/use-ace-drivers-and-powershell-to-talk-to-access-and-excel.aspx

Obtaining Identity Column Values in SQL Server

When you enter a new record into a table that contains an identity column, the identity value will be set with the next available identity value. Lots of times your database design requires you to obtain the value that was used to set the identity column. The identity value will be used to populate other tables that need to have a foreign key relationship with the inserted record. Therefore you need to be able to return the identity column value of the newly inserted record.


http://www.databasejournal.com/features/mssql/article.php/3931466/Obtaining-Identity-Column-Values-in-SQL-Server.htm

SQL Server Foreign Key Update and Delete Rules

Foreign key constraints are an integral part of SQL Server database design. These are used to maintain integrity among related data in different tables. While implementing update and delete operations on values in the parent table (referenced table with primary key) we have to consider the impact on related values in the child table. SQL Server provides different rules for managing the effect of updates and deletes on child table values. How can these rules be used effectively without threatening the relational integrity?


http://www.mssqltips.com/tip.asp?tip=2365

How Old Are Your Database Statistics?

Unlike fine wine, database statistics do not improve with age. I recently helped out with a client who was having issues with poor query performance on a SQL Server 2005 instance on very good hardware. After having them run my standard SQL Server 2005 Diagnostic Information queries, nothing glaringly obvious was jumping out at me. Sure, there were a couple of missing indexes that needed to be added, but overall query performance was still pretty bad. The server as a whole was not under obvious CPU, memory, or IO pressure.


http://www.sqlservercentral.com/blogs/glennberry/archive/2010/8/30/how-old-are-your-database-statistics_3F00_.aspx

Geocode Addresses in T-SQL

This article shows how you can "geocode" street address information natively in T-SQL with MS SQL 2005, and as a byproduct, how you can validate, normalize, and parse address information.

Note that this approach will work in SQL 2008 as well, though SQL 2008 provides geospatial types and functions that could be used in lieu of the numeric data type used here.

Geocoding is the process of converting a geographical address into latitude and longitude (GPS coordinates). Geocoding address information has a number of benefits:

• Means to interoperate with web-based mapping services
• Means to interoperate with GPS devices
• Makes it possible to calculate distance between addresses
• Makes geospatial analysis possible (i.e. identifying geographically similar addresses, etc.
• Compact way to represent a geographical location
• And more...

Map IP Address to a Geographical Location

On the Internet there is the concept of web site analytics that helps track all the visitors' activities and usage patterns. One of the dimensions to track is the geographical information of the visitors, which can be obtained by using the IP address information that is collected when a user lands on a Website. In this article, I will describe a simple process that enables your reporting system to display the geographical information of the visitors.

Reporting Services Disaster Recovery

Many organizations now rely on their reporting solutions for day-to-day business as much as the underlying OLTP systems. For some organizations, being able to report on data in real time can be as important as the availability of their underlying OLTP systems. So like any important database solution, you need to plan on how to recover your reporting solution quickly and efficiently, to keep your business running smoothly. For most medium and small companies, that will mean using the tools available to you with SQL Standard Edition.

In this article I will show you how to setup a Reporting Services solution that will allow you to quickly switch from one server to another, should your primary report server / report server database fail or should you need to switch your service to allow you to carry out server maintenance tasks, reducing downtime of the live system.

Laying out SQL Code

It is important to ensure that SQL code is laid out the best way for the team that has to use and maintain it. Before you work out how to enforce a standard, one has to work out what that standard should be for the application. So do you dive into detail or create an overall logic to the way it is done?

http://www.simple-talk.com/sql/t-sql-programming/laying-out-sql-code/

Database Indexing Development Lifecycle...Say What?

This is the first article of a series where I’ll be exploring Indexing Guidelines. Having appropriate indexes on your database is critical in making sure your application is retrieving records quickly. However, having too many indexes can also slow down your application. In this series of articles, I hope to help you understand how to determine what columns you should consider for indexes, and how those columns should be used in indexes to optimize performance. In this first article, I will be discussing the indexing development lifecycle.

Using SSIS to zip files and email the zipped files

This article makes use of global variables throughout and shows how they can be used in SSIS, you can adapt these to make a package that can be made as flexible as you want. The package described here zips all files in a folder to a single archive zip file.

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64028/

Map IP Address to a Geographical Location

On the Internet there is the concept of web site analytics that helps track all the visitors' activities and usage patterns. One of the dimensions to track is the geographical information of the visitors, which can be obtained by using the IP address information that is collected when a user lands on a Website. In this article, I will describe a simple process that enables your reporting system to display the geographical information of the visitors.

Use Backup/Restore to Minimize Upgrade Downtimes

As a SQL Server professional, at some point in your career, you will need to upgrade between versions of SQL Server, or move a database from an older server onto a newer one. There are quite a few different ways to go about doing this, the most common being; Detach/Copy/Attach and Backup/Restore. When downtime is acceptable, either of these methods can get the job done, the only caveat being that if you are performing a upgrade to a newer version of SQL Server and you decide to use Detach/Copy/Attach to upgrade the databases that you still should take a backup of the database before moving it so that you have a point to fall back top. Once you attach the database files to the newer version they will be upgraded internally and will no longer be able to be used on the older version.

If time is of the essence during the migration, and downtime must be minimized, the best approach will depend on the size of the database being upgraded. For a database that is under 4GB in size, it may be acceptable to still do a Detach/Copy/Attach move of the database, but for a database that is 40GB in size, the time it takes to copy the files to the newer server could exceed the allowable downtime for the system. If the database is 400GB, it will most certainly take to long to move the database by Detach/Copy/Attach. In this case the best path to migration/upgrade is to work with Backup/Restore.

So how do you go about doing this?

Calling a Web Service from within SQL Server

More and more shops are implementing web services. Doing this provides an architecture that allows applications to consume services to retrieve data. These services could be within your own organization or from a business partner. One of the problems you might run into when building applications that consume web services is how you can use web services data within a SQL Server instance. One of the reasons you might want to do this is so you can join a record set that is returned from a web service with one of your SQL Server tables. This can easily be done within an application, but how do you do this within a stored procedure that only runs within the context of SQL Server. In this article I will discuss one approach for doing this.

Backup Monitoring and Reporting

Database recovery is a core task of database administration and is defined as executing the tasks necessary to backup, save, retrieve and restore databases from hardware, software and user error. Your database recovery procedures should be documented and regularly tested by restoring either a full or random sample of backups. A key part of database backups is monitoring whether backups are in fact occurring. A common mistake in backup monitoring is only checking for backup success or failure when there are in fact three possible outcomes; success, failure and nothing. What is nothing? Nothing is what happens when a database or entire SQL instance for that matter is not configured for backups or the SQL Agent job or Windows services which are often a part of 3rd tools are in a hung state. In order to achieve effective backup monitoring you need to look at the non-event of a lack of backup rather than failed backup messages. Regardless of how a database is backed up, whether through SQL Server native backups or any one of a number of commercial backup software a row is written to the msdb.baclkupset table. Knowing this information, you can create daily monitoring and backup reports.

Creating a recycle bin for SQL Server 2005\2008

Recently while being shown around Oracle 10G (yes I said the 'O' word) I noticed that this product has a recycle bin. This recycle bin which stores only dropped tables falls under the Oracle flashback technology umbrella.

I was pretty sure I could do the same in SQL server using DDL triggers and schemas and prevent accidental drops of other objects as well. This article is the result of that challenge.

In a nutshell the SQL Server recycle bin is a combination of two schemas which act as bins (recycle bin and trash can) and a DDL trigger which determines which bin to place the dropped object (which is not dropped but renamed). A stored procedure (sp_undrop) is used to revert the object to its original name and schema.

Comparison of Dates in SQL

Dealing with dates has always been a problem with SQL Server queries. Small things lead to critical errors, which are very difficult to detect. One of the common usages of dates is in 'where' clause of the query to filter out the records based on one or more datetime fields. Keeping few things in mind while writing such SQL queries will help you to write an effective query which does not miss any desired records. This article gives you an idea about writing such queries involving date comparisons.

Collecting performance counters and using SQL Server to analyze the data

The following will explain how to select counters, how to collect data, how to load the data to SQL Server and how to query the data that has been saved.

Database level permissions for SQL Server 2005 and 2008

SQL Server 2005 introduced a new concept to SQL Server security and permissions: securables. Securables are anything within SQL Server that can have a permission assigned. One such securable is the database.

These are the list of database-level permissions.

Exploring SQL Server 2008’s Filtered Indexes

One of the most interesting features in SQL Server 2008 is filtered indexes. Let’s start with a quick explanation and then dig into the details and results.

A filtered index is simply an index with a WHERE clause. For example, if you have a table where a date is NULL in 90% of the records but you need to be able to find only records with a non-NULL date, a filtered index will have only the 10% that are not NULL.

It can be very useful for saving space, saving index maintenance costs, and making queries run faster. However, it is important to understand under whatcircumstances it will make your query faster.