Mohammed Fauzi @ Blogspot

Sample to find Area and perimeter of a Polygon

2011-06-19T07:19:00.000-07:00

Hi,
Today tried a sample to fetch Area and perimeter of a surface using ArcGIS API with Google maps. It is easy to use, we just need to join the points for the respective surface for which we need to find the Area & perimeter and double click. I always like Central park in Newyork, Thought let us try the tool there... It works perfect... please find the link & screen shot below.

Live Sample: http://www.kuwaitfresh.com/find_area.html

Terrain Analysis in ArcGIS Explorer

2011-06-18T04:05:00.000-07:00

Hi

Tried installing the Terrain Profile plug-in for ArcGIS explorer. Found it interesting because, I came to know that Terrain analysis & calculations are done through ArcGIS web-servers in real-time. The location striked on my Mind was Mt. Everest from Himalayas. Just searched for Mt. Everest and landed in the location. I just enabled Terrain Analysis, drew a straight line and double clicked in order to start calculation. In few seconds it was done! The values are almost close... Its fun working with this tool & much more to explore...

Regards

Fauzi

Salmiya Garden Distance

2011-05-27T05:30:00.000-07:00

Hi,
Today, i thought we need to find the distance we walk in the salmiya garden situated in 10th Block. We usually go for two rounds and some times more, based on the energy level :). Thanks to ArcGIS explorer which made our quest simple. its easy to do as well... Please see the screen shot below which shows a clear picture.

Answer: 1.048 Kms

Where Am I ! A sample on Geo location using ArcGIS API

2011-05-22T23:28:00.000-07:00

Hi

I have created a Geo location finder page using ArcGIS api. It is a test page to display the current location of the browser. I am sending to friends across the globe to evaluate. Please try from your end and reply whether it works fine or not & your browser version. mostly it is supported in all latest versions.

Live Sample : http://www.kuwaitfresh.com/WhereAmI.htm

We have used the following web-service in this sample:
http://server.arcgisonline.com/ArcGIS/rest/services/World_Street_Map/MapServer

Observation as on 1 July: For some friends in IE 8 it didn't work. Rest for Firefox version greater than 3.5,Chrome, IE9, It worked once the user allowed "share my location" option. and we have also tested in Iphone which is upgraded with version 4.3.

ArcGIS JavaScript API sample to display the current latitude and longitude

2011-05-22T06:11:00.000-07:00

Hi,

Here is a sample of ArcGIS Javascript API using google maps web services. To use google web services we need to register with them. After registration with google, they provide a API Key specifically for our domain, say for example in our case it is KuwaitFresh.com.

About the Sample, it helps us to fetch exactly the current Latitude and longitude on mouse click on the respective position. Please click the link below to navigate to the live sample. Thanks

Live Sample

Sample of ArcGIS online Viewer using Javascript API

2011-05-19T01:21:00.000-07:00

Hi,
Created a sample Map from API viewer in ArcGIS to display some cities in kuwait. Embedded the code in a page in my website as well... click the image below to view the page below.

http://www.kuwaitfresh.com/ArcGIS_MapViewer_sample.html

ArcGIS Explorer online : Oil spill in Gulf of Mexico over time

2011-05-19T01:04:00.000-07:00

Hi,
Got a chance to explore ArcGIS online tools. Came across this map which clearly explains the oil spill scenes in a slide show. Click the image to view the presentation.

Searh for a text in whole database SQL

2011-05-11T13:22:00.000-07:00

Hi,

There was a requirement to search for a text in the whole SQL database. found this Stored procedure very useful. though it is worth sharing...
Resource: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

-- SearchAllTables ''

CREATE PROC SearchAllTables

(

@SearchStr nvarchar(100)

)

BEGIN

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

SELECT ColumnName, ColumnValue FROM #Results

END

SQL to generate Script for All stored procedures in a Database

2011-04-14T02:44:00.000-07:00

Hi,
I had a requirment to create a local copy of a MS SQL Database hosted on remote environment(Godaddy Hosting). With the help for Import Data option in SQL Server Managment studio, I was able to replicate both the Tables & their respective Data. Since there no option to import Stored procedures. I was looking in google to generate SQL script for all the stored procedures from a Database.

List All Stored Procedure in Database: select * from sys.procedures

Fortunate to find this post: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-to-script-all-stored-procedures-in-a

set nocount on
DECLARE @Test TABLE (Id INT IDENTITY(1,1), Code varchar(max))

INSERT INTO @Test (Code)
SELECT 'IF object_ID(N''[' + schema_name(schema_id) + '].[' + Name + ']'') IS NOT NULL
DROP PROCEDURE ['+ schema_name(schema_id) +' ].[' + Name + ']' + char(13) + char(10) + 'GO' +
OBJECT_DEFINITION(OBJECT_ID) + char(13) +char(10) + 'GO' + char(13) + char(10)
from sys.procedures
where is_ms_shipped = 0

DECLARE @lnCurrent int, @lnMax int
DECLARE @LongName varchar(max)

SELECT @lnMax = MAX(Id) FROM @Test
SET @lnCurrent = 1
WHILE @lnCurrent <= @lnMax BEGIN SELECT @LongName = Code FROM @Test WHERE Id = @lnCurrent WHILE @LongName <> ''
BEGIN
print LEFT(@LongName,8000)
SET @LongName = SUBSTRING(@LongName, 8001, LEN(@LongName))
END
SET @lnCurrent = @lnCurrent + 1
END

Hope it Helps

UNZIP a zip file through C#

2011-02-08T23:03:00.000-08:00

Hi,
We had a FTP location where our vendors place a ZIP file. The idea is to process file once it is placed through a listner application. So we need to unzip the placed file through an application. The API: ICSharpCode.SharpZipLib.Zip was very much helpful to achieve the same.

Sample source code is below:

using System;
using System.Collections.Generic;
using System.Text;
using ICSharpCode.SharpZipLib.Zip;
using System.IO;

namespace unzipper
{
class Program
{
static void Main(string[] args)
{
UnZipFiles("C:\\TEST\\SharpZipLib_0860_Bin.zip", "C:\\TEST\\", "", false);
}

public static void UnZipFiles(string zipPathAndFile, string outputFolder, string password, bool deleteZipFile)
{
ZipInputStream s = new ZipInputStream(File.OpenRead(zipPathAndFile));
if (password != null && password != String.Empty)
s.Password = password;
ZipEntry theEntry;
string tmpEntry = String.Empty;
while ((theEntry = s.GetNextEntry()) != null)
{
string directoryName = outputFolder;
string fileName = Path.GetFileName(theEntry.Name);
// create directory
if (directoryName != "")
{
Directory.CreateDirectory(directoryName);
}
if (fileName != String.Empty)
{
if (theEntry.Name.IndexOf(".ini") < fullpath =" directoryName" fullpath =" fullPath.Replace(" fulldirpath =" Path.GetDirectoryName(fullPath);" streamwriter =" File.Create(fullPath);" size =" 2048;" data =" new" size =" s.Read(data,"> 0)
{
streamWriter.Write(data, 0, size);
}
else
{
break;
}
}
streamWriter.Close();
}
}
}
s.Close();
if (deleteZipFile)
File.Delete(zipPathAndFile);
}
}
}

Hope it Helpz...
Reference: http://www.eggheadcafe.com/tutorials/aspnet/9ce6c242-c14c-4969-9251-af95e4cf320f/zip--unzip-folders-and-files-with-c.aspx
Regards
Fauzi

Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561

2011-01-09T23:01:00.000-08:00

Hi,

We had a requirement to generate a PDF report in a table with all items for the selected vendor with their respective images. Thanks to ItextSharp for the wonderful utility to generate PDF's. Since we embed image in run time, while executing the part,
table.AddCell(image.Getinstance() we get the following exception:

"Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed."

Googled for the message and found that the solution is that we need to set trust level as full in web.config. Since it is an internal application for us it is not a big deal in our case. Any ways planning to encrypt the PDFs as password protected.

Solution: trust level="Full" in web.config
Resource: http://forums.asp.net/p/1422162/3433380.aspx

Hope It Helps :)

Regards
Fauzi

How to compile C program with Visual studio 2010

2010-12-11T10:20:00.000-08:00

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

2010-11-10T05:09:00.000-08:00

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

2010-11-08T23:37:00.000-08:00

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;
}
}

2010-11-04T04:17:00.000-07:00

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

2010-09-26T06:28:00.000-07:00

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.

2010-08-18T03:40:00.000-07:00

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

2010-07-06T01:21:00.000-07:00

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

2010-06-27T04:26:00.000-07:00

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

2010-06-22T04:50:00.000-07:00

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

2010-05-30T22:59:00.000-07:00

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:

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

2010-05-23T07:46:00.001-07:00

Was in Need of the same...

SQL Script:

CREATE PROC SearchAllTables

(

@SearchStr nvarchar(100)

)

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

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

SQL Query to List tables in a Database based on their size occupied

2010-05-23T01:23:00.000-07:00

Hi...

This time I was looking forward to list the tables in the database based on their size...

QUERY:

WITH table_space_usage ( schema_name, table_name, used, reserved, ind_rows, tbl_rows )

AS (SELECT

s.Name

, o.Name

, p.used_page_count * 8

, p.reserved_page_count * 8

, p.row_count

, case when i.index_id in ( 0, 1 ) then p.row_count else 0 end

FROM sys.dm_db_partition_stats p

INNER JOIN sys.objects as o ON o.object_id = p.object_id

INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id

LEFT OUTER JOIN sys.indexes as i on i.object_id = p.object_id and i.index_id = p.index_id

WHERE o.type_desc = 'USER_TABLE' and o.is_ms_shipped = 0)

SELECT t.schema_name

, t.table_name

, sum(t.used) as used_in_kb

, sum(t.reserved) as reserved_in_kb

,sum(t.tbl_rows) as rows

FROM table_space_usage as t

GROUP BY t.schema_name , t.table_name

ORDER BY used_in_kb desc

Hope It Helps

Fauzi ~ 4 Z

SQL Query to List all tables with their Row counts in a Database

2010-05-23T00:57:00.000-07:00

Hi there...

I was in need to analyze a database. This SQL Query to List all tables with their Row counts in a Database was pretty much useful.

SELECT

[TableName] = so.name,

[RowCount] = MAX(si.rows)

FROM

sysobjects so,

sysindexes si

WHERE

so.xtype = 'U'

AND

si.id = OBJECT_ID(so.name)

GROUP BY

so.name

ORDER BY

2 DESC

Hope it helpz
Fauzi

Gregorian Julian date Converter online

2010-05-05T04:42:00.001-07:00

http://peoplesoft.macek.cc/dateconverter.php?

Gregorian date to Julian date in C#

2010-05-05T04:32:00.000-07:00

Hey there...

Since we generat Reports from DB2 used by JDE, we need to convert Gregorian date to julian date when we write SQL queries... searched a lot for this option on web. Thought this should be helpful to lot of people who is looking for it...

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

Console.WriteLine(intJulianDate.ToString());

Hope it helpz
Fauzi - 4Z

DTSX failed to execute when invoked from web application ASP.Net

2010-04-25T00:15:00.000-07:00

Hi there...

Usually i use to have a console application to execute a DTSX file(SQL Server Integration Service - Business Intelligence application) And schedule it to run for automated execution.
(http://mohammedfauzi.blogspot.com/2009/10/how-to-run-dtsx-programmatically.html)

This time the requirement was to run another DTSX file on demand by end user. So we decided to go for a web application.

I made use of the same code which i used in previous post on submit click event here. Earlier i was getting permission related errors. The issue was solved when i gave permission for the respective user on whose credential the request is executed as anonymous user. When this issue was fixed, The other issue came was that the "Execution Failed" When the web page was executed from outside. Apparently it was working great when executed from Visual studio! Still the permission related issue was there...

After lots of Googling & trials found the solution!!!

Solution:
you need to the following:
1. In IIS, Right Click the Project -> Properties
2. Click on ASP.NET tab
3. Click 'Edit Configuration' button
4. Click 'Application' Tab
5. Under Identity Settings -> Click the Checkbox 'Local Impersonation'
6. Give the user name & password for local user of the machine(who has access to run the DTSX applicaiton)

Thats it... now when you run the web application it works fine :)

Hope It Helps...

Regards
Fauzi

Infopath: Code Behind Issue Event ID:5337

2010-04-08T00:45:00.000-07:00

Hi,

When I had Code-behind for business logic in Infopath forms like:

XPathNavigator nav = MainDataSource.CreateNavigator();

nav.SelectSingleNode("//my:CurrentUser", NamespaceManager).SetValue("SOME Values");

the following Warning message was shown:

Warning Message:

Object reference not set to an instance of an object.

System.NullReferenceException: Object reference not set to an instance of an object.
at ItemCreation.FormCode.FormEvents_Loading(Object sender, LoadingEventArgs e)
at Microsoft.Office.InfoPath.Server.SolutionLifetime.FormEventsHost.<>c__DisplayClass6.<>c__DisplayClassa.b__3()
at Microsoft.Office.InfoPath.Server.Util.DocumentReliability.InvokeBusinessLogic(Thunk thunk)
at Microsoft.Office.InfoPath.Server.SolutionLifetime.FormEventsHost.<>c__DisplayClass6.b__2(Object sender, LoadingEventArgs e)
at Microsoft.Office.InfoPath.Server.SolutionLifetime.FormEventsHost.<>c__DisplayClass34.b__30()
at Microsoft.Office.InfoPath.Server.DocumentLifetime.OMExceptionManager.CallFormCodeWithExceptionHandling(UserMessages userMessages, OMCall d)

An entry has been added to the Windows event log of the server.

Log ID:5337

Seems the Error use to come when ever we try to SET VALUE.

instead of directly setting the value. you can us like

Solution:

You can have a public method

public void writeToNode(XPathNavigator xpathNav, string xpath, string value)

{

xpathNav.SelectSingleNode(xpath, this.NamespaceManager).SetValue(value);

}

And use the following code to set value:

writeToNode(nav,"//my:CurrentUser","Some Values");

Resource: http://www.dennispoint.com/Lists/Posts/Post.aspx?ID=27

Hope IT Helpz
Fauzi

C# method to convert Julian to Gregorian date

2010-04-05T03:00:00.000-07:00

Hello there,

I was in urgent need to convert Julian date to Georgian for a asp.net application which reads data from DB2.

The following code was useful.

public static string JulianToDateTime(int julianDate)

{

int RealJulian = julianDate + 1900000;

int Year = Convert.ToInt32(RealJulian.ToString().Substring(0, 4));

int DoY = Convert.ToInt32(RealJulian.ToString().Substring(4));

DateTime dtOut = new DateTime(Year, 1, 1);

return dtOut.AddDays(DoY - 1).ToShortDateString();

}

Hope It Helps

Regards
Fauzi

How to Find you Black Berry PIN number

2010-04-04T02:49:00.000-07:00

Hi

I was trying to find the BB PIN number and found the content from a article:
Type ALT CAP H on your BlackBerry keyboard and a screen will show up showing you your PIN, your IMEI number (International Mobile Equipment Identity), signal strength and battery levels etc...

Resource: http://crackberry.com/quick-way-find-your-pin

Hope it helps
4Z

xp_sendmail: Procedure expects parameter @user, which was not supplied.

2010-04-01T06:14:00.000-07:00

Hello...

When you try to send E-mail from your SQL Server 2005 T-SQL with xp_sendmail, there will be an error message shown as follows:

xp_sendmail: Procedure expects parameter @user, which was not supplied.

And probably when you try to pass @user also, it will say invalid parameter.

It seems in SQL Server 2005, xp_sendmail is obsolete and we can use sp_send_dbmail instead.

Following is the syntax:

EXEC msdb.dbo.sp_send_dbmail
@recipients='vm.mohammedfauzi@gmail.com',
@subject = 'SQL:DataBase Mail Test',
@body = 'Hello Test From SQL.Hello Test From SQL.',
@profile_name = 'The Profile you created in Configure Database Mail',
@body_format = 'HTML'

The following link has screen shots to setup Email Profile in SQL Server : Click Here

Hope it helps

Fauzi

MOSS: Where to place generic Javascript in MOSS Server

2010-03-25T06:46:00.000-07:00

Hey Reader!

(writing on demand from Mr.Murthu Buddy :))

When you need to use a javascipt in your html page in MOSS Sharepoint.

you can place the Javascript file in the following location:

C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\js\mootools-1.2-core.js

and in your HTML file, you can refer it like:

<script type="text/javascript" src="http://portal/_layouts/js/mootools-1.2-core.js">script>

Hope it Helpz

Hosting the InfoPath 2007 Form Editing Environment in a Web Form

2010-03-23T03:39:00.001-07:00

Hi...

The Link is very useful to create a web form display infopath form

Hosting the InfoPath 2007 Form Editing Environment in a Custom Web Form

Regards
Fauzi

Automated Blog

2010-03-14T00:16:00.000-08:00

Hey There...

These days couldn't find enough time to blog...

So developed an automated blog!

Please view and feel free to share your thoughts...

www.KuwaitFresh.com

Best Regards
Fauzi

Convert DataReader To DataSet through C# Dynamically

2009-12-09T00:01:00.000-08:00

Hi,

I was writing an application to read data from AS 400, Db2, based on the data I have to generate a report. I was trying to covert OdbcDataReader to DataSet. The following Code was helpful to do the same... We can use it for SqlDataReader as well.

Code:

public DataSet ConvertDataReaderToDataSet(System.Data.Odbc.OdbcDataReader reader)

{

DataSet dataSet = new DataSet();

{

// 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;

}

Hope It Helpzzzz

4ZZZZ