SQL Server Development Techniques and Scripts

Google


Consultancy - I am happy to undertake system reviews, provide system design advice, mentor staff and carry out bespoke work
If you wish to discuss work or receive a quote please contact me at consultancy @ elmcrestprogramming.ltd.uk


Development Techniques
Products / Services
Sql Tutorial
Transact SQL
Triggers
Administration
DMO
FTP using t-sql
DTS
Visual Basic
DotNet .net
ASP
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)

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

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

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

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 Call stored procedures from ASP - inc file

Links - Useful sites about sql server and others
sqlteam.com Friendly, active, sql server question and answer forum + 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