Excel data to data table using Aspos

http://www.aspose.com/docs/display/cellsnet/Exporting+Data+from+Worksheets

using Aspose.Words;

using Aspose.Cells;

using Aspose.Cells.Charts;

using Aspose.Cells.Tables;

No Object Exist with Lineage ID of XXXX in SSIS

Please click below link
1) when you migrate your SSIS package to SQL higher version
2) place square brackets around column name in SUBSTRING function

see below link

click here



done.

Simple C# code to get Quarters

 public static DateTime GetQuarterStartDate()  
     {  
       DateTime date = DateTime.Today;  
       int quarterNumber = ((date.Month - 1) / 3) + 1;  
       return new DateTime(date.Year, (quarterNumber - 1) * 3 + 1, 1);  
     }  

empty toolbox in ssis, there are no usable controls in this group.

1) If you open Integration Service Project solution and you can see below

2) right click on your package and select SSIS toolbox

 

thanks

svchost.exe process consume CPU memory with "winrscmde" description

If  you feel your computer is very slow.  Some time you get BLUE screen in your Windows 7 OS.
Please check following things:
1) hit Ctrl + Shift + Esc to open task manager.
2) Click "Processes" tab.
3) Sort by "Memory" > higher memory first
4) check if any services running with "svchost.exe" image & "winrscmde" as description
and conusming lots of CPU memory.


Solution:

download TDSKiller.exe from CNET > Install & Scan your computer


http://download.cnet.com/Kaspersky-TDSSKiller/3000-2239_4-75722087.html 


More details:

http://www.bleepingcomputer.com/forums/topic436219.html
http://forums.spybot.info/showthread.php?t=66455




thx

select column in PDF, Vertical test select in pdf

hold Alt key and then select PDF column it will select vertical.

Cannot add an entity with a key that is already in use, LINQ

-- When you get above error there are two possibility

1) you are trying to insert duplicate primary key.
2) when you have SQL column with data type = "Uniqueidentifier" and default set to 'NewID()'
    that means its auto-generated field.

   Make sure in you DBML file that tale > field > properties > "Auto Generated Value"  is set to true
   default is fault

Operand type clash: int is incompatible with uniqueidentifier

When you get above error please double check following

- you are inserting into table from view
- table has column with data type - uniqueidentifier  with allow null
- view is returning null for uniqueidentifier field
- you have to convert null to uniqueidentifier field in view 

for example ,convert(uniqueidentifier,null) as MyUniqueIdentifierField  

Autogenerate get and set class properties

1) use  notepad ++ to generate class private properties
2)
You can right-click on the field and go to Refactor > Encapsulate Field. That will generate a public Property. You still have to do each one at a time but it's a lot faster than the typing!

sqlbulkcopy with transaction

string connstring = _hsconnstring.Substring(1, (_myconnstring.Length - 1));
using (SqlConnection destinationConnection = new SqlConnection((connstring)))
{
    destinationConnection.Open();

    using (SqlTransaction transaction = destinationConnection.BeginTransaction())
                {

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection,SqlBulkCopyOptions.Default,transaction))
                    {
                        try
                        {
                            bulkCopy.DestinationTableName = "myfirsttable";
                            bulkCopy.WriteToServer(outTable1);
                           
                            bulkCopy.DestinationTableName = "mysecondtable";
                            bulkCopy.WriteToServer(outTable2);

                            bulkCopy.DestinationTableName = "mythirdtable";
                            bulkCopy.WriteToServer(outTable3);

                            bulkCopy.DestinationTableName = "myfouthtable";
                            bulkCopy.WriteToServer(outTable4);
                           
                            bulkCopy.DestinationTableName = "myfifthtable";
                            bulkCopy.WriteToServer(outTable5);
                            bulkCopy.Close();
                            transaction.Commit();

                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            Utilities.HandleError("Error at data bulk insert: ", ex);
                            throw ex;
                        }

                    }
                }

            }

number of current open sql session using query

--SP
exec sp_who




--QUERY
SELECT
    DB_NAME(dbid) as DBName,
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE
    dbid > 0
GROUP BY
    dbid, loginame
    order by DB_NAME(dbid)

SQL create split function and how to use function in sql

ALTER FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))      
returns @temptable TABLE (items varchar(8000))      
as      
begin      
    declare @idx int      
    declare @slice varchar(8000)      
     
    select @idx = 1      
        if len(@String)<1 br="br" is="is" nbsp="nbsp" null="null" or="or" return="return" tring="tring">     
    while @idx!= 0      
    begin      
        set @idx = charindex(@Delimiter,@String)      
        if @idx!=0      
            set @slice = left(@String,@idx - 1)      
        else      
            set @slice = @String      
         
        if(len(@slice)>0) 
            insert into @temptable(Items) values(@slice)      
 
        set @String = right(@String,len(@String) - @idx)      
        if len(@String) = 0 break      
    end  
return      
end 
GO


--- How to call the function

select * from [dbm].[Split] ('L196,L201,L197',',')

good blog about to write design doc

http://blog.slickedit.com/2007/05/how-to-write-an-effective-design-document/

good sites for travel planning

http://travel.nytimes.com/2012/01/08/travel/19-web-sites-for-travel-savings-in-2012.html

send email through SQL SP

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'WNweb01',
@recipients='DL@abc.com',
@body = @mbody,
@subject = 'my subject here'

learning site for school students

http://www.khanacademy.org/

this assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded, powershell

see below link. we have to create two config file.

http://tfl09.blogspot.com/2010/08/using-newer-versions-of-net-with.html

xml DeSerialization xsd.exe

good article

-------

http://blogs.msdn.com/b/yojoshi/archive/2011/05/14/xml-serialization-and-deserialization-entity-classes-with-xsd-exe.aspx

below is simple steps using VS 2010

1) open XML file that you want to deserialize in VS 2010

2) from VS 2010 menu > XML > Create Schema

3) It will create schema in you project save somewhere this .xsd file (in project)

4) go to VS command prompt

5) change path to your .xsd file (where you save above .xsd file)

6) type : xsd myschemafile.xsd /c (must you /c)

7) it will generate myschemafile.cs file in same location

8) type following code

using (FileStream xmlStream = new FileStream(@"C:\book.xml", FileMode.Open))

{

using (XmlReader xmlReader = XmlReader.Create(xmlStream))

{

XmlSerializer serializer = new XmlSerializer(typeof(catalog));

catalog deserializedStudents = serializer.Deserialize(xmlReader) as catalog;

foreach (var b in deserializedStudents.book )

{

Console.WriteLine("author : {0}", b.author);

Console.WriteLine("description : {0}", b.description);

Console.WriteLine("genre : {0}", b.genre);

Console.WriteLine("price : {0}", b.price);

Console.WriteLine("");

}

}

}

generate classes from XSD file

To generate the classes from XSD, run the following on the command line

LinqToXsd.exe /Myschema.xsd

Note: download above exe from codeplex

http://linqtoxsd.codeplex.com/

serialization - Deserialization example

public class MySampleClass

{

//class fields

public string mystring = "Hello World";

public int myint = 1234;

public string[] mystrarray = new string[4];

private int myprivateint = 4321;

//public property

private string _mynameproperty;

public string MyNameProperty

{

get { return _mynameproperty; }

set { _mynameproperty = value; }

}

//constructor

public MySampleClass()

{ }

//public method

public string MyMethod()

{

return _mynameproperty;

}

}

public partial class Window1 : Window

{

public Window1()

{

InitializeComponent();

//Serialization exmaple

//serialization is the process of saving class member data into XML

//only public properties and fields can be serialized

MySampleClass objmysampleClass = new MySampleClass();

objmysampleClass.MyNameProperty = "This is Prashant Rathod";

objmysampleClass.mystrarray[0] = "adfsasf";

objmysampleClass.mystrarray[1] = "546345";

objmysampleClass.mystrarray[2] = "uoafas";

objmysampleClass.mystrarray[3] = "34672";

XmlSerializer myxmlserializer = new XmlSerializer(typeof(MySampleClass));

StreamWriter mystreamwriter = new StreamWriter(@"c:\MysampleSerializeXMLfile.xml");

myxmlserializer.Serialize(mystreamwriter, objmysampleClass);

mystreamwriter.Close();

//Deserialization is the reverse process – loading class member data from an XML document

MySampleClass objfromXMLfile = new MySampleClass();

XmlSerializer myserializer = new XmlSerializer(typeof(MySampleClass));

FileStream filestream = new FileStream(@"c:\MysampleSerializeXMLfile.xml", FileMode.Open);

objfromXMLfile = (MySampleClass)myserializer.Deserialize(filestream);

Console.WriteLine(objfromXMLfile.mystring);

Console.WriteLine(objfromXMLfile.MyNameProperty);

Console.WriteLine(objfromXMLfile.myint);

Console.WriteLine(objfromXMLfile.mystrarray);

}

}