How to compile C program with Visual studio 2010

Hi,

Came across this blog on how to compile C program with Visual studio 2010.

http://channel9.msdn.com/blogs/sam/c-language-programming-with-visual-studio-2010-ultimate-pro-or-vc-express

Regards
4Z

Update DB2: SQL query exceeds specified time limit or storage limit

Hey Reader,
I wrote a .Net console application to read data from SQL Server and update respective table in DB2. So the update query is built dynamically in the code. The connection used was ODBC. When command.ExecuteNonQuery method is processed there occured an exception with the following message.

Message:
Message = "ERROR [HY000] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - SQL query exceeds specified time limit or storage limit."

When googled came acorss this beautiful post. And the solution is in ODBC properties under Performance tab , under Advance, we just need to uncheck the option - "Allow query timeout".
the trick worked for me :)
Hope it helpz
Fauzi

Sample to Connect DB2 through DOTNET

Dear Reader,
Here is a sample to connect DB2 through .Net Applicaiton, Create for a friend, thought this must be useful for people looking for it.

Code:
------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.Data;

namespace DB2_ODBC_Sample
{
class Program
{
static void Main(string[] args)
{
try
{
OdbcCommand cmd = null;
OdbcConnection con = null;
OdbcDataReader myReader = null;

con = new OdbcConnection("DSN=[YOURDSN];UID=[YOUR USERNAME];PWD=[YOUR PASSWORD];" + "Driver={IBM DB2 ODBC DRIVER};");
cmd = new OdbcCommand();
cmd.Connection = con;

cmd.CommandText = "SELECT * FROM SOMETABLE fetch first 5 rows only";

cmd.CommandTimeout = 0;
con.Open();
myReader = cmd.ExecuteReader(System.Data.CommandBehavior.KeyInfo);
aawservice objaawservice = new aawservice();
DataSet objDataSet = objaawservice.ConvertDataReaderToDataSet(myReader);
con.Close();
for (int i = 0; i < objDataSet.Tables[0].Rows.Count; i++)
{
Console.WriteLine("\n");
Console.WriteLine(objDataSet.Tables[0].Rows[i][2].ToString());

}
Console.Write("\nPress [Enter] to Exit ... ");
Console.ReadLine();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}

}
class aawservice
{
public DataSet ConvertDataReaderToDataSet(System.Data.Odbc.OdbcDataReader reader)
{
DataSet dataSet = new DataSet();
do
{
// Create data table in runtime
DataTable schemaTable = reader.GetSchemaTable();
DataTable dataTable = new DataTable();

if (schemaTable != null)
{
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
DataRow dataRow = schemaTable.Rows[i];

// Create a column name as provided in Schema
string columnName = (string)dataRow["ColumnName"];

// Define Column Type here
DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);

//Adding Column to table
dataTable.Columns.Add(column);
}

dataSet.Tables.Add(dataTable);

// Fill the data table from reader data

while (reader.Read())
{
DataRow dataRow = dataTable.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
dataRow[i] = reader.GetValue(i);
dataTable.Rows.Add(dataRow);
}
}

else
{
// No records were returned
DataColumn column = new DataColumn("RowsAffected");
dataTable.Columns.Add(column);
dataSet.Tables.Add(dataTable);
DataRow dataRow = dataTable.NewRow();
dataRow[0] = reader.RecordsAffected;
dataTable.Rows.Add(dataRow);
}
}
while (reader.NextResult());
return dataSet;
}
}

In a application there is an option to upload file and the file name is dynamically set with user's Name and Phone number. When user fills the phone number as +965-97324246. The file name is saved a Name_+965-97324246.pdf. Since "+" is there the hyper link ofthe file gave error when we try to view in browser. Thus we can use Regex to ignore special charaters. please find the sample below:

Sample:

using System;

using System.Text.RegularExpressions;

namespace Regex_Replace

{

// Remove forbidden chars for filename

class Class1

{

static void Main(string[] args)

{

string sourceStr = @"1\2/3:4*5?6""7<8+>90";

string rgPattern = @"[\\\/:\*\?""<>+]";

Regex oRegex = new Regex(rgPattern);

Console.WriteLine(oRegex.Replace(sourceStr, ""));

Console.Write("\nPress [Enter] to Exit ... ");

Console.ReadLine();

}

}

}

Hope it Helps...

Happy Diwali :)

SQL function to convert Julian Date

Dear Reader,

Earlier I have already written a logic to convery Julian to Datetime in C#

today we had a requirment to convert the same in SQL server. So wrote a function for this opetaion

Syntax:

Create FUNCTION dbo.from_julian(@julian char(6)) RETURNS datetime AS

BEGIN

RETURN (select DATEADD(YEAR, @julian / 1000,DATEADD(DAY, @julian % 1000,'18991231')))

END

//TEST Query

--select dbo.from_julian(110269)

Explaination of conversion Logic:

(select DATEADD(YEAR, @julian / 1000,DATEADD(DAY, @julian % 1000,'18991231')))

18991231 is the last day in 19^th century

Step #1:

Select (DATEADD(DAY, @julian % 1000,'18991231'))

Exmaple: Select (DATEADD(DAY, 110269 % 1000,'18991231'))

Output: 1900-09-26 00:00:00.000

Step#2:

(select DATEADD(YEAR, @julian / 1000, Step#1))

(select DATEADD(YEAR, @julian / 1000, 1900-09-26 00:00:00.000))

(select DATEADD(YEAR, 110269 / 1000, 1900-09-26 00:00:00.000))

Select (110269 / 1000)

Output: 110

Adding Year 110 + 1900 = 2010

Result is datetime: 2010-09-26 00:00:00.000

Hope It Helps

4Z ~ Fauzi

Asp.Net Website: The XML page cannot be displayed.

When I run a web application through Visual Studio, the application was working fine. Later to access the Web application through IP Address, we installed IIS on the machine. Later when we tried to access the URL, the following erros is shown.

Error:
The XML page cannot be displayed
Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.
A name was started with an invalid character. Error processing resource 'http://localhost/xxx.aspx. Line 1, ...

ReSolution: The fix is we just need to reinstall .NET framework

In Command Prompt Execute: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Aspnet_regiis.exe -i

Resource: http://support.microsoft.com/?id=306005
http://msmvps.com/blogs/bernard/archive/2006/03/30/88491.aspx

Hope it helpz...

Ramadhan Kareem :)

Fauzi

C#.Net : Method to get Last date of Month & Julian Date

Hi...

We had a requirment to run an application for the last day of the month. The following method was helpful to get the date.
Sample Code:

private DateTime GetLastDayofMonth(DateTime dtDate)

{

DateTime dtToDate = dtDate;

dtToDate = dtToDate.AddMonths(1);

dtToDate = dtToDate.AddDays(-(dtToDate.Day));

return dtToDate;

}

// Later this code was helpful to convert it to julian date:

public string GetJulian_LastDayofPreviousMonth()

{

int intJulianDate = 1000 * (DateTime.Now.AddMonths(-1).Year - 1900) + GetLastDayOfMonth(DateTime.Now.AddMonths(-1)).DayOfYear;

return intJulianDate.ToString();

}

Hope it Helps

Fauzi ~4z

SQL Query to delete duplicate records

Hey Guys...
There was a small logical check missed in my automated blog(KuwaitFresh.com). And because of this there occured duplicate records which means duplicate posts! which will be not be accepted by any user :) Here is the query to delete duplicate records in your table...

List Duplicate Rows:

SELECT Field,Count(*)

FROM TABLE

GROUP BY FIELD

HAVING ( COUNT(*) > 1 )

DELETE duplicate Rows:

General Syntax:

DELETE FROM TABLENAME WHERE POSTID in

(SELECT T1.POSTID

from TABLENAME T1, TABLENAME T2

where T1.DUPLICATEFIELD = T2. DUPLICATEFIELD

and T1. UNIQUEFIELD > T2.UNIQUEFIELD)

Example:

DELETE FROM TABLENAME WHERE POSTID in

(SELECT T1.POSTID

from TABLENAME T1, TABLENAME T2

where T1.TITLE = T2.TITLE

and T1.POSTID > T2.POSTID)

Hope It is Helpful :-)

Regards

Fauzi ~ 4Z

Execute a parameterised EXE dynamically inside SSIS

Hi Reader....

We had a requirement to run a same application for different POS(Point of sale) The challenge is to create a single application that takes parameters Store number as input parameter and does the required operation. Doing so we can use the same applicaiton any number of stores. In SSIS we have the module "Execute Process Task" to exectue exe files. I have given the file location in "Executalbe" property under Process Tab.



Regarding the Parameter, First i tried with 'args[0]' in the EXE Created from DotNet Console Applcaition, tried to use it as input parameters. Unfortunately it did'nt work. When i googled found that we can use Console.ReadLine() and get the required parameters in the EXE file And when you need to execute through SSIS(SQL Server Integration Services), we just need to pass the parameter (User Varaiable in SSIS, Eg: User::VarialbeName) in Standard Input Varaible property under Process.

This will pass the parameter dynamically during execution :-)

Regards
Fauzi

SQL Query to List number of rows in all tables in a Database

Hey...

I was googling to find a query to give me the number of rows in all tables in a database. This was helpful to cross verify whether all records are moved to the hosting Database from my local database.

Resource: www.SQLAuthority.com

Syntax:

GO

CREATE TABLE #temp (

table_name sysname ,

row_count INT,

reserved_size VARCHAR(50),

data_size VARCHAR(50),

index_size VARCHAR(50),

unused_size VARCHAR(50))

SET NOCOUNT ON

INSERT #temp

EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT a.table_name,

a.row_count,

COUNT(*) AS col_count,

a.data_size

FROM #temp a

INNER JOIN information_schema.columns b

ON a.table_name collate database_default

= b.table_name collate database_default

GROUP BY a.table_name, a.row_count, a.data_size

ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC

DROP TABLE #temp

Hope it helpz
Fauzi

SQL Stored Procedure to search for a word in Database tables

Was in Need of the same...

SQL Script:

CREATE PROC SearchAllTables

(

@SearchStr nvarchar(100)

)

AS

BEGIN

--EXEC SearchAllTables 'Fauzi'

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)

SET @TableName = ''

SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN

SET @ColumnName = ''

SET @TableName =

(

SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

AND OBJECTPROPERTY(

OBJECT_ID(

QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)

), 'IsMSShipped'

) = 0

)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

BEGIN

SET @ColumnName =

(

SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)

AND TABLE_NAME = PARSENAME(@TableName, 1)

AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

AND QUOTENAME(COLUMN_NAME) > @ColumnName

)

IF @ColumnName IS NOT NULL

BEGIN

INSERT INTO #Results

EXEC

(

'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

FROM ' + @TableName + ' (NOLOCK) ' +

' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

)

END

END

END

SELECT ColumnName, ColumnValue FROM #Results

END

--------------------------------------------------------------------------------------

To Test : EXEC SearchAllTables 'Some Text'


Thanks to http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Hope it helps

Fauzi