My Technical Experience

Large Data Transfer over SQL server DB

2011-09-29T15:00:00.000+02:00

Transferring a large data set from server to server or even from database to another on the same server or even from table to table in the same database from SQL server is very big problem concerning time, efficiency, and memory and space together.

I was facing this problem in transferring large data set, contains 1.5 million records per table and each table set is a query from source tables. so the output is transferring query results to a tables. It sounds simple but it is not!

when doing it by SQL import wizard it will take one query per time!!!! how could they think i will need only this!
when trying to do it by SSIS this where i went to hell.... although SSIS has some benefits at least outlining the task flow of the work, but transferring large amount of data is much bigger than it.
my server specs are:
Core 2 due intel processor
6 G.B Memory
win 2008 64 bit
60 GB disk space

It sound OK for transferring 1.6 million records from a query, but it took nearly 1 hour to do so.
Sure I added indices and all the tuning it should take, but with no big improvement.

The best solution I found is: "Bulk insert"...
but how could i dump 1.5 million to a text file quickly as SSIS also fails doing that. the solution to this problem was bcp utility. "bulk copy" how could not Microsoft integrate it into SSIS or in the import wizard. it is like hiding a treasure.

bcp is brilliant in that, minimizing the time into seconds instead of hours in both dump in files and import into table. the only concern about that was to have a good HDD space to dump files on and then deletes it.

It solved my problem. hooray.

Pivot Viewer

2011-05-05T12:18:00.000+02:00

I am new to Silverlight, but I tried the Pivot Viewer to display lists from Sharepoint. the task seems fine unless:

make a dynamic list
problem with images

the first one I got this article which is quite powerful . so it solved me Part one.

the second one I tried to understand why my images are not displayed, but when importing some images from the internet it works. I got an answer that these images that works are DZI (Deep Zoom Image). and there is a DZI Composer!! rocks... Hell no. the composer outputs a bunch of files to work for the concept of DZI.What I only need is one picture! Is this Hard :(

*DZI for simplicity, is making the image in layers so that it loads fast, and when zooming we can get the more detailed layers.

I am trying to google around and there should be any luck.

Pivot Viewer now is good but it should be customeized. they will, but in next release....

Egypt Revolt !

2011-02-28T17:16:00.001+02:00

***************************************************************

Now Egypt is revolting, waiting the results then writing :)

***************************************************************

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

2010-12-15T15:36:00.002+02:00

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

this error message has many sources:

connection is used with user id, and password and not saved
configuration file does not have the server name
data flow task is not marked as delayvalidation = true
low disk space in the server you try to get the data from
Oracle provider
Excel 64 provider

I tried to google and found most of the replies are for Oracle and Excel, and sometimes the password. while my case does not include any !!!!!

so I found the delayvalidation and it seemed works but nothing happened. I tried to make the things easier as My package runs in 4 hours and deals with more than 30Million * 200,000 * 1000 records with other tables that compared with the above is look-ups.

what make me confused that it sometimes runs ok, but some other times gives me an error at anytime!!!!

Now, I figured that it may be due to disk space.

Microsoft Analysis Service Performance Final

2010-11-04T14:50:00.005+02:00

Final Show of this series, Microsoft does not have a solution for the Large column data size!!!!!! they offer to upgrade the system rather than having a solution.

anyway, the solution is to crack down the dimension so that SSAS can handle it!!!!!!

Microsoft Analysis Service Performance 3

2010-10-25T15:41:00.006+02:00

Now a new tip for the SSAS 2005.

make attribute relationships
remove unused aggregations

Then in any Normal Scenario [according to Microsoft], this will solve it unless you have the [Large Dimension Problem] then it will not affect anything.

the Large Dimension Problem starts when you have at least one dimension with 170,000 records and more. In my case I have 220,000 records So I guess there are no solution.

I read a lot about migrating from SSAS 2005 to SSAS 2008, and also from 32bit to 64bit. but the reviews still as it. it should enhance the memory usage, but the same processing.

another thing that might be interesting, when I browse from Excel 2007 @ the server things are mmm not that bad, but doing the same from a different computer, then the disaster happens when I get to the large dimension.

what Microsoft expect to have small dimensions for making them work fine !!!!!

Microsoft Analysis Service Performance 2

2010-10-10T16:54:00.007+02:00

After we got confused, why our cube is too slow when accessing it from other combuters in the same LAN or even when browsing the cube within the same server.

Things to check:

Make the server 64 bit
Install SQL server 64 bit

Config to check:

Use Windows heap instead of SSAS heap

change

MemoryHeapType from 1 to 2

and HeapTypeForObjects from 1 to 0

Disable the option of blocking other queries if one of them is taking longer time

CoordinatorQueryBalancingFactor from -1 to 1

and CoordinatorQueryBoostPriorityLevel from 3 to 0

This should be done in the msmdsrv.ini file

All this content is coming from Mr. Ashotosh sharma from Microsoft

but till now it didn't solve the problem completely!!! so we are focusing now in the design tips.

Microsoft Analysis Service Performance

2010-10-07T18:17:00.009+02:00

While the past 3 months, I used to develop an OLAP project using SSAS 2005. After developing and deploying it, without data it looks good. when running the ETL's to populate the data in it! the hell is coming up.

The cube begin to be slower each time we tried to view a dimension with 200K records!

Then we tried partitioning the data, and how could Microsoft did that. they were putting for you a SQL query to partition the data!!!!! why didn't they do it as in SSAS 2000.

another thing, when we deploy the same solution to SSAS 2000 the performance is amazingly high!!!!

So, we partitioned the data, and tried to make the Cube MOLAP 100% and again it is only 4%.

and we could not guess why, cuz it is just a wizard!!!!!!!

We are now trying to tackle this problem, as soon as we solve it. I will post the solution in here.

Barcode Font

2010-07-22T16:06:00.003+03:00

Image via Wikipedia

I searched a lot for a barcode to work on most of the devices that reads it. The solution is IDAutomationHC39M.ttf

you can find it on this link, also note you will write the code as follows: *123* to show it properly.

if the reader scans it, it will be 123

MS SSIS 2005

2010-02-23T11:57:00.003+02:00

The component failed because truncation occurred, and the truncation row disposition on "output column " specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

common error messages when dealing with SSIS. and you google alot to know the answer. some people advise to user derived columns!

but the problem is when initiating the data source itself.

So the answer is pretty simple:

for the field that produce this error, on the output field for this field make it not fail the componenet but rather ignor this error.

nettiers dynamic search query

2009-10-08T10:56:00.005+02:00

I am using nettiers 2.3.0 now in a project and then i faced a problem where i have to make a search function that takes a string and search in each entity of the string fields of it to get the results.

nettiers has a good functions like GetPaged that takes the where clause and search with it. but come on how to make this search with generic code and powerful!!!!

the solution is a magic word, "Reflection"!!! ta da .... you have to make your development framework ideal enough to do that. How?

As I said i am using nettiers this make me support the following: each entity has an interface describes each property. and a good naming convention that I can follow in naming the UI elements such as typed data sources, that nettiers generates, and so i wrote this code...

List<string> lookFor = new List<string>();

Type t = Type.GetType(string.Format("Entities.I{0}, Entities", EntityName));

foreach (PropertyInfo info in t.GetProperties())

{

if (info.PropertyType == Type.GetType("System.String"))

{

lookFor.Add(string.Format(" [{0}] Collate SQL_Latin1_General_CP1_CI_AS like '%{1}%' ", info.Name, txtName.Text));

}

string whereClause = string.Join(" OR ", lookFor.ToArray()) ;

DataSourceControl ds = WebFormUtil.GetControlByID(Page.Controls, EntityName + "DataSource") as DataSourceControl;

PropertyInfo filterProperty = ds.GetType().GetProperty("Parameters");

Object collection = filterProperty.GetValue(ds, null);

if (collection is ParameterCollection)

{

((CustomParameter)((ParameterCollection)collection)["WhereClause"]).Value = whereClause;

}

ds.DataBind();

with just the entity name as a variable, and the well formed structure of the tiers and the good structure of the any form that contains a list and a search box this code rocks. I am able to get the typed data source and attach a value to its attributes and finally get works done and generic.

Thanks to Reflection, it made it possible.

Source Safe Error "File xxxxxxx is already open"

2009-09-17T11:09:00.004+02:00

Did you face this error once !!!! I faced it and goggled it. and all the answers were try closing the antivires and download this application to check. and none fix the problem.

The solution I found was simple but takes a long time :( it is Source Safe Admin analyzer. I ran this tool from the Admin. and duh it worked. I can check in files to source safe.

Anyway it is fixed now. :D

Entity Framework

2009-04-14T13:00:00.031+02:00

Entity framework is designed to separate the usage of the data layer elements like table names columns types, database type, and some added functionalities like super-set and subset.

It is good in the aspects mentioned above. But as Microsoft says, it is not a OR mapper. So, you cant get the benefits of the OR mapper like caching and database column validation, custom load, foreign keys values, and some like that.

All you have is the designer that you can do many things in it. and you have to edit this file for further subclassing conditions value - it saves the hexadecimal value and you have to change it manually, hope it will be solved at the final release- also, you can manage whether this table can be accessed via stored procedure or by generated query - if you select a stored procedure for one function like delete, you have to supply *sp for the rest update, insert :S - It is not complete now. Hope that it will be fixed in the final release. I made a comparison between Entity framework and .net Tier, and I found this

Entity Framework	.net Tier
one file contains entities and relations	contains many projects and files to represent the entities and its relations	No. of files generated
you can subclass entities based on condition easily from designer	you have to create the subclass file and modify the super-class manually	Super-class and subclass
entity object references not accessible outside domain [web service, WCF]	entity object accessible anywhere	entity object accessibility
queries accessible via linq and e-sql	accessible via defined functions	data retrieved
select query generated from linq and e-sql	select query defined in sp	select query
using the facade generated class for update and delete and insert, no e-sql	using the sp for all the operations	DML functions

This is all as far as I used the Entity framework. it is good but not as good as hibernate.

*sp: stored procedure

Javascript Good Stuff

2009-03-01T16:59:00.005+02:00

I was reading many blogs, and surfing and I found this video and it is awesome.

It is about the Good parts and Bad parts of the Javascript language. I was shocked for some of the bad parts like 0.1 + 0.2 <> 0.3. check the video for more.

the video url is here

Visual Studio Add-on

2009-01-27T18:39:00.005+02:00

From alot of projects I figured out that Microsoft is missing a vital addon for its Solution Explorer Pane! that is expand/collapse feature.

This feature is vital when you are working in solutions each one consisting of more than 15 projects each project has folders and sometimes these folders contain folders :(

the problem when you want to reach a file you will manually collapse all those nodes then find your item. the worst thing when you are debugging and opened a file from outside the solution to read something then close it ......................... Visual studio behavior is miserable, it will open all the projects tree nodes, then you want to collapse all of them to focus on the selected node only.

I searched about this add-on and found one http://www.paraesthesia.com/archive/2004/06/25/solvent---power-toys-for-visual-studio-.net.aspx but it is for Visual Studio 2003.

hmmmm, Ok no deal I created one for VS 2005 ta.da.

the link is here

Asp.net Update Panel and File upload

2008-12-16T14:49:00.014+02:00

Got stuck when using this control in the update panel. and you got a work around by making a pop up window to download the file through it.

This is not the case here, now you can use the ordinary file upload in the update panel! the only thing you have to do is making the button that "submit" to be postback from updatepanel triggers.

< update panel>
< content template>
< /content template>
< triggers>
< ! -- here is your ascync trigger -- >
< / triggers>
< /update panel>

Note: make sure that the control in the triggers tags is visible, if you want to hide it try style="display:none;" attribute.

if you are using a master page that contains an update panel and you want to do it. you can add this line to the page load event:

ScriptManager.GetCurrent(Page).RegisterPostBackControl(Button1);

now it is fine and working properly :)

what is Future of Gaming?

2008-12-01T10:50:00.007+02:00

Twice entries per day, I rock :D

Now what? the question I got into my head about the gaming industry. Now we have WARP in windows 7, and intel i7, and DirectX 10, besides the GPU. They are all maximizing the power of the games rendering and blending. Which will open the gates for a new games that have richer interface and much more bump mapping textures.

You know what? all that means in simple statement that the game will use the GPU and CPU for game processing. which leed to GPU+CPU(cores). It may seem like running the game upon maultiple processors on your computer. interesting.

It is a thread world after all. The more you use thread the better code it will be. and this can be done from the IDE itself!!!

After all, lets cruse the new threading world.

Does your website contain a malware, infection?

2008-12-01T10:36:00.004+02:00

Too long for not writing anything in the blog. But it seems I missed it a lot.

one question in the subject, are you sure your web content is safe? if you are sure from the content you type. Then may be the users that write reviews or commenting had something else.

in the article below you will find 3 online tools to discover if your website contains malware, unauthorized content, your bad neighbors.

Trust me it is useful to check your site from time to another. to check its content.

check the site for more info.
http://www.labnol.org/internet/detect-webpages-that-serve-malware/5597/

Web 2.0 and Indentity

2008-08-21T14:25:00.012+03:00

From a long time, discovering passwords and the secret questions were hard to get and almost uses the technical expertise sniffing on ports, and hacking the target computer by applications or even using brute force attack, and getting some information about the domain you would like to hack, and all these stuff. The hardest part was to crack someone's identity over the web. It has to be through the relationship between you and the person you would like to hack. So, you sneak around him/her and watch the different activities he/she does to guess what he/she likes - like pets, furniture, trees, flowers...etc - it was the hardest part. It turns that you are a spy on him/her.

Web 2.0 has been coming. This sentence has changed the world. Many many people will be writing their stories and experience. The content will be from the users not from the companies. There were a punch of tools like blogging and wiki and other tools that make Web 2.0 more powerful. Yet we have a huge source of information getting from the people themselves and the companies. Pretty Cool huh !!!!

Now it is getting clear enough that Web 2.0 drives the identity to be public and more than that readable by hackers and crackers. I read this article How I Stole Someone's Identity, and I got shocked, not that shock when you touch electricity, but yeah your identity and my identity are public now. Anyone can read it and download it, and spend a day discovering my hobbies and my interests and begin trying to access my e-mail that i gave him. and then trying to access my visa number that I talked about it once in my blog, but not this blog :), and then ta da. I got busted and then I will receive a letter from the bank that I make my balance zero :( :( :( .

I guess from now, secrets should be kept in your mind only. Do not talk about yourself loudly, or you got busted by many hackers...

The Controls collection cannot be modified because the control contains code blocks (i.e. <% ... %>)

2008-07-15T17:14:00.005+03:00

If you have this exception, with wired error message and the stack trace is some how freeek.

Simply, this exception occurs when you have a server side call from javascript located at the head tag of the html like that

< head runat="server" >
< script >
var controlClientObject = <%= RadControl1.ClientID %>
...
< / script>

< body >
...

you should do that

< head runat="server" >

< body >
< script >
var controlClientObject = <%= RadControl1.ClientID %>
...
< / script >
...

I googled a lot and this is what concluded.

ref. http://www.telerik.com/help/aspnet/menu//menu_move_codeblocks.html

Excel Export

2008-05-19T11:14:00.017+03:00

Image via WikipediaFaced many problems when loading or exporting to excel files! got many confused when loading data from excel file and it seems that the data you have is less than the data in the file. Needed to do many calculations and formatting!

the problem is we don't know how to deal with excel, and how excel deals with data connectors like ODBC.

I faced these issues and searched a lot to get a result and got these guidelines.

1- for loading an excel file make sure you format the excel file to the desired format you want. example: if you only will get the data in number format, so format the column with the suitable numeric format. so that all the data that you will have is the numbers, if you types a word in this column, excel will not transfer this value to the ODBC driver.

If you want it generic so you will format it as text. so whatever gets into the cell excel will transfer it.

here is a snippet of how loading from excel. [the code is in C#]

public DataTable LoadSheet(string sheetName)
{
DataTable dtSheet = new DataTable();

string excelConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";";

string command = "select * from [" + sheetName + "$]";

new System.Data.OleDb.OleDbDataAdapter(command, excelConString).Fill(dtSheet);
return dtSheet;
}

this how to load a sheet of excel into DataTable which you can deal with in the rest of your code.

*Trick: when reading, excel considers the first formatted row as the table header [formatted means bordered] if you did not do that the headers will be F1, F2...etc

2- Writing to Excel. the problem begins.

first you have to export to a named sheet
the sheet should be in a table format, if you typed in this sheet before exporting the writing process will begin writing after your writing. example: you write in cell A2, when exporting the excel will begin writing from A3, B3,C3...etc.
any formats should be applied on the previous cell that you want to write in. example: you to begin writing in cell A4, and you want it to be formated as numeric (#.00). So, you have to apply this format on cell A3 only and not the column.
now you need the way to insert the rows.

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties=Excel 8.0;");
connection.Open();
OleDbCommand command;
// insert rows
foreach (object item in items)
{
// insert row
command = CreateInsertCommand(sheetName, fields, connection, item);
command.ExecuteNonQuery();
}
private OleDbCommand CreateInsertCommand(string sheetName, ExcelField[] fields, OleDbConnection connection, object item)
{
StringBuilder builder = new StringBuilder();
string fieldValue;
OleDbParameter parameter = null;
int count = fields.Length;

OleDbCommand command = connection.CreateCommand();

for (int i = 0; i < count; i++)
{
fieldValue = (item is DataRow) ? fields[i].GetDataRowValue(item) : fields[i].GetValue(item);
if (i == 0)
{
builder.AppendFormat("insert into [{0}$] values(?", sheetName);
}
else
builder.Append(", ?");

parameter = new OleDbParameter(string.Format("param{0}", i), GetOleDBType(fields[i].FieldType));
parameter.Value = fieldValue;

command.Parameters.Add(parameter);

if (i == count - 1)
builder.Append(")");
}

command.CommandText = builder.ToString();
return command;
}
and tada it did insert the rows.

*Tricks: make sure you cast the fields values to its proper type.

Now the nightmare part. the calculations and advanced formatting. this is an excel behavior. if you need to do this you have to create a separate sheet for viewing and other sheets to get the data and make a reference from the view sheet to the other sheets.

the calculations will be on the view sheet. your export function will export to those sheets. And you have to open the file to apply the formatting otherwise it will not affected!!!!

Enjooy the excel loading and exporting.

those tips for formatting Excel.

This is a test For Zemanta :)

2008-05-07T11:20:00.005+03:00

Image by Tom Raftery via FlickrI had added the Zemanta tool for testing it. Simply it is neat. and Now getting images and related articles and labels for the topic you want to write about is now pretty simple. when I writing now about the topic the images is not yet related :D i think Zemanta has no related images!!!

Asp.net TreeView state

2008-03-04T18:23:00.007+02:00

Don't you ever wanted to preserve the tree status over multiple pages? I searched for many times and found the solution.

use cookies
javascript override functions
TreeView javascript important functions

these script assign a value to cookie:

To override the function in JavaScript, simply define a new variable to hold the old function, and redefine the old function variable as a new function. Here’s the syntax:

var base_TreeView_ToggleNode = TreeView_ToggleNode;

TreeView_ToggleNode = function(data, index, node, lineType, children){

base_TreeView_ToggleNode(data, index, node, lineType, children);

setProfileFolder(data, index,node,children);

return;

}
The setProfileFolder function is our addition to the base method.

see the original reference here.

3- Now you have to see the important functions and concepts about the treeview control

TreeView_GetNodeText(node): it takes a node object and returns the node name

believe me it is good, if you want to try to get it by your self you will fail, the structure is so complicated.

Facing the similar nodes in text but in different branches:

This is why the index thing exists. The tree view in javascript collect all items in javascript in an array. So, if you have:

Parent
+ child 01
--+child 02
-----leaf

it will be indexed like this

Parent [0]
+ child 01 [1]
--+child 02 [2]
-----leaf [3]

you can navigate through the child nodes like this

var childNodes = children.getElementsByTagName("a");
var increment = 0;
for(;incrementvar child_node_name = TreeView_GetNodeText(childNodes[increment]) ;
}

In the server side parse the cookie back and preserve the collapse/expand behaviour.

Do not hesitate

2007-12-11T11:09:00.000+02:00

Don't you ever get a thought. and wants to make it. May be this idea is big or small.

I was wondering the past days, which idea could change the world, and make me rich :) at the same time. I was reading about many startups the last 2 years. some of them got the high pitch, like youTupe and Facebook

and many many other ideas that make a good leap. but i found this article that talks about good start. you should never think about money. you should never think about the market. Just do it.

Do your idea as simple as it is, make it exciting, and link to public to get their feedback.

Just Do It.

reference: http://www.techcrunch.com/2007/12/05/loic-le-meurs-ten-rules-for-startup-success/

SQL Server Reporting Service

2007-09-12T12:46:00.001+02:00

Now this is the first tool to view the things, like cubes or even a relational database.

the new version of SQL Reporting service 2005, they added in it 2 features:

1- the report can connect on a cube to build a report in it.
2- the report model concept.

the first one is logical, because they are now building a BI solution. but the second one is fantastic. Although you have a cube in your application or not, Although you meet all the reporting requirements or not. The Client can do his report by him self. and can store it in warehouse to view it multiple times!!!!

The report model make this task easy.

How?

First you generate the Model, from either cube or RDB. Then deploy it. then link the client to the report server. He will find a link named "Report Builder". The wizard will come now.

The Client will open the report builder, selecting the appropriate model. then tada.. he will build a report of huge functionality like navigate through, complex calculations, grouping, ...etc without even writing any minor script. Just drag and drop.

the report model is the business representation of your entities. whether it is cube or RDB. it will generate a business entities that you can change their names to logical names example: instead of "PRODUCT_CATEGORY", you will rename it to: "Product Category".

The Client will generate all his reports and then deploy it in the report server. Then you can link to it from the report you are doing or from your application.

try it by your self, it is cute....

the next time we will apply security on the report and show some troubleshoots.