 |
Setting default collation on a column
Paul Hayman (1047 views)
Setting default collation on a column
The following will set the collation on a column in a table back to Database Default without dropping and recreating the table (unlike Enterprise Manager)
#s#ALTER TABLE [MyTable] ALTER COLUMN [MyColumn]
#s# varchar(50) COLLATE database_def
|
 |
XMLHTTPRequest in Stored Procedure
Dave Howard (4275 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
|
 |
T-SQL script automatically adds auditing to a table
Dave Howard (13602 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-
|
 |
Select Column Information using SQL Server
Dave Howard (27116 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
|
 |
Changing Schema for a group of objects
Dave Howard (5711 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
|
 |
Enabling Agent XPs on a new SQL Server 2005 install
Dave Howard (8434 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
|
 |
Full Text Search in SQL 2005
Ric Hayman (14157 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
|
 |
Enabling Service Broker
Paul Hayman (4674 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
|
 |
Return new identity from Strongly Typed Dataset DataTable.Insert method
Dave Howard (13374 views)
Return new identity from Strongly Typed Dataset DataTable.Insert method
#$#<a href="http://www.dotnetkicks.com/kick/?url=http://www.geekzilla.co.uk/ViewF2737CEE-4FCE-4215-B8CA-74D77EF64A5F.htm"><img src="http://www.dotnetkicks.com/Services/Images/KickItImageGenerator.ashx?url=http://www.geekzil
|
 |
Handling hierachical structures in SQL
Dave Howard (3432 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
|
 |
Change the Owner for Stored Procedures
Greg Duffield (8443 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#
|
 |
Passing CSV to Stored Procedure
Greg Duffield (1486 views)
Passing CSV to Stored Procedure
There are many occasions when it would be easier to pass an array of values to a Stored Procedure instead of looping.
This can be done and is very simple to use once implemented, all it needs is a new UDF (User Defined Function).
#s#CREATE FUNCTION dbo.CSV
|
 |
SQL Server TABLE Variable
Greg Duffield (1027 views)
SQL Server TABLE Variable
When you need a temporary table in SQL Server 2000 (upwards) many people will implement a standard temporary table i.e.
#s#SELECT
#s# ProductID,
#s# ProductName,
#s# UnitPrice
#s#INTO #tmpTable
#s#FROM
#s# Products
#s#WHERE
#s# UnitsInStock < 40
#s#
#s
|
 |
GRANT Permissions on all of your Stored Procedures using a query
Paul Marshall (11555 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
|