SQL Job Scheduler History

On June 12, 2012, in MS SQL Server, Technical Knowledge, by Soheib
0

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
Tagged with: •
 

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

On June 8, 2012, in MS SQL Server, Technical Knowledge, by Soheib
9

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.

Tagged with: •
 

Change column type in SQL Server

On June 6, 2012, in MS SQL Server, Technical Knowledge, by Soheib
0

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.


 



							
											
		
					

					Tagged with:  • 
 
					


				

				
							
					

	

	
		

		
	
			

			
								
				

					

						

							
Report Builder Button is not showing in SQL Server (Report Manager)

						
							

								
								On March 16, 2012, 
								in MS Reporting Services, MS SQL Server, Technical Knowledge, 
								by Soheib																
							

						

						
							
							1
						

					

					
					
					
									

			

			 		
				

					

						
If you are accessing SQL Server Reporting Services in Report Manager and wondering where the Report Builder button is, this could be due to one of the following:


image


1) You are using the Express version of SQL Server.

Report Builder feature is not included in the express version of SQL Server.


2) You do not have Report Builder role assigned to the logged in user accessing SQL Server Reports:


image


To learn on how to do this, you can view a similar problem where required to change the Role setting. Jump to step number 3:


Edit Role Assignment Report Manager

							
											
		
					

					Tagged with:  •  • 
 
					


				

				
							
					

	

	
		

		
	
			

			
								
				

					

						

							
SQL Server 2012 Express Installation Tutorial

						
							

								
								On March 15, 2012, 
								in MS SQL Server, Technical Knowledge, 
								by Soheib																
							

						

						
							
							9
						

					

					
					
					
									

			

			 		
				

					

						
Before we begin the installation process, it is important that you choose the correct version of SQL Server Express. To learn more about which version to download, read the following article and download the right SQL Express version:


SQL Express 2012 Versions


After downloading the correct version, make sure that you have the right hardware and software requirements.


Environment Check


    

  • Supported OS: Server 2008 R2 SP1, Server 2008 SP2, Windows 7 SP1, and Vista SP2. 
    • 

  • Processor: 
    • 

  • Minimum for x64 installations: AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support. Processor Speed: 1.4 GHz 
    • 

  • Minimum for x86 installations: Pentium III compatible or higher. Processor Speed: 1.0 GHz 
    • 

  • Memory: Minimum: 512 MB
    • 



If you do not have the minimum requirement, you will be prompted with a message box similar to this:


(I was trying to install SQL Server 2012 Express on Windows 7 which did not have Service Pack 1 installed.)


image_thumb38_thumb


You can also perform System Configuration Check using a tool provided by the SQL Server 2012 Express application installation. Run the executable file after you have downloaded. Installation files begin to be extracted to a temporary folder, then the below window will appear. From the Planning menu on the left, choose System Configuration Checker:


image_thumb37_thumb


 


1. If you pass the minimum requirements, then you can proceed with the software installation. From the Installation menu on the left side, choose New SQL Server stand-alone installation or add features to an existing installation:


image


2. Setup will perform a quick system check. You can see below that one of the conditions failed and asks for a system reboot. This is because I have installed Window 7 Service Pack 1 (using Window Update) but did not restart my computer:


image


3. If you also required to do a reboot, then you have to start over the installation process from the beginning. In this step you have to accept the License Terms to continue:


image


4. Another quick check is done and this time the system passed all:


image


5. We can select the features that we want to install. Note that in this example, I have downloaded SQL Server 2012 Express with Advanced Services. This includes the Reporting Services:


image


6. Name the instance of your new SQL Server Express. I am also changing the Instance root directory:


image


7. You can define separate account for each service. I am leaving everything as the default:


image


8. You can also specify your Database Engine Collation. I am leaving as the default:


image


9. Choose the authentication type. It is recommended to choose Mixed mode for better security. You can also change this at a later time. Read this article to learn how to: Change SQL Server Authentication mode


If you choose mixed mode, then you have to enter a password for the SQL Server system administrator (sa) account:


image


10. If you ticked in the feature’s section to install Reporting Services, then here it will ask you whether to have the default configuration or do installation only and do Reporting Services configuration manually later. For this exercise, I am leaving as the default – to do both installation and configuration. Read the following article if you want to learn how to do configuration manually:

Installing and Configuring Reporting Services


image


11. The system will now continue with installation:


image


12. Finally, the last screen shows the installation process is completed successfully:


image


13. It may ask you to restart your computer:


image


14. We can see now that SQL Server 2012 Express with other tools installed under All Programs:


image


15. We can run the SQL Server Management Studio (SSMS) and see that we have already two databases installed. These are related to the Reporting Services.


image


16. We can also verify that the Reporting Services is running by typing the URL: http://localhost/Reports_SQLExpress


image


 


All done. Note that Reporting Services when access by IE9, will prompt you to enter username and password. To learn how to resolve this issue, please read the article:


SSRS Prompt for username and password


Reporting Services may also give you some User Access Control authorization error (UAC). To learn how to resolve this issue, read the following article:


SQL Server Reporting Services User Access Control (UAC)

							
											
		
					

					Tagged with:  •  • 
 
					


				

				
							
					

	

	
		

		
	
			

			
								
				

					

						

							
SQL Virtual Restore

						
							

								
								On March 14, 2012, 
								in MS SQL Server, Technical Knowledge, 
								by Soheib																
							

						

						
							
							0
						

					

					
					
					
									

			

			 		
				

					

						


“Rapidly mount live, fully functional database direct from backup” 




 


There are couple of ways to do a restore from a backup file in SQL Server; for instance, using SQL Server Management Studio restore wizard or manually writing your own script in T-SQL. However, these methods are not necessarily the best way to do a restore depending on the scenario you are working with; that is, if you have relatively large database backup file with not much time to do a restore. The above methods occupy large amount of space and take very long time to get a successful restored database.


I have a real work situation where I need to restore a backup database file which has a very large size 167GB and I have to do it in less than an hour. Here where this tool from RedGate (http://www.red-gate.com) comes handy. The product is called SQL Virtual Restore which as the name suggests it virtually restores the database using the backup file itself to mount as live database with fully functional access without the need to do a physical restore. You can try the product for 14days:

http://www.red-gate.com/products/dba/sql-virtual-restore/


The screenshot below shows the actual size of the backup file on the physical drive H:\


image


 


To do the restore traditionally, the physical space that is required after the restore is 245GB. Unfortunately, I do not have that much space on my hard drive, so actually the physical restore will fail:


 


image


 


So, the only way forward for me was to use SQL Virtual Restore product by RedGate and it actually worked perfectly because it did not need extra space. It used the backup file itself to mount the live database to. Here is how I did it from the beginning to the end:


Environment Check

You would need the following ready before you begin the installation process:


    

  • Windows Operating System (I am using Window 7 64bit)
    • 

  • SQL Server (I am using SQL Server 2008 R2)
    • 





How to install SQL Virtual Restore 2.3?


1. Download the product from RedGate website:

http://www.red-gate.com/products/dba/sql-virtual-restore/download


2. Unzip the compressed file: HyperBacComponents.zip


3. There are three executable files to choose from. For the SQL Virtual Restore, we only need HyperBac_Installer. Choose the correct version for your OS. In my case: HyperBac_Installer_x64.exe


image


 


4. Double click on the file HyperBac_Installer_x64.exe and follow the instructions on the screen. i have provided all the screenshots:


image


image


image


image


image


 


5. Activate the product if you have purchased it or click Exit for a 14dyas trial.


6. To open the program, go to Start > All Programs > HyperBac > SQL Virtual Restore Wizard


 


image


 


7. The wizard will start to guide you through 5 steps. Choose the SQL instance where you want the restore appears. I am choosing on my local instance:


image


 


8. Click on the Add Files button to browse through Window’s directory in order to select the backup file which you want to have it restored:


image


 


9. Keep the default selections or change according to your need. I am specifying a different directory for the database virtual files to be stored in:


image


 


10. Summary result is displayed before the final restore. You can also view the T-SQL code by clicking on View Scripts button. Click on Restore button to start the process:


image


 


11. You can see now that it has started restoring:


image


 


12. In less than an hour the restore completed successfully:


image


 


13. You can see from the completed result that we have saved 30% space. Nevertheless, I would say that I saved 245GB of space. Because if I would have done this by a physical restore, I would still have the backup file plus an additional restored database size 245GB; whereas, the virtual restore used 171GB in total including 168GB original backup file plus 3GB additional virtual database files.


Finally, we can see the restored database in SQL Management Studio:


image

							
											
		
					

					Tagged with:  •  •  •  • 
 
					


				

				
							
					

	

	
		

		
	
			

			
								
				

					

						

							
How to change Server Authentication mode in SQL Server?

						
							

								
								On March 12, 2012, 
								in MS SQL Server, Technical Knowledge, 
								by Soheib																
							

						

						
							
							8
						

					

					
					
					
									

			

			 		
				

					

						
During the installation process of SQL Server, user is prompted to choose the Authentication mode which is one of two:


    

  • Window Authentication mode
    • 

  • SQL Server and Window Authentication mode
    • 



It is recommended to use the second option – mixed mode. However, assuming that you have chosen Window Authentication mode and later on you decided to change to mix mode. This can be done and the easiest way to do is by following these few steps:


1. Open SQL Server Management Studio (SSMS) and login using Window Authentication:


image


2. Once logged in, create a new Login user or enter a password for the user sa. To do this, go to:

Security folder > Logins > sa  and double click on the selected sa user. Enter the password and click OK:


image


3. Now that we have a SQL server user, we can change the Authentication mode to mix mode. Right click on SQL Server icon and choose Properties:


image


4. From the left side menu, choose Security page and under Server authentication choose SQL Server and window Authentication mode. Click OK to finish:


image


5. A message box will appear that says you must restart SQL Server before changes take effect. To do this go to Run and type in services.msc then Click OK:


image


6. Windows Services dialog box will appear. Search for SQL Server under the list of names. Then select it and click on the re-start button.


image


7. Go back to your SSMS and create a new Database Engine connection. Under Authentication, choose, SQL Server Authentication and type sa under Login followed by the password in the password box. Then click on connect:


image


 


8. Finally, we can see that we are connected using the user sa:


image

							
											
		
					

					Tagged with:  •  •  • 
 
					


				

				
							
					

	

	
		

		
	
			

			
								
				

					

						

							
SQL Server Reporting Services 2008 Installation Tutorial

						
							

								
								On March 9, 2012, 
								in MS Reporting Services, Technical Knowledge, 
								by Soheib																
							

						

						
							
							3
						

					

					
					
					
									

			

			 		
				

					

						
This article is a step-by-step walk through of SSRS 2008 installation on a virtual machine. If you are going to install SSRS on a physical machine, then the steps are still similar and you can benefit from it.


Environment Check List


To follow the steps, you would need to have the following programs installed and configured on your machine:


    

  • Have already setup a virtual machine. I am using VMware with Windows XP as guest operating system (if you need help to do this, read article: How to install VMware Player?)
    • 

  • Have already installed SQL Server 2008 R2 on your machine (without the Reporting Services)
    • 

  • Have already created an ISO image from the installation disk
    
(If you need help to do this, read article: How to create ISO disk image?)
    • 



If you have the above installed and ready, here are the steps:


1. This first thing we need to do is to boot the virtual machine. Once the guest operating system is on, we have to mount the optical disk drive to the ISO image which we have already created and contains the installation files. To do this, right click on the CD icon located on the bottom right of your VMware Player window box and choose Settings:


image


2. Be default the connection is set to use physical drive. Choose, Use ISO image file and browse to the directory were file is stored then click OK:


image


3. If the ISO image is connected successfully, you will see the file mounted as your CD drive in My Computer window:


image


4. Right Click on the CD drive and choose Open to view the content. Browse to the appropriate folder directory (D:\English\SQLServer2008R2\Developer) and double click on the installation file Setup:


image


5. SQL Server Installation Center will open:


image


6. From the left menu, choose Installation and then click on New installation or add features:


image


7. Setup checks if you have the necessary support files. Click OK:


image


8. Click install:


image


9. The system checks if you have all the required software dependencies. If any of the rules fail for you, then setup will provide a link which contains further instructions. You have to install the prerequisites software before you can proceed. In this case, all rules passed and we click on Next:


image


10. Since we are adding the Reporting Services to an existing instance of SQL Server, we will select the second option and click Next:


image


11. The next window asks to select the new features that we want to add. Select (Tick) Reporting Services & Business Intelligence Development Studio and click Next:


image


12. Another check. Click Next:


image


13. Checking Disk Space Requirement. Click Next:


image


14. Next is server configuration which asks you to associate an Account to use for the Reporting Services. I am choosing SYSTEM (You can also create a separate account), click Next:


image


15. Choose Install, but do not configure the report server and click Next:


image


16. If you want to report errors to Microsoft, then tick the box; otherwise, click Next to continue installation:


image


17. Another check for installation rules. Click Next:


image


18. Setup is almost ready to proceed with the actual installation. Click Install:


image


19. Setup is complete and Reporting Services is added.


image


20. It is time now to configure the Reporting Services. Browse to:

Start > All Programs > Microsoft SQL Server 2008 R2 > Configuration Tools > Reporting Services Configuration Manager and click on it: (we can also see that SQL Server Business Intelligence Development Studio is installed):


image


21. You will be asked to connect to the Reporting Services. Click Connect:


image


22. If the installation is successful, you will be connected. On your left side, there is a menu which you would need to configure few. The first is Service Account, I have left this to the default which is Local Account but as it was mentioned previously, you can specify a separate account. Now go to next, Web Service URL:


image


23. Under the Web Service URL tab, it is best to leave the Virtual Directory name as defaulted to ReportServer. This is common and widely known. Click on Apply to deploy this configuration.


image


24. Under Database tab, Reporting Services store all reports and related information in separate database. Since this is the first time, we do not have them. Click on Change Database to create them.


image


25. Follow the steps in next few screens to create the reporting databases:


image


image


image


image


image


image


26. Now go to the next tab Report Manager URL. Leave the virtual directory as Reports and click on Apply to deploy the configuration. You will see once its done green checks under Results section.


For the purpose of this exercise, I am not going to skip Email Settings but you can add your details there if you want reports to be scheduled and sent to email address. Click on Exit to finish.


image


 


27. Finally, there are two important URL which allow us to access the Reporting Services:


http://localhost/Reports

(This is where all reports are going to displayed once deployed. It is also the place where users can view and run reports)


http://localhost/ReportServer

(This is the location where reports are going to be deployed to)


To check that everything is done correctly and the installation is successful, to test this, open your internet explorer and type the above URL, you should see this:


image


image

							
											
		
					

					Tagged with:  •  •  •  •  • 
 
					


				

				
							
					

	

	
		

		
	
			

			
								
				

					

						

							
Find list of tables in SQL server- search for objects

						
							

								
								On March 8, 2012, 
								in MS SQL Server, Technical Knowledge, 
								by Soheib																
							

						

						
							
							0
						

					

					
					
					
									

			

			 		
				

					

						
Of course, you can write your own SQL code to search for objects in SQL Server and here is one simple query:


SELECT


      name,


      Date=CONVERT(varchar,crdate,103),


      Type


FROM SYSOBJECTS


ORDER BY crdate DESC


 


Giving that Type (Object Types) are:


AF =       Aggregate function (CLR)


C =          CHECK constraint


D =         DEFAULT (constraint or stand-alone)


F =          FOREIGN KEY constraint


FN =       SQL scalar function


FS =        Assembly (CLR) scalar-function


FT =        Assembly (CLR) table-valued function


IF =         SQL inline table-valued function


IT =         Internal table


P =          SQL Stored Procedure


PC =       Assembly (CLR) stored-procedure


PG =      Plan guide


PK =       PRIMARY KEY constraint


R =          Rule (old-style, stand-alone)


RF =       Replication-filter-procedure


S =          System base table


SN =       Synonym


SQ =       Service queue


TA =       Assembly (CLR) DML trigger


TF =        SQL table-valued-function


TR =       SQL DML trigger


TT =        Table type


U =         Table (user-defined)


UQ =      UNIQUE constraint


V =         View


X =          Extended stored procedure


 


but you can also use third party applications that are light and easy to use. I like in particular the SQL Search 1.1 from RedGate.


You can download the application from their site:

http://www.red-gate.com/products/sql-development/sql-search/


There are two reasons why I like this tool:


One its FREE to use.


Two it has a unique feature that I like and it really saves my time at work. (read until the end) After performing a search query, the result of all objects will appear in the result section – like the snapshot below and here is a brief description of its main functionalities:


image


 


    

  1. SQL Search Tool Bar: is added to your SSMS for a convenient Search request Anew window will open when is clicked
    2. 

  2. Search Area: is where you can type your search query either full name or any part
    3. 

  3. Exact Match: if ticked then search result return only the exact match to your search phrase
    4. 

  4. Objects: you can filter the result by object type (i.e. Tables, Views)
    5. 

  5. Result Area: is the list of objects returned from your search query
    6. 

  6. Is a code over-view from the select object in the result screen.
    7. 



Now here is the best part of this tool. If you double click on one of the objects in the search result area, it will take you directly to the objects in the SSMS Object Explorer:


 


image


 


This will save a lot of time when you have long list of objects and you need to browse through them in order to fine the one that you are interested in.


Enjoy & Have Fun!!

							
											
		
					

					Tagged with:  •  •  •