SQL Server Development Techniques and Scripts

Donate to support site
Consultancy - System design advice, mentor staff, carry out bespoke work
Contact consultancy @ elmcrestprogramming.ltd.uk
Site Forum, email me or discuss here

My blog
Development Techniques and System Architecture
Products / Services
Sql Tutorial
Transact SQL
FTP using t-sql
Visual Basic, vbs
DotNet .net
Articles I have authored on other sites
Links - Useful sites about sql server and others

Books UK Books USA
Open University Books
finance UK

Development Techniques and System Architecture
System Interfaces System Interfaces, data transfer and process control
development sql server development
Release Control Administering SQL Server Release Control
Bad things Practices to avoid in sql server

Products / Services
Products Products and services

Sql Tutorial
SQL Tutorial SQL Tutorial

Transact SQL
sp_executeSQL setting variables from dynamic sql
Retrieve Tree Hierarchy Retrieve formatted tree structure
Get table row counts Retrieve the number of rows in each table in a database
Find gaps in sequence numbers Find gaps in sequence numbers
Create text file Creating a text file from a stored procedure
spFormatOutputBuffer Retrieve a sql server error message
Access Temp Tables Across SPs Create a temp table in one SP and access from another
sp_CreateDataLoadScript Create a data insert script from a table
sp_CreateDataLoadScript(remote) Create a data insert script from a table on a remote server
Create Script File Concatenate files to make single script
Move data using column definitions Move data from import table to production table using file format definition
Remove non-numeric characters Remove non-Numeric or non-alphameric characters from a string or field
Find non-alphameric characters Function to return all non-alphameric characters from a string in a table
Primary Key Columns Get all fields that are part of the primary key
Check if file exists find file - scripting object, xp_cmdshell, xp_fileexist
Cursors Advanced use of cursors in t-sql
Import Text Files Import and archive text files that arrive in a directory
f_GetEntryDelimiitted Get entries from csv string
fn_ParseCSVString Function to return a table from a delimitted (csv) string
bcp bcp using format file & quote delimitted strings
CSV String From Table Create delimitted string from table entries
BCP all tables BCP in and out data from all tables in a database
CrossTabs Crosstabs and pivot tables
Pivot_Statement Pivot_Statement
s_ProcessAllFilesInDir Process all files in a directory
UpdateText Inserting text data to a table in 8000 byte chunks
Replace Text Search and place strings in a text column in a table
Replace Text (2) As above - allows for replaced string in replacing tsring
Table name as variable Accessing a table from a name in a variable
send email Send emails asynchronously
Import and parse XML Import and parse XML document file using tsql
In csv string parameter Pass csv string parameter to an "in" statement
sp_ExecLinkedServer Run a query on a remote server (e.g. Oracle)
Calculate Average Duration Calculate average duration, date arithmetic
csv string from rows Create a separated list string from table row values
Date processing Format a date for input and ouput
Daily time between events Daily shift time, break time, number events
Daily Hours Worked Daily/weekly hours worked from start and end shift times
Regression Testing Compare before and after versions of tables after a system change
Maintain type2 dimension Update type 2 dimension from input data
Generate Test Table Data Populate a table with weighted random data entries
Generate insert statement Generate insert statement from table structure

Triggers (under development)
Triggers 1 A beginners guide
Triggers 2 Creating Audit Trails
Generate Trigger Generate a trigger from the table structure to log field updates by field name
Triggers Basic trigger information
Audit trail trigger Audit trail for all fields in a table from the table structure
Columns_updated() Trigger to detect columns updated - more than 32 fields
View audit field changes Return before and after values of rows in which fields have been changed on a particular day

Detecting installed sql server version Detecting installed sql server version
connection network library Set connection network library in registry
Spaceused for all tables Get space used (sp_spaceused) for all tables in a database
Backup a database T=SQL code to backup A database, Full, Log and differential
Backup all databases on a server Stored procedure to backup all databases on a server
sp_nrinfo Displaying blocking and connection info
sp_nrSpidByStatus Displaying connection commands by status
sp_nrLocks display commands executed and locks held by spids
Page Structure Data page structure and display using dbcc page
Recover corrupt database Recover data from a corupt database
Alter table - good or bad? Possible detrimental effects of using alter table
Large Tr Log File Shrinking a large transaction log file and stopping it from growing - part 1
s_TestRestore Automated test restore of latest backup with move of logical files
Add a self linked server Add the local server as a linked server under another name
Synchronise Directories Copy / synchronise directories / folders
Copy latest backup Copy most recent full backup to local server
Restore latest backup Restore most recent backup and move physical files
s_RestoreDatabase restore full backup and apply logs
Log Shipping Log Shipping
Move Databases Move physical location of database files - including system databases
Bad things Bad practices in sql server
Save Query Plans Save query plans and statements for all running spids
SQL Server 2008 New Functionality What's new in SQL Server 2008
Custom Keyboard Shortcuts Custom Keyboard Shortcuts, building dynamic sql

DMO transfer Transfer an object via sql-dmo
Scripting via DMO script all or a single Table, Store Procedure, Function to a file from t-sql
Script database objects from tsql using sql-dmo Script objects from all databases and save change history in SourceSafe
Now including remote server scripting, triggers, defaults and rules
Script data from tables Create a file of insert statements for the data from a table
Add to SourceSafe VB app to add/update scripts in SourceSafe
Script Table Structure .vbs script to script database structure from command line

FTP using t-sql
FTP Get Directory t-sql Get directory listing from FTP site
FTP Put File using t-sql Send a file to a FTP site
FTP Get File using t-sql Retrieve a file from a FTP site

sp_oacreate Load DTS Loading DTS package via sp_oacreate
Set DTS Run Time Values Configuring DTS package elements at run time
File exists Check if file exists in activex script
Execute Stored Procedure Execute Stored Procedure and get result in activex script
Save all DTS packages Save all dts packages on server to storage files
s_LoadPackageToServer Load a dts package from a structured storage file and save to sql server
Script properties Script properties of all DTS packages
SSIS Indirect Configuration SSIS - use reference to a configuration file
SSIS Configuration Files SSIS - use configuration files for sensitive data
SSIS Expressions SSIS - Expression examples
SSIS Configure Connection SSIS - Configure Connection for environment
SSIS Import to staging table SSIS - Import all files to staging tables and log process
SSIS Generic Importer SSIS - create package dynamically depending on file format
SSIS Generic Exporter 1 SSIS - create package dynamically depending on file format
SSIS Generic Exporter 3 SSIS export - Find configurable components
SSIS Generic Exporter 4 SSIS export - Find how to change columns
SSIS Generic Exporter 5 SSIS export - Prepare export package template XML
SSIS Generic Exporter 6 SSIS export - Stored procedure to output table export package XML
SSIS Generic Exporter 7 SSIS export - Package to control the export of table data

Visual Basic, vbs
Print To Word Using MSWord (MicroSoft Word) to print from VB
Add error handling Add error handling to every module in a VB project
VB6 Data Access Layer VB6 Data Access Layer
VB6 Call stored procedure Simple stored procedure call from visual basic
VB6 Get Database info OpenSchema calls, gets object info, SP parameters and resultset structure
VB6 SourceSafe files Loop through all files in a SourceSafe database
vbs split files VB Script. Read Write files, split by column data
vbs List archive contents VB Script. List contetcs of all files in archives in subfolders with attributes
vbs extract files from zip VB Script. Extract files from zip

DotNet .net
.net database access layer Call stored procedures from vb.net - OleDb, SQL

Call stored procedures from ASP Call stored procedures from ASP - inc file

Dos Batch Scripts
Concatenate, search, subroutines concatenate files, search for strings, recursive directoties
String manipulation substrings etc.
zip all files Dos Script to zip all files in a folder into zip files by filename

Read CSV Files Read CSV Files using PowerShell
Replace Text In Files Replace text in files PowerShell, command line
zip, unzip PowerShell - zip, unzip, compress, uncompress files

Microsoft Excel, Word
Excel_ConditionalSum Categorise amount values in excel transaction lists
Word - Draw Networks in Microsoft Word, Draw

Install MySQL Install MySQL for Microsoft Windows
Table Differences.html Find differences in data in two tables

sql server 2005 CTEs sql server 2005 common-table expressions (CTEs)
sql server Performance Diagnosing Batch System Performance Issues
sql server 2005 ssis exec sql server 2005 executing ssis packages
sql server csv files Creating csv files using bcp and stored procedures/
sql server 2005 SSIS FTP file rename SSIS move/rename a file on an FTP site
Partitioned Tables in SQL Server 2005 Partitioned Tables
Identity Columns Identity Columns

Links - Useful sites about sql server and others
sqlteam.com Friendly, active, sql server question and answer forum + articles
www.simple-talk.com SQL Server/.net articles
sqlservercentral.com Another sql server question and answer forum
replicationanswers.com Articles about replication by Paul Ibison
tek-tips.com Question and answer forum for all technologies
sswug.org Useful sql server site
sqldts.com Everything you need about DTS
mysql.com mysql
comparewebhosts.com finding a web host

Books Amazon.co.uk
cover cover cover