Grouping data from multiple rows into single row

Often we come across situation where we need to group data from multiple rows into single row. E.g.: An Employee works for multiple projects, so in this case if columns are EMPLOYEE_NAME, PROJECT_DURATION, PROJECTS_WORKED and think that employee works for 10 projects. Then you would have 10 rows containing EMPLOYEE_NAME as same and then 10 different projects. If you now want to have a single row with Employee name and then 10 subsequent name delimited in single column, unfortunately we don’t have any function like GROUP_CONCAT as in MySQL for SQL server. But not to worry this can be achieved by using XML PATH. Below is the example .

Insert into EMPLOYEE_PROJECT(EMPLOYEE_NAME,PROJECT_DURATION,PROJECT_NAME)
SELECT EMPLOYEE_NAME,PROJECT_DURATION,PROJECT_NAME=
REPLACE(
REPLACE(
(SELECT REPLACE(PROJECT_NAME, ‘ ‘, ”)
AS [data()] FROM EMPLOYEE_PROJECT_DETAILS
WHERE EMPLOYEE_NAME=a.EMPLOYEE_NAME
FOR XML PATH(”)),’ ‘,’ ; ‘),”,’ ‘)
FROM EMPLOYEE_PROJECT_DETAILS a WHERE EMPLOYEE_NAME IS NOT NULL GROUP BY EMPLOYEE_NAME,PROJECT_DURATION

Small query but very useful.

Happy Coding

Technorati Tags: Group,Grouping,XML PATH

Get Information on All Jobs running on a SQL server Instance.

The SQL server Jobs which are running on a Instance are very Important. As a DBA you need to take care of these Jobs and have all information about the Job. Your will get information on Jobs is SysJobs
but again not in the way you want. So just thought of pulling the Job information and below is the stored 
Procedure for the same.
This procedure give information on 

[job_id],

[Jobs],

[Category],

[Owner],

[Schedule Name],

[enabled],

[Frequecy],

[DateCreated],

[StartDate],

[NextRunDate],

[NextRunTime]

USE YourDatabaseName
GO
IF OBJECT_ID('dbo.spGetJobInfo') IS NOT NULL
DROP PROCEDURE dbo.spGetJobInfo
GO
CREATE PROCEDURE dbo.spGetJobInfo
AS
SET NOCOUNT ON
DECLARE @jobid VARCHAR(50)
DECLARE @sql NVARCHAR(1000)
CREATE TABLE #Jobs_Schedule(
            [schedule_id] [INT],
            [name] [SYSNAME],
            [enabled] [INT],
            [freq_type] [INT],
            [freq_interval] [INT],
            [freq_subday_type] [INT],
            [freq_subday_interval] [INT],
            [freq_relative_interval] [INT],
            [freq_recurrence_factor] [INT],
            [active_start_date] [INT],
            [active_end_date] [INT],
            [active_start_time] [INT],
            [active_end_time] [INT],
            [date_created] [DATETIME],
            [desc] VARCHAR(1000),
            [next_run_date] [INT],
            [next_run_time] [INT],
            [schedule_uid]    VARCHAR(1000),
            [job_count] [INT]
            ) ON [PRIMARY]
DECLARE c1 CURSOR FOR SELECT job_id FROM msdb..sysjobs
OPEN c1
    FETCH NEXT FROM c1 INTO @jobid
    WHILE @@fetch_status=0
        BEGIN
            INSERT #Jobs_Schedule EXEC ('msdb..sp_help_jobschedule @job_id='''+ @jobid+''',@include_description=1')
            FETCH NEXT FROM c1 INTO @jobid
        END
CLOSE c1
DEALLOCATE c1

CREATE TABLE #TempJoin
(
    job_id UNIQUEIDENTIFIER,
    Jobs VARCHAR(250),
    Category VARCHAR(250),
    OwnerName  VARCHAR(100),
    ScheduleName  VARCHAR(250),
    ISenabled  CHAR(4),
    Frequency VARCHAR(250),
    Date_Created  DATETIME,
    Active_Start_Date INT,
    Next_Run_Date  INT,
    Next_Run_Time INT
)
INSERT INTO #TempJoin
(
    job_id,
    Jobs,
    Category,
    OwnerName,
    ScheduleName,
    ISenabled,
    Frequency,
    Date_Created,
    Active_Start_Date,
    Next_Run_Date,
    Next_Run_Time
)
(
SELECT 
a.[job_id],
a.[Jobs],
a.[Category],
l.[name] AS [Owner],
a.[Schedule Name],
a.[enabled],
temp.[desc] AS [Frequecy],
temp.date_created AS [DateCreated],
temp.active_start_date AS [StartDate],
temp.next_run_date AS [NextRunDate],
temp.next_run_time AS [NextRunTime]
FROM #Jobs_Schedule AS temp 
RIGHT OUTER JOIN 
(SELECT
    j.job_id,
    j.[name] AS [Jobs], 
    j.owner_sid, 
    c.[name] AS [Category], 
    js.schedule_id, 
    s.[name] AS [Schedule Name], 
    CASE j.enabled WHEN 1 THEN 'YES' ELSE 'NO' END AS [enabled] 
    FROM msdb..syscategories AS c 
    RIGHT OUTER JOIN 
    msdb..sysjobs AS j ON c.category_id = j.category_id
    LEFT OUTER JOIN 
    msdb..sysjobschedules AS js ON j.job_id = js.job_id
    LEFT OUTER JOIN 
    msdb..sysschedules AS s ON js.schedule_id = s.schedule_id)
AS a ON temp.schedule_id = a.schedule_id 
LEFT OUTER JOIN 
sys.server_principals AS l ON a.owner_sid = l.sid)

CREATE TABLE #TempJoin1
(
    Name VARCHAR(250),
    job_id UNIQUEIDENTIFIER,
    LastRunStatus VARCHAR(25),
    LastRunDate INT,
    LastRunTIme INT,
)
INSERT INTO #TempJoin1
(
    Name,
    job_id,
    LastRunStatus,
    LastRunDate,
    LastRunTIme
)
(
SELECT  
   j.[name] AS [JobName],
   j.[job_id],  
   run_status = CASE h.run_status  
   WHEN 0 THEN 'Failed' 
   WHEN 1 THEN 'Succeeded' 
   WHEN 2 THEN 'Retry' 
   WHEN 3 THEN 'Canceled' 
   WHEN 4 THEN 'In progress' 
   END, 
   h.run_date AS LastRunDate,   
   h.run_time AS LastRunTime 
   FROM msdb..sysjobhistory h  
   INNER JOIN
   msdb..sysjobs j ON h.job_id = j.job_id  
   WHERE h.instance_id IN  
   (SELECT MAX(h.instance_id)  
        FROM msdb..sysjobhistory h GROUP BY (h.job_id))
)

SELECT 
    t1.Jobs,
    t1.OwnerName,
    t1.Category,
    t1.Date_Created,
    t1.Active_Start_Date,
    t1.ISenabled,
    t2.LastRunDate,
    t2.LastRunTIme,
    t2.LastRunStatus
    FROM #TempJoin t1
    INNER JOIN #TempJoin1 t2
    ON t1.job_id = t2.job_id

DROP Table #Jobs_Schedule
DROP Table #TempJoin
DROP Table #TempJoin1
GO

T-SQL to Get Default Audit Level of SQL server Instance

I have come across situations where you need to go and look for Default Audit Level for Installed Instances or servers. You can do this by getting info in server instances. Now what if you have around 100 server and instance. For this I could find a easy way and that’s a T-SQL function to get the default Audit Level for Instance.

Function:

USE YourDatabaseName
GO
CREATE FUNCTION dbo.fn_get_default_path()
RETURNS int
AS
BEGIN
    DECLARE @instance_name nvarchar(200), @system_instance_name nvarchar(200), @registry_key nvarchar(512), @path int, @value_name nvarchar(20);

    SET @instance_name = COALESCE(convert(nvarchar(20), serverproperty(‘InstanceName’)), ‘MSSQLSERVER’);

    — sql 2005/2008 with instance
    EXEC master.dbo.xp_regread N’HKEY_LOCAL_MACHINE’, N’Software\Microsoft\Microsoft SQL Server\Instance Names\SQL’, @instance_name, @system_instance_name output;
    SET @registry_key = N’Software\Microsoft\Microsoft SQL Server\’ + @system_instance_name + ‘\MSSQLServer’;

    SET @value_name = N’AuditLevel’
    EXEC master.dbo.xp_regread N’HKEY_LOCAL_MACHINE’, @registry_key, @value_name, @path output;   
    RETURN @path;
END
GO

Now to get the value run the below script:

DECLARE @Temp varchar(max),@Temp1 varchar(max)
SET @Temp = dbo.fn_get_default_path()
If @Temp = 0
Begin
Set @Temp1 = ‘NONE’
END
IF @Temp = 1
BEGIN
SET @Temp1 = ‘Successful Logins Only’
END
If @Temp = 2
BEGIN
SET @Temp1 = ‘Failed Logins Only’
END
IF @Temp = 3
BEGIN
SET @Temp1 = ‘Both Successful and Failed Logins’
END
SELECT @Temp1
GO

Get Free Disk Space for SQL server 2000

As a DBA you always need to monitor various metrics of a Database server. Disk space is one such metric you gotta watch out for. Monitoring Disk space for SQL server 2000 is bit difficult through script. Just happened to write a Script for SQL server 2000 which gives you free disk space in GB for all existing drives on database server.

SET NOCOUNT ON
DECLARE @TEMP AS VARCHAR(4000)
DECLARE @MBfree int
DECLARE @CMD1 varchar(1000)
SET @MBfree = 0
SET @CMD1 =
CREATE TABLE #tbl_xp_fixeddrives
(Drive varchar(2) NOT NULL,
[FreeSpace] int NOT NULL)
INSERT INTO #tbl_xp_fixeddrives(Drive, [FreeSpace])
EXEC master.dbo.xp_fixeddrives
SELECT @TEMP =
CREATE FUNCTION dbo.GetDriveSize (@driveletter CHAR(1))
 RETURNS NUMERIC(20)
 BEGIN
  DECLARE  @rs INTEGER, @fso INTEGER, @getdrive VARCHAR(13), @drv INTEGER,
@drivesize VARCHAR(20)
  SET @getdrive = ”GetDrive(“” + @driveletter + ””)”
  EXEC @rs = sp_OACreate ”Scripting.FileSystemObject”, @fso OUTPUT
  IF @rs = 0
   EXEC @rs = sp_OAMethod @fso, @getdrive, @drv OUTPUT
  IF @rs = 0
   EXEC @rs = sp_OAGetProperty @drv,”TotalSize”, @drivesize OUTPUT
  IF @rs<> 0
   SET @drivesize = NULL
  EXEC sp_OADestroy @drv
  EXEC sp_OADestroy @fso
  RETURN @drivesize
 END’
EXEC(@TEMP)
SELECT drive+‘:’+‘ ‘+
CAST(CAST((FreeSpace/(((dbo.GetDriveSize(drive))/1024)/1024))*100.0 as int) as VARCHAR(4000))+‘%’
from #tbl_xp_fixeddrives
DROP TABLE #tbl_xp_fixeddrives
SELECT @TEMP = ‘USE ‘
SELECT @TEMP = @TEMP+‘master ‘+‘DROP FUNCTION dbo.GetDriveSize’
EXEC (@TEMP)
SET NOCOUNT OFF
GO

Custom Reports using Crystal Reports and MySQL

1 Introduction

Reporting is one of the main important factors and features and part of IT Sector. Reports are important in various aspects such as Forecasting of Business, Trend Analysis, Service review, display statistics to client, track records etc.

We have various reporting tools in market like Microsoft SQL Server Reporting Services, Microsoft Excel, and Crystal Reports etc.

Select which product of reports or version is the not the question to be asked, But how well can be the report presented is the question to be asked.

So I have decided to take you through some of the key features and how a customized report can be prepared and present using crystal reports and the database as MySQL. This can also be done with other databases as well.

2 About products/Technologies

Whenever we are working on some technologies we need to know something about the technology/product we are working on. So let us get to know some basic information about the products.

2.1 About Crystal Reports

Crystal Reports is designed to work with your database to help you analyze and interpret important information. Crystal Reports makes it easy to create simple reports, and, it also has the comprehensive tools you need to produce complex or specialized reports.

Create any report you can imagine

Crystal Reports is designed to produce the report you want from virtually any data source. Built-in report experts guide you step by step through building reports and completing common reporting tasks. Formulas, cross-tabs, sub reports, and conditional formatting help make sense of data and uncover important relationships that might otherwise be hidden. Geographic maps and graphs communicate information visually when words and numbers are simply not enough.

Extend reporting to the Web

The flexibility of Crystal Reports doesn’t end with creating reports — your reports can be published in a variety of formats including Microsoft Word and Excel, E-mail and even over the Web. Advanced Web reporting lets other members of your workgroup view and update shared reports inside their web browser.

 

Incorporate reports into applications

Application and web developers can save time and meet their users’ needs by integrating the report processing power of Crystal Reports into their database applications. Support for most popular development languages makes it easy to add reporting to any application.

Whether it’s the web master in IT, the promotion manager in marketing, the database administrator in finance or the CEO, Crystal Reports is a powerful tool designed to help everyone analyze and interpret the information that’s important to them.

2.2 About MySQL

The MySQL database has become the world’s most popular open source database because of its high performance, high reliability and ease of use. It is also the database of choice for a new generation of applications built on the LAMP stack (Linux, Apache, MySQL, PHP / Perl / Python.) Many of the world’s largest and fastest-growing organizations including Facebook, Google, Adobe, Alcatel Lucent and Zappos rely on MySQL to save time and money powering their high-volume Web sites, business-critical systems and packaged software.

MySQL runs on more than 20 platforms including Linux, Windows, Mac OS, Solaris, HP-UX, IBM AIX, giving you the kind of flexibility that puts you in control. Whether you’re new to database technology or an experienced developer or DBA, MySQL offers a comprehensive range of database tools, support, training and consulting services to make you successful.

2.3 Creation of Crystal Reports

Now we are done with a brief introduction to the technologies we shall now go ahead with creating crystal reports.

2.3.1                     Installing MySQL connector for ODBC connections.

You can download the MySQL connector from the below link.

http://dev.mysql.com/downloads/connector/odbc/5.1.html

Select the appropriate msi package from above link and download it on to your machine where you have installed crystal reports. Install the connector using instructions provided by the installer during installation.

2.3.2                     Creating DSN to establish connectivity from Crystal Reports to MySQL server.

a)     Go to control panel and select Administrative tools.

b)    Under Administrative tools select Data Sources (ODBC) which will open up ODBC Data Source Administrator.

c)     Under System DSN click on Add and select MySQL ODBC 5.1 driver and click finish

d)    When you click on finish MySQL Connector/ODBC Data Source Configuration wizard appears. Fill up the appropriate connection parameters to MySQL server and click ok.

e)     Now click on OK and close ODBC Data Source Administrator.

That’s all we are done with DSN connection.

2.3.3                     Designing Crystal Report.

Now we are done with creation of DSN to establish connectivity between crystal reports and MySQL server, let us start designing a report. Below steps would help you to create a simple crystal report and export it to format of your choice from available choices

a)     Go to Start à All Programs à Crystal Reports 2008 and select crystal reports 2008.exe.

b)    On Start Page Under “Start a New Report” select Report Wizard to open up Standard Report Creation Wizard.

c)     In Standard Report Creation Wizard under Available Data Sources, Expand Create New Connection, search for ODBC (RDO) and expand it, which will open ODBC (RDO) screen.

d)    In ODBC (RDO) screen under available Data Source Name, select the DSN which was created in Step 2.3.2 and click next. Enter username and password and select the database and click finish.

e)     Now you will be able to find the ODBC connection, expand it and click on Add Command, which will open up “Add command to Report” screen.

f)     Enter the SQL Command through which you want to extract custom reports from MySQL database and click ok.

g)    Now you will be able to see the ODBCàCommand under Selected Tables menu in “Add command to Report”, now click next.

h)     You can now see available columns from your query in Available Fields. Select Fields to Display in report and click on the Arrow marks to Move the Fields from Available Fields to Fields to Display and click next.

i)      Now if you want to group the data by any of the field you can select the field and click on arrow mark to group the output data and click next.

j)      If you want to summarize the fields you can select the field from which you want to summarize the data and click next.

k)     Select the criteria to sort the data depending upon the Group and select next.

l)      Select the chart type if at you want a chart in Report and click next.

m)   If you want any of your fields to be displayed as a subset of main data select the fields and click next else just click next.

n)     Select the template in which you want your data to be displayed and click Finish.

o)    Now you will be able to see your report preview and design tabs. You can go to design tab and customize the look of the report.

p)    If you want to export the report, then go to fileàexportàexport report.

q)    Select the export format in which you want your report and select destination and click ok.

Bingo you are done with your report.

3 Conclusion

We are now done with creation of customized report preparation. Next time you want to use the report just open the .rpt file which you saved and go to report menu and click refresh data and export the report. This was just a basic creation of a report. You can just play like anything and prepare more complicated and customized reports using crystal report features like functions, parameters etc.

You can also use any RDBMS as back end to extract the report with appropriate drivers and connectors installed.

 

DBCC Shrinkfile unable to Shrinkfile

Many times it happens that you will not able to shrink datafile using DBCC Shrinkfile command. There may be various reason/error for it. One of them says as mentioned below:

“file id of database id cannot be shrunk as it is either being shrunk by another process or is empty”

This happens when already a DBCC shrinkfile has been run and due to some reason it was unable to shrink the file and the internal files are not re-organized or file has not grown to shrink. Or SQL server somewhere it feels that the file you are shrinking is in middle of Shrinking process

One of the solution for this file is to add some MB of data to the file which you are trying to shrink and then use DBCC Shrinkfile. This will work as adding a few MB to the data file resets an internal counter or switch that tells it it’s not in the middle of a shrink now