![]() |
SQL Server TipsFrom the Database Community ForumApril 2004Help! 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 to get this utility. It's been updated to include MDAC components through version 2.8 (including ADO.NET components). Easily rename SQL Server databasesAlthough 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 ServerSuppose 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 mailMost 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. 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. |