GeekZilla
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 - 4,090 viewsList 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 - 66,241 viewsList 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 - 9,784 viewsSQL 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 - 4,391 viewsSelect 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 - 3,475 viewsTruncate 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 - 8,903 viewsEnable '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 - 3,048 viewsUnable 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,733 viewsBackup 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,540 viewsXMLHTTPRequest 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,863 viewsT-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,732 viewsSelect 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,876 viewsBug 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,193 viewsEnabling 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,909 viewsExporting 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 - 8,102 viewsFull 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,379 viewsExtending 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,328 viewsChange 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,380 viewsIntellisense 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 - 2,064 viewsChange 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,775 viewsStrongly 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,768 viewsGRANT 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 - 60,748 viewsProblem 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,995 viewsFixing 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,272 views