SQL Server Development Techniques and Scripts



Consultancy - System design advice, mentor staff, carry out bespoke work
Contact consultancy @ elmcrestprogramming.ltd.uk


My blog
Products / Services
Sql Tutorial
Transact SQL
Triggers
Administration
DMO
FTP using t-sql
DTS
Visual Basic
DotNet .net
ASP
Articles I have authored on other sites
Links - Useful sites about sql server and others

finance UK
Purchase Hardware/Software UK
Books UK Books USA
Films
Guide to creating an internet forum



Development Techniques
development sql server development
Release Control Administering SQL Server Release Control
Bad things Practices to avoid in sql server
Books UK SQL Books Amazon.co.uk
Books USA SQL Books Amazon.com
Films OK nothing to do with IT but...

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
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)
csv string from rows Create a separated list string from table row values

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

Administration
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

DMO
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

DTS
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

Visual Basic
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

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

ASP
Call stored procedures from ASP concatenate files, search for strings, recursive directoties

Dos Batch Scripts
Concatenate, search, subroutines Call stored procedures from ASP - inc file

Articles
sql server 2005 CTEs sql server 2005 common-table expressions (CTEs)
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
dbforums.com Another question and answer forum for all databases
sql-server-performance.com Useful articles on sql server
sswug.org Useful sql server site
sqldts.com Everything you need about DTS
selfpromotion.com How to get a web site on search engines
able-consulting.com Connection string examples
mysql.com mysql manual
htmlreference.com html reference
comparewebhosts.com finding a web host
easyspace.com Cheap web host
webhost4life.com Cheap web host providing sql server
snitz.com Bulletin Board (forum) software


Books Amazon.co.uk
cover cover cover
Books Amazon.com


Old Web Site

Other Stuff
finance UK
Purchase Hardware/Software UK