Ask the Experts

Ask the Experts

• How do the T-SQL commands TRUNCATE TABLE and DELETE differ?

  TRUNCATE TABLE and DELETE are T-SQL statements with similar functions, but that doesn't make them interchangeable. They operate differently and use different amounts of SQL Server resources.  Continue Reading

• What is the benefit to Microsoft SQL Server Express on Azure?

  Microsoft recently announced that it has added Microsoft SQL Server Express to Azure. Expert Robert Sheldon shares what he sees as the benefits and pitfalls.  Continue Reading

• What to consider when deploying In-memory OLTP in SQL Server 2014

  In this expert answer, Michael Steineke from Edgenet talks about what to consider when deploying In-memory OLTP in SQL Server 2014.  Continue Reading

• Can SQL combine tables like these? Doubtful

  Can SQL combine tables where the values don't relate to one another? One expert says it's difficult unless you redesign the tables.  Continue Reading

• SQL PIVOT helps merge results into a single row

  SQL Server expert Denny Cherry shows how code can be used to the same effect as a SQL PIVOT statement.  Continue Reading

• Five steps for a successful backup of a stored procedure in SQL Server

  Follow these steps to use Management Studio for SQL 2005 for a successful SQL Server stored procedure backup.  Continue Reading

• Tips for tuning SQL Server 2005 to improve reporting performance

  There are a few things you can do to tune SQL Server for improved reporting performance. Here are some generic and Reporting Services-specific techniques.  Continue Reading

• Manipulate column names in a SQL Server table

  Learn SQL Server commands to manipulate column names in a table and turn column names into variables or rename SQL columns.  Continue Reading

• SQL Server trigger vs. stored procedure to receive data notification

  Learn why using a SQL Server stored procedures vs. a trigger can be a preferred method to retrieve data notification from a database field.  Continue Reading

• Can I encrypt and restore a database backup in SQL Server 2005?

  Discover the best solutions for encrypting, decrypting and restoring a database in SQL Server 2005.  Continue Reading

• Storing and searching for image files in SQL Server

  Learn about working with image files in SQL Server, including advice on using queries to locate them, in this expert answer.  Continue Reading

• Licensing a standby server for SQL Server replication

  Is a license needed for a standby server if it's used for SQL Server replication? Get that answer and a comparison of SQL Server log shipping to replication.  Continue Reading

• SSIS error message due to installation problem on SQL Server 2005

  If you are receiving an error message in SQL Server Integration Services (SSIS), your problem might be with the installation of the SQL Server 2005 client tools.  Continue Reading

• Using MDX and UDM in a SQL Server Analysis Services environment

  Learn the specifics of Multidimensional Expressions (MDX) and the Unified Dimensional Model (UDM) in a SQL Server Analysis Services environment.  Continue Reading

• Configuring SQL Server with a changed computer name

  Find out how to reconfigure all aspects of your SQL Server after your computer name has been changed.  Continue Reading

• Maintain large SQL Server database and resolve website 'Timeout Error'

  If you have exceptionally large SQL Server databases, refer to these best practices for proper database maintenance to steer clear of a website 'Timeout Error.'  Continue Reading

• How to retrieve SQL Server database disk space in use

  Find out where to look for the current amount of SQL Server database disk space being used in your environment.  Continue Reading

• Tool to synchronize two SQL Server databases

  Discover a tool that synchronizes two SQL Server databases allowing changes to one SQL Server database to update on the other.  Continue Reading

• Use SQL Profiler to find long running stored procedures and commands

  Troubleshoot long running stored procedures with SQL Server Profiler by adding events to find the command with high run time.  Continue Reading

• How can I export stored procedures in SQL Server 2005?

  Export stored procedures in SQL Server 2005 with these step-by-step instructions in SQL Server Management Studio.  Continue Reading

• Workaround to Reporting Services error in SQL Server Express

  Discover a SQL Server workaround to fix an error that pops up when trying to view a report in the Report Manager when using Reporting Services in SQL Server Express.  Continue Reading

• What is the code to restore SQL Server databases in VB?

  Learn how to restore a SQL Server database in VB.Net using this ADO.Net code sample provided by our expert.  Continue Reading

• Using BULK INSERT to insert rows from SQL Server dataset to table

  Learn how to use BULK INSERT to import all SQL Server dataset rows to a table.  Continue Reading

• SQL Server query to import database names

  Discover a query that will import all database names into your SQL Server.  Continue Reading

• Stored procedure for a BULK INSERT to SQL Server 2005 text file

  Discover a stored procedure using the BULK INSERT command to import multiple tables to a SQL Server 2005 text file.  Continue Reading

• Creating a login in SQL Server 2000 Enterprise Manager

  Find how to create a SQL Server 2000 login account and then set user account rights to specific databases with "db_owner."  Continue Reading

• SQL Server connection lost when SA password is changed

  Learn why SQL Server 2000 connection is lost on the client side when database administrator changes 'SA' password on the SQL Server domain.  Continue Reading

• Creating a SQL Server user authentication schema

  Learn how to create a SQL Server user authentication schema having password and tracked data changes requirements and how it involves Windows authentication.  Continue Reading

• Could a join of encrypted SQL Server data have a problem?

  When encrypting SQL tables that have joins in SQL Server 2000, learn about possible problems that may arise with different data values in those tables.  Continue Reading

• How to set SQL Server password for SA login

  Learn how to set a SQL Server password to an SA login and why you can not set this account for access to separate SQL Server databases.  Continue Reading

• Code to connect SQL Server 7.0 to Visual Basic 6.0

  Get the code to connect SQL Server version 7.0 to Visual Basic 6.0.  Continue Reading

• Adding non-clustered indexes to SQL Server columns

  Adding non-clustered indexes to SQL Server columns and tables frequently queried is possible. Learn how to add non-clustered indexes to SQL Server databases.  Continue Reading

• Merge replication to keep SQL Server databases in sync

  Keep SQL Server databases in sync with merge replication and limit connectivity issues. Use this resource for help setting up merge replication.  Continue Reading

• How to increase server memory used in SQL Server 2000

  Change SQL Server memory settings by starting with sp_configugure to view configurations. Next follow these steps to take with the operating system (OS).  Continue Reading

• Retrieve deleted tables in SQL Server

  Have SQL Server tables been deleted from an application? Find how to retrieve the tables via Lumigent's Log Explorer.  Continue Reading

• Should developers have permissions to SQL Server production queue?

  Should developers be granted permissions to production queue in a SQL Server environment? See why expert Greg Low suggests proc access by WITH EXECUTEAS.  Continue Reading

• Schedule stored procedure and save output to network drive

  Find how to save stored procedure output to the network drive via SQL Server Management Studio.  Continue Reading

• Query to find SQL Server database names created by users

  Find how to query in SQL Server to get all database names created by users.  Continue Reading

• Copy stored procedure between SQL Server 2005 databases

  Copying stored procedures between databases on the same SQL Server 2005 can be done through scripting the stored procedures with this method.  Continue Reading

• Problem attaching stored procedure and SQL Server trigger

  Learn why using the sp_OACreate stored procedure with a SQL Server 2005 trigger, is likely to result in a permissions problem.  Continue Reading

• Is it possible to find disabled indexes in SQL Server 2000?

  If you're working with SQL Server 2000 and trying to find a disabled index, here's something you should know.  Continue Reading

• Should I use the SQL Server INSTEAD OF trigger to update a table, or a regular trigger?

  Find how to use the INSTEAD OF trigger to update a SQL Server table for myDate and myTime.  Continue Reading

• Command to seed identity column in a SQL Server table

  Take a look at this command using a variable to seed an identify column in a SQL Server table.  Continue Reading

• How to convert DTS packages to SSIS and overwrite data

  Convert SQL Server 2000 DTS packages to SQL Server 2005 with this advice and also see how to overwrite data.  Continue Reading

• Query to search text in old DTS packages in SQL Server?

  Is there a query to search text in a SQL Server DTS package? SQL Server expert Joe Toscano explains which database objects can be viewed.  Continue Reading

• Retrieve images from SQL Server and store in VB.Net

  Having difficulty retrieving images from a SQL Serve database to a VB.Net application? Get best practices to retrieve, store and manage SQL Server images.  Continue Reading

• How to migrate DTS to SSIS and deploy SSIS packages

  Migrate DTS packages to SQL Server Integration Services with Migration Wizard and deploy SSIS packages in SQL 2005. Learn how to access DTS Designer tool.  Continue Reading

• Should I use BULK INSERT to import data from file to SQL Server databases, or a stored procedure?

  Looking for a stored procedure to import data from a file to a SQL Server database? Perhaps all you need is the BULK INSERT command.  Continue Reading

• Can you import Excel data to SQL Server Reporting Services?

  Importing data to SQL Server reporting Services is not an option. But here's how to create reports and use Excel spreadsheets as data sources.  Continue Reading

• Error importing Excel data to SSIS in SQL Server 2005

  Getting an error when importing data from an Excel spreadsheet to SSIS in SQL Server 2005? Try lining up data types in Excel and SSIS so they match.  Continue Reading

• Installing SQL Server native client

  Find out how to install a SQL Server native client.  Continue Reading

• Run SSRS with SQL Server Business Intelligence Development Studio

  What needs to be installed to run SQL Server Reporting Services (SSRS)? Get the scoop on Visual Studio and SQL Server Business Intelligence Development Studio.  Continue Reading

• Connect to SQL Server database with Visual Basics

  Find how to connect to a SQL Server database using Visual Basics 6.0.  Continue Reading

• Deploy reports from Reporting Services on webpage

  Find options on how to deploy reports from SQL Server Reporting Services on a website.  Continue Reading

• How to insert into SQL Server system tables

  Get the stored procedure to enable SQL Server updates to the systems' catalog in SQL Server 2005.  Continue Reading

• SQL Server error message: "Server out of memory"

  Avoid restoring your SQL Server database to solve the SQL Server error message "server out of memory." The problem could be the memory settings.  Continue Reading

• Restore SQL Server 2000 backups in SQL Server 2005

  Restore backups in SQL Server 2000 and SQL Server 2005 with this command.  Continue Reading

• Restoring a SQL Server database with a corrupt data file

  Find out how to restore data from a corrupt database.  Continue Reading

• Configure log shipping between SQL Server 2000 and 2005

  Have you upgraded to SQL Server 2005 and need to configure log shipping between SQL Server 2000 and SQL Server 2005? Learn about that possibility.  Continue Reading

• Executing a trigger before SQL Server table is updated

  Triggers in SQL Server can be executed before a table is updated. Get triggers to read and extract table values before and after the table is updated.  Continue Reading

• VARCHAR and NVARCHAR data types in SQL Server 2005

  Having trouble with using VARCHAR and NVARCHAR data types in SQL Server 2005 Express or Enterprise Edition? Learn which properties could be the problem.  Continue Reading

• Multiple SQL Server instances vs. multiple databases

  Get the pros and cons for installing multiple SQL Server 2005 instances on one machine vs. installing multiple databases within one instance.  Continue Reading

• 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

• Using FN_DBLOG function to interpret a SQL Server file

  FN_DBLOG is a function undocumented by Microsoft. Get the resources to utilize FN_DBLOG in interpreting an LDF file.  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

• How to tune SQL Server query performance after upgrade

  Still have slow queries after upgrading to SQL Server 2005? Learn how to performance tune your queries by rebuilding indexes and updating statistics.  Continue Reading

• SQL Server 2000 memory settings that work

  Having trouble monitoring memory usage in SQL Server 2000? Apply these memory settings through SQL Server Performance Monitor.  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

• Limit SQL Server admin permissions for domain accounts

  What is the best practice for using domain accounts such as those with domain admin permissions for SQL Server service accounts?  Continue Reading

• Problems connecting to SQL Server via Enterprise Manager

  Connection problems when trying to register a SQL Server instance through Enterprise Manager could trace back to client tools. Learn more about how to connect to Enterprise Manager.  Continue Reading

• Restoring from msdb with only .mdf and .ldf files

  I am working on an issue with an undocumented application, and the info is/was on a SS7 database, that was not maintained properly.

  How can I restore jobs from msdb when all I have is either a backup of it (but don't want to restore it to ...  Continue Reading

• Solve SQL Server permissions and authentication problems

  For the past two weeks I've been trying to resolve a permissions problem, an authentication problem, or both. I've run out of ideas of where to look. Here is what I have: (1) SQL Server 2005 on a Member Server (2003) (2) I'm running Visual ...  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

• Extended stored procedure to back up databases

  Does SQL Server 2005 (any edition) come with an extended stored procedure, allowing you to back up databases? If so, what is it called? I've looked all over the internet but only found third party ones.  Continue Reading

• User password display in SQL Server

  I am trying to find out which password I have assigned to the user schema on my SQL Server 2000. I know that you can change the password, but I've had some grief with that application in the past. I know where the password is stored, but it is in ...  Continue Reading

• Back up stored procedure only

  Is it possible to back up the stored procedure only? If so, how?  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

• Restoring database without .mdf file

  I have the .ldf file but don't have the .mdf file of my database. Can I restore my database from the existing .ldf file?  Continue Reading

• Utility to dump SQL Server Express database as text file

  I want to dump my SQL Server Express database as a SQL Server text file including "create table and index statements, and "insert into ... statements for all tables." Postgres has such a utility called pg_dump. Any ideas?  Continue Reading

• Delete .bak files automatically with CLR

  I have a query and I want to know if there is a stored procedure, SQL script, or T-SQL script to delete the .bak files, which are older than seven days. I have set up daily backups and after the day seven, the .bak files should purge off. How can I ...  Continue Reading

• Syntax error in SQL Server script

  I'm currently working on table partitioning. I'm struggling with using this " SELECT MAX(NAME) from sys.filegroups WHERE NAME NOT LIKE 'PRIMARY' " in order to get the last used Filegroup. Now this works just fine, but when I run the following script...  Continue Reading

• Calling a stored procedure remotely

  I'm using an ASP (Active Server Page) to call a stored procedure on a remote SQL Server. It's not executing, instead it's stalling. When my Web server and SQL Server are on the same machine I don't have this problem. What step am I missing to get ...  Continue Reading

• Conversion of char data type error

  I received this error: "XP and VS2003 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." The same page under Server 2003 works well. I need to continue developing under XP, what can I do?  Continue Reading

• Call triggers in a stored procedure

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

• Stored procedure practices in SQL Server

  Learn how to debug stored procedures, call a stored procedure from functions and duplicate values in a table with this SQL Server expert's advice.  Continue Reading

• Displaying a SQL Server database report in Excel

  We are working with SQL Server 2000 Enterprise Edition. We have been tasked with developing a report from the SQL Server database, which we then have to display in Excel. This data will be calculated using a query and then placed into specific cells...  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

• Transfer encrypted stored procedures in a database

  How do I transfer encrypted stored procedures in a DB? I am using the DTS wizard method with SQL Server 2000 on two servers. I copied all objects of the DB to the new destination second server but the database will not run on the new server because...  Continue Reading

• Flat file import not working on 64-bit systems

  Is SSIS on 64-bit systems any different from SSIS on 32-bit systems? I am trying to import a flat file in 64-bit systems, and SQL Server is throwing an exception, yet on 32-bit systems it is working fine.  Continue Reading

• Efficiently push and pull data in SQL Server

  What is the most efficient way to 'PUSH' data from Sybase to MS SQL Server or 'PULL' data into MS SQL Server from Sybase?  Continue Reading

• Install SQL Server 2000 Enterprise Editon on Windows 2000 Pro

  I'm having a problem installing SQL Server 2000 Enterprise Edition on Windows 2000 Professional. Do I need to install any Service Packs for this?  Continue Reading

• Schedule batch jobs in sequence in SQL Server 2000

  How do you schedule batch jobs in SQL Server 2005 to run in sequence? Is there a way do this other than using DB Maintenance?  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

• Parameterize tables and columns in a stored procedure

  To parameterize tables and columns in a stored procedure involves more than that of developing a straight forward SQL statement. SQL Server development expert Greg Low explains.  Continue Reading

• Using datagrids for a report in SQL Server Reporting Services

  Datagrids can serve as datasources in Visual Studio 2005. Follow these steps if you want to use your dataset for reports in SQL Server Reporting Services.  Continue Reading

• Stored procedure for displaying field names

  Display field names from a SQL Server database with this stored procedure shared by our development expert Greg Low.  Continue Reading

• Using asymmetric encryption

  SQL Server Security expert Steven Andres explains how the asymmetric encryption system could help protect against an attacker.  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

• Database Snapshots: Creating read-only copies of a database

  Database Snapshots feature in SQL Server 2005 Enterprise Edition creates read-only files. For these separate databases, our site expert Adam Machanic explains the functioning of locks.  Continue Reading