=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= SEARCHDATABASE.COM | Database Administrator Tips March 6, 2002
Essential tools and advice for the enterprise DBA: http://searchDatabase.techtarget.com/tips/ =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= IN THIS ISSUE | Table of Contents 1. DATABASE ADMINISTRATOR TIPS - SQL: Comparing trees in the nested sets model - Oracle: Another way to initialize variables in SQL*PLUS - SQL Server: An automatic script to select all columns in a table - More recent tips from members 2. IMPORTANT ANNOUNCEMENTS AND LINKS - Fabian Pascal answers your normalization questions - Ask the Expert: Database design and SQL questions answered - NEW Online Event: Bill Inmon on the sorry state of metadata - Featured Topic: Stump the Database Experts ___________________________________________________________________ ******************** FREE WINDOWS CONFERENCE ********************* Don't miss our Windows Decisions conference May 8-10 at the Hilton Chicago Hotel. Attend and discover: ** How to succeed with Active Directory ** Hidden tactics to lower your TCO ** Best practices for Windows 2000 systems management ** End-to-end network administration strategies ** How to solve your top 10 Windows interoperability problems ** And more... View full session info and apply today for FREE attendance at http://ad.doubleclick.net/clk;3903337;5058249;m?http://www.windowsdecisions.com/ ___________________________________________________________________ ___________________________________________________________________ THIS WEEK'S TIPS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= COMPARING TREES IN THE NESTED SETS MODEL | by Joe Celko In his continuing series of advanced SQL tips, Joe offers some code that compares nodes and structures of trees in the Nested Sets model for hierarchies. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci805976,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ANOTHER WAY TO INITIALIZE VARIABLES IN SQL*PLUS | by Robert Jones Everyone has some way to inititalize variables in SQL*PLUS. My favorite is to use the "col" directive with "new_val" option. This approach does not require a spoolfile making it somewhat easier to use in SQL*PLUS coding choirs. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci806007,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= GENERATING AN AUTOMATIC SCRIPT TO SELECT ALL COLUMNS IN A TABLE by Eli Leiba Here's a script that uses the derived table technique to generate an automatic script for selecting all columns in a table. >> CLICK for the full tip... http://www.searchDatabase.com/tip/1,289483,sid13_gci808536,00.html __________________________________________________________________ MORE RECENT TIPS FROM SEARCHDATABASE MEMBERS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= FINDING ASCII REPRESENTATIONS OF INTEGERS VIA SQL | by Karen Porter http://www.searchDatabase.com/tip/1,289483,sid13_gci804491,00.html You may sometimes want to include a carriage return or other character into an SQL statement. Single tics are especially confusing due to breaking them on and off to be effective. This tip shows you how to use the CHR() function to write single tics into SQL. If you forget what integer is required in the CHR() function, you can use the script to determine what integer you should use. A STORED PROCEDURE TO EXPORT/IMPORT DATA USING BCP | by Eli Leiba http://www.searchDatabase.com/tip/1,289483,sid13_gci804489,00.html Here is a SQL Server stored procedure that can be run from a master database to export or import a table to/from a given file path+name from/to a given database. SQL*PLUS FILE FINDER FOR WINDOWS | by Dusan Djuric http://www.searchDatabase.com/tip/1,289483,sid13_gci799189,00.html You are in an Oracle SQL*Plus session in Windows. Somewhere on your machine is the one script you need, but where? The script name is something like my*query.sql, but you're not sure. What do you do? Here's a handy way to find it fast. WHAT SQL IS RUNNING AND WHO IS RUNNING IT? | by Rohit Sinha http://www.searchDatabase.com/tip/1,289483,sid13_gci798210,00.html Many times you may need to find what SQL is currently running and which user is running it. The SQL in this tip runs on Oracle versions 7 and 8.1.7 and gives the SID, Serial#, SQL text, and the Oracle and OS user running it. The SID and serial number information may be used to kill the particular session if required. Rohit has the script saved as whatdousers.sql and just types @whatdousers at the SQL prompt. Hundreds more free tech tips submitted by members: http://searchdatabase.techtarget.com/tips/0,289484,sid13_tax281808,00.html Share your knowledge, submit a tip: win a CENDYNE CD-RW DRIVE http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz805991_cts805990,00.html ___________________________________________________________________ IMPORTANT MESSAGES AND LINKS: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= YOUR INTEGRITY, NORMALIZATION, AND RELATIONAL MODEL QUESTIONS ANSWERED | by Fabian Pascal In this month's column, Fabian tackles some thorny questions asked during his "Live Expert Q&A" on searchDatabase last December. Read his typically irascible responses and forceful arguments on topics such as: * The difference between logical and business models * Denormalization and performance * The lack of full normalization technology among database vendors * Integrity constraints and SQL DBMS's * Normalization and data warehouses * Fabian's favorite RDBMS! Read the column here: http://www.searchDatabase.com/tip/1,289483,sid13_gci804576,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= ASK THE EXPERT | This week's toughest database questions answered Database design: Using foreign keys to associate managers/employees http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid451262_tax285653,00.html Database design: Adding reviews to a database of books and authors http://www.searchDatabase.com/ateQuestionNResponse/0,289625,sid13_cid451261_tax285653,00.html SQL: Converting a character column containing dates to a DATE column http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid451066_tax285649,00.html SQL: What is a primary key? Composite PK? Foreign key? Tuple? http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid451070_tax285649,00.html Dozens more expert answers here: http://searchdatabase.techtarget.com/ateExperts/0,289622,sid13,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= LIVE EXPERT Q&A | Metadata: No longer a second-class citizen Who: Bill Inmon When: March 14, 2002 at 2:00 PM EST (19:00 GMT) The father of the data warehouse discusses how the world of metadata has arrived in such a sorry state and why your organization must develop a metadata strategy to succeed. >> CLICK: http://searchdatabase.techtarget.com/onlineEvents/0,289675,sid13,00.html =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= STUMP THE DATABASE EXPERTS | SearchDatabase Featured Topic Fancy yourself a database know-it-all? Our panel of 14 industry leaders and super-geeks begs to differ! If your technical question stumps our experts, you'll win a book of your choice in our store. Submit your question inside... >> CLICK for our current Featured Topic... http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci805176,00.html >> CLICK for previous Featured Topics... http://searchdatabase.techtarget.com/featuredTopics/0,290043,sid13,00.html ___________________________________________________________________ *********************SEARCHDATABASE CONTACTS*********************** TIP MAILBOX, (mailto:[EMAIL PROTECTED]) >> Submit your original tip here. TIM DICHIARA, Site Editor (mailto:[EMAIL PROTECTED]) >> Send your feedback and original articles. SARA CUSHMAN, Assistant Editor (mailto:[EMAIL PROTECTED]) >> Send your White Papers and favorite Web sites. ED PARRY, News Editor (mailto:[EMAIL PROTECTED]) >> Send your product announcements and poll ideas. TOM CLICK, Sales (mailto:[EMAIL PROTECTED]) >> Sponsor this or any other TechTarget newsletter. ___________________________________________________________________ **********************ABOUT THIS NEWSLETTER************************ Created by TechTarget (http://www.techtarget.com) TechTarget - The Most Targeted IT Media Copyright 2002, All Rights Reserved. If you no longer wish to receive this newsletter simply reply to this message with "REMOVE" in the subject line. Or, visit http://searchDatabase.techtarget.com/register and adjust your subscriptions accordingly. If you choose to unsubscribe using our automated processing, you must send the "REMOVE" request from the email account to which this newsletter was delivered. Please allow 24 hours for your "REMOVE" request to be processed.