DatabaseJournal.com Staff

These two TSQL scripts provide an excellent tool for shrinking those pesky log files. The stored procedures come with instructions for creating a management database to take care of all your log shrinking needs from one routine runnable as a scheduled job. sp_shrinklog can also be run stand-alone to shrink individual database logs. Tested in […]

Datawarehouse environments face the challenge of exchanging, integrating and consolidating huge amounts of data over many systems to provide a unified information base for Enterprise Intelligence. This process in general is refererred to as ETL ( Extract, Transform and Load ). Extract process is very often involved with the extraction of the data from many […]

Here’s a very useful stored procedure for dropping a column from a table. It first drops all the constraints for the column and then drops the column. Caution is required while using the SP as it drops the constraints including the Foreign keys without any warning. The script has been tested on SQL 7.0. The […]

Reverse-engineer existing SQL Server 7 Maintenance Plans. This Stored Procedure will script out existing jobs that are part of a maintenance plan, then add SQL to insert necessary data for the maintenance plan tables. It seems to handle multi-server jobs OK as well. The output is SQL which may be cut and pasted to a […]

I’ve found that many ASP developers don’t like repetitive tasks, yet many developers do the following: on each page using database connectivity, they write the following code: Click here for code example 1. Now why write all of this? First off, is a Session variable really needed here? No. Should we have to retype all […]

Data Warehouse with Multiple SQL Requesters Last, let us look at an example that is probably realistic for most businesses. We have one datastore for the enterprise that consists of several data servers and a mainframe, collectively called a Data Warehouse or a Distributed Data Processing system. That conglomerate of a datastore receives SQL requests […]

While migrating or restoring a user database from one server to another where multiple user databases and numerous database user logins exist on the servers, the DBA usually faces the problem of how to transfer only particular user logins and passwords for the database over to the destination server. Another complication arises when the DBA […]

This procedure raises an error if the free space on a drive that contains the root-level directory name of MSSQL7 has less than the passed-in threshhold of free space. If you set user databases to grow automatically and without limit, you need to worry about running out of disk space entirely. By scheduling this job […]

How to install a Microsoft. Exchange(tm) Client so SQL Server can send and receive mail: Created by Mike Culver — see his web site for additional info on SQL 6.5 and 7.0: http://www.realworldinfo.com/techinfo/ In User Manager for domains, connect to the local account database on the NT server where SQL Server is installed. Create an […]

This procedure is meant to be run as part of a backup transaction job created as a component of a Database Maintenance Plan. It reads the most recent log file created by the maintenance plan and searches for a “nonlogged” error. This error occurs when a non-logged operation (such as a select into or a […]

This script returns the User options set for the current session, providing a more descriptive result of the @@Options variable. It has been tested on SQL 7.0. The usage is :- Exec usp_GetDBOptions Author: Sumit Dhingra Download Script: Options.txt Disclaimer: We hope that the information on these script pages is valuable to you. Your use […]

Here’s a short and simple SQL Query that generates Serial numbers. The query was tested on SQL 7.0. Exec usp_GetDBOptions Author: Sumit Dhingra Download Script: Serial.txt Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. […]