Sort A SQL Server Table With CASE Or CHARINDEX

Let's say you have a table with states in your database, you only have 3 values NY, ME and SC.
You want to order the result like this: first NY followed by SC and ME last.
You can do that in two different ways
One: use a case statement in your order by
Two: use Charindex in your order by
Let's see how that works

CREATE TABLE #TEST (
STATE CHAR(2))

INSERT INTO #TEST
SELECT 'ME' UNION ALL
SELECT 'ME' UNION ALL
SELECT 'ME' UNION ALL
SELECT 'SC' UNION ALL
SELECT 'NY' UNION ALL
SELECT 'SC' UNION ALL
SELECT 'NY' UNION ALL
SELECT 'SC'

-- order by using CASE

SELECT *
FROM #TEST
ORDER BY CASE STATE
WHEN 'NY' THEN 1
WHEN 'SC' THEN 2
ELSE 3
END

--Order by using CHARINDEX
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'NY-SC-ME')

--or without NY since CHARINDEX will return 0 for NY and it will be first
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'SC-ME')


--the problem is of course if you have more values and you only want to have NY and SC showing up first and second
--let's insert 2 more rows
INSERT INTO #TEST
SELECT 'IL'
UNION ALL
SELECT 'CA'

-- Now the CHARINDEX Order doesn't work
-- the trick is to make it Descending and switch the states around
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'SC-NY') DESC

or this way

--Order by using CHARINDEX DESC
SELECT *
FROM #TEST
ORDER BY CHARINDEX(STATE,'ME-SC-NY') DESC

User Definded Functions And GETDATE() (nondeterministic built-in functions )

I have read on Louis Davidson's blog a post about UDF and being able to use GETDATE() in a UDF in SQL server 2005. I wasn't aware of that so I would like to bring it to your attention
In SQL server 2005 you are able to use the following nondeterministic built-in functions in Transact-SQL user-defined functions:
CURRENT_TIMESTAMP
GET_TRANSMISSION_STATUS
GETDATE
GETUTCDATE
@@PACK_SENT
@@PACK_RECEIVED
@@MAX_CONNECTIONS
@@PACKET_ERRORS
@@CONNECTIONS
@@TIMETICKS
@@CPU_BUSY
@@TOTAL_ERRORS
@@DBTS
@@TOTAL_READ
@@IDLE
@@TOTAL_WRITE
@@IO_BUSY

So let's get started and test it out, I will create 2 UDF's one that uses GETDATE() and one that uses DATEADD() and GETDATE()

CREATE FUNCTION fnNonDeterministic ()
RETURNS DATETIME
AS
BEGIN
RETURN GETDATE()
END
GO

CREATE FUNCTION fnNonDeterministicAdd (@Days int)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(d,@Days,GETDATE())
END
GO

--Let's see what the ouput is
SELECT dbo.fnNonDeterministic() AS DateReturned,
dbo.fnNonDeterministicAdd(2) AS DateAddReturned

Use CHARINDEX, LEN and SUBSTRING To Find Data Between Characters

Let's say you get data in the following format aaaa/bbbb/cccc/dddd/eeee
What you need from this file is just the data between the first and the last forward slash (in thid case bbbb)
If there is no slash in the file then grab all the data, if there is only 1 slash then grab everything after that slash
This was actually one of the questions that I have answered on tek-tips so yes data like this does exist.
How do you attack such a problem?
First you have to find the first slash, you do that by using CHARINDEX, CHARINDEX will return the position where the first slash is located, then you use CHARINDEX again with SUBSTRING to find the Second slash, finally you use SUBSTRING with the LEFT function and the difference between the 2 CHARINDEX calls as the length
I used a replace function at the beginning to check if there is more than 1 slash or not and then a case statement later on depending on the outcome of that replace function
Let’s see how I did it

--Create some test data
CREATE TABLE #test (ValueField VARCHAR(50))
INSERT INTO #test
SELECT ' /aaaa' UNION ALL
SELECT ' /yyyy/bbbb ' UNION ALL
SELECT 'zzzzz/c/defgh ' UNION ALL
SELECT 'VVVVV'

--This is our delimiter
DECLARE @Delimiter CHAR(1)
SET @Delimiter = '/'

--Show the whole field and the extracted value
SELECT ValueField AS Originalvalue,
CASE WHEN LEN(ValueField) -LEN(REPLACE(ValueField,@Delimiter,'')) > 1
THEN LEFT(SUBSTRING(ValueField,CHARINDEX(@Delimiter,ValueField)+1 ,LEN(ValueField)),CHARINDEX(@Delimiter,SUBSTRING(ValueField,CHARINDEX(@Delimiter,ValueField)+1 ,LEN(ValueField)))-1)
ELSE SUBSTRING(ValueField,CHARINDEX(@Delimiter,ValueField)+1 ,LEN(ValueField))END AS ExtractedValue
FROM #test

How To Check For A Certain Time With SQL

Sometimes you have jobs and you don't know how long they will run
Let's just assume here that the job has 2 steps
You run step 1 and it takes x hours, you only want to run step 2 if it's before 5:45 AM because some other job will start at 6:00 AM and you don't want those 2 jobs hitting the same table at the same time (a real world scenario might be different of course, this is just a simplified version)

I hardcoded 05:45:00.000 here, you can of course store this in a parameter or even in a table so that you only have to change it in one place


--Get today's date
DECLARE @CurrentDateTime DATETIME
SET @CurrentDateTime = GETDATE()

-- create a 2nd parameter and append the time that you need to check for
DECLARE @TimeToChek DATETIME
SELECT @TimeToChek = CONVERT(VARCHAR,@CurrentDateTime,101) + ' 05:45:00.000'



IF (@CurrentDateTime <= @TimeToChek)
-- It is before 5:45 AM
BEGIN
SELECT 'before 5:45am' OutputStatus,
@CurrentDateTime AS CurrentTime,
@TimeToChek AS TimeToCheck
END
ELSE
-- it is before 5:45 AM
BEGIN
SELECT 'Not before 5:45am' OutputStatus,
@CurrentDateTime AS CurrentTime,
@TimeToChek AS TimeToCheck
END

Beginning SQL Server 2005 For Developers: From Novice To Professional

Apress has published their latest SQl server 2005 book: Beginning SQL Server 2005 For Developers: From Novice To Professional


SQL Server 2005 will increase your programming options, productivity, analysis, and database management. If you have some basic knowledge of relational databases and want to start a career as a developer using SQL Server, then this book is your ideal first step. It explains the core jobs and roles for developing a database in both SQL Server 2000 and 2005.

This book features practical steps to help you overcome issues you’re likely to encounter. You’ll learn to use SQL for querying, inserting, updating, and deleting data. You’ll also learn how to back up and restore databases for basic administration in SQL Server. Further, you’ll cover how to build a complete database, from the fundamentals of relational database design to table and index creation.

Additionally, you’ll start to program in T-SQL, SQL Server’s implementation (and extension) of the SQL programming language, and you’ll come away with effective programming techniques using stored procedures and triggers. The book also includes a CD that contains an evaluation edition of SQL Server 2005 so you can start building database applications right away.

This book is 536 pages and below is the table of contents


CHAPTER 1 SQL Server 2005 Overview and Installation
CHAPTER 2 SQL Server Management Studio
CHAPTER 3 Database Design and Creation
CHAPTER 4 Security
CHAPTER 5 Defining Tables
CHAPTER 6 Creating Indexes and Database Diagramming
CHAPTER 7 Database Backups, Recovery, and Maintenance
CHAPTER 8 Working with the Data
CHAPTER 9 Building a View
CHAPTER 10 Stored Procedures
CHAPTER 11 T-SQL Essentials
CHAPTER 12 Advanced T-SQL
CHAPTER 13 Triggers
CHAPTER 14 SQL Server 2005 Reporting Services
APPENDIX Glossary of Terms
INDEX

Download chapter 4 (security) here

If you want to puchase this book the Amazon link is here

Check If Temporary Table Exists

How do you check if a temp table exists?
You can use IF OBJECT_ID('tempdb..#temp') IS NOT NULL
Let's see how it works

--Create table
USE Norhtwind
GO

CREATE TABLE #temp(id INT)

--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END

--Another way to check with an undocumented optional second parameter
IF OBJECT_ID('tempdb..#temp','u') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END



--Don't do this because this checks the local DB and will return does not exist
IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END


--unless you do something like this
USE tempdb
GO

--Now it exists again
IF OBJECT_ID('tempdb..#temp','local') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END

--let's go back to Norhtwind again
USE Norhtwind
GO


--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END

now open a new window from Query Analyzer (CTRL + N) and run this code again
--Check if it exists
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
BEGIN
PRINT '#temp exists!'
END
ELSE
BEGIN
PRINT '#temp does not exist!'
END


It doesn't exist and that is correct since it's a local temp table not a global temp table


Well let's test that statement
--create a global temp table
CREATE TABLE ##temp(id INT) --Notice the 2 pound signs, that's how you create a global variable

--Check if it exists
IF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
PRINT '##temp exists!'
END
ELSE
BEGIN
PRINT '##temp does not exist!'
END

It exists, right?
Now run the same code in a new Query Analyzer window (CTRL + N)

--Check if it exists
IF OBJECT_ID('tempdb..##temp') IS NOT NULL
BEGIN
PRINT '##temp exists!'
END
ELSE
BEGIN
PRINT '##temp does not exist!'
END

And yes this time it does exist since it's a global table

SQL:Format Data From Numberformat To Timeformat

Sometimes you have data that's in a number format and you need to show it in a time format. Instead of 2.25 you need to show 02:15
In that case you can use a combination of CONVERT and DATEADD
Run the examples below to see how it works

DECLARE @a DECIMAL(6,2)
SELECT @a = 3.00
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted

SELECT @a = 3.15
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted

SELECT @a = 3.25
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted


SELECT @a = 3.75
SELECT @a AS OldValue,
CONVERT(CHAR(5), DATEADD(ss, @a * 3600, 0), 108) AS TimeFormatted

SQL Server Related Webcasts For February 2006

Microsoft has scheduled 18 new new webcasts for February, covering such topics as SQL Server 2005 Essentials: SQL/CLR, Reporting Services Basics, Using .NET Procedural Code in SQL Server 2005m Using Analysis Services, Introduction to Data Mining and more. The whole list with their start dates is listed below. Enjoy

MSDN Webcast: SQL Server 2005 Essentials: SQL/CLR (Part 1 of 14) (Level 200)
Friday, February 03, 200610:00 AM Pacific Time As the first of a 14-part series, this webcast covers what it means to be a Microsoft .NET runtime host. Topics include how Microsoft SQL Server 2005 works with the .NET 2.0 application programming interfaces (APIs) to make loading and running code safe, reliable, and scalable.

MSDN Webcast: SQL Master Class: Reporting Services Basics (Level 200)
Tuesday, February 07, 20069:00 AM Pacific Time Microsoft SQL Server Reporting Services was released in 2004 to provide developers with an easy way to extract and share information captured in a relational database. In this webcast, we explore how to create basic reports, link them to together, and provide end users with a way to get the information they need.

MSDN Architecture Webcast: Build Reliable Application Platforms with SQL Server 2005 (Level 200)
Tuesday, February 07, 20061:00 PM Pacific Time Join us to discover the many advantages of using SQL Server 2005 for application building.

MSDN Webcast: SQL Server 2005 Essentials: Using .NET Procedural Code in SQL Server 2005 (Part 2 of 14) (Level 200)
Friday, February 10, 200610:00 AM Pacific TimeThis second webcast of a 14-part series covers in detail how to write procedures, functions, and triggers using a managed language. Additionally, we discuss when to use managed code and when to use Transact-SQL.

TechNet Webcast: Introduction to SQL Server 2005 Reporting Services (Level 200)
Monday, February 13, 20069:30 AM Pacific TimeAttend this webcast to learn about the new capabilities in SQL Server 2005 Reporting Services.

TechNet Webcast: Introduction to SQL Server 2005 Analysis Services (Level 200)
Tuesday, February 14, 20069:30 AM Pacific Time This webcast shows how you can use SQL Server Analysis Services to build a highly scalable business intelligence infrastructure that supports the analytics your business needs to improve overall performance.

TechNet Webcast: Introduction to Data Mining with SQL Server 2005 (Level 200)
Thursday, February 16, 20069:30 AM Pacific TimeThis webcast introduces you to the possibilities of data mining and predictive analytics.

MSDN Webcast: SQL Server 2005 Essentials: The SqlServer Data Provider (Part 3 of 14) (Level 200)
Friday, February 17, 200610:00 AM Pacific TimeThis third webcast of a 14-part series covers the new provider, System.Data.SqlServer, and discusses how connections, variable mappings, and other functions built into Transact-SQL are handled when a Microsoft .NET language is used.

TechNet Webcast: Advanced SQL Server 2005 Reporting Services (Level 300)
Monday, February 20, 20069:30 AM Pacific Time Topics in this webcast include: how to use the built-in expression language, report parameterization (data driven, multi-valued, and hierarchical), supporting multiple data sources (including relational, multi-dimensional, and XML), and making reports interactive.

TechNet Webcast: Security in SQL Server 2005 (Level 200)
Monday, February 20, 200611:00 AM Pacific TimeThis webcast highlights security concepts that are new to Microsoft SQL Server 2005, such as encryption and user-schema separation, and looks at how SQL Server 2005 breaks security down into several distinct areas.

MSDN Webcast: Using ADO.NET with Your Lotus Notes Applications (Level 400)
Tuesday, February 21, 20068:00 AM Pacific TimeADO.NET is the standard method for Microsoft Visual Studio .NET applications to interact with data sources such as Microsoft SQL Server, the IBM DB2 Universal Database, and Oracle.

TechNet Webcast: A Technical Overview of High Availability Features in SQL Server 2005 (Level 200)
Friday, February 24, 20068:00 AM Pacific TimeThere are many barriers to achieving high availability in a database system, and only some of them can be addressed by database management systems technology. This webcast examines some of the features of Microsoft SQL Server 2005—the latest release of the SQL Server product—that can help you overcome some of these barriers.

MSDN Webcast: SQL Server 2005 Essentials (Part 4 of 14): User-Defined Types and User-Defined Aggregates (Level 200)
Friday, February 24, 200610:00 AM Pacific TimeIn this fourth module of the 14-part series, learn about using .NET objects and how you can use them inside the database.

TechNet Webcast: Introduction to SQL Server 2005 Report Builder (Level 200)
Monday, February 27, 20069:30 AM Pacific Time Learn how Microsoft SQL Server 2005 Reporting Services is meeting the demand for end-user, ad hoc reporting.

TechNet Webcast: Introduction to SQL Server 2005 Integration Services (Level 200)
Monday, February 27, 200611:30 AM Pacific Time In this webcast, we introduce you to the enterprise-class capabilities of Integration Services including dedicated management functionality, collaborative development environment, and high scalability and reliability. You also learn how you can take advantage of SQL Server 2005 Integration Services to implement data quality, data profiling, and custom transforms in your organization.

MSDN Webcast: SQL Master Class: Using Report Builder (Level 200)
Tuesday, February 28, 20069:00 AM Pacific Time Find out about the new Report Builder in Microsoft SQL Server 2005 that allows end users to customize reports to their liking.

TechNet Webcast: Advanced SQL Server 2005 Integration Services (Level 300)
Tuesday, February 28, 200611:30 AM Pacific Time This webcast presents the key architectural enhancements in SSIS that makes it extremely scalable.

Varchar To Varbinary And Vice Versa

I have received an email from someone in Taiwan who looked at the contact me blog entry and wanted to know how I ‘encrypted’ my email address like that. Well there really is not much to it, you just convert the email string to varbinary. Then you convert it back to varchar to get the string back
For example if my email address is abc@qwerty.NotCom then you would do
SELECT CONVERT(VARBINARY(17),'abc@qwerty.NotCom')

This would return 0x616263407177657274792E4E6F74436F6D

Then you do
SELECT CONVERT(VARCHAR(17),0x616263407177657274792E4E6F74436F6D)
to see the string again
Of course you will have to increase the size of the varchar and the varbinary field if it exceeds 17 bytes

Top 5 SQL Server Posts for January 2006

Below are the top 5 posts according to Google Analytics for the month of January

SQL Server 2005 Free E-Learning
Feature Pack for Microsoft SQL Server 2005
Fast Date Ranges Without Loops In SQL Server 2000
Find all Primary and Foreign Keys In A Database

Top SQL Server Google Searches For January 2006

These are the top SQL Searches on this site for the month of December. I have left out searches that have nothing to do with SQL Server or programming. Here are the results...

MS SQL zero fill
"Trouble Checker" "SQL Server"
sql server management studio
auto increment
SQL books
"dts step by step"
SSPI
sp_removedbreplication
sql server
SQl server books
triggers
sql login
sql security

I decided to blog about some of these searches, I see a lot of searches for books nowadays and also for login problems. I did create 2 posts about the login problems so that's covered for now. I will feature more books from now on

SQL Server Site Geo Map For January 2006

These are the countries where the bulk of the visitors for this blog are coming from. As you can see most of the visitors are from the US and Europe, Asia is in third place. When I look at the maps day by day I also see some visitors from Africa and the Middle East but you need more than one visitor per city in order for it to show up in the monthly map.

Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005

I am seeing a lot of searches on this blog or reaching this blog from MSN/Google with this search “SQL 2005 Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.” So this is what causes the problem: your SQL Server has been setup with windows authentication only. In order to make it mixed mode authentication right click on the server name in enterprise manager select properties and click on the security tab. Select SQL server and Windows Authentication mode(see picture…) and that will fix it The cool thing about SQL server 2005 is that you can script this out so that you can run the code on multiple servers instead of going to all the servers and clicking etc. Just click on script and code like the one below will be generated

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2
GO

For the SQL server 2000 version go here

NULL Trouble In SQL Server Land

I am seeing a lot of searches for SQL + Nulls from this site so I decided to blog about it
Before I start I would like to point out that all the code will behave this way if ANSI_NULLS is set to on ,not to off

CREATE TABLE testnulls (ID INT)
INSERT INTO testnulls VALUES (1)
INSERT INTO testnulls VALUES (2)
INSERT INTO testnulls VALUES (null)

CREATE TABLE testjoin (ID INT)
INSERT INTO testjoin VALUES (1)
INSERT INTO testjoin VALUES (3)

--We get back value 1 here
SELECT * FROM testjoin WHERE ID IN(SELECT ID FROM testnulls)

--Nothing is returned
SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls)

--Value 3 is returned
SELECT * FROM testjoin WHERE ID NOT IN(SELECT ID FROM testnulls WHERE ID IS NOT NULL)


--value 3 is returned
SELECT * FROM testjoin j
WHERE NOT EXISTS (SELECT n.ID
FROM testnulls n
WHERE n.ID = j.ID)


--value 3 is returned
SELECT j.* FROM testjoin j
LEFT OUTER JOIN testnulls n ON n.ID = j.ID
WHERE n.ID IS NULL



--a count of 3 is returned
SELECT COUNT(*) FROM testnulls

-- a count of 2 is returned, the count ignores NULL values
SELECT COUNT(id) FROM testnulls

--By using coalesce the count is also 3
SELECT COUNT(COALESCE(id,0)) FROM testnulls

--all 3 rows are returned
SELECT * FROM testnulls

-- 1 row is returned
SELECT * FROM testnulls
WHERE ID = 1

-- only 1 row is returned the row with the NULL value is ignored
SELECT * FROM testnulls
WHERE ID <> 1


-- Now both rows that are not 1 are returned
SELECT * FROM testnulls
WHERE ID <>1
OR ID IS NULL

-- Now both rows that are not 1 are returned also
SELECT * FROM testnulls
WHERE COALESCE(ID,0) <> 1

Some more NULL Fun
You can''t compare NULL with anything
Since both values are unknown even a comparison with another NULL is unknown

DECLARE @v INT
DECLARE @v2 INT

SELECT @v =NULL,@v2 = NULL
IF @v = @v2
SELECT 'yes'
ELSE
SELECT 'No'


Be carefull with forgetting to initialize parameters while building string

DECLARE @SQL VARCHAR(500)
DECLARE @Id INT

SELECT @SQL = 'SELECT * FROM testnulls
WHERE ID =' + CONVERT(VARCHAR,@Id)

SELECT @SQL

Professional SQL Server 2005 Integration Services

Professional SQL Server 2005 Integration Services has just been published by WROX, a co-worker already ordered it and I skimmed though it during lunch and ordered my own copy. I already own SQL server 2000 DTS and was very satisfied with that book. I have included 2 links to 2 sample chapters as well as a link to Amazon, so far there are 2 reviews on Amazon and both are 5 stars. The book itself is 720 pages and below is the table of contents





Foreword.
Preface.
Acknowledegements.
Author Bios.
Introduction.

1. Services.
2. Importing and Exporting Data.
3. SSIS Fundamentals.
4. Data Flow Elements and Transforms.
5. Creating Your First Package.
6. Advanced Tasks and Transforms.
7. Scripting in DTS.
8. Accessing Heterogenous Data.
9. Reliability and Scalability.
10. Understanding the Integration Services Engine.
11. Applying the Integration Services Engine.
12. DTS 2000 Migration and Metadata Management.
13. Error and Event Handling.
14. Programming and Extending SSIS.
15. Adding a User Interface to Components.
16. External Management and WMI.
17. Using SSIS with External Applications.
18. SSIS Software Development Life Cycle.
19. Case Study: Typical Relational ETL.
Index.

Chapter 1 Welcome to SQL Server Integration Services sample chapter is available here
Chapter 5 Creating an End-to-End Package is available from Brian Knight's blog

The Amazon link is here

Format SQL Server Decimal Data

Yesterday someone posted a question in the SQL server
microsoft.public.sqlserver.programming news group about formatting decimal data. The person did not want trailing zeros or a decimal point if the amount was whole
The trick to do this is convert the decimal point to a space, rtrim it and then convert the space to a decimal. When you do that the trailing decimal point will vanish. The same applies for trailing zeros of course

output
.25000
.50000
1.00000
1.50000
2.00000
3.00000

desired output
0.25
0.5
1
1.5
2
3


--first create our test data table
CREATE TABLE #testdecimals(testdata DECIMAL(20,5))
INSERT INTO #testdecimals
SELECT 0.2500 UNION ALL
SELECT 0.5000 UNION ALL
SELECT 1.0000 UNION ALL
SELECT 1.5000 UNION ALL
SELECT 2.0000 UNION ALL
SELECT 3.0000

-- regular select
SELECT testdata
FROM #testdecimals

--formatted select
SELECT REPLACE(RTRIM(REPLACE(REPLACE(RTRIM(REPLACE(CONVERT(varchar,testdata),'0',' ')),' ','0'),'.',' ')),' ','.')
FROM #testdecimals

Use XP_CMDSHELL To Get All File Names With Size In A Directory

To get all the files with their filesize in a directory use xp_cmdshell with a temporary table
After the temporary table is populated you will be able to sort the result set any way you like


CREATE TABLE #tempList (Files VARCHAR(500))

INSERT INTO #tempList
EXEC MASTER..XP_CMDSHELL 'dir c:\ '


--delete all directories
DELETE #tempList WHERE Files LIKE '%<dir>%'

--delete all informational messages
DELETE #tempList WHERE Files LIKE ' %'

--delete the null values
DELETE #tempList WHERE Files IS NULL

--get rid of dateinfo
UPDATE #tempList SET files =RIGHT(files,(LEN(files)-20))

--get rid of leading spaces
UPDATE #tempList SET files =LTRIM(files)

--split data into size and filename
SELECT LEFT(files,PATINDEX('% %',files)) AS Size,
RIGHT(files,LEN(files) -PATINDEX('% %',files)) AS FileName
FROM #tempList

Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2000

I am seeing a ton of searches on this blog or reaching this blog from MSN/Google with this search “Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.” So this is what causes the problem: your SQL Server has been setup with windows authentication only. In order to make it mixed mode authentication right click on the server name in enterprise manager select properties and click on the security tab. Select SQL server and Windows (see picture…) and that will fix it
I will monitor the searches and write more stuff about things that people are searching for.
For the SQL server 2005 version go here

Kill All Connections To Your SQL Server Database

Sometimes you need to restore a database and put it in single user mode but you can't because people are still using it. I will show you 2 ways to accomplish that
The first way is to loop through the sysprocesses table and kill all the processes one by one. The second way is to do an alter statement on the DB


--loop through the sysprocesses
DECLARE @sysDbName SYSNAME
SELECT @sysDbName = 'northwind'

SELECT IDENTITY(int, 1,1)AS ID,spid
INTO #LoopProcess
FROM master..sysprocesses
WHERE dbid = DB_ID(@sysDbName)


DECLARE @SPID SMALLINT
DECLARE @SQL VARCHAR(255)
DECLARE @MaxID INT, @LoopID INT

SELECT @LoopID =1,@MaxID = MAX(ID) FROM #LoopProcess

WHILE @LoopID <= @MaxID
BEGIN
SELECT @SPID = spid FROM #LoopProcess WHERE ID = @LoopID
SELECT @SQL = 'KILL ' + CONVERT(VARCHAR, @SPID)

EXEC( @SQL )

SELECT @LoopID = @LoopID +1
END

DROP TABLE #LoopProcess


--alter the DB by making it single user (all transaction will be rolled back)
ALTER DATABASE northwind SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--do your restore here


-- Make the DB multi user again
ALTER DATABASE northwind SET MULTI_USER

Improve Your SQL Server Skills By Helping Others

Participating in forums and newsgroups is a great way to improve your SQL Server skills. You will see other people's problems and if you are just starting out then just relax and read the responses. The responses are most likely from a SQL Server MVP and these are top-quality. You will see many familiar authors in the MS public newsgroups such as Kalen Delaney, Itzik Ben-Gan, Adam Machanic, Louis Davidson and more. I have encountered SQL Server problems and remembered that I read about this on a SQL Server newsgroup and that a person posted a solution, from then on it's quick Google search and there you have it.

MS Public SQL Server Newsgroups
microsoft.public.sqlserver.newusers
microsoft.public.sqlserver.programming
microsoft.public.sqlserver.server

And a full list here

BTW these newsgroups can also be accessed by a newsreader as NTTP

Besides the newsgroups I also participate in the SQL Server Tek-Tip Forums for programming and administration What's great about Tek-Tips is the fact that people give stars to people who post a useful solution. So this will give you more incentives to post a good solution. You can also write searchable FAQ's

Tek-Tip SQL Server Forums
programming
administration

Like I said before you don't have to post solution but do at least spend some of your time reading the solutions to people's problems because sooner or later you might encounter them yourself.