SQL Server–Start SQL Server instance in single-user mode

There are certain situations when you have to start SQL server in single-user mode from the start up. For example, you may want to restore Master database or recover other system database or to change server configuration options and that will require to start an Instance of SQL Server in single-user mode.

• To start SQL Server in single-user mode is very simple. Please use MS-DOS change directory (cd) command to move to the correct (SQL Instance) directory before starting sqlservr.exe, as shown below

cd \Program Files\Microsoft SQL Server\MSSQL10.50.MSSQLSERVER\MSSQL\Binn

To start default instance enter the following command

sqlservr.exe –m

To start named instance of SQL Server enter the following command

sqlservr.exe - m -s <instancename>

Connect to SQL Server instance using SSMS or sqlcmd after running above command – Make sure to keep MS-Dos command window open. To stop single-user mode just press CTRL+C and it will prompt you to stop SQL Service.

• Alternatively (easy) use SQL Server Configuration Manager and click on SQL Server Services. Right click on desired SQL Server Instance and go to properties. On the advance table enter param ‘-m;’ before existing params in the startup parameters. Press apply and then restart the SQL Server Instance service.

Make sure to remove added parameter and restart service to allow multiuser connection.

Note: Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.

Technorati Tags: Server,instance,user,mode,Master,database,system,configuration,Microsoft,MSSQLSERVER,MSSQL,Binn,SSMS,Service,Manager,Services,Agent,sqlservr

SCVMM 2012 RC to RTM upgrade fails with error “upgrading from currently version is not supported”

 

I  have just tried to upgrade SCVMM 2012 RC to RTM and it failed to upgrade with following error “Upgrading from the currently installed version of VMM System Center 2012 Virtual Machine Manager is not supported. You must be running VMM 2008 R2 with Service Pack 1 (SP1) to upgrade to System Center 2012 Virtual Machine Manager.”

·         Uninstall current (RC) version of SCVMM. Make sure you select “Retain Database” in Database Options.

·         Using SQL Server Management Studio modify values of “DatabaseVersion” and “QFEDatabaseVersion” in table  tbl_VMM_GlobalSetting from RC (3.0.5007.0) to RTM (3.0.6005.0 or alternatively match to the version of setup.exe).

 

• Start SCVMM 2012 installation and select existing database.
• Finally, Don’t forget to update the VMM host agents!!

Technorati Tags: SCVMM 2012,2012

How to Round to 2 decimal places without Round function

Today, Someone asked me how to round float value to 2 decimal places without using T-SQL Round function. Let me know your thoughts for below T-SQL block.

Declare

      @MyValue Float = 3.1471,

      @IntPart1 int = 0 ,

      @intPart2 int = 0

Select @IntPart1=@MyValue

select @intPart2 = (@MyValue - @IntPart1)*100

If (@MyValue  - ( Cast(@IntPart1 AS Float) + Cast(@intPart2 AsFloat)/100))*10000 > 50

      Select Cast(@IntPart1 AS Float) + Cast(@intPart2+1 As Float)/100 ASRound1

Else

      Select Cast(@IntPart1 AS Float) + Cast(@intPart2 As Float)/100  AS Round1

SELECT INTO TABLE – INSERT INTO SELECT Inserting data from from one table to another

 

There are two different methods to insert data from one table to another. Although its really useful but there are few limitation in both of below methods. Don't forget to read list of limitations and restrictions below.

1. SELECT INTO

Below method will create table when data is inserted from one table to another table. Its useful when you need exactly same datatype as source table.

 

Use AdventureWorks2008R2;
Go
---Insert data using SELECT INTO
SELECT AddressLine1, City 
INTO BothellAddresses
FROM Person.Address 
where City  = 'Bothell';
GO
---VERIFY DATA
Select AddressLine1, City
FROM BothellAddresses
---DROP TABLE
DROP TABLE BothellAddresses 
GO

2. INSERT INTO SELECT

Below method will need table to be created prior to inserting data. Its really useful when table is already created and you want insert data from another table.

Use AdventureWorks2008R2;
Go
---Create Table
CREATE TABLE BothellAddresses (AddressLine1 NVARCHAR(60), City NVARCHAR(30))
---Insert into above table using SELECT
INSERT INTO BothellAddresses(AddressLine1, City)
SELECT  AddressLine1, City 
FROM Person.Address 
where City  = 'Bothell';
---VERIFY DATA
Select AddressLine1, City
FROM BothellAddresses
---DROP TABLE
DROP TABLE BothellAddresses 
GO

*The following limitations and restrictions apply to the INTO clause:

1. You cannot specify a table variable or table-valued parameter as the new table.
   
2. You cannot use SELECT…INTO to create a partitioned table, even when the source table is partitioned. SELECT...INTO does not use the partition scheme of the source table; instead, the new table is created in the default filegroup. To insert rows into a partitioned table, you must first create the partitioned table and then use the INSERT INTO...SELECT FROM statement.
   
3. When a computed column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computed at the time SELECT...INTO was executed.
   
4. SELECT...INTO cannot be used with COMPUTE.
   
5. The FILESTREAM attribute does not transfer to the new table. FILESTREAM BLOBs are copied and stored in the new table as varbinary(max) BLOBs. Without the FILESTREAM attribute, the varbinary(max) data type has a limitation of 2 GB. If a FILESTREAM BLOB exceeds this value, error 7119 is raised and the statement is stopped.
   
6. Indexes, constraints, and triggers defined in the source table are not transferred to the new table, nor can they be specified in the SELECT...INTO statement. If these objects are required, you must create them after executing the SELECT...INTO statement.
   
7. Specifying an ORDER BY clause does not guarantee the rows are inserted in the specified order.

* Source Microsoft

Technorati Tags: Select into,Insert into,Insert,Create Table,SQL,INTO

How to get the stored procedure or views containing particular text from original SQL definition statements?

How to get the stored procedure or views containing particular text from original SQL definition statements?

Here we using following views.

sys.syscomments for SQL 2000 Compatibility -- Contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements. For more information on sys.syscomments please go to http://msdn.microsoft.com/en-us/library/ms186293%28v=sql.100%29.aspx

Note:Microsoft recommend for any furture development do not use sys.syscomments view instead use sys.sql_modules.

sys.sql_modules For SQL 2005/SQL 2008.-- Returns a row for each object that is an SQL language-defined module.Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view. For a description of these types, see the type column in the sys.objectscatalog view for more information on sys.sql_modules please go to http://msdn.microsoft.com/en-us/library/ms175081%28v=sql.100%29.aspx

sys.sysobjects: Contains one row for each object that is created within a database, such as a constraint, default, log, rule, and stored procedure. For information on sys.sysobjects please go to http://msdn.microsoft.com/en-us/library/ms177596.aspx

SQL 2000	SQL 2005/SQL 2008
--to get the stored procedures containing Product in code USE AdventureWorks; GO select--top 10 *       name As Procedurename ,textAS ProcedureCode from       sys.syscomments sc inner join       sys.sysobjects so             on sc.id = so.id where       textlike'%Product%' and so.xtype ='P';	--to get the stored procedures containing Product in code USE AdventureWorks; GO select--top 10 *       name As Procedurename , sc.[definition] AS ProcedureCode from       sys.sql_modules sc inner join       sys.sysobjects so             on sc.object_id= so.id where       sc.[definition] like'%Product%' and so.xtype ='P';
--to get the Views containing Address in code USE AdventureWorks; GO select--top 10 *       name As Procedurename ,textAS ProcedureCode from       sys.syscomments sc inner join       sys.sysobjects so             on sc.id = so.id where       text like'%Address%' and so.xtype ='V';	--to get the Views containing Address in code USE AdventureWorks; GO select--top 10 *       name As Procedurename ,sc.[definition] AS ProcedureCode from       sys.sql_modules sc inner join       sys.sysobjects so             on sc.object_id= so.id where       sc.[definition] like'%Address%' and so.xtype ='V';