HTML to Word Document in .net with simple trick

Dear Friends,
Recently my friend got a new task to generate a word & PDF document at runtime from FCK Editor and he was in real hurry, so he told me a sample for him. I tried with a simple which I am sharing here.
I created a resume layout with FCK editor. Please find the below screen and code.


I hope you will also enjoy.

you can download code at


Thanks & Best Regards,
Rajat Jaiswal

How to check your SQL Server Performance using new Utility Performance Dashboard Reports ?

Hello friends,
I know after reading the post title you are very excited to know more, even I was excited too to know more for this topic which I like very much.
Microsoft Introduced Performance Dashboard Utility reports which help us in finding the cause of low performance of our SQL Server Database.
You can download the performance Dashboard utility reports setup from below link
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc
When you install this installer you will find a new folder is created with Performance Dashboard name in tools folder of Microsoft SQL SERVER (“C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard”)
Now when you open this folder you will get many RDL files.
Next question is how to use it so here we go.
1) Firstly run the script with name “setup.sql “ from Performance Dashboard folder (“C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard”)

2) Now open our SQL Server Management studio
3) Open object explorer
4) Right click on the server select reports option then select standard reports as shown in below fig

5) Now select Performance_dashboard _main.rdl report
6) When you select this report you will find following screen


Great it means you are able to see performance dashboard report.
The performance dashboard report gives you following result
1) First graph of report shows System CPU utilization (With the help of this you can find how much CPU utilization done by SQL)
2) Just Below the CPU utilization graph you can see current Activity (CPU consumption time, wait time) We can see detail with two parameter either by User Session or User request (When you click on the header of the grid you will get different report)
3) Below this we have miscellaneous information which will give basic information like number of database currently on server, active trace etc.
4) Waiting log just right side of CPU utilization graph which shows different wait type for SQL
5) Below that one more tabular grid there which shows historical records of SQL according to different criteria like waits, IO Statics
6) Expensive SQL report with different parameters like BY CPU, BY Duration, BY Logical Reads, BY Logical Writes etc.
Checks out there are many more feature reports.

So with these different reports we can find where we are lacking. Where is the problem?

My personal feeling is the utility is good to finding the performance counter but it will not provide any suggestion and solution to improve performance.
So why are you waiting?
Just download and enjoy the performance dashboard.
Have fun with reports in weekends: D.
Thanks & Regards
Rajat Jaiswal

Check Best Practices with SQL SERVER with Best Practices Analyzer (BPA)

Hello Friends,
We always heard that we should adopt Best Practices in SQL Server and most of the time we tried our best to adopt best practices in our work but the problem is who will analyze the best practices which we did for our database ,SQL Server.
Hmm, that’s the point my dear friends but don’t worry on this part also because Microsoft introduce BPA (Best Practice Analyzer).
I tried this tool recently it is easy to use. It scans your database, SQL SERVER and provides you the best way to adopt Best Practices with your current structure.
You can download the installer of this tool from http://www.microsoft.com/downloads/en/details.aspx?FamilyId=da0531e4-e94c-4991-82fa-f0e3fbd05e63
After install this BPA utility you can easily scan your SQL SERVER, Databases with this.

The BPA will provide you report of scan. The scan report will give you idea about what is missing, what need to do?
Below are some listed options which BPA raised after scan report
1) Backup is latest or is it out dated?
2) Database having any anomalies.
3) Database integrity check required or not?
4) Check Disk IO delay problem
5) Backup should be on separate volume
6) Bulletin\Administrators in sysAdmin Role
7) Set Page verify option
8) SQL Login Password policy violation
9) Database is not in full recovery mode
Although some of the warning in the above list which we can ignore according to our business need.
And many more issues to make SQL Server database robust just check below report snap.

As per my opinion the tool is good but need some more functionality or utility to manage and apply
Best practices. So why you people waiting just download, install and use.
Make your database robust with best practices.


Enjoy Best Practices.

Thanks & Regards
Rajat Jaiswal

Space issue of SQL Server Database how to claim free space of SQL SERVER ?

Dear Friends,

I have faced recently database size issue. In my current project I need to add a column bit field in table and update the bit field conditionally.

The table contains 547,173,777 rows. Before adding the column the database size in 177 GB and after adding and updating the Bit column for the table the database size increased 223 GB.

I am suppressed with that behavior.

So my main motto at that time first recovers space. I searched many articles for claim space. Firstly I am sharing the with you all the options related to space and later on I will share the steps which help me to recover space

1) Shrink File:-
Shrink file is the most used option to recover space again from database it mainly used to recover space from log files.

Its syntax is DBCC SHRINKFILE (Database_LogFileName, Target Size MB)

2) Shrink Database:-
Shrink Database is another option which is used to shrink Database. With this option we physically gain the size.

It syntax is DBCC SHRINKDATABASE (DatabaseName, Percentage)

3) Clean Table :-

Clean table option can be used in particular condition suppose you have added a VARCHAR column in table and added data also to that column now later on you want to remove the column then After removing the column you will see the table size is unchanged to recover VARCHAR column space you can use CLEANTABLE option.

Its syntax is DBCC CLEANTABLE (DatabaseName, TableName)

4) SP_SPACEUSED:

This option will provide you space used by table or database .sp space used column will return number of rows, reserved space, Data space, index space, unused.

Its syntax is SP_SPACEUSED ‘TableName’
5) UPDATEUSAGE:-

Update usage is another most important command which update accurate Rows & pages count. Its syntax is as follows DBCC UPDATEUSAGE (DATabaseName, tableName)

6) Index Defrag:
Index Defrag is one of the main commands. It should be used in maintenance plan also this command defrag the entire index and rearrange the index.

Its syntax is as follows DBCC INDEXDEFRAG (DatabaseName, TableName, IndexName)
Other than this re indexing, re organizing index are option which help in actual database size projection. So all the above statements are related to the space information for database.

Now in my case I got help from INDEXDEFRAG command.

I did defragmentation of all the index of a table on which I have added the bit column. After running defragmentation for each index I was amazed when I run SP_SPACEUSED it recovered almost 40 GB. But the problem not ends here when I saw my disk drive it not reflect actually on disk. Then I used shrink Database command which increase my server disk free space with 40%.

So here are the steps to recover space from a table or database

1) Check current space used by table with SP_SPACEUSED command
2) Run Index defragment command INDEXDEFRAG for each index on table
3) Shrink the Log file with SHRINKFILE command

4) Shrink the database using SHRINKDATABASE command

After running above steps you will surprised. I hope these steps will help you.


Thanks & Regards

Rajat Jaiswal

Create Database with isqlW command

Hello friends,
Batch files many times reduce our work. Many tables, stored procedures, functions times we need to create same database, tables, stored procedures, and pre pop data on multiple instances or even same environment. Then in that case we just create scripts and run again and again in SQL Management Studio. But if you are in hurry and you want to run the script on single click without opening file then the easiest way is use ” ISQLW” .isqlw is the another way and we can use it with batch file.
So here are the basic steps:-
Suppose I want to create friends DB database with all theand pre pop data.
Then step 1:- Generate, a script file in which we will keep all the create table’s statements, create procedures statements and pre pop scripts.
Step2:- create a batch file for this just open notepad and save the file with extension “.bat” then the file is saved as batch file. (A batch file is executable file)
Step 3: Just write isqlw command in that as shown below
IsqlW -E -i "\fdb.sql" -o "\rajat.txt"
Where -I switch for input file – o for output file. With isqlw you can give credential also but my sql server is with mix mode so there is no problem.
After writing isqlw statement just save it and run it will create database FriendDB with all the tables, stored procedures and prepop data.
And the entire log will register in rajat.txt file.
In this way you can enjoy one click database creation with all tables’ stored procedures and pre pop data.

Thanks & Esteemed Regards
Rajat Jaiswal

Restore database from zip file in SQL SERVER

Recently one of my colleagues shared a link which is very useful in many ways.

http://clay.lenharts.net/blog/category/mssql-compressed-backup/

The link is very useful for restoring compressed backup. Hope this will help you also.

trust me it saved my whole day work because my production database size is 100 GB.

Thanks & Regards

Rajat Jaiswal

Pivot Viewer "Cricket World Cup 2011"

Hello friends,

we have left the last topic with definition of Pivot Viewer now in this post we are going to make a sample which use pivot viewer. Our sample is “Cricket World Cup 2011” so here we go
Step 1:- First checks on your machine following things are installed if not then please installed them
a) Silverlight 4 (http://silverlight.codeplex.com/ )
b) Pivot collection tool (http://www.silverlight.net/learn/pivotviewer/collection-tools/)

Step 2:- Once you have installed above tools then create a pivot collection first. The pivot collection can also be generate using code also (at run time) but here I am using excel pivot collection generator.
Step 3:- For generating collection just open excel book go to pivot collection menu when you click pivot collection just below the menu new pivot collection tools will be visible. Then just create new collection button as shown in above image.

Excel Pivot Collection
Step 4:- When you click the new collection button you will get new excel object as shown in below fig

Pivot Collection Template
Step 5:- Now add the data according to your need as I added following columns
a) Team
b) Name
c) Bating Style
d) Bowling style
e) Is Captain
f) 100’s
g) 50’s
h) Runs
I) Average
j) URL
k) Description
l) Image
Step 6: Just fill the data as per the filtration column as shown in below fig

Step 7: Now once your data is fill then just click on publish collection button on toolbar
It will generate a “CXML” file which is collection Xml file with

deep zoom images collection.

Step8:- Now your collection is ready so next thing is how to use it. So first create a VS2010 Silverlight web application project.

Step 9: Copy CXML & and images folder to your web project first
Step 10: Once you copied the CXML and Images then open your Silverlight project and drag drop Pivot control or if it is not exist in your tool manually add for that just add reference as shown below.
xmlns:pivot="clr-namespace:System.Windows.Pivot;assembly=System.Windows.Pivot"
Step 11:- Now in the code behind just assign the collection URL to pivot control. (The URL nothing but the CXML URL which you already copied in your web project in step 9) as shown below.
this.pivot.LoadCollection("http://localhost:54210/CricketWorldCup2011.cxml", "");
Step 12: Now run the project you will get following screen. On left side you will get filter criteria when you change t

hat your data will also change in centre.


Cricket World Cup 2011 Pivot
Hope the steps will be helpful and you can download code from.
Excel Data Cricket World Cup Team 2011 Data
Solution of Pivot Viewer Download Solution
Thanks & Esteemed Regards
Rajat Jaiswal

What is Pivot Viewer in Silverlight ?

Hi Folks,

Sorry for such a late post. I know you were waiting for the new post from last 2 week. So here I am with a new interesting area which will help in data visualization. “Pivot” control is great tool supported by Silverlight 4 .
I know that I am bit late to explore such a great control. I really want to thank Microsoft Pivot team for this they did such a great job.

Again our 3 basic questions WWH means,
What is pivot viewer control?
Why pivot viewer control?
And lasts but not the least How we can use & utilize it?

So, we first start with pivot viewer.
Pivot viewer is new control supported with Silverlight 4. It is a representational control you can use this control where you want a
BI functionality . Or we can say that it provide a unique way to represent data to analyse. You can filter data on the basis of various filters which you define and the transition from one filter to another filter is so smooth you worth to watch it. You can change view from normal grid view to column view.

Now if you are aware of BI (Business Intelligence) tools then I am just saying here it is one of the BI tools which help end user to get the result by applying multiple filters. You can define facts & fact category (facts are actual data & fact categories are the fields on which you can apply filter to get desire result).

Now I know you are most interested in where & how can we use? So I am taking very interesting topic which is “Cricket World Cup 2011”.

I will explain whole topic with exam

ple in next post.


Thanks
Rajat Jaiswal

Silverlight Out Of Browser Application (OOB Application)

Hello friends,

As we have seen last night how to access Video capturing devices with Silverlight 4.0.
Now today I thought why we don’t try for OOB Application means Out Of Browser Application.
Out of Browser application means you can run your Silverlight application just like an EXE we don’t require any browser like IE, Firefox etc to run our Silverlight application. So here is the step which we have to follow to get out of Browser application.

Step 1:- Check Silverlight project’s property page. On this page you will find a checkbox which is “Enable running application out of the browser” just check this checkbox as shown in below fig.

Step 2:- Click on the button “Out-Of-Browser Settings...” and you will find below screen. In this screen you set title for the application, give description for the application, define icons for application.

Step3:- Be sure if you want to access local files of computer then check “Require elevated trust when running outside the browser” checkbox. When this checkbox is checked then you can easily access the file from my documents and local directory easily it give full trust to your application.
Step4 :- you can check whether the application is installed on machine or not with app.current.InstallState you can check Installation state if not installed the you can choose app.current.Install() method to install app.

Hope you will enjoy OOB application.
Thanks & Regards
Rajat Jaiswal

Webcam or other video device access using Silverlight 4.0

Hello friends,

Microsoft Silverlight 4.0 provided another great feature of Audio & Video capturing. Today I am interested in using one of these cool features which is video capturing.
With the help of the video & audio capturing feature we can get all the capturing devices and use them. In current example there is only one source which is my laptop webcam.

So we are using 8 simple steps. With the help this simple we are capable of capturing webcam and stop capturing.

Step 1:- Create a new project of Silverlight with version Silverlight 4 version.
Step 2:- Draw a shape either rectangle or eclipse. The shape will hold the video.
Step 3:- CaptureDeviceConfiguration provides GetAvailableVideoCaptureDevice & GetDefaultvideoCaptureDevice methods.
The GetAvailableVideoCaptureDevice list out the entire available video capture device attached with your machine and GetDefaultVideoCaptureDevice provides default video capture device.

But before accessing any device from any machine we need to request device access and need to determine whether the device access is allowed or not. For determining the permission or request a device access we have following 2 methods

CaptureDeviceConfiguration.AllowedDeviceAccess CaptureDeviceConfiguration.RequestDeviceAccess

It will prompt message for accessing the capturing device when you run the program.

var webCam = CaptureDeviceConfiguration.GetDefaultVideoCaptureDevice();

Step 4:-
Once you determine the capture device configuration then you need to assign it to assign as a video capture source device of capturesource.
captureSource = new CaptureSource();

Step 5:- Once you assign the capture source’s video capture device then use a new brush utility provided by Silverlight which is Video Brush. You just need to set source of video Brush which will be your capture source.
var videoBrush = new VideoBrush();
videoBrush.SetSource(captureSource);

Step 6:- Once the video Brush’s source is set then you can fill any shape with video brush.
rectangle1.Fill = videoBrush;
ellipse1.Fill = videoBrush;

Step 7:- now to start capturing from video source you need to call start method of capture source.

captureSource.Start();

Step 8:- you can stop capturing video by stop method.

captureSource.Stop();

From the above description you can capture video from Webcam or any other video device which attached with your machine using Silverlight video capture facility. One more advantage is that you can take image also means image capturing is also possible. For this you have to define asynchronous image capture event.


In this way you can access video capture device. More than this you can download code from
download source code

Enjoy Silverlight.


Thanks & Regards
Rajat Jaiswal

how to find column or text in entire stored procedure?

Hello Friends,
Sometime we want to search a particular column or some text in entire stored procedures. For this we can utilize following two simple approaches
Approach 1:-
BEGIN TRY
DECLARE @strColumn VARCHAR(1000)
SET @strColumn ='Rajat'
SELECT DISTINCT o.name
FROM sys.syscomments c
INNER JOIN sys.objects o ON o.object_Id = c.Id
AND o.type ='P'
WHERE text like '%' + @strColumn +'%'
ORDER BY o.NAME
END TRY
BEGIN CATCH
SELECT ERROR_LINE(),ERROR_NUMBER(),ERROR_MESSAGE()
END CATCH
Approach 2:-
BEGIN TRY
DECLARE @strColumn VARCHAR(1000)
SET @strColumn ='Rajat'

SELECT SPECIFIC_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE= 'PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%' + @strColumn +'%'
ORDER BY SPECIFIC_NAME
END TRY
BEGIN CATCH
SELECT ERROR_LINE(),ERROR_NUMBER(),ERROR_MESSAGE()
END CATCH


Hope this will helpful to you somewhere.

Thanks
Rajat Jaiswal

Call SQL Server Reporting Services in ASP.NET by 5 easy steps

Hello friends,
Wish you Happy New Year.

Today I am going to share a simple way to use SQL Server Reporting Services locally. I am trying to share simple steps for creating report.
I am using North wind database and try to create a sample report which shows customer information.
Here we go
Step 1:- Create a project in my case I am using web project you can choose either web/window. In the project add a typed dataset and drag drop customers table on XSD. (You can customize schema according to your need) as shown in below fig.

Step2:- Now add a report (.RDLC)

Step 3:- When you add report then on right side a screen(Just like solution explore) will appear with name “ Web Site Data source “ in this you will find the typed dataset which we have added and when you expand the typed dataset object you will find all the table schema as show in fig.

Step 4: Now just drag drop a table from toolbars’ report item. And drag drop column from data source (type data set which shown in step3) to table column which you need to show in report.

Step 5:- Once you done with step4 now next step to call report from “Asp.net”. For this just drag drop Microsoft reporting viewer and do following code.
Below is the code
Dim sqlcon As New SqlConnection(ConfigurationManager.ConnectionStrings("connectionString").ConnectionString.ToString())
sqlcon.Open()
Dim sqlcmd As New SqlCommand
sqlcmd.CommandText = "SELECT * FROM Customers"
sqlcmd.CommandType = CommandType.Text
sqlcmd.Connection = sqlcon
Dim sda As New SqlDataAdapter
Dim ds As New myDs
sda.SelectCommand = sqlcmd
sda.Fill(ds, ds.Customers.TableName)
‘clearing report viewer
Me.ReportViewer1.LocalReport.DataSources.Clear()
‘Create datasource for report
Dim rds As New ReportDataSource()
rds.Name = "myDs_Customers"
rds.Value = ds.Customers
Me.ReportViewer1.LocalReport.DataSources.Add(rds)
‘assigning report path.
Me.ReportViewer1.LocalReport.ReportPath = "myReport.rdlc"
Me.ReportViewer1.LocalReport.Refresh()


Now run the page and you will get report as shown below.

Thanks
Rajat

Happy New Year

Wish you all happy new year.

Hope we all do best of the best in the life.Try to put our self best in all the fields.

God Bless :) Jai mata di

thanks

Rajat Jaiswal