SQL Server, SQL Server Express, and SqlDataSource Control FAQ

FAQ Publisher — Wed, 13 May 2009 07:42:49 GMT

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:

SELECT @@VERSION,

CONVERT(VARCHAR(20),SERVERPROPERTY('productversion')), CONVERT(VARCHAR(20),SERVERPROPERTY('productlevel'))

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 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”.