How to get SQL Server instance names on command prompt ?

To get list of SQL Server instances on (DOS) command prompt use any of the following commands.

    SQLCMD -L

    OSQL -L

Please note L must be capital. These commands will show SQL Server instances of any versions installed on a machine.

Reference: http://stackoverflow.com/questions/141154/how-can-i-determine-installed-sql-server-instances-and-their-versions

Advertisements

#sql-server, #sql-server-tips

SQL Server 2008 R2 Installation error “Remove SQL Server 2005 express tools”

While installing SQL Server 2008 R2 on my computer yesterday I faced an error, detail text of error message was:

Rule “SQL Server 2005 Express tools” failed.
the SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools.

I had Windows 7 in my computer and SQL Server 2005 Express with SQL Server Management Studio was already installed on it. I found solution in blog of Anthony Stevens (http://thepursuitofalife.com/sql-server-2008-installation-error-remove-sql-express-tools/) where he says to uninstalled SQL Server Management Studio before installing SQL Server 2008. I did the same error was removed.

#sql-server-2008-express-r2, #sql-server-errors

Generating GUID in SQL Server ?

NewID() funtion can be used in SQL Server to generate GUID.

For example:

Select newid();

or to select into a variable:

declare @GUID nvarchar(50);
Select @GUID=NewID();
Print @GUID;

#sql-server, #sql-server-tsql

“An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)” CREATE DATABASE permission denied in database ‘master’. (Microsoft SQL Server, Error: 262)

You may received this error if you are using SQL Server express with Windows Vista or Windows 7 and authentication mode is windows. This error can occurs if current user is not administrator. If current user is administrator right click on SQL Server Management Studio and run it as administrator. This error will gone.

#sql-server-2005, #sql-server-errors, #ssms-2005

Unspecified error (MS Visual Database Tools) in SSMS 2005 Express

You may faced this error if you have installed Visual Studio 2010 on your machine with SSMS 2005 Express. What happens is that VS 2010 install SQL Server 2008 Express and on trying to connect to SQL Server 2008 database instance with SSMS 2005 Express cause this error. Some of feature of SSMS work normally but one thing that do not work is design mode of a table. Solution is simple use SQL Server 2005 Express with SSMS 2005 Express and for SQL Server 2008 Express edition install SSMS 2008 Express 🙂

#sql-server-2005, #sql-server-2008, #sql-server-errors, #ssms-2005, #ssms-2008