Tuesday, 25 April 2017
Task Pane 
Login
username:
password:
Remember Me
|Signup
Our Sponsors
Current Poll
SQL Server 2008
Are you ready for the upcoming SQL Server 2008 release?







[show results]
Category Information
SQL Server Stuff
SQL Server resources
Jump to:

Resources
8/11/2004 1:40:52 PM Author: philcart
Capture sp_who output to table
This script will capture the output from sp_who2 to a table. This allows you to,
a) perform trend analysis on server usage
b) easily track down heavy database users and processes for troubleshooting.
c) the script captures all output columns, but with a few simple modifications, you can specify which columns you view. eg: just the spid, dbname, DiskIO and CPUTime.
d) If you want to display activity in a web page, running the output to a table removes the overhead of executing sp_who on the ASP page. The sp_who2 procedure creates it's own temp table and does a lot of reformatting on the results. Reading from your own table is a very simple select statement.

NOTE: Before running this script ensure you have
1) Add your database and server name where indicated by << >>.
2) Make sure you have the server listed as a linked server not a remote server. By default each server should be its own linked server. This is important as the OPENQUERY statement only works for linked servers.

This script has three distinct parts,
Part 1 is a table to hold the output from sp_who2. The table is just a mirror of the sp_who2 output with an identity field added for the PK.

Part 2 is the stored procedure that updates the table. The stored procedure uses the OPENQUERY statement. This means that you don't need to worry about creating and dropping temp tables. It's also an easy way to query select columns in the stored procedure output. The use of 'SET FMTONLY OFF' was suggested by one of the guru's on www.sqlservercentral.com, can't recall who now. Also, before you get carried away, you can't use variables or parameters.

Part 3 is a scheduled job to run the procedure. Currently I have the job running every 7 mins from 2 mins past the hour. Running at this frequency means that the job will run at different times throughout the day. You can change the job to run more frequently, but be aware of the extra overhead on the server and adjust if necessary.
8/11/2004 4:13:27 PM Author: philcart
ASP page for checking database connectivity from Webserver
This page is used to check for database connectivity between the webserver and the database server. We use it in our monitoring package to make sure the database is accessible from the webserver.

It accepts two parameters via the query string. These are the Server and database name that you want to attempt to connect to. It then opens an ADO connection to the specified database and queries the sysobjects table of that database. It doesn't return anything from the table, it just uses it in the query as follows,
SELECT 'DATABASE CONNECTION OK' as chkMsg FROM dbo.sysobjects

Example Syntax:
http://webserver/check/chkdb.asp?srvr=DBServer&dbname=DBase

If the connection is successful, this will return a page with,
DBServer - DBase


DATABASE CONNECTION OK

To use, extract the files in the zip to a directory on your webserver.
Alter database connection string in global.asa to have the appropriate username and password to connect to your SQL Server.
Create a virtual directory on IIS pointing to the directory containing the extracted files.
Open web browser and enter URL with appropriate parameters as per example above.

As always, any questions or comments are welcome.
Phill
8/11/2004 4:19:35 PM Author: philcart
ASP page for checking Scheduled Job failures
This page is basically the same as the one for checking database connectivity. The big difference is that it calls a stored procedure to check for Scheduled job failures.

As with the database connectivity page the query string for the URL should contain two parameters. These are the Server and database name where the stored procedure is located. There is also an optional third parameter which the stored procedure accepts. This is the number of minutes for the failure window. The default value for the third parameter is 10 minutes, which will check for any failures within the last ten minutes.

To use, extract the files in the zip to a directory on a machine running IIS.
Connect to the SQL Server you want to check and run the Stored Procedure Creation script.
Alter database connection string in global.asa to have the appropriate username and password to connect to your SQL Server.
Create a virtual directory on IIS pointing to the directory containing the extracted files.
Open web browser and enter URL with appropriate parameters.

Example Syntax:
http://webserver/checker/chkjobs.asp?srvr=DBServer&dbname=DBase&mins=20

As always, any questions or comments are welcome.
Phill
18/02/2005 5:49:23 PM Author: Rowan
ppts for test
ppts for test
28/02/2005 12:43:25 PM Author: ace
SQL Server 2000 Object Naming Convention
Hi all,

Thought I might post this for the community. Naming Conventions are obviously quite important. Here is an example of a Naming Convention I used at one site.

Hope you find it useful!

Regards,

Victor
7/03/2005 1:06:58 PM Author: maryb
DTS converting Sybase to SQL Server
These files contain a VB6 project that builds a DTS package and script files to convert Sybase databases to SQL Server. It is provided with no guarantees and should be used as a guide only.
7/03/2005 1:08:10 PM Author: maryb
SQL Injection Attacks
This demo describes some SQL Injection attacks and should be used to help you avaoid them in your asp code.
7/03/2005 1:09:33 PM Author: maryb
Managing snapshots over inconsistent WAN connections
This document describes how to manage transactional replication (especially snapshots) over inconsistent WAN connections. It comes with no guarantees and should be used as a guide only.
23/03/2005 11:45:30 AM Author: Greg_Linwood
Collecting multi-resultset output from DBCC SHOW_STATISTICS using DTS
[from my blog]

Someone asked how to collect the multi-resultset output from DBCC SHOW_STATISTICS in the SQL MVP forum today. This is a tricky thing to do in TSQL because there are no features on TSQL to catch multi-resultset output from stored procs, DBCC etc.

You can use the INSERT / EXEC sp.. technique to collect output from procs that return ONE resultset, but that doesn't help if the proc returns MANY resultsets & you need info from the [n]th resultset.

You can use the WITH TABLERESULTS option with some DBCC commands, but again, this is no help if the DBCC statement returns multiple resultsets (such as DBCC SHOW_STATISTICS) & you need to collect all of the output.

I'd previously written component code to do something like this in VB & C# in the past, but it struck me when I saw this question today that another option would be to use DTS's ActiveX script object.

SO I flung together a few scripts & offered them up to the MVP forum as an option. I'm sure those guys will improve on these scripts, but I've loaded them up to the Resources section in case they're of any use to anyone.

These have been put together with DBCC SHOW_STATISTICS hard-coded, but it would only take a little more work to make the solution a little more generic. I'm just a little lazy right now. (c:
7/04/2005 10:50:10 AM Author: stevea
AS2K versus AS2K5
A pdf that I foudn to be quite good at describing some key differences betwix AS2K and AS2K5. Mosha (www.mosha.com) gave it a bagging re: the MDX coverage in his blog but he's covering that off by giving his own series for MDX http://sqljunkies.com/WebLog/mosha/archive/2005/02/16/7852.aspx
7/04/2005 12:00:39 PM Author: latenightdba
Extract DTS packages
a small vbs script to extract all DTS packages (latest version) from defined sql server to .dts files to allow easy migration to another SQL server.
22/04/2005 1:02:03 PM Author: Igor2004
User-Defined string Functions Transact-SQL
Ladies and Gentlemen,
I am pleased to offer, free of charge, the following string functions Transact-SQL:
AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character.
RAT(): Returns the numeric position of the last (rightmost) occurrence of a character string within another character string.
OCCURS(), OCCURS2(): Returns the number of times a character expression occurs within another character expression (OCCURS() - including overlaps),
(OCCURS2() - excluding overlaps).
PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side.
PADR(): Returns a string from an expression, padded with spaces or characters to a specified length on the right side.
PADC(): Returns a string from an expression, padded with spaces or characters to a specified length on the both sides.
CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters.
STRFILTER(): Removes all characters from a string except those specified.
GETWORDCOUNT():Counts the words in a string.
GETWORDNUM(): Returns a specified word from a string.
GETNUMWORD(): Returns the index position of a word in a string.
GETALLWORDS(): Inserts the words from a string into the table.

More than 23000 people have already downloaded my functions. I hope you will find them useful as well.
With the best regards, Igor Nikiforov.
5/05/2005 4:29:32 PM Author: philcart
Generate list of DTS connection properties
This little script will create a CSV file containing connection properties for all DTS packages.

You'll need to run it on a server or workstation that has SQL Server, or the DTS runtimes, installed.

Download the file and save it with a vbs extension. Fill in your server name and output file at the start of the script.

Run it from a command prompt using either cscript or wscript.
1/09/2005 1:41:36 PM Author: angrykoala
Sydney - Aug 2005 - Neil Jacobson - Scripting with Metadata in SQL 2000
Scripting with Metadata for Common Tasks
Putting Information_schema views and system tables to work.

©2005 Neil Jacobson

This script is supplied as is. No warranty is applicable or implied.

Any use of this script must include this header / copyright information intact.

It is recommended that all code be thoroughly tested to ensure suitability and must not be used in a
Production environment without following your organisation's change control and testing procedures.

This script was designed to run on a SQL Server 2000 environment using sort order 52.
The script may not necessarily produce the expected results if object names contain non alphanumeric characters e.g. spaces or '-'

When using SQL Server Query Analyser the results should be set to text output with 8000 characters per column.

Updated 10 Nov 2005 to include new 2005 features.


10/02/2006 4:01:54 AM Author: Igor2004
User-Defined string Functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net)
User-Defined string Functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net)
Ladies and Gentlemen,
I am pleased to offer, free of charge, the following string functions MS SQL Server 2005 Transact-SQL SQLCLR (VB. Net, C#.Net, C++. Net):
AT(): Returns the beginning numeric position of the nth occurrence of a character expression within another character expression, counting from the leftmost character.
OCCURS(): Returns the number of times a character expression occurs within another character expression (including overlaps).
PADL(): Returns a string from an expression, padded with spaces or characters to a specified length on the left side.
CHRTRAN(): Replaces each character in a character expression that matches a character in a second character expression with the corresponding character in a third character expression.
STRTRAN(): Searches a character expression for occurrences of a second character expression, and then replaces each occurrence with a third character expression. Unlike a built-in function Replace, STRTRAN has three additional parameters.
STRFILTER(): Removes all characters from a string except those specified.
GETWORDCOUNT(): Counts the words in a string.
GETWORDNUM(): Returns a specified word from a string.
GETALLWORDS(): Inserts the words from a string into the table.
PROPER(): Returns from a character expression a string capitalized as appropriate for proper names.
RCHARINDEX(): Similar to the Transact-SQL function Charindex, with a Right search.
AT, PADL, PADR, CHRTRAN, PROPER: Similar to the Oracle functions PL/SQL INSTR, LPAD, RPAD, TRANSLATE, INITCAP.
Plus, there are versions for MS SQL SERVER, SYBASE ASA, DB2, Oracle.
More than 10000 people have already downloaded my functions. I hope you will find them useful as well.
9/03/2006 2:47:31 PM Author: robf
Adelaide Mar 2006 - SMO by Peter Ward
All the slides, codes, etc.
1/09/2006 12:07:47 PM Author: nickward
SQL Server Service Broker Scripts
These scripts are a demonstration environment for implementing Service Broker as shown during TechEd Australia 2006.
1/09/2006 12:12:44 PM Author: nickward
Building Reliable Distributed Applications with SQL Server Service Broker
This is the presentation delivered during TechEd Australia 2006 including architecture, implementation, scenarios and resources. Note: the demonstration SQL scripts are also available on this site.
23/07/2007 7:23:09 AM Author: EasternMining1
Using SQL Server OLAP to manage an Oracle RAC cluster
This is a classic OLAP tool that every site could benefit from. It puts Windows Performance Monitor information into a cube and makes it available close to real time. To see the tool in action, or for more Business Inteligence demonstrations goto http://EasternMining.com.au/demonstrations
28/08/2008 4:15:42 PM Author: angrykoala
SQL Server Enterprise Architectural Summit (SEAS) 2008 Abstract
Full details including schedule and bios for SEAS 2008.
http://www.microsoft.com.au/teched/pricing_add_ons.aspx

The SQL Server Enterprise Architect Summit is a 2-day intensive course designed for experienced SQL DBA’s.

The Microsoft Customer Advisory Team (CAT) are part of the SQL Development team in Redmond. They assist with the deployments and tuning of some of the largest deployments of SQL Server 100+TB, most complex implementations and take those lessons back to become enhancements for future products. Benefit from hearing first hand from experts in the Microsoft Customer Advisory team as they deliver best practices, real world experience, design and deployment of SQL Server enterprise solutions/systems on the SQL 2005 platform and 2008 platforms.

Need to convince the boss - show him this document