Archive for the ‘SQL Server 2011 (Denali)’ Category

Setting the PowerShell Execution Policy

Posted by Jugal Shah on May 22, 2012

Problem
Recently I moved PowerShell script files to a production environment and when executing it from the command prompt, I got this error: “File cannot be loaded because the execution of scripts is disabled on this system. Please see “get-help about_signing” for more details”. In this tip we cover what needs to be done to resolve this issue.

Solution
http://www.mssqltips.com/sqlservertip/2702/setting-the-powershell-execution-policy/

Posted in SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Error, Script, T-SQL, Power Shell, get- help about_signing | 1 Comment »

How to make SQL Server View Read Only?

Posted by Jugal Shah on May 21, 2012

In SQL Server a view represents a virtual table. Just like a real table, a view consists of rows with columns, and you can retrieve data from a view (even you can INSERT/UPDATE/DELETE data in a view). The fields in the view’s virtual table are the fields of one or more real tables in the database. You can use views to join two tables in your database and present the underlying data as if the data were coming from a single table, thus simplifying the schema of your database for users performing ad-hoc reporting. You can also use views as a security mechanism to restrict the data available to end users

See the below example how we can make the view read only.


--creating a sample table
Create table tbl1
(
	myID int,
	name varchar(10)
)

--inserting data
insert into tbl1 values(1,'Jugal'),(2,'SQL'),(3,'DBPool')

--creating sample view
create view vwtbl1
as
select * from tbl1

--inserting data using view
insert into vwtbl1 values(1,'Jugal'),(2,'SQL'),(3,'DBPool')

--altering view to make it readOnly
alter view vwtbl1
as
select myid,name from tbl1
union all
select 0,0 where 1 =0

INSERT/UPDATE/DELETE will fail with the below errors.

Msg 4406, Level 16, State 1, Line 1 Update or insert of view or function 'vwtbl1' failed because it contains a derived or constant field.

Msg 4426, Level 16, State 1, Line 1 View 'vwtbl1' is not updatable because the definition contains a UNION operator.

Posted in SQL Server, SQL Server 2008, Database, DB Articles, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: RDBMS, T-SQL, View, SQL Code, Query, Read Only | 1 Comment »

SQL Server Login Failure Error 18456, Severity 14, State 10

Posted by Jugal Shah on April 28, 2012

Problem
Recently I experienced a situation where a SQL Server login failed with this error message:
Error: 18456, Severity: 14, State: 10.
Login failed for user ‘SQL_Login’. Reason: Password validation failed with an infrastructure error. Check for previous errors. [CLIENT: XXX.XXX.XXX.XXX].

Can you help me decipher this SQL Server issue and correct it? Check out this tip to learn more.

Solution
http://www.mssqltips.com/sqlservertip/2679/sql-server-login-failure-error-18456-severity-14-state-10/

Posted in SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Error 18456, Login Failure, Severity 14, State 10, sys.dm_os_ring_buffers, sys.ms_ticks | 1 Comment »

Script to Create Foreign Key on the Compound Primary Key

Posted by Jugal Shah on April 16, 2012

Compound Primary key is a primary key which is created on more than one column. Now the questions is how to create the foreign key for the compound primary key where it references more than one column.

Check the below example.

create table employee
(
	empID int not null,
	SSN int not null,
	name varchar(20)
)


ALTER TABLE [employee]
ADD CONSTRAINT pk_employee PRIMARY KEY (empID, SSN)


create table EmpDetail
(
		empID int,
		SSN int,
		address varchar(20),
		city varchar(20),
		pin varchar(20)
)

ALTER TABLE dbo.empDetail
   ADD CONSTRAINT FK_Employee
   FOREIGN KEY(empID, SSN)
   REFERENCES dbo.employee(empID, SSN)


SELECT
    tc.TABLE_NAME,
    tc.CONSTRAINT_NAME, 
    ccu.COLUMN_NAME
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu 
      ON ccu.TABLE_NAME = tc.TABLE_NAME AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE
    tc.TABLE_NAME IN ('employee','employeeDetail')

Posted in Database, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Compound Key, Foreign Key, SQL, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T-SQL, Technology | Leave a Comment »

Primary Key, Unique Key Constraints – Clustered Index and Non Clustered Index

Posted by Jugal Shah on April 15, 2012

You can use the below script to create the Primary Key on the already existing tables. Primary key enforces a uniqueness in the column and created the clustered index as default.

Primary key will not allow NULL values.

-- Adding the NON NULL constraint
ALTER TABLE [TableName]	 
ALTER COLUMN PK_ColumnName int NOT NULL

--Script to add the primary key on the existing table
ALTER TABLE [TableName]
ADD CONSTRAINT pk_ConstraintName PRIMARY KEY (PK_ColumnName)

If you want to define or create the non-clustered index on the existing table, you can use the below script. If the data in the column is unique, you can create the Unique Constraint as well.

Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.

--script to create non-clustered Index
create index IX_ColumName on TableName(ColumnName)
--script to create Unique constraint on the existing table
ALTER TABLE TableName ADD CONSTRAINT ConstraintName UNIQUE(ColumnName)

Posted in Database, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Software Development, SQL, SQL Coding Standards, SQL Constraint and Keys, SQL Download, SQL Error Messages, SQL Index, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Tips and Tricks, T-SQL, Technology | Leave a Comment »

How to connect the SQL Server running on the different TCP/IP port?

Posted by Jugal Shah on March 14, 2012

In case you have configured SQL Instance to use the static TCP/IP port number. You can connect SQL Server as below using SSMS.

Posted in OS and SQL, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Jugal Shah, SQL Server, SQLDBPool, SSMS, Static Port, TCP Port | 1 Comment »

Script to create a Database with different collation

Posted by Jugal Shah on March 4, 2012

One of my blog reader has asked how to create the database with different collation. You can create the database with different collation using below script.

create database SQLDB collate Latin1_General_CS_AS;
go
create database SQLDB1 collate Latin1_General_CI_AS;
go

SELECT DATABASEPROPERTYEX('SQLDB', 'Collation') SQLDB;
SELECT DATABASEPROPERTYEX('SQLDB1', 'Collation') SQLDB1;

Posted in Database, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Collation, http://sqldbguru.blogspot.com/, Jugal Shah, SQLDBPool.com, T-SQL Script | Leave a Comment »

Script to drop all SQL Logins

Posted by Jugal Shah on March 2, 2012

Recently I got a requirement to drop all SQL Logins from the SQL Server. I have created below script to drop all SQL Login.

Before getting into detail, please take a note that “A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped”

SA login account and the few policy certificates also come in SQL Login type, I have excluded them.

select *
from sys.server_principals
where name = 'sa' or substring(name, 1, 1) = '#'

select 'DROP LOGIN ' + [name]
from sys.server_principals
where type_desc = 'SQL_LOGIN'
and sid <> 0x01 and substring(name, 1, 1) <> '#'

Next step is to execute the output of the above query.

Posted in Database, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Drop Login, SQL Login, SQL Server, SQLDBPool, T-SQL Script | Leave a Comment »

Steps to Drop an Orphan SQL Server User when it owns a Schema or Role

Posted by Jugal Shah on March 2, 2012

Problem
Recently I had an issue while dropping an orphaned user for an obsolete login. I was unable to drop the user and it failed with the below error messages.

Msg 15421, Level 16, State 1, Line 1 The database principal owns a database role and cannot be dropped. Msg 15138, Level 16, State 1, Line 1 The database principal owns a schema in the database, and cannot be dropped.</code

Solution http://www.mssqltips.com/sqlservertip/2620/steps-to-drop-an-orphan-sql-server-user-when-it-owns-a-schema-or-role/

Posted in SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Jugal Shah, Login Issue, Orphan Login, Orphan User, SQLDBPool, T-SQL Script | Leave a Comment »

Steps to Save/Store file into Database

Posted by Jugal Shah on February 16, 2012

You can use the below scripts to store/save the file into SQL Server database table. Please note it is not recommended to store file into database. You can store the file on file system and path in the database.

use sqlDBPool
--documents table will store files into varbinary field
--drop table documents
create table documents
(
	documentID int identity(1,1),
	doctype char(5),
	document varbinary(max)
)


--script to store/save document into table
insert into documents 
 Select 'xls', (SELECT * FROM OPENROWSET(BULK N'C:\JSpace\book1.xls', SINGLE_BLOB) AS document) document
go

select * from documents

Posted in SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: How to store image into database, Jugal Shah, save file into table, SQL Script, SQLDBPool, store, T-SQL | 1 Comment »

@@MICROSOFTVERSION Function

Posted by Jugal Shah on February 12, 2012

Problem: Take an example you are writing a script which is going to be deployed on the all the versions of SQL Servers and you want to check the SQL Server version details using T-SQL code. Below solution will guide you how to check the SQL Server version in stored procedure/t-sql batch.

Solution: You can use the @@MICROSOFTVERSION to get the SQL Server version information. If the output of the below script is 9 than its SQL 2005, if 10 than SQL Server 2008 and if 11 than SQL Server 2011

select @@VERSION

--method - 1
select @@MICROSOFTVERSION as MSVersion, CAST (@@MICROSOFTVERSION as BINARY(5)) as MsVersionInBinary
-- Remove the first non-zero character after 0x0 from binary output here it is A and divide the @@MicrosoftVersion outout 
select substring(cast(@@MICROSOFTVERSION/0x000000640 as varchar(10)),1,2) as MsSQLVersion

--Method 2
select @@MICROSOFTVERSION / POWER(2,24) as usingPowerFunctionMSSQLVersion

Posted in Database, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: @@MICROSOFTVERSION, How to check SQL Version, Jugal Shah, SQL Script, SQLDBPool, T-SQL | Leave a Comment »

Steps to create the deadlock scenario

Posted by Jugal Shah on February 12, 2012

A deadlock occurs when two or more processes permanently block each other by each process having a lock on a resource which the other process are trying to lock.

Please execute the below queries as per the mentioned comments to produce a deadlock.

--turning on the traceflag to record deadlock info into error log
dbcc traceon(1204,-1)
dbcc tracestatus(1204)

--creating test database
create database sqlDBPool
--Connecting to SQLDBPool database
use sqldbpool
--table creation
create table tb1 (col1 int)
create table tb2 (col1 int)
--inserting dummy records
insert into tb1 values(1),(2),(3)
insert into tb2 values(1),(2),(3)

--Open first connection to update table explicit transaction
begin transaction
  update tb1 set col1 = 5
  
--Open second connection to update table explicit transaction
use sqlDBPool
begin transaction
  update tb2 set col1 = 6
  update tb1 set col1 = 6

--Open first connection to update table explicit transaction
  update tb2 set col1 = 5

You can see the one of the transaction will fail with the below error message.

Msg 1205, Level 13, State 45, Line 3 Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

As we have turned on the deadlock trace flag, you can see the below information in the SQL Server error log.

Starting up database 'sqlDBPool'.
Deadlock encountered .... Printing deadlock information
Wait-for graph
NULL
Node:1  
RID: 9:1:153:0                 CleanCnt:2 Mode:X Flags: 0x3
 Grant List 1:
   Owner:0x05684480 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:52 ECID:0 XactLockInfo: 0x065F82A8
   SPID: 52 ECID: 0 Statement Type: UPDATE Line #: 1
   Input Buf: Language Event: update tb2 set col1 = 5
Requested by: 
  ResType:LockOwner Stype:'OR'Xdes:0x05A8CC10 Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x05A70354) Value:0x6767b20 Cost:(0/432)
NULL
Node:2  
RID: 9:1:155:0                 CleanCnt:2 Mode:X Flags: 0x3
 Grant List 2:
   Owner:0x067679A0 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:55 ECID:0 XactLockInfo: 0x05A8CC38
   SPID: 55 ECID: 0 Statement Type: UPDATE Line #: 3
   Input Buf: Language Event: begin transaction    update tb2 set col1 = 6    update tb1 set col1 = 6
Requested by: 
  ResType:LockOwner Stype:'OR'Xdes:0x065F8280 Mode: U SPID:52 BatchID:0 ECID:0 TaskProxy:(0x0941A354) Value:0x6a943a0 Cost:(0/432)
NULL
Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x05A8CC10 Mode: U SPID:55 BatchID:0 ECID:0 TaskProxy:(0x05A70354) Value:0x6767b20 Cost:(0/432)

Posted in Database, DB Articles, SQL Scripts, SQL Server, SQL Server 2008, SQL Server 2008 R2, SQL Server 2011 (Denali) | Tagged: Deadlock, deadlock scenario, Deadlock trace flag, Jugal Shah, Script, SQLDBPool, T-SQL | 2 Comments »

« Previous Entries

Click to subscribe to this blog

Blog Readers

Disclaimer

Database Support

Top Rated

Follow Me

About Me

Contact Me

Twitter

SQLDBPool Facebook Page