*

SQL Server Tips

From the Database Community Forum

April 2004

Help! I know which version of SQL Server I've got, but which version of MDAC do I have?

To answer this question, download the latest version of the Microsoft Component Checker utility. Point your browser to

http://www.microsoft.com/downloads/details.aspx?FamilyID=8f0a8df6-4a21-4b43-bf53-14332ef092c9&displaylang=en

to get this utility. It's been updated to include MDAC components through version 2.8 (including ADO.NET components).

Easily rename SQL Server databases

Although you can't do so in Enterprise Manager, changing the name of an existing database is no problem with T-SQL. To do so, connect to your SQL Server with Query Analyzer and use the following code:

USE Master
	GO
	EXEC sp_dboption 'OldDbName', 'Single User', 'TRUE'
	EXEC sp_renamedb 'OldDbName', 'NewDbName'
	EXEC sp_dboption 'NewDbName', 'Single User', 'FALSE'
	GO

Before you can rename the database, you have to have exclusive access to it. This is the purpose of turning on the Single User option. Once you're alone, you can call on the system stored procedure sp_renamedb to make the change.

Pick a random record from a table in SQL Server

Suppose you need to retrieve one record from a table, but you want to choose that record randomly. There are, of course, several ways your might approach this problem, but clearly the most succinct is this one:

	SELECT TOP 1 * FROM Employees
	ORDER BY NEWID()

Since the NEWID() function returns a uniqueidentifier value which Microsoft guarantees to be unique, arranging the records in a table by this value will put them into a random order. Just pop the top record off the randomly sorted pile, and your problem is solved. You could also return a subset of n random records by replace TOP 1 with TOP N.

Use a real Windows account if your SQL Server needs to send mail

Most of us run the SQL Server service under the Local System account, which is fine for most situations. But if you're going to send mail from your SQL Server (using xp_sendmail or through SQL Server Agent), then you have to configure the SQL Server service to run in the context of a genuine Windows user and not the Local System account. The reason is that SQL Server will need access to the mail server to use a mail profile and send messages, and the Local System account doesn't come with this sort of access.



Internet Content Rating Association Valid XHTML 1.0! Valid CSS!

Alrak's Course Resources ©2002-2007 Karla Carter. All rights reserved. This material (including, but not limited to, Mini-lectures and Challenge Labs) may not be reproduced, displayed, modified or distributed without the express prior written permission of the copyright holder. For permission, contact Karla.