tag:blogger.com,1999:blog-22904831460962911312024-03-14T01:41:36.770-07:00Microsoft SQL ServerAnonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.comBlogger36125tag:blogger.com,1999:blog-2290483146096291131.post-16232321966929885962015-07-20T02:35:00.003-07:002015-07-20T02:35:45.960-07:00SQL Server Security Bulletin MS15-058 Released<div dir="ltr" style="text-align: left;" trbidi="on">
<div>
A SQL Server security bulletin (<a href="https://technet.microsoft.com/en-us/library/security/MS15-058" target="_blank">MS15-058</a>) was released yesterday (14-Jul-2015).</div>
<div>
“<em>This update resolves vulnerabilities in Microsoft SQL Server
that could allow remote code execution if an authenticated attacker runs
a specially crafted query that is designed to execute a virtual
function from a wrong address. This leads to a function call to
uninitialized memory.</em>“</div>
<div>
It applies to:</div>
<ul>
<li>SQL Server 2008</li>
<li>SQL Server 2008 R2</li>
<li>SQL Server 2012</li>
<li>SQL Server 2014</li>
</ul>
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-21393205594992693082015-07-14T23:48:00.001-07:002015-07-14T23:48:36.972-07:00Standardizing DBMAIL Profile & Account Setup for Notification<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="content-text" itemprop="articleBody">
Copy the script to SSMS
<br />
<span style="font-size: 12.8000001907349px;">Change the parameter values mentioned in the procedure argument lists</span>
<br />
Run the procedure.
<br />
<span style="font-size: 12.8000001907349px;">Once its successfully setup, you'll receive an alert.</span>
<br />
</div>
<div class="content-text" itemprop="articleBody">
USE [db_maint]<br />GO<br />SET ANSI_NULLS ON<br />GO<br />SET QUOTED_IDENTIFIER ON<br />GO<br />---------------------------------------------------------------------------------------------------------------------<br />IF EXISTS ( SELECT *<br /> FROM sys.objects<br /> WHERE object_id = OBJECT_ID(N'usp_dbMAilAccount')<br /> AND type = N'P')<br /><br />DROP PROCEDURE [usp_dbMAilAccount];<br />GO<br />---------------------------------------------------------------------------------------------------------------------<br />Create Proc usp_dbMAilAccount (@profile_name nvarchar(400) = 'DBMail_Global_Profile', --newly added.<br /> @account_name nvarchar(400) = 'DBMail_Account', --newly added.<br /> -- @email_address nvarchar(400) = 'MSSQL_DB_Mail@mail.com',<br /> @display_name nvarchar(400) = 'DBMail',<br /> @description nvarchar(100) = 'Database Mail Account',<br /> @mailserver_name nvarchar(400) = 'mail_server_name',<br /> @mailserver_type nvarchar(100) = 'SMTP',<br /> @replyto_address nvarchar(100) = NULL,<br /> @port INT = 25,<br /> @recipients nvarchar(500) = 'youremail@mail.com',<br /> --@recipients nvarchar(500) = 'xyz@mail.com',<br /> @Option nvarchar(5) = 'C')<br /><br />as<br />--------------------------------------------------------------------------------------------------------------------<br />/* <br /> Purpose: The purpose of this procedure is to create / setup/ delete/ update database mail account.<br /> <br /> First it will cleanup all existing accounts and followed by setting up the fresh accounts.<br />----------------------------------------------------------------------------------------------------------------------<br /> **RUN : <br /> -- To Create the DB mail account<br /> EXEC usp_dbMAilAccount @recipients = 'xyz@mail.com'<br /><br /> -- To Delete the DB mail account<br /> EXEC usp_dbMAilAccount @Option ='C'<br />----------------------------------------------------------------------------------------------------------<br /><br />Important Procedures: 1. sysmail_add_profileaccount_sp<br /> 2. sysmail_help_principalprofile_sp<br /> 3. sp_send_dbmail<br />----------------------------------------------------------------------------------------------------------<br />EXEC msdb.dbo.sysmail_help_configure_sp;<br />EXEC msdb.dbo.sysmail_help_account_sp;<br />EXEC msdb.dbo.sysmail_help_profile_sp;<br />EXEC msdb.dbo.sysmail_help_principalprofile_sp;<br />---------------------------------------------------------------------------------------------------------------------<br /><br />Key Procedures to Note & Remember:<br />1. sysmail_add_account_sp<br />2. sysmail_add_profile_sp<br />3. sysmail_add_profileaccount_sp<br />4. sysmail_add_principalprofile_sp<br />5. sp_send_dbmail<br />6. sysmail_delete_profileaccount_sp<br />7. sysmail_delete_profile_sp<br />8. sysmail_delete_account_sp<br />------------------------------------------------------------------------<br />--Profiles<br />SELECT * FROM msdb.dbo.sysmail_profile<br />------------------------------------------------------------------------------<br />--Accounts<br />SELECT * FROM msdb.dbo.sysmail_account<br />------------------------------------------------------------------------------<br />--Profile Accounts<br />select * from msdb.dbo.sysmail_profileaccount<br />------------------------------------------------------------------------------<br />--Principal Profile<br />select * from msdb.dbo.sysmail_principalprofile<br />------------------------------------------------------------------------------<br />--Mail Server<br />SELECT * FROM msdb.dbo.sysmail_server<br />SELECT * FROM msdb.dbo.sysmail_servertype<br />SELECT * FROM msdb.dbo.sysmail_configuration<br />------------------------------------------------------------------------------<br />--Email Sent Status<br />SELECT * FROM msdb.dbo.sysmail_allitems<br />SELECT * FROM msdb.dbo.sysmail_sentitems<br />SELECT * FROM msdb.dbo.sysmail_unsentitems<br />SELECT * FROM msdb.dbo.sysmail_faileditems<br />--------------------------------------------------------------------------------------------------------------------------------<br />--Email Status<br />SELECT SUBSTRING(fail.subject,1,25) AS 'Subject', fail.mailitem_id, LOG.descriptionFROM msdb.dbo.sysmail_event_log LOG<br />join msdb.dbo.sysmail_faileditems fail ON fail.mailitem_id = LOG.mailitem_id WHERE event_type = 'error'<br />--------------------------------------------------------------------------------------------------------------------------------<br /> --Mail Queues<br />EXEC msdb.dbo.sysmail_help_queue_sp<br />--------------------------------------------------------------------------------------------------------------------------------<br />--DB Mail Status<br />EXEC msdb.dbo.sysmail_help_status_sp<br />-------------------------------------------------------------------------------------------------------------------------------------- <br />Key Queries:<br /><br />select * from msdb.dbo.sysmail_profileaccount<br />select * from msdb.dbo.sysmail_profile<br />select * from msdb.dbo.sysmail_account<br /><br />--SELECT * FROM msdb.dbo.sysmail_profileaccount pa<br />-- JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id<br />-- JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id<br /><br />----------------------------------------------------------------------------------------------------------------------------------------------<br />-- Delete Unsent Emails.<br /> SELECT * FROM msdb.dbo.sysmail_event_log;<br />-- To get number of unsent emails<br />select count(*) from msdb.dbo.sysmail_unsentitems;<br />-- remove all the unsent emails<br />delete from msdb.dbo.sysmail_unsentitems;<br /><br />----------------------------------------------------------------------------------------------------------------------------------------------<br />-- Delete failed alerts older than 2 days.<br />DECLARE @Date datetime<br />SELECT @Date = DateAdd(dd,-2,Getdate())<br />SELECT @Date<br />EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @Date , @sent_status = 'failed' <br />------------------------------------------------------------------------------------------------------------------------------------------<br />-- Here is how we will delete all events in the log older than two days<br />DECLARE @Date datetime<br />SELECT @Date = DateAdd(dd,-2,Getdate())<br /><br />EXECUTE msdb.dbo.sysmail_delete_log_sp @logged_before = @Date <br />----------------------------------------------------------------------------------------------------------------------------------------------<br />*/<br />BEGIN<br />BEGIN TRY<br />SET NOCOUNT ON<br /><br />--Is db mail enabled for use?<br />IF NOT EXISTS (SELECT value_in_use<br /> FROM sys.configurations<br /> WHERE name = 'Database Mail XPs'<br /> AND value_in_use = 1)<br />BEGIN<br /> EXEC ('sp_configure ''show advanced options'', 1')<br /> RECONFIGURE;<br /> EXEC ('sp_configure ''Database Mail XPs'', 1')<br /> RECONFIGURE;<br /> EXEC ('sp_configure ''show advanced options'', 0')<br /> RECONFIGURE;<br />END<br />----------------------------------------------------------------------------------------------------------------------------<br />-- Generate logic to build account name.<br />declare @servername sysname = (select @@servername);<br />--declare @account_name nvarchar(1200) = 'DBMail' ;<br />-- declare @profile_name nvarchar(1200);<br />declare @email_address nvarchar(1200)= 'DBMAIL';<br /><br />--DBMail_LVDB0001_SPE<br />--select @backup_path <br /> DECLARE @len INT, <br /> @i INT, <br /> @Left nVARCHAR(4000), <br /> @right nVARCHAR(4000),<br /> @full_string nVARCHAR(4000);<br /><br /> --SET @SERVERNAME = (SELECT @@servername); <br /> --select @backup_path AS [TLOG Backup PATH] <br /> --PRINT @backup_path <br /> IF @SERVERNAME LIKE '%\%' <br /> BEGIN <br /> SET @Left= (SELECT LEFT(@SERVERNAME, Charindex('\', @SERVERNAME) <br /> - 1)) <br /> --SELECT @Left AS [DEFAULT SERVER NAME] <br /> SET @len = (SELECT Len(@SERVERNAME)) <br /> --SELECT @LEN <br /> SET @right = (SELECT Substring(@SERVERNAME, <br /> Charindex ('\', @SERVERNAME) + <br /> 1 <br /> , @len)) <br /> --SELECT @right AS [SQL INSTANCE NAME] <br /> SET @full_string = @Left + '_'+ @right;<br /> --PRint @full_string<br /> END <br /> ELSE <br /> BEGIN <br /> SELECT @left = @SERVERNAME;<br /> SET @full_string = @left;<br /> --PRint @full_string<br /><br /> END <br /><br />-- Build the final account name.<br />SET @email_address = @email_address + '_'+ @full_string+'@mail.com';<br />--Print @email_address<br /><br />SET @display_name = @display_name + '_' + @full_string;<br />--Print @display_name<br /><br />---------------------------------------------------------------------------------------------------------------------<br />--declare @ProfileID INT;<br /><br />----Does the profile exist already?<br />--SELECT @ProfileID = p.profile_id<br />----SELECT p.name, *<br />--FROM msdb.dbo.sysmail_profileaccount pa<br />--JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id<br />--full outer JOIN msdb.dbo.sysmail_account a ON a.account_id=pa.account_id<br />--WHERE p.name = @profile_name; <br />---------------------------------------------------------------------------------------------------------------------<br />--SELECT * FROM msdb.dbo.sysmail_profileaccount pa<br />-- JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id<br />-- JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id<br /><br />----------------------------------------------------------------------------------------------------------------------<br />-- Clean up the unnecesassary db accounts and profiles. We need one global profile to send email alerts. [Mandatory Step]<br /><br />--IF EXISTS(<br />--SELECT * FROM msdb.dbo.sysmail_profileaccount pa JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id<br />-- JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE p.name = @ProfileName AND<br />-- a.name = @AccountName)<br />--BEGIN<br />-- PRINT 'Deleting Profile Account'<br />-- EXECUTE sysmail_delete_profileaccount_sp @profile_name = @ProfileName, @account_name = @AccountName<br />--END<br />-- IF EXISTS(<br />--SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @ProfileName)<br />--BEGIN<br />-- PRINT 'Deleting Profile.' EXECUTE sysmail_delete_profile_sp @profile_name = @ProfileName<br />--END<br />-- IF EXISTS(<br />--SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @AccountName)<br />--BEGIN<br />-- PRINT 'Deleting Account.' EXECUTE sysmail_delete_account_sp @account_name = @AccountName<br />--END<br /><br />---------------------------------------------------------------------------------------------------------------------------------------<br />select * from msdb.dbo.sysmail_profile <br />select * from msdb..sysmail_profileaccount<br />select * from msdb.dbo.sysmail_account <br />---------------------------------------------------------------------------------------------------------------------------------------<br /><br />create table #temp2 (profile_id INT, account_id INT, Profile_name nvarchar(1000), account_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))<br /><br />INSERT INTO #temp2<br />SELECT p.profile_id, a.account_id, p.name,a.name, a.display_name,a.last_mod_datetime,a.last_mod_user <br /> FROM msdb.dbo.sysmail_profileaccount pa<br /> full outer JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id<br /> full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id<br /><br />--select * from #temp2<br /><br />declare @profile_id3 INT, @Profile_name3 nvarchar(1000),@account_name3 nvarchar(1000);<br /><br />select @profile_id3 = profile_id from #temp2;<br /><br />select @Profile_name3 = Profile_name from #temp2;<br /><br />select @account_name3 = account_name from #temp2;<br /><br />IF ((@profile_id3 IS NULL) and (@Profile_name3 IS NULL)) <br />BEGIN<br /> <br /> DELETE FROM msdb.dbo.sysmail_account<br /> WHERE name = @account_name3<br /><br /> -- PRINT 'Deleting Account, when there''s NO Profile Available (i.e. PRofile_ID and Profile_NAme Both NULL) := ' + @account_name3;<br /> --EXECUTE msdb..sysmail_delete_account_sp @account_name = @account_name3; <br /><br />--declare @cmd2 nvarchar(4000)<br />--if not exists ( select d.is_encrypted, d.name, k.* from sys.dm_database_encryption_keys k inner join sys.databases d on d.database_id = k.database_id<br />-- where d.name = @db_nm and is_encrypted = 1) <br />--begin<br />-- select @cmd2 = 'USE ' + quotename(@db_nm) + ';' + CHAR(13)+<br />-- 'CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE ' + quotename(@cert_nm) + ';'<br /> <br />-- Print @cmd2<br />-- EXEC sp_executesql @cmd2;<br />--END<br />--ELSE<br />--BEGIN<br />-- PRINT 'DATABASE ENCRYPTION KEY Already Exists Against the Database:= ' + @db_nm<br />--END<br /><br />END<br />--select * from #temp2<br /><br />drop table #temp2<br />------------------------------------------------------------------------------------------------------------------------------------------<br />create table #temp (profile_id INT, account_id INT, Profile_name nvarchar(1000), account_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))<br /><br />INSERT INTO #temp<br />SELECT p.profile_id, a.account_id, p.name,a.name, a.display_name,a.last_mod_datetime,a.last_mod_user <br /> FROM msdb.dbo.sysmail_profileaccount pa<br /> full outer JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id<br /> full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id<br />--SELECT p.profile_id, a.account_id, p.name as 'Profile_name' ,a.name as 'account_name', a.display_name,a.last_mod_datetime,a.last_mod_user <br />-- FROM msdb.dbo.sysmail_profileaccount pa<br />-- inner JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id<br />-- full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id<br /> --where p.profile_id IS NOT NULL and a.account_id IS NOT NULL;<br /><br />--select * from #temp<br /><br />---------------------------------------------------------------------------------------------------------------------------------------<br />declare @profile_id1 INT, @account_id1 INT, @Profile_name1 nvarchar(1000), @account_name1 nvarchar(1000);<br /><br />DECLARE Cur1 CURSOR FOR<br />SELECT profile_id, account_id , Profile_name , account_name FROM #temp<br /><br />OPEN Cur1;<br />FETCH NEXT FROM Cur1 INTO @profile_id1 , @account_id1, @Profile_name1, @account_name1;<br /><br />WHILE @@FETCH_STATUS = 0<br /> BEGIN<br /> <br /> --print @profile_id1<br /> --print @account_id1<br />---------------------------------------------------------------------------------------------------------------------------------------<br />--IF EXISTS(<br />--SELECT * FROM msdb.dbo.sysmail_profileaccount pa JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id<br />-- JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE p.name = @ProfileName AND<br />-- a.name = @AccountName)<br />--BEGIN<br />-- PRINT 'Deleting Profile Account'<br />-- EXECUTE sysmail_delete_profileaccount_sp @profile_name = @ProfileName, @account_name = @AccountName<br />--END<br />-- IF EXISTS(<br />--SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @ProfileName)<br />--BEGIN<br />-- PRINT 'Deleting Profile.' EXECUTE sysmail_delete_profile_sp @profile_name = @ProfileName<br />--END<br />-- IF EXISTS(<br />--SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @AccountName)<br />--BEGIN<br />-- PRINT 'Deleting Account.' EXECUTE sysmail_delete_account_sp @account_name = @AccountName<br />--END<br />---------------------------------------------------------------------------------------------------------------------------------------<br /> --Delete from msdb.dbo.sysmail_profileaccount<br /> --where profile_id = @profile_id1 and account_id = @account_id1;<br /><br /> IF EXISTS(<br /> SELECT * FROM msdb.dbo.sysmail_profileaccount pa JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id<br /> JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE p.name = @Profile_name1 AND<br /> a.name = @account_name1)<br /> BEGIN<br /><br /> PRINT 'Deleting Profile Account:= ' + @Profile_name1;<br /> EXECUTE msdb..sysmail_delete_profileaccount_sp @profile_name = @Profile_name1, @account_name = @account_name1;<br /><br /> END<br /> <br /> --DELETE FROM msdb.dbo.sysmail_profile <br /> --where profile_id = @profile_id<br /><br /> IF EXISTS(<br /> SELECT * FROM msdb.dbo.sysmail_profile p WHERE p.name = @Profile_name1)<br /> BEGIN<br /><br /> PRINT 'Deleting Profile:= ' + @Profile_name1 <br /> EXECUTE msdb..sysmail_delete_profile_sp @profile_name = @Profile_name1;<br /> END<br /><br /> --DELETE FROM msdb.dbo.sysmail_account<br /> --WHERE account_id = @account_id1;<br /><br /> IF EXISTS(<br /> SELECT * FROM msdb.dbo.sysmail_account a WHERE a.name = @account_name1)<br /> BEGIN<br /><br /> PRINT 'Deleting Account:= ' + @account_name1 <br /> EXECUTE msdb..sysmail_delete_account_sp @account_name = @account_name1;<br /> END<br /><br />FETCH NEXT FROM Cur1 INTO @profile_id1 , @account_id1, @Profile_name1, @account_name1;<br /> END;<br /><br />CLOSE Cur1;<br />DEALLOCATE Cur1;<br /><br />drop table #temp<br />---------------------------------------------------------------------------------------------------------------------<br />create table #temp1 (profile_id INT, account_id INT, Profile_name nvarchar(1000), account_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))<br /><br />INSERT INTO #temp1<br />SELECT p.profile_id, a.account_id, p.name,a.name, a.display_name,a.last_mod_datetime,a.last_mod_user <br /> FROM msdb.dbo.sysmail_profileaccount pa<br /> full outer JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id<br /> full outer JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id<br /><br />--select * from #temp1<br /><br />/*<br />declare @profile_id2 INT, @Profile_name2 nvarchar(1000);<br /><br />select @profile_id2 = profile_id from #temp1;<br /><br />select @Profile_name2 = Profile_name from #temp1;<br /><br />IF (@profile_id2 IS NULL) and (@Profile_name2 IS NULL)<br />BEGIN<br /> <br /> PRINT 'Deleting Account, when there''s NO Profile Available (i.e. PRofile_ID and Profile_NAme Both NULL) := ' + @account_name1 <br /> EXECUTE msdb..sysmail_delete_account_sp @account_name = @account_name1; <br />END<br /><br />*/<br /><br />drop table #temp1<br />-----------------------------------------------------------------------------------------------------------------------------<br />IF @Option = 'C'<br />BEGIN<br /><br />IF exists (select * from msdb.dbo.sysmail_account where name = @account_name)<br />BEGIN<br />PRint 'Database Mail Account ' + quotename (@account_name) + ' is already setup in the Server:= ' + quotename(@servername);<br />Print 'NOT Allowed to Create DB Mail Account Multiple Time.'<br /><br />--select * from msdb.dbo.sysmail_account;<br /><br />--select * from msdb.dbo.sysmail_account where name = @account_name;<br /><br />END<br /><br />ELSE IF NOT exists (select * from msdb.dbo.sysmail_account where name = @account_name)<br />BEGIN<br />PRINT 'Creating Account: ' + @account_name;<br /><br />--select * from msdb.dbo.sysmail_account where name = @account_name<br />-----------------------------------------------------------------------------------------------------------------------------------------<br />-- To setup database mail.<br /><br />-- Steps1: EXECUTE msdb.dbo.sysmail_add_profile_sp<br />-- step 2: EXECUTE msdb.dbo.sysmail_update_account_sp<br />-- Step 3: EXECUTE msdb.dbo.sysmail_add_principalprofile_sp<br />-- Step 4: EXECUTE msdb.dbo.sysmail_add_account_sp<br />-- Step 5: EXECUTE msdb.dbo.sysmail_add_profileaccount_sp<br />-----------------------------------------------------------------------------------------------<br /><br />--EXECUTE msdb.dbo.sysmail_add_profile_sp<br />-- @profile_name = @profile_name,<br />-- @description = @description;<br /><br />-- <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><br />-- STEP 1 Create Email Account in SQL Server:<br />-----------------------------------------------------------------------------------------------------------------<br />-- Setup mail account<br />declare @account_id int<br />execute msdb.dbo.sysmail_add_account_sp<br />@account_name= @account_name,<br />@email_address= @email_address, <br />@display_name= @display_name,<br />@description = @description,<br />@mailserver_name= @mailserver_name,<br />@mailserver_type = @mailserver_type,<br />@port = @port,<br />@username=null,<br />@password=null,<br />@enable_ssl=0,<br />--@username=null,<br />--@password=null,<br />@use_default_credentials=1,<br />@account_id=@account_id output;<br />---------------------------------------------------------------------------------------------------------------------<br />--select @account_id<br />--select * from msdb.dbo.sysmail_account<br /><br />------------------------------------------------------------------------------<br />-- to update the above details to add user_name, pswd = NULL, use the updte script.<br />--declare @account_id int<br />execute msdb.dbo.sysmail_update_account_sp<br />@account_id= @account_id, -- Pass the correct account ID.<br />@account_name=@account_name,<br />@email_address=@email_address,<br />@display_name=@display_name,<br />@description=@description,<br />@replyto_address= @replyto_address,<br />@mailserver_name=@mailserver_name,<br />@mailserver_type=@mailserver_type,<br />@port=@port,<br />@username=null,<br />@password=null,<br />@use_default_credentials=1,<br />@enable_ssl=0;<br />------------------------------------------------------------------------------<br />-- STEP 2 Creat Email Profile in SQL Server:<br />-- CREATE PROFILE<br /><br />SET @description = 'Database Mail Profile';<br />EXECUTE msdb.dbo.sysmail_add_profile_sp<br /> @profile_name = @profile_name, <br /> @description = @description;<br />------------------------------------------------------------------------------<br />-- Set the New Profile as the Default<br />EXECUTE msdb.dbo.sysmail_add_principalprofile_sp<br /> @profile_name = @profile_name,<br /> @principal_name = 'public',<br /> @is_default = 1 ;<br />---------------------------------------------------------------------------------------------------------------------<br />-- STEP 3 Link email account to email profile:<br />-- LINK ACCOUNT TOPROFILE<br />EXECUTE msdb.dbo.sysmail_add_profileaccount_sp <br />@profile_name = @profile_name,<br />@account_name = @account_name,<br />@sequence_number = 1<br />---------------------------------------------------------------------------------------------------------------------<br />PRint 'Database Mail Account Has been Setup ' + quotename (@account_name) + ' is already setup in the Server:= ' + quotename(@servername);<br />---------------------------------------------------------------------------------------------------------------------------------------<br /><br />--Create primary profile; we always want a profile for DBOPS team.<br />--IF @ProfileID IS NULL<br />--BEGIN<br />-- PRINT 'Creating profile: ' + @account_name;<br />-- EXEC msdb.dbo.sysmail_add_profile_sp<br />-- @profile_name = @account_name,<br />-- @description = @description,<br />-- @profile_id = @ProfileID output<br /><br />--Print @ProfileID<br />---------------------------------------------------------------------------------------------------<br /><br /> --IF (@profile_id IS NOT NULL) -- use id<br /> --BEGIN<br /> --SELECT @profileid = profile_id FROM msdb.dbo.sysmail_profile WHERE profile_id=@profile_id<br /> --IF (@profileid IS NULL) -- id is invalid<br /> --BEGIN<br /> -- RAISERROR(14606, -1, -1, 'profile')<br /> -- RETURN(3)<br /> --END <br /><br />-- SET @ProfileName = (select name from msdb..sysmail_profile where profile_id = @cnt)<br /><br />--SET @Sub = 'Testing Email From ' + quotename(@Server) + ' from profile : ' + @ProfileName<br /><br />--declare @Profile_Name1 nvarchar(400);<br />/*<br />SELECT @profileid = profile_id FROM msdb.dbo.sysmail_profile WHERE name like '%DBMail_%';<br /><br />select @Profile_Name = Name FROM msdb.dbo.sysmail_profile WHERE name like '%DBMail_%';<br /><br /> IF (@Profile_Name1 is not null)<br /> BEGIN<br /><br /> Print @profileid;<br /> Print @Profile_Name<br /><br /> -- give everybody access to use this profile and make it the default<br /> EXEC msdb.dbo.sysmail_add_principalprofile_sp<br /> @profile_id = @profileid,<br /> @principal_name = 'public',<br /> @is_default = 1<br /><br />END<br />*/<br />---------------------------------------------------------------------------------------------------------------------------------------<br />END<br /><br />/*<br />ELSE IF @Option = 'D'<br />BEGIN<br />Print 'delete'<br />IF exists (select * from msdb.dbo.sysmail_account where name = @account_name)<br />BEGIN<br /><br />print 'db mail account ' + quotename (@account_name) + ' exists. Can be Deleted.'<br />-- Delete the mail account:=<br />--delete from msdb.dbo.sysmail_account<br />--where name = @account_name;<br /><br /> Print @profile_id<br /> Print @account_id1<br /> Print @account_name;<br /><br /> Delete from msdb.dbo.sysmail_profileaccount<br /> where profile_id = @profile_id and account_id = @account_id1;<br /><br /> --PRINT 'Deleting Profile.' EXECUTE msdb..sysmail_delete_profile_sp @profile_name = @Profile_name;<br /><br /> DELETE FROM msdb.dbo.sysmail_profile <br /> where profile_id = @profile_id<br /><br /> --PRINT 'Deleting Account.' EXECUTE msdb..sysmail_delete_account_sp @account_name = @account_name1;<br /><br /> DELETE FROM msdb.dbo.sysmail_account<br /> WHERE account_id = @account_name;<br /><br /><br />print quotename (@account_name) + ' is successfully Deleted From the Server:= ' + quotename(@servername); <br /><br />END<br /><br /><br />ELSE IF NOT exists (select * from msdb.dbo.sysmail_account where name = @account_name)<br />BEGIN<br /><br /><br />Print 'db mail account ' + quotename (@account_name) + ' deleted already From the Server:= ' + quotename(@servername);<br />Print 'Can''t be deleted again.'<br />END<br />END<br />*/<br />---------------------------------------------------------------------------------------------------------------------<br />--- Clean Up All Other DB Mail Accounts Other than Server DB Mail Account.<br /><br />select * from msdb.dbo.sysmail_profileaccount<br />select * from msdb.dbo.sysmail_profile<br />select * from msdb.dbo.sysmail_account<br />---------------------------------------------------------------------------------------------------------------------<br />SELECT * FROM msdb.dbo.sysmail_profileaccount pa<br /> JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id<br /> JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id<br />---------------------------------------------------------------------------------------------------------------------<br />/*<br />IF EXISTS(<br />SELECT * FROM msdb.dbo.sysmail_profileaccount pa<br /> JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id<br /> JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id<br />WHERE<br /> p.name != @account_name AND<br /> a.name != @account_name)<br />BEGIN<br /><br />--declare @count INT = 1, @tot INT;<br /><br />--SET @tot = (SELECT count(*) FROM msdb.dbo.sysmail_profileaccount pa<br />-- JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id<br />-- JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id<br />--WHERE<br />-- p.name != @account_name AND<br />-- a.name != @account_name)<br />------------------------------------------------------------------------------------------------------<br /><br />create table #temp (profile_id INT, account_id INT, Profile_name nvarchar(1000), display_name nvarchar(1000), last_modified_datetime nvarchar(1000), last_modified_user nvarchar(1000))<br /><br />INSERT INTO #temp<br />SELECT * FROM msdb.dbo.sysmail_profileaccount pa<br /> JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id<br /> JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id<br /><br /><br />select * from #temp<br /><br />--WHILE(@count <= @tot)<br /> --BEGIN<br /> PRINT 'Deleting Profile Account';<br /> <br />-- IF (@profileid IS NOT NULL AND @accountid IS NOT NULL) -- both parameters supplied for deletion<br /><br /> DELETE FROM msdb.dbo.sysmail_profileaccount<br /><br /> WHERE profile_id=@profileid AND account_id=@accountid<br /><br /> select * from msdb.dbo.sysmail_profileaccount;<br /><br /><br /> select * from msdb.dbo.sysmail_profile <br /><br /> --ELSE IF (@profileid IS NOT NULL) -- profile id is supplied<br /><br /> -- DELETE FROM msdb.dbo.sysmail_profileaccount<br /><br /> -- WHERE profile_id=@profileid<br /><br /><br /><br /> --ELSE IF (@accountid IS NOT NULL) -- account id is supplied<br /><br /> -- DELETE FROM msdb.dbo.sysmail_profileaccount<br /><br /> -- WHERE account_id=@accountid<br /><br /><br /><br /> --ELSE -- no parameters are supplied for deletion<br /><br /> --BEGIN<br /><br /> -- RAISERROR(14608, -1, -1, 'profile', 'account') <br /><br /> -- RETURN(3) <br /><br /> --END<br /><br /><br /><br /> --EXECUTE msdb..sysmail_delete_profileaccount_sp<br /> --@profile_name = @account_name,<br /> --@account_name = @account_name<br />--END<br />------------------------------------------------------------------------------------------------------<br />IF EXISTS(<br />SELECT * FROM msdb.dbo.sysmail_profile p<br />WHERE p.name = @account_name)<br />BEGIN<br /> PRINT 'Deleting Profile.'<br /> <br /> DELETE FROM msdb.dbo.sysmail_profile <br /> WHERE name ! = @account_name<br /><br /> --EXECUTE msdb..sysmail_delete_profile_sp<br /> --@profile_name = @account_name<br />END<br /><br />IF EXISTS(<br />SELECT * FROM msdb.dbo.sysmail_account a<br />WHERE a.name = @account_name)<br />BEGIN<br /> PRINT 'Deleting Account.'<br /><br /> DELETE FROM msdb.dbo.sysmail_account<br /><br /> WHERE name != @account_name<br /><br /><br /> --EXECUTE msdb..sysmail_delete_account_sp<br /> --@account_name = @account_name<br />END<br /><br />--END<br />END<br />*/<br />----------------------------------------------------------------------------------------------------------------------------------------------------------<br />-- Test whether the dbmail account has been setup correctly. It should have one entry, so recipient should receive only a single email alert from each server.<br /><br />DECLARE<br /> @Total int<br /> ,@cnt int<br /> ,@ProfileName nvarchar(1000)<br /> --,@AccountName nvarchar(4000)<br /> ,@Server varchar(500)<br /> ,@Sub nvarchar(4000)<br /> ,@body nvarchar(4000),<br /> @cnt_Accounts INT;<br /><br />SET @Total = (SELECT MAX(profile_id) FROM msdb..sysmail_profile)<br /> SET @cnt = 1<br /><br />--SET @cnt_Accounts = (select count(*) from msdb..sysmail_account)<br /><br />--DECLARE @Table TABLE (account_name varchar(400),account_id INT)<br /><br />--INSERT INTO @TABLE<br />--select name, account_id from msdb..sysmail_account<br />--Order by Account_id<br /><br />--select * from @TABLE<br /><br />DECLARE @tableHTML NVARCHAR(4000) ;<br />-------------------------------------------------------<br />--select * from msdb.dbo.sysmail_profileaccount<br />--select * from msdb.dbo.sysmail_profile<br />--select * from msdb.dbo.sysmail_account<br />------------------------------------------------------<br /><br />SET @tableHTML =<br /> N'<style type="text/css"><br />#box-table<br />{<br />font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;<br />font-size: 12px;<br />text-align: center;<br />border-collapse: collapse;<br />border-top: 7px solid #9baff1;<br />border-bottom: 7px solid #9baff1;<br />}<br />#box-table th<br />{<br />font-size: 13px;<br />font-weight: normal;<br />background: #b9c9fe;<br />border-right: 2px solid #9baff1;<br />border-left: 2px solid #9baff1;<br />border-bottom: 2px solid #9baff1;<br />color: #039;<br />}<br />#box-table td<br />{<br />border-right: 1px solid #aabcfe;<br />border-left: 1px solid #aabcfe;<br />border-bottom: 1px solid #aabcfe;<br />color: #669;<br />}<br />tr:nth-child(odd) { background-color:#eee; }<br />tr:nth-child(even) { background-color:#fff; } <br /></style>'<br />+<br /><br /><br /> N'<H1><body><font color = red> Global Mail Profile & Accounts Configured (Server : ' + @full_string + ')' + ' </font></H1>' + '<BR>' + '<BR>' + <br /> <br /> N'<table id="box-table" border="1">' +<br /> N'<tr><th>Profile_ID</th>'+<br /> N'<th>Profile_Name</th>'+<br /> N'<th>Profile_Description</th>'+<br /> N'<th>Last_Mod_Datetime</th>'+<br /> N'<th>Last_Mod_User</th></tr>' +<br /> CAST ( ( SELECT td = profile_id, '',<br /> td = name, '',<br /> td = [description], '',<br /> td = convert(varchar(19), [last_mod_datetime],121) , '',<br /> td = [last_mod_user], ''<br /> FROM msdb.dbo.sysmail_profile<br /> ORDER BY profile_id ASC<br /> FOR XML PATH('tr'), TYPE <br /> ) AS NVARCHAR(MAX) ) +<br /> N'</table>'<br /><br /> + <br /> '<BR>' + '<BR>' + '<BR>' +<br /> N'<table id="box-table" border="1">' +<br /> N'<tr><th>Account_ID</th>'+<br /> N'<th>Account_Name</th>'+<br /> N'<th>Account_Description</th>'+<br /> N'<th>Email_Address</th>'+<br /> N'<th>Display_Name</th>'+<br /> N'<th>Last_Mod_Datetime</th>' +<br /> N'<th>Last_Mod_User</th></tr>' +<br /> CAST ( ( SELECT td = account_id, '',<br /> td = name, '',<br /> td = [description], '',<br /> td = email_address, '',<br /> td = display_name, '' ,<br /> td = convert(varchar(19), [last_mod_datetime],121) , '',<br /> td = last_mod_user, ''<br /> FROM msdb.dbo.sysmail_account<br /> ORDER BY account_id ASC<br /> FOR XML PATH('tr'), TYPE <br /> ) AS NVARCHAR(MAX) ) +<br /> N'</table>';<br /><br />select @tableHTML;<br /><br /><br />------------------------------------------------------------------------------------------------------<br />SET @Server = @@SERVERNAME<br /><br />WHILE(@cnt <= @Total)<br /> BEGIN<br /> SET @ProfileName = (select name from msdb..sysmail_profile where profile_id = @cnt)<br /><br /> --SET @AccountName = (select * from msdb..sysmail_account where @account_id = @cnt)<br /><br />SET @Sub = 'Testing Email From ' + quotename(@Server) + ' from profile : ' + @ProfileName;<br /><br /> IF (@ProfileName is not null)<br /> BEGIN<br /><br />--Print '------------------------------'<br />--PRINT @ProfileName<br />--Print @AccountName<br />--PRINT @Sub<br />--Print '------------------------------'<br /><br />----------------------------------------------------------------------------------------------------------------------------------------<br />EXEC msdb.dbo.sp_send_dbmail<br /> @recipients = @recipients<br /> , @subject = @Sub<br /> , @body = @tableHTML<br /> , @profile_name = @ProfileName<br /> , @body_format = 'HTML' ;<br /> END<br /><br />SET @cnt = @cnt + 1<br />END<br /><br />END<br />------------------------------------------------------------------------------------------------------------------------------------<br />SET NOCOUNT OFF<br />END try <br /><br /> BEGIN catch <br /> DECLARE @ErrorNumber INT; <br /> DECLARE @ErrorSeverity INT; <br /> DECLARE @ErrorState INT; <br /> DECLARE @ErrorLine INT; <br /> DECLARE @ErrorProcedure NVARCHAR(4000); <br /> DECLARE @ErrorMessage NVARCHAR(4000); <br /><br /> SELECT @ErrorNumber = Error_number(), <br /> @ErrorSeverity = Error_severity(), <br /> @ErrorState = Error_state(), <br /> @ErrorLine = Error_line(), <br /> @ErrorProcedure = Error_procedure(); <br /><br /> SELECT @ErrorMessage = <br /> N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' <br /> + 'Message: ' + Error_message(); <br /><br /> SELECT @ErrorMessage AS [Error_Message]; <br /><br /> SELECT @ErrorProcedure AS [Error_Procedure]; <br /><br /> PRINT 'Error ' <br /> + CONVERT(VARCHAR(50), Error_number()) <br /> + ', Severity ' <br /> + CONVERT(VARCHAR(5), Error_severity()) <br /> + ', State ' <br /> + CONVERT(VARCHAR(5), Error_state()) <br /> + ', Procedure ' <br /> + Isnull(Error_procedure(), '-') + ', Line ' <br /> + CONVERT(VARCHAR(5), Error_line()); <br /><br /> PRINT Error_message(); <br /> END catch <br /><br /> SET nocount OFF <br />END<br />GO<br /><br />SET ANSI_NULLS OFF<br />GO<br />SET QUOTED_IDENTIFIER OFF<br />GO </div>
<div class="content-text" itemprop="articleBody">
</div>
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-81123679490689925352015-07-14T23:37:00.004-07:002015-07-14T23:37:55.161-07:00Disk Free Space Check<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="content-text" itemprop="articleBody">
Create the table "Capacity_DiskSpaceTracking". Script is available in the commented section.
<br />
Build the procedure
<br />
Run it:
<br />
<span style="font-size: 12.8000001907349px;"> </span><span class="Apple-tab-span" style="font-size: 12.8000001907349px; white-space: pre;"> </span><span style="font-size: 12.8000001907349px;"> exec [usp_Disk_Free_Space_Check]</span>
<br />
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span> @dbmail_profile= 'Operators',
</div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span> @dbmail_recipient = 'youremail@domain.com' ,
</div>
<div>
<span class="Apple-tab-span" style="white-space: pre;"> </span> @deleteEntry = 360; -- No Of Days!
</div>
</div>
<div class="content-text" itemprop="articleBody">
</div>
<div class="content-text" itemprop="articleBody">
USE [DB_Maint]<br />GO<br /><br />SET ANSI_NULLS ON<br />GO<br /><br />SET QUOTED_IDENTIFIER ON<br />GO<br /><br />IF OBJECT_ID(N'[dbo].[usp_Disk_Free_Space_Check]') IS NOT NULL<br />BEGIN<br /> DROP PROCEDURE [dbo].[usp_Disk_Free_Space_Check]<br />END<br />GO<br /><br />CREATE PROC [dbo].[usp_Disk_Free_Space_Check] (@dbmail_profile sysname = NULL, @dbmail_recipient sysname = NULL, @deleteEntry int = 360)<br /><br />AS<br />BEGIN<br /> SET NOCOUNT ON<br /> BEGIN TRY<br /><br /><br /> /* <br /> Desc: 1. Calcualates each drive's growth rate and report them as Alerts.<br /> 2. If free disk space <=30, it will throw alert.<br /> 3. If free disk space <=20, it will throw alert.<br /> 4. If free disk space <=10, it will throw WARNING!! alert.<br /> <br /> RUNNING Instructions :=<br /> exec [usp_Disk_Free_Space_Check]<br /> @dbmail_profile= 'Operators', <br /> @dbmail_recipient = 'youremail@domain.com' ,<br /> @deleteEntry = 360; -- No Of Days!<br /> */<br /> <br /> <br /> /* <br /> -----------------------------------------------------------<br /> Step 1: Create the table.<br /> <br /> USE [DB_Maint]<br /> GO<br /> <br /> SET ANSI_NULLS ON<br /> GO<br /> <br /> SET QUOTED_IDENTIFIER ON<br /> GO<br /> <br /> SET ANSI_PADDING ON<br /> GO<br /> <br /> CREATE TABLE [dbo].[Capacity_DiskSpaceTracking](<br /> [ID] [int] IDENTITY(1,1) NOT NULL,<br /> [Drive] varchar(10) NOT NULL,<br /> [ServerName] nvarchar(4000) NULL,<br /> [Free(MB)] varchar(1000) NULL,<br /> [Total(MB)] varchar(1000) NULL,<br /> [Free(%)] varchar(1000) NULL,<br /> [Date_Entered] datetime NULL,<br /> [login_sname] [sysname] NOT NULL DEFAULT (suser_sname())<br /> ) ON [PRIMARY]<br /> <br /> GO<br /> <br /> SET ANSI_PADDING OFF<br /> GO<br /> <br /> <br /> <br /> */<br /><br /> -- xp_fixeddrives<br /><br /><br /> DECLARE @hr int<br /> DECLARE @fso int<br /> DECLARE @drive char(1)<br /> DECLARE @odrive int<br /> DECLARE @TotalSize varchar(20)<br /> DECLARE @MB bigint;<br /><br /> SET @MB = 1048576 -- 1 GB<br /><br /> CREATE TABLE #drives (<br /> drive char(1) PRIMARY KEY,<br /> FreeSpace int NULL,<br /> TotalSize int NULL<br /> )<br /> INSERT #drives (drive, FreeSpace)<br /> EXEC master.dbo.xp_fixeddrives<br /> EXEC @hr = sp_OACreate 'Scripting.FileSystemObject',<br /> @fso OUT<br /> IF @hr <> 0<br /> EXEC sp_OAGetErrorInfo @fso<br /> DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR<br /> SELECT<br /> drive<br /> FROM #drives<br /> ORDER BY drive<br /> OPEN dcur<br /> FETCH NEXT FROM dcur INTO @drive<br /> WHILE @@FETCH_STATUS = 0<br /> BEGIN<br /> EXEC @hr = sp_OAMethod @fso,<br /> 'GetDrive',<br /> @odrive OUT,<br /> @drive<br /> IF @hr <> 0<br /> EXEC sp_OAGetErrorInfo @fso<br /> EXEC @hr = sp_OAGetProperty @odrive,<br /> 'TotalSize',<br /> @TotalSize OUT<br /> IF @hr <> 0<br /> EXEC sp_OAGetErrorInfo @odrive<br /><br /> UPDATE #drives<br /> SET TotalSize = @TotalSize / @MB<br /> WHERE drive = @drive<br /> FETCH NEXT FROM dcur INTO @drive<br /> END<br /><br /> CLOSE dcur<br /> DEALLOCATE dcur<br /><br /> EXEC @hr = sp_OADestroy @fso<br /> IF @hr <> 0<br /> EXEC sp_OAGetErrorInfo @fso<br /><br /> SELECT<br /> @@servername AS ServerName,<br /> drive,<br /> FreeSpace AS 'Free(MB)',<br /> TotalSize AS 'Total(MB)',<br /> CAST((FreeSpace / (TotalSize * 1.0)) * 100.0 AS int) AS 'Free(%)',<br /> GETDATE() AS Date_Entered INTO #result_set<br /> FROM #drives<br /><br /> -- Print Intermediate Results<br /> --select * from #result_set<br /><br /><br /> INSERT INTO [DB_Maint].[DBO].[Capacity_DiskSpaceTracking] ([ServerName],<br /> [drive],<br /> [Free(MB)],<br /> [Total(MB)],<br /> [Free(%)],<br /> [Date_Entered])<br /> SELECT<br /> ServerName,<br /> drive,<br /> [Free(MB)],<br /> [Total(MB)],<br /> [Free(%)],<br /> Date_Entered<br /> FROM #result_set<br /><br /><br /> --INSERT INTO Capacity_DiskSpaceTracking<br /> --(DriveLetter, Label, FreeSpaceMB,UsedSpaceMB,TotalSpaceMB,FreeSpacePercentage)<br /> --SELECT DriveLetter<br /> -- , Label<br /> -- , FreeSpace <br /> -- , (TotalSpace - FreeSpace) AS [UsedSpace MB]<br /> -- , TotalSpace <br /> -- , ((CONVERT(NUMERIC(9,2),FreeSpace) / CONVERT(NUMERIC(9,2),TotalSpace)) * 100) AS [Percentage Free]<br /> --FROM ##_DriveInfo<br /> --ORDER BY [DriveLetter] ASC <br /><br /><br /> -- Display Results.<br /><br /> SELECT<br /> *<br /> FROM [DB_Maint].[DBO].[Capacity_DiskSpaceTracking]<br /><br /><br /> DECLARE @servername nvarchar(100),<br /> @drive1 nvarchar(2),<br /> @freeMB int,<br /> @totalMB int,<br /> @free int,<br /> @date_entered nvarchar(50)<br /><br /> DECLARE db_crsr_T CURSOR FOR<br /> SELECT<br /> [ServerName],<br /> [drive],<br /> [Free(MB)],<br /> [Total(MB)],<br /> [Free(%)],<br /> [Date_Entered]<br /> FROM #result_set<br /><br /> OPEN db_crsr_T<br /> FETCH NEXT FROM db_crsr_T INTO @servername, @drive1, @FreeMB, @totalMB, @free, @date_entered<br /> WHILE @@fetch_status = 0<br /> BEGIN<br /><br /> IF @free < 30<br /> AND @free > 10<br /> BEGIN<br /> DECLARE @msg1 nvarchar(1000),<br /> @subject nvarchar(4000)<br /><br /> SELECT<br /> @subject = 'DISK SPACE ALERT !! ' + ' ' + SUBSTRING(@@servername, 1, 20) + ' Drive has < 30% Free Space '<br /> -- + ' ' + 'in Database: ' + @DBNAME;<br /><br /><br /> --declare @body1 nvarchar(max)<br /> SET @msg1 = N'<H1> <Font Color = "red"> DRIVE FREE SPACE CHECK: </font> </H1>' +<br /> N'<body><H3> <Font Color = "red"> Server: ' + @@servername + '</H3></font>' +<br /> '<BR>' + '<BR>';<br /><br /> SET @msg1 = @msg1 + 'Instance ' + RTRIM(@servername) + ' only has ' + CONVERT(nvarchar(9), @freeMB) + ' MB free on disk ' + CONVERT(char(1), @drive1) + ':\. The percentage free is ' + CONVERT(nvarchar(3), @free) + '. Drive ' + CONVERT(char(1), @drive1) + ':\ has a total size of ' + LTRIM(CONVERT(nvarchar(10), @totalMB)) + ' MB and ' + CONVERT(nvarchar(9), @freeMB) + ' MB free.'<br /> EXEC msdb.dbo.sp_send_dbmail @profile_name = @dbmail_profile, -- CHANGE THIS TO YOUR SERVERS MAIL PROFILE NAME...<br /> @recipients = @dbmail_recipient, --CHANGE THIS TO YOUR EMAIL ADDRESS...<br /> @body = @msg1,<br /> @subject = @subject,<br /> @body_format = 'HTML',<br /> @importance = 'HIGH';<br /> END<br /><br /> IF @free < 10<br /> BEGIN<br /> DECLARE @msg2 nvarchar(1000),<br /> @subject2 nvarchar(4000);<br /><br /> SELECT<br /> @subject2 = 'DISK SPACE ALERT !! ' + ' ' + SUBSTRING(@@servername, 1, 20) + ' Drive has < 10% Free Space ';<br /><br /> SET @msg2 = N'<H1> <Font Color = "red"> DRIVE FREE SPACE CHECK: WARNING !!! One Or more Drive has <10% free space !!! </font> </H1>' +<br /> N'<body><H3> <Font Color = "red"> Server: ' + @@servername + '</H3></font>' +<br /> '<BR>' + '<BR>';<br /><br /> SET @msg2 = @msg2 + '<Font Color = "red">WARNING!! </font> Instance ' + RTRIM(@servername) + ' only has ' + CONVERT(nvarchar(9), @freeMB) + ' MB free on disk ' + CONVERT(char(1), @drive1) + ':\. The percentage free is ' + CONVERT(nvarchar(3), @free) + '. Drive ' + CONVERT(char(1), @drive1) + ':\ has a total size of ' + LTRIM(CONVERT(nvarchar(10), @totalMB)) + ' MB and ' + CONVERT(nvarchar(9), @freeMB) + ' MB free.'<br /> EXEC msdb.dbo.sp_send_dbmail @profile_name = @dbmail_profile, -- CHANGE THIS TO YOUR SERVERS MAIL PROFILE NAME...<br /> @recipients = @dbmail_recipient, --CHANGE THIS TO YOUR EMAIL ADDRESS...<br /> @body = @msg2,<br /> @subject = @subject2,<br /> @body_format = 'HTML',<br /> @importance = 'HIGH';<br /> END<br /><br /> FETCH NEXT FROM db_crsr_T INTO @servername, @drive1, @FreeMB, @totalMB, @free, @date_entered<br /> END<br /><br /> CLOSE db_crsr_T<br /> DEALLOCATE db_crsr_T<br /><br /><br /> DROP TABLE #drives<br /> DROP TABLE #result_set<br /><br /> -- Cleanup from physical table.<br /><br /> DECLARE @NumRecords varchar(20)<br /><br /> PRINT @deleteEntry;<br /><br /> -- Retain records for 60 days Old. Keep @deleteEntry = 60<br /> DELETE FROM [DB_Maint].[DBO].[Capacity_DiskSpaceTracking]<br /> WHERE [Date_Entered] <= (GETDATE() - @deleteEntry)<br /><br /> SELECT<br /> @NumRecords = @@ROWCOUNT<br /><br /> SELECT<br /> @NumRecords<br /><br /> IF @NumRecords > 0<br /> BEGIN<br /> --DELETE FROM Capacity_DiskSpaceTracking WHERE DATEDIFF(DAY, TimeCollected, GETDATE()) > @deleteEntry<br /> DBCC CHECKIDENT ([DB_Maint.dbo.Capacity_DiskSpaceTracking], RESEED, 0);<br /> END<br /><br /> END TRY<br /> BEGIN CATCH<br /> DECLARE @ErrorNumber int;<br /> DECLARE @ErrorSeverity int;<br /> DECLARE @ErrorState int;<br /> DECLARE @ErrorLine int;<br /> DECLARE @ErrorProcedure nvarchar(4000);<br /> DECLARE @ErrorMessage nvarchar(4000);<br /><br /> SELECT<br /> @ErrorNumber = ERROR_NUMBER(),<br /> @ErrorSeverity = ERROR_SEVERITY(),<br /> @ErrorState = ERROR_STATE(),<br /> @ErrorLine = ERROR_LINE(),<br /> @ErrorProcedure = ERROR_PROCEDURE();<br /><br /> SELECT<br /> @ErrorMessage =<br /> N'Error %d, Level %d, State %d, Procedure %s, Line %d, '<br /> + 'Message: ' + ERROR_MESSAGE();<br /><br /> SELECT<br /> @ErrorMessage AS [Error_Message];<br /><br /> SELECT<br /> @ErrorProcedure AS [Error_Procedure];<br /><br /> PRINT 'Error '<br /> + CONVERT(varchar(50), ERROR_NUMBER())<br /> + ', Severity '<br /> + CONVERT(varchar(5), ERROR_SEVERITY())<br /> + ', State '<br /> + CONVERT(varchar(5), ERROR_STATE())<br /> + ', Procedure '<br /> + ISNULL(ERROR_PROCEDURE(), '-') + ', Line '<br /> + CONVERT(varchar(5), ERROR_LINE());<br /><br /> PRINT ERROR_MESSAGE();<br /> END CATCH<br /><br /> SET NOCOUNT OFF<br />END<br /><br />GO<br /><br />SET ANSI_NULLS OFF<br />GO<br /><br />SET QUOTED_IDENTIFIER OFF<br />GO </div>
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-35151194148336978952015-07-14T23:34:00.004-07:002015-07-14T23:34:55.395-07:00Script to estimate the Size of log backups<div dir="ltr" style="text-align: left;" trbidi="on">
We have an environment where we constantly need to estimate the size of
log backups(.Trn file size). I use this script to achieve it. <br />
<br />
create table #dbcclogspace([Database Name] nvarchar(200),[Log Size (MB)] float,[Log Space Used (%)] float,[Status] int)<br /> insert into #dbcclogspace([Database Name],[Log Size (MB)],[Log Space Used (%)],[Status])<br /> exec('DBCC sqlperf(logspace)')<br /> select [Database Name],([Log Space Used (%)]*[Log Size (MB)])/100 as [LogBackupSize in MB],(([Log Space Used (%)]*[Log Size (MB)])/100)/1024 as [LogBackupSize in GB] from #dbcclogspace<br /> drop table #dbcclogspace </div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-59814492393633845282015-07-14T23:34:00.000-07:002015-07-14T23:34:12.588-07:00Move database files to another Drive<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="content-text" itemprop="articleBody">
We had a couple of times the demand to transfer SQL related files from
C:\ drive to D:\ drive because system backups need them to be elsewhere
and the person who installed SQL Server (and the databases) was not
DBA.
<br />
Here is the way I recommand to run the script if you are not used to use it :
<br />
<ol>
<li>
Copy-Paste the code of the script in a connection to the SQL Server instance of your choice</li>
<li>
Modify the #CHANGEME to 1 so that the script does not change anything on the server</li>
<li>
Execute the script</li>
<li>
Copy the result in another window and run the commands</li>
</ol>
We ran it a couple of times and it seems to be working as expected.
<br />
Hope this helps.
<br />
</div>
<div class="content-text" itemprop="articleBody">
-- https://msdn.microsoft.com/en-us/library/ms345483.aspx<br />-- https://msdn.microsoft.com/fr-be/library/ms345408%28v=sql.105%29.aspx<br />-- OK for SQL Server 2008 R2 Express Edition<br />/* <br />!!! REPLACE #CHANGEME by 1 if just to test and by 0 if execution !!!<br />*/<br /><br />-- ---------------------------------------------------------------------------------------------------<br />PRINT 'Setting user databases offline'<br />-- ---------------------------------------------------------------------------------------------------<br /><br />DECLARE @JustTest BIT = #CHANGEME<br />DECLARE GetDDL CURSOR FOR <br /> select 'ALTER DATABASE ' + QUOTENAME(name) + ' SET OFFLINE' from master.sys.databases<br /> where name not in ('master','tempdb','model','msdb') -- system databases cannot be set offline !<br /><br />DECLARE @tsql NVARCHAR(MAX); <br />OPEN GetDDL ;<br /><br />fetch next from GetDDL into @tsql;<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN <br /> if(@JustTest = 1) <br /> BEGIN<br /> PRINT @tsql ;<br /> END <br /> ELSE <br /> BEGIN <br /> exec sp_executesql @tsql ;<br /> END <br /> fetch next from GetDDL into @tsql;<br />END <br /><br />CLOSE GetDDL<br />DEALLOCATE GetDDL<br />GO <br /><br /> <br />PRINT 'Move data and log files to D:\ drive'<br /><br />DECLARE @JustTest BIT = #CHANGEME<br />DECLARE GetDDL CURSOR FOR <br /> select <br /> 'ALTER DATABASE ' <br /> + QUOTENAME(DB_NAME(database_id)) + <br /> ' modify FILE ( NAME = ' + name + ', FILENAME=''' + REPLACE(physical_name,'C:\','D:\') + ''')' as DDLs<br /> from master.sys.master_files<br /> where DB_NAME(database_id) <> 'master' ; -- master needs a setting at service startup level<br /> <br />DECLARE @tsql NVARCHAR(MAX); <br />OPEN GetDDL ;<br /><br />fetch next from GetDDL into @tsql;<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN <br /> if(@JustTest = 1) <br /> BEGIN<br /> PRINT @tsql ;<br /> END <br /> ELSE <br /> BEGIN <br /> exec sp_executesql @tsql ;<br /> END <br /> fetch next from GetDDL into @tsql;<br />END <br /><br />CLOSE GetDDL<br />DEALLOCATE GetDDL<br />GO <br /><br /><br />-- ---------------------------------------------------------------------------------------------------<br />PRINT 'YOU MUST COPY FILES TO NEW LOCATION then execute the following commands'<br />PRINT '!!! If an error occurs when restarting => adjust file permission to allow SQL Service account to access those files'<br />-- ---------------------------------------------------------------------------------------------------<br /><br />PRINT 'Bringing user databases ONLINE'<br /><br />DECLARE @JustTest BIT = #CHANGEME<br />DECLARE GetDDL CURSOR FOR <br /> select 'ALTER DATABASE ' + QUOTENAME(name) + ' SET ONLINE' from master.sys.databases<br /> where name <> 'master'<br /><br />DECLARE @tsql NVARCHAR(MAX); <br />OPEN GetDDL ;<br /><br />fetch next from GetDDL into @tsql;<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN <br /> if(@JustTest = 1) <br /> BEGIN<br /> PRINT @tsql ;<br /> END <br /> ELSE <br /> BEGIN <br /> exec sp_executesql @tsql ;<br /> END<br /> fetch next from GetDDL into @tsql;<br />END <br /><br />CLOSE GetDDL<br />DEALLOCATE GetDDL<br />GO <br /><br />if(exists(select * from master.sys.master_files where DB_NAME(database_id) = 'master' and SUBSTRING(physical_name,0,4) = 'C:\'))<br />BEGIN <br /> PRINT '!!!!! MASTER Database needs to be moved too !!!!'<br /> PRINT 'Here is the procedure : '<br /> PRINT ''<br /> PRINT '1) Open SQL Server Configuration Manager'<br /> PRINT '2) On "SQL Server Services" tab, right-click on the service related to the instance you need to take care of'<br /> PRINT '3) Choose "Properties" in the pop-up menu'<br /> PRINT '4) Go to advanced settings and edit startup parameters'<br /> PRINT '5) Edit at least "-d"(master.mdf) and "-l" (master.ldf) parameters. Optionnally also edit "-e" (errorlog) option'<br /> PRINT ' Example : -dD:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf;-eD:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;-lD:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf'<br /> PRINT '6) Stop SQL Server services'<br /> PRINT '7) Move master.mdf and master.ldf to new location'<br /> PRINT '8) Restart database engine'<br /> PRINT '9) Check all is OK'<br />END <br /><br />PRINT 'Just to be sure : Restart SQL Server instance (and dependant services)' </div>
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-5566070692343214392015-07-14T23:31:00.003-07:002015-07-14T23:31:44.301-07:00Delete Backup Files From Default Backup Location<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="content-text" itemprop="articleBody">
Copy the script to SSMS
<br />
Create the procedure
<br />
Run it
<br />
(Note: If required change the retention days)
<br />
RUN:
<br />
EXEC [usp_retention_backup]
<br />
</div>
<div class="content-text" itemprop="articleBody">
</div>
<div class="content-text" itemprop="articleBody">
USE [db_maint]<br />GO<br />SET ANSI_NULLS ON<br />GO<br />SET QUOTED_IDENTIFIER ON<br />GO<br /><br />IF EXISTS ( SELECT *<br /> FROM sys.objects<br /> WHERE object_id = OBJECT_ID(N'usp_retention_backup')<br /> AND type = N'P')<br /><br />DROP PROCEDURE [usp_retention_backup];<br />GO<br /><br />--EXEC sp_configure 'xp_cmdshell', 1<br /><br />--GO<br /><br />--RECONFIGURE<br /><br />--GO<br /><br /><br />Create PROC [dbo].[usp_retention_backup] <br /><br />(@days AS VARCHAR(4) = 3 -- Pass number of days<br /><br />)<br /><br /><br /><br />/*******************************************************************************************************<br /><br />** DESCRIPTION: DELETE BACKUP FILES FROM DEFAULT BACKUP LOCATION<br /><br />** WRITTEN BY: SM<br /><br />** DATE: 13/03/2015<br /><br /><br /><br />***<br /><br />1. No more hard code of the backup location. Instead it will extract the backup location from registry.<br /><br />2. If in the registry the backupdirectory is not created, it will throw <br /><br />3. Pass the right parameters which signifies the days before which you want to delete the backup files.<br /><br /> For E.g. if @days = 3, it will delete all the backup files which are 3 days older than today's back.<br /><br /> if @days = 0, the procedure will delete all backup files from the backup location.<br /><br /><br /><br />4. It will tell you the count of the backup files deleted from the location by running this script.<br /><br />5. If you want to retain any of the backup files from being removed, please change the extension of the backup files from <br /><br /> ".BAK" to ".XXX" (XXX = Any other name)<br /><br />******************************************************************************************************<br /><br /><br /><br />** RUNNING THE PROCEDURE INSTRUCTIONS... **<br /><br /><br /><br />*/<br /><br />AS<br /><br />SET NOCOUNT ON<br /><br />BEGIN<br /><br />--BEGIN TRY<br /><br /><br /><br />DECLARE @backup_path nvarchar(2048);<br /><br />DECLARE @backupfile nvarchar(1000);<br /><br />DECLARE @BackupDirectory NVARCHAR(2048);<br /><br />--DECLARE @days AS VARCHAR(2) -- days for retention<br /><br />DECLARE @path AS VARCHAR(128) -- the path for deletion<br /><br />DECLARE @cmd AS VARCHAR(512) -- the actually command<br /><br />DECLARE @currentDateTime datetime;<br /><br />DECLARE @filename nvarchar(256);<br /><br />DECLARE @return_value INT;<br /><br />DECLARE @return_value1 INT;<br /><br />DECLARE @counter int;<br /><br /><br /><br />--DECLARE @min INT;<br /><br />--SET @currentDateTime = GetDate();<br /><br /><br /><br />EXEC @return_value = <br /><br /> MASTER..XP_INSTANCE_REGREAD @rootkey = 'HKEY_LOCAL_MACHINE', <br /><br /> @key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER',<br /><br /> @value_name = 'BackupDirectory', @BackupDirectory = @BackupDirectory OUTPUT ; <br /><br /> SET @backup_path = (SELECT @BackupDirectory AS [SQL Server default backup Value]) <br /><br /> select @backup_path as "Backup Path"<br /><br /> --SELECT @backup_path<br /><br /> -- If the backup path has a "\" at the end, then remove it, as it will not work to delete files per the syntax of forfiles.<br /> IF (SELECT RIGHT(@backup_path,1) ) ='\' <br /><br /> BEGIN<br /> SET @backup_path = LEFT(@backup_path, LEN(@backup_path) - 1)<br /><br /> END<br /> ELSE <br /> select @backup_path as "Backup Path"<br /><br />IF @return_value <> 0 -- It's a failure<br /><br />BEGIN<br /><br /> PRINT 'Unable to retrieve a valid Backup directory from Registry'<br /><br /> <br /><br /> --RETURN(1) --Exits unconditionally from a query or procedure<br /><br /> RETURN @@ERROR<br /><br />END<br /><br /><br /><br />-- Query to check number of backup files in the location.<br /><br /><br /><br />IF OBJECT_ID('#DirOutput') IS NOT NULL<br /><br />DROP TABLE #DirOutput;<br /><br /><br /><br />DECLARE @cmd1 nvarchar(500),<br /><br /> @count1 INT;<br /><br /><br /><br />SET @cmd1 = 'dir ' +@backup_path+' /A:A'<br /><br />--PRINT @cmd1<br /><br />CREATE TABLE #DirOutput(<br /><br /> files varchar(500))<br /><br /><br /><br />INSERT INTO #DirOutput<br /><br />EXEC master.dbo.xp_cmdshell @cmd1<br /><br /><br /><br />SELECT @count1 = COUNT(*)<br /><br />FROM #DirOutput<br /><br />WHERE files LIKE '[0-9][0-9]/%'<br /><br /><br /><br />SELECT @count1 'Number of Backup Files before delete'<br /><br />DROP TABLE #DirOutput<br /><br /><br /><br />--SET @days = '3' -- change the days here, remember it is type VARCHAR<br /><br />--SET @days = @days;<br /><br />SET @cmd = 'forfiles /P "' + @backup_path + '" /s /m *.bak /d -' + @days + ' /c "cmd /c del @path"' <br /><br /><br />-- forfiles /P "I:\BACKUP\UAT\" /S /M *.bak /D -3 /C "cmd /c del @PATH"<br /><br />--Print @cmd<br /><br />EXEC @return_value1 = master.dbo.xp_cmdshell @cmd<br /><br />--PRINT @return_value1<br /><br /> IF @return_value1=0<br /><br /> BEGIN<br /><br />--SELECT @@ROWCOUNT AS DELETED; <br /><br />--PRINT @backup_path<br /><br />SELECT 'BACKUP FILES ARE SUCCESSFULLY DROPPED FROM SERVER: ' + @@SERVERNAME;<br /><br /><br /><br />-- Query to check number of backup files in the location.<br /><br />--PRINT @backup_path<br /><br />IF OBJECT_ID('#DirOutput10') IS NOT NULL<br /><br />DROP TABLE #DirOutput10<br /><br />DECLARE @cmd10 nvarchar(500),<br /><br /> @count10 INT,<br /><br /> @files10 INT;<br /><br /><br /><br />SET @cmd10 = 'dir ' +@backup_path+' /A:A'<br /><br />--PRINT @cmd10<br /><br />--PRINT @cmd1<br /><br />CREATE TABLE #DirOutput10(<br /><br /> files varchar(500))<br /><br /><br /><br />INSERT INTO #DirOutput10<br /><br />EXEC master.dbo.xp_cmdshell @cmd10<br /><br /><br /><br />SELECT @count10 = COUNT(*)<br /><br />FROM #DirOutput10<br /><br />WHERE files LIKE '[0-9][0-9]/%'<br /><br /><br /><br />SET @files10= @count1 - @count10;<br /><br />SELECT @files10 'Number of Backup Deleted';<br /><br />DROP TABLE #DirOutput10<br /><br /><br /><br />END<br /><br />ELSE<br /><br />RETURN @@ERROR<br /><br />SET NOCOUNT OFF<br />END<br /><br />GO<br />SET ANSI_NULLS OFF<br />GO<br />SET QUOTED_IDENTIFIER OFF<br />GO </div>
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-22539339519431396982015-07-14T23:28:00.004-07:002015-07-14T23:28:31.110-07:00Recent Restore History<div dir="ltr" style="text-align: left;" trbidi="on">
Simply execute on your instance to receive the most recent info about any database restores that have taken place on the server.<br />
<br />
SET NOCOUNT ON<br />SELECT<br /> destination_database_name<br /> ,bmf.physical_device_name<br /> ,restore_date <br />FROM msdb.dbo.restorehistory<br />INNER JOIN msdb.dbo.backupset as bs ON bs.backup_set_id = msdb.dbo.restorehistory.backup_set_id<br />INNER JOIN msdb.dbo.backupmediafamily as bmf ON bs.media_set_id = bmf.media_set_id <br />WHERE restore_history_id IN <br /> (<br /> SELECT MAX(restore_history_id)<br /> FROM msdb.dbo.restorehistory<br /> WHERE restore_type = 'D' <br /> AND destination_database_name IN <br /> (<br /> SELECT DISTINCT destination_database_name <br /> FROM msdb.dbo.restorehistory<br /> )<br /> GROUP BY destination_database_name <br /> )<br />ORDER BY restore_date DESC<br />SET NOCOUNT OFF </div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-33376313011769451492015-07-14T23:27:00.003-07:002015-07-14T23:27:36.866-07:00List schemas in a database<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="content-text" itemprop="articleBody">
Run the script to see which schemas are available in a database.
<br />
[SchemaName] = the schema's name
<br />
<span style="font-size: x-small;">[SchemaOwner] </span><span style="font-size: 12.8000001907349px;">= authorization specified at creation</span>
<br />
<span style="font-size: 12.8000001907349px;">[Source] is either User, System, or Fixed Role</span>
<br />
</div>
<div class="content-text" itemprop="articleBody">
-- Works on MS SQL Server 2005+<br />SELECT<br /> a.name As [SchemaName] -- The schema's name<br /> , b.name As [SchemaOwner] -- Set at creation<br /> , CASE <br /> WHEN a.schema_id <5 THEN 'SYSTEM' <br /> WHEN a.schema_id >16000 THEN 'FIXED ROLE'<br /> ELSE 'User' <br /> END As [Source] -- User or system?<br />FROM sys.schemas a<br /> INNER JOIN sys.schemas b<br /> ON a.principal_id = b.schema_id<br />ORDER BY <br /> [Source] DESC -- Sort 'user' schemas to the top<br /> </div>
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-24469245964624741112015-07-14T23:26:00.000-07:002015-07-14T23:26:06.977-07:00Check Overdue Databases Backup List For Multiple SQL Servers<div dir="ltr" style="text-align: left;" trbidi="on">
We can use this SQL Script to get Overdue databases list which have not been backed-up from last 24 hrs.
<br />
Note: We can change the Rotation in this SQL Script.
<br />
We can use this SQL Script as input in SQLCMD/OSQL command as:
<br />
Put Overdue database SQL query into "C:\temp_rahul\Overdue.sql"
<br />
-S = Server Name here is "local\sql"
<br />
-U = User Name here is SA
<br />
-d = Database Name here is master
<br />
-i = Input SQL file here as "C:\temp_rahul\Overdue.sql"
<br />
-n = Print path here as C:\temp_rahul\Overdue.txt
<br />
osql -Slocal\sql -Usa -Ppass@#123 -dmaster -i "C:\temp_rahul\Overdue.sql"-w180 -n >> "C:\temp_rahul\Overdue.txt"<br />
<br />
DECLARE @li_rowcount int<br />DECLARE @ldt_timelimit datetime<br />DECLARE @lvc_msg varchar(2000)<br />DECLARE @hidden varchar(50)<br /><br />SET NOCOUNT ON<br />SET ROWCOUNT 0<br /><br /><br />SET ANSI_WARNINGS OFF <br /><br />-- Create and populate temp table with most recent full backup date<br /><br /><br />create table #tempexc (CustID varchar (150),Rotation int, DOW int )<br />if exists (select * from master.dbo.sysobjects where id = object_id(N'[master].[dbo].[_OverdueBackups_Exceptions]') )<br />begin<br /> insert into #tempExc (CustID,Rotation,DOW) (select * from [master].[dbo].[_OverdueBackups_Exceptions])<br /> select @hidden = ' * '<br />end<br />else <br /> select @hidden = ' '<br />insert into #tempexc (CustID,[Rotation],[DOW]) values ('pubs',0,0)<br />insert into #tempexc (CustID,[Rotation],[DOW]) values ('tempdb',0,0)<br />insert into #tempexc (CustID,[Rotation],[DOW]) values ('Northwind',0,0)<br /><br /><br />SELECT sdb.name, Max(backup_start_date) AS backup_start_date, 0 Rotation<br />INTO #OverdueBackups<br />FROM master.sys.databases sdb LEFT OUTER JOIN<br /> msdb.dbo.backupset bs on bs.database_name = sdb.name<br />WHERE (bs.type ='D' OR bs.type ='I' OR bs.type IS NULL) AND state_desc IN ('ONLINE') AND replica_id is null<br />GROUP BY sdb.name<br />ORDER BY sdb.name<br /><br />-- Remove anything with a Rotation of 0 i.e. to be ignored<br />delete #OverdueBackups<br />where name in (select CustID from #tempexc where Rotation = 0)<br /><br />-- Set default Rotation = 24hrs (We can change it)<br />update #OverdueBackups <br />set #OverdueBackups.Rotation = -24<br /><br />--Set Rotation period of any exceptions<br />update #OverdueBackups <br />set #OverdueBackups.Rotation = #tempexc.Rotation<br />from #tempexc <br />where name = CustID and #tempexc.Rotation < 0<br />-- Remove databases backed up within the time limit<br />-- Note Rotations defined as Negative values<br /><br />DELETE #OverdueBackups<br />WHERE backup_start_date > DateAdd( hh, Rotation, GetDate() )<br />-- Anything left is overDue<br /><br />PRINT CHAR(13)+CHAR(10)<br />SELECT @li_rowcount = COUNT(name) FROM #OverdueBackups<br /><br />-- If any rows left<br />IF @li_rowcount > 0<br /> BEGIN<br /> PRINT @@Servername + @hidden+ space(25- len(@@Servername)) + + CHAR(9) + <br /> ' - SOME or ALL BACKUPS OVERDUE (NOTE: Take backup of databases wherever required.)' +<br /> CHAR(13)+CHAR(10)<br /> SELECT CAST ('' + name + '''' AS char(100)) AS ' Overdue backups',<br /> CASE WHEN backup_start_date IS NULL THEN '-- Never backed up' <br /> ELSE '-- '+CAST(backup_start_date AS varchar(19)) <br /> END AS ' Last Full backup', Rotation as [Rotation period]<br /> FROM #OverdueBackups<br /> ORDER BY CASE WHEN backup_start_date IS NULL THEN '2' ELSE '1' END, name<br /> END<br />ELSE<br /> PRINT @@Servername + SPACE(26- len(@@Servername)) + ' - ALL OK' +@hidden+ CHAR(13)+CHAR(10)<br /><br /><br />DROP TABLE #tempexc<br />DROP TABLE #OverdueBackups<br />GO<br /> </div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-87017109444089916882015-07-14T23:25:00.000-07:002015-07-14T23:25:16.966-07:00Monitor SQL Server and Service <div dir="ltr" style="text-align: left;" trbidi="on">
<div class="content-text" itemprop="articleBody">
<div style="margin-bottom: .1in; margin-left: 0in; margin-right: 0in; margin-top: .1in;">
<span style="font-family: Arial, sans-serif; font-size: 9.5pt;">Create a .txt file with all the server name and keep it in safe location (C:\ServerList.txt).<span class="apple-converted-space"> </span></span>
</div>
<div style="margin: 0.1in 0in;">
<span style="font-family: Arial, sans-serif; font-size: 9.5pt;">Save the powershell script to a preferred location as .ps1 file.</span>
</div>
<div style="margin-bottom: .1in; margin-left: 0in; margin-right: 0in; margin-top: .1in;">
<span style="font-family: Arial, sans-serif; font-size: 9.5pt;">Create a new task on Windows Task </span><span style="font-size: 12.8000001907349px;">Scheduler </span><span style="font-family: Arial, sans-serif; font-size: 9.5pt;">to run the powershell script every 5 or 10 minutes.</span>
</div>
</div>
<div class="content-text" itemprop="articleBody">
</div>
<div class="content-text" itemprop="articleBody">
$servers = gc 'C:\ServerList.txt'<br />foreach($server in $servers)<br />{$body=get-wmiobject win32_service -computername $server | <br />select name,state | where {($_.name -eq "MSSQLSERVER")-and $_.state -match "Stopped"} | <br /> Out-String<br /> <br />if ($body.Length -gt 0)<br />{<br /> $smtp = new-object Net.Mail.SmtpClient("xxxxx.xxxxx.com") <br /> $subject="SQL service is down on " + $server<br /> $smtp.Send("xxxxx@xxxxx.com", "mashrurs@shuvo.com", $subject, $body)<br /> <br />}<br />}<br /><br />foreach($server in $servers)<br /> {<br /> $result = (Test-Connection $server -Quiet)<br /> if ($result -match 'False')<br /> {<br /> $smtp = new-object Net.Mail.SmtpClient("XXXX.XXXX.com") <br /> $subject="SQL Server $Server is down"<br /> $smtp.Send("XXXX@xxxx.com", "mashrurs@shuvo.com", $subject, $result)<br /> }<br /> } </div>
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-76574607905714560182015-07-14T23:23:00.003-07:002015-07-14T23:23:55.131-07:00Setup Alerts for SQL Replication<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="content-text" itemprop="articleBody">
<div>
/* Typical SQL Alerts to deal with REplication. */
</div>
<div>
</div>
<div>
USE [msdb]
</div>
<div>
GO
</div>
<div>
</div>
<div>
--1.
</div>
<div>
EXEC msdb.dbo.sp_add_alert @name=N'Replication Warning: Long merge over dialup connection (Threshold: mergeslowrunduration)',
</div>
<div>
@message_id=14163,
</div>
<div>
@severity=0,
</div>
<div>
@enabled=1,
</div>
<div>
@delay_between_responses=30,
</div>
<div>
@include_event_description_in=5,
</div>
<div>
@category_name=N'Replication',
</div>
<div>
@job_id=N'00000000-0000-0000-0000-000000000000'
</div>
<div>
GO
</div>
<div>
--2.
</div>
<div>
EXEC msdb.dbo.sp_add_alert @name=N'Replication Warning: Long merge over LAN connection (Threshold: mergefastrunduration)',
</div>
<div>
@message_id=14162,
</div>
<div>
@severity=0,
</div>
<div>
@enabled=1,
</div>
<div>
@delay_between_responses=30,
</div>
<div>
@include_event_description_in=5,
</div>
<div>
@category_name=N'Replication',
</div>
<div>
@job_id=N'00000000-0000-0000-0000-000000000000'
</div>
<div>
GO
</div>
<div>
</div>
<div>
--3.
</div>
<div>
EXEC msdb.dbo.sp_add_alert @name=N'Replication Warning: Slow merge over dialup connection (Threshold: mergeslowrunspeed)',
</div>
<div>
@message_id=14165,
</div>
<div>
@severity=0,
</div>
<div>
@enabled=1,
</div>
<div>
@delay_between_responses=30,
</div>
<div>
@include_event_description_in=5,
</div>
<div>
@category_name=N'Replication',
</div>
<div>
@job_id=N'00000000-0000-0000-0000-000000000000'
</div>
<div>
GO
</div>
<div>
</div>
<div>
--4.
</div>
<div>
EXEC msdb.dbo.sp_add_alert @name=N'Replication Warning: Slow merge over LAN connection (Threshold: mergefastrunspeed)',
</div>
<div>
@message_id=14164,
</div>
<div>
@severity=0,
</div>
<div>
@enabled=1,
</div>
<div>
@delay_between_responses=30,
</div>
<div>
@include_event_description_in=5,
</div>
<div>
@category_name=N'Replication',
</div>
<div>
@job_id=N'00000000-0000-0000-0000-000000000000'
</div>
<div>
GO
</div>
<div>
</div>
<div>
--5.
</div>
<div>
EXEC msdb.dbo.sp_add_alert @name=N'Replication Warning: Subscription expiration (Threshold: expiration)',
</div>
<div>
@message_id=14160,
</div>
<div>
@severity=0,
</div>
<div>
@enabled=1,
</div>
<div>
@delay_between_responses=30,
</div>
<div>
@include_event_description_in=5,
</div>
<div>
@category_name=N'Replication',
</div>
<div>
@job_id=N'00000000-0000-0000-0000-000000000000'
</div>
<div>
GO
</div>
<div>
</div>
<div>
--6.
</div>
<div>
EXEC msdb.dbo.sp_add_alert @name=N'Replication Warning: Transactional replication latency (Threshold: latency)',
</div>
<div>
@message_id=14161,
</div>
<div>
@severity=0,
</div>
<div>
@enabled=1,
</div>
<div>
@delay_between_responses=30,
</div>
<div>
@include_event_description_in=5,
</div>
<div>
@category_name=N'Replication',
</div>
<div>
@job_id=N'00000000-0000-0000-0000-000000000000'
</div>
<div>
GO
</div>
<div>
</div>
<div>
--7.
</div>
<div>
EXEC msdb.dbo.sp_add_alert @name=N'Replication: agent custom shutdown',
</div>
<div>
@message_id=20578,
</div>
<div>
@severity=0,
</div>
<div>
@enabled=1,
</div>
<div>
@delay_between_responses=0,
</div>
<div>
@include_event_description_in=5,
</div>
<div>
@category_name=N'Replication',
</div>
<div>
@job_id=N'00000000-0000-0000-0000-000000000000'
</div>
<div>
GO
</div>
<div>
</div>
<div>
--8.
</div>
<div>
EXEC msdb.dbo.sp_add_alert @name=N'Replication: agent failure',
</div>
<div>
@message_id=14151,
</div>
<div>
@severity=0,
</div>
<div>
@enabled=1,
</div>
<div>
@delay_between_responses=0,
</div>
<div>
@include_event_description_in=5,
</div>
<div>
@category_name=N'Replication',
</div>
<div>
@job_id=N'00000000-0000-0000-0000-000000000000'
</div>
<div>
GO
</div>
<div>
</div>
<div>
--9.
</div>
<div>
EXEC msdb.dbo.sp_add_alert @name=N'Replication: agent retry',
</div>
<div>
@message_id=14152,
</div>
<div>
@severity=0,
</div>
<div>
@enabled=1,
</div>
<div>
@delay_between_responses=0,
</div>
<div>
@include_event_description_in=5,
</div>
<div>
@category_name=N'Replication',
</div>
<div>
@job_id=N'00000000-0000-0000-0000-000000000000'
</div>
<div>
GO
</div>
<div>
</div>
<div>
--10.
</div>
<div>
EXEC msdb.dbo.sp_add_alert @name=N'Replication: agent success',
</div>
<div>
@message_id=14150,
</div>
<div>
@severity=0,
</div>
<div>
@enabled=1,
</div>
<div>
@delay_between_responses=0,
</div>
<div>
@include_event_description_in=5,
</div>
<div>
@category_name=N'Replication',
</div>
<div>
@job_id=N'00000000-0000-0000-0000-000000000000'
</div>
<div>
GO
</div>
<div>
</div>
<div>
--11.
</div>
<div>
EXEC msdb.dbo.sp_add_alert @name=N'Replication: expired subscription dropped',
</div>
<div>
@message_id=14157,
</div>
<div>
@severity=0,
</div>
<div>
@enabled=1,
</div>
<div>
@delay_between_responses=0,
</div>
<div>
@include_event_description_in=5,
</div>
<div>
@category_name=N'Replication',
</div>
<div>
@job_id=N'00000000-0000-0000-0000-000000000000'
</div>
<div>
GO
</div>
<div>
</div>
<div>
--12.
</div>
<div>
EXEC msdb.dbo.sp_add_alert @name=N'Replication: Subscriber has failed data validation',
</div>
<div>
@message_id=20574,
</div>
<div>
@severity=0,
</div>
<div>
@enabled=1,
</div>
<div>
@delay_between_responses=0,
</div>
<div>
@include_event_description_in=5,
</div>
<div>
@category_name=N'Replication',
</div>
<div>
@job_id=N'00000000-0000-0000-0000-000000000000'
</div>
<div>
GO
</div>
<div>
</div>
<div>
--13.
</div>
<div>
EXEC msdb.dbo.sp_add_alert @name=N'Replication: Subscriber has passed data validation',
</div>
<div>
@message_id=20575,
</div>
<div>
@severity=0,
</div>
<div>
@enabled=1,
</div>
<div>
@delay_between_responses=0,
</div>
<div>
@include_event_description_in=5,
</div>
<div>
@category_name=N'Replication',
</div>
<div>
@job_id=N'00000000-0000-0000-0000-000000000000'
</div>
<div>
GO
</div>
<div>
</div>
<div>
--14.
</div>
<div>
EXEC msdb.dbo.sp_add_alert @name=N'Replication: Subscription reinitialized after validation failure',
</div>
<div>
@message_id=20572,
</div>
<div>
@severity=0,
</div>
<div>
@enabled=1,
</div>
<div>
@delay_between_responses=0,
</div>
<div>
@include_event_description_in=5,
</div>
<div>
@category_name=N'Replication',
</div>
<div>
@job_id=N'00000000-0000-0000-0000-000000000000'
</div>
<div>
GO </div>
<div>
</div>
<div>
</div>
</div>
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-39460816517496498592015-07-14T23:22:00.003-07:002015-07-14T23:22:43.729-07:00Conver Number to string ( exp. 9 - 0009 or 9 - 09)<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="content-text" itemprop="articleBody">
This Function convert integer to string with N number of ceros to the
left. It only needs two parameters the integer value and the lenght of
the string that you need..
<br />
Example:
<br />
Select dbo.NumberToString (9, 5) you get 00009
<br />
<span style="font-size: 12.8000001907349px;"> Select dbo.NumberToString (7, 2) you get 07</span>
<br />
<span style="font-size: 12.8000001907349px;">;)</span><br />
<br />
<span style="font-size: 12.8000001907349px;">CREATE FUNCTION [dbo].[NumberToString] (@value int, @size INT) <br />RETURNS VARCHAR(20) <br />AS <br />BEGIN <br /> DECLARE @TMP VARCHAR (128); <br /> SET @TMP = CAST(@value AS VARCHAR(20)); <br /> IF @TMP IS NULL <br /> SET @TMP=0; <br /> <br /> WHILE LEN(@TMP)< @SIZE <br /> SET @TMP = '0'+@TMP; <br /> RETURN @TMP; <br />END </span>
<br />
</div>
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-68590266994773915352015-07-14T23:21:00.000-07:002015-07-14T23:21:14.261-07:00SQL Backups Compression Archive<div dir="ltr" style="text-align: left;" trbidi="on">
This script takes the last full backup includes diff and logs backups related until the next full backup in one file.
<br />
It requires that 7za.exe is on PATH, (ej. %windir%\system32).
<br />
To use it run it like:
<br />
EXECUTE [dbo].[DatabaseBackupCompression] @DatabaseName = 'DB_EXAMPLE' ,
<br />
@BackupDirectory = 'C:\DB_BACKUPS' ,
<br />
@FinalDirectory = 'C:\ARCHIVE_BACKUPS'
<br />
Replace @BackupDirectory with the path used in Ola Hallengren script,
@DatabaseName with the database to archive and @FinalDirectory with the
path where the archive will be stored.
<br />
It takes aditional arguments like:
<br />
@ServerName: If running from another server.
<br />
@DatabaseName: Database to compress
<br />
@BackupDirectory: Path of Backup Directory
<br />
@FinalDirectory: Path of Archived files
<br />
@FormatFolder: Folder structure created to store archives, vaild formats are: yyyyMMdd, yyyyMM\dd, yyyy\MM\dd, yyyyMM, yyyy\MM
<br />
@CompressionLevel: 0 No Compression (Store) - 9 Max compression, {5} - Default (Normal) Compression
<br />
@SplitFileMB: Split the file (volume) in every X MB.
<br />
I hope this is useful.<br />
<br />
USE [master]<br />GO<br /><br />CREATE PROCEDURE [dbo].[DatabaseBackupCompression]<br /> @ServerName NVARCHAR(MAX) = NULL,<br /> @DatabaseName NVARCHAR(MAX) = NULL,<br /> @BackupDirectory NVARCHAR(MAX) = NULL,<br /> @FinalDirectory NVARCHAR(MAX) = NULL,<br /> @FormatFolder NVARCHAR(MAX) = 'yyyyMM', -- yyyyMMdd, yyyyMM\dd, yyyy\MM\dd, yyyyMM, yyyy\MM<br /> @CompressionLevel INT = 5, -- 0 No Compression (Store) - 9 Max compression, {5} - Default (Normal) Compression<br /> @SplitFileMB INT = NULL<br />AS<br />BEGIN<br /> SET NOCOUNT ON<br /><br /> DECLARE @CurrentRootDirectoryPath nvarchar(4000)<br /> DECLARE @FullDirectoryPath nvarchar(512)<br /> DECLARE @DirectoryCheck bit<br /><br /> DECLARE @Error int<br /> SET @Error = 0<br /><br /> IF @ServerName IS NULL<br /> SET @ServerName = @@SERVERNAME<br /> <br /> DECLARE @StartMessage nvarchar(max)<br /> DECLARE @EndMessage nvarchar(max)<br /> DECLARE @DatabaseMessage nvarchar(max)<br /> DECLARE @ErrorMessage nvarchar(max)<br /><br /> ----------------------------------------------------------------------------------------------------<br /> --// Log initial information //--<br /> ----------------------------------------------------------------------------------------------------<br /> SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,SYSDATETIME(),120) + CHAR(13) + CHAR(10)<br /> SET @StartMessage = @StartMessage + 'Server: ' + CAST(SERVERPROPERTY('ServerName') AS nvarchar) + CHAR(13) + CHAR(10)<br /> SET @StartMessage = @StartMessage + 'Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS nvarchar) + CHAR(13) + CHAR(10)<br /> SET @StartMessage = @StartMessage + 'Edition: ' + CAST(SERVERPROPERTY('Edition') AS nvarchar) + CHAR(13) + CHAR(10)<br /> SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + (SELECT QUOTENAME(schemas.name) FROM sys.schemas schemas INNER JOIN sys.objects objects ON schemas.[schema_id] = objects.[schema_id] WHERE [object_id] = @@PROCID) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)<br /> RAISERROR(@StartMessage,10,1) WITH NOWAIT<br /><br /> ----------------------------------------------------------------------------------------------------<br /> --// Set Root Backup Directory //--<br /> ----------------------------------------------------------------------------------------------------<br /> IF @BackupDirectory IS NULL<br /> BEGIN<br /> DECLARE @DefaultBakDirectory nvarchar(4000)<br /> EXECUTE [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', @DefaultBakDirectory OUTPUT<br /> SET @BackupDirectory = @DefaultBakDirectory<br /> END<br /> <br /> ----------------------------------------------------------------------------------------------------<br /> --// Remove last slash from path //--<br /> ----------------------------------------------------------------------------------------------------<br /> IF RIGHT(@BackupDirectory, 1) = '\'<br /> SET @BackupDirectory = LEFT(@BackupDirectory, LEN(@BackupDirectory) - 1)<br /><br /> IF RIGHT(@FinalDirectory, 1) = '\'<br /> SET @FinalDirectory = LEFT(@FinalDirectory, LEN(@FinalDirectory) - 1)<br /><br /> ----------------------------------------------------------------------------------------------------<br /> --// Set Full Path of Corresponding Backup //--<br /> ----------------------------------------------------------------------------------------------------<br /> SET @FullDirectoryPath = @BackupDirectory + '\' + @ServerName + '\' + @DatabaseName<br /> RAISERROR(@FullDirectoryPath,10,1) WITH NOWAIT<br /><br /> ----------------------------------------------------------------------------------------------------<br /> --// Fill Table Variable with filenames of backups //--<br /> ----------------------------------------------------------------------------------------------------<br /> DECLARE @DirectoryTree TABLE (<br /> name nvarchar(512)<br /> ,depth int<br /> ,isfile bit<br /> );<br /> DELETE FROM @DirectoryTree;<br /><br /> INSERT @DirectoryTree(name,depth,isfile)<br /> EXEC master.sys.xp_dirtree @FullDirectoryPath,0,1<br /> <br /> ----------------------------------------------------------------------------------------------------<br /> --// Fill Table Variable with id and type of backups //--<br /> ----------------------------------------------------------------------------------------------------<br /> DECLARE @Backups TABLE (<br /> id int<br /> ,filepath nvarchar(512)<br /> ,typebak nvarchar(5)<br /> ,datebak datetime2(0)<br /> );<br /> DELETE FROM @Backups;<br /><br /> -- MAGIC!<br /> INSERT INTO @Backups(id,filepath,typebak,datebak)<br /> SELECT ROW_NUMBER() OVER(PARTITION BY replace(substring(name, len(name)-23, 4), '_', '')<br /> ORDER BY convert(datetime2(0), substring(name, len(name)-18, 8) + ' ' + <br /> substring(substring(name, len(name)-9, 6), 1, 2)+ ':' + substring(substring(name, len(name)-9, 6), 3, 2)+ ':' + <br /> substring(substring(name, len(name)-9, 6), 5, 2), 112) ASC),<br /> @FullDirectoryPath + '\'+replace(substring(name, len(name)-23, 4), '_', '')+'\' + name, <br /> replace(substring(name, len(name)-23, 4), '_', ''), <br /> convert(datetime2(0), substring(name, len(name)-18, 8) + ' ' + <br /> substring(substring(name, len(name)-9, 6), 1, 2)+ ':' + substring(substring(name, len(name)-9, 6), 3, 2)+ ':' + <br /> substring(substring(name, len(name)-9, 6), 5, 2), 112)<br /> FROM @DirectoryTree<br /> WHERE depth = 2 AND isfile = 1<br /><br /> ----------------------------------------------------------------------------------------------------<br /> --// Select range of last backup files //--<br /> ----------------------------------------------------------------------------------------------------<br /> DECLARE<br /> @id_work int,<br /> @date_start datetime2(0),<br /> @date_end datetime2(0),<br /> @date_end_full datetime2(0),<br /> @dateformat nvarchar(32),<br /> @folderformat varchar(32)<br /><br /> SELECT @id_work = MIN(id) FROM @Backups WHERE typebak = 'FULL' HAVING COUNT(*) > 1<br /> SELECT @date_start = datebak FROM @Backups WHERE typebak = 'FULL' AND id = @id_work<br /> SELECT @date_end_full = datebak FROM @Backups WHERE typebak = 'FULL' AND id = @id_work + 1<br /> SELECT @date_end = MAX(datebak) FROM @Backups WHERE typebak <> 'FULL' AND datebak BETWEEN @date_start AND @date_end_full<br /><br /> DELETE FROM @Backups WHERE typebak = 'FULL' AND id = @id_work + 1<br /><br /> IF @date_end IS NULL<br /> SET @date_end = @date_start;<br /><br /> ----------------------------------------------------------------------------------------------------<br /> --// Select range of last backup files //--<br /> ----------------------------------------------------------------------------------------------------<br /> IF OBJECT_ID('tempdb..##FilesToCompress') IS NOT NULL<br /> DROP TABLE ##FilesToCompress;<br /><br /> CREATE TABLE ##FilesToCompress (<br /> id int identity(1,1),<br /> filepath nvarchar(512)<br /> );<br /><br /> INSERT INTO ##FilesToCompress(filepath)<br /> SELECT filepath FROM @Backups WHERE datebak BETWEEN @date_start AND @date_end<br /> ORDER BY datebak ASC<br /><br /> IF (@date_start <> @date_end)<br /> SET @dateformat =<br /> REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@date_start,120)),'-',''),' ','_'),':','')<br /> + '-' +<br /> REPLACE(REPLACE(REPLACE((CONVERT(nvarchar, @date_end ,120)),'-',''),' ','_'),':','')<br /> ELSE<br /> SET @dateformat =<br /> REPLACE(REPLACE(REPLACE((CONVERT(nvarchar,@date_start,120)),'-',''),' ','_'),':','')<br /><br /> -- Valid formats yyyyMMdd, yyyyMM\dd, yyyy\MM\dd, yyyyMM, , yyyy\MM<br /> IF (@FormatFolder = 'yyyyMMdd')<br /> SET @folderformat = CONVERT(VARCHAR(8), @date_start, 112)<br /> ELSE IF (@FormatFolder = 'yyyyMM\dd')<br /> SET @folderformat = CONVERT(VARCHAR(6), @date_start, 112) + '\' + LEFT(CONVERT(VARCHAR(2), @date_start, 112), 2)<br /> ELSE IF (@FormatFolder = 'yyyy\MM\dd')<br /> SET @folderformat = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '\')<br /> ELSE IF (@FormatFolder = 'yyyyMM')<br /> SET @folderformat = CONVERT(VARCHAR(6), @date_start, 112)<br /> ELSE IF (@FormatFolder = 'yyyy\MM')<br /> SET @folderformat = REPLACE(CONVERT(VARCHAR(7), GETDATE(), 111), '/', '\')<br /> ELSE<br /> SET @folderformat = CONVERT(VARCHAR(6), @date_start, 112) -- Same as yyyyMM<br /><br /> DECLARE<br /> @ExecString VARCHAR(8000),<br /> @CompressFile NVARCHAR(200),<br /> @FileList NVARCHAR(200)<br /><br /> SET @CompressFile = @DatabaseName + '_' + @dateformat +'.7z'<br /><br /> SET @FinalDirectory = @FinalDirectory + '\' + @ServerName + '\' + @DatabaseName + '\' + @folderformat<br /><br /> RAISERROR(@FinalDirectory,10,1) WITH NOWAIT<br /> <br /> SET @ExecString = 'mkdir ' + @FinalDirectory<br /> EXEC master..xp_cmdshell @ExecString, no_output;<br /><br /> SET @FileList = @FinalDirectory + '\' + @DatabaseName + '_' + @dateformat + '.txt'<br /><br /> SET @ExecString = 'bcp "select filepath from ##FilesToCompress" queryout ' + @FileList + ' -c -T -S' + @ServerName<br /> EXEC master..xp_cmdshell @ExecString, no_output;<br /><br /> IF EXISTS(SELECT 1 FROM ##FilesToCompress)<br /> BEGIN<br /> IF OBJECT_ID('tempdb..##FilesToCompress') IS NOT NULL<br /> DROP TABLE ##FilesToCompress;<br /> <br /> DECLARE @SplitCommand VARCHAR(20) = ''<br /> IF @SplitFileMB IS NOT NULL<br /> SET @SplitCommand = '-v' + CAST(@SplitFileMB AS VARCHAR) + 'm '<br /><br /> SET @ExecString = '@7za.exe a -ssc -bd -mx'+ CAST(@CompressionLevel AS VARCHAR) +' -t7z -ms=on ' + @SplitCommand + '"' + @FinalDirectory + '\' + @CompressFile + '" @' + @FileList + ''<br /> --PRINT @ExecString<br /> EXEC master..xp_cmdshell @ExecString, no_output;<br /><br /> SET @ExecString = '@for /f "delims=" %i in (' + @FileList +') do del /f /q %i'<br /> --PRINT @ExecString<br /> EXEC master..xp_cmdshell @ExecString, no_output;<br /><br /> SET @ExecString = '@del ' + @FileList<br /> --PRINT @ExecString<br /> EXEC master..xp_cmdshell @ExecString, no_output;<br /> END<br /><br /> ----------------------------------------------------------------------------------------------------<br /> --// Cleanup //--<br /> ----------------------------------------------------------------------------------------------------<br /> IF OBJECT_ID('tempdb..##FilesToCompress') IS NOT NULL<br /> DROP TABLE ##FilesToCompress;<br />END<br />GO </div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-22527965396979671182015-07-14T23:20:00.001-07:002015-07-14T23:20:09.930-07:00When server was rebooted<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="content-text" itemprop="articleBody">
<div>
Create a .txt and write down all your server name like
</div>
<div>
Server1
</div>
<div>
Server2
</div>
<div>
Server3
</div>
<div>
</div>
<div>
Save the txt file in safe location. C: ServerProdQaDev.txt
</div>
<div>
</div>
<div>
Run the following PowerShell script and see the results when is your server was rebooted
</div>
</div>
<div class="content-text" itemprop="articleBody">
</div>
<div class="content-text" itemprop="articleBody">
FOREACH($svr in GC C:\ServerProdQaDev.txt)<br />{Write-Host $svr “was rebooted on:” <br />$lastBootUpTime = Get-WmiObject Win32_OperatingSystem -ComputerName $svr | Select -ExpandProperty lastBootUpTime;<br />[System.Management.ManagementDateTimeConverter]::ToDateTime($LastBootUpTime);} </div>
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-23393128713939674412015-07-14T23:19:00.000-07:002015-07-14T23:19:16.979-07:00DB Integrity & Backup Report<div dir="ltr" style="text-align: left;" trbidi="on">
Execute on a server to return information for each database in regards to backups and database integrity checks.
<br />
I normally combine this with my email report procedure as a schedule report on each server.<br />
<br />
DECLARE <br /> @vcDBName VARCHAR(200)<br /> ,@vcExec VARCHAR(MAX) <br /><br />CREATE TABLE ##dbinfo<br /> (<br /> DBName VARCHAR(100)<br /> ,ParentObject VARCHAR(100)<br /> ,Object VARCHAR(100)<br /> ,Field VARCHAR(100)<br /> ,Value VARCHAR(100)<br /> )<br /><br />SET @vcExec = ''<br /><br />SELECT @vcExec = @vcExec+<br />' <br />INSERT INTO ##dbinfo<br /> (<br /> ParentObject<br /> ,Object<br /> ,Field<br /> ,Value<br /> )<br />EXEC(''DBCC DBINFO (['+name+']) WITH TABLERESULTS'')<br /><br />DELETE FROM ##dbinfo<br />WHERE Field <> ''dbi_dbccLastKnownGood''<br /><br />UPDATE ##dbinfo<br />SET DBName = '''+name+'''<br />WHERE DBName IS NULL<br />'<br />FROM sys.databases<br />WHERE [state] = 0<br /><br />EXEC (@vcExec)<br /><br />SELECT <br /> a.database_name<br /> ,a.FullBackupDate<br /> ,b.LogBackupDate<br /> ,sd.recovery_model_desc<br /> ,CASE<br /> WHEN value = '1900-01-01 00:00:00.000' THEN CAST('NeverRan' AS VARCHAR)<br /> WHEN DATEDIFF(d, value, GETDATE()) > 7 THEN CAST('NotCurrent' AS VARCHAR)<br /> ELSE CAST('Current' AS VARCHAR)<br /> END AS CheckDBStatus<br />FROM<br /> (<br /> SELECT<br /> database_name<br /> ,MAX(backup_finish_date) FullBackupDate<br /> FROM msdb.dbo.backupset<br /> WHERE TYPE = 'D'<br /> GROUP BY database_name<br /> ) a<br />LEFT OUTER JOIN <br /> (<br /> SELECT<br /> database_name<br /> ,MAX(backup_finish_date) LogBackupDate<br /> FROM msdb.dbo.backupset<br /> WHERE TYPE = 'L'<br /> GROUP BY database_name<br /> ) b <br />ON a.database_name = b.database_name<br />INNER JOIN sys.databases sd ON sd.name = a.database_name<br />INNER JOIN ##dbinfo db ON db.DBName = a.database_name<br />ORDER BY a.database_name<br /><br />DROP TABLE ##dbinfo </div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-18057919021138170292015-07-14T23:16:00.004-07:002015-07-14T23:16:46.712-07:00List Job Dependencies<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="content-text" itemprop="articleBody">
To execute:
<br />
exec spJob_Dependencies
<br />
Output:
<br />
<strong>Job Name Type_Desc </strong>
<br />
Test_Load Addresses USER_TABLE
<br />
Test_Load Post_Codes USER_TABLE<br />
<br />
create procedure spJob_Dependencies<br />as<br />select <br />database_name,<br />Job,<br />Name,<br />Type_Desc,<br />1 as Has_Dep<br /> from<br />(<br />Select a.database_name,<br />a.name Job,<br />b.name,<br />b.type_Desc,<br />charindex(b.name,a.command)as Has_Dep<br />from<br />(select js.database_name,<br />j.name,<br />js.command<br />from <br />msdb..sysjobs j<br />inner join msdb..sysjobsteps js<br />on j.job_id = js.job_id<br />and js.database_name = DB_NAME()<br />)a<br /> ,(select name,<br /> type_desc <br /> from sys.objects<br /> where type IN ('V','U','S','FN')<br /> )b)z <br /> where Has_Dep > 0<br /> order by 1,3,2 <br />
</div>
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-86568368403758672492015-07-14T23:01:00.004-07:002015-07-14T23:01:32.177-07:00List Out All User Defined Triggers<div dir="ltr" style="text-align: left;" trbidi="on">
Copy the script in SSMS
<br />
Run it.<br />
<br />
SET NOCOUNT ON<br />DECLARE @DB_Name varchar(100) <br />DECLARE @Command nvarchar(200)<br />DECLARE database_cursor CURSOR FOR <br />SELECT NAME <br /> FROM sys.databases <br /> WHERE database_id IN (SELECT dbid <br /> FROM sys.sysdatabases d <br /> EXCEPT <br /> SELECT database_id <br /> FROM msdb..suspect_pages) <br /> AND NAME NOT IN ('tempdb','distribution' ) <br /> --AND compatibility_level > 80 <br /> AND state_desc = 'ONLINE' <br /> AND source_database_id IS NULL -- Excludes snapshot <br /> --ORDER BY [seq]<br /> OPTION (FAST 10)<br /><br />OPEN database_cursor<br /><br />FETCH NEXT FROM database_cursor INTO @DB_Name<br /><br />WHILE @@FETCH_STATUS = 0 <br />BEGIN <br /> <br /> declare @cmd nvarchar(4000);<br /> select @cmd = N'use ' + quotename(@DB_Name) + N';'<br /> --exec sp_executesql @cmd<br /><br /> select @cmd = @cmd + '<br /> SELECT db_name() as Database_Name,<br /><br /> TRIG.name as Trigger_Name,<br /> TAB.name as Associated_Table_Name,<br /> trig.is_ms_shipped, --0; user trigger<br /> TRIG.is_disabled,<br /> Comments.Text TriggerText<br />FROM [sys].[triggers] as TRIG<br /> Inner Join sys.tables as TAB on TRIG.parent_id = TAB.object_id<br /> Inner Join syscomments Comments On TRIG.object_id = Comments.id<br /> Inner Join sys.objects as o on o.object_id = tab.object_id<br />WHERE<br /> TRIG.is_disabled = 1;' --disabled<br /><br />--select @cmd<br /> exec sp_executesql @cmd<br /><br /> FETCH NEXT FROM database_cursor INTO @DB_Name <br />END<br /><br />CLOSE database_cursor <br />DEALLOCATE database_cursor<br /><br />SET NOCOUNT OFF </div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-43603457700837448922015-07-14T23:00:00.002-07:002015-07-14T23:00:36.545-07:00Kill all USer Sessions before starting to restore a Database<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="content-text" itemprop="articleBody">
Copy the script to SSMS
<br />
Run it.
<br />
Exec [usp_KillBeforeRestoreDB] @db_name = 'AdventureWorks_2005'<br />
<br />
SET ANSI_WARNINGS OFF<br />GO<br /><br />SET QUOTED_IDENTIFIER ON<br />GO<br /><br />SET NOCOUNT ON<br />IF EXISTS ( SELECT *<br /> FROM sys.objects<br /> WHERE object_id = OBJECT_ID(N'usp_KillBeforeRestoreDB')<br /> AND type = N'P')<br /><br />DROP PROCEDURE [usp_KillBeforeRestoreDB];<br />GO<br /><br /><br />CREATE PROCEDURE [dbo].[usp_KillBeforeRestoreDB] (@db_name sysname)<br />AS<br /><br />/*To Run: Exec [usp_KillBeforeRestoreDB] @db_name = 'AdventureWorks_2005' */<br /><br />DECLARE @cmdKill VARCHAR (50)<br /><br />DECLARE killCursor<br /> CURSOR FOR<br /> SELECT 'KILL ' + CONVERT (VARCHAR (5),<br /> p.spid)<br /> FROM master.dbo.sysprocesses AS p<br /> WHERE p.dbid = DB_ID (@db_name)<br /><br />OPEN killCursor<br /><br />FETCH killCursor INTO @cmdKill<br /><br />WHILE<br /> 0 = @@FETCH_STATUS<br /> BEGIN<br /> EXECUTE (@cmdKill)<br /> FETCH killCursor INTO @cmdKill<br /> END<br /><br />CLOSE killCursor<br /><br />DEALLOCATE killCursor<br /><br /><br />GO<br /><br />SET NOCOUNT OFF <br />
</div>
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-24303968450979963602015-07-14T22:59:00.003-07:002015-07-14T22:59:23.794-07:00Compare hotfix<div dir="ltr" style="text-align: left;" trbidi="on">
f you have 2 node SQL Cluster or mirroring, may need to check all the
servers are on same windows hotfix or not. There is an easy way to
check it in PowerShell.
<br />
If both Servers are on same hotfix then there will no output. If not it will show which KB is missing on which Server.
<br />
**You must have Sysadmin privilege to the SQL server.<br />
<br />
$node1 = gwmi win32_quickfixEngineering -ComputerName Server1 <br />$node2 = gwmi win32_quickfixEngineering -ComputerName Server2<br />Compare-Object -ReferenceObject $node1 -DifferenceObject $node2 -Property HotFixId <br /> </div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-13865126650586749722015-07-14T22:58:00.004-07:002015-07-14T22:58:35.485-07:00Random Data Generator<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="content-text" itemprop="articleBody">
Sometimes, I find that I have need to generate a data set of random
integers or characters. I created the following query to generate a data
set containing a random set of data for various data types (integer,
float, decimal, varchar, bit and datetime). For integers you can input a
minimum and maximum value to be generated.
<br />
The query is well commented and is easy enough to alter for your specific needs.
<br />
I use a Numbers (i.e Tally) table to generate the appropriate number
of rows needed. If you don't have a Numbers table simply change the FROM
clause to:
<br />
FROM sys.columns A CROSS JOIN sys.columns B CROSS JOIN sys.columns C
<br />
I hope some of you find this helpful.
<br />
Peter Wehner
<br />
</div>
<div class="content-text" itemprop="articleBody">
</div>
<div class="content-text" itemprop="articleBody">
DECLARE @min INT, @max INT, @rowCnt INT<br /><br />SET @min = 1;<br />SET @max = 1000;<br />SET @rowCnt = 1000;<br /><br />SELECT TOP (@rowCnt)<br /><br />--ints<br />RandInt = ABS(CHECKSUM(NEWID())), -- Random integer<br />RandIntMinMaxInc = (ABS(CHECKSUM(NEWID())) % (@max - @min + 1)) + @min, -- @min & @max inclusive<br />RandIntMinIncMaxExc = (ABS(CHECKSUM(NEWID())) % (@max - @min)) + @min, -- @min inclusive & @max exclusive<br />RandIntMinExcMaxInc = (ABS(CHECKSUM(NEWID())) % (@max - @min)) + @min + 1, -- @min exclusive & @max inclusive<br />RandIntMinMaxExc = (ABS(CHECKSUM(NEWID())) % (@max - @min - 1)) + @min + 1, -- @min exclusive & @max exclusive<br /><br />--decimals/floats<br />RandFloatMinMaxExc = RAND(CHECKSUM(NEWID())) * (@max - @min ) + @min, -- min & max exlusive<br />RandDecMinMaxExc = CONVERT(DECIMAL(11,2), RAND(CHECKSUM(NEWID())) * (@max - @min ) + @min),-- min & max exlusive (set presicion & scale appropriately)<br /><br />-- DateTime (3,012,153 is max # of days for datetime, 3,652,058 max days for datetime2)<br />RandDate = DATEADD(DAY, ABS(CHECKSUM(NEWID())) % (3012153 + 1), CONVERT(DATETIME, '17530101')), -- Datetime (min = 1753-01-01, max = 9999-12-31)<br />RandDate2 = DATEADD(DAY, ABS(CHECKSUM(NEWID())) % (3652058 + 1), CONVERT(DATETIME2, '00010101')), -- Datetime2 (min = 0001-01-01, max = 9999-12-31)<br /><br />-- Bit<br />RandBit = CONVERT(BIT, ROUND(RAND(CHECKSUM(NEWID())), 0)),<br /><br />-- varchar<br />RandLetter = SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),<br /><br />-- replicate a random letter a random number of times between @min and @max<br />RandString = CONVERT(VARCHAR(MAX), REPLICATE(SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', 1 + ABS(CHECKSUM(NEWID())) % 26, 1),(ABS(CHECKSUM(NEWID())) % (@max - @min + 1)) + @min))<br /><br />FROM dbo.Numbers<br />--FROM sys.columns A CROSS JOIN sys.columns B CROSS JOIN sys.columns C </div>
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-55185297230905507842015-07-14T22:56:00.003-07:002015-07-14T22:56:49.825-07:00Powershell Script To Check the SQL Jobs Status remotely<div dir="ltr" style="text-align: left;" trbidi="on">
<div>
Steps to run this script..
</div>
<div>
</div>
<div>
1) Save the script in folder "D:\CheckSQLJobs.ps1"
</div>
<div>
</div>
<div>
2) Start windows powershell with the run as administrator option
</div>
<div>
</div>
<div>
3) Run the below command on windows powershell prompt like below.
</div>
<div>
</div>
<div>
PS C:\> <strong>. <span style="font-size: 12.8000001907349px;">D:\CheckSQLJobs.ps1</span></strong>
</div>
<div>
</div>
<div>
You will get the results like below.
</div>
<div>
</div>
<div>
</div>
<br />
<div class="wideImageContainer" style="width: 824px;">
<img alt="" border="0" src="http://www.sqlservercentral.com/Images/26597.jpg" style="cursor: zoom-in; display: block; max-width: 824px;" /><div>
<img align="bottom" src="http://www.sqlservercentral.com/Resources/Images/zoom.gif" style="margin: 0px 3px;" /><a href="https://www.blogger.com/null">Zoom in</a><span> | </span><a href="https://www.blogger.com/null">Open in new window</a></div>
</div>
<span style="font-size: 12.8000001907349px;">3) Now you can check SQL Job's STATUS remotely by below command.</span>
<br />
<span style="font-size: 12.8000001907349px;"> </span><span style="font-size: 12.8000001907349px;">PS C:\> </span><span style="font-size: 12.8000001907349px;"><strong>checkSQLJobs</strong> <strong>SQLInstanceName</strong> <strong>Options</strong></span>
<br />
<span style="font-size: 12.8000001907349px;">Options are :</span>
<br />
<div>
JA<span class="Apple-tab-span" style="white-space: pre;"> </span>## List Of All Jobs
</div>
<div>
JR<span class="Apple-tab-span" style="white-space: pre;"> </span>## List Of Currently Running Jobs
</div>
<div>
JS<span class="Apple-tab-span" style="white-space: pre;"> </span>## List Of Jobs with last status "Succeeded"
</div>
<div>
JF<span class="Apple-tab-span" style="white-space: pre;"> </span>## List Of Jobs with last status "Failed"
</div>
<div>
JC<span class="Apple-tab-span" style="white-space: pre;"> </span>## List Of Jobs with last status "Cancelled"
</div>
<div>
JD<span class="Apple-tab-span" style="white-space: pre;"> </span>## List Of "Disabled" Jobs
</div>
<div>
JNS<span class="Apple-tab-span" style="white-space: pre;"> </span>## List Of Jobs which are not scheduled"
</div>
<div>
jnxtrun ## Jobs Next Run date and time
</div>
<div>
</div>
<div>
Example :
</div>
<div>
</div>
<div>
<div class="wideImageContainer" style="width: 824px;">
<img alt="" border="0" src="http://www.sqlservercentral.com/Images/26605.jpg" style="cursor: zoom-in; display: block; font-size: 12.8px; max-width: 824px;" /><div>
<img align="bottom" src="http://www.sqlservercentral.com/Resources/Images/zoom.gif" style="margin: 0px 3px;" /><a href="https://www.blogger.com/null">Zoom in</a><span> | </span><a href="https://www.blogger.com/null">Open in new window</a></div>
</div>
</div>
<div>
</div>
<div>
</div>
<div>
</div>
<div>
So enjoy the Power of PowerShell.. Happy Learning :-) </div>
<div>
</div>
<div>
#######################<br />cd \<br />Set-ExecutionPolicy RemoteSigned<br />Add-PSSnapin SqlServerCmdletSnapin100<br />Add-PSSnapin SqlServerProviderSnapin100<br />[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")<br />#######################<br /><br />function checkSQLJobs<br />{<br /> $srv=NEW-OBJECT ('MICROSOFT.SQLSERVER.MANAGEMENT.SMO.SERVER') $args[0]<br /><br /> ###########################################################################################################################<br /> if($args[1] -EQ "JA") ## List Of All Jobs<br /> {<br /> $srv.jobserver.jobs| where-object {$_.name -like "*"} | sort-object -descending {$_.lastrundate} | select name,lastrundate,lastrunoutcome,currentrunstatus,currentrunstep,isenabled,nextrundate | format-table -AUTOSIZE | Out-String -Width 4096<br /> }<br /> ###########################################################################################################################<br /> elseif($args[1] -EQ "JR") ## List Of Running Jobs<br /> {<br /> $srv.jobserver.jobs| where-object {$_.name -like "*" -and $_.currentrunstatus -eq 1 } | sort-object -descending {$_.lastrundate} | select name,currentrunstatus,currentrunstep,lastrundate,lastrunoutcome,isenabled,nextrundate | format-table -AUTOSIZE | Out-String -Width 4096<br /> }<br /> ###########################################################################################################################<br /> elseif($args[1] -EQ "JS") ## List Of Jobs with status "Succeeded" <br /> {<br /> $srv.jobserver.jobs| where-object {$_.name -like "*" -and $_.lastrunoutcome -eq "succeeded" -and $_.currentrunstatus -ne 1} | sort-object -descending {$_.lastrundate} | select name,lastrundate,lastrunoutcome,currentrunstatus,currentrunstep,isenabled,nextrundate | format-table -AUTOSIZE | Out-String -Width 4096<br /> }<br /> ###########################################################################################################################<br /> elseif($args[1] -EQ "JF") ## List Of Jobs with status "Failed"<br /> {<br /> $srv.jobserver.jobs| where-object {$_.name -like "*" -and $_.lastrunoutcome -eq "failed" -and $_.currentrunstatus -ne 1} | sort-object -descending {$_.lastrundate} | select name,lastrundate,lastrunoutcome,currentrunstatus,currentrunstep,isenabled,nextrundate | format-table -AUTOSIZE | Out-String -Width 4096<br /> }<br /> ###########################################################################################################################<br /> elseif($args[1] -EQ "JC") ## List Of Jobs with status "Cancelled"<br /> {<br /> $srv.jobserver.jobs| where-object {$_.name -like "**" -and $_.lastrunoutcome -eq "cancelled" -and $_.currentrunstatus -ne 1} | sort-object -descending {$_.lastrundate} | select name,lastrundate,lastrunoutcome,currentrunstatus,currentrunstep,isenabled,nextrundate | format-table -AUTOSIZE | Out-String -Width 4096<br /> }<br /> ###########################################################################################################################<br /> elseif($args[1] -EQ "JD") ## List Of Jobs with status "Disabled"<br /> {<br /> $srv.jobserver.jobs| where-object {$_.name -like "*" -and $_.isenabled -eq 0 } | sort-object -descending {$_.lastrundate} | select name,lastrundate,lastrunoutcome,currentrunstatus,currentrunstep,isenabled,nextrundate | format-table -AUTOSIZE | Out-String -Width 4096<br /> }<br /> ###########################################################################################################################<br /> elseif($args[1] -EQ "JNS") ## List Of Jobs which are not scheduled"<br /> {<br /> $srv.jobserver.jobs| where-object {$_.name -like "*" -and $_.isenabled -eq 1 -and $_.nextrundate -eq "1/1/0001 12:00:00 AM" } | sort-object -descending {$_.lastrundate} | select name,lastrundate,lastrunoutcome,currentrunstatus,currentrunstep,isenabled,nextrundate | format-table -AUTOSIZE | Out-String -Width 4096<br /> }<br /> ###########################################################################################################################<br /> elseif($args[1] -EQ "jnxtrun") ## Jobs Next Run date and time<br /> {<br /> $srv.jobserver.jobs| where-object {$_.name -like "*" -and $_.isenabled -eq 1 -and $_.nextrundate -ne "1/1/0001 12:00:00 AM" } | sort-object {$_.nextrundate} | select name,currentrunstatus,nextrundate | format-table -AUTOSIZE | Out-String -Width 4096<br /> }<br /> ###########################################################################################################################<br />}<br /> </div>
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-5007740100996005262015-07-14T22:54:00.002-07:002015-07-14T22:54:43.770-07:00Display Transaction Log Size<div dir="ltr" style="text-align: left;" trbidi="on">
Many times I have to perform ad-hoc queries that insert, update or
delete very large amounts of data. For example, a nightly ETL load
failed and I must reload the data manually or deleting large amounts of
data<br />
in batches from a logging table. In such cases I like to keep an eye
out on how full the transaction log is becoming while these ad-hoc
queries are running to ensure that the T-Log doesn't grow out of
control. If needed, I can then pause whatever query is running to allow
log truncation to occur or take a manual log backup.<br />
<br />
The DBCC SQLPERF(logspace) is a nice way to see how full the
transaction log is. But, if you have many databases on the server, it
can be diffcult to find the database you're interested in since the list
isn't sorted (it's actually sorted by database_id, which isn't terribly
helpful). Here is a stored proc that I use that will execute the DBCC
command and return the database list sorted alphabetically. You can also
pass in a search string for the database name and it will return only
those databases that have the search string in their name (sorted by
name). I put this proc in a utility database that I use for functions
and procs that are needed server-wide.<br />
<br />
Usage is very simple:<br />
EXEC dbo.TLogSqlPerf
<br />
This will return all databases sorted alphabetically.<br />
<br />
EXEC dbo.TLogSqlPerf @p_DbName = 'Adventure'<br />
This will return only those databases with "Adventure" in their name (i.e. AdventureWorks2012 and AdventureWorksDW2012)<br />
<br />
<br />
SET ANSI_NULLS ON<br />GO<br />SET QUOTED_IDENTIFIER ON<br />GO<br /><br />IF OBJECT_ID(N'dbo.TLogSqlPerf', N'P') IS NOT NULL DROP PROCEDURE dbo.TLogSqlPerf;<br />GO<br /><br />CREATE PROCEDURE dbo.TLogSqlPerf<br />(<br /> @p_DbName NVARCHAR(200) = ''<br />)<br />AS<br /><br />SET NOCOUNT ON;<br /><br />DECLARE @t TABLE <br />(<br /> DatabaseName NVARCHAR(128),<br /> LogSizeMB DECIMAL(18,5),<br /> LogUsedPct DECIMAL(18,5),<br /> [Status] INT<br />)<br />INSERT INTO @t<br />(<br /> DatabaseName,<br /> LogSizeMB,<br /> LogUsedPct,<br /> [Status]<br />)<br />EXEC sp_executesql N'DBCC SQLPERF(logspace) WITH NO_INFOMSGS'<br /><br />SELECT<br /> DatabaseName,<br /> LogSizeMB,<br /> LogUsedPct,<br /> [Status]<br />FROM @t<br />WHERE DatabaseName LIKE '%' + @p_DbName + '%'<br />ORDER BY DatabaseName<br /><br />RETURN 0;<br /><br />SET NOCOUNT OFF;<br />
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-17864444271971514652015-07-07T06:42:00.000-07:002015-07-07T06:42:09.025-07:00Copying data from one SQL server table to another SQL Server table<div dir="ltr" style="text-align: left;" trbidi="on">
Hi Readers, <br />
I had a requirement where I had to copy data from
one SQL Server located in U.S. (Source Server) to SQL Server located in
India (Destination Server). The copy has to be triggered when data is
inserted at Source Server table. <br />
I encountered various issues
starting from Linked server connection, collation issues to distributed
transaction coordinator issues. <br />
Finally, I have collected all
steps that I followed in my project and steps needed to handle all
issues. And here is my blog talking about them. Feel free to drop any
suggestions or comments for further enhancement of this blog. <br />
<h3>
</h3>
<h2>
Scenario:-</h2>
Let
us assume you have ‘EmployeeDetails’ table present in Source (Local SQL
Server) and Destination Server (‘MyDestinationSQLServer’). With every
Insert in EmployeeDetails table in local server, we want the records to
be inserted in destination server table as well. <br />
<a href="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image001_5F00_401C4A92.png"><img alt="clip_image001" border="0" height="89" src="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image001_5F00_thumb_5F00_4FBEE654.png" style="background-image: none; border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="clip_image001" width="285" /></a> <br />
<br />
<h2>
Steps Followed:-</h2>
Majorly, there were below 5 steps that were followed:- <br />
1. Create local login in both source and destination SQL server (Same Credentials to be used). <br />
2. Create Linked Servers. Link your Source Server to Destination Server. <br />
3. Enabling Distributed Transaction Coordinator service and properties <br />
4. Handling collation issues if any. <br />
5. Writing stored procedure and trigger for copying data from source server to destination server. <br />
<br />
<h2>
Detailed Steps:-</h2>
We will look into each step in details now:- <br />
<br />
<h3>
1. Create local login. This step should be followed in both source and destination machines.</h3>
a. Login to the SQL Server <br />
b. Go to Server->Security->Logins->New Login (I have used ‘Admin’ and ‘P@ssw0rd’ as credentials.) <br />
<a href="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image003_5F00_062F9857.jpg"><img alt="clip_image003" border="0" height="419" src="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image003_5F00_thumb_5F00_310A8D1A.jpg" style="background-image: none; border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="clip_image003" width="467" /></a> <br />
c. Give the user ‘sysadmin’ server role from Server Roles tab. <br />
<br />
<h3>
2. Create linked server. You will be required to link source server to destination server to copy data.</h3>
a. Run below script from source server (Local server in this blog) to create linked server. <br />
b.
Make sure to update destination server name and Database name.
(Currently used name is ‘MyDestinationSQLServer’ and ‘MyDatabase’)<br />
<div id="codeSnippetWrapper" style="background-color: #f4f4f4; border-bottom: silver 1px solid; border-left: silver 1px solid; border-right: silver 1px solid; border-top: silver 1px solid; cursor: text; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; height: 278px; line-height: 12pt; margin: 20px 0px 10px; overflow: auto; padding-bottom: 4px; padding-left: 4px; padding-right: 4px; padding-top: 4px; text-align: left; width: 101.84%;">
<div id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">USE</span> [master]</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_addlinkedserver @server = N<span style="color: #006080;">'MyDestinationSQLServer'</span>, @srvproduct=N<span style="color: #006080;">''</span>, @provider=N<span style="color: #006080;">'SQLNCLI'</span>, @datasrc=N<span style="color: #006080;">'MyDestinationSQLServer '</span>, @<span style="color: blue;">catalog</span>=N<span style="color: #006080;">'MyDatabase'</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N<span style="color: #006080;">'MyDestinationSQLServer '</span>,@useself=N<span style="color: #006080;">'False'</span>,@locallogin=<span style="color: blue;">NULL</span>,@rmtuser=<span style="color: blue;">NULL</span>,@rmtpassword=<span style="color: blue;">NULL</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N<span style="color: #006080;">'MyDestinationSQLServer '</span>,@useself=N<span style="color: #006080;">'False'</span>,@locallogin=N<span style="color: #006080;">'admin'</span>,@rmtuser=N<span style="color: #006080;">'admin'</span>,@rmtpassword=<span style="color: #006080;">'P@ssw0rd'</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_serveroption @server=N<span style="color: #006080;">'MyDestinationSQLServer'</span>, @optname=N<span style="color: #006080;">'collation compatible'</span>, @optvalue=N<span style="color: #006080;">'false'</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_serveroption @server=N<span style="color: #006080;">'MyDestinationSQLServer'</span>, @optname=N<span style="color: #006080;">'data access'</span>, @optvalue=N<span style="color: #006080;">'true'</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_serveroption @server=N<span style="color: #006080;">'MyDestinationSQLServer'</span>, @optname=N<span style="color: #006080;">'dist'</span>, @optvalue=N<span style="color: #006080;">'false'</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_serveroption @server=N<span style="color: #006080;">'MyDestinationSQLServer'</span>, @optname=N<span style="color: #006080;">'pub'</span>, @optvalue=N<span style="color: #006080;">'false'</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_serveroption @server=N<span style="color: #006080;">'MyDestinationSQLServer'</span>, @optname=N<span style="color: #006080;">'rpc'</span>, @optvalue=N<span style="color: #006080;">'false'</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_serveroption @server=N<span style="color: #006080;">'MyDestinationSQLServer'</span>, @optname=N<span style="color: #006080;">'rpc out'</span>, @optvalue=N<span style="color: #006080;">'false'</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_serveroption @server=N<span style="color: #006080;">'MyDestinationSQLServer'</span>, @optname=N<span style="color: #006080;">'sub'</span>, @optvalue=N<span style="color: #006080;">'false'</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_serveroption @server=N<span style="color: #006080;">'MyDestinationSQLServer'</span>, @optname=N<span style="color: #006080;">'connect timeout'</span>, @optvalue=N<span style="color: #006080;">'0'</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_serveroption @server=N<span style="color: #006080;">'MyDestinationSQLServer'</span>, @optname=N<span style="color: #006080;">'collation name'</span>, @optvalue=<span style="color: blue;">null</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_serveroption @server=N<span style="color: #006080;">'MyDestinationSQLServer'</span>, @optname=N<span style="color: #006080;">'lazy schema validation'</span>, @optvalue=N<span style="color: #006080;">'false'</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_serveroption @server=N<span style="color: #006080;">'MyDestinationSQLServer'</span>, @optname=N<span style="color: #006080;">'query timeout'</span>, @optvalue=N<span style="color: #006080;">'0'</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_serveroption @server=N<span style="color: #006080;">'MyDestinationSQLServer'</span>, @optname=N<span style="color: #006080;">'use remote collation'</span>, @optvalue=N<span style="color: #006080;">'true'</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">EXEC</span> master.dbo.sp_serveroption @server=N<span style="color: #006080;">'MyDestinationSQLServer'</span>, @optname=N<span style="color: #006080;">'remote proc transaction promotion'</span>, @optvalue=N<span style="color: #006080;">'true'</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">GO</pre>
</div>
</div>
c. After the linked server is created successfully. Test the Connection
<br />
Go to Server -> Server Objects -> Linked Servers -> MyDestinationSQLServer -> Right Click -> Test Connection.
<br />
You will see below popup when linked server connection test is successful.
<br />
<a href="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image005_5F00_4040F5E7.png"><img alt="clip_image005" border="0" height="148" src="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image005_5F00_thumb_5F00_02476529.png" style="background-image: none; border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="clip_image005" width="327" /></a>
<br />
<br />
<h3>
3. Distributed Transaction Coordinator service should
be running. This step should be followed in both source and destination
server machines.</h3>
a. Go To Services.msc.
<br />
b. Start DTC Service
<br />
c. Set Startup Type to Automatic
<br />
<a href="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image007_5F00_1492BC9C.jpg"><img alt="clip_image007" border="0" height="72" src="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image007_5F00_thumb_5F00_6AB24866.jpg" style="background-image: none; border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="clip_image007" width="567" /></a>
<br />
<br />
<h3>
4. Allow DTC in windows firewall. This step should be followed in both source and destination server machines.</h3>
a. Go to control panel -> Window Firewall -> Allow an app or feature through Windows Firewall
<br />
b. Scroll down to DTC feature
<br />
c. Enable domain and private communication
<br />
<a href="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image008_5F00_35A849E7.png"><img alt="clip_image008" border="0" height="80" src="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image008_5F00_thumb_5F00_708F7CB0.png" style="background-image: none; border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="clip_image008" width="560" /></a>
<br />
<br />
<h3>
5. Update local DTC security settings. This step should be followed in both source and destination server machines.</h3>
a. Go to Control Panel -> Administrative Tools -> Component Services
<br />
b. Expand Component Services -> Computers -> My Computer -> Distributed Transaction Coordinator -> Local DTC.
<br />
<a href="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image009_5F00_305BF3E7.png"><img alt="clip_image009" border="0" height="244" src="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image009_5F00_thumb_5F00_71F63033.png" style="background-image: none; border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="clip_image009" width="381" /></a>
<br />
c. Right click -> Properties -> Security tab
<br />
d. Enable network DTC access, allow inbound and outbound
communication with no authentication required as shown in screenshot
below:-
<br />
<a href="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image011_5F00_4815BBFE.jpg"><img alt="clip_image011" border="0" height="380" src="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image011_5F00_thumb_5F00_356352F8.jpg" style="background-image: none; border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="clip_image011" width="346" /></a>
<br />
<br />
<h3>
6. Resolve Collation issues.</h3>
I had different collations in columns of source table and destination
table in actual production scenario. Source and Destination Servers
were having different collations as mentioned below
<br />
Source Server Collation: SQL_Latin1_General_CP1_CI_AI
<br />
Destination Server Collation: Latin1_General_CI_AI
<br />
So while creating stored procedure, it was failing on where clause in equal to operator with below error:-
<br />
<span style="color: red;">Cannot resolve the collation conflict
between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the
equal to operation.</span>
<br />
To resolve issue on specific columns ‘COLLATE DATABASE_DEFAULT’
has to be added when comparing columns. Something like shown below<br />
<div id="codeSnippetWrapper" style="background-color: #f4f4f4; border-bottom: silver 1px solid; border-left: silver 1px solid; border-right: silver 1px solid; border-top: silver 1px solid; cursor: text; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 20px 0px 10px; overflow: auto; padding-bottom: 4px; padding-left: 4px; padding-right: 4px; padding-top: 4px; text-align: left; width: 97.5%;">
<div id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">INNER</span> <span style="color: blue;">JOIN</span> [Mydatabase].[dbo].EmployeeDetails SourceTable <span style="color: blue;">ON</span> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">DestTable.EmpId <span style="color: blue;">COLLATE</span> DATABASE_DEFAULT = SourceTable.EmpId <span style="color: blue;">COLLATE</span> </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">DATABASE_DEFAULT <span style="color: blue;">where</span> SourceTable.EmpId = @SourceEmpId</pre>
</div>
</div>
<h3>
7. Write Script to copy data from one server to another. </h3>
In this blog, we are considering a simple example of
‘EmployeeDetails’ table with majorly 5 columns – Unique Id, Employee Id,
Name, Address and Phone Number
<br />
<a href="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image0011_5F00_4499BBC5.png"><img alt=" " border="0" height="123" src="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image0011_5F00_thumb_5F00_348D7DBF.png" style="background-image: none; border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" width="394" /></a>
<br />
a. Script to create table is given below<br />
<div id="codeSnippetWrapper" style="background-color: #f4f4f4; border-bottom: silver 1px solid; border-left: silver 1px solid; border-right: silver 1px solid; border-top: silver 1px solid; cursor: text; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 20px 0px 10px; overflow: auto; padding-bottom: 4px; padding-left: 4px; padding-right: 4px; padding-top: 4px; text-align: left; width: 97.5%;">
<div id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">SET</span> ANSI_NULLS <span style="color: blue;">ON</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">SET</span> QUOTED_IDENTIFIER <span style="color: blue;">ON</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">GO</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">CREATE</span> <span style="color: blue;">TABLE</span> [dbo].[EmployeeDetails](</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">[Id] [<span style="color: blue;">int</span>] <span style="color: blue;">IDENTITY</span>(1,1) <span style="color: blue;">NOT</span> <span style="color: blue;">NULL</span>,</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">[EmpId] [nvarchar](50) <span style="color: blue;">NOT</span> <span style="color: blue;">NULL</span>,</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">[Name] [nvarchar](50) <span style="color: blue;">NOT</span> <span style="color: blue;">NULL</span>,</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">[Address] [nvarchar](50) <span style="color: blue;">NOT</span> <span style="color: blue;">NULL</span>,</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">[Phone] [nvarchar](50) <span style="color: blue;">NOT</span> <span style="color: blue;">NULL</span>,</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">CONSTRAINT</span> [PK_EmployeeDetails] <span style="color: blue;">PRIMARY</span> <span style="color: blue;">KEY</span> <span style="color: blue;">CLUSTERED</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">(</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">[EmpId] <span style="color: blue;">ASC</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">)<span style="color: blue;">WITH</span> (PAD_INDEX = <span style="color: blue;">OFF</span>, STATISTICS_NORECOMPUTE = <span style="color: blue;">OFF</span>, IGNORE_DUP_KEY = <span style="color: blue;">OFF</span>, </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">ALLOW_ROW_LOCKS = <span style="color: blue;">ON</span>, ALLOW_PAGE_LOCKS = <span style="color: blue;">ON</span>) <span style="color: blue;">ON</span> [<span style="color: blue;">PRIMARY</span>]</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">) <span style="color: blue;">ON</span> [<span style="color: blue;">PRIMARY</span>]</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">GO</pre>
</div>
</div>
b. Stored Procedure to copy data from Source Server table to Destination Server EmployeeDetails table.
<br />
Below stored procedure will give you collation issue while
running as collation of column EmpId in Source and Destination table was
different<br />
<div id="codeSnippetWrapper" style="background-color: #f4f4f4; border-bottom: silver 1px solid; border-left: silver 1px solid; border-right: silver 1px solid; border-top: silver 1px solid; cursor: text; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 20px 0px 10px; overflow: auto; padding-bottom: 4px; padding-left: 4px; padding-right: 4px; padding-top: 4px; text-align: left; width: 97.5%;">
<div id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">Create</span> <span style="color: blue;">Procedure</span> [dbo].[InsertEmployeeDetails]</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">@SourceEmpId nvarchar(10)</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">AS</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">declare</span> @DestEmpExists <span style="color: blue;">int</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">Select</span> @DestEmpExists = <span style="color: blue;">Count</span>(*) <span style="color: blue;">from</span> </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">[MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails <span style="color: blue;">where</span> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">EmpId = @SourceEmpId</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">If</span> @DestEmpExists = 0</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">BEGIN</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">PRINT</span> <span style="color: #006080;">'Inserting Employee Information in Destination Employee Table'</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">Insert <span style="color: blue;">Into</span> </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">[MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails (EmpId, </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">Name, Address, Phone)</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">Select</span> EmpId, Name, Address, Phone <span style="color: blue;">from</span> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">[MyDatabase].[dbo].EmployeeDetails <span style="color: blue;">where</span> EmpId = @SourceEmpId</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">END</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">ELSE</span> <span style="color: blue;">If</span> @DestEmpExists = 1</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">BEGIN</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">PRINT</span> <span style="color: #006080;">'Updating Employee Information in Destination Employee Table'</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">Update</span> [MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">SET</span> Name = SourceTable.Name, Address = SourceTable.Address, Phone = </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">SourceTable.Phone</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">FROM</span> [MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails DestTable</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">INNER</span> <span style="color: blue;">JOIN</span> [MyDatabase].[dbo].EmployeeDetails SourceTable <span style="color: blue;">ON</span> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">DestTable.EmpId = SourceTable.EmpId <span style="color: blue;">where</span> SourceTable.EmpId = @SourceEmpId</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">END</pre>
</div>
</div>
Error received was
<br />
<span style="color: red;">Msg 468, Level 16, State 9, Procedure InsertEmployeeDetails, Line 28</span>
<br />
<span style="color: red;">Cannot resolve the collation conflict
between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the
equal to operation.</span>
<br />
<a href="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image013_5F00_3F4D65C5.jpg"><img alt="clip_image013" border="0" height="63" src="http://blogs.technet.com/cfs-file.ashx/__key/communityserver-blogs-components-weblogfiles/00-00-00-79-57-metablogapi/clip_5F00_image013_5F00_thumb_5F00_0EB9E80D.jpg" style="background-image: none; border-bottom: 0px; border-left: 0px; border-right: 0px; border-top: 0px; display: inline; padding-left: 0px; padding-right: 0px; padding-top: 0px;" title="clip_image013" width="617" /></a>
<br />
To resolve this issue, ‘COLLATE DATABASE_DEFAULT’ was added when
comparing columns. Below is the updated stored procedure with collation
issues fixed.<br />
<div id="codeSnippetWrapper" style="background-color: #f4f4f4; border-bottom: silver 1px solid; border-left: silver 1px solid; border-right: silver 1px solid; border-top: silver 1px solid; cursor: text; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 20px 0px 10px; overflow: auto; padding-bottom: 4px; padding-left: 4px; padding-right: 4px; padding-top: 4px; text-align: left; width: 97.5%;">
<div id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">Create</span> <span style="color: blue;">Procedure</span> [dbo].[InsertEmployeeDetails]</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">@SourceEmpId nvarchar(10)</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">AS</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">declare</span> @DestEmpExists <span style="color: blue;">int</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">Select</span> @DestEmpExists = <span style="color: blue;">Count</span>(*) <span style="color: blue;">from</span> </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">[MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails <span style="color: blue;">where</span> EmpId = </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">@SourceEmpId</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">If</span> @DestEmpExists = 0</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">BEGIN</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">PRINT</span> <span style="color: #006080;">'Inserting Employee Information in Destination Employee Table'</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">Insert <span style="color: blue;">Into</span> [MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">(EmpId, Name, Address, Phone)</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">Select</span> EmpId, Name, Address, Phone <span style="color: blue;">from</span> </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">[MyDatabase].[dbo].EmployeeDetails <span style="color: blue;">where</span> EmpId = @SourceEmpId</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">END</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">ELSE</span> <span style="color: blue;">If</span> @DestEmpExists = 1</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">BEGIN</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">PRINT</span> <span style="color: #006080;">'Updating Employee Information in Destination Employee Table'</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">Update</span> [MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">SET</span> Name = SourceTable.Name, Address = SourceTable.Address, Phone = </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">SourceTable.Phone</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">FROM</span> [MyDestinationSQLServer].[MyDatabase].[dbo].EmployeeDetails DestTable</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">INNER</span> <span style="color: blue;">JOIN</span> [MyDatabase].[dbo].EmployeeDetails SourceTable <span style="color: blue;">ON</span> </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">DestTable.EmpId <span style="color: blue;">COLLATE</span> DATABASE_DEFAULT = SourceTable.EmpId <span style="color: blue;">COLLATE</span> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">DATABASE_DEFAULT <span style="color: blue;">where</span> SourceTable.EmpId = @SourceEmpId</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">END</pre>
</div>
</div>
c. Trigger created on EmployeeDetails table in Source server.<br />
<div id="codeSnippetWrapper" style="background-color: #f4f4f4; border-bottom: silver 1px solid; border-left: silver 1px solid; border-right: silver 1px solid; border-top: silver 1px solid; cursor: text; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 20px 0px 10px; overflow: auto; padding-bottom: 4px; padding-left: 4px; padding-right: 4px; padding-top: 4px; text-align: left; width: 97.5%;">
<div id="codeSnippet" style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"> </pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">Create</span> <span style="color: blue;">TRIGGER</span> [dbo].[Trg_InsertEmployeeDetails] </pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">ON</span> [dbo].[EmployeeDetails]</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">AFTER</span> INSERT, <span style="color: blue;">UPDATE</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">AS</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">BEGIN</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">SET</span> XACT_ABORT <span style="color: blue;">ON</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">BEGIN</span> <span style="color: blue;">DISTRIBUTED</span> <span style="color: blue;">TRANSACTION</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">--1. <span style="color: blue;">Declare</span> variables</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">declare</span> @InsertedEmpId nvarchar(50)</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">--2. <span style="color: blue;">Fetch</span> <span style="color: blue;">values</span> <span style="color: blue;">from</span> <span style="color: blue;">Table</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">Select</span> @InsertedEmpId = EmpId <span style="color: blue;">from</span> inserted</pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">exec</span> InsertEmployeeDetails @SourceEmpId = @InsertedEmpId</pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">COMMIT</span> <span style="color: blue;">TRANSACTION</span></pre>
<pre style="background-color: white; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;"><span style="color: blue;">SET</span> XACT_ABORT <span style="color: blue;">OFF</span></pre>
<pre style="background-color: #f4f4f4; border-bottom-style: none; border-left-style: none; border-right-style: none; border-top-style: none; color: black; direction: ltr; font-family: 'Courier New', courier, monospace; font-size: 8pt; line-height: 12pt; margin: 0em; overflow: visible; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; text-align: left; width: 100%;">END</pre>
</div>
</div>
Hope this will help you. Suggestion and Feedback is welcome.
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-24994806944308665782015-07-04T07:12:00.002-07:002015-07-04T07:12:21.550-07:00Table Partitioning<div dir="ltr" style="text-align: left;" trbidi="on">
* It is a new feature introduced in SQL Server 2005.<br />
* While working with large tables, generally we face the following problems<br />
* Less performance.<br />
* Pressure increases on the hardware where the table is created.<br />
* Index size also increases.<br />
* We have to rebuild or reorganize the complete index always. <br />
<div>
* To overcome all these problems we can divide the table horizontally into more than on part. </div>
<div>
This process is called table partitioning.<br />
* Dividing the table into segments horizontally is table partitioning.<br />
* Each part is called partition. All the partitions consists of same structure.<br />
* Each partition can be stored in different files/filegroups.<br />
* One of a performance improvement technique.<br />
* We can split the table into parts depending on some column values.</div>
<div>
<br />
</div>
<div>
Steps</div>
<div>
1) Define a partition function<br />
* Here we will mention the ranges of partitions.<br />
syn:<br />
CREATE PARTITION FUNCTION <function_Name> (data type) <br />
AS RANGE LEFT/RIGHT FOR VALUES (V1,V2,....) </div>
<div>
* Here, data type is the column's type which is used to implement partitions.<br />
i.e. partition key data type.</div>
<div>
LEFT: <br />
Specifies that partition values will be less than or equal to the values defined in the Partition Function.</div>
<div>
RIGHT: <br />
Specifies that partition values will be less than the values defined in the Partition Function.</div>
<div>
EX:</div>
<div>
CREATE PARTITION FUNCTION MyPartitionRange (INT) <br />
AS RANGE LEFT FOR VALUES (1,2) </div>
<div>
This functions allows to create 3 partitions.<br />
First partition consists of values <=1<br />
Sec >1 and <=2<br />
Third >2</div>
<div>
2) Define a partition scheme from the partition function<br />
<br />
* It defines the locations for partitions. i.e. which partition should be maintain in which file group.<br />
* We can maintain each partition in a seperate filegroup or all partitions in a single file group.</div>
<div>
syn:<br />
<br />
CREATE PARTITION SCHEME <name> AS <br />
PARTITION <partition_functionname><br />
ALL TO (fg1,fg2,.........) </div>
<div>
3) Create the partition Table</div>
<div>
CREATE TABLE <tname><br />
( <br />
colname type,<br />
PartCol type <br />
) <br />
ON<br />
<partition_schemename> (PartCol) <br />
EX:<br />
Step1: Creating partition Function.</div>
<div>
CREATE PARTITION FUNCTION MyPartitionRange (INT) <br />
AS RANGE LEFT FOR VALUES (1,2) </div>
<div>
Step2: Creating partition scheme.</div>
<div>
CREATE PARTITION SCHEME MyPartitionScheme AS <br />
PARTITION MyPartitionRange <br />
ALL TO ([PRIMARY]) </div>
<div>
Step3: Creating partition table</div>
<div>
CREATE TABLE MyPartitionedTable <br />
( <br />
i INT , <br />
s CHAR(8000) , <br />
PartCol INT <br />
) <br />
ON<br />
MyPartitionScheme (PartCol) </div>
<div>
Step4: To display partition details</div>
<div>
SELECT * FROM sys.partitions<br />
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')</div>
<div>
Step5: Inserting sample rows</div>
<div>
INSERT MyPartitionedTable (i, s, PartCol) SELECT 1, 'a', 1<br />
INSERT MyPartitionedTable (i, s, PartCol) SELECT 2, 'a', 2<br />
INSERT MyPartitionedTable (i, s, PartCol) SELECT 3, 'a', 2<br />
INSERT MyPartitionedTable (i, s, PartCol) SELECT 4, 'a', 3<br />
INSERT MyPartitionedTable (i, s, PartCol) SELECT 5, 'a', 3<br />
INSERT MyPartitionedTable (i, s, PartCol) SELECT 6, 'a', 3<br />
INSERT MyPartitionedTable (i, s, PartCol) SELECT 7, 'a', 4</div>
<div>
Step6: Verifying the partitions</div>
<div>
SELECT *<br />
FROM sys.partitions<br />
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')</div>
<div>
Step7: To find the given value falls in which partition</div>
<div>
SELECT PartitionNo = $partition.MyPartitionRange(6)</div>
<div>
<br />
</div>
<div>
What Operations Can Be Performed on Partitioned Data<br />
---------------------------------------------------------------------<br />
1. SPLIT: Insert a boundary in an existing partition to create a new partition.</div>
<div>
* If the size of a partition becomes very large then we can split it into two partitions.<br />
syn:<br />
ALTER PARTITION FUNCTION <function_name>() split RANGE (value)<br />
Ex:<br />
ALTER PARTITION FUNCTION MyPartitionRange () split RANGE (3)</div>
<div>
select * <br />
FROM sys.partitions<br />
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')</div>
<div>
<br />
</div>
<div>
2. SWITCH: Swap a populated table or partition with an empty table or partition.<br />
--------<br />
Step1:</div>
<div>
select * into MyNewPartition from MyPartitionedTable where 1=3</div>
<div>
--copying rows present in 3rd partition into MyNewPartition</div>
<div>
ALTER TABLE MyPartitionedTable switch PARTITION 3 TO MyNewPartition</div>
<div>
--STEP3:<br />
select * from MyNewPartition</div>
<div>
--step4: Now 3rd partition becomes empty.</div>
<div>
SELECT *<br />
FROM sys.partitions<br />
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')</div>
<div>
<br />
</div>
<div>
--Copying Rows present in a table into required partion</div>
<div>
Step1:</div>
<div>
select * into MyNewPartition from MyPartitionedTable where 1=3<br />
<br />
Step2: Copy rows present in non-partitioned table into 4th partition</div>
<div>
ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 4</div>
<div>
Note1:<br />
* MyNewPartition should consists of check constraint which allows the respective partition range.<br />
* It should be there on same filegroup where the respective partition is present.</div>
<div>
<br />
select * from mynewpartition</div>
<div>
<br />
ALTER TABLE OrdersOctober2004 SWITCH TO Orders PARTITION 24<br />
GO</div>
<div>
<br />
3. MERGE: Combine two adjacent partitions into a single partition.<br />
---------------------------------------------------------------</div>
<div>
* If the no of rows in the partitions are less then if we want then we can merge into single partition.</div>
<div>
CREATE PARTITION FUNCTION myRangePF1 (int)<br />
AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );<br />
GO</div>
<div>
ALTER PARTITION FUNCTION myRangePF1 ()<br />
MERGE RANGE (100);</div>
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0tag:blogger.com,1999:blog-2290483146096291131.post-112254008211130932015-07-04T07:11:00.003-07:002015-07-04T07:11:44.945-07:00Recovery Models in SQL Server <div dir="ltr" style="text-align: left;" trbidi="on">
Database Recovery models plays an important role in the data recovery
and high availability possibilities in SQL Server. Complete behaviour of
Transaction Log file of a database depends on recovery models. The
following features of Transaction Log depends on recovery models of
database.<br />
<br />
1. What is recorded in Transaction Log File.<br />
2. Which types of backups are possible.<br />
3. When the Transaction Log file is truncated.<br />
4. Log shipping , Database mirroring are possible or not.<br />
5. Point in time recovery is possible or not.<br />
<br />
<br />
<ul>
<li>SQL Server supports 3 types of recovery models</li>
<ul>
<li>Full</li>
<li>Bulk Logged</li>
<li>Simple</li>
</ul>
</ul>
We can set the recovery model of database as follows<br />
USE MASTER<br />
GO<br />
ALTER DATABASE <dbName> SET RECOVERY <FULL/BULK_LOGGED/SIMPLE><br />
<br />
We can check the recovery model of database from sysdatabases or sys.databases view of master database.
</div>
Anonymoushttp://www.blogger.com/profile/03732698092158397093noreply@blogger.com0