Wait for this ebook ( "Troubleshooting SQL Server: A Guide for the Accidental DBA") from Jonathan Kehayias for few weeks and I receive newsletter from Simple-Talk today!
Quote from Simple Talk
"
•Understand how to approach troubleshooting and the essential tools you need.
•Explore the areas where problems regularly arise and the tools you can use to diagnose them.
•Get practical solutions to remove the root causes of issues, rather than "papering over the cracks".
"
You can download the ebook from here
You should subscribe newsletter from Simple-Talk
Monday, November 14, 2011
Friday, October 28, 2011
SQL Denali Resource
Free Ebook
Introducing Microsoft SQL Server Code Name “Denali” (DRAFT Preview)
Free Resource download from Microsoft.com
Microsoft® SQL Server® code name 'Denali', Community Technology Preview 3 (CTP 3) Product Guide
Download the datasheets, white papers, technical presentations, demonstrations, and links to videos that will help you evaluate Microsoft® SQL Server® code name 'Denali.'
*Added 7/Feb/2012
SQL Server Virtual Lab
http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx?WT.mc_id=soc-n-[TR]-loc-[Services]-[farukc]
Introducing Microsoft SQL Server Code Name “Denali” (DRAFT Preview)
Free Resource download from Microsoft.com
Microsoft® SQL Server® code name 'Denali', Community Technology Preview 3 (CTP 3) Product Guide
Download the datasheets, white papers, technical presentations, demonstrations, and links to videos that will help you evaluate Microsoft® SQL Server® code name 'Denali.'
*Added 7/Feb/2012
SQL Server Virtual Lab
http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx?WT.mc_id=soc-n-[TR]-loc-[Services]-[farukc]
Saturday, October 22, 2011
replication issue 'sp_repldone/sp_replcounters'
Summary:- SQL server out of diskspace.
- This cause by replication issue. Replication fail and space in transaction log not release.
-Fix the replication issue with change log reader agent property and restart replication log reader .
value change for log reader agent :
Query Timeout (3600) and ReadBatchSize (100).
-Replication resumed and Transaction log size clear.
Detail:
Checking
1. SQL error log
- Replication-Replication Transaction-Log Reader Subsystem: agent servername-dbname-4 scheduled for retry. The process could not execute 'sp_repldone/sp_replcounters' on 'servername'.
2. Replication monitor
3. Transaction grow very huge, even with backup transaction, the content can't clear
How to solve:
1. From replication monitor->My Publishers->Servername->Right Click ->Agent Profiles->Select log reader agent
2. Select new agent profile and press OK
3. GO to Job Activity Monitor, restart this job servername-database-4 under Repl-log_reader category
4. GO back to Replication monitor, and you will see
5. 5Before we choose the new agent profiler, “The process is running and waiting for a response from the server” run 6 time and will show error
Agent servername-database-4' is retrying after an error. 185 retries attempted. See agent job history in the Jobs folder for more details.
6. For new log, we see this records more than 6 time ““The process is running and waiting for a response from the server””
7. After log reader finish scan transaction log , replicate start.
8.After replication successful and transaction log start backup, free space of transaction log is resumed
9. Transaction log can be shrink now and free space of the server resumed.
Reference:Reference
- This cause by replication issue. Replication fail and space in transaction log not release.
-Fix the replication issue with change log reader agent property and restart replication log reader .
value change for log reader agent :
Query Timeout (3600) and ReadBatchSize (100).
-Replication resumed and Transaction log size clear.
Detail:
Checking
1. SQL error log
- Replication-Replication Transaction-Log Reader Subsystem: agent servername-dbname-4 scheduled for retry. The process could not execute 'sp_repldone/sp_replcounters' on 'servername'.
2. Replication monitor
3. Transaction grow very huge, even with backup transaction, the content can't clear
How to solve:
1. From replication monitor->My Publishers->Servername->Right Click ->Agent Profiles->Select log reader agent
2. Select new agent profile and press OK
3. GO to Job Activity Monitor, restart this job servername-database-4 under Repl-log_reader category
4. GO back to Replication monitor, and you will see
5. 5Before we choose the new agent profiler, “The process is running and waiting for a response from the server” run 6 time and will show error
Agent servername-database-4' is retrying after an error. 185 retries attempted. See agent job history in the Jobs folder for more details.
6. For new log, we see this records more than 6 time ““The process is running and waiting for a response from the server””
7. After log reader finish scan transaction log , replicate start.
8.After replication successful and transaction log start backup, free space of transaction log is resumed
9. Transaction log can be shrink now and free space of the server resumed.
Reference:Reference
Thursday, June 30, 2011
SQL Fill Factor
SQL Fill Factor
Learn about SQL Fill Factor, and come out the summary.
Correct me if I wrong on SQL Fill Factor
- Fill factor setting define how full the page should be fill when index is created.
- Use fill factor to prevent frequent page split for high update/insert operation.
- Fill factor setting Will not take affect if table no has data when we define fill factor on table.
- If table has data when index created/rebuilt, Create/rebuilt index operation will redistribute data in leaf page based on the fill factor define.
- Low fill factor will slower data selection operation as data span across more page compare to high fill factor.
- Low fill factor will use more disk space as well.
- For table with high insert/update operation, high fill factor will cause page split and operation will be slow.
- For data will be added to the end of the table, fill factor should be default 0. Example index with identity column.
Reference
Fill Factor, Microsoft, http://msdn.microsoft.com/en-us/library/ms177459.aspx
Fill Factor, Microsoft, http://msdn.microsoft.com/en-us/library/aa933139%28v=sql.80%29.aspx
Learn about SQL Fill Factor, and come out the summary.
Correct me if I wrong on SQL Fill Factor
- Fill factor setting define how full the page should be fill when index is created.
- Use fill factor to prevent frequent page split for high update/insert operation.
- Fill factor setting Will not take affect if table no has data when we define fill factor on table.
- If table has data when index created/rebuilt, Create/rebuilt index operation will redistribute data in leaf page based on the fill factor define.
- Low fill factor will slower data selection operation as data span across more page compare to high fill factor.
- Low fill factor will use more disk space as well.
- For table with high insert/update operation, high fill factor will cause page split and operation will be slow.
- For data will be added to the end of the table, fill factor should be default 0. Example index with identity column.
Reference
Fill Factor, Microsoft, http://msdn.microsoft.com/en-us/library/ms177459.aspx
Fill Factor, Microsoft, http://msdn.microsoft.com/en-us/library/aa933139%28v=sql.80%29.aspx
Friday, June 24, 2011
SQL Table no have index key
I use the below script to search
1. SQL table without clustered index
2. SQL table without any index
-- this only show table without clustered index
SELECT
sys.tables.name,
sys.indexes.name
FROM sys.tables
left join sys.indexes
ON sys.tables.object_id = sys.indexes.object_id
WHERE
isnull(sys.indexes.name,'')=''
and sys.tables.name = 'tablename'
ORDER BY
sys.tables.name
--this will show table without any index
SELECT
a.name,
FROM sys.tables a
left join sys.indexes b
ON a.object_id = b.object_id
WHERE
isnull(b.name,'')=''
--and sys.tables.name = 'PackageServerPathLink'
and not exists (select 1 from sys.indexes c where isnull(name,'')<>''
and c.object_id=b.object_id)
ORDER BY
a.name
1. SQL table without clustered index
2. SQL table without any index
-- this only show table without clustered index
SELECT
sys.tables.name,
sys.indexes.name
FROM sys.tables
left join sys.indexes
ON sys.tables.object_id = sys.indexes.object_id
WHERE
isnull(sys.indexes.name,'')=''
and sys.tables.name = 'tablename'
ORDER BY
sys.tables.name
--this will show table without any index
SELECT
a.name,
FROM sys.tables a
left join sys.indexes b
ON a.object_id = b.object_id
WHERE
isnull(b.name,'')=''
--and sys.tables.name = 'PackageServerPathLink'
and not exists (select 1 from sys.indexes c where isnull(name,'')<>''
and c.object_id=b.object_id)
ORDER BY
a.name
Thursday, March 24, 2011
Troubleshoot on timeout query
We have one request to trace on timeout issue on applicaition.
I setup profiler to trace which query has timeout issue
Error and Warnings
- Attention
Stored Procedures
- RPC: Starting
- SP: Starting
- SP: StmtStarting
TSQL
- SQL: BatchStarting
- SQL: StmtStarting
Setting sample table and data
Create table member3
(member_no int,
lastName varchar(20),
firstName varchar(20));
insert into member3 values (1,'tan','tanlast'),(100,'lee','leelast');
Setting timeout environment
begin tran
update member3
set lastname='me'
where member_no=1
Open a second Microsoft SQL Server Management Studio
In connect to server box, Click on Option and change Execution time-out from 0 (unlimited) to 2 seconds
Press connect
In Query Window, type this,
select * from member3
where member_no=100
After run for 2 second, SQL will give the below message
Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
From SQL Profiler, look for EventClass=Attention which highlight with red color
You can do this to find “Attention”
CTRL+F, In “Find what” column, keying “Attention”
After get the EventClass with “Attention”, look for the SPID. In this example, SPID for “Attention” = 58
Look backward for SPID=58, you will know the above query is face timeout
In a real production environment, lot of the query is running and we difficult to find the backward query. We can do in this way.
Save the trace log in trace file
Open the trace file that you save just now
Filter the SPID from File-> Properties -> Events Selection -> Column Filter -> SPID -> Equals -> Keying in 58 and press ok, ok
The SQL profiler will only show trace with SPID 58, which easy us to search which query cause timeout.
I setup profiler to trace which query has timeout issue
Error and Warnings
- Attention
Stored Procedures
- RPC: Starting
- SP: Starting
- SP: StmtStarting
TSQL
- SQL: BatchStarting
- SQL: StmtStarting
Setting sample table and data
Create table member3
(member_no int,
lastName varchar(20),
firstName varchar(20));
insert into member3 values (1,'tan','tanlast'),(100,'lee','leelast');
Setting timeout environment
begin tran
update member3
set lastname='me'
where member_no=1
Open a second Microsoft SQL Server Management Studio
In connect to server box, Click on Option and change Execution time-out from 0 (unlimited) to 2 seconds
Press connect
In Query Window, type this,
select * from member3
where member_no=100
After run for 2 second, SQL will give the below message
Msg -2, Level 11, State 0, Line 0
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
From SQL Profiler, look for EventClass=Attention which highlight with red color
You can do this to find “Attention”
CTRL+F, In “Find what” column, keying “Attention”
After get the EventClass with “Attention”, look for the SPID. In this example, SPID for “Attention” = 58
Look backward for SPID=58, you will know the above query is face timeout
In a real production environment, lot of the query is running and we difficult to find the backward query. We can do in this way.
Save the trace log in trace file
Open the trace file that you save just now
Filter the SPID from File-> Properties -> Events Selection -> Column Filter -> SPID -> Equals -> Keying in 58 and press ok, ok
The SQL profiler will only show trace with SPID 58, which easy us to search which query cause timeout.
Tuesday, March 01, 2011
SQL Free Learning Resource
SQL Server 2008 Microsoft Certified Master (MCM) Readiness Videos
Total 70 Video post by Microsoft.
Topic include : Database Structures, Database Structures - Demo Video, New Database Structures in SQL Server 2008, New Database Structures in SQL Server 2008 - Demo Video, Data File Internals and Maintenance. .and more. Please visit Microsoft Site to look for more
You can download in the below format:
Video: WMV | 3GP | iPod | Zune | MP4 | WMV (ZIP) | PSP
Audio: AAC | WMA | MP3 | MP4
SQL Server 2008 R2 Update for Developers Training Kit (January 2011 Update)
This update include how to build Microsoft Business Intelligence Solution with SQL 2008 R2"
Summary of what include in download package:
31 presentations (PowerPoint slides, videos and transcripts included)
27 demos (installer scripts, videos and transcripts included)
12 hands on labs (installer scripts included)
SQL Stairway From SQLServercentral.com
Just wonder how to start the below topic ?
Database Design
Stairway to Integration Services
Stairway to MDX
Stairway to Server-side Tracing
Stairway to SQL Server Agent
Stairway to SQL Server Indexes
Stairway to SQL Server Replication
Stairway to SQL Server Reporting Services
Stairway to StreamInsight
Visit this SQLServercentral.com
SQLBits.com share video session for last year conference (2010), and it free
SQL Server 2008 Jump Start
There are 5 tracks in this event: Overview Sessions, Database Infrastructure and Scalability, Business Intelligence Part I & II, Developer & Software + Services, and Application Compatibility & Upgrade.
Total 70 Video post by Microsoft.
Topic include : Database Structures, Database Structures - Demo Video, New Database Structures in SQL Server 2008, New Database Structures in SQL Server 2008 - Demo Video, Data File Internals and Maintenance. .and more. Please visit Microsoft Site to look for more
You can download in the below format:
Video: WMV | 3GP | iPod | Zune | MP4 | WMV (ZIP) | PSP
Audio: AAC | WMA | MP3 | MP4
SQL Server 2008 R2 Update for Developers Training Kit (January 2011 Update)
This update include how to build Microsoft Business Intelligence Solution with SQL 2008 R2"
Summary of what include in download package:
31 presentations (PowerPoint slides, videos and transcripts included)
27 demos (installer scripts, videos and transcripts included)
12 hands on labs (installer scripts included)
SQL Stairway From SQLServercentral.com
Just wonder how to start the below topic ?
Database Design
Stairway to Integration Services
Stairway to MDX
Stairway to Server-side Tracing
Stairway to SQL Server Agent
Stairway to SQL Server Indexes
Stairway to SQL Server Replication
Stairway to SQL Server Reporting Services
Stairway to StreamInsight
Visit this SQLServercentral.com
SQLBits.com share video session for last year conference (2010), and it free
SQL Server 2008 Jump Start
There are 5 tracks in this event: Overview Sessions, Database Infrastructure and Scalability, Business Intelligence Part I & II, Developer & Software + Services, and Application Compatibility & Upgrade.
Thursday, January 27, 2011
Alternative method of insert data if not found, update record if data already exists
Recover Merge SQL Statement in Microsoft SQL Server 2008 Product Overview
Previously, SQL Statement for "insert data if not found, update record if data already exists" :
If (select 1 from table where id=@id)
Update
Else
Insert
With merge function, we can do this way
MERGE Table1 cs
USING Table2 cd
on Table1.id=cd.table1_id
WHEN MATCHED THEN
UPDATE
SET cs.Cust_Name=cd.Cust_name,
cs.Cust_Surname=cd.Cust_surname,
cs.Cust_Email=cd.Cust_email
WHEN NOT MATCHED BY TARGET THEN
INSERT (Cust_ID,cust_name,cust_surname,cust_email)
VALUES(cd.Cust_ID, cd.Cust_Name,cd.Cust_Surname,cd.Cust_Email);
Merge function can do more
If not match by target
If not match by source
Output
Reference
SQL Server 2008: Locking with MERGE statement compared to Update/Insert in SQL Server 2005
SQL Server: Best way to Update row if exists, Insert if not
The MERGE Statement in SQL Server 2008
SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
Using SQL Server 2008's MERGE statement
---------------------------------------------------------------------------------
Example
Create database temptest1456
use temptest1456
CREATE TABLE [dbo].[Customers](
[Cust_ID] [int] NOT NULL,
[Cust_Name] [varchar](50) NOT NULL,
[Cust_Surname] [varchar](50) NOT NULL,
[Cust_Email] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE TYPE [dbo].[customertype] AS TABLE(
[Cust_ID] [int] NULL,
[Cust_Name] [varchar](50) NULL,
[Cust_Surname] [varchar](50) NULL,
[Cust_Email] [varchar](50) NULL
)
GO
insert into Customers values (1,'1steven','1gerrard','1sg@liverpool.com')
insert into Customers values (2,'1jamie','1gerrard','1sg@liverpool.com')
insert into Customers values (3,'1Kamie','1Kerrard','1sg@liverpool.com')
CREATE procedure newcustomer(@cust_id varchar(50),@cust_name varchar(50),@cust_surname varchar(50),@cust_email varchar(50))
as
begin
IF exists (select 1 FROM Customers where Cust_ID=@cust_id)
update Customers
set Cust_Name=@cust_name,
Cust_Surname=@cust_surname,
Cust_Email=@cust_email
where Customers.Cust_ID= @cust_id
ELSE
insert into customers
(Cust_ID,Cust_Name,Cust_Surname,Cust_Email)
values (@cust_id,@cust_name,@cust_surname,@cust_email)
END
Exec newcustomer 3, '2jamie','2caragher','2jc@liverpool.com'
Exec newcustomer 4, '2kamie','2daragher','2kc@liverpool.com'
Exec newcustomer 6, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 7, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 8, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 9, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 10, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 11, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 12, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 13, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 14, '2kamie','2daragher','2kc@liverpool.com'
Exec newcustomer 16, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 17, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 18, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 19, '2lamie','2earagher','2lc@liverpool.com'
Alter procedure newcustomer(@Customer_details customertype READONLY)
as
begin
update Customers
set Cust_Name=b.Cust_name,
Cust_Surname=b.Cust_surname,
Cust_Email=b.Cust_email
from @Customer_details b
where Customers.Cust_ID=b.Cust_Id
insert into customers
select * from @Customer_details a
where not exists (Select 1 from Customers b where a.cust_id=b.Cust_ID)
end
DECLARE @Customer_details AS customertype
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (5, '3jamie','3caragher','3jc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (6, '3kamie','3daragher','3kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (7, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (8, '3kamie','3daragher','3kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (9, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (10, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (11, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (12, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (13, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (14, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (15, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (16, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (17, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (18, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (19, '3lamie','3earagher','3lc@liverpool.com')
exec newcustomer1 @customer_details
ALTER procedure newcustomer (@Customer_details customertype READONLY)
as
BEGIN
MERGE Customers cs
USING @Customer_details cd
on cs.cust_id=cd.cust_id
WHEN MATCHED THEN
UPDATE
SET cs.Cust_Name=cd.Cust_name,
cs.Cust_Surname=cd.Cust_surname,
cs.Cust_Email=cd.Cust_email
WHEN NOT MATCHED BY TARGET THEN
INSERT (Cust_ID,cust_name,cust_surname,cust_email)
VALUES(cd.Cust_ID, cd.Cust_Name,cd.Cust_Surname,cd.Cust_Email);
END
DECLARE @Customer_details AS customertype
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (7, '4jamie','4caragher','4jc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (8, '4kamie','4daragher','4kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (9, '5lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (15, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (16, '4jamie','4caragher','4jc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (17, '4kamie','4daragher','4kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (22, '5lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (23, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (24, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (25, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (26, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (27, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (28, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (29, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (30, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (31, '4lamie','4earagher','4lc@liverpool.com')
exec newcustomer1 @customer_details
drop database temptest1456
Previously, SQL Statement for "insert data if not found, update record if data already exists" :
If (select 1 from table where id=@id)
Update
Else
Insert
With merge function, we can do this way
MERGE Table1 cs
USING Table2 cd
on Table1.id=cd.table1_id
WHEN MATCHED THEN
UPDATE
SET cs.Cust_Name=cd.Cust_name,
cs.Cust_Surname=cd.Cust_surname,
cs.Cust_Email=cd.Cust_email
WHEN NOT MATCHED BY TARGET THEN
INSERT (Cust_ID,cust_name,cust_surname,cust_email)
VALUES(cd.Cust_ID, cd.Cust_Name,cd.Cust_Surname,cd.Cust_Email);
Merge function can do more
If not match by target
If not match by source
Output
Reference
SQL Server 2008: Locking with MERGE statement compared to Update/Insert in SQL Server 2005
SQL Server: Best way to Update row if exists, Insert if not
The MERGE Statement in SQL Server 2008
SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
Using SQL Server 2008's MERGE statement
---------------------------------------------------------------------------------
Example
Create database temptest1456
use temptest1456
CREATE TABLE [dbo].[Customers](
[Cust_ID] [int] NOT NULL,
[Cust_Name] [varchar](50) NOT NULL,
[Cust_Surname] [varchar](50) NOT NULL,
[Cust_Email] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE TYPE [dbo].[customertype] AS TABLE(
[Cust_ID] [int] NULL,
[Cust_Name] [varchar](50) NULL,
[Cust_Surname] [varchar](50) NULL,
[Cust_Email] [varchar](50) NULL
)
GO
insert into Customers values (1,'1steven','1gerrard','1sg@liverpool.com')
insert into Customers values (2,'1jamie','1gerrard','1sg@liverpool.com')
insert into Customers values (3,'1Kamie','1Kerrard','1sg@liverpool.com')
CREATE procedure newcustomer(@cust_id varchar(50),@cust_name varchar(50),@cust_surname varchar(50),@cust_email varchar(50))
as
begin
IF exists (select 1 FROM Customers where Cust_ID=@cust_id)
update Customers
set Cust_Name=@cust_name,
Cust_Surname=@cust_surname,
Cust_Email=@cust_email
where Customers.Cust_ID= @cust_id
ELSE
insert into customers
(Cust_ID,Cust_Name,Cust_Surname,Cust_Email)
values (@cust_id,@cust_name,@cust_surname,@cust_email)
END
Exec newcustomer 3, '2jamie','2caragher','2jc@liverpool.com'
Exec newcustomer 4, '2kamie','2daragher','2kc@liverpool.com'
Exec newcustomer 6, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 7, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 8, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 9, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 10, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 11, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 12, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 13, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 14, '2kamie','2daragher','2kc@liverpool.com'
Exec newcustomer 16, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 17, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 18, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 19, '2lamie','2earagher','2lc@liverpool.com'
Alter procedure newcustomer(@Customer_details customertype READONLY)
as
begin
update Customers
set Cust_Name=b.Cust_name,
Cust_Surname=b.Cust_surname,
Cust_Email=b.Cust_email
from @Customer_details b
where Customers.Cust_ID=b.Cust_Id
insert into customers
select * from @Customer_details a
where not exists (Select 1 from Customers b where a.cust_id=b.Cust_ID)
end
DECLARE @Customer_details AS customertype
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (5, '3jamie','3caragher','3jc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (6, '3kamie','3daragher','3kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (7, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (8, '3kamie','3daragher','3kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (9, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (10, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (11, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (12, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (13, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (14, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (15, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (16, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (17, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (18, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (19, '3lamie','3earagher','3lc@liverpool.com')
exec newcustomer1 @customer_details
ALTER procedure newcustomer (@Customer_details customertype READONLY)
as
BEGIN
MERGE Customers cs
USING @Customer_details cd
on cs.cust_id=cd.cust_id
WHEN MATCHED THEN
UPDATE
SET cs.Cust_Name=cd.Cust_name,
cs.Cust_Surname=cd.Cust_surname,
cs.Cust_Email=cd.Cust_email
WHEN NOT MATCHED BY TARGET THEN
INSERT (Cust_ID,cust_name,cust_surname,cust_email)
VALUES(cd.Cust_ID, cd.Cust_Name,cd.Cust_Surname,cd.Cust_Email);
END
DECLARE @Customer_details AS customertype
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (7, '4jamie','4caragher','4jc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (8, '4kamie','4daragher','4kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (9, '5lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (15, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (16, '4jamie','4caragher','4jc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (17, '4kamie','4daragher','4kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (22, '5lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (23, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (24, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (25, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (26, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (27, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (28, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (29, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (30, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (31, '4lamie','4earagher','4lc@liverpool.com')
exec newcustomer1 @customer_details
drop database temptest1456
Monday, January 03, 2011
SQL 2008 Logon Trigger
Create Logon Trigger
CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
ROLLBACK
END
Drop Logon trigger
DROP TRIGGER logon_trigger_not_from_excel ON ALL SERVER
Which trigger appear in SQL Server
SELECT name, s.type_desc SQL_or_CLR,
is_disabled, e.type_desc FiringEvents
FROM sys.server_triggers s
INNER JOIN sys.server_trigger_events e ON
s.object_id = e.object_id
What is the metadata of Trigger?
SELECT t.name, m.definition
FROM sys.server_sql_modules m
INNER JOIN sys.server_triggers t ON
m.object_id = t.object_id
Refenrece:
Logon Triggers
Frederik Vandeputte : SQL Server Logon triggers part 2
Viewing DDL Triger Medatdata by joining sys.server_SQL_modules and sys.server_triggers
CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
ROLLBACK
END
Drop Logon trigger
DROP TRIGGER logon_trigger_not_from_excel ON ALL SERVER
Which trigger appear in SQL Server
SELECT name, s.type_desc SQL_or_CLR,
is_disabled, e.type_desc FiringEvents
FROM sys.server_triggers s
INNER JOIN sys.server_trigger_events e ON
s.object_id = e.object_id
What is the metadata of Trigger?
SELECT t.name, m.definition
FROM sys.server_sql_modules m
INNER JOIN sys.server_triggers t ON
m.object_id = t.object_id
Refenrece:
Logon Triggers
Frederik Vandeputte : SQL Server Logon triggers part 2
Viewing DDL Triger Medatdata by joining sys.server_SQL_modules and sys.server_triggers
SQL 2008 control access from EXCEL, ODBC, ACCESS
We want change Application access from SQL Authentication to SSPI in order to trace who is access SQL table
We worry user may connect to SQL Server from Microsoft Excel, Microsft Access, SQL Server Management Studio
After google, we found out that is a features in SQL Server to control acccess of user: Logon Trigger
Below Code from Frederik Vandeputte-SQL Server Logon triggers part 2
CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
ROLLBACK
END
After create Logon Trigger in SQL Server, I open Microsoft Excel, try connect to SQL Server, but connection successful!!
The result is not expected! SQL Server should reject the connection from Microsoft Excel
I can see the database and table in the SQL Server and successful retrieve the data and display in Excel File.
why the connection can establish even I already setup to rollback if connection from Excel?
Step to made connection from Microsoft Excel to local server:
Open Excel file->data->From Other Sources
From SQL Server-> keying .
"." for local server
log on credentials = User WIndows Authentication
I can view all database and table from local server
Now let troubleshoot why the trigger logon is not effective or no rollback the connection:
Below code from Vipul Shah's SQL Server Logon triggers part 2
CREATE DATABASE dbadb
go
use dbadb
CREATE TABLE myTest
(id int identity(1,1),
EventTime datetime,
LoginName varchar(50),
HostName varchar(50),
LoginType varchar(50),
ApplicationName varchar(100))
CREATE TRIGGER myTest_LogonTrigger ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
declare @LogonTriggerData xml,
@EventTime datetime,
@LoginName varchar(50),
@HostName varchar(50),
@LoginType varchar(50)
set @LogonTriggerData = eventdata()
set @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
set @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
set @HostName = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
set @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)')
insert into dbadb..myTest values (@EventTime, @LoginName, @HostName, @LoginType,app_name())
END
Connect to SQLServer from Microsft Excel :
Open Excel file->data->From Other Sources
From SQL Server-> keying .
"." for local server
log on credentials = User WIndows Authentication and Press "Next"
Connection succesful and "Select Database and Table" page appear
Go backup to SQLServer and type the below query
select * from dbadb.dbo.mytest
It show the below result :
It show that Excel connection appear as Application Name "2007 Microsoft Office system"
Let alter logon trigger not from excel as below
ALTER TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%2007 Microsoft Office system%' ROLLBACK
END
Make connection to SQL Server from Microsoft Excel again.
Connection Fail!
It show logon failed for login 'testuser' due to trigger execution.
We successful stop user from connection to SQLServer through Microsoft Excel.
I test few application and the associate application name detect from trigger logon listed as below:
Reference:
Logon Triggers
Prevent Access to SQL Server 2005 databases from Microsoft Excel and Word
Frederik Vandeputte : SQL Server Logon triggers part 2
We worry user may connect to SQL Server from Microsoft Excel, Microsft Access, SQL Server Management Studio
After google, we found out that is a features in SQL Server to control acccess of user: Logon Trigger
Below Code from Frederik Vandeputte-SQL Server Logon triggers part 2
CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
ROLLBACK
END
After create Logon Trigger in SQL Server, I open Microsoft Excel, try connect to SQL Server, but connection successful!!
The result is not expected! SQL Server should reject the connection from Microsoft Excel
I can see the database and table in the SQL Server and successful retrieve the data and display in Excel File.
why the connection can establish even I already setup to rollback if connection from Excel?
Step to made connection from Microsoft Excel to local server:
Open Excel file->data->From Other Sources
From SQL Server-> keying .
"." for local server
log on credentials = User WIndows Authentication
I can view all database and table from local server
Now let troubleshoot why the trigger logon is not effective or no rollback the connection:
Below code from Vipul Shah's SQL Server Logon triggers part 2
CREATE DATABASE dbadb
go
use dbadb
CREATE TABLE myTest
(id int identity(1,1),
EventTime datetime,
LoginName varchar(50),
HostName varchar(50),
LoginType varchar(50),
ApplicationName varchar(100))
CREATE TRIGGER myTest_LogonTrigger ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
declare @LogonTriggerData xml,
@EventTime datetime,
@LoginName varchar(50),
@HostName varchar(50),
@LoginType varchar(50)
set @LogonTriggerData = eventdata()
set @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
set @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
set @HostName = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
set @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)')
insert into dbadb..myTest values (@EventTime, @LoginName, @HostName, @LoginType,app_name())
END
Connect to SQLServer from Microsft Excel :
Open Excel file->data->From Other Sources
From SQL Server-> keying .
"." for local server
log on credentials = User WIndows Authentication and Press "Next"
Connection succesful and "Select Database and Table" page appear
Go backup to SQLServer and type the below query
select * from dbadb.dbo.mytest
It show the below result :
id | EventTime | LoginName | HostName | LoginType | ApplicationName |
---|---|---|---|---|---|
1 | 2011-01-01 20:25:04.467 | testuser | Windows (NT) Login | 2007 Microsoft Office system |
It show that Excel connection appear as Application Name "2007 Microsoft Office system"
Let alter logon trigger not from excel as below
ALTER TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%2007 Microsoft Office system%' ROLLBACK
END
Make connection to SQL Server from Microsoft Excel again.
Connection Fail!
It show logon failed for login 'testuser' due to trigger execution.
We successful stop user from connection to SQLServer through Microsoft Excel.
I test few application and the associate application name detect from trigger logon listed as below:
Application | App_name() |
---|---|
Excel Office Excel 2007 | 2007 Micorosoft office System |
Microsoft Office Access 2007 | 2007 Micorosoft office System |
Microsoft SQLServer Management Studio | Microsoft SQL Server Management Studio - Transact-SQL IntelliSense |
OSQL | OSQL-32 |
Reference:
Logon Triggers
Prevent Access to SQL Server 2005 databases from Microsoft Excel and Word
Frederik Vandeputte : SQL Server Logon triggers part 2
Subscribe to:
Posts (Atom)