SQL Server Magazine UPDATE—brought to you by SQL Server Magazine and SQL Server Magazine Connections
THIS ISSUE SPONSORED BY
Panorama Software
http://lists.sqlmag.com/cgi-bin3/DM/y/eRim0FgQMn0BRZ0BBI70A1
Get High-Speed Access to Article Archives
http://lists.sqlmag.com/cgi-bin3/DM/y/eRim0FgQMn0BRZ0KrA0Au
(Below COMMENTARY)
Faster Backup
http://lists.sqlmag.com/cgi-bin3/DM/y/eRim0FgQMn0BRZ0BBI80A2
(below NEWS AND VIEWS)
SPONSOR: PANORAMA SOFTWARE
Enter our drawing for the new Palm Tungsten W Handheld. Companies that use Panorama's NovaView Business Intelligence Platform build the best BI solutions for Microsoft Analysis Services. Read how Jelly Belly increased sales productivity through its deployment of Panorama NovaView to its entire sales force.
http://lists.sqlmag.com/cgi-bin3/DM/y/eRim0FgQMn0BRZ0BBI70A1
July 10, 2003—In this issue:
1. COMMENTARY
- Parting the Sea of Information
2. SQL SERVER NEWS AND VIEWS
- Fix Slow Connections and Query Failures
- Results of Previous Instant Poll: Web Seminar Training
- New Instant Poll: Authentication Choices
3. READER CHALLENGE
- July Reader Challenge Winners and August Challenge
4. ANNOUNCEMENTS
- SSMU Business Intelligence Online Mini-Series
- SQL Server Worldwide User's Group Help Center
5. HOT RELEASES (ADVERTISEMENTS)
- SQL Server Magazine Connections: 4-for-1 Offer
- Attention Visitors to www.sqlmag.com
6. RESOURCE
- What's New in SQL Server Magazine: A Different Setup
- Hot Thread: Security Best Practices
- Tip: Determining Tempdb's File Size
7. NEW AND IMPROVED
- Automatically Generate Insert Scripts
- Develop and Maintain Equivalent Scripts
8. CONTACT US
- See this section for a list of ways to contact us.
1. COMMENTARY
(contributed by Brian Moran, news editor)
I often touch on the topic of "drowning in a sea of information." Microsoft does a wonderful job of producing voluminous documentation to answer almost any question you ask. However, the company typically does a poor job of organizing the documentation so that customers can weave its products into useful, business-oriented solutions. The result is that the answer you need is probably out there, but you might have a hard time finding it and applying it to your needs.
Last week, I highlighted two SQL Server-related e-books about OLAP and data mining. While I was researching these books, I stumbled across a Microsoft Web site called ".NET Enterprise Servers Online Books." In addition to the data-mining book "Preparing and Mining Data with Microsoft SQL Server 2000 Analysis Services" that I mentioned last week, the site includes links to such titles as "Securing B2B XML Web Services with WSE," "Understanding and Troubleshooting Directory Access," "Disaster Recovery for Microsoft Exchange 2000 Server," and "Best Practices for Deploying Full-Text Indexing."
IT professionals will find the titles on the site interesting and useful. The e-books are a joint effort from Microsoft Consulting Services, the product teams, and customers who use the products in the real world. Microsoft says that the books "offer practical guidance, informed insight that helps you make intelligent decisions, and relevant code examples that you can use as a starting point for creating your own solutions."
Wow! What a wonderful idea. Why are books such as these important? They can help you learn how to apply answers and advice in practical ways. Efforts such as this new e-book site are good for Microsoft customers, and I hope that the company continues to add titles to the site—preferably titles about SQL Server.
Take a few minutes to visit the site, and let me know what you think. Does Microsoft do a good job of giving us the information we need to stitch together multiple technologies and products to build complete business solutions? If not, what could the company do better? Send me your thoughts, and I'll share the most interesting ideas in a future commentary.
GET HIGH-SPEED ACCESS TO ARTICLE ARCHIVES
The SQL Server Magazine Master CD provides realtime desktop access to the articles, code, and expertise published in SQL Server Magazine and T-SQL Solutions. Search by keyword, subject, author, or issue. Order your subscription today.
2. SQL SERVER NEWS AND VIEWS
Microsoft released two hotfixes for SQL Server. The first fix corrects a problem of slow connections to Analysis Services. The article "FIX: HTTP Connections to Analysis Services Server Computers Running Windows Server 2003 Are Slow" explains that HTTP connections to Analysis Services server computers running Microsoft Windows Server 2003 are slower than HTTP connections to Analysis Services server computers running Windows 2000. The problem occurs because of a change in Active Server Pages (ASP) behavior in Microsoft Internet Information Services (IIS) 6.0, which is included with Windows Server 2003. You can obtain the hotfix for this problem.
The second fix corrects a problem of cross-database query failures. The article "FIX: Cross Database Query Fails with Error 3624 and an Assertion Occurs in the Dbtable.cpp File" describes a query failure that occurs because of a retail assertion that prevents the query from running. To improve performance and avoid excessive locking operations, SQL Server 2000 caches database locks for each connection. The database-lock cache size is fixed. If a cross-database query holds more locks than the cache size allows, SQL Server doesn't cache the additional locks. However, when the cache is available, SQL Server sometimes attempts to put the uncached locks into the cache. This attempt is what causes the retail assertion. You can obtain the hotfix for this problem.
The voting has closed in SQL Server Magazine's Instant Poll for the question, "Have you ever attended a Web seminar for SQL Server training?" Here are the results (+/- 1 percent) from the 188 votes:
- 14% Yes, and I found it valuable
- 5% Yes, but I didn't find it valuable
- 51% No, but I'd like to
- 30% No, and I don't plan to
The next Instant Poll question is "What kind of authentication do you use in your SQL Server environment?" Go to the SQL Server Magazine Web site and vote for 1) SQL Server and Windows authentication (mixed), 2) Windows authentication, 3) Both, for different situations, or 4) I'm not sure.
http://www.sqlmag.com
SPONSOR: FASTER BACKUP
Slash backup/restore time, improve reliability—and lower costs. With EMC CLARiiON backup-to-disk storage solutions you can cut backup time by as much as one-third compared to tape. And restore in up to 80% less time. EMC CLARiiON. It's fast, reliable—and more affordable than you think. Get the free white paper, "Stepping Up to Disk Based Backup."
http://lists.sqlmag.com/cgi-bin3/DM/y/eRim0FgQMn0BRZ0BBI80A2
3. READER CHALLENGE
(contributed by SQL Server MVP Umachandar Jayachandran) Congratulations to Dave Wilson, a senior database administrator for Starbucks Coffee Company in Seattle, and Vadim Rapp, owner of Vadim Rapp Consulting in Park Ridge, Illinois. Dave won first prize of $100 for the best solution to the July Reader Challenge, "Writing a Stored Procedure." Vadim won second prize of $50. You can find a recap of the problem and the solution to the July Reader Challenge at http://www.sqlmag.com/articles/index.cfm?articleid=39491. Now, test your SQL Server savvy in the August Reader Challenge, "Trimming Blanks" (below). Submit your solution in an email message to [email protected] by July 17. 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: Christine, a database analyst for a company that has several SQL Server installations, needs to trim leading and trailing blanks from the data in a table's text column. The following code creates the sample table containing the data she's working with:
CREATE TABLE #t (
id int NOT NULL IDENTITY PRIMARY KEY CLUSTERED,
t TEXT NULL
)
INSERT INTO #t VALUES( 'a ')
INSERT INTO #t VALUES ( 'b ')
INSERT INTO #t VALUES ( 'c d ')
INSERT INTO #t VALUES ( 'e')
INSERT INTO #t VALUES ( ' ')
INSERT INTO #t VALUES ( ' f')
INSERT INTO #t VALUES ( ' g ' + space(7000) )
INSERT INTO #t VALUES ( ' h j k l m ')
INSERT INTO #t VALUES ( 'This is a test')
Help Christine write the code to trim all leading and trailing blanks in the t column. The logic should accomplish the same result as applying LTRIM() and RTRIM() system functions on string data but can't use these functions.
4. ANNOUNCEMENTS
(brought to you by SQL Server Magazine and its partners)
Don't miss the Business Intelligence Mini-Series, an advanced-level online training course for SQL Server professionals. This four-part Web seminar series will be August 6, 13, 20, and 27, 2003, from 1:00 P.M. to 2:00 P.M. Eastern time and taught by business technology professional Scot Reagin. Get complete details, including early-bird pricing info at
http://lists.sqlmag.com/cgi-bin3/DM/y/eRim0FgQMn0BRZ0BBJA0AD
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/eRim0FgQMn0BRZ0BAaZ0Ay
5. HOT RELEASES (ADVERTISEMENTS)
SQL Server Magazine Connections runs concurrently with Microsoft ASP.NET Connections, Visual Studio Connections, and Microsoft Office System Conference. Register now for the best discount, plus access to all four conferences for the price of one.
http://lists.sqlmag.com/cgi-bin3/DM/y/eRim0FgQMn0BRZ0ggP0AS
If you've been putting off subscribing to SQL Server Magazine, now is the time to act. Starting July 1, the last 24 issues of SQL Server Magazine online are locked down and available only to subscribers. For a limited time, subscribe at the best rates ever offered online!
http://lists.sqlmag.com/cgi-bin3/DM/y/eRim0FgQMn0BRZ0BAw30Ah
6. RESOURCES
When a query needs to find items with certain relationships, you can use relational division—or you can try some new tricks. In "A Different Setup," Itzik Ben-Gan shows you how to use "outside-the-box" thinking to creatively solve set-identification problems. Read this July SQL Server Magazine article at
http://www.sqlmag.com/articles/index.cfm?articleid=38812
KaliBaba is considering implementing SQL Server 2000 for internal users only. The new server won't have Internet access, so it won't require virus updates. KaliBaba wants to set up a firewall to restrict IP access to the SQL Server. If she sets up a firewall, can she also hide the IP from internal browsers? What best practices can you recommend to KaliBaba? See what other DBAs have said, and offer your advice, on SQL Server Magazine's Security forum at the following URL:
http://www.sqlmag.com/forums/messageview.cfm?catid=6&threadid=17071
(contributed by Brian Moran)
Q. How can I find the size of the files in tempdb when SQL Server was last started? I know that each time I stop and start SQL Server, it drops, then recreates tempdb. So, SQL Server resets the size of the tempdb files to their original size as of the last SQL Server restart or to the size the files were last manually set to through an ALTER command. I know that performance can decrease if my tempdb files need to grow substantially during normal processing. I'd like to see how much tempdb files have grown so that I can determine whether to manually set them to a larger size.
A. There isn't an obvious way to determine how much tempdb files have grown since the last time SQL Server was restarted. As much as Microsoft chastises us for directly accessing system tables, the answer to this question is an example of when you need to read system tables to get the information you seek. I don't have space to fully discuss the sysaltfiles table located in the master database or the sysfiles table located in each database. I can say that a row exists in the master..sysaltfiles table for each file in tempdb, and a corresponding row exists in the sysfiles table within tempdb. The size column in sysaltfiles will help you determine the size of the files within tempdb when SQL Server was last started. The size column in tempdb..sysfiles shows the current size of the files. These sizes will be different if SQL Server has auto-grown the files. The size column in each table tracks the number of 8K pages assigned to the files. Therefore, the query that the following code shows returns the original and current size of all files in the tempdb database.
SELECT
alt.filename
,alt.name
,alt.size * 8.0 / 1024.0 AS originalsize_MB
,files.size * 8.0 / 1024.0 AS currentsize_MB
FROM
master.dbo.sysaltfiles alt INNER JOIN tempdb.dbo.sysfiles files
ON
alt.fileid = files.fileid
WHERE
dbid = db_id('tempdb')
AND alt.size files.size
Send your technical questions to
[email protected]
7. NEW AND IMPROVED
(contributed by Carolyn Mader)
XpressApps announced mssqlXpress 1.2, software that automatically generates INSERT scripts for SQL Server databases. When you have two databases with the same structure and you want to transfer data from one database to another, insertXpress can insert a script that contains all the data you selected from the source database. You just have to run the software on the target database, and the software automatically inserts the data. InsertXpress can transfer all field types (i.e., image, binary, varbinary, and timestamp), limit the number of rows transferred from the tables, automatically insert table and column names in your WHERE statement, and replace or append the existing data in the target database. Pricing starts at $99 for a single-user license. Contact XpressApps at [email protected].
http://www.xpressapps.com
Red Earth Technologies released Equivalent Script 1.1, an SQL tool that develops entire SQL scripts once, then generates equivalent SQL scripts for multiple database servers. Equivalent Script generates SQL scripts for SQL Server, Microsoft Access, Oracle, and MySQL. The software generates scripts that don't require postediting and will run the first time. You can use the interface to create and generate scripts, or you can programmatically access the Document Object from within your application. Equivalent Scripts costs $395. Contact Red Earth Technologies at [email protected].
http://www.redearthtechnologies.com
8. CONTACT US
Here's how to reach us with your comments and questions:
- ABOUT THE COMMENTARY — [email protected]
- ABOUT THE NEWSLETTER IN GENERAL — [email protected]
(please mention the newsletter name in the subject line)
- TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
- PRODUCT NEWS — [email protected]
- QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
Customer Support — [email protected]
- WANT TO SPONSOR SQL SERVER MAGAZINE UPDATE?
More than 102,000 people read SQL Server Magazine UPDATE every week. Shouldn't they read your marketing message, too? To advertise in SQL Server Magazine UPDATE, contact Beatrice Stonebanks at [email protected] or 800-719-8718.
SQL Server Magazine UPDATE is brought to you by SQL Server
Magazine, 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
