DatabaseJournal.com Staff

Connecting the Front and Back Ends Having established a front end and a DBMS back end, we then need to connect them through some type of software. The connection functions include: defining which datastore to use, how to transfer SQL statements and results, and a host of settings that control the above processes. For example, […]

sp_hywho2 is a modified version of the sp_hywho script posted on 6/8/01 that will work on SQL Server 2000. This is an enhanced version of sp_who that shows more detail process information and also checks for and displays blocking information. It will also accept a database as a parameter to view information for only a […]

This Stored Procedure allows users to pass in multiple values as a parameter to it. The user can pass in as many values as he or she wants. The only restriction is the length of the parameter declared. Also, the values need to be separated by a comma. The script was created for SQL Server […]

This procedure will send mail using the CDONTS object installed by Microsoft SMTP service. The Microsoft SMTP service comes with IIS, but can be installed and run independently of IIS. The procedure handles multiple attachments as well as subject, cc, bcc, HTMLFormat, and priority options. This is tested only SQL Server 7 only. Use of […]

Here’s a UDF function for SQL 2000 that will save you datetime headaches when passing datetime variables into your SQL Procedures. The function converts any datetime field to ensure that you don’t lose data for the last day to be included. Some SQL Procedures use ‘between’ when quering datetime fields. This may cause you to […]

Procedural Versus Declarative Languages We mentioned at the beginning of the chapter, that SQL is not a procedural language. Before we move on to look at how we can actually implement SQL, we’ll first see exactly what we mean by a declarative rather than procedural language. Many readers will have experience in languages like Visual […]

Here’s an SQL Script created to aid an archive process using DTS in SQL 7.0. The script works with both SQL Server 7.x & SQL Server 2000. It has been designed with a specific function: Spreadsheets containing source data are copied to a server, ready for import into a datawarehouse. The files are then moved, […]

Here’s a UDF function for SQL 2000 that will save you datetime headaches when passing datetime variables into your SQL Procedures. The function converts any datetime field to ensure that you don’t lose data for the last day to be included. Here’s a simple and straightforward stored procedure that searches a column in a table […]

This script will return all databases and their corresponding table information on a SQL server. Constraints or expansion of information returned can be added to the respective select statements. This script was written and tested on SQL server 2000, SP1, but should also work on SQL server 7.0. Author: Allen Duet Download Script: dbtablelisting.txt Disclaimer: […]

Like the sp_xfrdata_v2.0.sql script below, this SQL 7.0 script will generate insert-statements for rows in a table and is useful for quick transfers between databases or for filling tables with test or standard info. Features: handles identity columns (using identity_insert) skips timestamp columns handles quotes in strings handles text fields to a maximum of 1000 […]

Book overview Structured Query Language is the industry standard for querying data held in relational databases. SQL can be used to create and alter the structure of databases, add new data, and access or modify existing data. Beginning SQL Programming begins by looking at what SQL is, then moves on to teach you the fundamentals […]

Proc_Parser is a stored procedure for SQL Server 6.5, 7.0, and 2000 that separates the parameters of a string list and returns the data in a table, i.e.: EXEC PROC_PARSER ‘ X=10 Y=35 Z=ABC W=”TESTE=2″ ‘ Will return: NAME VALUE ——— ———— X 10 Y 35 Z ABC W “TESTE=2” Author: Mauro C. Pichiliani Download […]