Latest Posts

Report Manager Export in Pipe Delimited Format New Option Adding

On June 13, 2012, in Uncategorized, by Soheib
3

In one of the projects that I worked for, I had to export data from the report manager in Pipe Delimited format. None of the export options that come standard include Pipe Delimited. So, here is a solution that I found that might be useful to you as well. I appreciate if you leave some feedback in the comment’s area or if you have any other suggestions.

Solution:

1) The first thing we need to do is to edit the configuration file for reporting services. This file is called: RSReportServer.config

2) By default this configuration file is located in the \Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportServer

3) Save a copy of the file in case you need to roll back your changes

4) Open the original file in Notepad or a code editor. Do not use Textpad

5) Add the following code to your <Render> node:

<Extension Name="PIPE" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">        <OverrideNames>            <Name Language="en-US">TXT (Pipe delimited)</Name>        </OverrideNames>        <Configuration>          <DeviceInfo>            <FieldDelimiter>|</FieldDelimiter>            <FileExtension>txt</FileExtension>          </DeviceInfo>        </Configuration>      </Extension>

 

6) Save the file

7) Go to the Report Manager http://localhost/reports and open any report

8) Use the export option and you should see Pipe Delimited listed

image

 

I hope this is helpful to you and don’t forget to leave some feedback.

 

SQL Job Scheduler History

On June 12, 2012, in Uncategorized, by Soheib
1

Here is an easy way to get list of jobs ran in the past with status. Do you know of a better or simpler way to get the output? If so, please use the comment area to share your code.

 

   1:  CREATE  PROCEDURE [dbo].[pr_check_JobHistory] @dateparam DATETIME = NULL
   2:                                               
   3:  AS
   4:   
   5:  BEGIN
   6:    IF @dateparam IS NULL
   7:    SET @dateparam = GETDATE();
   8:    
   9:    SELECT sysjobhistory.server,
  10:           sysjobs.name
  11:           AS
  12:           job_name,
  13:           CASE sysjobhistory.run_status
  14:             WHEN 0 THEN 'Failed'
  15:             WHEN 1 THEN 'Succeeded'
  16:             ELSE '???'
  17:           END
  18:           AS
  19:           run_status,
  20:           Isnull(Substring(CONVERT(VARCHAR(8), run_date), 1, 4) + '-' +
  21:                         Substring(CONVERT(VARCHAR
  22:                                   (8), run_date), 5, 2) + '-' +
  23:                  Substring(CONVERT(VARCHAR(
  24:                            8), run_date), 7, 2), '')
  25:           AS
  26:           [Run DATE],
  27:           Isnull(Substring(CONVERT(VARCHAR(7), run_time+1000000), 2, 2) + ':'
  28:                   +
  29:                         Substring(CONVERT(VARCHAR(7), run_time+1000000), 4, 2
  30:                          )
  31:                  +
  32:                  ':' +
  33:                  Substring(CONVERT(VARCHAR(7), run_time+1000000), 6, 2), '')
  34:           AS
  35:           [Run TIME],
  36:           Isnull(Substring(CONVERT(VARCHAR(7), run_duration+1000000), 2, 2) +
  37:                   ':' +
  38:                         Substring(CONVERT(VARCHAR(7), run_duration+1000000),
  39:                         4,
  40:                         2)
  41:                  + ':' +
  42:                  Substring(CONVERT(VARCHAR(7), run_duration+1000000), 6, 2),
  43:           ''
  44:           ) AS
  45:           [Duration],
  46:           sysjobhistory.step_id,
  47:           sysjobhistory.step_name,
  48:           sysjobhistory.MESSAGE
  49:    FROM   msdb.dbo.sysjobhistory
  50:           INNER JOIN msdb.dbo.sysjobs
  51:             ON msdb.dbo.sysjobhistory.job_id = msdb.dbo.sysjobs.job_id
  52:    WHERE  sysjobhistory.run_date = Datepart(yyyy, @dateparam) * 10000 +
  53:                                           Datepart(mm, @dateparam) * 100 +
  54:                                    Datepart
  55:                                    (
  56:                                           dd, @dateparam)
  57:           --AND sysjobs.name = @JobName --remove this line if you want to show all jobs for the specified day
  58:    ORDER  BY instance_id DESC
  59:  END
 

Move database files .mdf & .ldf to different physical location

On June 8, 2012, in Uncategorized, by Soheib
16

Here is a brief and simple way to move your Data files from one location to another.

This method involves using SSMS (SQL Server Management Studio)

1- Open SSMS and locate to database you want to change Data file directory

2- Remember/Note the current Data file directory path. You can do this by Right Clicking on the database and choose Properties. Then, from the left side menu, click on Files to see the Path of your current Data file directory path. Note it down or click on Control + C to copy the path name.

image

3- Detach Database by Right clicking on the database and choosing >> Tasks >> Detach

4- A new window will appear. Click on Drop Connections check box and then click OK.

image

5- Using the windows explorer, go to your Data file directory which you copied or wrote down in step 2:

image

6- Copy/Move the Data File (.MDF) and the Transaction Log file (.LDF) to your new physical location. Note that I always recommend copying first instead of moving in case the file gets corrupted during the move, you will still have the original one to go back to.

image

7- Once you copied the two files, click on Database(s) main folder and choose Attach. Then, from the new opened window, click on Add:

image

8- Navigate to the new path where files are copied and choose the .mdf file and click on OK:

image

9- Now you can see that both the Data File (.MDF) and Transaction Log File (.LDF) are shown in the new location. Click OK and you are done (You need to refresh the Databases for the new attached database to appear again).

image

 

To improve the quality of this user-guide, please help me with some feedback or by simply commenting below.

 

Change column type in SQL Server

On June 6, 2012, in Uncategorized, by Soheib
1

There are two ways to change the column type in SQL Server:

1- Is by writing a quick code in query script.

2- Is by using the enterprise manger: SSMS (SQL Server Management Studio).

 

Method 1

Run the following code in SQL server:

ALTER TABLE <table name> ALTER COLUMN <column name> DATA TYPE

Example Code:
ALTER TABLE tblExampleTable ALTER COLUMN ID INT

See Microsoft webpage for full Alter Table syntax details:
http://msdn.microsoft.com/en-us/library/ms190273.aspx

Method 2

To do the changes using the second method follow these steps:

a- Open SSMS

b- Navigate to your table which you want to modify column

c- Find column and Right click the choose Modify

image

d- A window will open in Edit mode so that you can change the type of each column:

image

e- Click on Save once you are done.

Note that when you click Save, you might show the below warning message. Saving changes is not permitted:

image

In this case, you can change some settings to allow this save and avoid the warning. To do this simply follow these steps:

1- In SSMS, click on Tools

2- Choose Options

3- Click on the Designers section from the left side menu

4- Un-check the box for: Prevent saving changes that require table re-creation

image

Now try to save again and this time it will work.

 

 

After the successful installation of SQL Server Reporting Services and when you are trying to access the Report Manger via link: http://localhost/Reports an error message is prompted when you click on Report Builder:

image

The error message is:

To use Report Builder, you must install .Net Framework 3.5 on this computer. To visit the Microsoft Download Centre and install .Net Framework 3.5, click install.

image

 

If you already have .Net Framework 3.5 installed, then you do not need to do this. Even if you install it again, it will still prompt you with the same message again.

Solution:

This is a problem with Internet Explorer version 9. To bypass this, simply click on the compatibility icon, then try to access Report Builder again. This time it will work.

image

Now you will see the Report Builder has launched:

image

 

Hope this is helpful and send me your questions if you have problems or write in the comments.

 

Convert VHD to VMDK

On March 23, 2012, in Uncategorized, by Soheib
1

If you have .VHD virtual disk from one of the Microsoft virtual products and you want to convert it to .VMDK by VMware, this you can be done using a conversion tool. There are two methods that you can follow depending on your scenario:

Method A: VMware vCenter Converter Standalone

Use a converter software product by VMware which is free to download using the link below:

http://downloads.vmware.com/d/

You can use this if your .VHD virtual machine is:

  1. Powered-on machine or
  2. Hyper-V Server

image_thumb1

Method B: StarWind V2V Converter

If your .VHD is not in any of the above cases (i.e. backup image) then you can use StarWind software tool to do this conversion. I like this product as it is free to use, simple and does the job. You can download it from this link: http://www.starwindsoftware.com/download-starwind-converter

image_thumb13

If you run into problems or have any other related queries, please write in the comment.

 

Download SQL Express 2012

On March 23, 2012, in Uncategorized, by Soheib
1

You can download SQL 2012 Express from the following link:

Download SQL 2012 Express

SQL Server 2012 comes in different versions. To learn more about the differences, please read this article:

SQL Server 2012 Express

To learn how to do installation with step-by-step guide, read this article:

SQL Server 2012 Express Installation Tutorial

 

Convert VMDK to VHD

On March 23, 2012, in Uncategorized, by Soheib
5

If you have a .VMDK virtual disk and you want to convert it to .VHD use Method B:

For Method A, read this post

Method A: VMware vCenter Converter Standalone

You can use a converter software product by VMware which is free to download using the link below:

http://downloads.vmware.com/d/

You can use this if your .VHD virtual machine is:

  1. Powered-on machine or
  2. Hyper-V Server

image

Method B: StarWind V2V Converter

If your .VHD is not in any of the above cases (i.e. backup image) then you can use StarWind software tool to do this conversion. I like this product as it is free to use, simple and does the job. You can download it from this link: http://www.starwindsoftware.com/download-starwind-converter

image

If you run into problems or have any other related queries, please write in the comment.

 

Installing MySQL Community Server for Windows Tutorial (Workbench 5.2 CE)

On March 22, 2012, in Uncategorized, by Soheib
1

“The world’s most popular open source database”   MySQL Community Server

MySQL has significantly improved the front side of its product by providing user-friendly interfaces for easy control and administration. This is clearly demonstrated in Workbench CD 5.238 which is included in MySQL Community Edition, a free downloadable version. In this tutorial, we are going to cover the basic installation of MySQL on Windows platform.

If you find this tutorial helpful or if you have enquiries, please write in the comment section.

 

1. Download the MySQL Community Server from the link above.

2. Double click on the mysql-installer-5.5.21.0.msi Windows Installer Package.

3. You will see the first Welcome screen. Click on Install MySQL Products:

image

4. Accept License Agreement and click on Next:

image

5. If you have access to the internet, then click on Execute to check if there are newer versions of the products:

image

6. Choose the type of the setup. I am selecting Full as I want all features to be included:

image

7. Requirements is checked and in this case, Microsoft Visual C++ runtime is missing. Click Execute to download the missing requirement:

image

8. Accept the License Terms and click on Install:

image

9. A final requirement check. Click on Next to proceed:

image

10. List of products to be installed is displayed. Click on Execute to proceed:

image

11. Progress Summary is displayed:

image

12. When all products are installed successfully, click on Next:

image

13. Products which require configuration is displayed. Click Next to proceed:

image

14. Choose the type of the configuration which you require for the MySQL server. Read the description for each type to learn about the differences. I am choosing Developer Machine since I am going to use it with other products and I want the minimal memory. Click Next to proceed:

image

15. Default Network and Security settings are displayed. Enter root account password and click Next to continue:

image

16. Confirm the configuration overview and click Next:

image

17. Products are going to be installed and a completion message is displayed in the end. Click on Start MySQL Workbench to start working with MySQL. Click Finish:

image

18. MySQL Workbench will start. As this is the first time, click on New Connection to create a connection to the database so that we can query:

image

19. Type in the connection name: MydbConnection and click on OK:

image

20. You will be prompted with root account password. Enter and click OK:

image

21. Now you can see your database objects on the left side and you can write your SQL query in the middle box.

image

 

If you find this tutorial helpful or if you have enquiries, please write in the comment section.
 

Download Windows 7 Service Pack 1 (SP1) (KB976932)

On March 20, 2012, in Uncategorized, by Soheib
0

Service Pack 1 (SP1) for Windows Server 2008 R2 and Windows 7 helps keep your PCs and servers on the latest support level. It provides ongoing improvements to the Windows operating system (including previous updates delivered over Windows Update, and incremental updates to the Windows 7 and Windows Server 2008 R2 platforms based on customer and partner feedback) and makes it easy for organizations to deploy a single set of updates. Microsoft

 

There are two ways of getting hold of Window 7 Service Pack 1 (SP1):

1. From Windows Update (recommended method):

You can do this by going to Start > All Programs > Windows Update

imageimage

 

2. Standalone Version (x86, x64 and IA64)

You can download this from this link: Window 7 Service Pack 1