Stuff I Do

Get a free eBook from Microsoft Press titled “Introducing Microsoft SQL Server 2008 R2“.

Use this single line command to re-index every table in a Microsoft SQL Server database:

EXEC sp_msForEachTable
  @COMMAND1 = 'DBCC DBREINDEX ("?")'

We use an open source software package called GLPI to track our equipment and the trouble tickets associated with them. A while back I had built a ASP.net front end for our end users to enter their own trouble tickets. Since the database is populated with every computer, printer and phone in the company there was a simple little interface that allowed them to select a radio button for say printer and then a drop down box would appear with all the printers in the company with the printer’s location for them to select the printer that is having the issue.

The problem came into the fact that I thought people would be conscientious enough to actually select the device that was actually having the problem. This was not the case. Instead all the trouble tickets were being generated for the first piece of equipment in the corresponding list. A bit frustrating as we would then have to drop into the database and change the association between the ticket to the correct equipment.
This needed to stop so here is how I did it.

First off I changed the SQL query to add one more select option to the top of the list of options they could choose from. For the printers the query now looked like this for the printers:

SELECT * FROM
   (SELECT  id,
       CONCAT(name,'  {  ',comments,' } ') AS name
   FROM glpi_printers
   WHERE deleted = 0
       AND is_template = 0
       AND name <> 'shelf'
   UNION ALL
   SELECT  0,
       '--SELECT A PRINTER--'
   ) as t1
ORDER BY
   name;

This now adds the option –SELECT A PRINTER—at the top of the list with an ID value of 0.
Next I add a CompareValidator control to the drop down list that displays the equipment listing.

<asp:CompareValidator
   ID="cvDevice"
   runat="server"
   ControlToValidate="ddlSelectedDevice"
   ValueToCompare="0" type="Integer"
   operator="GreaterThan"
   ErrorMessage="<br /><strong>You must select a valid device for support!</strong>"
   display="Dynamic"
   SetFocusOnError="True"
/>

Now when they create a ticket if they skip over the dropdown list for the equipment, they will get a message telling them to select a valid device. While this does not guarantee that they will actually choose the equipment that is in need of support, I have a better chance at getting the correct equipment selected.

MySQL has a cool command called

SHOW TABLES;

which will list out all the tables in the current database.

I wanted the same ability in Microsoft SQL Server. To get the same info you would issue

SELECT NAME
FROM {databasename}..sysobjects
WHERE xtype = 'U'

Replace {databasename} with your database.

Ok so about a year ago I was fretting over the idea that MySQL was now owned buy Sun and I actually said, “…Worse of all is if Oracle had purchased them, we would never see another MySQL product again same with IBM.” Uhm yeah, well the news comes out today that Oracle is buying Sun and there by owns MySQL now too.

Interesting.

Our maintenance department uses a CMMS package called MP2 for all maintenance work orders. The software is based on a MSSQL back end which has allowed us to create many additional reports and web interfaces to help the maintenance staff handle the work orders. Some of the work orders are scheduled PMs and they have a due date. Part of their review is making sure they have the work orders closed on time so to help them out I created a SQL Server Reporting Services report that will email them a list of work orders that are due the next business day.

First I have to get a listing of all the work orders that are due the next business day and then if it is Friday, get all the work orders that are due up through Monday. Below is the TSQL to get that info:

select
	wo.wonum,
	woeqlist.location,
	equip.description,
	wo.taskdesc,
	wo.wotype,
	wo.atfirstname + ' ' + wo.atlastname as fullname,
	wo.schedfinishdate
from
	wo left join woeqlist on wo.wonum = woeqlist.wonum
		join equip on woeqlist.eqnum = equip.eqnum
where
	schedfinishdate
		between
			cast(convert(char, getdate(), 101) as datetime) + 1 and
			case
				when (select datename(dw, getdate())) = 'Friday' then cast(convert(char, getdate(), 101)as datetime) + 3
				else cast(convert(char, getdate(), 101) as datetime) + 1
			end
	and status != 'C'
order by wonum

This will return the work order number, which plant location the equipment’s name, what the title of the work order is, type of work order, who is assigned to the work order and the date it is due as a extreme reminder which day they need to complete this job.

Throw that in a Reporting Services report and we are almost ready. I don’t want to send an email unless there is actually a work order that is due for the following day. For this I create a Data Driven Subscription in the Reporting Services web page for this report.

To do this go to the report you want to send out in the Reporting Services web page and click on the Subscriptions tab. Select the New Data Driven Subscription. Give this subscription a description and select the Shared Data Sources option and click Next. Pick your shared data source and click Next. Now we will make a slightly modified query to get back an email address if any records are available.

select
	email = 'MaintenanceDept@domain.com'
from
	wo
where
	schedfinishdate
		between
			cast(convert(char, getdate(), 101) as datetime) + 1 and
			case
				when (select datename(dw, getdate())) = 'Friday' then cast(convert(char, getdate(), 101)as datetime) + 3
				else cast(convert(char, getdate(), 101) as datetime) + 1
			end
	and status != 'C'
group by email

This will return us just one record of MaintenanceDept@domain.com if there are any number of work orders due for the next business day.

On the next page in the TO: field select the database field email to include the needed email address. Set any other properties on this page and click Next. Set the days and hours that you want the report to run and you are done.

Now an email will go out only when there are work orders that are due tomorrow and if no work orders are do, no one gets an email.

In a later post, I will show a super cool user defined function that you can use to take into account holidays and other office closed events.

We are in the process of moving from a Windows 2003 server to a Windows 2008 server for our intranet and one column in one page of ASP.net code was returning System.Byte[] instead of the appropriate value. This only happened on the 2008 server and the 2003 rendered the data correctly. Apparently this is an issue when your database is MySQL, as mine is so you have to wrap the offending data in a CAST. Such as this simple example:

SELECT column1, CAST(column2, AS CHAR) FROM table

Once you have CAST the value to a CHAR it will work correctly again.

I am using ODBC connection to the MySQL database and on the 2003 box it is MySQL ODBC 3.51.12 Driver and on the 2008 box it is MySQL ODBC 5.1.4 Driver. Could it be the ODBC driver that causes the issue? Maybe but I am not willing to take the time to try different ODBC drivers for something that a CAST statement fixes.

Wow is the only thing I could think of when I saw this on my RSS reader. Then my heart sunk, as I am a fan of MySQL but not of Sun and I still wonder how Sun exists today. But further thinking has left me ok with this deal. First off it could be way worse if you think about the power players who cold have bought MySQL, Microsoft could have bought them and buried the project, Novell could have bought them and bungled it as bad as they have the other open source products they have gotten their hands on. Worse of all is if Oracle had purchased them, we would never see another MySQL product again same with IBM. So at the end of the day I guess Sun is OK.

When using MSSQL Profiler you need your Database ID in order to limit the data returned to you when watching where a process fails. Here is a quick query to return the name of your databases and the IDs associated to them.

SELECT name, database_id FROM sys.databases

Most of the documentation and samples of using parameterized queries assumes that you are using MS SQL server as your backend. In that setup you can use this sort of SQL code in the asp:SqlDataSource tag:

SelectCommand="SELECT tracking, date, author, contents, private FROM glpi_followups WHERE tracking = @tracking"

Trying to use this same code for MySQL will return nothing. To get it to work with MySQL you need to use a ? instead of the @parameter.

SelectCommand="SELECT tracking, date, author, contents, private FROM glpi_followups WHERE tracking = ?"

What I have not figured out is if you have more than one parameter how do you use make one ? seem different than the next ?. If anyone has the solution to that one, please let me know.