Select Column Information using SQL Server
Dave Howard (82698 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 (41122 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
|
Backup ALL your SQL Server 2005 databases using ONE script
Paul Hayman (36706 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_
|
GRANT Permissions on all of your Stored Procedures using a query
Paul Marshall (26161 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
|
Enabling Service Broker
Paul Hayman (22091 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
|
T-SQL script automatically adds auditing to a table
Dave Howard (22031 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-
|
Enabling Agent XPs on a new SQL Server 2005 install
Dave Howard (19925 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 (17272 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#
|
Enabling TRUSTWORTHY on a SQL 2005 Database
Paul Hayman (15956 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
|
Writing CLR Stored Procedures in C#
Paul Hayman (13660 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
|
CLR Stored Procedure for searching files
Paul Hayman (11538 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
|
Changing Schema for a group of objects
Dave Howard (9155 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
|
XMLHTTPRequest in Stored Procedure
Dave Howard (8404 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
|
Updating Foreign keys in a GridView
Dave Howard (8001 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
|
DTS, Excel and mixed format columns
Paul Hayman (6791 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.
|
SQL Server 2005 mdf file Query String
Barrington Haynes (5711 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");
|
Strongly Typed Dataset doesn't always auto generate DELETE and UPDATE stored procedures
Dave Howard (5650 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
|
Fixing broken users after Database Restore
Paul Hayman (5300 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
|
Handling hierachical structures in SQL
Dave Howard (4359 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 (3962 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
|
Enabling CLR execution in SQL Server
Paul Hayman (3070 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 (2419 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>] [/
|