How to find the registration number that sums up to your lucky number ?

DECLARE @StartNum INT, @EndNum INT, @Ones INT = 0, @Tens INT = 0, @Hundreds INT = 0, @Thousands INT = 0, @Sum INT, @LuckyNumber INT

-- Please set your inputs here. Both StartNum & EndNum Should be <= 4 digit number & Lucky Number between 1 to 9
-- Parameter Section BEGIN
SET @StartNum = 1
SET @EndNum = 9999
SET @LuckyNumber = 1
-- Parameter Section  END

DECLARE @Numbers TABLE
(
Number INT,
DigitSum INT
)

WHILE ( @StartNum  <= @EndNum )
    BEGIN
        IF (@StartNum < 10)
            BEGIN
                SET @Ones = @StartNum
            END
           
        ELSE IF (@StartNum >= 10 AND @StartNum < 100)
            BEGIN
                SET @Tens = LEFT(@StartNum,1)
                SET @Ones = RIGHT(@StartNum,1)
            END

        ELSE IF (@StartNum >= 100 AND @StartNum < 1000)
            BEGIN
                SET @Hundreds = LEFT(@StartNum,1)
                SET @Tens = RIGHT(LEFT(@StartNum,2),1)
                SET @Ones = RIGHT(@StartNum,1)
            END
           
        ELSE
            BEGIN
                SET @Thousands = LEFT(@StartNum,1)
                SET @Hundreds = RIGHT(LEFT(@StartNum,2),1)
                SET @Tens = RIGHT(LEFT(@StartNum,3),1)
                SET @Ones = RIGHT(@StartNum,1)
            END
   
    SET @Sum = @Thousands + @Hundreds + @Tens + @Ones
   
        IF (@Sum >= 10)
            BEGIN
                SET @Tens = LEFT(@Sum,1)
                SET @Ones = RIGHT(@Sum,1)
                    INSERT INTO @Numbers
                    SELECT @StartNum, @Tens + @Ones
            END
        ELSE
            BEGIN
                INSERT INTO @Numbers
                SELECT @StartNum, @Thousands + @Hundreds + @Tens + @Ones
            END

    SET @StartNum = @StartNum + 1

    END

SELECT Number FROM @Numbers WHERE DigitSum = @LuckyNumber

Logic to Rebuild Or ReIndex the Indexes Dynamically

SET NOCOUNT ON
DECLARE@Index_Name NVARCHAR(150), @FragPercent FLOAT, @RowCount INT, @ExecCmd NVARCHAR(500) = '', @EntityName NVARCHAR(100)
SET@EntityName = '@TableName'
DECLARE@IndexInfo TABLE (RwNo INT IDENTITY(1,1), Index_ID INT , Index_Name NVARCHAR(150), FragmentationPercentage FLOAT)
INSERTINTO @IndexInfo
SELECTa.index_id, name, avg_fragmentation_in_percent
FROMsys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@EntityName), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.OBJECT_ID = b.OBJECT_ID AND a.index_id = b.index_id
SET
@RowCount = (SELECT COUNT(*) from @IndexInfo)
--SELECT * FROM @IndexInfo
WHILE
( @RowCount > 0)
BEGIN
SELECT@Index_Name = Index_Name, @FragPercent = FragmentationPercentage FROM @IndexInfo WHERE RwNo = @RowCount
SELECT
@ExecCmd = CASE WHEN @FragPercent > 5 AND @FragPercent <= 40 THEN 'ALTER INDEX ' + @Index_Name + ' ON ' + @EntityName + ' REORGANIZE'
WHEN @FragPercent > 40 THEN 'ALTER INDEX ' + @Index_Name + ' ON ' + @EntityName + ' REBUILD'
--ELSE ''
END
EXEC
(@ExecCmd)
SET
@RowCount = @RowCount - 1
END

How to find tables in a database without a clustered index

SELECT TOP 1000 o.name, i.type_desc, o.type_desc, o.create_date
FROM sys.indexes i
INNER JOIN sys.objects o
 ON  i.object_id = o.object_id
WHERE o.type_desc = 'USER_TABLE'
AND i.type_desc = 'HEAP'
ORDER BY o.name
GO

Custom SSIS Components

For Additional Custom SSIS Components Please refer this link:

http://www.cozyroc.com/products

2. Creating Projects and Data Sources

Function to Split String into a Table

CREATE FUNCTION [dbo].[splitstring_to_table]
(
@string NVARCHAR(1000),
@delimiter CHAR(1)
)
RETURNS @output TABLE(
data NVARCHAR(256)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1
BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (data)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END
RETURN
END

Limitations Found in SSIS (till 2008 R2)

1. Flat File Connection Manager: A Column of Length 4000 characters or less than it in a Flat File is only allowed while configuring the Flat File Connection Manager.

2. keep watching to know more..

How to Re-Index All the tables in a DB ?

Use this in a stored procedure:

DECLARE @TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'Reindexing ' + @TableName + ' table'

DBCC DBREINDEX (@TableName, ' ', 80)
FETCH NEXT FROM cur_reindex INTO @TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex

Reading / Writing Data From / To SharePoint Lists Using SSIS

Pre-Requisite:

Since the Sharepoint List related SSIS components are not available in the standard SSIS toolset one has to add these dataflow components to the toolbox by adding a reference (a DLL) available at http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652 (Please consider the Beta version as it is the latest one).

Note: we have two sets of SharePointListAdaptersSetup.msi, one for 2005 and the other for 2008 and above versions.

How to Configure?

Once after downloading the (.msi) file. Run the executable and provide the installation path where the rest of the (.dll’s) are placed (which is nothing but the directory you used for installing the BI Components while installing SQL Server) because the editor fails to identify the components if they are installed to some other directory.

If you are not aware of the directory details, prior running the .msi, open one of your package in your SSIS project, Open Dataflow task, Right click on the tool box and select Choose Items…

 

Path gives you the absolute path info where the rest of the SSIS components are installed. Use the same path as the destination for your .msi. After proper installation your SharePoint list components appears in the list as shown above.

How to Read the Data from Sharepoint List:

1.       Create a new connection under connection managers. Provide proper credentials.

 

2.       Open the SharePoint List Source and select the connection you have just created.

 

3.       Under Component Properties provide the CamlQuery, SiteListName & SiteURL as shown below:

        4.  Map the columns using column Mappings tab

 

How to Write the Data to Sharepoint List:

1.       Steps 1 & 2 (creation of new connection and using it) are same as above.

      2.    Select Batch Type as Modification (for Insertion & Updation) & Deletion for Deletes. Provide the SiteListName & SiteURL as shown below:

      3.       Map the columns using column Mappings tab. Ignore ID attribute mapping if it is an insertion case. Use it only for Updation Scenario.

CAML - Basics

The Collaborative Application Markup Language (CAML) is an XML-based query language that helps you querying, building and customizing Web sites based on Windows SharePoint Services.

Example:

<Query>  

   <Where>

    <And>

        <And>

           <Eq>

             <FieldRef Name='LastName' />

             <Value Type='Text'>Janssens</Value>

           </Eq>

           <Geq>

             <FieldRef Name='Age' />

             <Value Type='Number'>21</Value>

           </Geq>

        </And>

        <Lt>

          <FieldRef Name='Age' />

          <Value Type='Number'>60</Value>
        </Lt>

    <And>

   </Where>

</Query> 

How to Build CAML?

It is an XML-based query language. Its root element is Query. Within the Query element two other elements are possible but not mandatory: the OrderBy element and the Where element.

The OrderBy element is the simplest one. It is used to sort the returning list items. You have to specify the field(s) on which you want to sort the items and the sort direction. The syntax looks as follows:

<OrderBy>

   <FieldRef Name='LastName' Ascending='False' />

</OrderBy>

If you omit the Ascending attribute, your resulting rows will be sorted in ascending order. If you want to order in descending order you have to specify Ascending=’False’.

The GroupBy clause is used for grouping:

<GroupBy>

      <FieldRef Name="Modified"/>

</GroupBy> 

<Where>

   <Or>

      <Neq>

          <FieldRef Name="Status"></FieldRef>

          <Value Type="Text">Completed</Value>

      </Neq> 

      <IsNull>

          <FieldRef Name="Status"></FieldRef>

      </IsNull> 

   </Or>

</Where>

The Where clause is used to specify one or more filter criteria. In its most simple form you specify an operator, a field name for which you want to specify a criterion, and a value.

<Where>

   <Eq>

     <FieldRef Name='LastName' />

      <Value Type='Text'>Janssens</Value>

  </Eq>

</Where>

Fields

The FieldRef element can be any field of the list on which you want to execute the CAML query. If you use the Name attribute you need to specify the internal name of the field. But you can also use the ID attribute to specify the Guid of the field.

Value

The Value element specifies the value part of the criterion. The attribute Type is optional and specifies the data type of the field you want to specify the criterion for. If omitted the data type is considered as being Text. In all other cases you have to specify the Type attribute.

If the field type is a Lookup you need to specify the text value. For example you have an Employees list and the Country field is a lookup field referring to the Countries list. In that case an employee living in Belgium will have f.e. following value: #15; Belgium. If you have to query for the employees living in Belgium you will have to write your query as follows:

<Where>

    <Eq>

     <FieldRef Name='Country' />

     <Value Type='Lookup'>Belgium</Value>

  </Eq>

</Where>

You can find this not good coding practice because the name of the country can change in time. In that case you can also query on the id of the country specifying the LookupId attribute in the FieldRef element:

<Where>

  <Eq>

     <FieldRef Name='Country' LookupId='True' />
     <Value Type='Lookup'>15</Value>
  </Eq>
</Where> 

Escape sequence:

The ScriptEncode method escapes characters that would otherwise conflict with script.

Original	Replacement
"	\"
\	\\
+	\u002b
>	\u003e
<	\u003c
'	\u0027

 Example:

<FieldRef Name=\"Checkbox\"></FieldRef> <Value Type=\"bit\">1</Value> 

Few of the commonly used operators list: 

Operator	Description
Eq	Equals (=)
Neq	Not Equals ( <>)
Gt	Greater than (>)
Geq	Greater or Equal to (>=)
Lt	Less than (<)
Leq	Less than or Equal to (<=)
IsNull / IsNotNull	Null  / Not Null
BeginsWith	Begins With Word
Contains	Like
And	Used within the ‘Where’ element to group filters in a query for a view
Or	Used within the ‘Where’ element to group filters in a query for a view
DateRangesOverlap	compare the dates in a recurring event with a specified DateTime value, to determine whether they overlap

Example for View:

<View>

 <Query>

      <OrderBy>

          <FieldRef Name='ID'/>

      </OrderBy>

  <Where>

  <Or>

     <Geq>

         <FieldRef Name='Field1'/>

         <Value Type='Number'>1500</Value>

     </Geq>

     <Leq>

        <FieldRef Name='Field2'/><Value Type='Number'>500</Value>

     </Leq>

   </Or>

  </Where>

 </Query>

   <ViewFields>

        <FieldRef Name='Title'/>

        <FieldRef Name='Name'/>

   </ViewFields>

   <RowLimit>10</RowLimit>

</View>

Example: DateRangesOverlap

<where> 

     <DateRangesOverlap>

          <FieldRef Name="EventDate"></FieldRef>

          <FieldRef Name="EndDate"></FieldRef>

          <FieldRef Name="RecurrenceID"></FieldRef>

            <Value Type="DateTime">

              <Now/>  

            </Value>

</DateRangesOverlap>

</Where>

Example: Begins With / Contains 

<Where>

   <And>

        <BeginsWith>

               <FieldRef Name="Conference"/>

               <Value Type="Note">Morning</Value>

        </BeginsWith>

       <Contains>

               <FieldRef Name="Conference" />

               <Value Type="Note">discussion session</Value>

       </Contains>

   </And>

</Where>

Building Queries for working with DateTime Values

Filtering on DateTime fields also requires some extra attention. First of all when querying for a specific date, you have to use the SharePoint datetime notation:

<Where>

   <Ge>

        <FieldRef Name="StartDate" />

        <Value Type="DateTime">2008-08-10T10:00:00Z</Value>

   </Ge>

</Where>

But in this case the date is hard coded and the time part will not be taken into account. This query will return all list items with a start date as of 10 August 2008, also those starting before 10 o’clock. If you want your query to take into account the time part, you have to use a special attribute IncludeTimeValue that you can set on the FieldRef element:

<Where>

   <Ge>

        <FieldRef Name="StartDate" IncludeTimeValue="TRUE" />

       <Value Type="DateTime">2008-08-10T10:00:00Z</Value>

   </Ge>

</Where>

This query will return all list items with a start date as of 10 August 10 o’clock.

As already said, this way the date is hard coded. If you want your query a bit more dynamic, you can always use the element Today.

<Where>

   <Ge>

        <FieldRef Name="StartDate" />

        <Value Type="DateTime"><Today /></Value>

   </Ge>

</Where>

Today will not take a time part into account. You can also add or subtract a number of days from today’s date. In that case you have to add the Offset attribute to the Today element. The Offset attribute accepts a positive value for adding days and a negative value for subtracting days.

 <Where>

   <Ge>

        <FieldRef Name="StartDate" />

        <Value Type="DateTime"><Today Offset="10" /></Value>

   </Ge>

</Where>

Retrieving List Items with CAML using the SharePoint Object Model

If you need to retrieve items from a list when developing web parts, application pages or custom field types you can best use the SPQueryobject from the SharePoint object model. This object is located in the Microsoft.SharePoint namespace of the Microsoft.SharePoint.dll located in the Global Assembly Cache.

Instantiate the object as follows:

SPQuery qry = new SPQuery();

The most important property is the Query property, which needs to be set to your CAML query:

string camlquery = "<OrderBy><FieldRef Name='Country' /></OrderBy><Where>"

    + "<Eq><FieldRef Name='LastName' /><Value Type='Text'>Smith</Value></Eq>"

    + </Where>";

qry.Query = camlquery;

At this point you can execute the query on your list:

SPListItemCollection listItemsCollection = list.GetItems(qry);

A small remark with the GetItems method of the SPList instance: this method returns a collection of type SPListItemCollection. It is possible that it is easier working with a DataTable. In that case you can execute the query as follows:

DataTable listItemsTable = list.GetItems(qry).GetDataTable();

The query will not only return all list items that have their last name set to Smith, but also all columns of each list item. In cases you work with large lists it can be important to retrieve a subset of list items containing only the columns you need. In that case you will have to set the ViewFields property of the SPQuery object. You can do this by specifying all columns you want to see returned:

qry.ViewFields = "<FieldRef Name='FirstName' /><FieldRef Name='LastName' />";

This will return the first name and the last name of the retrieved employees, but also the system fields like the ID and the Created date.

The major disadvantage of the SPQuery object is that you can query only one list. If you want to query more than one list you will have to use the SPSiteDataQuery.

We can also retrieve the List Items from List.asmx (Web Service).