DateTime startTime, endTime;
startTime = Convert.ToDateTime("2:30 AM");
endTime = Convert.ToDateTime("3:30 AM");
var timeDiff = new TimeSpan(endTime.Ticks - startTime.Ticks);
MessageBox.Show("Time difference in hours is " + timeDiff.Hours);
Sharing knowledge does not lessen your store, often it gets you more.
Success doesn't happen overnight and patience is key to living your dream life.
Success is a journey not a destination
Monday, December 13, 2010
SSIS: Column cannot convert between unicode and non-unicode string data types
Scenario
If you try to import Excel data into SQL Server you may get this error having to convert the columns from Unicode to non-Unicode.
Solution
Use the Data Conversion Task
So to get around this problem we have to also use a Data Conversion task. This will allow us to convert data types so we can get the import completed. Map the non-unicode columns with unicode version of the columns and import the data to SQL server.
If you try to import Excel data into SQL Server you may get this error having to convert the columns from Unicode to non-Unicode.
Solution
Use the Data Conversion Task
So to get around this problem we have to also use a Data Conversion task. This will allow us to convert data types so we can get the import completed. Map the non-unicode columns with unicode version of the columns and import the data to SQL server.
Saturday, December 11, 2010
SSIS: Foreach File Enumerator in Foreach Loop Container with dynamic folder path using variables
Setting the folder path in foreach loop container dynamically using a variable.
This can be done by setting the Directory property of the Foreach File Enumerator.
1. Create two variables named @FolderPath and @FileName.
2. Drag a Foreach Loop Container task to the Control Flow panel, double click it to pop up the property window.
3. Switch to the Collection tab, choose the Enumerator as "Foreach File Enumerator", expand Expressions and add two properties "Directory" for folder path and "FileSpec" for filtering specific types of files. Specify "@[User::FolderPath]" to Directory and "*.txt" to FileSpec.
4. Switch to the Variable Mappings and specify User::FileName mapping to the index 0.
This can be done by setting the Directory property of the Foreach File Enumerator.
1. Create two variables named @FolderPath and @FileName.
2. Drag a Foreach Loop Container task to the Control Flow panel, double click it to pop up the property window.
3. Switch to the Collection tab, choose the Enumerator as "Foreach File Enumerator", expand Expressions and add two properties "Directory" for folder path and "FileSpec" for filtering specific types of files. Specify "@[User::FolderPath]" to Directory and "*.txt" to FileSpec.
4. Switch to the Variable Mappings and specify User::FileName mapping to the index 0.
Monday, December 06, 2010
Convert YYYYMM Integer Date to Datetime and retrieve month name
SELECT DATENAME(MONTH, DATEADD(mm,(((YYYYMM/100)-1900)*12)+(YYYYMM%100)-1,0)) AS MonthName FROM
Sunday, December 05, 2010
NET4 Client Profile vs NET4 Full Framework?
NET4 Client Profile:
Always target NET4 Client Profile for all your client desktop applications (including Windows Forms and WPF apps).
NET4 Full framework:
Target NET4 Full only if the features or assemblies that your app need are not included in the Client Profile. This includes:
If you are building Server apps. Such as:
If you use legacy client scenarios. Such as:
If you targeting developer scenarios and need tool such as MSBuild or need access to design assemblies such as System.Design.dll
Projects that target NET4 Client Profile by default
The following projects starting with VS 2010 target the NET4 Client Profile by default:
Windows Project (C# and VB)
- WPF Application
- WPF Browser Application
- WPF Custom Control Library
- WPF User Control Library
- Windows Forms Application
- Windows Forms Control Library
- Console Application
- Empty Project
- Window Service
Visual F#
- F# Application
- F# Tutorial
Workflow (C# & VB)
- Activity Designer Library
- Activity Library
- Workflow Console Application
WCF (C# & VB)
- WCF Service Library
Office 2007 & 2010 templates (C# & VB)
- All Projects
All other projects, including Class Library, target the Full Framework by default.
Always target NET4 Client Profile for all your client desktop applications (including Windows Forms and WPF apps).
NET4 Full framework:
Target NET4 Full only if the features or assemblies that your app need are not included in the Client Profile. This includes:
If you are building Server apps. Such as:
- ASP.Net apps
- Server-side ASMX based web services
If you use legacy client scenarios. Such as:
- Use System.Data.OracleClient.dll which is deprecated in NET4 and not included in the Client Profile.
- Use legacy Windows Workflow Foundation 3.0 or 3.5 (WF3.0 , WF3.5)
If you targeting developer scenarios and need tool such as MSBuild or need access to design assemblies such as System.Design.dll
Projects that target NET4 Client Profile by default
The following projects starting with VS 2010 target the NET4 Client Profile by default:
Windows Project (C# and VB)
- WPF Application
- WPF Browser Application
- WPF Custom Control Library
- WPF User Control Library
- Windows Forms Application
- Windows Forms Control Library
- Console Application
- Empty Project
- Window Service
Visual F#
- F# Application
- F# Tutorial
Workflow (C# & VB)
- Activity Designer Library
- Activity Library
- Workflow Console Application
WCF (C# & VB)
- WCF Service Library
Office 2007 & 2010 templates (C# & VB)
- All Projects
All other projects, including Class Library, target the Full Framework by default.
SSIS Ouput Column Names for errors
The "Error Description" custom component for SSIS (SQL Server Integration Services 2005 and 2008) helps to enhance the error flows.
It provides error description, column name and other useful information.
You can connect many inputs to this component to create effective error flows quickly.
The component is available on Codeplex
http://eod.codeplex.com/
Normally SSIS provides only error code and error column where error column provides only the Lineage Id not the actual column name. The above component does the mapping between Lineage ID and Output Column ID and provides the Column name.
It provides error description, column name and other useful information.
You can connect many inputs to this component to create effective error flows quickly.
The component is available on Codeplex
http://eod.codeplex.com/
Normally SSIS provides only error code and error column where error column provides only the Lineage Id not the actual column name. The above component does the mapping between Lineage ID and Output Column ID and provides the Column name.
Friday, December 03, 2010
ORA-12154: TNS:could not resolve service name with .NET Windows Services
When I try to connect to oracle database from a .NET Windows service I get the following error.
ORA-12154: TNS:could not resolve service name
a) Tried fixing the NTFS permissions on the oracle folder for the user account running the service.
b) Checked the tnsnames.ora and sqlnet.ora is available and service name is configured in it.
Still I got the same error. If I try to connect to the oracle database from the same server using .NET Windows Forms or Console application, the connection established successfully.
Solution
Change the connection string to expand out the tns entry:
Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.161.50.101)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MOUAT)));Password=password;User Id=username;
This will give the real error:
And got 'ORA-06413: Connection not open.'
Which was due to the 'Windows Service' path having brackets in it.
Change the connection string to exclude the TNS resolution and then make sure that the path to the calling application does not contain brackets '(' or ')'.
ORA-12154: TNS:could not resolve service name
a) Tried fixing the NTFS permissions on the oracle folder for the user account running the service.
b) Checked the tnsnames.ora and sqlnet.ora is available and service name is configured in it.
Still I got the same error. If I try to connect to the oracle database from the same server using .NET Windows Forms or Console application, the connection established successfully.
Solution
Change the connection string to expand out the tns entry:
Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.161.50.101)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MOUAT)));Password=password;User Id=username;
This will give the real error:
And got 'ORA-06413: Connection not open.'
Which was due to the 'Windows Service' path having brackets in it.
Change the connection string to exclude the TNS resolution and then make sure that the path to the calling application does not contain brackets '(' or ')'.
Thursday, December 02, 2010
Unable to load DLL 'OraOps9.dll'
System.DllNotFoundException: Unable to load DLL 'OraOps9.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
Possible reasons for the above error would be using ODP.NET (Oracle Data Provider for .NET) in your application.
Make sure the ODAC (Oracle Data Access Components) is installed on the target machine running the application.
http://www.oracle.com/technetwork/database/windows/downloads/index-101290.html
Possible reasons for the above error would be using ODP.NET (Oracle Data Provider for .NET) in your application.
Make sure the ODAC (Oracle Data Access Components) is installed on the target machine running the application.
http://www.oracle.com/technetwork/database/windows/downloads/index-101290.html
Wednesday, December 01, 2010
PDF freezing while loading
This happens with pdfs that were published with the fast webview option. (linearized) and having StaticFileHandler mapped to PDF files in the web.config file.
This issue occurs due to a problem with the StaticFileHandler while processing Ranged requests that have a buffer size greater than 16. The default range buffer size is 16. The Adobe PDF web browser plug-in uses Ranged HTTP requests and may send more than 16 ranged fragments for large .pdf files. In this scenario, the StaticFileHandler attempts to resize its buffer by calling the Buffer.BlockCopy function, which results in the web browser hanging and the download failing.
Solution
Remove the StaticFileHandler mapping from the web.config file so that the PDFs can be served via the DefaultHttpHandler.
This issue occurs due to a problem with the StaticFileHandler while processing Ranged requests that have a buffer size greater than 16. The default range buffer size is 16. The Adobe PDF web browser plug-in uses Ranged HTTP requests and may send more than 16 ranged fragments for large .pdf files. In this scenario, the StaticFileHandler attempts to resize its buffer by calling the Buffer.BlockCopy function, which results in the web browser hanging and the download failing.
Solution
Remove the StaticFileHandler mapping from the web.config file so that the PDFs can be served via the DefaultHttpHandler.