SQL Server Express UPDATE--August 21, 2006:What's the CLR and Should I Care?

SQL Server Express UPDATE--August 21, 2006:What's the CLR and Should I Care?

In this issue, you'll learn how the CLR works with SQL Server 2005 Express, how to back up your database to a default directory, and what you should know if you're running MSDE and you're planning to upgrade your OS to Vista.

Subscribe to SQL Server Magazine and make sure you add [email protected] to your list of allowed senders and contacts:
&nbsp &nbsp &nbsp https://store.pentontech.com/index.cfm?s=9&cid=51&promotionid=1491



FREE SQL Server 2005 Training CD-ROM!
&nbsp &nbsp &nbsp http://www.appdev.com/promo/RN00440

SPI Dynamics
&nbsp &nbsp &nbsp https://download.spidynamics.com/1/ad/sql.asp?Campaign_ID=70160000000CZT8

Announcing sqlSentry Standard!
&nbsp &nbsp &nbsp http://www.sqlsentry.net/sqlsentry-editions-standard.asp?ad=SS_Mag_HS_2006_08_21_A


======= Contents ======================

August 21, 2006


  • What's the CLR and Should I Care?


  • From the Community: Backing Up to a Non-Default Directory
  • Check It Out: MSDE Not Supported on Vista

    Resources and Events

  • Cross-Platform Data Roadshow
  • Microsoft TechEd: Developers
  • Consolidating SQL Server Deployments
  • Quest Software's SQL Server 2005 Backup and Recovery Challenge!

    Featured White Paper

  • Business Communications Trends: Integrating Fax Servers in MFP Environments

    Editors' Choice for Top SQL Server Express/MSDE Products

    Exclusive Email Offers

  • Monthly Online Pass--only $5.95 per month!
  • Save $40 on SQL Server Magazine

    ========== Sponsor: AppDev ====================================

    FREE SQL Server 2005 Training CD-ROM!
    &nbsp &nbsp &nbsp Start learning SQL Server 2005 today with cutting edge training from AppDev. Get a FREE SQL 2005 training CD (a $115 value!) taken directly from our new course. Click the link below for your FREE SQL Server 2005 training CD.
    &nbsp &nbsp &nbsp http://www.appdev.com/promo/RN00440


    1. ==== Commentary ====================

    What's the CLR and Should I Care?
    &nbsp &nbsp &nbsp by Michael Otey, [email protected]


    If you're just beginning to work with databases or you haven't kept up with all the new SQL Server 2005 features, then the relationship between the CLR and SQL Server 2005 Express is likely to be something of a mystery. Even if you're familiar with .NET programming, in which the CLR plays a major role, you still might be wondering how the CLR relates to a database.

    Most .NET programmers know that the CLR is essentially a runtime layer that sits between your .NET application and the OS. The CLR's job is to translate .NET program-generated instructions into native Win32 instructions that Windows OSs can execute. The primary benefits that the CLR provides are increased application security and stability. In addition, the CLR helps to protect the OS from problematic applications that might have memory leaks or buffer-overflow exploits. The idea behind the CLR is not new. Java, and its associated Java Virtual Machine (JVM) layer, was the first programming language to employ this type of architecture.

    So how does the CLR work with SQL Server Express? The CLR is integrated with all SQL Server 2005 editions, including SQL Server Express. Creating database objects by using CLR objects provides some key advantages. First, it lets you create database objects--such as stored procedures, triggers, and functions--by using any .NET language such as Visual Basic (VB) and C#. Before the CLR was introduced, you had to use T-SQL commands to create database objects. Although T-SQL has excellent set-oriented data- retrieval capabilities, the .NET languages are better at handling complex logic and mathematical operations. Using the CLR opens up additional capabilities in SQL Server database object programming that weren't available before.

    Second, T-SQL is designed to be a relational-database-oriented language and it doesn't include the ability to access resources outside of a relational database. In other words, T-SQL can't do things like read and write to the file system or registry. To give SQL Server the ability to access resources outside of a relational database, Microsoft added extended stored procedures, which are essentially native Win32 programs that run within the same in-memory process space as the SQL Server engine. Extended stored procedures fill the gap by providing access to external resources. But because these procedures run in the same process as the database engine, a bug in an extended stored procedure can bring down the SQL Server engine--not an ideal situation. However, integrating the CLR into SQL Server Express solves both of these problems. CLR objects can access external resources such as the file system and registry, but because CLR objects run within the confines of the CLR, they're isolated from the SQL Server engine, so a bug can't affect the functionality of the core database engine.

    Integrating the CLR into the SQL Server 2005 product line adds functionality to SQL Server Express and lets you create database objects that contain complex logic and access external resources. To take advantage of the CLR, you have to program CLR objects by using one of the .NET languages and you need to enable CLR functionality by using the SQL Server Surface Area Configuration tool. (For security reasons, the CLR is turned off by default). I hope I've demystified the CLR and you now realize that it's just another SQL Server development tool.


    *********** SPI Dynamics ******************************

    ALERT: How Hackers Launch SQL Injection Attacks
    &nbsp &nbsp &nbsp It's as simple as placing additional SQL commands into a Web Form input box giving hackers complete access to all your back- end systems! Firewalls and IDS will not stop such attacks because SQL Injections are NOT seen as intruders. Download this *FREE* white paper from SPI Dynamics for a complete guide to protection!
    &nbsp &nbsp &nbsp https://download.spidynamics.com/1/ad/sql.asp?Campaign_ID=70160000000CZT8


    Editor's Note: Be a SQL Server Innovator!
    &nbsp &nbsp &nbsp If you've developed a resourceful solution that uses SQL Server technology to solve a business problem, you qualify to enter the 2006 SQL Server Magazine Innovators Contest! Grand- prize winners will receive airfare and a conference pass to SQL Server Magazine Connections in Las Vegas, November 6-9, 2006, plus more great prizes and a feature article about the winning solutions in the January 2007 issue of SQL Server Magazine. Contest runs through September 1, 2006, so enter today!
    &nbsp &nbsp &nbsp http://www.sqlmag.com/awards/?type=innovator


    2. ==== Features =======================

    From the Community: Backing Up to a Non-Default Directory


    Author's Note: I received an interesting question from a reader about backup and restore and thought I'd share the information. The Jump Start column will be back in the next edition of the newsletter.

    Q: I tried to back up my database by using T-SQL commands. However, when I entered the following code to back up my database to a non-default directory, the process failed.

    USE master
    EXEC sp_addumpdevice 'disk', 'testing_Backup', 'C:\testing.Bak'
    BACKUP DATABASE testDB TO testing_Backup

    What should I do to back up and restore my database from a non- default directory?
    -- Chau Cheng Chai

    A: You raise an interesting question, Chau Cheng Chai. SQL Server 2005 Express is not restricted to backing up to just the default directory; you can back up to other directories as well. You didn't send me the error message you received while attempting the backup operation, but I can tell you that the two most likely causes of backup and restore errors usually pertain to the database or file-system permissions or locations. In your code, you attempted to back up your database to the root directory. The sp_addumpdevice can't use the root directory as a backup target. Instead, you should create another directory, for example c:\sqlbackup. Try the following code:

    USE master
    EXEC sp_addumpdevice 'disk', 'testing_Backup',
    BACKUP DATABASE testDB TO testing_Backup

    Hope this helps, and as always it's great to hear from our readers.
    -- Michael Otey, [email protected]

    ***** HOT SPOT: InterCerve ************************

    Everything you need for a small- to mid-sized SQL Server environment
    &nbsp &nbsp &nbsp The award-winning power of sqlSentry is now available at a lower price point--$395 per instance. That's a 60 percent savings!
    &nbsp &nbsp &nbsp http://www.sqlsentry.net/sqlsentry-editions-standard.asp?ad=SS_Mag_HS_2006_08_21_B


    EDITORS' NOTE: Regional Events Cover Four Key Interoperability Topics
    &nbsp &nbsp &nbsp Are you a Windows fan, a UNIX diehard, or a Linux lover? Check out TechX World, an OS-agnostic event designed to give you the insider's tips on coping with your "Windows Plus" world. You'll get to spend a day with technical experts Michael Otey, Gil Kirkpatrick, Dustin Puryear, and Randy Dyess.
    &nbsp &nbsp &nbsp Designed specifically for IT professionals who work in a "Windows Plus" environment, TechX World is a four-track, one-day event featuring information about OS interoperability, data interoperability, directory and security integration, and virtualization. The content will focus on interoperability tips to help make disparate systems work well together.
    &nbsp &nbsp &nbsp The regional event series will visit four cities between October 24 and November 2, including Washington DC, Chicago, Dallas, and San Francisco. Attendees who register before August 31 will receive early bird pricing and a one-year subscription to Windows IT Pro. At $129 per person for four tracks and a full day of learning, it's worth sending the entire team to make sure you cover all the sessions. For complete agenda and speaker details, go to
    &nbsp &nbsp &nbsp http://www.techxworld.com/registration/index.cfm?code=

    Check It Out: MSDE Not Supported on Vista
    &nbsp &nbsp &nbsp by Michael Otey, [email protected]

    If you're currently running Microsoft SQL Server Desktop Engine (MSDE) and you're planning to upgrade your desktop OS to Windows Vista, you might want to also upgrade from MSDE to SQL Server 2005 Express before your Vista migration because Microsoft won't be supporting MSDE on Vista. For more information, check out Mike Wachal's blog at
    &nbsp &nbsp &nbsp http://blogs.msdn.com/sqlexpress/


    3. ==== Resources and Events ===========

    Cross-Platform Data Roadshow
    &nbsp &nbsp &nbsp Oracle professionals will cover key concepts about Oracle and SQL Server in enterprise database computing. This event provides invaluable information including: benefits of 64-bit computing on the Windows platform, SQL Server BI for Oracle, high-availability proof points for SQL and Oracle and much more.
    &nbsp &nbsp &nbsp http://www.windowsitpro.com/roadshows/sqloracle/?code=0823emailannc

    Microsoft TechEd: Developers, 7 to 10 November 2006, Barcelona, Spain
    &nbsp &nbsp &nbsp Four days that will take you months ahead of the game! Get deep-dive technical training, information, and community resources focusing on building software solutions with Microsoft development tools. Visit the Web site for further information and register before the Early Bird deadline of 29 September 2006 to save 300 euros.
    &nbsp &nbsp &nbsp http://www.microsoft.com/europe/teched-developers/

    Consolidating SQL Server Deployments
    &nbsp &nbsp &nbspDoes your SQL Server deployment suffer from low utilization rates, suboptimal availability, and management challenges? Learn to overcome these difficulties by deploying a database utility that can help lower your cost of ownership by 70% and can react to changes within 30 seconds. Live Web Seminar: Tuesday, August 22
    &nbsp &nbsp &nbsp http://www.sqlmag.com/go/seminars/polyserve/consolidatesql/?partnerref=0821emailannc

    Quest Software's SQL Server 2005 Backup and Recovery Challenge!
    &nbsp &nbsp &nbspTest your SQL Server 2005 Backup and Recovery Skills--you could win a $100 American Express Gift Card! Take the quiz now!
    &nbsp &nbsp &nbsp http://popquiz.sqlmag.com/quest


    4. ==== Featured White Paper ============


    Integrating Fax Servers in MFP Environments
    &nbsp &nbsp &nbsp Did you know that wasteful processes can drive the cost of document management and output to as high as 10 to 15 percent of your company's annual revenues? Download this free white paper today and find out how you can use fax solutions to achieve cost control, security and compliance, increased workflow and more.
    &nbsp &nbsp &nbsp http://www.windowsitpro.com/go/whitepapers/Faxback/faxing?code=0821featwp


    WANTED: Your reviews of products you've tested and used in production. Share your experiences and ratings of products to "[email protected]" and get a Best Buy gift certificate.


    5. Editors' Choice for Top SQL Server Express/MSDE Products
    &nbsp &nbsp &nbsp by Blake Eno

    SQL Server Magazine recently announced the winners of its 2006 Editors' Choice Awards. In the SQL Server Express/MSDE category, Vale Software Express Agent was the platinum winner. This product lets you create and schedule jobs that can automatically perform routine functions such as backup maintenance and database backup. The gold winner, Layton Technology's Layton Database Manager, provides a graphical database-management console that lets you create, view, and manage your databases and their objects. You'll find more information about these products and their functionality and the complete list of winners in the 2006 Editors' Choice Awards at
    &nbsp &nbsp &nbsp http://www.sqlmag.com/Articles/ArticleID/50660/50660.html

    6. ==== Exclusive Email Offers ====

    Monthly Online Pass--only $5.95 per month!
    &nbsp &nbsp &nbsp Includes instant online access to every article ever written in SQL Server Magazine, plus the latest digital issue. Order now:
    &nbsp &nbsp &nbsp https://store.pentontech.com/index.cfm?s=9&promocode=eu2168um

    Save $40 on SQL Server Magazine
    &nbsp &nbsp &nbsp Subscribe to SQL Server Magazine today and SAVE up to $40! Along with your 12 issues, you'll get FREE access to the entire SQL Server Magazine online article archive, which houses more than 2300 helpful SQL Server articles. This is a limited-time offer, so order now:
    &nbsp &nbsp &nbsp https://store.pentontech.com/index.cfm?s=9&promocode=eu2168us

    ==== Contact Us ====

    About the [email protected]
    About the [email protected]
    About technical questions--
    About product [email protected]
    About your [email protected]
    About sponsoring an issue of SQL Server Express UPDATE--Richard Resnick, [email protected]

    SQL Server Express UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.
    &nbsp &nbsp &nbsp https://store.pentontech.com/index.cfm?s=9&cid=51&promotionid=1491

    Manage Your Account
    You are subscribed as %%$email%%. To unsubscribe from this email newsletter, click here
    &nbsp &nbsp &nbsp http://lists.sqlmag.com/u?id=%%SUBSCRIBER_ID_TAG%%

    To manage your email account, simply log on to our Email Preference Center.
    &nbsp &nbsp &nbsp http://www.sqlmag.com/email

    View the SQL Server Magazine Privacy Policy.
    &nbsp &nbsp &nbsp http://www.sqlmag.com/aboutus/index.cfm?action=privacy

    SQL Server Magazine is a division of Penton Media, Inc.
    221 East 29th Street
    Loveland, CO 80538
    Attention: Customer Service Department

    Copyright 2006, Penton Media, Inc. All Rights Reserved.


  • Hide comments


    • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

    Plain text

    • No HTML tags allowed.
    • Web page addresses and e-mail addresses turn into links automatically.
    • Lines and paragraphs break automatically.