Custom Search
| Home | Sitemap | Feedback | About... |
Custom Search
|
Q: How can I determine what service pack and hotfixes are installed on an SQL server?
Last modified: April 2, 2007
A: You can get this information by running the following T-SQL statement:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
And then look up the product version and build number here:
937137 - The SQL Server 2005 builds that were released after SQL Server 2005 Service Pack 2 was released
Q: How can I determine if I am running a 32-bit or a 64-bit edition?
Last modified: October 8, 2006
A: You can get this information by running the following T-SQL statement:
SELECT @@VERSIONThe output should look something like this:
32-bit edition:
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Oct 8 2006 12:25:49
Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows
NT 5.1 (Build 2600: Service Pack 2)
64-bit edition:
Microsoft SQL Server 2005 - 9.00.2047.00 (X64) Oct 8 2006 12:28:13
Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit)
on Windows NT 5.2 (Build 3790: Service Pack 1)
Q: How can I determine the number of processor cores in my SQL server?
Last modified: August 19, 2007
A: You can get this information by running the following T-SQL statement:
SELECT cpu_count/hyperthread_ratio AS cores FROM sys.dm_os_sys_info;
Q: How can I list all SQL servers in my domain?
Last modified: October 8, 2006
A: Type the following command at the command prompt:
sqlcmd -Lwhich will list the locally configured server computers, and the names of the server computers that are broadcasting on the network.
Note: Because of the nature of broadcasting on networks, sqlcmd may not receive a timely response from all servers. Therefore, the list of servers returned may vary for each invocation of this option.
Q: Can I install Express Edition on a dual CPU system?
Last modified: October 8, 2006
A: Yes, you can. Express Edition is limited to 1 CPU and 1 GB of RAM, but you can install it on a system with more than
the maximum supported resources. It will not use more than 1 logical CPU and 1 GB of RAM.
More information:
Q: I have installed SQL Server Management Studio Express (SSMSE). How do I connect to the Database Engine?
Last modified: November 29, 2006
A: The default installation of SQL Server Express uses an instance name (SQLExpress). This instance name must be provided to connect to SQL Server Express by using SSMSE. If you have a instance name other than SQLExpress, connect by using computer name\instance name.
To connect SSMSE to a SQL Server 2005 Express Database Engine instance, follow these steps:
For connections to SQLexpress from other tools, check:
How to connect to SQL Express from "downlevel clients"
Q: I have installed SQL Server 2005 succesfully, but there's no system tray icon for the SQL Manager, like in SQL 2000
Last modified: October 8, 2006
A: The SQL Manager is no longer available with SQL 2005.
You can Pause, Stop and Start the SQL Server service with the SQL Server Configuration Manager, but
there's no system tray icon anymore.
You can download a freeware third party tool to replace the SQL Manager:
Q: Can I run both SQL 2000 and SQL 2005 on the same server?
Last modified: November 24, 2007
A: Yes, you can install multiple instances of both server versions on the same machine. At least one of them has to be installed as a named instance.
For a default instance, the default port is 1433. For a named instance, it is dynamic but you could configure it as fixed port. Applications querying a SQL Server by IP number will be directed to the default instance. Applications should always query the SQL server to get the dynamic port of a named instance, and then connect to that instance. If you experience connection problems to one of the instances, check thes articles:
905618 - You may receive an error message when you try to connect to an instance of SQL Server 2000 or SQL Server 7.0 that was installed after you installed SQL Server 2005
922131 - You cannot remotely connect to a named instance of SQL Server 2000 by using the TCP/IP protocol
You can manage the SQL 2000 instance with SQL 2005 Management Studio.
For a complete list of allowed combinations, see BOL:
"Using SQL Server 2005 Side-By-Side with Previous Versions of SQL Server"
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/93acefa8-bb41-4ccc-b763-7801f51134e0.htm
Note: although it's perfectly possible to run SQL Server 2000 and SQL Server 2005 in parellel, this is only recommended for testing purposes, not on production servers. This is not because of functionality, but because of resource usage and service packs updates.
Q: Why doesn't Reporting Services install on Vista Home Premium?
Last modified: December 30, 2007
A: During installation of SQL 2005 on Vista Home Premium, you'll get a warning that IIS is not installed or is disabled, even after you installed all available IIS components through the Control Panel.
The problem is that Reporting Services needs the "Windows Authentication" component of IIS to be installed and this component is not available with IIS on Vista Home Premium Edition.
So you need Vista Business, Enterprise or Ultimate to be able to install Reporting Services, as documented here:
934164 - How to install SQL Server 2005 Reporting Services on a Windows Vista-based computer
You can see which components of IIS are available on which Windows version here:
Overview of Available Features in IIS 7.0
Q: During installation I get an error: "SQL Server Setup failed to obtain system account information for the ASPNET account". How do I solve this?
Last modified: November 24, 2007
A: The ASPNET account is created/removed depending on the existence of the
installation of ASP.NET which is a subset of .NET Framework.
You will get this error when the ASPNET account is missing.
You can solve the problem by starting a Command Prompt, go to the
"C:\Windows\Microsoft.NET\Framework\v1.1.4322" folder and run the following command:
aspnet_regiis -iThis will create the necessary account and you'll be able to install SQL Server 2005.
Q: How can I move my database files to another disk in my SQL Server?
Last modified: November 25, 2006
A: An easy way to move user database files to a different location is by detaching, moving and attaching the user database.
This can be done in SQL Server Management Studio so you don't have to type any code.
Here are the steps:
Alternatively you can backup the database and restore it, specifying a different location for the files on the options table or use the MOVE clause in the RESTORE command in Transact-SQL. Details are here:
221465 - INF: Using the WITH MOVE Option with the RESTORE Statement
If you want to move the system databases, be sure to read these articles:
Moving the SQL 2005 System Databases
224071 - How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
Q: How can I move my database files to another SQL Server?
Last modified: November 25, 2006
A: You can use either backup / restore or detach / attach. Note that you also will have to transfer the logins and passwords.
Use the articles listed here for detailed instructions:
314546 - How to move databases between computers that are running SQL Server
224071 - How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server
221465 - INF: Using the WITH MOVE Option with the RESTORE Statement
246133 - How to transfer logins and passwords between instances of SQL Server
918992 - How to transfer the logins and the passwords between instances of SQL Server 2005
Moving Your Users with Their Databases
Sync SQL Logins - freeware utility
168001 - User logons and permissions on a database may be incorrect after the database is restored
240872 - How to resolve permission issues when you move a database between servers that are running SQL Server
Q: Can I move a database created in SQL 2005 64-bit onto a platform with SQL 2005 32-bit? And vice-versa?
Last modified: March 18, 2007
A: Yes, as long as both servers run SQL 2005, this is no problem at all. You can restore a database backup created with SQL 2005 X64 Edition to a database server running SQL 2005 x86 Edition, as well as vice-versa.
Q: Can I move my SQL Server 2005 database to SQL Server 2000?
Last modified: June 12, 2007
A: Due to incompatibility issues, this is not possible, as documented here:
937758 - You receive an error message when you try to attach a SQL Server 2005 database in SQL Server 2000
However, you can move the data and objects:
DropObjectsFirst: True
IncludeExtendedProperties: True
CopyData: True
ExistingData: Replace
CopySchema: True
UseCollation: True
IncludeDependentObjects: True
CopyAllObjects: False
CopyAllTables: True
CopyAllViews: True
CopyAllStoredProcedures: True
CopyAllDefinedFunctions: True
CopyAllDefaults: True
CopyAllUserDefinedDataTypes: True
<other options>: False
CopyDatabaseUsers: False
CopyDatabaseRoles: False
CopySqlServerLogins: False
CopyObjectLevelPermissions: False
CopyIndexes: True
CopyTriggers: True
CopyFullTextIndexes: True
CopyPrimaryKeys: True
CopyForeignKeys: True
GenerateScriptsInUnicode: True
If you just want to transfer the data, steps 5 and 6 can be omitted.
It is recommended that you install SQL Server 2005 SP2 and SQL Server 2000 SP4 before the process.
Q: I get an error: "Create maintenance plan failed". How do I solve this?
Last modified: February 24, 2007
A: If you are running SQL 2005 SP1, make sure that you have Integration Services installed. To create or execute maintenance plans, you must install SSIS.
More information:
Integration Services is not needed any more when you are running SQL 2005 SP2.
Q: Can I enlist a SQL Server 2000 as a target server to a SQL Server 2005 master server?
Last modified: October 8, 2006
A: No, you cannot do that. Target servers must be at least the version of the master server. Otherwise
the master could try to do something that is unsupported on lower level targets.
The master can be a lower version. When upgrading, upgrade the targets first, and then the master.
Q: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections". How do I solve this?
Last modified: Nov. 24, 2007
A: This error can have several different causes:
Remote connections are disabled by default for Express and Developer Editions of SQL 2005.
You will have to enable remote connections and [optionally] start the SQL Server Browser service.
If there is a firewall between your client and the SQL server, you will also have to open the appropriate ports on the firewall.
Detailed information:
Remote connections are enabled by default for Standard and Enterprise Editions of SQL 2005.
SQL Server 2005's authentication mode is Windows Authentication by default and the "sa" account is disabled by default.
You can change the authentication mode by connecting to your SQL Server instance using SQL Server Management Studio after the setup.
Go to Server Properties - Security, where you will see the Server authentication modes. If needed, you can enable
the "sa" account from the Security\Logins node.
Detailed information:
Q: How can I determine the license type and number of licenses of an SQL server?
Last modified: January 2, 2008
A: On a server running SQL 2000, you can get this information by running the following T-SQL statements:
SELECT SERVERPROPERTY('LicenseType') AS [LicenseType]
SELECT SERVERPROPERTY('NumLicenses') AS [NumLicenses]
Amazingly, this doesn't work anymore on SQL 2005. The only way to be sure which license your SQL 2005 server is using is by checking the paper license! When you run the above statements on SQL 2005, it will always return the value "DISABLED" as the license type.
The best you can achieve is to manually enter the type and number of licenses that you bought in the registry, because SQL 2005 still reads these values from the registry, but doesn't create them on installation. The details can be found here. This will give you a fast method of checking your licenses. But make sure you lock away those paper licenses in a secure place!
Q: Does a dual core CPU count as 1 or 2 CPU's in the "Per Processor" licensing model?
Last modified: August 14, 2007
A: CPU licensing is per socket. For licensing purposes, a dual core (more generally speaking: a multicore) CPU counts as one processor.
Q: What are the licensing requirements when running SQL Server in a Virtual Machine (VM)?
Last modified: August 14, 2007
A: When you run SQL Server in a Virtual Machine (VM) and you are using the "Per processor" licensing model, you have to buy a Per processor license for every CPU which is available to the VM. The number of CPUs installed in the host server doesn't play a role.
| Copyright © 2005-2008 Vera Noest | Privacy statement |