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.
Tuesday, March 01, 2011
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
Wednesday, December 22, 2010
FULLTEXT STOPLIST SQL 2008
In SQL FULLTEXT, we can discards commonly occurring string from build into FULLTEXT INDEX.
Example : is, an, i, we. Thease discarded string s are called stopwords
In SQL 2005 and below, stoplist is called noise word and define in noise file under fulltext database.
Noise word is applied through SQL Instance
In SQL 2008, we can define STOPLIST in database instance
There is 3 option to select when we create STOPLIST
Create an empty stoplist
Create from the system stoplist
Create from an exisiting full-text stop list
System stoplist
select * from sys.fulltext_system_stopwords
Current database stoplist
select * from sys.fulltext_stoplists
I try create stoplist in adventurework
but sql return with error
CREATE FULLTEXT STOPLIST myStoplist;
"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'STOPLIST'."
I just notice in "Stopwords and Stoplists", it did mention :
"Important CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are supported only under compatibility level 100. Under compatibility levels 80 and 90, these statements are not supported. However, under all compatibility levels the system stoplist is automatically associated with new full-text indexes."
I change the database compatibility level from 90 to level 100 and execute the query again
CREATE FULLTEXT STOPLIST myStoplist;
Stoplist create successful
Reference :
Stopwords and Stoplists
http://msdn.microsoft.com/en-us/library/ms142551.aspx
Example : is, an, i, we. Thease discarded string s are called stopwords
In SQL 2005 and below, stoplist is called noise word and define in noise file under fulltext database.
Noise word is applied through SQL Instance
In SQL 2008, we can define STOPLIST in database instance
There is 3 option to select when we create STOPLIST
Create an empty stoplist
Create from the system stoplist
Create from an exisiting full-text stop list
System stoplist
select * from sys.fulltext_system_stopwords
Current database stoplist
select * from sys.fulltext_stoplists
I try create stoplist in adventurework
but sql return with error
CREATE FULLTEXT STOPLIST myStoplist;
"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'STOPLIST'."
I just notice in "Stopwords and Stoplists", it did mention :
"Important CREATE FULLTEXT STOPLIST, ALTER FULLTEXT STOPLIST, and DROP FULLTEXT STOPLIST are supported only under compatibility level 100. Under compatibility levels 80 and 90, these statements are not supported. However, under all compatibility levels the system stoplist is automatically associated with new full-text indexes."
I change the database compatibility level from 90 to level 100 and execute the query again
CREATE FULLTEXT STOPLIST myStoplist;
Stoplist create successful
Reference :
Stopwords and Stoplists
http://msdn.microsoft.com/en-us/library/ms142551.aspx
Tuesday, December 21, 2010
SQL 2008 express : The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. [0x80070422]
Install SQL Express Edition with Advanced Service (64-bit)
After install successful, SQL Server start but $SQL Server Agent not start
Check from SQL Server Configuration Manager -> SQL Server Agent (MSSQLSERVER)
State -> Stopped
Right click ->Property->Service->Start Mode change from "Disable" to "Automatic" and press "Apply"
SQL Server give the below message :
"The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. [0x80070422]"
Google and found this from Microsoft Site.
SQL Server Features that Are Not Supported in SQL Server Express
One of the component is "SQL Server Agent and SQL Server Agent Service"
That is nothing wrong with installation but SQL Server Agent and SQL Server Agent Service by design is not supported in SQL Server Express 2008
After install successful, SQL Server start but $SQL Server Agent not start
Check from SQL Server Configuration Manager -> SQL Server Agent (MSSQLSERVER)
State -> Stopped
Right click ->Property->Service->Start Mode change from "Disable" to "Automatic" and press "Apply"
SQL Server give the below message :
"The service cannot be started, either because it is disabled or because it has no enabled devices associated with it. [0x80070422]"
Google and found this from Microsoft Site.
SQL Server Features that Are Not Supported in SQL Server Express
One of the component is "SQL Server Agent and SQL Server Agent Service"
That is nothing wrong with installation but SQL Server Agent and SQL Server Agent Service by design is not supported in SQL Server Express 2008
Monday, December 20, 2010
SQL 2008 Table data type
My developer ask me question about table data type.
Google and found this new features sql server 2008-user defined table type and table valued parameters and this Passing table valued parameters in SQL Server 2008
We have the same scenario but with additional step
When we execute the store procedure with pass in parameter, we want
1. Check whether the data already exists in table.
2. If exists, update the data with new value
3. If not exists, insert the data
In this store procedure, we have this :
CREATE procedure insertintocustomer(@Cust_ID int, @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)
Begin
update customers
set cust_surname=@Cust_Surname, Cust_Email=@Cust_Email
where cust_id=@Cust_id
END
else
BEGIN
insert into customers
values(
@Cust_ID, @Cust_Name,@Cust_Surname,@Cust_Email)
END
End
So we do this
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
Google and found this new features sql server 2008-user defined table type and table valued parameters and this Passing table valued parameters in SQL Server 2008
We have the same scenario but with additional step
When we execute the store procedure with pass in parameter, we want
1. Check whether the data already exists in table.
2. If exists, update the data with new value
3. If not exists, insert the data
In this store procedure, we have this :
CREATE procedure insertintocustomer(@Cust_ID int, @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)
Begin
update customers
set cust_surname=@Cust_Surname, Cust_Email=@Cust_Email
where cust_id=@Cust_id
END
else
BEGIN
insert into customers
values(
@Cust_ID, @Cust_Name,@Cust_Surname,@Cust_Email)
END
End
So we do this
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
Tuesday, December 14, 2010
Using Inner join or Function(Case)
One of the developer using function in query to return the result that he needed. The query and write using inner join without function
What is the different ?
Example :
In adventurework :
create table HumanResources.Gender
(id char(1),
Name varchar(20))
insert into HumanResources.Gender
values ('M','This is Male')
insert into HumanResources.Gender
values ('F','This is Female')
create FUNCTION dbo.GetGender (@what char(1))
RETURNS varchar(20)
AS
BEGIN
Declare @whatGender varchar(20)
SELECT @whatGender=Name from HumanResources.Gender where id=@what
RETURN @whatGender
End
Run Display Estimated Execution plan
together for the above 2 query
What Estimated Execution plan above show?
First query Query cost is 69% compare to second query Cost 31% (25%+6%)
Is that mean second query better that first query ?
Let set statistics time on, click on
and actual run the query
set statistics time on
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
set statistics time off
Actual execution plan
From the actual exection plan
First query still have high relative cost compare to second query
From the statistics
First query CPU time = 0 ms, elapsed time = 83 ms.
compare to Second query CPU time = 15 ms, elapsed time = 91 ms.
Second query elapsed time more slower compare to first query and use more CPU resource.
Let turn on Profiler to see what is running when we execute the query
Open SQL Profiler and select only TSQL:SQL:Batch Complete and click run
In Microsoft SQL Management Studio and execute first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
Go back to SQL Server Profiler and check what is running
Only have query with SQL:BatchCompleted found in SQL Server Profiler with CPU 15, Read 19, Write 0, Duration 85
In Microsoft SQL Management Studio and execute second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
Go back to SQL Server Profiler and check what is running
Only have query with SQL:BatchCompleted found in SQL Server Profiler with CPU 31, Read 893, Write 0, Duration 85
It show First query use less resource and run more faster compare to Second query.
Go back to SQL Server Profiler, pause the trace
From File->Properties->Event Selection->Check on Show all events-> Go to TSQL Category and check on SP:StmtCompleted (SQL statement within a stored procedure has completed).
Press run to run SQL profiler again.
From Menu bar, click on "Clear Trace Window" to clear the early trace
In Microsoft SQL Management Studio and execute first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
Go back to SQL Profiler and check the result
That is only 1 line in the SQL Profiler, SQL:BatchCompleted with CPU 31, Read 893, Write 0, Duration 85
Let clear Trace Window in SQL Proiler to clear to trace result and go back to Microsoft SQL Management Studio and execute second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
Go back to SQL Profiler and check the result
SP:stmtCompleted with
- SELECT @whatGender=Name from HumanResources.Gender where id=@what
- RETURN @whatGender
Last result is SQL:BatchCompleted with query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
When we execute Second query, SQL need to get Name value from HumanResources.Gender and return the value to GetGender function.
If we have 291 result return, SQL will execute (291*2)+1 time in SQL Server compare to first query which only execute one
From the result above, we can conclude first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
is better than second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
What is the different ?
Example :
In adventurework :
create table HumanResources.Gender
(id char(1),
Name varchar(20))
insert into HumanResources.Gender
values ('M','This is Male')
insert into HumanResources.Gender
values ('F','This is Female')
create FUNCTION dbo.GetGender (@what char(1))
RETURNS varchar(20)
AS
BEGIN
Declare @whatGender varchar(20)
SELECT @whatGender=Name from HumanResources.Gender where id=@what
RETURN @whatGender
End
Run Display Estimated Execution plan
together for the above 2 query
What Estimated Execution plan above show?
First query Query cost is 69% compare to second query Cost 31% (25%+6%)
Is that mean second query better that first query ?
Let set statistics time on, click on
and actual run the queryset statistics time on
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
set statistics time off
Actual execution plan
From the actual exection plan
First query still have high relative cost compare to second query
From the statistics
First query CPU time = 0 ms, elapsed time = 83 ms.
compare to Second query CPU time = 15 ms, elapsed time = 91 ms.
Second query elapsed time more slower compare to first query and use more CPU resource.
Let turn on Profiler to see what is running when we execute the query
Open SQL Profiler and select only TSQL:SQL:Batch Complete and click run
In Microsoft SQL Management Studio and execute first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
Go back to SQL Server Profiler and check what is running
Only have query with SQL:BatchCompleted found in SQL Server Profiler with CPU 15, Read 19, Write 0, Duration 85
In Microsoft SQL Management Studio and execute second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
Go back to SQL Server Profiler and check what is running
Only have query with SQL:BatchCompleted found in SQL Server Profiler with CPU 31, Read 893, Write 0, Duration 85
It show First query use less resource and run more faster compare to Second query.
Go back to SQL Server Profiler, pause the trace
From File->Properties->Event Selection->Check on Show all events-> Go to TSQL Category and check on SP:StmtCompleted (SQL statement within a stored procedure has completed).
Press run to run SQL profiler again.
From Menu bar, click on "Clear Trace Window" to clear the early trace
In Microsoft SQL Management Studio and execute first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
Go back to SQL Profiler and check the result
That is only 1 line in the SQL Profiler, SQL:BatchCompleted with CPU 31, Read 893, Write 0, Duration 85
Let clear Trace Window in SQL Proiler to clear to trace result and go back to Microsoft SQL Management Studio and execute second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
Go back to SQL Profiler and check the result
SP:stmtCompleted with
- SELECT @whatGender=Name from HumanResources.Gender where id=@what
- RETURN @whatGender
Last result is SQL:BatchCompleted with query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
When we execute Second query, SQL need to get Name value from HumanResources.Gender and return the value to GetGender function.
If we have 291 result return, SQL will execute (291*2)+1 time in SQL Server compare to first query which only execute one
From the result above, we can conclude first query
SELECT Gender,*
FROM [HumanResources].[Employee] a
inner join [HumanResources].[Gender] b
on a.Gender=b.id
is better than second query
SELECT dbo.GetGender(Gender),Gender,a.*
FROM [HumanResources].[Employee] a
SQL Change column name and type
I want change existing table column name and data type
The faster way is drop and create
If table already have data inside and we don't wanna recreate table, we can do this :
Original table structure
create table HumanResources.Gender2
(id char(1),
lang varchar(20),
Name varchar(20))
Execute below query to change column name
sp_RENAME 'HumanResources.Gender2','lang', 'lang_id' , 'COLUMN'
Caution: Changing any part of an object name could break scripts and stored procedures.
Identify store procedure which reference to Gender2.lang table
Example :
create procedure GetGender2
@id int
as
select lang,name from Gender2 where
id =@id
To identify table, trigger, view reference to Gender2.lang table, execute this
sp_depends 'Gender2'-- Gender2 is the table name
In the current database, the specified object is referenced by the following:
name type
dbo.GetGender2 stored procedure
After change the column name and excute the above store procedure, SQL will give this error message:
Msg 207, Level 16, State 1, Procedure GetGender2, Line 4
Invalid column name 'lang'.
We should change all store procedure which reference to this table
In this case, we need change Store Procedure GetGender2, change lang to lang_id
alter procedure GetGender2
@id int
as
select lang_id,name from Gender2 where
id =@id
I execute the second query to change the data type
alter table HumanResources.Gender2
ALTER COLUMN lang_id int
Data type change successful
If original table already have data and data as below:
insert into Gender2
values ('English','This is Male')
insert into Gender2
values ('English','This is Female')
When we run
alter table HumanResources.Gender2
ALTER COLUMN lang_id int
SQL will give error :
- Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'English' to data type int.
The statement has been terminated.
We fail to change the data type because original data is character, we can't change the data type to integer.
Reference :
SQL SERVER – How to Rename a Column Name or
Table Name
Listing SQL Server Object Dependencies
The faster way is drop and create
If table already have data inside and we don't wanna recreate table, we can do this :
Original table structure
create table HumanResources.Gender2
(id char(1),
lang varchar(20),
Name varchar(20))
Execute below query to change column name
sp_RENAME 'HumanResources.Gender2','lang', 'lang_id' , 'COLUMN'
Caution: Changing any part of an object name could break scripts and stored procedures.
Identify store procedure which reference to Gender2.lang table
Example :
create procedure GetGender2
@id int
as
select lang,name from Gender2 where
id =@id
To identify table, trigger, view reference to Gender2.lang table, execute this
sp_depends 'Gender2'-- Gender2 is the table name
In the current database, the specified object is referenced by the following:
name type
dbo.GetGender2 stored procedure
After change the column name and excute the above store procedure, SQL will give this error message:
Msg 207, Level 16, State 1, Procedure GetGender2, Line 4
Invalid column name 'lang'.
We should change all store procedure which reference to this table
In this case, we need change Store Procedure GetGender2, change lang to lang_id
alter procedure GetGender2
@id int
as
select lang_id,name from Gender2 where
id =@id
I execute the second query to change the data type
alter table HumanResources.Gender2
ALTER COLUMN lang_id int
Data type change successful
If original table already have data and data as below:
insert into Gender2
values ('English','This is Male')
insert into Gender2
values ('English','This is Female')
When we run
alter table HumanResources.Gender2
ALTER COLUMN lang_id int
SQL will give error :
- Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'English' to data type int.
The statement has been terminated.
We fail to change the data type because original data is character, we can't change the data type to integer.
Reference :
SQL SERVER – How to Rename a Column Name or
Table Name
Listing SQL Server Object Dependencies
SQL 2008 Free Learning Resource Clinic 10259: SQL Server 2008: Database Infrastructure and Scalability
Read Kimberly L. Tripp Blog, and found this
"When SQL Server 2008 was in beta, Paul and I worked on a project to create content that would "jumpstart" folks in learning and working with SQL Server 2008. We delivered these "new features" courses at various locations and finally, we recorded the content. All of this content is now available FOR FREE from Microsoft through their eLearning outlet"
Clinic 10259: SQL Server 2008: Database Infrastructure and Scalability.
You need login into Microsoft Learning to start the Clinic. The good news is this is free and valid for one year!
"When SQL Server 2008 was in beta, Paul and I worked on a project to create content that would "jumpstart" folks in learning and working with SQL Server 2008. We delivered these "new features" courses at various locations and finally, we recorded the content. All of this content is now available FOR FREE from Microsoft through their eLearning outlet"
Clinic 10259: SQL Server 2008: Database Infrastructure and Scalability.
You need login into Microsoft Learning to start the Clinic. The good news is this is free and valid for one year!
Thursday, December 09, 2010
You must use the Role Management Tool to install or configure Microsoft.NET Framework 3.5SP1
I want install SQL Express Version in Windows 2008 R2
When I Install SQL Server Express 2008, installation fail and give this error message
"need install Microsoft .NET Framework 3.5 SP1 Setup"
I Download and install Microsoft .NET Framework 3.5 SP1 Setup but fail
"You must use the Role Management Tool to install or configure Microsoft.NET Framework 3.5SP1."
From Server-> Start->Administrator tools->Server management ->Features -> Add Features->.NET Framework 3.5.1 Features
After installation, SQL intallation can contineous.
When I Install SQL Server Express 2008, installation fail and give this error message
"need install Microsoft .NET Framework 3.5 SP1 Setup"
I Download and install Microsoft .NET Framework 3.5 SP1 Setup but fail
"You must use the Role Management Tool to install or configure Microsoft.NET Framework 3.5SP1."
From Server-> Start->Administrator tools->Server management ->Features -> Add Features->.NET Framework 3.5.1 Features
After installation, SQL intallation can contineous.
Friday, November 26, 2010
SQL 2005 Self-Paced Lab from PSS
If you still using SQL 2005, or want to learn the below topic, go to download PSS Service Center Lab 2005
Blocking and Deadlocking
In these labs you will use SQLDiag, SQL Profiler, and various DMVs to solve blocking and deadlocking problems.
Performance
In these labs you will use SQLDiag, SQL Profiler, showplan, and ReadTrace to solve various query performance problems.
Debugging
In these labs you will learn more about debugging tools used by the PSS team such as SQLDumper and Windows Debugging Tools to solve problems like a crash of SQL Server.
Server and Data Recovery
In these labs you will learn to how to solve various server and database offline problems.
Blocking and Deadlocking
In these labs you will use SQLDiag, SQL Profiler, and various DMVs to solve blocking and deadlocking problems.
Performance
In these labs you will use SQLDiag, SQL Profiler, showplan, and ReadTrace to solve various query performance problems.
Debugging
In these labs you will learn more about debugging tools used by the PSS team such as SQLDumper and Windows Debugging Tools to solve problems like a crash of SQL Server.
Server and Data Recovery
In these labs you will learn to how to solve various server and database offline problems.
Subscribe to:
Posts (Atom)