A Few SQL Server 2005 Express Troubleshooting Tips

As an entry-level database server that is built on the same platform as the entire SQL Server product family, SQL Server 2005 Express combines simplicity with great power and a massive set of features. Naturally, all these capabilities can a bit confusing at times, so here's some help with some of the most common predicaments that you likely encounter.

Having trouble installing Server 2005 Express

Having some cool new software and not being able to get it installed is not much fun. Luckily, SQL Server 2005 Express usually installs without a hitch. If you do encounter an obstacle, use the following checklist to help get you out of hot water:

1. Make sure you have sufficient permissions to add or remove software.

In general, installing or removing software as an administrator is a good idea. Otherwise, the operating system may block you from making these kinds of changes.

2. Remove any previous versions of SQL Server 2005 Express via the Add/Remove Programs application within the Control Panel.

If you skip this step, the installer probably complains loudly and then keels over. Even though it's tedious, take the time to clean things up before trying to install.

3. Download and deploy the Windows Installer.

If you're running a more modern version of Windows, you likely already have the installer on your system.

4. Download and install the Microsoft .NET Framework 2.0.

SQL Server 2005 Express is built upon this framework; if it's missing, you can't install the database.

Having trouble connecting to the Server 2005 Express database

Connection problems are one of the most common complaints about any database server, SQL Server 2005 Express included. Happily, you can usually overcome these complications without too much difficulty. If you can't connect, try one of these remedies:

• Make sure the database server is running: Unless you request that the SQL Server 2005 Express service launch when your system boots, you simply need to start the service.

• Make sure that you're using the right protocol: You can use several communications methods to communicate with SQL Server 2005 Express. In order to successfully converse with the database server, you need to make sure that both the client and server are speaking on the right channel with the right setup. In particular — because SQL Server 2005 Express defaults to local connections only — if you want remote access, you must run the SQL Server Surface Area Configuration tool to allow both local and remote connections. You can also choose the protocol for these conversations.

• Adjust your connection string: When you connect to SQL Server 2005 Express, you need to specify a connection string that helps locate the database server. Often highly site-specific, even the smallest error in this connection string dooms your conversation from the start.

Administering your database

Even though SQL Server 2005 Express is an entry-level database that doesn't require much care and feeding, you still need to periodically handle administrative tasks. Here are two good choices to get the job done quickly and easily:

• SQLCMD utility: This character-based tool ships with every copy of SQL Server 2005 Express. You can run just about any administrative task by using direct Transact-SQL or one of the hundreds of built-in system stored procedures.

• SQL Server Management Studio Express Edition: If you have more of a hankering for graphical tools, you want to look at this utility. While a full-featured version ships with the more extensive SQL Server editions, even this entry-level version available for SQL Server 2005 Express lets you perform many administrative chores. And whatever isn't possible, you can always handle with direct Transact-SQL or system stored procedures.

Finding lost data in your database

If you can't seem to locate information that you know is in your database, don't despair: Unless someone has inadvertently deleted data, it's likely still patiently waiting inside your database. In many cases, difficulties like this one really are symptoms of an underlying permission problem.

Because it's built on the enterprise-class SQL Server database platform, SQL Server 2005 Express offers all the security capabilities of its bigger siblings. Unfortunately, all this power can sometimes translate into unforeseen security obstacles. These aren't hard to overcome, but you do need to know how to correctly configure your permissions.

Correcting your database

Unless you believe in gremlins or other supernatural entities that descend out of the ether and wreak havoc on your data, chances are that any information problems are due to one of a relatively small number of errors and omissions. Here's what to watch out for:

• Referential integrity issues: To help keep all your data synchronized, SQL Server 2005 Express offers referential integrity features. These prevent you or your applications from inadvertently altering rows from one table without making corresponding changes in another table.

• Failure to use transactions: Transactions help certify that your database interactions happen in logically consistent groups. Without proper transactions, an operation may update one table but fail to do the same for other tables. The result is damaged data integrity.

• Incorrectly defined columns: Believe it or not, sometimes database designers choose the wrong kind of data type when setting up their tables. For example, a particular field may need to contain currency amounts, which include decimals. Yet when they write the SQL to create the table, they choose the INTEGER data type for this column. This data type means that SQL Server 2005 Express discards any fractional amounts from that column.

Another common problem sees database designers not providing enough space for character-based fields. Again, SQL Server 2005 Express cheerfully tosses away any extra data, leading to damaged information and unhappy users.

dummies

Source:http://www.dummies.com/how-to/content/a-few-sql-server-2005-express-troubleshooting-tips.html