All to share !!

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

2012-08-17T14:17:00.000+01:00

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

How to find Missing Identity Values?

2012-07-25T12:20:00.001+01:00

How to find Missing Identity Values?

One of my friends asked me long time ago to get missing identity values. Through power of Row_Number() Window function we can achieve it as below.

--Logic To Identify Missing Values.

Declare @MissingIdentityValues Table

(

IDCol Int

)

Insert @MissingIdentityValues(IDCol) Values (1) , (2) , (5) , (7) , (8) , (10) , (11) , (15) , (16)

Select MIN(IDCol) StartRange, MAX(IDCol) EndRange

From

(

select IDCol , IDCol - ROW_NUMBER() OVER(Order By IDCol) as GroupDiff

from @MissingIdentityValues

) AS GroupDiff

Group By GroupDiff

SQL Server – AdventureWorks for SQL Server 2012

2012-05-14T14:36:00.000+01:00

I have just started learning about SQL Server 2012 RTM and realised that I haven’t installed AdventureWorks sample database. I have quickly searched online and reached to Microsoft Database Products Samples where it provides information about all sample databases and procedure to install databases for SQL Server 2012. See below to install (restore) database:

Download AdventureWorks from here
Run below script

CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'D:\SQL\AdventureWorks2012_Data.mdf')
FOR ATTACH_REBUILD_LOG ;

You will see below message and you are DONE after running above script!

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf" may be incorrect.

New log file 'D:\SQL\AdventureWorks2012_log.ldf' was created.

Converting database 'AdventureWorks2012' from version 705 to the current version 706.

Database 'AdventureWorks2012' running the upgrade step from version 705 to version 706.

Technorati Tags: Server,AdventureWorks,database,Microsoft,Products,Samples,databases,procedure,Download,script,CREATE,FILENAME,ATTACH_REBUILD_LOG,activation,failure,MSSQLSERVER,MSSQL,DATA

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

2012-04-18T16:53:00.001+01:00

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

2012-03-07T19:17:00.001+00:00

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

2012-02-23T18:23:00.001+00:00

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:

You cannot specify a table variable or table-valued parameter as the new table.
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.
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.
SELECT...INTO cannot be used with COMPUTE.
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.
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.
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?

2012-02-22T22:34:00.000+00:00

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';

P2V using SCVMM error's at select host screen because of disk space requirement

2012-02-20T18:11:00.000+00:00

Have you ever seen below error while converting P2V using SCVMM?

There is not enough disk space on the available volumes.

Maximum disk space on the most appropriate volume, accounting for the host resource utilization (in megabytes): 60766

The virtual machine requirement (in megabytes): 162836

The maximum available space at the time of evaluation, after considering the virtual machine requirements (in megabytes): 139290

ID: 3501

I was bit worried after seeing error because currently I am having cluster server with more than 10 gigs of free space and it’s always fine while creating new VM. You don’t need to worry about above error because it simple to convert P2V if you are definitely sure about free space on host. Go to below screen and press next after selecting system drive only.

After selecting above continue with wizard by pressing next till you reach last second tab \ option in wizard “Conversation option”. Once you are there go back to above screen and select all drives that you want to be in virtualised machine and then press “Conversion Information” in the left side of the wizard and finally create to finish wizard. That’s all you have to do if you get above error while converting Physical to Virtual using SCVMM or Microsoft System Centre Virtual Machine Manager 2012.

Update query with JOIN

2012-02-14T18:23:00.002+00:00

One of my colleague just asked me about update query with JOIN but as he is new to SQL he tried to do exactly same as Select query and it was failing to execute. There is little difference when you do UPDATE query with JOIN. He was trying to execute below query and was not able to understand correct syntax of the query.

UPDATE Sales.SalesPerson AS sp

INNER JOIN Sales.SalesOrderHeader AS so

ON sp.BusinessEntityID = so.SalesPersonID

SET SalesYTD = SalesYTD + SubTotal

Where sp.SalesPersonID > 1

Below is correct syntax for running UPDATE query with JOIN:

UPDATE Sales.SalesPerson

SET SalesYTD = SalesYTD + SubTotal

FROM Sales.SalesPerson AS sp

JOIN Sales.SalesOrderHeader AS so

ON sp.SalesPersonID = so.SalesPersonID

Where sp.SalesPersonID > 1