 |
Select Column Information using SQL Server
Dave Howard (27160 views)
Select Column Information using SQL Server
#$#<a href="http://www.dotnetkicks.com/kick/?url=http://www.geekzilla.co.uk/ViewF3E9658C-7B7A-472C-BE4A-5C25CD26C0E8.htm"><img src="http://www.dotnetkicks.com/Services/Images/KickItImageGenerator.ashx?url=http://www.geekzilla.co.uk/ViewF3E9658C-7B7A-472C
|
 |
Full Text Search in SQL 2005
Ric Hayman (14237 views)
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 wrong in exp
|
 |
T-SQL script automatically adds auditing to a table
Dave Howard (13619 views)
T-SQL script automatically adds auditing to a table
#$#<a href="http://www.dotnetkicks.com/kick/?url=http://www.geekzilla.co.uk/ViewECBC0CC3-1C7E-4E7E-B243-F2F259A5C920.htm"><img src="http://www.dotnetkicks.com/Services/Images/KickItImageGenerator.ashx?url=http://www.geekzilla.co.uk/ViewECBC0CC3-
|
 |
GRANT Permissions on all of your Stored Procedures using a query
Paul Marshall (11568 views)
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 the
|
 |
Writing CLR Stored Procedures in C#
Paul Hayman (10587 views)
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 to
|
 |
Backup ALL your SQL Server 2005 databases using ONE script
Paul Hayman (8541 views)
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
#s#DECLARE @DBName varchar(255)
#s#
#s#DECLARE @DATABASES_Fetch int
#s#
#s#DECLARE DATABASES_
|
 |
Enabling Agent XPs on a new SQL Server 2005 install
Dave Howard (8452 views)
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 this state
|
 |
Change the Owner for Stored Procedures
Greg Duffield (8452 views)
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.
#s#DECLARE
#s# @OldOwner sysname,
#s# @NewOwner sysname
#s#
#s#
|
 |
CLR Stored Procedure for searching files
Paul Hayman (6349 views)
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 learn
|
 |
Enabling TRUSTWORTHY on a SQL 2005 Database
Paul Hayman (6272 views)
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.
#c#ALTER DATABASE databasename SET TRUSTWORTHY ON;
#c#GO
|
 |
Changing Schema for a group of objects
Dave Howard (5721 views)
Changing Schema for a group of objects
#$#<a href="http://www.dotnetkicks.com/kick/?url=http://www.geekzilla.co.uk/View4384ACEC-8C9D-452A-83F6-230FFE43C17B.htm"><img src="http://www.dotnetkicks.com/Services/Images/KickItImageGenerator.ashx?url=http://www.geekzilla.co.uk/View4384ACEC-8C9D-452A-83F
|
 |
Updating Foreign keys in a GridView
Dave Howard (5112 views)
Updating Foreign keys in a GridView
#$#<a href="http://www.dotnetkicks.com/kick/?url=http://www.geekzilla.co.uk/ViewD7E7F40C-85F7-47D8-89DE-0FB5F7AFAAE5.htm"><img src="http://www.dotnetkicks.com/Services/Images/KickItImageGenerator.ashx?url=http://www.geekzilla.co.uk/ViewD7E7F40C-85F7-47D8-89DE
|
 |
SQL ViewState
Barrington Haynes (4963 views)
SQL ViewState
Whilst working on a recent project, I was having problems with the size of the view state being downloaded to the client.
Whilst surfing Google, I found the following article which has now solved my problems, http://weblogs.asp.net/adweigert/archive/2004/03/09/86628.aspx
The
|
 |
Enabling Service Broker
Paul Hayman (4684 views)
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
#s#-- Enable Service Broker:
#s#ALTER DATABASE [Database Name] SET ENABLE_BROKER;
#s#
#s#-- Disable Service Broker:
#s#A
|
 |
XMLHTTPRequest in Stored Procedure
Dave Howard (4286 views)
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 thing wor
|
 |
DTS, Excel and mixed format columns
Paul Hayman (4081 views)
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 source.
|
 |
Strongly Typed Dataset doesn't always auto generate DELETE and UPDATE stored procedures
Dave Howard (3713 views)
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 the rel
|
 |
Handling hierachical structures in SQL
Dave Howard (3433 views)
Handling hierachical structures in SQL
#$#<a href="http://www.dotnetkicks.com/kick/?url=http://www.geekzilla.co.uk/View96264E4E-52EE-42BD-9494-2F33947A9063.htm"><img src="http://www.dotnetkicks.com/Services/Images/KickItImageGenerator.ashx?url=http://www.geekzilla.co.uk/View96264E4E-52EE-42BD-9
|
 |
Problem With SQL Server 2005 on Vista Beta2
Mark Page (2860 views)
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 to play
|
 |
SQL Server 2005 mdf file Query String
Barrington Haynes (2656 views)
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.
#c#conn = new SqlConnection(@"server=(local)\SQLExpress; AttachDbFileName=|DataDirectory|MyDatabase.mdf; Integrated Security=false; User Instance=false");
|
 |
Fixing broken users after Database Restore
Paul Hayman (2512 views)
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.
#s#-- Replace username with the account to fix
#s#sp_change_users
|
 |
Enabling CLR execution in SQL Server
Paul Hayman (1670 views)
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
#c#sp_configure 'clr enabled', 1
#c#GO
#c#RECONFIGURE
#c#GO
|
 |
Exporting large amounts of Data from SQL
Danny Mehmed (1590 views)
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
#s#bcp [<database_name.>[<owner>].]<table_name> out <csv-file> [/U<username>] [/P<password>] [/
|