SQL

1. Copy table with data from one SQL to another:

1. Create linked server entry on target server. This link should to point on source server

2. Use the following query (run it on target server):

select * into targetTable from [sourceserver].[sourcedatabase].[dbo].[sourceTable]

2. Frame and count number of occurrences of particular value in table

1. Table tbl_Test has two columns: id (unique, primary key) and data (non-unique values)

select tbl_Test.data, row_number() over (partition by data order by data)

from dbo.tbl_Test

3. Select data from Linked Server

INSERT INTO pbxrecords

SELECT * FROM [PBX]...cdr

where calldate <= CONVERT(char(10),GETDATE()-1,111) + ' 23:59:59'

and calldate > (select MAX(calldate) from pbxrecords)

This code is a part of integration between Asterisk (MySQL) switchboard and SSRS (SQL Server Reporting Services). I run this stored procedure daily (scheduled job), appending data to pbxrecords table

4. The following stored procedure shows last login date of all enabled users in "domain_name" domain

USE [IT]

GO

/****** Object: StoredProcedure [dbo].[it001_Enabled_Users_Last_Logon] Script Date: 09/29/2012 15:36:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Igor Margulyan

-- Create date: 06.04.2011

-- Description: Control report shows login date of all enabled users in domain

-- exec it001_Enabled_Users_Last_Logon

-- =============================================

ALTER PROCEDURE [dbo].[it001_Enabled_Users_Last_Logon]

AS

BEGIN

-- http://support.microsoft.com/kb/269181

-- The object is called userAccountControl and it is assigned a value as per below:

-- 512 - Enabled Account

-- 514 - Disabled account

-- 544 - Account Enabled - Require user to change password at next logon

-- 66048 - Password never expires

-- 262656 - Smart Card Logon Required

-- 66048 = Enabled, password never expires

-- 66050 = Disabled, password never expires

select

case when ISNULL(NumberOfDaysSince01011601,0) > 0 then

dateadd(dd, NumberOfDaysSince01011601 - 146097, '2001-01-01')

else 0 end LogonDate, sAMAccountName LoginName

, givenName + ' ' + sn as EmployeeName

, userAccountControl AccountStatus

from (

select lastlogon, ((convert(bigint,lastlogon)/60)/60)/24/1000/1000/10

as NumberOfDaysSince01011601, sAMAccountName, givenName, sn, userAccountControl

from (

select * from OPENQUERY(ADSI,

'SELECT displayName, sAMAccountName, givenName, sn

, lastlogon, userAccountControl

FROM

''LDAP://DC=domain_name,DC=org'' WHERE objectCategory=''user'' ')) a ) b

WHERE userAccountControl <> 514 and userAccountControl <> 66050

-- and sAMAccountName = 'smt'

order by 1 DESC

END

5. Select with OPENQUERY

set @sqlstr = 'select *

from OPENQUERY(' + rtrim(@crmserver) + ',''' + rtrim(@mysqlstr) + ''')'

exec(@sqlstr)

6. User defined Table Types (by Corina Pavel)

1. You can create user defined table type as following:

2. Then you create nice stored procedure which gets data from your ERP (or any other source) and puts it in the variable of previously defined type.

USE [parpar]

GO

/****** Object: StoredProcedure [FRC].[frc001_GetSalesOrders] Script Date: 09/29/2012 15:51:56 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--EXEC FRC.frc001_GetSalesOrders

ALTER PROCEDURE [FRC].[frc001_GetSalesOrders]

AS

BEGIN

DECLARE @SalesHardOrders AS FRC.SalesForecast;

INSERT INTO @SalesHardOrders

SELECT Q.[Year]

, Q.[Month]

, Q.Customer

, Q.Item

, CASE WHEN LTRIM(RTRIM(Q.Item)) LIKE 'TOOLING%' THEN 'Tooling'

WHEN LTRIM(RTRIM(Q.Item)) LIKE 'TL[0-9][0-9][0-9][0-9]%'THEN 'Tooling'

WHEN LTRIM(RTRIM(Q.Item)) LIKE 'NRE%' THEN 'NRE'

ELSE 'Product' END AS ProductCategory

, 3 ForecastType

, ISNULL(SUM(Q.OrderQty),0) AS OrderedQty

, ISNULL(SUM(Q.DelQty),0) AS DeliveredQty

, ISNULL(SUM(Q.DelUSDValue),0) AS DeliveredValue

, 100 Probability

, ISNULL(SUM(Q.USDAmount),0) AS USDAmount

, ISNULL(SUM(Q.USDAmount),0)/NULLIF(SUM(Q.OrderQty),0) ASAvgPrice

FROM

(

SELECT YEAR(CASE WHEN sls045.t_invn = 0 THEN sls041.t_ddta ELSEsls045.t_ddat END) AS [Year]

, MONTH(CASE WHEN sls045.t_invn = 0 THEN sls041.t_ddta ELSEsls045.t_ddat END) AS [Month]

, LTRIM(RTRIM(sls040.t_cuno)) AS Customer

, RTRIM(LTRIM(sls045.t_item)) AS Item

, sls045.t_oqua AS OrderQty

, sls045.t_dqua AS DelQty

, sls045.t_dqua * (sls045.t_pric * sls045.t_rats_2) AS DelUSDValue

, (t_amnt * sls045.t_rats_2) USDAmount

FROM BAANSRV2.baandb.dbo.ttdsls045105 sls045

LEFT OUTER JOIN BAANSRV2.baandb.dbo.ttdsls041105 sls041

ON sls045.t_pono = sls041.t_pono

AND sls045.t_orno = sls041.t_orno

INNER JOIN BAANSRV2.baandb.dbo.ttdsls040105 sls040

ON sls040.t_orno = sls045.t_orno

WHERE sls040.t_cotp NOT IN ('PTH','HBB')

AND (CASE WHEN sls045.t_invn = 0 THEN sls041.t_ddta ELSEsls045.t_ddat END)

BETWEENCONVERT(DATETIME,CONVERT(NVARCHAR(4),YEAR(GETDATE()))+ '0101')

AND DATEADD(dd, -DAY(DATEADD(mm,1,GETDATE())), DATEADD(mm,13,GETDATE()))

) Q

GROUP BY Q.[Year]

, Q.[Month]

, Q.Customer

, Q.Item

HAVING SUM(Q.OrderQty)<>0;

EXEC [FRC].[frc002_UpdHardOrders] @SalesHardOrders;

END

3. Next you merge the data in user defined table variable with data in one of your table as following

USE [parpar]

GO

/****** Object: StoredProcedure [FRC].[frc002_UpdHardOrders] Script Date: 09/29/2012 15:55:26 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [FRC].[frc002_UpdHardOrders]

(@SalesHardOrders FRC.SalesForecast READONLY)

AS

BEGIN

BEGIN TRY

BEGIN TRANSACTION

MERGE INTO FRC.SalesOrdersAndForecast AS TRG

USING @SalesHardOrders AS SRC

ON TRG.[Year] = SRC.[Year]

AND TRG.[Month] = SRC.[Month]

AND LTRIM(RTRIM(TRG.Customer)) = LTRIM(RTRIM(SRC.Customer))

AND LTRIM(RTRIM(TRG.Item)) = LTRIM(RTRIM(SRC.Item))

AND TRG.ForecastType = SRC.ForecastType

WHEN MATCHED THEN

UPDATE

SET OrderedQty = SRC.OrderedQty

,USDAmount= SRC.USDAmount

,DeliveredQty = SRC.DeliveredQty

,DeliveredValue = SRC.DeliveredValue

,USDPrice = SRC.AvgPrice

,LastUpdOn = GETDATE()

,LastUpdBy = ORIGINAL_LOGIN()

,VersionNo = TRG.VersionNo + 1

WHEN NOT MATCHED BY TARGET THEN

INSERT ([Year],[Month],Customer,Item,ProductType,ForecastType

,OrderedQty,USDAmount,USDPrice,Probability,DeliveredQty,DeliveredValue)

VALUES(SRC.[Year],SRC.[Month],SRC.Customer,SRC.Item,SRC.ProductCategory,SRC.ForecastType

,SRC.OrderedQty,SRC.USDAmount,SRC.AvgPrice,SRC.Probability,

SRC.DeliveredQty,SRC.DeliveredValue)

WHEN NOT MATCHED BY SOURCE THEN

DELETE;

COMMIT TRANSACTION

END TRY

BEGIN CATCH

DECLARE @ErrMessage NVARCHAR(400),

@ErrSeverity INT,

@ErrState INT,

@ErrLine INT;

SELECT @ErrMessage = ERROR_MESSAGE(),@ErrSeverity = ERROR_SEVERITY(),

@ErrState = ERROR_STATE(), @ErrLine = ERROR_LINE();

IF XACT_STATE() = 1 OR XACT_STATE() = -1

ROLLBACK TRANSACTION;

INSERT INTO dbo.EventsLog (EventDate,EventType,HostName,AppName,SysUser,ObjectName,ErrorNumber

,ErrorLine,EventDescription)

VALUES(GETDATE(),'i',HOST_NAME(),APP_NAME(),ORIGINAL_LOGIN(),ERROR_PROCEDURE(),ERROR_NUMBER()

,ERROR_LINE(),ERROR_MESSAGE());

RAISERROR('Msg Description: %s Line: %d',@ErrSeverity,@ErrState,@ErrMessage,@ErrLine)

END CATCH;

END;

7. Database Triggers

USE [Database_Name]

GO

/****** Object: DdlTrigger [triTriggerName] Script Date: 09/29/2012 17:38:49 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [trigTriggerName]

ON DATABASE

WITH EXECUTE AS CALLER

FOR DDL_DATABASE_LEVEL_EVENTS

AS

BEGIN

DECLARE @AuditDate DATETIME,

@UserLogin SYSNAME,

@UserName SYSNAME,

@ServerName SYSNAME,

@DatabaseName SYSNAME,

@SchemaName SYSNAME,

@ObjectType SYSNAME,

@ObjectName SYSNAME,

@EventType SYSNAME,

@TSQLStatement NVARCHAR(MAX),

@XMLEventData XML;

SET @AuditDate = GETDATE();

SET @UserLogin = SUSER_SNAME();

SET @XMLEventData = EVENTDATA();

SET @UserName = @XMLEventData.value('data(/EVENT_INSTANCE/UserName)[1]','NVARCHAR(128)');

SET @ServerName = @XMLEventData.value('data(/EVENT_INSTANCE/ServerName)[1]','NVARCHAR(128)');

SET @DatabaseName = @XMLEventData.value('data(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)');

SET @SchemaName = @XMLEventData.value('data(/EVENT_INSTANCE/SchemaName)[1]','NVARCHAR(128)');

SET @ObjectType = @XMLEventData.value('data(/EVENT_INSTANCE/ObjectType)[1]','NVARCHAR(128)');

SET @ObjectName = @XMLEventData.value('data(/EVENT_INSTANCE/ObjectName)[1]','NVARCHAR(128)');

SET @EventType = @XMLEventData.value('data(/EVENT_INSTANCE/EventType)[1]','NVARCHAR(128)');

SET @TSQLStatement = @XMLEventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','NVARCHAR(MAX)');

INSERT INTO AuditTrail.SchemaAudit ( AuditDate, UserLogin, UserName, ServerName, DatabaseName, SchemaName,

ObjectType, ObjectName, EventType, TSQLStatement, XMLEventData )

VALUES ( @AuditDate, @UserLogin, @UserName, @ServerName, @DatabaseName, @SchemaName,

@ObjectType, @ObjectName, @EventType, @TSQLStatement, @XMLEventData )

END;

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

DISABLE TRIGGER [trigTriggerName] ON DATABASE

GO

ENABLE TRIGGER [trigTriggerName] ON DATABASE

GO

The following trigger does not allow to delete (DROP) tables in the database

USE [CAPA]

GO

/****** Object: DdlTrigger [trigNoTableDeletion] Script Date: 09/29/2012 17:55:07 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [trigNoTableDeletion]

ON DATABASE

WITH EXECUTE AS CALLER

FOR DROP_TABLE

AS

BEGIN

PRINT 'You cannot delete tables from this database'

ROLLBACK;

END;

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

DISABLE TRIGGER [trigNoTableDeletion] ON DATABASE

GO

8. Prepare Audit Table

USE [Traceability]

GO

/****** Object: Table [dbo].[TblSerials] Script Date: 02/11/2013 09:56:39 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[TblSerials.Audit](

[Cust] [nchar](10) NOT NULL,

[serial] [int] NOT NULL,

[Action] NVARCHAR(10) NOT NULL CONSTRAINT CK_ACTIONCHECK([Action] In ('Deleted','Updated')),

RowType NVARCHAR(10) NOT NULL CONSTRAINT CK_ROWTYPE CHECK(RowType in ('New','Old','Deleted')),

ChangedDate DATETIME NOT NULL Default GETDATE(),

ChangedBy NVARCHAR(50) NOT NULL DEFAULT SUSER_SNAME()

CONSTRAINT [PK_TblSerials.Audit] PRIMARY KEY CLUSTERED

(

[Cust] ASC,

[serial] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

9. Send email from SQL server

USE msdb

GO

EXEC sp_send_dbmail @profile_name='FirstProfile',

@recipients='user@company.com',

@subject='Hello, this is test message from trigger from SQL server',

@body='This is the body of the test message.

Congrates Database Mail Received By you Successfully.

This email message was sent from Table trigger'

10. Cursor for all columns in all Tables

USE [Test14032013]

GO

/****** Object: StoredProcedure [dbo].[TestCursor] Script Date: 03/14/2013 09:18:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Igor Margulyan

-- Create date: 14.03.2013

-- Description:

-- exec TestCursor

-- =============================================

ALTER PROCEDURE [dbo].[TestCursor]

AS

BEGIN

DECLARE @table_name as varchar(100)

DECLARE tbl_cursor CURSOR FOR

SELECT TABLE_NAME FROM information_schema.tables

OPEN tbl_cursor

FETCH NEXT FROM tbl_cursor INTO @table_name

WHILE @@FETCH_STATUS = 0 -- ok

BEGIN

SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(@table_name)

FETCH NEXT FROM tbl_cursor INTO @table_name

END

-- close curcor

CLOSE tbl_cursor

DEALLOCATE tbl_cursor

END