SQL Server – Bring Database from Single User Mode to MultiUser Mode
Its often troubles DBAs where once a DB went to Single User Mode it cant revert back to Multi User mode, the error looks some what like this Error Getting : Changes to the state or options of database cannot be made at this time. The database is in single-user mode, and a user [...]
full storyHow to restart SQL Serives Console.
Go to Start Menu > programs > Administrative Tools > Services > Right Click on “SQL Server (MSSQLSERVER)” and Choose Restart.(This will restart all SQL Services)
full storyMS SQL Server get next Identity insert value
The problem behind SQL Server Identity field lies where when somebody delete intermediate rows, next identity value may be different from what we expect for eg. if we are inserting from 1 -50 on a table say table1 with an identity field, and we deleted 40-50 values after insertion what will be next identity [...]
full storySQL SERVER Insert result of a Stored Procedure in to a table
Result of Stored procedures are key in most SQL issues, to import resultset into a table , first the DB should include the table, insert into will not work with this scenario For Eg. Create proc PrintDate as select getdate() We need to insert result of this proc to a table called table1 create table1 [...]
full storyADO.NET Data Services
Microsoft Data Platform Development Technical Article Ref:MSDN Summary: This document describes how to create and use Microsoft® ADO.NET Data Services, and discusses various details around the URI and payload formats. This document is meant as an introduction to ADO.NET Data Services and thus covers the core aspects of the technology, defering discussion of more [...]
full storySql Server Function With Table Parameter and Retuning Table
its a complex requirement for having table parameters to be passed and returned from a stored procedure by default SQL server doent accept table for this we need to create a user defined data type, say tabletype(for here) create type tableType as table ( f1 int, f2 char(1) ) Now create a table tab1 [...]
full storySql server Function Returning a table
here is an example for Functions in SQl Server which returns a table –<START> create function fnTab() returns @tab table( f1 int,f2 varchar(2000) ) as begin –inserting values to the table to be returned insert into @tab values (1,’name1′) insert into @tab values (1,’name2′) return end –</END> now select from function select * from dbo.fnTab() [...]
full storywhat happened when more than one rule assigned to a column in SQL Server
this is a complex case suppose we created 2 rules create rule rule11 as @r =100 create rule rule111 as @r =1001 and there is a tbale create table t11( cind int) what will happen if we tried to bound these 2 rules to this table sp_bindrule’rule11′,’t11.cind’ sp_bindrule’rule111′,’t11.cind’ now try sp_help t11 RowGuidCol No [...]
full storySql Server Defaults
defaults are used for inserting default values to tables when the field is skipped or inserted iwth default values To dmonstrate lets create a table first create table default_test ( id int, r_date datetime default(getdate()), r_count int default(100) ) Now insert values to fields except r_date insert into default_test (id) values (1000) r_date [...]
full storySQL Server Wild Chards
eg. table create table students ( st_id int primary key, st_name varchar(100), st_address varchar(max), st_phone char(10) ) insert into students select 1,’Abhilash’,’KOCHI’,’98400000′ insert into students select 2,’Sooraj’,’Thrissur’,’99000000′ insert into students select 3,’Roopjith’,’Kochi’,’974000000′ insert into students select 4,’Cimily’,’Thodupuzha’,’99610000′ insert into students select 5,’Sunit’,’Mumbai’,’9000000′ insert into students select 6,’Rakesh’,’Utterpradesh’,’9000000′ list all records starts either [...]
full storyDefault Constraint MS SQL Server
defaultconstraint in SQL server used to set a default value for a field when this field is intentionally or by keyword creating table with default constraint create table default_test ( id int, r_date datetime default(getdate()), r_count int default(100) ) here r_date will be current date and r_count will be 100 if skipped to insert [...]
full storyupdate_statistics in SQL server
update_statistics in SQL server creates new indexes and re arrange all existing indexes accordingly to make sql server to perform better. syntax update_statistics
full story