General Articles

Get the progress of active SQL backups using TSQL

Get the progress of active SQL backups using TSQL The following script returns the status of any running backups on Microsoft SQL Server SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)     AS [Percent Complete

Paul Hayman - 3,005 views

List all Tables SQL Server

List all Tables SQL Server Here's a tiny bit of SQL for listing all the Tables in a database: SELECT      * FROM      sys.tables

Paul Hayman - 58,893 views

List all Stored Procedures SQL Server

List all Stored Procedures SQL Server Here's a tiny bit of SQL for listing all the Stored Procedures in a database: SELECT      * FROM      sys.procedures

Paul Hayman - 8,712 views

SQL Server Database, kill active processes and take it offline

SQL Server Database, kill active processes and take it offline Use the following TSQL to kill the active processes on a database and take it offline. DECLARE @DatabaseName nvarchar(50) SET @DatabaseName = N'databasename' DECL

Paul Hayman - 3,275 views

Select list of all tables in a MS SQL Server database including row count

Select list of all tables in a MS SQL Server database including row count The following script lists all tables in a database and their row count. SELECT      [TableName] = so.name,      [RowCount]&n

Paul Hayman - 2,424 views

Truncate all Logs on a SQL Server with TSQL

Truncate all Logs on a SQL Server with TSQL Hers's some T-SQL for truncating all logs on a Microsoft SQL Server: DECLARE @DBName varchar(255) DECLARE @LogName varchar(255) DECLARE @DATABASES_Fetch int DE

Paul Hayman - 7,517 views

Enable 'xp_cmdshell' in SQL server without Surface Area Configuration Tool

Enable 'xp_cmdshell' in SQL server without Surface Area Configuration Tool Here's the script needed for enabling 'xp_cmdshell' on a SQL Server. This allows you to run commands like MKDIR etc from TSQL. EXECUTE SP_CONFIGURE 'show advanced options', 

Paul Hayman - 2,029 views

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey'

Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' I was getting the following error when I attempted to create a new job in SQL Server Agent: Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey' to type 'Microsoft.SqlServe

Ric Hayman MCPD - 12,673 views

Backup ALL your SQL Server 2005 databases using ONE script

Backup ALL your SQL Server 2005 databases using ONE script I wanted to backup all my databases... I had loads, creating a step for each db was getting tedious, so I wrote this script. Enjoy DEC

Paul Hayman - 81,468 views

XMLHTTPRequest in Stored Procedure

XMLHTTPRequest in Stored Procedure I recently had a requirement to call a webservice from a stored procedure. I looked into doing this by writing a CLR assembly in SQL 2005. There were a few articles online describing this but working in a large organisation I knew that getting the thi

Dave Howard - 20,384 views

T-SQL script automatically adds auditing to a table

T-SQL script automatically adds auditing to a table The script below looks at the structure of the table you specify and creates a new table called xxx_Audit with the same structure plus a timestamp, less any constraints/unique keys etc. It then creates a new trigger on the original tabl

Dave Howard - 30,631 views

Select Column Information using SQL Server

Select Column Information using SQL Server Use this T-SQL to select infomation about a specific table/columns, Useful for automated scripts. DECLARE @tablename varchar(100) SET @tablename = N'myTable' SELECT clmns.name&n

Dave Howard - 154,500 views

Bug in VS2005? Using Typed DataSets with SQL 2005 and stored procedures

Bug in VS2005? Using Typed DataSets with SQL 2005 and stored procedures I have been a fan of typed datasets since I started using them in VS2005. Using the feature with SQL Express/stored procedures, Express/SQL Statements, SQL Server/SQL Statements all generate my DAL just fine. However, I

Dave Howard - 10,139 views

Enabling Agent XPs on a new SQL Server 2005 install

Enabling Agent XPs on a new SQL Server 2005 install I had to do this prior to creating a maintainence plan for a database on my development box. When i first tried to create the plan I was given a message that Agent XPs are not enabled and that I should run sp_reconfigure. Run t

Dave Howard - 28,852 views

Exporting large amounts of Data from SQL

Exporting Large amounts of Data from SQL If you have ever had an issue transfering large amount of data from SQL you can use the command line tool to solve this problem. From the command line bcp [<database_name.>[<owner>].]<table_name> out 

Danny Mehmed - 7,800 views

Full Text Search in SQL 2005

Full Text Search in SQL 2005 Full Text Search was installed when SQL Server was installed and a service was running, so I expected it to just be sitting there waiting. This is unlike SQL 2000, where I lost count of how many times I had to install it on different servers. Was I wron

Ric Hayman MCPD - 60,290 views

Extending Strongly Typed Datasets

Extending Strongly Typed Datasets Strongly Typed Datasets are a rather nice addition to VS2005 (they don't lend themselves well to acronyms however) You still need to create a database and tables and realationships but using STDs saves a writing huge amount of code. If this code

Dave Howard - 6,261 views

Change Database Owner Following Restore

Change Database Owner Following Restore I upgraded a SQL 2000 database to SQL 2005 by backing up and restoring. However when this was done, the database does not have a valid owner and you will not be able to add diagrams etc. To resolve this you will need to run the following SQ

Greg Duffield - 2,321 views

Intellisense For SQL Server Query Analyser

Intellisense For SQL Server Query Analyser Query Analyser has always been missing something and that something was intellisense. Just think about the time this would save you not having to have to look up the column names all the time or how to use a T-SQL function you have not used for some

Greg Duffield - 1,995 views

Change the Owner for Stored Procedures

Change the Owner for Stored Procedures This one comes from Tim Page, if you ever need to change the owner of several Store Procedures at the same time then this is the T-SQL for you. And this even supports SQL Sever 2005.

Greg Duffield - 33,142 views

Strongly Typed DataSets and GridViews

Strongly Typed DataSets and GridViews If you are anything like me you don't enjoy writing 5 or 6 stored procedures for each business object in your solution. If you do, then click here to read more about T-SQL! http://www.geekzilla.co.uk/Browse.aspx?CategoryID=32 For the re

Dave Howard - 9,699 views

GRANT Permissions on all of your Stored Procedures using a query

GRANT Permissions on all of your Stored Procedures using a query Permissions are an essential security feature, and you'll often create Roles and Users when you copy your Database live. Rather than using the permissions dialog boxes in Enterprise Manager you can write some T-SQL to perform

Paul Marshall - 59,847 views

Problem With SQL Server 2005 on Vista Beta2

Problem With SQL Server 2005 on Vista Beta2 So you've been through the throws of building a Vista Beta 2 installation and boy it looks good. Maybe a couple of missing drivers to hunt down but otherwise all that Vista goodness. So now you want to install some developer tools and start t

Mark Page - 4,950 views

Fixing broken users after Database Restore

Fixing broken users after Database Restore Ever restored a Database which was created on a different server then not been able to use the same logon you had previously set up? This command fixes the problem with the account. -- Replace username with the account to fix sp_change_

Paul Hayman - 8,217 views