Ask the Experts

Ask the Experts

• Stored procedure concurrency problems in SQL Server 2005

  Multiple readers can sometimes read the same row simultaneously causing a false result. SQL Server 2005 expert Adam Machanic suggests modifying the stored procedure or even employing SQL Server Broker.  Continue Reading

• Upgrading SQL Servers

  Migrating to SQL Server 2005 from SQL Server 2000 is a hefty feat when compared to upgrading from 7.0 to SQL Server 2000. Site expert Adam Machanic shares his advice on upgrading SQL Servers.  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

• Running a stored procedure in context with the master database

  SQL Server Development expert Greg Low explains how to access objects in the master database.  Continue Reading

• Monitoring failed login attempts in SQL Server 2000

  As failed login error reporting is disabled by default in SQL Server 2000, Security expert Steven Andres details how to enable this function.  Continue Reading

• Data warehousing 101

  The preference of a transactional database vs. Data Warehousing depends on the depth of the query being performed. SQL Server Development Expert Joe Toscano details the pros and cons.  Continue Reading

• SQL Server 2005 T-SQL debugging alternatives

  With no T-SQL debugger in SQL Server Management Studio you can use Visual Studio 2005's T-SQL debugger, but if you're not a fan of step-debugging, SQL Server 2005 expert Adam Machanic has detailed some alternatives.  Continue Reading

• Preventing SQL Server DTS package failures

  SQL Server DTS package failures when updating a table can be avoided by using FileSystemObject and ActiveX Script Tasks.  Continue Reading

• Create private temp tables for many users to execute same procedure

  A naming convention in a stored procedure allows you and your neighbors to execute the procedure simultaneously, according to SQL Server Expert Joe Toscano.  Continue Reading

• SQL Server features to identify rows in a table

  Features available in SQL Server to identify rows in a table are comparable to those offered by Oracle, such as Rownum and Rowcount.  Continue Reading

• Common DBMS table and column name restrictions

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

• Using SQLMail to send query results

  Sending query result by email requires SQLMail, which is essentially the integration of SQL Server & Exchange.  Continue Reading

• Permissions problem connecting to SQL Server with Excel and VBA

  Connecting to SQL Server with Excel and VBA could be a permissions issue and the user should first ensure the login is mapped to a valid database.  Continue Reading

• SQL Server and MSDE version compatibility

  SQL Server 2005 Expert Adam Machanic explains the version compatibility between SQL Server and other servers such as MSDE.  Continue Reading

• Sysindexes replacement in SQL Server 2005

  SQL Server 2005 has a set of internal tables which functions as SQL Server 2000's sysindexes. However, the user does not have access to the tables, but can get a glimpse of what they do.  Continue Reading

• Changing the 'SA' password on SQL Server 7.0 without old password

  SQL Server Security Expert Steven Andres explains how administrators of operating systems (OS) can change the 'SA' password on SQL Server 7.0 without the old password.  Continue Reading

• Securing IIS and SQL Server as part of an online platform

  Learn how to protect the platform between your database and the web beginning with keeping your DMZ machines as distant from your internal domain as possible.  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

• Handling metadata processes for SSIS projects

  Learn how to capture info for data transformation in this DTS package that appends data to an audit table.  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

• Tools and technique to move SQL Server data to Oracle

  Once you have the necessary tools to create a Data Source Name (DSN) which provides the Oracle connectivity details, you can specify Oracle as the destination in your DTS package and start moving.  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

• The pros and cons of data warehousing

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

• Implementing C++ Windows authentication

  SQL Server Development expert Greg Low explains how to implement C++ Windows authentication for users connecting to SQL Server 2000.  Continue Reading

• Encrypt a CSV file using stored procedure in SQL Server 2000

  SQL Server Development expert Greg Low explains how to encrypt a CSV (comma-separated values) file.  Continue Reading

• Copying SQL Server database tables

  SQL Server expert Greg Low explains how to copy tables from one database into another.  Continue Reading

• Visual Studio report processing error

  SQL Server expert Eric Brown sheds light on an error message received when trying to run a Visual Studio report in Windows.  Continue Reading

• Finding the latest altered date of a stored procedure

  SQL Server expert Greg Low explains how to find the latest altered date of a stored procedure.  Continue Reading

• Sysindexes.status vs. INDEXPROPERTY

  SQL Server 2005 expert Adam Machanic explains why the INDEXPROPERTY system tool is the best way to get relevant index information.  Continue Reading

• When to update index statistics

  SQL Server 2005 expert Adam Machanic explains why it is not necessary to update statistics after rebuilding indexes.  Continue Reading

• What is a SQL Server instance?

  SQL Server expert Adam Machanic's definition of a SQL Server instance.  Continue Reading

• Copying views from one database server to another

  SQL Server expert Joe Toscano tells explains how to use a stored procedure to copy a view in one database to another.  Continue Reading

• Passing column and table names as parameters

  DTS/SSIS Joe Toscano answers a user's question about passing column and table names as parameters within a stored procedure.  Continue Reading

• Differences between DTS and SSIS

  DTS/SSIS expert Joe Toscano offers some resources to explain the differences between DTS and SSIS.  Continue Reading

• Connecting to SQL Server using Visual Studio

  SQL Server expert Eric Brown provides some helpful information on connecting to SQL Server using Visual Studio.  Continue Reading

• How can I compare encrypted columns in SQL Server 2000?

  SQL Server security expert Steven Andres offers third-party apps to help compare encrypted columns.  Continue Reading

• How to secure ASP.NET applications that pull data from SQL Servers

  When your Web servers live on a different domain from your SQL Servers, you need to make sure your applications are secure. Security expert Steven Andres explains how to secure ASP.NET applications.  Continue Reading

• A sample query for students with most classes

  Expert Adam Machanic offers a sample query to find the names of the students enrolled in the largest number of classes.  Continue Reading

• 'Suspect' SQL Server database solution

  If you're SQL Server database is marked suspect, follow MVP Adam Machanic's advice by trying a database state in SQL Server 2005 called EMERGENCY.  Continue Reading

• Advice for becoming an expert DBA

  SQL Server 2005 expert Adam Machanic offers general guidelines for becoming an expert DBA.  Continue Reading

• What is the difference between T-SQL and "normal" SQL?

   Continue Reading

• Is there a maximum number of sockets that SQL can handle?

   Continue Reading

• Why is my SQL Server frequently corrupted?

   Continue Reading

• Changing date format of a flat file using VB.NET

  Development expert Greg Low explains how to use VB.NET to format a flat file into the datetime SQL datatype format.  Continue Reading

• Sum aggregate operation won't take varchar data type

  The sum or average aggregate operation cannot take a varchar data type as an argument. So how can this user sum the total? Development expert Greg Low explains.  Continue Reading

• How to speed up a composite nonclustered index-based query

  A query still responds slowly after implementing an index. Performance Tuning expert Jeremy Kadlec offers help troubleshooting the SQL Server problem.  Continue Reading

• Resolve errors received when restoring a database in SQL Server

  See how to restore a database after receiving a server error message by first running this command SQL Server as advised by our expert.  Continue Reading

• Developing a table-like structure in Visual Basic

  Expert Greg Low explains how to create table-like structures in Visual Basic, so they are not connected to any database.  Continue Reading

• SQL Server versions with data encryption/decryption support

  Versions of SQL Server that support data encryption/decryption are explained by SQL Server expert Greg Low.  Continue Reading

• Best approach for using stored SQL procedures in Visual C++

  Greg Low outlines six steps for using stored SQL procedures in Visual C++.  Continue Reading

• What's the best approach for restoring MSDB on another server?

  Expert Greg Robidoux explains how to restore MSDB on another server while also allowing users access.  Continue Reading

• Query Analyzer's replacement in SQL Server 2005

  What will replace the beloved and greatly-needed Query Analyzer tool in SQL Server 2005? Expert Adam Machanic explains.  Continue Reading

• Setting up automatic switchover between SQL Server instances

  In the event that your primary SQL Server 2000 Standard Edition box has a meltdown, is it possible to kill replication on an identical, secondary box to make that the new primary? Contributors Adam Machanic and Hilary Cotter offer their advice.  Continue Reading

• Overcoming SQL Server's 8060-byte row limit

  SQL Server 2005 expert Adam Machanic discusses an important feature called row overflow, which greatly relaxes the 8060-byte restriction.  Continue Reading

• SQL Native Client

  Don't know what SQL Native Client is and how it can help you? SQL Server 2005 expert Adam Machanic discusses what it can do for you.  Continue Reading

• SQL Server 2005 DDL vs. DML triggers

  SQL Server 2005 adds a new type of trigger -- DDL (Data Definition Language) triggers. Find out how they compare to "normal" DML triggers in this expert response.  Continue Reading

• SQL Service Broker and Microsoft Message Queuing

  SQL Server 2005 expert Adam Machanic discusses SQL Server 2005's native, queued messaging system, SQL Service Broker.  Continue Reading

• What is the SQLCMD tool used for?

  In this expert response, SQL Server 2005 expert discusses SQL Server 2005's command-line query tool.  Continue Reading

• SQLCLR permission sets

  SQL Server 2005 expert Adam Machanic discusses SQL CLR permission sets in this expert response.  Continue Reading

• Forced parameterization database option

  In this expert response, expert Adam Machanic discusses a new SQL Server 2005 setting called forced parameterization.  Continue Reading

• Relationship between SQL Service Broker and Microsoft Message Queuing

  SQL Server 2005 expert Adam Machanic discusses the relationship between SQL Server Broker and Microsoft Message Queuing.  Continue Reading

• Installing SQL Server client tools on Windows XP Pro

  Monitoring and Administration expert Kevin Kline discusses the installation of SQL Server client tools on Windows XP Pro.  Continue Reading

• Installing SQL Server 2000 Enterprise Edition on Windows XP

  Having trouble installing SQL Server 2000 Enterprise Edition on a machine that's running Windows XP. In this expert response, Monitoring and Administration Expert Kevin Kline discusses how to install SQL Server on Windows XP.  Continue Reading

• Storing XML data in SQL Server 2005

  SQL Server 2005 Expert Adam Machanic discusses storing XML data in SQL Server 2005 using the new XML datatype.  Continue Reading

• Getting scheduled DTS packages to run

  Troubleshooting your DTS packages can be frustrating. In this expert response, Monitoring and Administration expert Kevin Kline discusses why a DTS package may run when executed but not when scheduled.  Continue Reading

• CLR stored procedures versus T-SQL stored procedures

  Expert Adam Machanic discusses the performance differences between CLR and T-SQL stored procedures.  Continue Reading

• Aggregate functions in SQL Server 2005

  SQL Server 2005 expert Adam Machanic answers a commonly asked question regarding aggregate functions in SQL Server 2005.  Continue Reading

• Storing business objects as a SQL CLR user-defined type

  Thinking of storing business objects as SQL CLR user-defined types? SQL Server 2005 expert Adam Machanic discusses why you shouldn't.  Continue Reading

• Row versioning with SNAPSHOT isolation level

  Using "dirty" reads is a thing of the past. SQL Server 2005 expert Adam Machanic discusses SQL Server 2005's new snapshot feature.  Continue Reading

• Performance differences between Windows and Linux-based databases

  Get help making an apples-to-apples comparison of database performance in Windows vs. Linux systems. Performance and Tuning expert Jeremy Kadlec gives you several factors to consider.  Continue Reading

• SQL Server performance considerations when configuring a SAN

  If you are concerned about how well your SQL Server will perform with a storage area network (SAN), let Performance and Tuning expert Jeremy Kadlec guide you.  Continue Reading

• Query to return database name

  Global function DB_NAME() allows you to return the name of a database that a process is connected to. SQL Server 2005 expert Adam Machanic offers a sample query in this response.  Continue Reading

• Executing an Oracle stored procedure on SQL Server

  Oracle uses its own dialect of SQL called PL/SQL, while SQL Server uses T-SQL, making it difficult to get Oracle stored procedures to work on a SQL Server database. But there is a migration tool that will help.  Continue Reading

• Unknown cause of SQL Server 2000 corruption

  Performance and Tuning expert Jeremy Kadlec reviews troubleshoots what may be causing corruptions in a non-default instance of SQL Server 2000 running on Windows Server 2003.  Continue Reading

• Nullable fields in a table

  Can't decide whether to use nullable fields in a table. Development expert Andrew Novick discusses what to do.  Continue Reading

• Connecting Visual Basic to SQL Server

  If you've had trouble connecting Visual Basic to SQL Server, let expert Andrew Novick offer some basic advice to get you started.  Continue Reading

• Stored procedures in Crystal Reports

  Development expert Andrew Novick gives you the resources to improve your stored procedure writing skills.  Continue Reading

• Stored procedure like Microsoft Access cross tab query

  Expert Kevin Kline discusses cross tab queries in this tip.  Continue Reading

• Storing an image file in SQL Server

  Development expert explains the simplest way to store an image in SQL Server.  Continue Reading

• Character limit in a database field

  I have an MS SQL Server 2000 database with a field which is a varchar(255). My clients understand that the text descriptions which they enter to describe an event is limited to 255 characters; however, what is happening is that MS SQL is truncating ...  Continue Reading

• Security via authentication in Windows and authorization in SQL Server

  Using Windows NT and SQL Server, what is the best way to add security by using both authentication in Windows NT and then authorization in SQL Server? And what is the best way to have record-level security for SQL tables? Is there any information on...  Continue Reading

• Restoring a database with only the .mfd file

  Do you want to learn how to restore a database with only the .mfd file? Expert Greg Robidoux tells you how.  Continue Reading

• How can I transfer data from a SQL server to a mainframe without using FTP?

  How can I transfer data from a SQL server to a mainframe without using FTP? I have an extremely large amount of data to transfer and FTP takes too long.  Continue Reading

• SQL Server 2000 on Windows XP

  Will Microsoft Windows XP support SQL Server 2000? Find out in this tip.  Continue Reading

• Recovering SQL Server performance without sp_updatestats

  Alternatives to running sp_updatestats to recover SQL Server performance are offered by performance and tuning expert Jeremy Kadlex.  Continue Reading

• Why does our DTS package fail when executed from a separate server under different permissions?

  Our DTS package fails to execute when executed from a separate server under different permissions. Here are the specs:

  • Both Server1 and Server2 are on the same domain. Server1 is the SQL Server instance (Enterprise Edition) with SP4.

    <...  Continue Reading

  • Improve SQL Server performance by creating a transaction log file

    Creating an additional file for databases and transaction logs can not only provide additional space, it can also improve database performance. Monitoring and Administration expert Kevin Kline explains how.  Continue Reading

  • SQL Server data modeling tools

    Performance and tuning expert Jeremy Kadlec discusses SQL Server data modeling tools.  Continue Reading

  • How do I run a T-SQL query to loop through all databases?

    Find out how to run a T-SQL query to loop through all databases.  Continue Reading

  • Stored procedures looking at different databases

    Expert Andrew Novick reveals how to execute a store procedure in one database look at tables in another database.  Continue Reading

  • SQL Server 7.0 database creation using DTS or BCP

    How to create a SQL Server 7.0 database using DTS or BCP is discussed by expert Greg Robidoux discusses converting .BAK files.  Continue Reading

  • Enabling SQL Mail on SQL Server

    Expert Steven Andres describes why you should not enable SQL Mail on SQL Servers and the security risk associated with this feature.  Continue Reading

  • Stored procedures and remote procedure calls

    Expert Andrew Novick determines whether a stored procedure is also a remote procedure call.  Continue Reading

  • What is the difference between a clustered index and a non-clustered index?

    This expert response examines clustered indexes vs. non-clustered indexes.  Continue Reading

  • Using newid in SQL Server to insert records in parent-child tables

    In SQL Server parent-child tables, the child records seem to have no information about the unique ID given by SQL to the parent record. Developer expert Andrew Novick explains how to address this problem.  Continue Reading

  • All about data purging

    What is data purging? What technology or method can I use to purge data in SQL Server 2000?  Continue Reading

  • Comparing DDL and DML

    Is it possible to use the GENERATE SQL Script wizard to get a verifiably identical export file? We want to be able to checksum script files at different locations. So far, we can't seem to get enough consistency between two supposedly identical ...  Continue Reading

  • Query processing in relational systems vs. low-level query languages

    How does query processing in relational systems differ from the processing of low-level query languages?  Continue Reading

  • Equivalent code of sp_configure 'allow updates' in SQL Sever 2005

     Continue Reading

  • Built-in facility to encrypt column data

     Continue Reading