Select a list of databases missing Primary keys
Paul Hayman (695 views)
Select a list of databases missing Primary keys
The following T-SQL returns a list of tables in a database which have no primary key set.
#s# select * from sys.tables where object_id not in (
#s# select object_id from sys.indexes where is_primary_key = 1
#s# )
#s# order by name
|
Unable to cast object of type 'Microsoft.SqlServer.Management.Smo.SimpleObjectKey'
Ric Hayman MCPD (9325 views)
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.SqlServer.Mana
|
Setting default collation on a column
Paul Hayman (6731 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 (11344 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 (26552 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 (130527 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 (10415 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 (25898 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 MCPD (55948 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 (40659 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 (24298 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 (4756 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 (21685 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 (4545 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 (1913 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 (37846 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
|