beyondrelational.com

Generating Alphanumeric Serial numbers

Fri, 24 May 2013 00:00:00 GMT

One of the common requirements is to generate an alphanumeric incremental values like PA0000000001, PA0000000002, etc. There can be several methods to to this. I will list out some of them

Method 1: Create an identity column and concatenate in SELECT statement when presenting data

Declare  @test table (cust_id int identity(1,1), cust_name varchar(50))
insert into @test(cust_name)
select 'Murugan' union all
select 'Suresh' union all
select 'Naresh' union all
select 'Kumar' union all
select 'Ezhilan'
select 'PA'+replace(str(cust_id,10),' ','0') as cust_id,cust_name from @test

The result is

cust_id      cust_name
-----------  ---------------
PA0000000001 Murugan 
PA0000000002 Suresh 
PA0000000003 Naresh 
PA0000000004 Kumar 
PA0000000005 Ezhilan

Method 2 : Create an identity column and use computed column

Declare  @test table (cust_id int identity(1,1), cust_name varchar(50), cust_id_new as 'PA'+replace(str(cust_id,10),' ','0'))
insert into @test(cust_name)
select 'Murugan' union all
select 'Suresh' union all
select 'Naresh' union all
select 'Kumar' union all
select 'Ezhilan'
select cust_id_new,cust_name from @test

The result is

cust_id_new  cust_name
-----------  ---------------
PA0000000001 Murugan 
PA0000000002 Suresh 
PA0000000003 Naresh 
PA0000000004 Kumar 
PA0000000005 Ezhilan

Method 3 : Create an alphanumeric incremental value before adding to a table

Declare  @test table (cust_id varchar(12), cust_name varchar(50))
declare @cust_id varchar(12)
set @cust_id=coalesce((select max(substring(cust_id,3,10)*1+1) from @test),1)
set @cust_id= 'PA'+replace(str(@cust_id,10),' ','0')
insert into @test(cust_id,cust_name)
select @cust_id,'Murugan'

set @cust_id=coalesce((select max(substring(cust_id,3,10)*1+1) from @test),1)
set @cust_id= 'PA'+replace(str(@cust_id,10),' ','0')
insert into @test(cust_id,cust_name)
select @cust_id,'Suresh'

set @cust_id=coalesce((select max(substring(cust_id,3,10)*1+1) from @test),1)
set @cust_id= 'PA'+replace(str(@cust_id,10),' ','0')
insert into @test(cust_id,cust_name)
select @cust_id,'Naresh'

select cust_id, cust_name from @test

The result is

cust_id      cust_name
-----------  ---------------
PA0000000001 Murugan 
PA0000000002 Suresh 
PA0000000003 Naresh

I prefer method 1 or 2 for the simple approch

Advantages of Plug-and-Use Desktop BI Tool You Should Know

Thu, 23 May 2013 00:00:00 GMT

The basic function of a calculator is computing, which can be as simple as the four arithmetic operations and also as complex as the calculation for the next move in chess with Deep Blue. Among these, esCalc is a desktop data calculation tool for the business users to handle the occasional, complex, and business-related data computation. Such tools are named as the desktop BI software.

For example, a stock analyst is required to recommend some stocks to the clients urgently. Among all calculations involved, the analyst needs to find shares from more than 20 daily trading stocks which had risen on 5 consecutive days in previous month. He opens this desktop BI software and imports the daily data of the more than 20 stocks. He continuously monitors and analyzes the data, works out the outline of algorithms, and then groups, summarizes, sorts, filters and takes other possible operations to calculate the results with some simple formulas. At last, he gets the result and makes the recommendation successfully.

The similar calculations also include:

For all clients of the insurance company, what’s the average insurance price of those who bought the basic insurance first and then bought the additional insurance?

In the 3 months with the most client complaints, find out the top 3 products with the highest defective rate.

For the top N sales persons who achieved the 50% of the total sales for the company, what are their respective sales proportions to that of their respective sales team?

esCalc is the plug-and-use desktop BI software with the powerful computational capability for business users to grasp easily, owing to the following advantages:

Typical Desktop Application

The installer of esCalc is only dozens of MB. The installation procedure only requires a few clicks and can be run immediately after installation. As a JAVA application running on the Windows desktop, esCalc can run on most office computers independently without having to deploy the extra server additionally.

esCalc resembles Excel in UIs that is easy to learn and grasp. The overall interface is shown as the below figure:

The Data Computation section is shown below:

esCalc is especially designed for the business users without technical background. It can be installed in a common working environment easily, and be used once installed.

Various Data sources Support

esCalc supports various databases, including MSSQL, Oracle, Access, MySQL, DB2, Sybase, and other mainstream databases. In facts, esCalc supports any databases with JDBC and ODBC drivers.

Besides the access to the database in the LAN, esCalc also supports the access to the local data file, such as txt, log, tab, other text files, and the Excel 97~2010 spreadsheets.

esCalc also supports the interactive calculations between various data sources, for example, to store the basic information like the company name, the contact information, and the company industry in the database, and to store the follow-up visit to some clients by sales persons in another Excel spread sheet. With esCalc, you can merge the two pieces of data easily to form a follow-up visit log classified by the company industry. Even if their Number of clients and the Sort Criteria differ to each other, esCalc can handle it with easy.

The office environment of business personnel is sometimes rather complex, such as the CRM, MIS, ERP, DSS or performance environments, supply chain management, and other application systems. esCalc supports various data sources and interactive calculations and is capable to handle the complex office environment.

Step-by-step Calculation

The step-by-step calculation can decompose the complex computational goal into several simple steps and complete a seemingly complicated great goal by solving each simple small problem.

Still with the above example, to calculate the stock rising for consecutive 5 days, you can group by stock first, and then calculate the daily increment of the stock. An increment greater than 0 indicates the stock is rising. Based on this judgment, the consecutive rising days of each stock can be calculated out. Lastly, the longest consecutive rising days of each stock can be calculated through filtering and sorting. The details are shown below:

Step 1: Import the stock data from txt file, as shown in the below figure:

Step 2: Filter out the data in the previous month by date. Suppose it is June in 2011, as shown in the below figure:

Step 3: Set the level by the stock code:

In the above figure, the newly-built level is in the red block on the left, and in the red circle on the right is a stock. This row is the summery row.

Step 4: Sort the transaction data of each stock by Date in ascending order.

Step 5: Calculate the daily growth rate of each stock. In this step, a calculation column D needs to be added, and a formula =(C4[A2]-C3[A2])/C3[A2] is entered to the D4. The formula will be pasted to the related cells automatically, as shown in the below figure:

Step 6: Compute the consecutive rising days. In this step, a computation column E needs to be added, and then the formula in E4: =if(D4>0,E3+1) to be entered. The result is shown in the below figure:

Step 7: Calculate the longest days of a certain stock rising consecutively by inputting the formula like max({E4}) in E2. The result is shown below:

Step 8: Fold the summary row by clicking on the level number 1 on the left, as shown in below figure:

Step 9: Sort by the longest consecutive rising days, as shown in below figure:

In the above figure, 5 stocks keep rising for 5 consecutive days in June 2011, which are American Express Co., The Boeing Co., Citigroup, Inc., General Motors Corp, and Coca-Cola Co. It is certain that the data can also be filtered by the longest consecutive rising days, as shown in the below figure:

To solve the relatively complex computational goal in this case, we decompose the computational goal into 9 steps of simple operation or formula computing.

The step-by-step computation allows users to decompose, simplify, and ultimately solve problems in a rather visual train of thoughts. Owing to this, business users can also solve some complex data computing problems by themselves.

Adequate data computational capability

esCalc is powerful enough to handle the various computational task in the daily office work.

Function as SQL in every aspect

With the same computational capability as SQL, esCalc can be used to filter, group, sort, and perform the distinct, union, join, and other equivalent actions of SQL. Please refer to the menu shown in the below figure:

No technical background required

SQL can only be grasped by the professional technician, while esCalc represents SQL functions with graphics and decomposes it into several steps so that even the business users without technical experiences can handle it easily. For example, in the step 3 of “building levels by stock code”, esCalc does not require any complex coding, and users only need to set it up in the menu easily.

Alternatively, the default shortcut menu can also be used, as shown in below figure:

Because SQL does not support the step-by-step calculation, SQL solution to this case will be lengthy, error-prone, and hard-to-understand statements. Obviously, it is hard for the business users to grasp.

Computational capability beyond Excel

The Excel and other tools alike do not provide the auto copy and intelligent adjustment functions. The similar functions can only be implemented with a great amount of manual operations. For example, the longest consecutive rising days of each stock, esCalc users can enter the formula =max({E4}) in E2 directly, as shown in the below figure:

After entering the formula in E2, all homocells of E2 will be populated with the formula, for example, E94. By comparison, Excel does not provide the auto-copy function, and you will have to conduct it manually. In addition to the auto-copy, esCalc also supports the intelligent adjustment function, for example, the formula in E94 will be adjusted to =max({E96}) to meet the business logics, as shown in the below figure:

In addition, the homo-cell model maintains the business relations between esCalc cells, so that the true grouping is implemented, and the grouped data can be further processed. This is hard to be implemented with the Excel and other tools alike, for example, in the step 4 of the above case, “sort the dealing data of each stock by Date ascendingly”. For Excel, ungrouping is required to sort the data by stock & date and then group. However, with esCalc, you can sort directly to implement it.

Multiple computational functions

esCalc can be used to perform the complex computations related to sequence numbers. For example, still in the above example, calculate the rankings of closing price of the end of previous month on the basis of the result in step 8.

Firstly, calculate the closing price in the end of month, then append a new column F, and enter the new formula in F2: ={C3}.m(-1), as shown in the below figure:

Then calculate the rankings. Append the new column G, and input the formula ={F2}.ranki(F2) in G2, as shown in the below figure:

As can be seen from above, 9N12584 (i.e. IBM) has the highest closing prices among these shares of more than 20.

esCalc can also perform the intersection, union, compliment, and other set operations, for example, compute the stocks that are among the last 10^th (cheaper) by closing price and rising for consecutive 4 days. esCalc can also be used to perform the inter-row computations such as monthly year-on-year comparison and the link relative ratio comparison, for example, compute the stock price moving averages in the 5 days.

In conclusion, esCalc is a typical desktop application which is able to support multiple data sources and step-by-step computation with sufficient data computational capability. It is the data computation tool for business users to handle the various computational problems in the daily office work easily.

#0266 - SQL Server - IDENTITY Columns - @@IDENTITY, SCOPE_IDENTITY, IDENT_CURRENT and $IDENTITY

Thu, 23 May 2013 00:00:00 GMT

IDENTITY columns are perhaps the least understood and yet most used features of Microsoft SQL Server.

In this post, I will explore some system functions and keywords that we as developers and administrators can use to get information about IDENTITY columns.

Whenever a record is inserted to a table using an IDENTITY column via an INSERT, SELECT INTO or bulk copy command, the identity values on the affected tables are incremented. Microsoft SQL Server gives us three system functions that can provide the last used identity value – depending upon the scope and session.

IDENT_CURRENT()

Given a table name, this system function returns the last identity value used for the given table or view. Since this function works on a particular table or view, this value is independent of the session or the scope.

SCOPE_IDENTITY()

This system function provides the last used IDENTITY value within the same scope.

Statements inside the same stored procedure, trigger or batch comprise of the same scope – those within triggers fired as a result of a statement inside a stored procedure aren’t.

If no statement has been executed which affects an IDENTITY value, this function returns NULL.

@@IDENTITY

The @@IDENTITY system function contains the last value that was generated by the statement – irrespective of the scope.

If no statement has been executed which affects an IDENTITY value, this function returns NULL. However, please note that this function reports the identity value last generated by the statement – irrespective of the scope. This means that if a statement causes execution of a trigger which generates identity values in other tables, calling the @@IDENTITY function immediately after this statement will return the last IDENTITY value generated by the trigger.

Let’s look at each of these via an example:

USE AdventureWorks2008R2;
GO
SET NOCOUNT ON ;

SELECT  IDENT_CURRENT('Purchasing.PurchaseOrderDetail') AS PODetailIdentity,
        IDENT_CURRENT('Production.TransactionHistory') AS TransactionlIdentity,
        SCOPE_IDENTITY() AS ScopeIdentityValue,
        @@IDENTITY AS LastUsedIdentity;
        
    --Perform the INSERT into the table: Purchasing.PurchaseOrderDetail
    --The insert trigger on this table: Purchasing.iPurchaseOrderDetail, causes a record
    --to be inserted into Production.TransactionHistory, thereby incrementing it's IDENTITY column - TransactionID
    INSERT  INTO Purchasing.PurchaseOrderDetail
            (
              PurchaseOrderID,
              DueDate,
              OrderQty,
              ProductID,
              UnitPrice,
             --LineTotal,
              ReceivedQty,
              RejectedQty,
             --StockedQty,
              ModifiedDate
            )
            SELECT  1 AS PurchaseOrderId,
                    '2005-05-31' AS DueDate,
                    1 AS OrderQty,
                    4 AS ProductId,
                    57.02 AS UnitPrice,
                    --258.06 AS LineTotal,
                    1 AS ReceivedQty,
                    0 AS RejectedQty,
                    --1 AS StockedQty,
                    '2013-05-09' AS ModifedDate ;
    GO

SELECT  IDENT_CURRENT('Purchasing.PurchaseOrderDetail') AS PODetailIdentity,
        IDENT_CURRENT('Production.TransactionHistory') AS TransactionlIdentity,
        SCOPE_IDENTITY() AS ScopeIdentityValue,
        @@IDENTITY AS LastUsedIdentity;
        
/****RESULTS****/
/*
        PODetailIdentity    TransactionlIdentity    ScopeIdentityValue    LastUsedIdentity
Before: 8847                213450                  NULL                  NULL  
After : 8848                213451                  8848                  213451
*/

Inserting a new record in the table: Purchasing.PurchaseOrderDetail causes the insert trigger on this table: Purchasing.iPurchaseOrderDetail to be executed. This trigger maintains a history of the PurchaseOrder Detail transactions in the table - Production.TransactionHistory, thereby incrementing it's IDENTITY column – TransactionID.

As can be seen in the output:

IDENT_CURRENT() provides us the last used identity value for the given table
SCOPE_IDENTITY() provides us the last used identity value within the scope of the batch
@@IDENTITY provides us the last used identity value irrespective of the scope

$IDENTITY

The $IDENTITY keyword allows us to refer to an identity column in a table without using the corresponding column name. Here’s an example:

USE AdventureWorks2008R2 ;
GO
SELECT  $IDENTITY
FROM    Purchasing.PurchaseOrderDetail ;
GO

The query above will return the value of the [PurchaseOrderDetailId] in the table [Purchasing].[PurchaseOrderDetail] since it’s marked as an identity column.

Other functions - IDENT_INCR() and IDENT_SEED()

If one needs to look at the value of the seed and increment values used in the identity column definition, the IDENT_INCR() and IDENT_SEED() functions can be used.

USE AdventureWorks2008R2;
GO
SELECT IDENT_SEED('Production.TransactionHistory') AS IdentityIncrement,
       IDENT_INCR('Production.TransactionHistory') AS IdentitySeed;
GO
/* RESULTS */
/*
IdentityIncrement   IdentitySeed
100000	            1
*/

Checking for Identity columns in a table

The Catalog View sys.identity_columns has one record for each identity column and provides information like the seed, increment, last value generated for that column and other meta-data information.

Usage is shown in the query below:

USE AdventureWorks2008R2 ;
GO
SELECT * FROM sys.identity_columns
WHERE object_id = OBJECT_ID('Production.TransactionHistory');
GO

Until we meet next time,

Be courteous. Drive responsibly.

Getting Started with Windows Azure - Part 9: Manually Deploy ASP.NET MVC application to Windows Azure WebSites using WebMatrix

Thu, 23 May 2013 00:00:00 GMT

**Introduction:** In this article, we are going to see how we can manually test and deploy the application which we developed in Visual Studio 2012 to Windows Azure Websites using the WebMatrix tool. In our earlier article we have seen how to deploy the ASP.NET MVC application which we developed in Visual Studio 2012 to the Azure environment using the inbuilt tools and services by packaging and uploading from the Visual Studio IDE itself. So here we are going to do the manual work on deploying the application that too using the WebMatrix tool. For the beginners who are not comfortable of WebMatrix is a free and lightweight web development tool with which we can Create, publish, and maintain any website with ease. Let us see the step-by-step approach to deploy the application which we developed in our previous article using the WebMatrix tool. **Steps:** Open the ASP.NET MVC application, which we developed in Visual studio 2012 IDE as an administrator. Build and execute the project once to check if any dependencies are not associated in any issues or any errors as shown in the screen below: ![asp .net mvc application][1] Since we are going to deploy the application to the Windows Azure Websites, which are like a shared hosting environment, we have very limited resources that can be very much helpful to the application maintenance. So to start with, open the application in Visual Studio IDE, go the web application project, right click on the project, and select the option “Convert to Web Application” as shown in the screen below: ![Convert to Web Application][2] Now again right click on the project and select the publish option, which will create a list of publishable files and folders, which we can manually package and upload to the Azure environment as shown in the screen below: ![publish][3] Now a new window open, which is the Publish wizard with which we can directly publish the application to the Windows Azure environment but it, will publish to the Cloud Services. Since we want to publish the application to the Windows Azure Web sites we can simply proceed the below steps as shown in the screen below: ![connection][4] Since we selected the File system we can see the files and folders that are required to run the application on the IIS web server will be published to this folder and we can see the status in the Visual Studio IDE itself as shown in the screen below: ![files and folders][5] Navigate to the folder which we selected for the publish files options and we can see a list of files and folders that the visual studio generates which are very much required to start the project in any IIS Supportive environment. Now go to the folder, right click on the folder name, and select the option “Open as website with Microsoft WebMatrix” as shown in the screen below: ![Open as website with Microsoft WebMatrix][6] If WebMatrix was not installed, first install the tool from the Microsoft website free of cost and then open the files. We can see the list of files and folder that are available as a list view in the Microsoft WebMatrix environment as shown in the screen below: ![list view][7] Click on Run and select the browser to which we want to check the application, here in our case we use the Internet explorer and we can see the application runs perfectly as this tool inbuilt has its own IIS Express running to manage the requests that comes from the application as shown in the screen below: ![run application][8] Now the files and folders are working perfectly and we need to upload these files to the Windows Azure Web Sites, so first create an empty Window Azure website with the website name and the basic region and subscription to provision as shown in the screen below: ![Windows Azure Web Sites][9] On successful Windows Azure web site creation navigate to the website Dashboard and get the FTP details, which can be used to upload the deployable files to the Windows Azure environment as shown in the screen below: ![dashboard][10] Now go back to WebMatrix and select the Remote option available at the top left corner and select the publish option. We can see a windows which asks if we have a hosting environment or need to create one. Select we have a hosting environment option and select the FTP or web deploy option and enter the FTP details as shown in the screen below: ![FTP details][11] Click on Test Connection option and see if the provided credentials are accurate, once the connection is successful proceed with publishing and access the website online. **Conclusion:** Therefore, in this article we have seen how to manually test the application developed using Visual Studio 2012 IDE in a Microsoft WebMatrix tool and upload the application directly from the tool to the Windows Azure websites easily in simple steps. [1]: http://media.beyondrelational.com/images.ashx?id=bd670ced5451401eb2ea6111db100106&w=628&h=0 [2]: http://media.beyondrelational.com/images.ashx?id=681a8794cc154956a770e3faa5005d0b&w=628&h=0 [3]: http://media.beyondrelational.com/images.ashx?id=91fe0cd238c042aebf785acc649869f9&w=628&h=0 [4]: http://media.beyondrelational.com/images.ashx?id=e179d6c602324e9b907356429475801b&w=628&h=0 [5]: http://media.beyondrelational.com/images.ashx?id=121eb02c7b204c27b1c8ad88adb72925&w=628&h=0 [6]: http://media.beyondrelational.com/images.ashx?id=7b113d5bdc4245179edf1d3feffe05f3&w=628&h=0 [7]: http://media.beyondrelational.com/images.ashx?id=bd0f1585c88842d9ad466619e80c55c7&w=628&h=0 [8]: http://media.beyondrelational.com/images.ashx?id=4a8730cc948c4e7f826f9ebb198561f5&w=628&h=0 [9]: http://media.beyondrelational.com/images.ashx?id=40ccec19767e49d4b5923f9d4a7db0ed&w=628&h=0 [10]: http://media.beyondrelational.com/images.ashx?id=8afdb3eae2624c5cb6b87268e976034c&w=628&h=0 [11]: http://media.beyondrelational.com/images.ashx?id=74355e22efff48d186e4b6380e2a82ce&w=628&h=0

SQL Server Import and Export Wizard – SSIS Part 2

Tue, 21 May 2013 00:00:00 GMT

Earlier we discussed how to Import data from a Flat File Data source and to load it into MS SQL Server Table. That was very easy to implement and same is the case in Export Data Wizard. We can go ahead by selecting any of the ways I mentioned earlier in my Import Wizard post ( If you want to again see them, Click Here -> SQL Server Import Wizard ). I am going by the 1st way i.e Using SQL Server Management Studio.

You can Export billions of data with 1 click using this Export Wizard feature provided in SSIS. Let’s take some data to get started. We created a database named chander (like to get popular, that’s why used my name), containing some data in the form of rows and columns as shown in the image below. Now, lets see how can we export this table to a Simple Text file.

STEP 1. Open SQL Server Management Studio (SSMS) and Right click on the database whose data you need to Export (say in our case lets select database named chander).Go to Tasks option and select Export Data. This will launch Import Export Wizard screen.

STEP 2. When the Microsoft Wizard appears, Click Next to begin and specify the Source connection. These are the basic steps which we performed earlier also while importing data.

STEP 3. Since you had opened the wizard from SQL Server Management Studio by selecting Export Data option, this screen is pre-populated. Now, specify from where your data is coming in the Data source drop-down box. Once, you choose your Data source, other options can vary as per your requirement. According to this example, I will choose “SQL Server Native Client” as Data source. Make sure the Server name references your database server(In our case,it’s localhost), Authentication is set to Use Windows Authentication and select your database which you need to export from Database drop-down(as in this case I am choosing chander as my database). Click Next.

STEP 4. Now,you will proceed to Choose a Destination Screen to specify where you need to copy data. Go ahead with the following steps :-

In the Destination drop-down list, choose Flat File Destination as we are exporting data of our database to a Flat file(Simple Text file).
Click on Browse button (under File name option) to navigate to the path where you want to store the your Flat file.
Other fields will be set by default like Locale is English (United States) , Code Page is 1252, Format is delimited and Text qualifier is none. But you will have an option to set them as you need. Click Next and select Copy data from one or more tables or views and proceed ahead.

Note:- Tick the check-box at bottom if you need to have column names in the first data row. I am checking it here because we have Name,Age,Location as our 1^st field.

STEP 5. Clicking Next will lead to Configure Flat File Destination Screen. You will see some fields which needs to be specified to configure the destination.

Source table or view :- Choose your source from the Dropdown list box.
Row and Column Delimiter :- Set Row delimiter as {CR}{LF} and Column delimiter as Tab {t}.

You can preview your data from the Preview button and also can edit columns mappings from Edit Mapping Button. Click Next.

Step 6. On the Save & Run Package screen, you can Run Immediately without saving or can Save SSIS Package by clicking the respective check boxes. If you wish to Save it for later execution then Select File System as the option to save SSIS Package to. Click Next.

STEP 7. On the Save SSIS Package, enter Package name, description and set File name. Save the package by Clicking Next.

STEP 8. Review the details on the Complete Wizard screen and click Finish. The package will be saved to the location we specified and will export the Customers data from SQL Server Table to Customer.txt Flat file.

STEP 9. Let’s verify whether we implemented it properly or just wasted our time specifying fields. Go the specified path where you saved it. Open the Customers.txt file and review the data.

Great !!! We implemented it. So, you can see how easy it is to export data using SSIS Import and Export Wizard. Add the package to an SSIS Project and review the package created by Import Export Wizard.

So, this completes the implementation of Import and Export Wizard. I hope this helped you to understand the concepts of SSIS. I appreciate the time you dedicated for reading this post and your comments shall encourage me to write more posts. We will learn the other functionality of SSIS in our next posts.

For Further Information and Tutorials, VISIT My Website -PhpRinG-Tutorials

Getting Started with Adobe After Effects - Part 9: Keying

Tue, 21 May 2013 00:00:00 GMT

In this lesson we will look at how to pull keys (often called green/blue screening, chroma keying or simply keying). Firstly, please download some footage to practice with from the following link: [http://www.hollywoodcamerawork.us/greenscreenplates.html][1] Hollywood Camerawork is a great resource for training material. I chose the “hair detail” clip because you’ll often have to key hair and it can be tricky. Note that the files download in .zip format so you’ll need to extract it before you can import it into after effects. They come as an image sequence so when you go to import them inside after effects, choose any image and make sure you click the Sequence button. ![sequence button][2] Because image sequences are multiple images, they have no inherent duration. By default after effects plays image sequences at 30 frames per second. That may be fine for you, though HDTV is 25 frames per second so I have set my default to 25 in the preferences (edit > preferences > import). ![image sequences][3] You can also change the footage from the default import by right clicking on footage in the project panel and clicking interpret footage > main. Drag the footage into the new composition icon. If your footage looks stretched or squashed, you can change the composition settings with control + K. I used the HDTV 720 25 preset. Head over to the effects panel and expand the keying section, we have a lot of different options. ![effects panel][4] Drag the **colour key** effect onto the footage. Click on the eye dropper next to the key colour and select the green that we want to remove in the composition window. ![colour key][5] Nothing appears to happen because although we have chosen which colour to key, we also need to choose a tolerance. Increasing the tolerance increases the range of colours (next to our chosen colour) that will be removed from the image. Have a play with the settings and see what works best. It’ll be impossible to get a good looking image just by playing with the tolerance. The feather and thin settings can help us refine our key. I’m sure you all know what the feather setting does, and the thin setting erodes pixels adjacent to already keyed pixels. To help you tweak settings you can choose to view the alpha to make sure that we don’t have holes in our matte (alt+4). You can also turn on transparency grid by clicking this button or create a coloured solid behind the footage layer. ![toggle transparency grid][6] It’s difficult to pull a good key with the colour key, so remove it from our footage by selecting it in the effects panel and pressing delete. Let’s use the Keylight effect which is a much more powerful keyer. ![keylight effect][7] You can choose which colour you’d like to key using the screen colour. I simply picked the green and the result looks good already. This is a combination of keylight being a really great keyer and the footage being evenly lit with a smooth greenscreen. Let’s tweak some of the settings: Screen pre blur is a setting that blurs the original image, pulls the key from that blurred image but uses the unblurred image for the RGB channels. This creates softer edges. Additional settings can be found in the dropdown menus. Screen matte is particularly important, go ahead and expand it. ![additional settings][8] When tweaking the settings it’s often much easier to be looking at the alpha channel rather than the red green blue channels (Alt+4 is the shortcut). - Clip Black: this setting will remove transparent parts of the image that have alphas less than this value. For example if I had a green tracking marker in the background that I wanted to remove and it was 19% opaque, I could set the clip black to 20 (this setting is measured as a %) and the tracking marker would be completely removed. Note that when working in 8 bits per channel (as we are), the alpha in our info panel is not measured as a %, it is measured as a value from 0 to 255). - Clip White works the same as clip black but instead of being a threshold that lowers transparency, it is a threshold that increases transparency. - Clip rollback is a value that removes any changes to the two above settings. This is useful for tweaking the intensity of both settings at once. - Screen shrink is similar to edge thin in the colour key, it erodes or expands the matte. If you have a green fringe around your image, screen shrink can come in handy. - Screen softness controls the softness of your edge. Increasing this in subtle amounts is a good way to improve jagged edges. - Replace method is a setting for spill suppression. Often when a character is in a green screen studio, the green may spill onto them (especially if they are wearing white). Spill suppression attempts to remove the spill. The default setting of soft colour does a good job but sometimes I find it can be quite grainy, which forces me to use the source setting. In our specific example there is no spill on the character so these settings won’t make much of a difference. **Some general tips for keying:** Often the softness required to key hair will not be suitable for other parts of the image such as clothes or skin. In this case you will need to pull separate keys, one for the hair and one for the other parts. Each key will have to be on a separate layer. Separate each area with masks and use feathering to blend them together. If you have holes in your character but don’t want to crank the clip white to fill the holes (this might decrease the fidelity of your edges) you can always duplicate the layer, remove the keylight and create masks to fill the gaps. In the next lesson, we will look at general workflow practices and rendering our files. [1]: http://www.hollywoodcamerawork.us/greenscreenplates.html [2]: http://media.beyondrelational.com/images.ashx?id=f7b6ebfa16554ace8c950b8dcc318569&w=628&h=0 [3]: http://media.beyondrelational.com/images.ashx?id=65867f0ca0a34d5dbb0ec3ed33c24a15&w=628&h=0 [4]: http://media.beyondrelational.com/images.ashx?id=2d6102bc905742aab2f952ad8b6e9937&w=628&h=0 [5]: http://media.beyondrelational.com/images.ashx?id=da44dac93e894238b00979de3ebaf2e4&w=628&h=0 [6]: http://media.beyondrelational.com/images.ashx?id=5ec81446578744109206f2aec0f74071&w=628&h=0 [7]: http://media.beyondrelational.com/images.ashx?id=ed2d1f276cca40c7bb808bf28c0426b3&w=628&h=0 [8]: http://media.beyondrelational.com/images.ashx?id=a3c0b4ee27ee48edac71a9ba26083c5e&w=628&h=0

Calling https from Windows Phone 8 app

Mon, 20 May 2013 00:00:00 GMT

Hello All,

I am facing issue "Method Not found" while calling wcf service which is on https, it works fine with http. Not even wcf, I cant make call to ashx or asmx or aspx...

Any help will be appreciated

thanks
Imran

#0265 - SQL Server - An introduction to IDENTITY columns

Mon, 20 May 2013 00:00:00 GMT

One of the most frequently used, but perhaps one of the least understood feature of Microsoft SQL Server has to be the IDENTITY columns.

IDENTITY columns are any columns that are used as identifier columns. They create an automatically incrementing identification number on a given table.

Over the course of time, I have seen many novice engineers make statements that imply certain common implementations of IDENTITY to be the default and only behavior that these columns exhibit. For example, it’s a common implementation to have the IDENTITY value increment by 1, but it is not the only implementation possible. As we will see in the upcoming posts, increments can have a lot of different flavours.

An Introduction

In order for Microsoft SQL Server to create an automatically incrementing identification number the IDENTITY property consists of two attributes:

SEED – this specifies the value that should be used by the first record inserted into the table
INCREMENT – this specifies the increment or the value to be added to the seed to determine successive identity numbers

Here are some general rules around IDENTITY columns:

A table may have only one column defined with the IDENTITY property
The column must be defined as NOT NULL and must not have a DEFAULT constraint defined on it
Valid data types for IDENTITY are decimal, int, bigint, numeric, smallint and tinyint
Default value for both seed and increment is 1

The question therefore comes up is:

Why are IDENTITY columns useful?

Why IDENTITY?

Generally, IDENTITY columns are used as primary key values. When I created my first table that used the IDENTITY property, I was unaware of its significance – I had used it because it was part of the design standard used by the product. Upon study, I later realized the following:

By their very nature, IDENTITY values make for “non-intelligent” keys
IDENTITY values are auto-generated when a record is inserted. What this means is that IDENTITY ensures that the values generated are naturally ordered – making them ideal candidates for a clustered index

Generating the clustered index on an IDENTITY column essentially means that the logical ordering of records essentially remains the same
Similarly, record retrieval is faster and key management also becomes easier

Because IDENTITY can be used on BIGINT datatypes, it has a very vast addressing capacity – generally sufficient for most OLTP systems

These are the primary reasons why IDENTITY columns have become an important and commonly used design choice for integer-based primary key values.

From my next post, I will be presenting a series on the various aspects of IDENTITY and myths that surround the IDENTITY column. Are there any questions that come to your mind when working with IDENTITY columns? Do leave a note and I will try my best to answer them as I move along.

Until we meet next time,

Be courteous. Drive responsibly.

Always choose proper datatype

Mon, 20 May 2013 00:00:00 GMT

I often see that some junior develepers forget an important thing while designing a table. They choose wrong datatypes to store data ie VARCHAR to store DATETIME or DECIMAL values. This is mostly identified either when verifying the design or giving some unexpected output.

I understand that when you import data from various other sources, you may need to use VARCHAR datatypes to accomadate some formatted datetime/float values. This is ok when you do it for a staging table which will get processed later to move poper set of data to Production table. But when you design a table which accepts proper data for each of the datatype, never use wrong datatypes.

Consider the following example

create table #testing(id varchar(10), date_of_birth varchar(10),amount varchar(20))

insert into #testing(id,date_of_birth,amount)
select '10','11/06/1982','2000.50' union all
select '107','19/08/1966','11500.00' union all
select '22','01/01/1945','7500.00' union all
select '56','13/12/1986','2000.00' union all
select '78','10/10/1979','2000.75' ;

select max(id) as id,max(date_of_birth) as date_of_birth,max(amount) as amount from #testing

The result is

id    date_of_birth   amount 
----  -------------   -------
78    19/08/1966     7500.00

The result is entirely wrong all because of choosing the wrong datatype and applying the aggregated functions over there.

So except for staging tables, always choose proper datatype to store data

A network-related or instance-specific error occurred while establishing a connection to SQL Server- Error while connecting SQL Server

Sun, 19 May 2013 18:29:00 GMT

Most of developers, DBA or any persons who are working with SQL server, they have seen the error which i mentioned in title. From the error you can easily imagine there is a something error in Server name which are trying to connect server. But when this sql server instance working locally then something is network issue to connect with this server or it should some configuration issue when trying to connect this sql server instance from other server.

This is most common error or may be you have seen most of time and faced too. You may the solution but even i am writing here for the solution which have applied at my end. Before moving ahead, Please look the error in detail,

Cannot connect to Server
------------------------------

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

Let me share screen shot too, you have seen most,

Error is recommending to configure remote server to allow to connect from other server. As i know then we should go ahead with following steps,

1. Please make sure SQL Browser service is enabled and running. If not, then enable it from SQL Server configuration manager.

2. Make sure TCP/IP is enabled for that SQL Server instance.

3. Make sure “remote access” value must be turned on from sp_configure. If not then change it with following query,

exec sp_Configure 'remote access',1
go
reconfigure
go

4. Make sure Ports are opened for SQL Service and SQL Browser if Windows firewall is turned on.

SQL Server default port is 1433. if you changed it then check port from TCP/IP properties and IP Addresses tab.

Add port for SQL server database service.

Add port for SQL server Browser service.

After completion of all above step, we will able to success to connect that sql server instance from other server. This issue you may faced and you may applied the solution too. it will be fine and valuable to share your thought here if i missed something and need some additional steps.

Passing dynamic column names to SELECT statement in the HTML var

Sun, 19 May 2013 00:00:00 GMT

Hello, I have gone through the below posting and its very useful stuff. Now i got into a situation where my column names changes dynamically. In such case how can i pass them in the SELECT of HTML variable. Please help. http://beyondrelational.com/modules/2/blogs/106/posts/11108/ssis-using-db-mail-to-send-html-format-mail.aspx Thank you.

Technology Is No Longer the Analysis Bottleneck for Business Users

Thu, 16 May 2013 00:00:00 GMT

The business computing always occurs in enterprises, which plays an important role in enterprises operations, and mainly handled by the business users. The key business computing is usually characterized with timeliness and weak predication. There is no ready answer in the ERP, CRM, and other business systems. For example, find out the materials whose inventory level is below the warning limit for 3 consecutive days in the recent 2 weeks. Among those materials, which are used to produce the top 5 Halloween best-sellers? In another example, find out the agents contributing to the 60% of the total sales. Assign those agents in proper order to the salespersons whose performances rank ahead of others in the year-end sales promotion.

Most business users have no IT experience. They can only rely on the business spreadsheet software to complete such computing. However, the traditional business spreadsheet software is weak in calculation, which will bring about errors in the calculation process.

Considering the above examples, you must filter or sort the summary values and detail values. But, the traditional business spreadsheet software is only fit for the 2D sheet computing, and lacks the further processing capability on the grouped data. The manual operations are usually required to implement computing with such spreadsheet and the process is error-prone.

In order to solve the above problems, the set operation capability to some degree is also required. But the business spreadsheet software has no concept of sets, and no business association between cells is available. The set operation can only be handled via the complicated search & find formula, which adds to the computing difficulty virtually, and thus a higher technical competence is required.

In this case, the sheets from multiple data sources need to be merged. But in the traditional business spreadsheet software, users can only copy the data, and is unable to merge the data at various levels or in the uncertain orders according to a certain relation. This is usually achieved through manual operation or complex formula.

esCalc, the desktop BI software, introduces the concept of “homocell”. It is the ideal spreadsheet to solve the above-mentioned problems. The core concept of esCalc is to treat the cells of various business statuses differently, and treat the cell of the same business status uniformly. In esCalc, filtering the summary value will not affect the detail data. The formula entered in the detail data section will be copied and pasted to other homocells automatically, while not the summary section. The homocell relationship between cells ensure both of them being in a same set, so users can seek the intersection set and union set conveniently; Multiple sheets can be merged through the join or union operation according to the homocell relation, even for the data at various levels and in the uncertain orders.

Visual Studio 2008 Report RDL line object on top of a chart not appearing outside of development

Thu, 16 May 2013 00:00:00 GMT

I have some line objects where i base their visibility on a date. These lines are placed on top of a chart (line graph) object. The lines appear and disappear exactly as I want them to when I am in development. When I deploy the RDL the lines are hidden behind the graph, so they aren't appearing. I tried changing the zindex in the xml, tried putting the lines in another rectangle and trying to make that rectangle be the highest layer by giving it a high zindex. Nothing is working. Is it possible to place a line object directly over a chart area so it won't be hidden? I am confused why it works in development but not when deployed. Thanks for any help on this.

#0264 - SQL Server - How to check if a database belongs to your product?

Thu, 16 May 2013 00:00:00 GMT

One question that I often face from teams is:

Given a set of SQL server databases, how do we identify that a given database belong to our product?

There are various mechanisms that can be used, depending upon how the underlying database design is. I have two mechanisms that I use frequently.

Check for the existance of a particular table (a table that stores your product version or some table which holds product-specific static data)

Many database designs (especially those derived from legacy systems) are such that all tables have a column that is used for various generic purposes (to indicate whether a particular record was modified as part of a data clean-up, or a GUID column to facilitate replication, etc)

In such database designs, one can check if all tables have the particular column. Pseudo-code to achieve this check efficiently is shown below:

USE AdventureWorks2008R2 ;
GO
IF NOT EXISTS ( SELECT  st.*
                FROM    sys.tables AS st
                        LEFT OUTER JOIN sys.columns AS sc ON st.object_id = sc.object_id
                                                             AND sc.name  = 'BusinessEntityId'
                WHERE   st.type = 'U'
                        AND st.object_id IS NOT NULL
                        AND sc.object_id IS NULL ) 
    BEGIN
        PRINT 'All tables contain the column - BusinessEntityId.' ;
    END
ELSE 
    BEGIN
        PRINT 'Some or all tables do not contain the column - BusinessEntityId.' ;
    END
GO

The benefit of the above query is that it queries the meta-data and is therefore faster than a custom iterative mechanism

I trust this post was helpful. I would also like to know what is the method you use for detecting your product database?

Until we meet next time,

Be courteous. Drive responsibly.

Getting Started with Windows Azure - Part 8: Deploy ASP.NET MVC application to the Windows Azure Cloud using Visual Studio 2012

Thu, 16 May 2013 00:00:00 GMT

**Introduction:** In this article, we are going to see how to deploy as ASP.NET MVC application to the Windows Azure Cloud services from Visual Studio 2012 IDE. In our earlier article we have seen how to analyze and capture a Virtual Machine Image and store it to the Cloud VHD. ASP.NET MVC 3 gives a powerful, patterns and framework based way to build dynamic websites that enables a clean separation of data and the views and that gives you full control over markup for enjoyable, agile development. ASP.NET MVC includes many features that enable fast, Test driven Development friendly development for creating sophisticated applications that use the latest web standards. ASP.NET MVC on windows Azure provides a greatest support with the web roles that are available to make the development much easier and quicker. Let us see the step-by-step process on how to develop an application and deploy the application directly to the Windows Azure Cloud Services. **Steps:** Open Visual Studio 2012 IDE in administrator mode and create a new Windows Azure Cloud Project by Clicking on New Project and selecting Cloud template tab as shown in the screen below. Note to open the Visual Studio Integrated Development Environment (IDE) in administrator mode as Microsoft Windows Azure projects need to take some control over the emulators, which needs the administrator privileges. ![new project][1] Clicking on OK and proceeding to the next step, we can see a window with the list of available roles that can be used across different programming languages. Since in our example we are going to see how to develop an ASP.NET MVC3 app that will be deployed to the Microsoft Azure website, select the ASP.NET MVC3 web role and move it to the Windows Azure Cloud Services Solution to proceed to the next step as shown in the screen below: ![Windows Azure Cloud Services Solution][2] We can see a new windows which the list of options that are required to configure the application, here we can select the application is going to be an Empty app, Intranet App or an Internet App. Since we are going to make it available over the internet we will select the application as Internet application and click on OK as shown in the screen below. If we need to create a unit test we can select the option that is available as a checkbox in the same screen. ![project template][3] We can see the application project is created and the list of files will be loaded to the solution explorer. Now we will make some small changes in the application to showcase the application that is designed for this sample. So make changes in the views and build the application to view it locally as shown in the screen below: ![changes in view][4] Now our next step is to start packaging and upload the application to the Windows Azure cloud services, to start with first right click on the application project and select the option Package. First we will package the application and upload the package to the Windows Azure Cloud Services using the available tools. Normally on creating a package, we will get two files namely a configuration file and a package file as shown in the screen below: ![package][5] Clicking on Package option will get a small popup asking for the package configuration options like selecting the mode of the build configuration and the type of the service configuration to select based on our requirement as shown in the screen below: ![package configuration][6] After selecting the appropriate option click on Package will start preparing the package (Service and Configuration files), we can see the status of the package preparation at the output window of the Visual Studio 2012 IDE. After successful creation of the package we can see a folder will be opened with the 2 files created for the application. Basically the folder will be created in the bin folder of the application project under the build option mode; here we selected the Release version so the package files will be created in the Release folder as shown in the screen below: ![Release folder][7] Now we can see the package is ready to use, our next step is to publish the package to the Microsoft Windows Azure Environment. To start with login to the portal using the link [www.windowsazure.com][8]. On successful login select the option New Windows Azure Cloud Services as shown in the screen below. In the Windows Azure Cloud Services provide the valid Service name and select the appropriate region name and subscription to start creating the environment. ![valid service name][9] Now we can see the services are created and clicking on the service name, we can see the option to start uploading the package. Click on the upload production package will open windows where we need to enter the deployment package name and select the appropriate package (i.e. the package file and the service configuration file) as shown in the screen below: ![upload a package][10] Once all the files are selected click on proceed to process further, we can see the upload starts and it will take some time to provision based on the size of the package we deploy. Once the package is uploaded successful, we can see the environment ready and accessible as shown in the screen below: ![environment ready][11] To access the application navigate to the URL [http://f5debugbrmvc.cloudapp.net/][12] and we can see our application online which is hosted on the Windows Azure Cloud environment. The URL can be fetched from the Dashboard of the application, which we uploaded as shown in the screen below: ![URL][13] **Conclusion:** Therefore, in this article we have seen how to upload an ASP.NET MVC application, which is designed and developed in Visual Studio 2012 IDE with the help of inbuilt packaging tools for Windows Azure. [1]: http://media.beyondrelational.com/images.ashx?id=f313d4690c544bd191d7bf2b4e6065fd&w=628&h=0 [2]: http://media.beyondrelational.com/images.ashx?id=46a5c0634ae34f0b8b99a64ac76a7470&w=628&h=0 [3]: http://media.beyondrelational.com/images.ashx?id=0d282ad99cc74ed3944d3a0fa09a563b&w=628&h=0 [4]: http://media.beyondrelational.com/images.ashx?id=e8288e1f858b468b9bbec02d4d9ba700&w=628&h=0 [5]: http://media.beyondrelational.com/images.ashx?id=6e8982c13f224ad7a337ba26520b2aae&w=628&h=0 [6]: http://media.beyondrelational.com/images.ashx?id=0c930427d7c94602868a2137874464e1&w=628&h=0 [7]: http://media.beyondrelational.com/images.ashx?id=06955dd788a54a59b4616a335daaad34&w=628&h=0 [8]: www.windowsazure.com [9]: http://media.beyondrelational.com/images.ashx?id=c62dc677659e4459a75a1a3781c655a0&w=628&h=0 [10]: http://media.beyondrelational.com/images.ashx?id=6cb96dc62f9d4862a75f027c350f2ebd&w=628&h=0 [11]: http://media.beyondrelational.com/images.ashx?id=51cf80d9e0174c60b7f09e17a27e8b85&w=628&h=0 [12]: http://f5debugbrmvc.cloudapp.net/ [13]: http://media.beyondrelational.com/images.ashx?id=8af00f66fbcc4e70a7ed07a17a74a418&w=628&h=0

Ehh, What’s up, SqlJason?

Wed, 15 May 2013 23:30:00 GMT

It’s been more than a month since the last post and you might be wondering – what’s up with SqlJason? Well, let me tell you that the last one month has been really busy and I thought of filling you guys in with some of the juicy stuff that’s been happening!

1) Speaking Events
Last year April was when I started my first speaking assignment in the USA and it was great to have so many different opportunities to present last year. This year April also turned out to be the starting month for my presentations, and I can’t believe that I already presented at five different places this month.

The PASS BA Conference was the first stop, and this was my first experiencing presenting in a big stage. Looks like it went good from the session evaluations (got a 4.6, my session ranks in the top 15 out of the 60+ sessions in the conference)

The attendance was also pretty decent, managed to pull in 98 people which is the highest I have ever got in any of my sessions.

I had lots of fun with my #sqlfamily and also got to meet a lot of new people. Overall, a very fun experience and I am definitely going to attend PASS BA Conference next year. Next stop was SQL Saturday Chicago and this was just the next day after PASS BA Con. After all the partying and the awesome speaker dinner at the previous night, speaking was always going to be difficult task but luckily, everything including the demos went fine. I also got to see some great sessions by Mark V & Doug Lane, Julie Koesmarno, Leonard Murphy. In short, another great experience and a very well organized event. Next on the list was SQL Saturday Jacksonville which took place 2 weeks after the Chicago event. The Jacksonville event was one of my favourites from last year and it turned out to be even better this year as it was the first time all four of us CBIG buddies (me, Javier, Melissa and Rafael) got selected to speak at the same event. And I must say Jacksonville didn’t disappoint me, great crowd as usual and highly interactive (and very generous with the session evals too hehe). A couple of days later, I started off for my first user group speaking event of the year – Triad SQL BI User Group at Winston Salem. A big thanks to the user group leaders (Katherine Fraser and Doug Purnell) for giving me the opportunity, really enjoyed interacting with the user group members there. The next speaking event was the Carolina Code Camp 2013 which was in Charlotte itself (finally a home event! ). Unlike the other four events, the majority of the attendees was from a non-SQL Server background. So it was fun speaking to a different profile of attendees and I must say that this was one of those sessions where the attendees laughed at all of my jokes (either they got all of my jokes or were being REALLY nice with me ).

I remember someone saying that event speaking is really addictive, I couldn’t agree more! Just to prove that, I have 2 more events coming up in the next week:-

a) SQL Saturday Atlanta – I will be speaking on the topic “GeoSpatial Analytics using Microsoft BI” at 1 pm, this weekend. This is going to be another really great event with registrations hitting record levels for a SQL Saturday (800+ including waiting list). If you are coming for the event, definitely drop by and introduce yourself, I would love to meet you.

b) Mariner Webinar – If you are not able to attend my session at SQL Saturday Atlanta, don’t worry. I am doing the same session (“GeoSpatial Analytics using Microsoft BI”) as a Webinar for my company - Mariner on May 21, 2013 12:00 PM-01:00 PM Eastern. You can register for this FREE event by clicking on this link - Click to Register

2) CBIG Updates

As some of you recall, me and three others (Javier, Melissa and Rafael) started a new BI user group this year in Charlotte – CBIG. We are having a great run with attendees steadily increasing from 35 in the first meeting to 50+ in the last meeting (65 being the highest as of now). Even though five meetings are too early to call the user group a success, the popularity of CBIG is evident from the 87 registrations (including waiting list) we received in our fifth meeting (where our seating capacity is only 60). We had already moved from the initial meeting room after our second meeting (where our seating capacity was 30) and now, due to the increasing number of registrations, we are moving our venue from New Horizons to the Microsoft Campus (which has a seating capacity of 85) in July. That said, the numbers are secondary. The primary aim for us is to cultivate a culture in CBIG which mainly revolves around the community spirit as well as a passion for technology. If you are in or around Charlotte, don’t miss out on our user group meetings on the first Tuesdays of every month. We have got meetings lined up till September as of now and the RSVP links can be found at http://charbigroup.com/. Also, don’t hesitate to contact me if you want to speak at our user group, we are always looking for speakers!

3) SQL Saturday Charlotte – BI Edition Announced

And yes, we have also announced the date for SQL Saturday Charlotte – BI Edition. The event will take place on Saturday, October 19, 2013 (which happens to be the Saturday after the PASS Summit 2013, which also is in Charlotte). The planning is going on full steam and we are confident that we will have a great event laid out for all of you guys. Ensure that you register today to extend your PASS Summit 2013 experience.

4) And now the BIG one - Personal Updates

For those of you who know me personally, this might be old news. And for the others, you might recall me saying at the start of the year that I might not match up to my previous year’s activities this year due to personal reasons. Well, the personal reason is given below.

Yups, I am going to be a father to a baby girl this August 12 and I have no shortage of adjectives to describe my condition right now – excited, anxious, nervous, hyperactive, charged and so on. I am looking forward to those sleepless nights and all the other joys that come with parenthood. For the moment, I am going to stop writing and enjoy a good night’s sleep when I can.

SSIS Execute Process Task

Wed, 15 May 2013 00:00:00 GMT

Hi... I am getting an error while using Execute Process Task, The senario is that i have an application called RG, where the CVS file had to upload to get the download with certain codes attached to it which is required for analysis. My collegue has written a batch file as cd E:\Grouper\Executables\2012_13_LP HRGGrouper -i E:\Grouper\Input\2012_13\AE_Input.csv -o E:\Grouper\Output\2012_13\AE_Output.csv -d E:\Grouper\RDF\2012_13\EM.rdf -l em_v45lp -h which runs fine, but when i try to replicate the same, it gives the error as "The process exit code was "1" while the expected was "0". can you please advise what could be the problem... Thanks

Display style of field based on expression

Tue, 14 May 2013 00:00:00 GMT

Hi ,

I am having difficulties to display color style of field in report based on expression.

input field datatype : bigint

input field value format example : 20120111 (it'sYYYYMMDD)

I need to display this field as "green" id it is greater or equal to date "01/01/2012".i wrote following expression and try other ways but no success.

=IIF(

(

FormatDateTime(

MID(StrConv(Fields!Service_From_Date.Value,vbProperCase),1,4) + "-" +

MID(StrConv(Fields!Service_From_Date.Value,vbProperCase),5,2) + "-" +

MID(StrConv(Fields!Service_From_Date.Value,vbProperCase),7,2),DateFormat.ShortDate)

)

>= (FormatDateTime('2012-01-01', DateFormat.ShortDate)),"Green","Black")

Thanks in advance.

Regards,

Pavan Patel

8 important C# Interview questions on IL code, JIT, CLR, CTS, CLS and CAS

Tue, 14 May 2013 00:00:00 GMT

Introduction

This article will revise 6 important c#/.NET interview questions around concepts like IL code, just in time compiler, common type system; common language specification, code access security and common language run time. Now many .NET developers would frown at the article saying this is basic and simple.

But what I have seen is as you climb up the ladder you get engrossed in to projects, production issues, or you start doing too many non-technical activities and you start forgetting fundamentals. It’s not that you do not know, you have just forgotten. And if you are not able to answer questions around these topics how much ever senior you are, it sends a very bad message to the interviewer.

What is IL code?

IL code is a partially compiled code.

Why it is partially compiled and why not full compiled?

When a compiler compiles a code he takes in to consideration things like operating system version, hardware etc and then compiles a optimized code as per those environmental data.

Now let’s say your compiler has compiled an optimized code as per “Windows 98” operating system. Now this code when run on windows server will not probably run in a optimized manner.

So what .NET does it only compiles the code partially. Later when the code runs the compiler kicks off, detects the environment and does a fully optimized code compiler as per that operating system and hardware.

So every time compiling on the fly can lead to performance issues?

Once the code is compiled it’s maintained in cache. So the performance hit is only for the first time when .NET application runs.

Who compiles IL code?

JIT (Just in time compiler) compiles the IL code to a fully compiled format.

Can we see IL code?

Yes, we can see the IL code by using the ILDASM tool.

What is use of CTS?

In .NET there are lots of languages like C#, VB.NET, VF.NET etc. There can be situations when we want code of one language to be called in other language. In order to ensure smooth communication between these languages the most important thing is data type compatibility. CTS (Common types system) ensures that data types defined in two different languages get compiled to a common data type.

So “Integer” data type in VB6 and “int” data type in C++ will be converted to System.int32, which is data type of CTS.

What is the use of CLS ?

CLS is a subset of CTS. CLS is a specification or set of rules or guidelines. When any .NET code adheres to these set of rules it can be consumed by any .NET language.

For instance let’s say you have a c# class which has two variables defined “I” and “i”. In other words one variable is in capital and the other is in small.

Now this is perfectly valid in c# , but if this class is consumed in VB.NET it will go for a toss. Because VB.NET is not case sensitive and will not know which “I /i” variable to choose. So if you want this c# code to be consumed in VB.NET you need to define variable with two different names. This is one of the rules of CLS, that you should not have variable with same name with case sensitive.

So in this way we have number of CLS rules which if followed will ensure that your code can be consumed in any .NET compatible language.

What is CAS?

CAS is the part of .NET security model which determines whether or not a particular code is allowed to run and what kind of resources can the code access.

What is CLR ?

CLR (Common language run time) is the heart of.NET framework and it does 4 primary important things:-

· Garbage collection

· CAS (Code Access security)

· CV (Code verification)

· IL to Native translation.

I have created this nice video here which talks about what kind of questions are asked in c# interviews. http://www.youtube.com/watch?v=3PXspVcSSKE

You can also visit my site c# and .NET interview questions where i have created range of videos on various topics of c# from interview preparation aspect.

8 important C# Interview questions on IL code, JIT, CLR, CTS, CLS and CAS

Tue, 14 May 2013 00:00:00 GMT

Introduction

What is IL code?

IL code is a partially compiled code.

Why it is partially compiled and why not full compiled?

When a compiler compiles a code he takes in to consideration things like operating system version, hardware etc and then compiles a optimized code as per those environmental data.

Now let’s say your compiler has compiled an optimized code as per “Windows 98” operating system. Now this code when run on windows server will not probably run in a optimized manner.

So every time compiling on the fly can lead to performance issues?

Once the code is compiled it’s maintained in cache. So the performance hit is only for the first time when .NET application runs.

Who compiles IL code?

JIT (Just in time compiler) compiles the IL code to a fully compiled format.

Can we see IL code?

Yes, we can see the IL code by using the ILDASM tool.

What is use of CTS?

So “Integer” data type in VB6 and “int” data type in C++ will be converted to System.int32, which is data type of CTS.

What is the use of CLS ?

CLS is a subset of CTS. CLS is a specification or set of rules or guidelines. When any .NET code adheres to these set of rules it can be consumed by any .NET language.

For instance let’s say you have a c# class which has two variables defined “I” and “i”. In other words one variable is in capital and the other is in small.

So in this way we have number of CLS rules which if followed will ensure that your code can be consumed in any .NET compatible language.

What is CAS?

CAS is the part of .NET security model which determines whether or not a particular code is allowed to run and what kind of resources can the code access.

What is CLR ?

CLR (Common language run time) is the heart of.NET framework and it does 4 primary important things:-

· Garbage collection

· CAS (Code Access security)

· CV (Code verification)

· IL to Native translation.

I have created this nice video here which talks about what kind of questions are asked in c# interviews. http://www.youtube.com/watch?v=3PXspVcSSKE

You can also visit my site c# and .NET interview questions where i have created range of videos on various topics of c# from interview preparation aspect.

8 important C# Interview questions on IL code, JIT, CLR, CTS, CLS and CAS

Tue, 14 May 2013 00:00:00 GMT

Introduction

What is IL code?

IL code is a partially compiled code.

Why it is partially compiled and why not full compiled?

When a compiler compiles a code he takes in to consideration things like operating system version, hardware etc and then compiles a optimized code as per those environmental data.

Now let’s say your compiler has compiled an optimized code as per “Windows 98” operating system. Now this code when run on windows server will not probably run in a optimized manner.

So every time compiling on the fly can lead to performance issues?

Once the code is compiled it’s maintained in cache. So the performance hit is only for the first time when .NET application runs.

Who compiles IL code?

JIT (Just in time compiler) compiles the IL code to a fully compiled format.

Can we see IL code?

Yes, we can see the IL code by using the ILDASM tool.

What is use of CTS?

So “Integer” data type in VB6 and “int” data type in C++ will be converted to System.int32, which is data type of CTS.

What is the use of CLS ?

CLS is a subset of CTS. CLS is a specification or set of rules or guidelines. When any .NET code adheres to these set of rules it can be consumed by any .NET language.

For instance let’s say you have a c# class which has two variables defined “I” and “i”. In other words one variable is in capital and the other is in small.

So in this way we have number of CLS rules which if followed will ensure that your code can be consumed in any .NET compatible language.

What is CAS?

CAS is the part of .NET security model which determines whether or not a particular code is allowed to run and what kind of resources can the code access.

What is CLR ?

CLR (Common language run time) is the heart of.NET framework and it does 4 primary important things:-

· Garbage collection

· CAS (Code Access security)

· CV (Code verification)

· IL to Native translation.

I have created this nice video here which talks about what kind of questions are asked in c# interviews. http://www.youtube.com/watch?v=3PXspVcSSKE

You can also visit my site c# and .NET interview questions where i have created range of videos on various topics of c# from interview preparation aspect.

8 important C# Interview questions on IL code, JIT, CLR, CTS, CLS and CAS

Tue, 14 May 2013 00:00:00 GMT

Introduction

What is IL code?

IL code is a partially compiled code.

Why it is partially compiled and why not full compiled?

When a compiler compiles a code he takes in to consideration things like operating system version, hardware etc and then compiles a optimized code as per those environmental data.

Now let’s say your compiler has compiled an optimized code as per “Windows 98” operating system. Now this code when run on windows server will not probably run in a optimized manner.

So every time compiling on the fly can lead to performance issues?

Once the code is compiled it’s maintained in cache. So the performance hit is only for the first time when .NET application runs.

Who compiles IL code?

JIT (Just in time compiler) compiles the IL code to a fully compiled format.

Can we see IL code?

Yes, we can see the IL code by using the ILDASM tool.

What is use of CTS?

So “Integer” data type in VB6 and “int” data type in C++ will be converted to System.int32, which is data type of CTS.

What is the use of CLS ?

CLS is a subset of CTS. CLS is a specification or set of rules or guidelines. When any .NET code adheres to these set of rules it can be consumed by any .NET language.

For instance let’s say you have a c# class which has two variables defined “I” and “i”. In other words one variable is in capital and the other is in small.

So in this way we have number of CLS rules which if followed will ensure that your code can be consumed in any .NET compatible language.

What is CAS?

CAS is the part of .NET security model which determines whether or not a particular code is allowed to run and what kind of resources can the code access.

What is CLR ?

CLR (Common language run time) is the heart of.NET framework and it does 4 primary important things:-

· Garbage collection

· CAS (Code Access security)

· CV (Code verification)

· IL to Native translation.

I have created this nice video here which talks about what kind of questions are asked in c# interviews. http://www.youtube.com/watch?v=3PXspVcSSKE

You can also visit my site c# and .NET interview questions where i have created range of videos on various topics of c# from interview preparation aspect.

Getting Started with Adobe After Effects - Part 8: Channels and Blending Modes

Tue, 14 May 2013 00:00:00 GMT

In this lesson, we will look at what channels our images are made from and how we can use blending modes inside of after effects. Images that display on our monitors are made of 3 channels, red, green and blue. Import a photo that you have on your computer (or from the internet) into after effects. Drag it into the New Composition button (labelled 1 in below image). ![new composition button][1] Number 2 represents which channels we are currently viewing in the composition window. By default we are seeing the red, green and blue channels combined together to create the pretty image we see in the comp window. By combined I mean literally added together on top of each other. If you run your cursor over the image, you will see the values in the info panel (labelled 3). This shows the values of the RGB channels individually from 0 to 255. To help visualise this, press the show channel icon (labelled 2) and select the red channel (hotkey alt+1). The image will look very different and is black and white, rather than red. That’s because each channel is just a greyscale image that contains values for how much of that channel should be in the image. In the red channels case, we can see that there isn’t much red in the sky, but a fair amount in the road. ![scaling image][2] Press alt+2 to go to the blue channel, you’ll notice it’s very bright in the sky. Press alt+3 to go to the green channel. To replicate the process after effects uses to combine all channels, let’s separate the image into its individual RGB channels. Duplicate the image twice, and call one layer red, one green and one blue. Now search for the effect shift channels and drag it onto all three layers. This effect allows us to isolate one channel for each layer. To do this for the red layer, set the take green and take blue to off (this will leave just the red channel). Do the same for the green and blue layers. ![isolating channels][3] Note that if you adjust the red layer whilst the green and blue layers are on top (as it is in the above image) you won’t see any changes unless you turn the visibility of the green and blue layers off. Alternatively, you can solo the layer you’re working on with the labelled button. Once all three channels have been isolated on their respective layers, we need to pre-compose them otherwise after effects won’t acknowledge the effects when we use blend modes. To pre-compose a layer, select it and press control + shift + c. Also, make sure to name it and set the “move all attributes into the new composition” option, otherwise the shift channels effect will be outside the composition. ![pre compose][4] Repeat that process for the blue and green channels. Now let’s discuss blending modes (which are a very important part of compositing). If you don’t see the blend mode options, press F4 to toggle modes and it should appear. When you click on the menu to expand it, you’ll notice there are a ton of modes. ![processing channels][5] All of these modes are mathematical operations, some are self-descriptive. For example: the add mode simply adds the numerical channel data that we were looking at in the info panel with the layers underneath. If you have a single layer and set the blending mode to add nothing will happen because it has nothing to add to. You can look up online what each of the blending modes do but here is a quick summary of the most important modes: - Screen: Adds A over B but doesn’t let the result go past a value of 255 or white (for 8 bit images) - Multiply: Multiplies A x B, very useful for removing white values from A. - Soft light: A little more complex than add, screen or multiply but can create some great results. See here: [http://en.wikipedia.org/wiki/Blend_modes#Soft_Light][6] Remembering that we separated all three channels into separate layers, if we were to add these all back together we should have the same image we started with. Go ahead and set the top two layers to add (the bottom layer doesn’t need to have a blending mode). ![setting layers][7] Excellent, the image is exactly how it used to be. Now try turning off any one layer (the blue layer for example) and you’ll be able to see what the image looks like without any blue information. One other very important channel found in some images is the alpha channel. Like the red, green and blue channels it is also a greyscale or black and white channel that holds information. Unlike the R, G, B channels it does not determine colour information but opacity. If you import an image that has an alpha channel, you can view this information by pressing alt+4. The alpha information also appears in the info panel. A value of 255 alpha is opaque and a value of 0 alpha means completely transparent. ![set alpha information][8] *If you’d like to experiment with this image, type “png transparent image” into Google images and it will be the first image*. The way after effects takes this greyscale alpha channel and produces transparency is by multiplying the colour channels (R, G, B) by the alpha channel. Often this process is done for us and is called premultiplication. Sometimes in compositing, having a premultiplied image will produce incorrect results. There are ways to unpremultiply an image. An image whose colour channels have not been multiplied by the alpha channel are called “straight” or “unpremultiplied”. An interesting thing to note is that alpha channels exist even when we might not see them. For example you might not think that when we create a new solid (control + Y) that it comes with an alpha channel, but this is how after effects makes our layers transparent when we lower the opacity. The opacity (shortcut T when selecting a layer) is simply manipulating our alpha channel. Also when we isolate selections or create cut-outs with masks, we are manipulating the alpha channel. Create a mask on your solid layer and go to the alpha channel, you’ll see the information after effects is using to control which parts of the image are transparent and those that are opaque. I have added feathering to my mask to illustrate the point further. ![feathering the mask][9] In the next lesson we will look at chroma keying. [1]: http://media.beyondrelational.com/images.ashx?id=aa8a6fda3497425ebcc9f08062e8e17b&w=628&h=0 [2]: http://media.beyondrelational.com/images.ashx?id=8dd68cf0872e4e5da77ff46258ef756f&w=628&h=0 [3]: http://media.beyondrelational.com/images.ashx?id=477476ff3c384ff5a85b1b7d293a83ef&w=628&h=0 [4]: http://media.beyondrelational.com/images.ashx?id=a1771781e0994273a7fc0129b670871d&w=628&h=0 [5]: http://media.beyondrelational.com/images.ashx?id=067704a040614e768ba4203556db576c&w=628&h=0 [6]: http://en.wikipedia.org/wiki/Blend_modes#Soft_Light [7]: http://media.beyondrelational.com/images.ashx?id=e3f9cef4c9054396b6fe9d102331268e&w=628&h=0 [8]: http://media.beyondrelational.com/images.ashx?id=6f9243f1d99a4b5ba4240a7f16c1350e&w=628&h=0 [9]: http://media.beyondrelational.com/images.ashx?id=fbb1fd7f339246db8cdcc903ac26b90b&w=628&h=0

Related to Stored Procedure & User Defined Function's Pre-Compiled Concepts

Mon, 13 May 2013 00:00:00 GMT

Hi All,

Need Help on Bellow Mentioned Doubts

Q 1: What happens when we Submit / Execute the a Sql Statement (For Ex : select * from person )

i.e. what are the execution steps taken by the Sql Server behind the Scene.

Q 2 : What is Pre-Compiled Concept in Sql Server ?

Q 3: Is Stored Procedures in Sql Server are Pre-Compiled?

Q 4: Is User Defined Functions in Sql Server are Pre-Compiled?

Q 5: If UDF’s are not Pre-Complied , why there are not Pre- Compiled?

I Googled the above Q 3 & Q 4 , some where they say Stored Procedures are Pre-Complied but User Fined Functions are not Pre-Complied they compile only in execution time ( which indirectly say’s that UDF’s are compiled every time ? And Any Performance Degradation of UDF’s against SP’s)

I am really bit confusing between both SP’s and UDF’s Pre-Compilation concepts.

Page break inside group...

Mon, 13 May 2013 00:00:00 GMT

hi Divya, below is the link of my problem.Can u help me on that. [link text][1] looking forward for your quick response. [1]: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/91caa6e0-f73b-4436-9ac1-7b332ee85026/#91caa6e0-f73b-4436-9ac1-7b332ee85026 regards Msbialearner