Posted on the September 26th, 2008 under SQL by admin
This handy little SQL select statement was given to me by one of the developers I work with. It’s used to look at a date column in a SQL table and see how many rows fall within a given week from the earliest entry. For instance you could see how many subscriptions you have week by week which in turn could be used to assess the quality of your marketing methods. Let’s use that premise for the example.
So the SQL table name is subscribe and the column will be dateAdded
select convert(datetime, convert(varchar(25), dateadd(day, 1-datepart
(weekday, dateadded),dateadded),107)),count(*)
from subscribe
group by convert(datetime, convert(varchar(25), dateadd(day, 1-datepart
(weekday, dateadded),dateadded),107))
order by 1
So this SQL statement should deliver something like this…
6/9/2008
5
13/9/2008
6
20/9/2008
9
This little statement assumes Sunday is the first day of the week but this can be remedied (I’ve been informed).
Posted on the September 25th, 2008 under SQL by admin
Background
I came across this issue when dumping the contents of a table into a CSV file from Enterprise Manager. Many people put comma’s into fields when typing their own address or something similar. This means when the CSV is opened many rows of data will be erroneous. So the solution is to replace any comma with a space so the contents of that field are still readable.
Solution
Replace(table.field, ‘,’, ‘ ‘)
*Replace the “table.field” with the applicable table name and field name
This simple bit of code fixes the comma issue and will allow the table to be dumped into a CSV for further manipulation.
So that’s how to replace a comma with a space in SQL
Posted on the September 25th, 2008 under MS Office by admin
When you create a signature in Outlook 2007 it creates 3 separate files (.htm, .txt and .rtf). To create a more custom signature you can write it in HTML – this is especially useful for when dealing with graphics and advanced formatting.
To create a signature in Outlook 2007:
In Outlook go to Tools > Options and the Mail Format tab.
Click the Signatures button.
Click the New button.
Give your signature a name like ’sig1′
Click on OK
Outlook doesn’t require any content to be added and will create the 3 individual files.
Click OK and close Outlook
Locate your signature folder:
Copy one of the following lines depending on what your operating system is.
Click on Start and then Run – paste the line you copied into the Run box and hit Enter
You should see 3 files created for your sig1 file (in .htm, .txt and .rtf formats). If you can’t see the file extensions, go to Tools / Folder Options / View and untick ‘Hide extensions for known file types’, or right-click the file and select ‘Properties’ to determine the file type.
Replace the HTML file created by Outlook: Take your HTML signature file and use it to replace the .htm file in your signature folder (i.e. save it as in this system folder, using the same filename as the .htm signature file created by Outlook ).
To start using your new signature:
Restart Outlook.
Posted on the September 24th, 2008 under Installs by admin
Trac is front end to subversion with integrated wiki for developers. Use this How To article to help you install Trac on a Windows computer that isn’t running Apache. The guide assumes you have a working Subversion repository and are using the TortoiseSVN shell on client PC’s.
Introduction
Firstly let me say Trac is a brilliant bit of software. It is basically a friendly front end to subversion which is a version control system. Trac allows you to use its in-built tracd software so you can view Subversion changes through a browser. Working with developers, this lets you track changes to code easily as well as support development goals through Trac’s project management tools.
This How To guide is by no means a replacement for the Trac documentation. The Trac site and the support provided by their developers is really very good. However I ran into a number of problems when installing the software on a standalone Windows server that wasn’t running Apache.
1. OK so let’s assume you have a working repository which we will say is located at E:\repos and a folder with all of your code located at E:\code.
2. Let’s install Python – Double click the python-2.5.2.msi file and select ‘Install for all users’. Click Next and then Next again (change the location of the Python install if you wish). Click Next once more and Python will start installing. Click Finish and that’s Python installed.
3. Now let’s install the Python SVN file (”svn-python” etc) – Double click the file and then click Next, Next again and Next once more. The installation will run and click the Finish button when prompted.
4. Then we install Python SQLite – Double click the file and then click Next, Next again and Next once more. The installation will run and click the Finish button when prompted.
5. Trac needs to be installed now – Double click the Trac exe and then click Next, Next again and Next once more. The installation will run and click the Finish button when prompted.
6. Lastly we need to install Genshi – Double click the Genshi file and then click Next, Next again and Next once more. The installation will run and click the Finish button when prompted.
Getting Trac Running
1. Create a folder called tracproject or something similar – I’d recommend putting it in the same directory as your repository.
2. Get your ez_setup.py file and move it into C:\Python25\Scripts folder(or wherever your Python install is located). Bring up a command prompt (Click on Start > Run and type ‘cmd‘). Enter the following making sure you hit Enter after each of the four lines.
cd c:\
cd pyth*
cd scr*
ez_setup.py
3. This will bring up about 10 lines of code and then leave the command prompt in the same directory. Now type the following and hit Enter.
trac-admin E:\project initenv
This will install Trac to the E:\project directory. Now the CMD window will prompt you to input four pieces of information. Look at what I used below to indicate what you should use.
Project Name [My Project] – I used “mycode” Database connection string [sqlite:db/trac.db]> Leave blank by hitting Enter Repository type [svn]> Leave blank by htting Enter Path to Repository [/path/to/repos]>E:\repos
The CMD window will install Trac based on the existing repository. The more existing revisions you have in the repository the longer this process will take, it’s indexing each change you’ve ever made. Once the install is complete the last line should say “Congratulations!” but don’t close the cmd window!. So Trac is now installed but you’re not done yet. It must be configured correctly to get the most out of it’s use.
4. To easily handle accounts in Trac I would 100% recommend installing the Account Manager Plugin available from Trac Hacks (a handy site with lots of Trac plugins) – let’s do this now. In the same cmd window (which should still be in C:\Python25\Scripts) type the following and hit Enter.
This will bring up about 12 lines of code with the last line starting “Finished processing dependencies”.
5. In Windows open up the E:\project folder and go into the conf sub-directory. There’s a file called trac.ini - open it in an editor like Notepad. You need to copy the following lines below and paste them into Notepad above the [header_logo] section.
Once that’s been pasted in save the ini file and close Notepad. In explorer go up a level to E:\project and create a new text file called passwd.txt – leave the file blank.
6. Lastly let’s give all anonymous users (those who aren’t logged in) admin access. In the same CMD window type the following and hit Enter.
Note this will not return any lines. We will remove the admin access later once you, the real admin, have created your account. And with that we can start Tracd. In CMD type the following and hit Enter.
tracd –p 8000 E:\project
7. Open up your favourite browser and paste this URL: http://localhost:8000/. You should see a link to the name you gave your project, such as mycode. Click on that and there you have Tracd – the front end of the Trac software.
8.In the top right there’s a tab called Admin – click on it. On the menu on the right click on Configuration and when the page loads select HtDigestStore where the filename is E:\project\passwd.txt and the realm is “trac“.
9. Below Configuration is Users – click on it. Create yourself an admin account. Now using the menu go to Permissions. On the right of that page you will see the Grant Permission section. Use the subject field for the username you gave yourself on the Users page and select the TRAC_ADMIN as Action. Click Add and you will see your username in the middle column of the page with TRAC_ADMIN next to it. For the subject anonymous click on the TRAC_ADMIN so it’s ticked and then click on the Remove selected items button.
10. From now on you will need to use that Admin account to Login and make changes to the Trac environment. By default anonymous users can still view all of the Tracd menu’s to see changes to the repository and view tickets.
I hope this How To guide has helped you get Trac running on your systems. Please feel free to make comments/suggestions/improvement.
My name is Myles and welcome to How To Solutions. Through this site I hope to provide some useful solutions to some easy IT bits and pieces plus some of the harder things that can be challenging.
Please feel free to leave any comments and suggestions – they will be read and responded to where possible. You can contact me here if you to ask any questions.
Welcome to How To Solutions! Please use the material on this site to help you find solutions to common IT problems. If you find anything useful then please comment on it.
Add to Favourites