Articles tagged under sql

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,516 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,759 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,703 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,731 views

Changing Schema for a group of objects

Changing Schema for a group of objects This script moves all of you stored procedures from one Schema to another. This was really handy when using a Typed Dataset with SQL 2005 and stored procedures. The wizard creates stored procedures in the users Schema (and complains if it does not e

Dave Howard - 11,419 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,885 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 - 8,025 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,344 views

Enabling Service Broker

Enabling Service Broker The following T-Sql enables or disabled service broker on SqlServer 2005. The Service Broker is required by .net for SqlCacheDependency support -- Enable Service Broker: ALTER DATABASE [Database Name] SET ENABLE_BROKER; -- Di

Paul Hayman - 65,058 views

Strongly Typed Dataset doesn't always auto generate DELETE and UPDATE stored procedures

Strongly Typed Dataset doesn't always auto generate DELETE and UPDATE stored procedures I found this problem whilst creating a STD that included a many to many relationship between two tables. I created a table consisting of the two foreign keys and appropriate constraints to support t

Dave Howard - 7,316 views

Handling hierachical structures in SQL

Handling hierachical structures in SQL Dealing with tree structures in SQL can be tricky. A common solution is to have a self referencing table with a primary key and parent key column. This allows for any depth of tree and allows each 'node' to have zero or more child nodes. Be

Dave Howard - 5,173 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,567 views

Updating Foreign keys in a GridView

Updating Foreign keys in a GridView A GridView generally shows and allows the user to update rows in a database. They are easy to set up until the values that you want to update is a foreign key. Its not obvious how to send the appropriate value back to the database or how to

Dave Howard - 12,308 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 - 60,528 views

CLR Stored Procedure for searching files

CLR Stored Procedure for searching files I was recently asked to write a CLR stored procedure which would process a text file and return a row for each line in the file that contained text matching our search criteria. I'd never written a CLR stored proc before so it was an interesting

Paul Hayman - 16,016 views

Enabling TRUSTWORTHY on a SQL 2005 Database

Enabling TRUSTWORTHY on a SQL 2005 Database If your .NET stored procedure wants to have external access, the targer database will have to have TRUSTWORTHY set on.. this is how you do it. ALTER DATABASE databasename SET TRUSTWORTHY ON; GO

Paul Hayman - 50,464 views

Enabling CLR execution in SQL Server

Enabling CLR execution in SQL Server If you plan to run stored procedures you have written in .NET you will probably need to enable CLR execution on your server. To do this you'll need to run the following T-SQL sp_configure 'clr enabled', 1 GO RECONFIGURE GO

Paul Hayman - 4,485 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,977 views

Writing CLR Stored Procedures in C#

Writing CLR Stored Procedures in C# As SQL Server 2005 rolls out DBA's are going to be forced to learn either C# or Visual Basic or both. Until now these were client side languages and not knowing them had little impact on your job. And if you write code in these languages your going to have

Paul Hayman - 16,010 views

DTS, Excel and mixed format columns

DTS, Excel and mixed format columns I recently had a problem DTS importing an Excel spreadsheet which contained columns with mixed text and data values. After a bit of hunting around, I found an extended property IMEX=1. The IMEX=1 property forces everything to text when reading from a sour

Paul Hayman - 8,923 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,247 views

SQL Server 2005 mdf file Query String

SQL Server 2005 mdf file Query String To add a SQL Server 2005 local database to one of your projects, create the connection as follows. conn = new SqlConnection(@"server=(local)\SQLExpress; AttachDbFileName=|DataDirectory|MyDatabase.mdf; Integrated Security=false;

Barrington Haynes - 7,675 views