SQL Server Editions & Features. 3

 

1.  How do I check the version and edition of the current SQL Server instance in a query?. 3

 

2.  How do I check and compare the features are supported by the different editions of SQL Server 2005/2008?  3

 

SQL Server Installation. 4

 

3.  Why is there no SQL Server 2005 Management Studio Express after installation?. 4

 

4.  How do I remove a SQL Server 2000/2005 instance manually?. 4

 

5.  Why do I receive “IIS feature requirement” warning when installing SQL Server 2005?. 4

 

T-SQL Reference. 5

 

6.  How do I insert a value into or reset identity column value?. 5

 

7.  How do I remove duplicate rows from a table in SQL Server?. 5

 

8.  How do I set a column name and table name to a variable in T-SQL?. 6

 

9.  How do I attach databases without “.ldf” files?. 7

 

10.  How do I import data from Excel to SQL Server?. 7

 

11.  How do I downgrade a database from a higher version to a lower versiong?. 7

 

12.  How do I move SQL Server databases between servers?. 8

 

13.  Can we use Import and Export Wizard or Data Transformation Services (DTS) with SQL Server 2005 Express Edition?  8

 

SQL Server Maintenance. 9

 

14.  Why do the “.ldf” files grow too large?. 9

 

15.  How do I limit the amount of memory used by SQL Server?. 9

 

16.  How do I backup and restore SQL Server databases with applications?. 10

 

SQL Server and ASP.NET. 11

 

17.  Why am I unable to check the checkbox “Generate INSERT, UPDATE, and DELETE statements” when configuring SqlDataSource?. 11

 

18.  Why am I unable to find DataSource controls in design view?. 11

 

19.  How do I make efficient paging when retrieving large amount of records?. 11

 

20.  How do I store and retrieve BLOBs data in SQL Server?. 12

 

21.  Why do I receive the error “SQL Server does not allow remote connections” when connecting to SQL Server 2005?  13

 

22.  Why do I fail in creating or attaching databases in Windows Vista?. 13

 

23.  Why do I receive the error “This server version is not supported” when adding new SQL Server 2008 connections in Visual Studio 2008?. 14

 

---

SQL Server Editions & Features [top]

1.  How do I check the version and edition of the current SQL Server instance in a query? [top]

We can execute the following statements to check the version and edition:

 

 

Related Documentations:

  

SERVERPROPERTY (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms174396.aspx

  

How to identify your SQL Server version and edition

http://support.microsoft.com/kb/321185

 

2.  How do I check and compare the features are supported by the different editions of SQL Server 2005/2008? [top]

Please refer to the documentations below:

  

Features Supported by the Editions of SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms143761(SQL.90).aspx

  

SQL Server 2005 Features Comparison

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx?PHPSESSID=0324345d45ef1bf1f764044e03584cd0

  

Features Supported by the Editions of SQL Server 2008

http://msdn.microsoft.com/en-us/library/cc645993.aspx

  

Microsoft SQL Server 2008 Editions

http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx

  

---

SQL Server Installation [top]

3.  Why is there no SQL Server 2005 Management Studio Express after installation? [top]

When we install Visual Studio with SQL Server 2005 Express Edition, there is no SQL Server Management Studio Express by default, we need to download and install it.

  

Also, if we have workstation tools of other editions (such as Developer or Enterprise) installed, that can also be used for SQL Express Edition.

 

SQL Server 2005 Management Studio Express installation:

http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

 

4.  How do I remove a SQL Server 2000/2005 instance manually? [top]

Sometimes, we have to uninstall the SQL Server instance manually if an installation fails or if Add or Remove Programs doesn’t work.

 

How to manually remove SQL Server 2000 default, named, or virtual instance

http://support.microsoft.com/kb/290991

  

How to uninstall an instance of SQL Server 2005 manually

http://support.microsoft.com/kb/909967

 

5.  Why do I receive “IIS feature requirement” warning when installing SQL Server 2005? [top]

Several SQL Server features require Microsoft Internet Information Services (IIS). If we don’t install all the IIS components, some SQL Server features won’t be available.

 

We need to install IIS 7.0 by using the Programs and Features-> Turn Windows features on or off in Control Panel

 

For more details, please refer to the following KB document:

  

You receive a warning message on the System Configuration Check page of the SQL Server 2005 Setup program on a computer that is running Windows Vista or a Server Core installation of Windows Server 2008

  

http://support.microsoft.com/kb/920201

  

---

T-SQL Reference [top]

6.  How do I insert a value into or reset identity column value? [top]

We can set property IDENTITY_INSERT to ON to insert value into identity column.

 

 

We can reset the identity value with DBCC CHECKIDENT.

 

 

Related documentations:

  

SET IDENTITY_INSERT (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms188059.aspx

  

DBCC CHECKIDENT (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms176057.aspx

 

7.  How do I remove duplicate rows from a table in SQL Server? [top]

Duplicate rows break the entity integrity and should be avoided in a relational database. Please refer to the KB article below to locate and remove duplicate primary keys from a table.

 

How to remove duplicate rows from a table in SQL Server

http://support.microsoft.com/kb/139444

 

8.  How do I set a column name and table name to a variable in T-SQL? [top]

We cannot set the column name and table name to a variable in T-SQL. Instead, we have to use a dynamic query to accomplish this.

 

 

Dynamic SQL also increases the risk for SQL injection attacks, which we must pay attention to.

 

Related links:

 

Dynamic SQL & SQL injection

http://blogs.msdn.com/raulga/archive/2007/01/04/dynamic-sql-sql-injection.aspx

 

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

 

---

Working with SQL Server [top]

9.  How do I attach databases without “.ldf” files? [top]

If the transaction log files are missing, we can rebuild the log file with the following statements:

 

 

Related documentation:

 

CREATE DATABASE (Transact-SQL)

http://msdn.microsoft.com/en-us/library/ms176061.aspx

 

10.  How do I import data from Excel to SQL Server? [top]

There are several ways for us to import data from Excel worksheets to SQL Server databases.

 

For detail, please refer to this documentation:

 

How to import data from Excel to SQL Server

http://support.microsoft.com/kb/321686

 

11.  How do I downgrade a database from a higher version to a lower versiong? [top]

We can use Generate SQL Server Scripts Wizard to script all the database elements and structures, and use Import and Export Wizard to copy the data.

 

However, if we use certain new features of the higher SQL Server version, the features won’t work in the lower version.

 

We can also use the Database Publishing Wizard to generate the script file contains database schema and records.

 

Related documentations:

 

Generate SQL Server Scripts Wizard

http://msdn.microsoft.com/en-us/library/ms186996.aspx

 

How to: Start the SQL Server Import and Export Wizard

http://msdn.microsoft.com/en-us/library/ms140052(SQL.90).aspx

 

Publishing a SQL Database

http://msdn.microsoft.com/en-us/library/bb907585.aspx

 

Related thread:

http://forums.asp.net/t/1004394.aspx

 

12.  How do I move SQL Server databases between servers? [top]

There are several ways for us to move SQL Server user database between servers, please refer to the KB article below:

 

How to move databases between computers that are running SQL Server

http://support.microsoft.com/kb/314546

 

13.  Can we use Import and Export Wizard or Data Transformation Services (DTS) with SQL Server 2005 Express Edition? [top]

In SQL Server 2005 Express Edition, there is no Import and Export Wizard (Data Transformation Services - DTS in SQL Server 2000) by default.

 

We can download and install either of the following files:

 

Microsoft SQL Server 2005 Express Edition Toolkit

http://www.microsoft.com/downloads/details.aspx?FamilyID=3c856b93-369f-4c6f-9357-c35384179543&DisplayLang=en

 

Microsoft SQL Server 2005 DTS Designer Components

http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/SQLServer2005_DTS.msi

 

After the installation, we can find the DTS Wizard at the default directory of SQL Server installation.

 

For example,

C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe

 

---

SQL Server Maintenance [top]

14.  Why do the “.ldf” files grow too large? [top]

Sometimes, the transaction log (“.ldf” files) may become very large unexpectedly and use up all the disk space. We need to limit or reduce the size of transaction log file when it grows too large.

 

For more details, please refer to these documentations:

 

Managing the Transaction Log

http://msdn.microsoft.com/en-us/library/ms345382.aspx

 

A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server

http://support.microsoft.com/kb/317375

 

Related thread:

http://forums.asp.net/t/1368475.aspx

 

15.  How do I limit the amount of memory used by SQL Server? [top]

By default, SQL Server can change its memory requirements dynamically based on available system resources. Sometimes, SQL Server will use the memory as much as possible to speed up access for incoming queries. We can set the max and min server memory to configure the amount of memory in the buffer pool used by SQL Server instance.

 

For detail, please refer to these documentations:

 

How to: Set a Fixed Amount of Memory (SQL Server Management Studio)

http://msdn.microsoft.com/en-us/library/ms191144.aspx

 

Server Memory Options

http://msdn.microsoft.com/en-us/library/ms178067(SQL.90).aspx

 

Effects of min and max server memory

http://msdn.microsoft.com/en-us/library/ms180797.aspx

 

Optimizing Server Performance Using Memory Configuration Options

http://msdn.microsoft.com/en-us/library/ms177455.aspx

 

How to adjust memory usage by using configuration options in SQL Server

http://support.microsoft.com/kb/321363

 

16.  How do I backup and restore SQL Server databases with applications? [top]

 

We can use SQL Server Management Objects (SMO) to build customized applications to manage SQL Server.

SMO is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server, which is compatible with SQL Server 2000, SQL Server 2005, and SQL Server 2008.

 

For detail, please refer to the documentations below:

 

Backing Up and Restoring Databases and Transaction Logs

http://msdn.microsoft.com/en-us/library/ms162133.aspx

 

SQL Server Management Objects (SMO)

http://msdn.microsoft.com/en-us/library/ms162169.aspx

 

---

SQL Server and ASP.NET [top]

17.  Why am I unable to check the checkbox “Generate INSERT, UPDATE, and DELETE statements” when configuring SqlDataSource? [top]

The checkbox will be enabled when the select table has the primary key and the primary key column is one of the returned columns.

 

Related documentation:

  

Inserting, Updating, and Deleting Data with the SqlDataSource

http://www.asp.net/learn/data-access/tutorial-49-vb.aspx

 

18.  Why am I unable to find DataSource controls in design view? [top]

In Visual Studio, we need to configure the property "Non Visual Controls" in Visual Studio to show Non-Visual controls (such as SqlDataSource) in design view.

 

Visual Studio 2005

View -> Non Visual Controls

 

Visual Studio 2008

View -> Visual Aids -> ASP.NET Non-visual Controls

 

Related thread:

http://forums.asp.net/t/1334823.aspx

 

19.  How do I make efficient paging when retrieving large amount of records? [top]

When we retrieve or display large amount of records from SQL Server, efficient paging is quite necessary. By using efficient, only the records for current page will be returned, which improves the performance greatly.

 

For details, please refer to these links:

 

Efficient Data Paging and Sorting with ASP.NET 2.0 and SQL 2005

http://weblogs.asp.net/scottgu/archive/2006/03/22/440900.aspx

 

How to dynamically page through a large result set in ASP.NET by using SQL Server stored procedures in Visual C# .NET

http://support.microsoft.com/kb/829142

 

Efficiently Paging Through Large Amounts of Data

http://www.asp.net/learn/data-access/tutorial-25-vb.aspx

  

20.  How do I store and retrieve BLOBs data in SQL Server? [top]

BLOBs (binary large objects) are very large variable binary or character data, typically documents (.txt, .doc) and pictures (.jpeg, .gif, .bmp), which can be stored in a database.

 

In SQL Server, BLOBs can be text, ntext, image nvarchar(max), varchar(max) and varbinary(max) data type.

 

For more details, please refer to the following documents:

 

How to read and write a file to or from a BLOB column by using ADO.NET and Visual C# .NET

http://support.microsoft.com/kb/317016

 

How to read and write a file to or from a BLOB column by using ADO.NET and Visual Basic .NET

http://support.microsoft.com/kb/316887

 

Working with Batched Data

http://www.asp.net/learn/data-access/#batched

 

It is recommended to use nvarchar(max), varchar(max), and varbinary(max) instead of ntext, text, and image.

 

Related documentation:

 

Using Large-Value Data Types

http://msdn.microsoft.com/en-us/library/ms178158(SQL.90).aspx

 

Generally, there are two choices for storing the BLOBs (binary large objects) data: database or file system. We need to consider the space cost, maintenance, synchronization, performance etc. to make the decision.

 

Related documentation:

http://research.microsoft.com/apps/pubs/default.aspx?id=64525

 

Related thread:

http://forums.asp.net/t/1096999.aspx

 

21.  Why do I receive the error “SQL Server does not allow remote connections” when connecting to SQL Server 2005? [top]

This kind of issue is usually caused by incorrect SQL Server instance name or SQL Server instance doesn’t enable remote connections settings.

 

We need to:

 

1) Make sure the connection string is valid. Check if the SQL Server instance is running and its instance name is correct.

 

2) Make sure the SQL Server instance allows remote connections.

 

Related documentation:

 

How to configure SQL Server 2005 to allow remote connections

http://support.microsoft.com/kb/914277

 

22.  Why do I fail in creating or attaching databases in Windows Vista? [top]

When we are creating or attaching database in Windows Vista, we may receive the errors below:

 

CREATE DATABASE permission denied in database 'master'

 

The server principal "UserName" is not able to access the database "model" under the current security context.

 

The failure is caused by User Account Control (UAC) in Windows Vista. By default, users on Windows Vista that are members of the Windows Administrators group are not automatically granted SQL Server administrator privileges.

 

To solve this, we can disable the UAC or right-click the program icon and then click “Run as administrator”.

 

Related documentations:

  

How to: Connect to SQL Server from Windows Vista

http://msdn.microsoft.com/en-us/library/bb326612(SQL.90).aspx

  

How to use User Account Control (UAC) in Windows Vista

http://support.microsoft.com/kb/922708

 

23.  Why do I receive the error “This server version is not supported” when adding new SQL Server 2008 connections in Visual Studio 2008? [top]

Since Visual Studio 2008 and 2005 were released before SQL Server 2008, their respective tools and data designers do not automatically support SQL Server 2008 and its new data types and features.

  

To work with SQL Server 2008 in Visual Studio 2008 or use certain SQL Server features, Visual Studio 2008 Service Pack 1 and .NET Framework 3.5 Service Pack 1 are required.

 

Related documents:

  

Visual Studio 2008 Service Pack 1 and .NET Framework 3.5 Service Pack 1

http://msdn.microsoft.com/en-us/vsts2008/products/cc533447.aspx

  

Actions that are required before you install SQL Server 2008 on a computer that has Visual Studio 2008 or the prerelease version of SQL Server 2008 installed

  

http://support.microsoft.com/kb/956139