Problem solve

Problem solve

Get help with specific problems with your technologies, process and projects.

• Calling an extended stored procedure in SQL Server 2005

  Find out why problems calling an extended stored procedure in SQL Server 2005 can involve security settings. Continue Reading

• Installing different SQL Server versions on one machine

  Different SQL Server versions can be installed on one machine, learn how and get the best practices for SQL Server installation and compatibility. Continue Reading

• Managing permissions in SQL Server Reporting Services

  SQL Server Reporting Services offers ease when creating and deploying SQL Server reports for your entire organization. But when it comes to SSRS security, there's some work to be done on your part. Continue Reading

• Examples of SQL Server stored procedures and parameters

  How can SQL Server stored procedures be helpful if you don't know they exist? Database architect Denny Cherry shares 18 examples of handy stored procedures and their parameters that Microsoft has not documented. Continue Reading

• Is it possible to search for rows containing underscores in SQL Server?

  Is it possible to do a SQL Server search for a row with particular strings containing an underscore? Learn how to structure the WHERE clause to search for such rows. Continue Reading

• SQL Server 2005 Express vs. Desktop Engine (MSDE)

  SQL Server 2005 Express Edition and the SQL Server Desktop Engine (MSDE) each provides a medium where database administrators can work before leaping to the full version of SQL Server. Windows expert Serdar Yegulalp examines these products to help ...Continue Reading

• Best SQL Server indexing strategies

  SQL Server supports only two index types for most data types: clustered and non-clustered. Learn how and why building the appropriate index type during your database design will significantly impact SQL Server performance.Continue Reading

• SQL Server filegroups for backup and restore

  SQL Server filegroups have hundreds of significant uses in your database. What will be your backup and restore strategy for these filegroups? Database administrator Eric Johnson examines filegroup sizes and scenarios and shows you how to optimize ...Continue Reading

• SQL Server patch pros and cons

  Are you afraid to patch your SQL Server systems? Installing a patch can bring your database server down and corrupt data. Yet, unpatched software leaves your system vulnerable to attacks by unruly insiders. IT security specialist Kevin Beaver ...Continue Reading

• Temporary tables in SQL Server vs. table variables

  Once you've written your T-SQL code, it's time to execute it. You'll most likely need a table in which to store data temporarily. SQL Server database architect Denny Cherry explains your four options while offering the pros and cons of using ...Continue Reading

• Managing identity columns with replication in SQL Server

  Managing identity columns with replicated tables in your SQL Server 2005 database requires some TLC. In these scenarios, see how to replicate tables with identity columns and also get options to manage identity seeds for replicated tables. You'll ...Continue Reading

• Differences between varchar and nvarchar in SQL Server with examples

  Discover the key differences between varchar and nvarchar for SQL Server data types from expert Serdar Yegulalp. Learn length requirements, how each data type is stored differently and how to mix and match them.Continue Reading

• T-SQL commands vs. XML AUTO in SQL Server

  XML functions may use many more resources than the standard T-SQL commands, depending on the amount of data. SQL Server database expert Michelle Gutzait shows performance implications for each when comparing the XML AUTO function with T-SQL commands.Continue Reading

• Identify performance bottlenecks in SQL Server

  Identifying and resolving bottlenecks is key to a high-performing SQL Server. Types of bottlenecks include memory, disk and CPU bottlenecks. SQL Server MVP Hilary Cotter explains how to locate these log jams using System Monitor, SQL Profiler and ...Continue Reading

• SQL Server queries with DMVs for examining bottlenecks

  Here, you'll find queries incorporating Dynamic Management Views (DMVs) that display information on some of the common SQL Server performance bottlenecks.Continue Reading

• Detect performance bottlenecks with these tools

  Detect performance bottlenecks in SQL Server with these four tools: Task Manager, System Monitor, SQL Profiler and Dynamic Management Views (DMVs).Continue Reading

• Building a data warehouse in SQL Server: Eight tips to get started

  When building a data warehouse using SQL Server technologies, there are some challenges you may face. SQL Server database expert Baya Pavliashvili addresses some of the most common issues.Continue Reading

• T-SQL error handling with Try...Catch blocks

  T-SQL Try…Catch blocks in SQL Server 2005 are key for transaction management via error handling. Also called exception handling, error handling now offers developers improved capabilities and system functions to handle errors when executing ...Continue Reading

• Optimize disk configuration in SQL Server

  Proper disk configuration can result in a lifetime of high performance for SQL Server databases. Go beyond storage capacity requirements and consider drive performance. Database architect Denny Cherry outlines best practices for designing your disk ...Continue Reading

• SQL Server cluster design: One big cluster vs. small clusters

  Clustering design in a SQL Server environment is a key strategy. You must consider options including load balancing, disk array failure, the number of nodes and instances.Continue Reading

• Performance analysis tools for SQL Server

  Just being equipped with SQL Server tools is not enough. Database administrators and developers need to know how to use and ultimately optimize SQL Server tools like Query Analyzer, Index Tuning Wizard and SQL Profiler. Serdar Yegulalp explains how ...Continue Reading

• Mimic SQL Server Management Studio reports for Reporting Services

  SQL Server Management Studio has standard reports that can be mimicked to appear in Reporting Services. Find out how to get the reports' source code to do so, and also how to add custom reports to Management Studio.Continue Reading

• 'Out-of-range datetime value' error in SQL Server when working with dates

  Have you ever been stumped by this error message: "conversion of char data type to datetime data type resulted in an out of range datetime value"? Contributor Serdar Yegulalp shares causes and fixes for the frequently seen error message in SQL ...Continue Reading

• Debugging SSIS packages in SQL Server

  Debugging SSIS packages is a feature in SQL Server 2005 that offers many methods for finding causes of problems. Contributor Eric Johnson takes you through a step-by-step process for two of these methods: breakpoints and data viewers.Continue Reading

• Monitoring server disk space in SQL Server

  Monitoring your SQL Server database files is an essential operation of all DBAs. Avoid downtime and data loss with the three methods described here to monitor auto-growths of database files.Continue Reading

• Maintain DTS packages in SQL Server 2005

  What if you've migrated to SQL Server 2005 and want to hold off upgrading your DTS packages to SSIS? Learn how to edit, maintain and even develop existing DTS packages using the SQL Server DTS Designer Tool.Continue Reading

• Optimize merge replication performance

  Improve merge replication performance with methods explained in this tip and foster a highly scalable SQL Server environment. SQL Server expert and Microsoft MVP Hilary Cotter explains the tuning process and helps you determine if merge replication ...Continue Reading

• Copy-only backups: Another useful tool in SQL Server 2005

  Copy-only backups of your database are a new option with SQL Server 2005. This useful backup tool doesn't interfere with your regularly scheduled backup sequences. If creating a spontaneous full backup is necessary, you'll avoid negative impacts to ...Continue Reading

• Create username and password for new SQL Server database

  I have created a SQL Server 2005 database and I plan to protect it with a username and password. I'm unclear as to how to do this. How can I make a security system for my new database with its security objects?Continue Reading

• Protect against SQL Injection by whitelisting

  OK, admittedly the best practice for guarding against SQL Injection attacks is to white-ist acceptable characters. However, given that knowledge is power, is there a comprehensive list of special characters (such as the single-quote or double-hyphen...Continue Reading

• Tracing statements in SQL Server 2000

  Use SQL Profiler in your production environment to target poorly performing T-SQL commands in SQL Server 2000 and SQL 2005. Microsoft database expert Michelle Gutzait guides you through SQL Trace, from how to use trace definition through ...Continue Reading

• Targeting SQL Profiler to determine performance problems

  Use SQL Profiler in your production environment to target poorly performing T-SQL commands in SQL Server 2000 and SQL 2005. Microsoft database expert Michelle Gutzait guides you through SQL Trace, from how to use trace definition through ...Continue Reading

• MDX functions: SCOPE, THIS and FREEZE

  MDX in SQL Server 2005 Analysis Services brings exciting improvements, including query support and expression/calculation language.Continue Reading

• CASE statements now supported by MDX script in SQL Server 2005

  MDX in SQL Server 2005 Analysis Services brings exciting improvements including query support and expression/calculation language.Continue Reading

• T-SQL in SSIS: The power and the weaknesses

  Using T-SQL vs. the Data Flow task in SQL Server Integration Services (SSIS) is a decision dependent on a number of factors. Serdar Yegulalp shares some things to consider, including data origin, operation complexity and stored procedure purpose.Continue Reading

• 32-bit to 64-bit SQL Server migrations

  Migrating from 32-bit to 64-bit in SQL Server is an involved process, but you can build a bridge away from any 32-bit dependencies. Serdar Yegulalp discusses 32-bit and 64-bit platform issues including data source providers, user-defined functions ...Continue Reading

• Replicated stored procedure options with SQL Server 2005

  Replicating stored procedures between SQL Server environments is a process for which you have many technologies to choose from, including native replication features, log shipping and manual and automated deployments.Continue Reading

• Clustered and non-clustered indexes in SQL Server

  Rules for using a clustered index vs. a non-clustered index are tough to decipher. In this tip, you'll see the fundamentals to consider when making your choice, along with some best practice techniques. Get an overview of tradeoffs and proper index ...Continue Reading

• Troubleshoot SQL Server queries and improve I/O

  SQL Server performance success often relies on I/O activity. Edgewood Solutions' Jeremy Kadlec first takes you through the steps applying Performance Monitor to troubleshoot I/O problems.Continue Reading

• Database security policies to think about

  Government and industry regulations are tightening up on information security policies. Is it time to update your organization's database security beyond basic policies for passwords and data backups? IT security specialist Kevin Beaver gives you ...Continue Reading

• SQL Server bulk-logged recovery

  Minimal space requirements for transaction logs and best performance for bulk operations are among the advantages to the bulk-logged recovery model in SQL Server. However, one disadvantage is that if there is a bulk-logged transaction in your backup...Continue Reading

• Data cleansing techniques in SQL Server

  Data that is collected and used for decision-making purposes must be accurate from the start in your SQL Server environment. Refer to this checklist of data cleansing techniques and ensure data accuracy.Continue Reading

• 32-bit vs. the 64-bit SQL Server performance surge

  The 64-bit platform in SQL Server is setting a new computing standard. Will you make the leap from 32-bit to 64-bit? Contributor Serdar Yegulalp compares the two editions and highlights 64-bit advantages, including sustaining additional concurrent ...Continue Reading

• Active/Active clusters in SQL Server

  Multi-instance, or Active/Active SQL Server cluster design is an involved process. To optimize high availability and performance, you must consider a number of components in ensuring both instances can run on a single node. Hilary Cotter walks you ...Continue Reading

• Call triggers in a stored procedure

  Can I call triggers in stored procedures or vice versa?Continue Reading

• SSIS package to filter false records

  I have a table, let's call it AlarmTriggers and it has a Boolean field pulled into it. When my software writes to it the table doesn't set the field to true. It's kept false. I want to use an SSIS package that filters all false records in the table ...Continue Reading

• SQL Server permission to create jobs and schedules in SSIS

  We're having a lot of trouble scheduling and editing SSIS jobs in SQL Server 2005. The SA has created a proxy account for us to create SSIS packages. We're able to create jobs and schedules, but they're not running. Also, once we create a job and a ...Continue Reading

• SQL Server query design: 10 mistakes to avoid

  SQL Server queries should guide you toward successful, trouble-free data retrieval. Edgewood Solutions' Jeremy Kadlec has put together a Top 10 list of query design mistakes to avoid.Continue Reading

• Extend SSIS packages with scripting and programming

  SQL Server Integration Services brings new meaning to importing data, compared to DTS. Microsoft also offers two methods of extending SSIS functionality. Contributor Serdar Yegulalp shares how to extend Control Flow and Data Flow with scripting, and...Continue Reading

• Logging for security compliance in SQL Server

  Logging for security compliance in SQL Server can be tricky. Recommendations come from all directions: vendors, auditors and lawyers. As a SQL Server administrator, it's your job to determine what's reasonable based on risk. Contributor Kevin Beaver...Continue Reading

• SQL Server Integration Services programming basics

  SSIS is more than a tool to move data around in SQL Server 2005. It creates a system where the data sets that you import and the packages you create are programmatic objects that can talk to one another, rather than existing as static data.Continue Reading

• Boost SQL Server data warehouse performance

  Boost SQL Server data warehouse performance with these tips on improving query performance by expert Jeremy Kadlec.Continue Reading

• Speed up SQL Server backup and restore time

  Backup and restore are perhaps the most critical tasks you'll run on SQL Server. They should run in minimal time as they both utilize system resources. In fact, restores require exclusive access to the database. Edgewood Solutions' Greg Robidoux ...Continue Reading

• Step-by-Step Guide: Test for a Trojan horse on your SQL Server

  SQL Sever sluggishness and other database and operating system woes can mean there's a Trojan horse in your midst. Before you spend countless hours troubleshooting, run these tests to rule out a malware infection.Continue Reading

• Step 1: Scan your SQL Server for malware

  If you don't have anti-virus software installed to protect your SQL Server, then get it on and running as soon as possible. Follow this step-by-step guide to test for a Trojan on your SQL Server.Continue Reading

• Migrating SQL Server 2000 DTS packages to SSIS

  SSIS is more than a replacement for SQL Server 2000's DTS packages. The scale of changes is vast and this SQL Server 2005 ETL platform more powerful. In his tip, contributor Joe Toscano helps make the migration as painless as possible by guiding you...Continue Reading

• Database backup and restore corruption you haven't considered

  Backups are common practice to minimize data loss. Equally important is scheduled routine maintenance, i.e. integrity checks. These checks ensure no corruption exists in your database on the SQL Server level. Taken a key step further to examine the ...Continue Reading

• Database security options to protect data in SQL Server

  Encryption and data separation in SQL Server are not easy or cheap methods. But customers want security controls in place to protect their sensitive data. In this tip, contributor Kevin Beaver shares alternative tools and techniques to protect ...Continue Reading

• Stored procedures vs. dynamic SQL: When should you use each?

  Stored procedures and dynamic SQL each have their place in the SQL Server world. You must consider parameters, tables being queried, the number of databases and so forth. In this tip, Serdar Yegulalp maps out the best scenarios for each.Continue Reading

• FAQ: SQL Server query errors and explanations

  Queries in SQL Server have many daunting components. To ease the pain of running a query we've compiled a dozen Frequently Asked Questions. Here, our experts address areas including how to fix code errors, speed up queries, show the query navigation...Continue Reading

• Restoring an existing database on the same server

  Restoring an older version of a database to the same server is an occasional duty for DBAs. Backup and Recovery expert Greg Robidoux takes you through the process in T-SQL and Enterprise Manager.Continue Reading

• Cross database access when executing stored procedures

  Executing a stored procedure that draws from tables on other databases requires the option of cross-database ownership chaining. SQL Server development expert shares what you'll need to investigate this server option.Continue Reading

• Service Broker in SQL Server: How and when to use it

  Service Broker allows database developers to write applications that work across multiple instances in SQL Server. Contributor Serdar Yegulalp explains the benefits of this queued messaging system.Continue Reading

• Restoring from previous SQL Server versions

  Restoring your SQL Server 7.0 or 2000 machine to 2005 may be fairly straightforward. Restoring an earlier version is another story entirely. Edgewood Solutions' Greg Robidoux explains how to restore an older database to a new SQL Server 2005.Continue Reading

• SQL vs. T-SQL

  SQL Server 2005 and T-SQL do have differences. The advantages and disadvantages would depend user environment as described by SQL Server 2005 expert Adam Machanic.Continue Reading

• SQL Server Reporting Services analysis and tuning

  Commands running in your report server may be preventing real-time performance. Analyze the impact of performance hogs and get help tuning Reporting Services.Continue Reading

• Understanding database requirements for report server

  Disk space requirements can vary due to many variables. Learn to understand your disk space by ,over time and during high-use periods, monitoring the size of your report server database .Continue Reading

• FAQ: SQL Server login and permissions troubleshooting

  Monitor failed logins, restrict account access or solve a permissions issue with these 10 SQL Server security FAQs.Continue Reading

• Database mirroring setup in SQL Server 2005

  SQL Server 2005 database mirroring allows you to automatically mirror database contents from one SQL Server database to another. Edgewood Solutions' Greg Robidoux explains how it works, what you need to make it work and how to get it up and running...Continue Reading

• IIS tips to secure SQL Server

  IIS security measures can be implemented outside SQL Server to protect databases from malicious attacks. CISSP Kevin Beaver offers 10 Internet Information Server tips to harden SQL Server security.Continue Reading

• SQL Server clustering best practices

  SQL Server clustering provides high availability to clients by sharing server resources over several nodes, but only when all of its dependencies are addressed. Contributor Hilary Cotter identifies clustering topologies, dependencies and best ...Continue Reading

• SQL Server 2005 Enterprise Edition vs. Standard Edition

  SQL Server expert Adam Machanic highlights the differences to consider when weighing the move to SQL Server 2005 Enterprise and Standard Edtions.Continue Reading

• Moving data between MySQL and SQL Server

  If your objective is to move data back and forth between MySQL and SQL Server, you must take into account data typing restrictions, normalization standards and migration tools.Continue Reading

• Merge replication pros and cons in SQL Server 2000

  Merge replication should typically be avoided when achieving high availability in SQL Server 2000, according to contributor Michelle Gutzait. She explains the pros and cons of using application load balancing with merge replication and offers a ...Continue Reading

• Step 5: Monitor and stabilize consolidated SQL Server databases

  Monitoring should allow you to proactively manage consolidated SQL Servers, looking for outages and performance problems. Get help in this step excerpted from Chapter 2, 'Planning your ...Continue Reading

• Configure RAID for maximum SQL Server I/O throughput

  Backup and restore is one of the most I/O intensive activities you will perform on your SQL Server. Edgewood Solutions' Greg Robidoux explains how to maximize I/O throughput by isolating disk activities and choosing the appropriate RAID level.Continue Reading

• Use cube partitions to improve Analysis Services performance

  Analysis Services performance may falter when reporting on huge volumes of data. Contributor Baya Pavliashvili explains how to use cube partitions to improve query performance and reduce downtime.Continue Reading

• Common DBMS table and column name restrictions

  SQL Server 2000 DBMS table and column name restrictions are detailed by expert Joe ToscanoContinue Reading

• Exception handling best practices in SQL Server 2005

  SQL Server 2005 brings you TRY/CATCH, a new T-SQL feature that helps find and address errors before they reach application code. Get best practices for working with the new feature.Continue Reading

• SQL Server 2005 backward compatibility issues to consider

  Do you fear a SQL Server 2005 upgrade will break server applications? Contributor Serdar Yegulalp addresses key backward compatibility problems.Continue Reading

• Performing incremental loads and extracting data

  SQL Server Security Expert Joe Toscano shares source data tables designed to easily perform an incremental load / extract.Continue Reading

• Generating multiple flat files from a single flat file source

  Generating multiple flat files from a single flat file source could be done by first creating a DTS package with Custom Active Script task.Continue Reading

• SQL Server collation conflict fixes

  Fixing SQL Server collation conflict errors when using temp tables in an application and your database collation differs from tempdb.Continue Reading

• Writing a generic audit trail trigger in SQL Server

  Writing a generic audit trail trigger in SQL Server to keep track of inserts, deletes, and updates within a table is explained in this expert response.Continue Reading

• SQL Server example: Heavy insertion process from OLTP and triggers

  This example demonstrates why and how to avoid locks when inserting a large amount of data from a file to a table row by row in Microsoft SQL Server.Continue Reading

• SQL Server trigger example: Cursors in SQL Server triggers

  This example shows how cursors in triggers could degrade Microsoft SQL Server performance, and provides an alternative method.Continue Reading

• SQL Server trigger example: INSTEAD OF triggers vs. AFTER triggers

  This example demonstrates when it's best to use INSTEAD OF triggers and when to implement AFTER triggers in Microsoft SQL Server.Continue Reading

• Designing and implementing triggers in Microsoft SQL Server

  Learn tips on SQL Server trigger design, including how to boost performance by grouping commands.Continue Reading

• SQL Server trigger example: One trigger vs. two for the same logic

  This examples explores if adding functionality to an existing trigger improves performance or if it's better to create a new SQL Server trigger.Continue Reading

• SQL Server trigger example: Nested triggers in SQL Server

  This example shows how to use triggers to summarize data for a table in Microsoft SQL Server that is heavily updated.Continue Reading

• Why use triggers in Microsoft SQL Server?

  The problem with triggers is they can't be seen. Learn how triggers work and get design considerations for SQL Server triggers.Continue Reading

• Using Microsoft SQL Server triggers

  Not sure why or when to use SQL Server triggers? This series breaks down the basics, including design considerations and performance issues, and shows examples of triggers.Continue Reading

• How to apply indexes in the real world

  The way an index is applied is generally dependent on the function of the table for which the index is created. Learn how to apply indexes in this book excerpt.Continue Reading

• Configure Windows Firewall to allow SQL Server connections

  To access a SQL Server instance on a computer protected by Windows Firewall, you must configure the security tool to receive incoming connections. This tip explains how.Continue Reading

• Working with schemas in SQL Server 2005

  If you're managing too many databases with too many objects, it may be time to take advantage of SQL Server 2005's ANSI SQL feature: schemas.Continue Reading

• Configure SQL Server 2005 TempDB for performance

  Some processing once reserved for the SQL Server 2000 transaction log, such as row versioning and online index rebuilds, has been moved to SQL Server 2005 TempDB, making it essential that you properly configure your database -- or suffer the ...Continue Reading

• Learning Guide: SQL Server performance tuning A to Z

  From testing and monitoring to tuning and troubleshooting, you're sure to find a tip or tool to help you resolve your SQL Server performance dilemmas in this guide.Continue Reading

• The pros and cons of data warehousing

  SQL Server expert Eric Brown explains the pros and cons of data warehousing.Continue Reading

• Using Surface Area Configuration to lock down SQL Server

  The SQL Server 2005 Surface Area Configuration tool allows you to manually enable only the services you absolutely need, minimizing the attack surface.Continue Reading

• Stored procedure: Determine last database backup

  You have dozens or hundreds of SQL Servers to administer and you just want to know when the last backup was taken for each database on each instance. This stored procedure will help.Continue Reading