Finding missing sequence in mysql server

select t1.eventlogid + 1 as missing_id
from ka_eventlognew1 t1
left join ka_eventlognew1 t2 on t2.eventlogid = t1.eventlogid + 1
where t2.eventlogid is null;

select t1.eventlogid - 1 as missing_id
from ka_eventlognew1 t1
left join ka_eventlognew1 t2 on t2.eventlogid = t1.eventlogid - 1
where t2.eventlogid is null;

split fn mn and ln

SELECT
   SUBSTRING_INDEX(SUBSTRING_INDEX(displaypart, ' ', 1), ' ', -1) AS first_name,
   If(  length(displaypart) - length(replace(displaypart, ' ', ''))>1,
       SUBSTRING_INDEX(SUBSTRING_INDEX(displaypart, ' ', 2), ' ', -1) ,'')
           as middle_name,
   SUBSTRING_INDEX(SUBSTRING_INDEX(displaypart, ' ', 3), ' ', -1) AS last_name
FROM tfsusers1

Protect excel with password

Here is the VBA Code to protect an excel sheet with the password

Please make sure that the libraries have interop dll .
Microsoft.Office.Interop.Excel.dll

Module Module1

    Sub Main()


     
        Dim strfilename As String = "c:\mssql\DW_Reporting\Templates\dwreport.xls"
        Dim strnewfilename As String = "c:\MSSQL\SSIS\dwReport\protected\DWDailyReport.xls"

        Dim strPassword As String = "pwd"


        Dim objExcel As New Microsoft.Office.Interop.Excel.Application

        Dim objWorkbook As Microsoft.Office.Interop.Excel.Workbook

        objWorkbook = objExcel.Workbooks.Open(strFilename)

        objExcel.DisplayAlerts = False

        objWorkbook.SaveAs(strnewfilename, Password:=strPassword)

        objWorkbook.Close()

   
    End Sub

Difference between SQL Server 2005/2008/2012

SQL Server 2005 to 2008
• Transparent Data Encryption. The ability to encrypt an entire database.
• Backup Encryption. Executed at backup time to prevent tampering.
• External Key Management. Storing Keys separate from the data.
• Auditing. Monitoring of data access.
• Data Compression. Fact Table size reduction and improved performance.
• Resource Governor. Restrict users or groups from consuming high levels or resources.
• Hot Plug CPU. Add CPUs on the fly.
• Performance Studio. Collection of performance monitoring tools.
• Installation improvements. Disk images and service pack uninstall options.
• Dynamic Development. New ADO and Visual Studio options as well as Dot Net 3.
• Entity Data Services. Line Of Business (LOB) framework and Entity Query Language (eSQL)
• LINQ. Development query language for access multiple types of data such as SQL and XML.
• Data Synchronizing. Development of frequently disconnected applications.
• Large UDT. No size restriction on UDT.
• Dates and Times. New data types: Date, Time, Date Time Offset.
• File Stream. New data type VarBinary(Max) FileStream for managing binary data.
• Table Value Parameters. The ability to pass an entire table to a stored procedure.
• Spatial Data. Data type for storing Latitude, Longitude, and GPS entries.
• Full Text Search. Native Indexes, thesaurus as metadata, and backup ability.
• SQL Server Integration Service. Improved multiprocessor support and faster lookups.
• MERGE. TSQL command combining Insert, Update, and Delete.
• SQL Server Analysis Server. Stack improvements, faster block computations.
• SQL Server Reporting Server. Improved memory management and better rendering.
• Microsoft Office 2007. Use OFFICE as an SSRS template. SSRS to WORD.
SQL Server 2008 to SQL Server R2
• Backup compression is available in R2 standard (was only in Enterprise)
• SQL Server 2008 R2 Express Edition database size limit increased to 10 GB (from 4GB)
• PowerPivot for SharePoint introduced for loading, querying, and managing PowerPivot workbooks that you publish to a SharePoint 2010 server
• PowerPivot for Excel is used to assemble and create relationships in large amounts of data from different sources, and then use that data as the basis for PivotTables and other data visualization objects that support data analysis in Excel.
• Utility Explorer introduced for centralized management of managed SQL instances.
• Master Data Services introduced to manage master (reference) data and maintain an auditable record of that data as it changes over time.
• Enhanced security to prevent an authentication relay attack.
SQL Server 2008R2 to 2012
• Inclusion of Visual Studio 2010 (BI edition)
• AlwaysOn technology – allowing automatic replication and switching to mirrored database/server in the event of problems
• In most instances, the performance is 10 times faster than the predecessor (Microsoft claim)
• Buffer rate is high in SQL Server 2012 because of data compression.
• Data visualization tool is available in SQL Server 2012.This allows snapshots of data.
• Support for persistent computed columns and extra geographical approach is possible with spatial features in SQL Server 2012.
• Allows for warm Service Patch patching – meaning SQL or server does not have to be rebooted/restarted to apply patches
• New ‘Contained Databases’ feature allowing easier movement of database instances between servers
• New ‘ColumnStore’ indexes – which turn indexes 90 degrees in use (instead of one index per row, you get multiple column indexes per row which has a major impact on performance)
• New TRY_CONVERT T-SQL command, which will perform data type conversion, but will NULL rather than error where conversion fails.
• New OFFSET/FETCH command which allows automatic capture of pages of rows (like SELECT TOP 90 * from…, but gives a starting row number for the page)
• Redesign of SQL Server management studio – providing new features, better intellisense, new viewing tools and cleaner interface
• Custom user server roles – meaning you can define roles with specific access (such as BI data reader) and assign these custom roles to defined users (therefore faster user maintenance)
• New virtual Windows FileTable commands in T-SQL lets you manage a folder like a table of documents, but still have external control over the contents: UPDATE C:\Docs\*.* SET ReadOnly = 1 WHERE Author = ‘Bob’ AND Created < ’20100101′;)
• NEGATIVE – SQL Server Management Studio will no longer run on XP. Needs Windows Vista, 7 or 8.
• NEGATIVE – SQL Server is no longer licenced on server, but either CALS or Cores in server CPU which could increase costs for large user connection scenarios.
• NEGATIVE – AWE is no longer supported, meaning your SQL Server instance on x86 with 32GB of RAM is only going to be able to use 4GB. Note – SQL now designed to run on 64bit hardware.

Copy data from one mysql server to another using SSIS

create a user in mysql

CREATE USER 'admin'@'%' IDENTIFIED BY '@dm1n';
GRANT ALL TO 'admin'@'%'
FLUSH PRIVILEGES;

Create 3 Variables MaxID ,MinID and Variable


















Now add for loop,execute sql task and DFT




In Data flow task Define the source connection and Destination Connection.Use ADO.Net Connection managers for both.

In For Loop Type the number of times you want your loop to execute





















 In First Execute SQL task editor add the destination connection and Insert this in SQL Command

 set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES'


In Second execute SQL task set the properties as



 
 Right click the data flow task and go to properties ->expressions ->ADO NET Source 1 command

"SELECT  EventLogID, ApiKey, ApiToken, UserID, EventDate, EventTypeID, EventCategoryID, Message, Data, CreatedDate, UpdatedDate FROM  ka_eventlog
   WHERE     EventLogID between '"+ (DT_WSTR,15) @[User::minid]+"'  AND '"+ (DT_WSTR,15) @[User::maxid]+"'"


Connect to destination Connection

Make sure you set your connection manager's properties by right clicking them and setting as

 Retain Same connection true
and
Delay Validation true