SQL Server Magazine UPDATE, September 11, 2003

SQL Server Magazine UPDATE—brought to you by SQL Server Magazine and SQL Server Magazine Connections

THIS ISSUE SPONSORED BY

TDWI World Conference, San Diego, November 2-7

Lumigent: Free Paper on Continuous Data Auditing
(below COMMENTARY)

SPONSOR: TDWI WORLD CONFERENCE, SAN DIEGO, NOVEMBER 2-7

Join keynote speakers Barry Devlin and Claudia Imhoff at the TDWI World Conference in sunny San Diego, November 2-7, 2003. Industry experts deliver over 50 full-day, half-day, and evening classes. Conference highlights include a Business Intelligence Strategies program for technical executives, three new one-day methodology courses, peer networking, one-on-one consulting, a hassle-free exhibit hall, data warehousing in higher education Special Interest Group, and more. Register before October 3 to receive the early-bird discount!
   http://lists.sqlmag.com/cgi-bin3/DM/y/ecjJ0FgQMn0BRZ0BCWg0Am

September 11, 2003—In this issue:

1. COMMENTARY

2. SQL SERVER NEWS AND VIEWS

3. READER CHALLENGE

4. ANNOUNCEMENTS

5. RESOURCES

6. HOT RELEASES (ADVERTISEMENTS)

7. NEW AND IMPROVED

8. CONTACT US

See this section for a list of ways to contact us.

1. COMMENTARY

(contributed by Brian Moran, news editor, [email protected])

Are you planning to evaluate a 64-bit version of SQL Server 2000 for an upcoming project? Several readers have asked, "Where can I get an evaluation copy? I can't find one on the Microsoft Web site." I did some checking and, sure enough, Microsoft doesn't have an evaluation version of 64-bit SQL Server 2000 available for free download. The company provides a free evaluation version of the 32-bit product, so it's a little odd that Microsoft wouldn't make the 64-bit version available for free too.

Microsoft suggested two options for acquiring 64-bit SQL Server. You can download SQL Server 2000 Developer Edition (64-bit) from the MSDN Subscribers download site if you have a MSDN Universal subscription. The download is free to subscribers, but MSDN Universal subscriptions aren't free. The least expensive (and still legal) way to acquire a 64-bit version of SQL Server 2000 is to buy the SQL Server 2000 Developer Edition (64-bit). Microsoft sells the software for $49.99, and I've seen it available from Independent Software Vendors (ISVs) for less. I think offering a free evaluation for 32-bit and not doing so for 64-bit is an inconsistent policy for Microsoft, but alas, I don't make the rules.

The $49.99 list price for SQL Server 2000 Developer Edition is a nice touch, though. Microsoft lowered the price from $499 to $49.99 on August 1. But don't get any clever ideas about using the Developer Edition as a low-cost way to acquire SQL Server. The EULA clearly states that the Developer Edition is only for designing, developing, and testing your software products. However, using the Developer Edition is a great way to learn about SQL Server or just have fun. (It's pretty sad that I think playing with SQL Server might be fun, huh?) You can find a list of ISVs that sell the Developer Edition at http://shop.microsoft.com/helpdesk/mvlref.asp?S=Open.

LUMIGENT: FREE PAPER ON CONTINUOUS DATA AUDITING

Learn how you can monitor and audit database activity without the use of triggers and application modification. Automatically capture changes in permissions, schema and data mods. Free white paper at:
   http://lists.sqlmag.com/cgi-bin3/DM/y/ecjJ0FgQMn0BRZ0BCWh0An

2. SQL SERVER NEWS AND VIEWS

Euan Garden, SQL Server Product Unit Manager, will present information about the upcoming SQL Server release, Yukon, on October 28th at 7:30 p.m. The event, which takes place during the Microsoft Professional Developers Conference in Los Angeles, will be in the ballroom of the Los Angeles Hyatt Regency hotel and is open to the public. Registration will begin at 6:30 p.m., and Microsoft will provide pizza and soda. Attendees will learn about the next major release of SQL Server and interact with members of the SQL Server development team.

By default, SQL Server denies access to user accounts don't have explicit access to a database, a table, or a view. And by default, ASP.NET applications run in the context of the ASPNET user account. Unless you permit access to the ASPNET user account, an ASP.NET application can't read or update data in a SQL Server database. The Microsoft article "HOW TO: Configure SQL Server Security for .NET Applications" describes the process that you can use to give an ASP.NET application permissions to a SQL Server 2000 database. You can read the article at
   http://support.microsoft.com/default.aspx?scid=kb;en-us;815154

The voting has closed in SQL Server Magazine's Instant Poll for the question, "What is your primary application programming language?" Here are the results (+/- 1 percent) from the 604 votes (deviations from 100 percent are due to a rounding error):

The next Instant Poll question is "Do you use SQL Server 2000 Developer Edition?" Go to the SQL Server Magazine Web site and vote for 1) Yes, we've used it for some time, 2) Yes, we got it when Microsoft reduced the price 3) No, but we plan to now that the price has dropped, or 4) No, and we don't plan to.
   http://www.sqlmag.com

3. READER CHALLENGE

(contributed by SQL Server MVP Umachandar Jayachandran, [email protected])

Congratulations to Willem Grobbelaar, a SQL Server DBA for Comparex Africa in Cape Town, South Africa, and Simon Lidberg, in Stockholm, Sweden. Willem won first prize of $100 for the best solution to the September Reader Challenge, "Restoring Database Backups." Simon won second prize of $50. You can find a recap of the problem and the solution to the September Reader Challenge at
   http://www.sqlmag.com/articles/index.cfm?articleid=40031

Now, test your SQL Server savvy in the October Reader Challenge, "A New View" (below). Submit your solution in an email message to [email protected] by September 18. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here's the challenge: Greg is a database analyst for a company hosting databases on SQL Server 2000 and 7.0 servers. He has several users creating objects for applications in their respective schema. The users don't have permissions to query system tables directly and have been explicitly denied access to system tables such as sysobject and syscomments. The users obtain most schema information from system stored procedures, information schema views, and metadata functions. Greg wants to provide users with a view that gives them details about computed columns on tables because this information isn't available from any system stored procedures or views. For simplicity, he wants to create a view modeled after the existing information schema view.

Help Greg create a view named INF_SCHEMA_COMPUTED_COLUMNS that satisfies the following requirements:

3. ANNOUNCEMENTS

(brought to you by SQL Server Magazine and its partners)

Microsoft Professional Developers Conference, Los Angeles, CA, October 26-30. Connect with the future of Microsoft platform software. See under the hood of Microsoft Windows Longhorn, SQL Server Yukon, Visual Studio Whidbey, and more. EARLY BIRD DEADLINE EXTENDED: Register by September 24th and save $300 off the standard price.
   http://lists.sqlmag.com/cgi-bin3/DM/y/ecjJ0FgQMn0BRZ0BCWi0Ao

SSWUG.org (www.sswug.org) provides resources, help, articles, scripts, news, links and much more on a daily basis on the use and support of SQL Server, Oracle and XML. Sign up for the daily newsletter.
   http://lists.sqlmag.com/cgi-bin3/DM/y/ecjJ0FgQMn0BRZ0BAaZ0Ah

5. RESOURCES

SQL Server Magazine readers often send in their solutions to puzzles that Itzik Ben-Gan poses in his T-SQL Black Belt column. This month and next month, Ben-Gan shares some of the best solutions he's received. In his September column "Try, Try Again," Ben-Gan shares two set-based solutions to the August relational-division puzzle. Read this September SQL Server Magazine article and see how your solution measures up at
   http://www.sqlmag.com/articles/index.cfm?articleid=39604

Gphipps wants to use T-SQL to attach a copied subscription database (an .msf file) but isn't sure whether he can because all the documentation he's read describes using Enterprise Manager to perform this action. Can gphipps use T-SQL? See what other DBAs have said, and offer your opinion, on SQL Server Magazine's T-SQL forum.
   http://www.sqlmag.com/forums/messageview.cfm?catid=29&threadid=18943

(contributed by Brian Moran, [email protected])

Q. I have a problem with developers cluttering my production server with Data Transformation Services (DTS) packages. The developers need permissions on the server, but I don't want them creating packages on it. How do I prevent certain users from creating new DTS packages on my server?

A. Although Microsoft doesn't provide a documented way to prevent users from creating new DTS packages, you can use the following technique to keep all but the craftiest users from saving packages on your server. Enterprise Manager uses the undocumented stored procedure sp_add_dtspackage to add package information to msdb. If you remove the permission for a user to execute this procedure, the user won't be able to use Enterprise Manager to save a DTS package to msdb. Msdb is the storage location if you select "SQL Server" as the storage location when you save the DTS package by using the Package Designer GUI. So, this tip will prevent your developers from saving DTS packages to your production server from the GUI.

A developer might get around this roadblock by using the DTS object model and a COM design tool such as Visual Basic (VB) to create a package, then saving the package to Meta Data Services on SQL Server. This technique is advanced, so removing the EXECUTE permission for sp_add_dtspackage will keep most SQL Server users from creating new packages because they won't be able to save them.

Use this tip with caution, and remember to check for behavior changes on your system after you apply a hotfix or service pack. I don't know of any damage that changing the EXECUTE permission for sp_add_dtspackage can cause, but you usually shouldn't tamper with undocumented system objects.

Send technical questions to [email protected].

6. HOT RELEASES (ADVERTISEMENTS)

HostMySite.com provides plans as low as $8.95/mo. for shared hosting or $129.00/mo. for dedicated servers. Our network boasts 99.9% uptime and phone support is available 24/7/365. Call 1-877-248-HOST or visit www.HostMySite.com to sign up today!
   http://lists.sqlmag.com/cgi-bin3/DM/y/ecjJ0FgQMn0BRZ0BCWj0Ap

SQL Server Magazine Connections runs concurrently with Microsoft ASP.NET Connections, Visual Studio Connections, and Microsoft Office System Connections. Register before the discount expires and receive 4 conferences for the price of 1. Check out the Harley-Davidsongiveaway:
   http://lists.sqlmag.com/cgi-bin3/DM/y/ecjJ0FgQMn0BRZ0ggP0AB

Receive a print subscription to SQL Server Magazine, full SQL Server Magazine Web site access, and a subscription to the SQL Server Magazine Master CD (2 CDs), for only $39.95 (US)! Click here now.
   http://lists.sqlmag.com/cgi-bin3/DM/y/ecjJ0FgQMn0BRZ0BCKs0Am

7. NEW AND IMPROVED

(contributed by Carolyn Mader, [email protected])

Isotupa Consulting announced ICI Database Editor Tool kit for Desktop, software for software developers and systems integrators who edit large, changing databases. The software helps you build applications quickly. For changed or new database structures, the software doesn't require coding, compiling, or deployment; you just reconfigure at the server. The software lets you create applications that build data editing forms at run time based on customizable configuration data. You can embed the module in your application and access data before, after, and during editing. You can define callback functions to manipulate data based on user input. ICI Database Editor Tool kit supports SQL Server, Oracle, OLE DB, and ADO.NET databases. You can purchase a site license for all your developers, and you can receive a year of free maintenance. Contact Isotupa Consulting at 519-569-8366.
   http://www.isotupaconsulting.com

TECA announced ImageTransferPlus 2.0, a graphical 32-bit Windows import and export utility that lets you easily transfer Binary Large Objects (BLOBs) and Character Large Objects (CLOBs) between the file system and your SQL Server database. The software prevents you from having to write custom file import and export programs. The integrated file system explorer lets you navigate the PC file system. The database explorer lets you interactively select database columns. A built-in graphical viewer lets you view many binary formats and text files. ImageTransferPlus can import and export most file types to and from a SQL Server image column by selecting the file and column through the GUI. For pricing, contact TECA at 503-901-6335.
   http://www.teca.com

8. CONTACT US

Here's how to reach us with your comments and questions:

SQL Server Magazine UPDATE is brought to you by SQL ServerMagazine, the only magazine completely devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
http://www.sqlmag.com/sub.cfm?code=ssei211x1y

Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.
http://www.winnetmag.net/email