Thursday, December 13, 2012

How to parse comma delimited string into IN Clause in SQL Server

Hi Folks,

Today i was trying to pass multi-value parameter into my select query in SQL Server. To accomplish this i stored the multi-value parameter in a comma delimited string. Then, i want to pass it in my IN clause of the select statement. here is an example to what i want to accomplish:

Delcare @myVar varchar(500)
Set @myVar='1,2,3,4,5,7'

Select * from Employee
where EmployeeId IN (@myVar)

You will get this error:
Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value '1,2,3,4,5,7' to data type int.


It makes sense, because i have a varchar that holds all my values and i want to pass those in the IN clause that only accepts integer values!.

The solution for this problem is to parse this string into set of integers and pass it back to your query in your IN clause. For this reason, i have created a function that return a table of 1 column of type int. This will be passed back to the select statement i have mentioned above.

The function code to parse comma delimited string into set of integers:

-- =============================================
-- Author: Mostafa Elzoghbi
-- Create date: 12/10/2012
-- Description: Parse a string into set of numbers
-- =============================================

CREATE FUNCTION [dbo].[ParseCommaDelimitedString]
( @CommaSeparatedStr
nvarchar(1000) =NULL)
RETURNS @myTable TABLE ([Id] [int] NOT NULL)
AS
BEGIN
declare
@pos int
declare @piece varchar(500)
-- Need to tack a delimiter onto the end of the input string if one doesn't exist
if right(rtrim(@CommaSeparatedStr ),1) <> ','
set @CommaSeparatedStr = @CommaSeparatedStr + ','
set @pos = patindex('%,%' , @CommaSeparatedStr )
while @pos <> 0
begin
set @piece = left(@CommaSeparatedStr , @pos - 1)
-- You have a piece of data, so insert it, print it, do whatever you want to with it.
insert @myTable
select @piece

set @CommaSeparatedStr = stuff(@CommaSeparatedStr , 1, @pos, '')
set @pos = patindex('%,%' , @CommaSeparatedStr )
end
RETURN
END
-- =============================================

What you need after that to re-write your query as follows:

Select *

from Employee
where EmployeeId IN
( select * from ParseCommaDelimitedString(@myVar))

Now, you will be able to pass any comma delimited string, varchar,nvarchar to this function and it returns table of integer that you can set in the IN clause of you select statement or any other T-SQL statements you works with your logic.

Hope this helps.

--ME

Wednesday, December 12, 2012

Error: unable to retrieve column information from the data source in SSIS

Hi Folks,

I was trying to call a stored procedure from OLE DB Source in SSIS. and i getting this error:

"Error: unable to retrieve column information from the data source"

I wasn't even able to get the columns returned from this stored procedure.

My stored procedure is having a dynamic query i'm building based on passed parameters from SSIS package.

My Stored procedure code:

Declare @sqlStatement as nvarchar(1000)
SET @sqlStatement = ' SELECT * FROM dbo.myTable
WHERE ID IN ('+ @Ids + ' )'
-- For tracing purposes
print @sqlStatement
execute sp_executesql @sqlStatement

To fix this problem, the SSIS when it executes the stored procedure in the design time, it doesn't pass any parameters, with that being said, you have to make sure that your stored procedure is working when you pass NULL values by default. and this was the trick to fix the problem. In my case, when i pass NULL values for my parameter that contains multiple values my SQL query statement is not valid!

I fixed my stored procedure through the following:

Declare @sqlStatement as nvarchar(1000)
IF @Ids is not NULL
SET @sqlStatement = ' SELECT * FROM myTable WHERE ID IN (' + @Ids + ' )'
ELSE
SET @sqlStatement = ' SELECT *FROM myTable'

-- For tracing purposes
print @sqlStatement
-- EXEC @sqlStatement
execute sp_executesql @sqlStatement

After fixing my stored procedure to work with null passed values and my dynamic sql statement is correct i was able to view my columns returned from the stored procedure and it works like a charm!

Hope this tip helps you when you create any stored procedure that is being called from SSIS objects such as OLE DB source or SQL Task.

Thanks,
--ME

Sunday, December 09, 2012

Study Material Notes for Programming HTML5 and CSS 3 Microsoft Exam

Hi Folks,

I got a free voucher from Microsoft for programming HTML 5 and CSS 3 Exam. I studied and reviewed online video materials and i want to share with you my study notes and hope this will be helpful for passing the exam. Congratulations in advance for everyone!.

My Notes:

  1. Use accepts:'application/bin,text/xml' to accepts only XML and binary content in HTML responses.
  2. Use the following condition to check if the html response content is binary: If(request.getResponseHeader("Content-Type")=="application/bint"
  3. To show the status of upload is displayed in the progress bar:Xhr.upload.onprogress=
  4. To support 2 way communication between a web page and a worker role, implement the following:
    1. From the main page, use onmessage event handler of the web worker to capture events.
    2. From the web worker, use the onmessage event handler of the main page to capture events.
  5. Standard-complaint screen readers must be able to identify the links contained within the navigation structure automatically: Use semantic markup.
  6. Use appendChild to add a control in DOM document.
  7. To extend a class with a method you should use prototype keyword as follows: Customer.prototype.GetCommission() = funct() {…}
  8. If you want an input control to allow only numeric values, use the following:    type="number"
  9. To pass objects between web page and web worker you can use: JSON,String and JavaScript types.
  10. To register an event listener for the web worker use addEventListenr and to stop a web worker use: self.close();
  11. Anchor selector order:  a:link --> a:visted --> a:hover --> a:active
  12. Use Figure semantic markup to include image and its caption using figcaption markup.
  13. To store user's information you can use: localStorage to get or set  user's data.
  14. To write a code to throw an error: throw new Error("Invalid",200)
  15. To show @ in the email address for a submitted form,   Use: str=$("form").serialize() ;         str=decodeUriComeponent(str);
  16. Apply styles from highest to lowest priority: 
    1. User agent style sheets
    2. Author normal style sheets
    3. Author important style hseets
    4. User normal style sheets
    5. User important style sheets
  17. To make sure that the advertisement section on the right most of the page use:
  18. To handle automatically each time the request status change use xhr.onreadstatechange
  19. Catch specific error number by using e.number and not e.message
  20. To use text-transform in CSS, you should use captalize  semantic tag.
  21. You need to group page content together to maximize search engine readability use article semantic markup.
  22. When  you want the inner paragraph exactly far from the outer paragraph, then you should use Relative.
  23. To show JSON confirmation number in a label: $("#txtValue").text(JSONObject.Confirmation);
  24. To show text around an image is showing in the center of the page use: -ms-wrap-side: both
  25. Use header to apply css or classes to different heading markup such as: H1,H2
**Videos - Jump start from Microsoft Virtual academy:

https://www.microsoftvirtualacademy.com/tracks/developing-html5-apps-jump-start

Good Luck, Feel free to drop a line with any feedback you would like to add.


Wednesday, December 05, 2012

Functions vs. Methods in JavaScript with Encapsulation!

Hi Folks,

I found something interesting to share with all of you in Javascript. Functions in javascript as we all know is defined with function keyword while Method in Javascript is a function but can have a scope and you can hide it as we do in object oriented programming. I will be showing 2 examples that implement function versus method in your web page to add 2 numbers.

1) Function to add 2 numbers in Javascript:

In your script tag, this is the declaration of the function:

<script language=javascript >
function myfunction() {
document.getElementById('txtResult').value = parseInt(document.getElementById('Text1').value) +                                             parseInt(document.getElementById('Text2').value);
</script>

My page, see the result after you change the second text box:

<p>Using Functions</p><input type="text" id="Text1" /><br /><input type="text" id="Text2" onchange="myfunction();" /><br /><input type="text" id="txtResult" /><p>

2) Method to add 2 numbers using Methods:

<script language=javascript >
// Methods
var ops = {

add: function AddTwoNumbers() {
                              document.getElementById('txtResult2').value =
                              parseInt(document.getElementById('Text3').value) +
                                 parseInt(document.getElementById('Text4').value);               
                                               }
         };</script>

My page:

<p>Using Methods</p><input type="text" id="Text3" /><br /><input type="text" id="Text4" onchange="ops.add();" /><br /><input type="text" id="txtResult2" />

You will see that we implemented the add function that has the scope within ops object, and in this way you can design and organize your functions within different scopes and with that being said you implement encapsulation for your methods!.

Anoter note, you gave an alias to your function with "add" alias name and this is not the actual name of the method since if you try to call the method as AddTwoNumbers you won't be able to do that.

The running application:



Hope this helps!

Sunday, December 02, 2012

Asynchronous Programming in C# - async/await


Asynchronous Programming in C# - async/wait

Hi Folks,

I want to share with you an important enhancement in .NET framework 4.5. The asynchronous programming paradigm for .NET developers using new C# keywords async/await. Since I'm C# developer; I will be highlighting this new design pattern keywords in C# for asynchronous programming which is async/await which is supported only starting from .NET framework 4.5.

what's async/await for C#?

Async is a new modifier in C# 4.5. async specifies to the compiler that the method is executing in asynchronous mode and not synchronous.

For example - C# Code:

async Task Sum(int x, int y) {
// your method implementation.
}

The async method returns a Task object or doesn't return any objects and this case you will write void instead of Task object.

The async method doesn't allow input parameters by reference or output.

The  async method has to contain a line to call the async method by using await operator.

For example - C# Code:
private async void btnAdd_Click(object sender, RoutedEventArgs e)
{
     int x=10,y=15,z=0;
    // The complier will call Sum asynchronously and return the value in z.
    z=await Sum(x,y);
 }


An async method provides a convenient way to perform potentially long-running processing without blocking the caller's thread.

The caller of an async method can resume its work without waiting for the async method to finish which makes your application more responsive and user friendly.


Read more about async/await from MSDN:
http://msdn.microsoft.com/en-us/library/hh156513(v=vs.110).aspx
http://msdn.microsoft.com/en-us/library/hh156528.aspx

Hope this helps.

-ME



 

Tuesday, November 20, 2012

Error connecting to TFS 2010 using VS BIDS 2008

Hi All,

If you are experiencing an error when connecting to TFS 2010 using Microsoft Visual Studio Business Intelligence Development Studio (BIDS) while you don't have this problem connecting using VS 2010 so follow below steps to fix it.

1) Close your VS 2008 instance if you have it open.

2) Install VS 2008 SP1 if you don't have it already.
http://www.microsoft.com/en-us/download/details.aspx?id=10986

3) Install Team Explorer Forward Compatability Update:
http://www.microsoft.com/en-us/download/details.aspx?id=10834

4) From the start menu, Open Run window and type: regedit
we need to update the default entry of your Team Explorer for TFS server url.

Navigate to:
HKEY_CURRENT_USER -->Software -->Microsoft-->VisualStudio-->9.0 -->TeamFoundation -->Servers

Right click on Server and click on add string:
set the name to: tfsservername
set the value to: your tfs server url, for example:
http://TFSServer:8080/tfs/CollectionName

Collection Name: is the project collection you want to connect to in TFS.

5) Close registry editor window and open VS 2008 BIDS.

6) You will automatically be connected to the project collection you previously specified and be able to bind your projects to the TFS 2010.

Enjoy Coding!

Hope this helps.

Regards,
Mostafa E.


Sunday, November 04, 2012

Error When activating Windows 8 Code 0x8007007B

Hi Folks,

If you are facing a problem activating your windows 8 machine this is because there is a bug till this point that won't allow you to activate or even change the product key from the  user interface. The solution for this problem is to change the key using the command line. you will be able to do this in 2 simple commands.

The Error Code when trying to activate your windows 8:




1) Open Command Prompt as Administrator
From the Metro Desktop, Hover the top right corner to get the menu; then search for "Command Prompt" and then hit enter. you will get the command prompt tile. then right click on this tile and click on Run As Administrator.

Below Figure shows the appearance before you click on Run as Administrator.


 


2) Type in this command to uninstall the product key

slmgr /upk


Once you enter this command you will get this popup window.




3) Install your product key

slmgr /ipk XXXXX-XXXXX-XXXXX-XXXXX



Once you install the new key you will get this message



Once you installed the new key, you will notice that if you go back by typing Activate Windows in the search menu from Metro Desktop you will be noticing that the windows is activated.

Congrats and have fun with Windows 8!

Hope this helps.

Regards,
Mostafa E.

Friday, November 02, 2012

Getting Started Windows 8 Store Apps Resources

Hi Folks,

I'm writing this post to share the resources that every software engineer or developer needs to get started with Windows 8 Store App development.

First, Before heading out and install Development resources (SDK) to start playing and exploring with windows 8 store app tools looks like. here is some sites you need to read to have a basic understanding about windows 8 store apps principles, design and concepts:

1) Windows Store Apps development center:
This site is the main site that contains all materials you want to get or read about Windows Store Apps design and development.
http://msdn.microsoft.com/en-us/windows/apps

2) First document i suggest to read, Windows 8 Product Guide for Developers, find it here:
http://msdn.microsoft.com/en-US/windows/apps/hh852650

3) Spend some time understanding the API reference for Win Store apps using JavaScript, C#,C++ or VB.NET:
This source shows how to use,understand & explore Windows 8 API using different programming languages. Using C# or JavaScript to build HTML Store Apps is different than using Traditional JavaScript in some manner.
http://msdn.microsoft.com/library/windows/apps/br211369

4) Get some basic understanding of Metro Style Store apps guidelines and principles:
Even if you are not a designer, it is crucial to have basic understanding of designing Views and pages.
http://msdn.microsoft.com/library/windows/apps/hh779072

After the previous four steps, you should be able to build your development box with Windows 8,VS 2012 Express, Blend and SQL 2012 Express, so you proceed to the following resources.
Downloads link:
http://msdn.microsoft.com/en-US/windows/apps/br229516

5) Getting started with development tutorials:
Get some samples, tutorials to get dirty hands working with VS 2012, Blend and All windows Store necessary tools to get up to speed in development.
http://msdn.microsoft.com/library/windows/apps/br211386

Since I'm a C# guy, here is the Get Started Development resources for C# Developers:
http://msdn.microsoft.com/en-us/library/windows/apps/hh974581.aspx

Once you reach this step, you should be able to build your first windows store app. now the time for deployment/licensing and selling your application.

6) Selling you application:
Get to know the available markets, languages and how to get paid.
http://msdn.microsoft.com/library/windows/apps/hh694064

I will keep updating this post as i find good resources to read  for early adopters!

Hope this helps,

Regards,
Mostafa E.

Thursday, November 01, 2012

Microsoft Surface - Product Review

Hi Folks,

I'm writing this blog post to share with all my blog readers and friends my experience in using Microsoft Surface. I was eager and waiting till it is available to pre-order it online and i ended up getting it from the store right away with no waiting LONG lines...lucky me!



I have been using Microsoft Surface for 6 days, and i want to share with you my personal insights from technical point of view and not from the marketing aspect! So, Let's start by answering basic questions:

1) Why did i buy Microsoft Surface ?
Since my wife has an iPad and we are using it for quite long time, I have much deeper experience using my iPhone and iPad or in general Apple Products. Before i bought MS Surface; I watched few reviews about mini iPad which is the latest and greatest from Apple. After comparing the 2 products i found that the mini iPad was over priced in comparison to the MS Surface, doesn't have Office, OS is locked not as Windows 8 and it serve the purpose as a E-Reader and a handy PC with fully featured Windows OS including IIS, Control Panel and Services. In addition to that, Fully fledged SDK for Developers to build Windows Phone Apps, Surface Apps and Web Apps for different platforms using HTML 5 and JavaScript!

If i would buy an E-Reader tablet my choice will be Kindle Fire from Amazon for 199$ and not min iPad for 329$.

All above was enough for me to go with the Surface!




2) What you can do with the Surface ?

a) Metro Style Experience: With Windows RT, You would have the same experience when you have Windows 8 on your PC. Very intuitive Tiles and fluid touch experience for what's in the start page, Pin to Task bar, and switch to Desktop Mode (You regular Windows Experience). The User Experience is fresh and new and this is a winner for The surface over iPad.

b) Cloud Connected Device: With the surface as for iPad, you will be able to sync up all you files to SkyDrive - Cloud Service from MS and they offer you to upgrade with extra 3GB for free when you purchase it! This is really cool. I have all my files linked to my Microsoft Account and i access it wherever i want and using any machine. MS is little behind since Apple introduced iCloud long time back, but here we are now we have it for both.

c) Store: Windows RT comes with the store where you can install all your applications as AppStore for Apple. The MS store still doesn't have lots of applications as Apple AppStore but it is growing tremendously!

d) MS Office: You have Word, PowerPoint, Excel, Access, OneNote, InfoPath for Free when you have Surface unlike iPad. Microsoft Office price is included and you don't need to pay extra for Office, this is a big plus!

e) Kinect: If you have XBOX 360, you will be able to play XBOX from the Surface, this is really cool! I tried it to surf internet and run Netflix and other applications in my XBOX and it was amazing experience.

Note: This feature requires you to have Windows Live Gold Membership to fully utilize all your applications in XBox.

f) People Hub: Connect & Read all your social media network notifications including: Facebook, Twitter, Windows Live and Mail notifications all through one place.

g) For Developers: If you are a geek and likes to develop apps for the surface. you can have all development environment for free from Microsoft. SDK,VS 2012 Express with Blend and SQL 2012 seamless integration for building Windows 8, Windows RT application with the ability to test your applications against these platforms. check out Windows Store Dev Center for details:
http://msdn.microsoft.com/en-us/windows/apps/br229512.aspx

-- Updated 11/13/2012
h) Connect the Surface to your TV: I bought a mini-HDMI cable from Amazon and now i'm able to stream any shows and vidoes i'm watching to my TV. here is what you can search for: mini HDMI to HDMI cable and you can get it for only 5$ from Amazon.

This is all what i have been seeing and experiencing during my last few days using the Surface!

Hope you like it and see it useful!
Drop me a line if you have any other questions or a feedback!

Thanks.

Mostafa E.