Advertisements

Archive

Archive for the ‘SQL Server’ Category

Sql Server useful commands

Coming…

Advertisements
Categories: SQL, SQL Server Tags: ,

Performance Tuning in SSIS for Denodo ADO Net Source

Assumption: You have Denodo driver installed on server. (I had used “DenodoODBC Unicode” version 9.03.04.00, when I created User DSN)

Below steps helped me import two million rows (6 columns with mostly int data) data in less than 4 minutes. Earlier it used to take more than one and half hour.

Here trick is to change User DSN settings. :):):)

  • Click “Start” and type-in c:\Windows\SysWOW64\odbcad32.exe and hit enter in Search Programs and File to open “ODBC Data Source Administrator”
  • In “ODBC Data Source Administrator”, in “User DSN” tab select appropriate Data source from “User Data Sources” list. Click “Configure”.
  • Above step will open “ODBS Driver setup” window, Click “Datasource” button
  • do below changes in “Advanced Options” window
    1. on Page 1
      1. in “Data Type Options”, un-select “Text as LongVarchar”
      2. in Miscellaneous, change “Chunk Size” from 100 to 75000
    2. on Page 3
      1. in “The Use of LIBPQ library”, change selection from “unspecified” to ”yes” , click Apply, Click Ok.

I hope this helps.

Logging by Triggers in SQL Server

We can alternatively use SQL Server feature CDC (Change Data Capture), but here I am providing one more way of doing it (custom solution).

Db Objects: (two tables and two triggers)

  1. TargetTableName: Table whose data we want to log
  2. LogTableName: Table where data will be logged. This table structure will be same as TargetTableName, and additionally this table will have three extra columns namely
    • [log_action_type]
    • [log_action_date]
    • [log_action_user]
    • [log_action_hostname]
  3. tl_TargetTableName: Trigger to log DML changes (Update and Delete only)
  4. tu_LogTableName: Trigger to avoid Delete or Update operations operations on logging table
 
use dbname
go

alter table LogTableName add [log_action_type] 	[char](1) not null;
alter table LogTableName add [log_action_date] 	[datetime] not null default getdate();
alter table LogTableName add [log_action_user] 	[varchar](50) null;
alter table LogTableName add [log_action_hostname] 	[varchar](50) default host_name();

go

IF OBJECT_ID ('dbo.tu_LogTableName ','TR') IS NOT NULL
   DROP TRIGGER dbo.tu_LogTableName;
GO
-- =============================================
-- Author:		Umesh Patil	
-- Create date: 2014/08/26
-- Description:	Restirct Delete or Update operations operations on logging table
-- =============================================
CREATE TRIGGER dbo.tu_LogTableName  
   ON  dbo.LogTableName 
   FOR DELETE,UPDATE
AS 
BEGIN
	RAISERROR ('Can not perform Delete or Update operations on UserTableName table, either disable/delete the trigger',16,1);
	ROLLBACK TRANSACTION;
	RETURN;
END
GO

use dbname
go

IF OBJECT_ID ('dbo.tl_TargetTableName','TR') IS NOT NULL
   DROP TRIGGER dbo.tl_TargetTableName;
GO
-- =============================================
-- Author:		Umesh Patil	
-- Create date: 2014/08/26
-- Description:	Log DML changes (Update and Delete only)
-- =============================================
CREATE TRIGGER dbo.tl_TargetTableName 
   ON  dbo.TargetTableName
   FOR DELETE,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	DECLARE @ActionType char(1)

	IF EXISTS (SELECT 1 FROM deleted)
		BEGIN
			IF EXISTS(SELECT 1 FROM inserted)
				SET @ActionType='U'
			ELSE
				SET @ActionType='D'
		END

	IF  @ActionType='U' OR  @ActionType='D'
		INSERT INTO [dbo].[TargetTableName]
           	([PrimaryKey_Column]
		  --include all columns
		,[log_action_type]
		,[log_action_date]
		,[log_action_user]
		  ,[log_action_hostname])
		select		
			[PrimaryKey_Column]
			--include all columns
			,@ActionType as [log_action_type]
			,getdate() as [log_action_date]
			,suser_sname() as [log_action_user]
			,host_name() as [log_action_hostname]
		from deleted
END
GO
Categories: SQL, SQL Server, T-SQL Tags: , ,

PowerShell for Copy Database

March 25, 2013 13 comments

This is PowerShell tutorial script to understand how to copy existing database with PowerShell and SMO. We will be using Transfer Class from SMO. If you wish to just generate the copy script then there is an option to just script out the transfer using the ScriptTransfer method. When we are ready to bring the data and schema over, we can use the TransferData method. Read more…

SQL Sever – PowerShell for Executing a SQL Query on Multiple Servers

March 14, 2013 13 comments

This is 50th Article on Bi-BigData.com and we are getting great response from the community. This article will be covering how to use PowerShell and SMO to connect multiple SQL Server instances and execute a pre defined SQL command against all of them. This is applicable when we have same database available on multiple SQL Server Instances where you want to execute same SQL query. We are going to use the Invoke-Sqlcmd cmdlet to achieve our mission. Read more…

PowerShell tutorial Part 1- Poweshell for Creating View

March 10, 2013 5 comments

Powershell Tutorial Part 1 – Powershell for Creating View

This article I’ll explain how to create SQL Server View using PowerShell and SMO. This PowerShell Script is compatible with PowerShell V3.0.

I’ll be using Production.ProductCategory, Production.ProductSubCategory and Production.Product tables to create view. Following t-SQL script is equivalent script which we will write in PowerShell later. Read more…

%d bloggers like this: