I've seen the light at the end of the tunnel, and thankfully this time it wasn't an oncoming train.
I've finally managed to get the correct data out in a usable format and generate the reports that our accounts department required (reconciling the net number of newspapers sold, grouped by route to market and taking into account discounts and returning the correct revenues). The SQL query used is a bit of a beast (and takes half-an-hour to run) but it at least returns the correct result set.
Now I can sit down quietly and have a head-ache!
Showing posts with label SQL Server. Show all posts
Showing posts with label SQL Server. Show all posts
Tuesday, 11 March 2008
Monday, 10 March 2008
SQL Induced Brain Death
It isn't too often I find myself praising Microsoft's software, but when it comes to SQL, Microsoft's admin tools reign supreme (although MySQL's tools are catching up).
Sadly, today I've been using Sybase's SQL server, which although it is a good database product, it stinks big time when it comes to the management tools.
So this afternoon has felt a bit like stepping back in time ten years, to a time when all SQL code was written by hand instead of dragging links in a graphical environment, and testing the results meant exporting a text file for another program to deal with.
Sigh.
This makes dealing with complex queries more of a hassle than it needs to be, and accounts for the feeling of brain death that I'm now enjoying.
Sadly, today I've been using Sybase's SQL server, which although it is a good database product, it stinks big time when it comes to the management tools.
So this afternoon has felt a bit like stepping back in time ten years, to a time when all SQL code was written by hand instead of dragging links in a graphical environment, and testing the results meant exporting a text file for another program to deal with.
Sigh.
This makes dealing with complex queries more of a hassle than it needs to be, and accounts for the feeling of brain death that I'm now enjoying.
Wednesday, 24 October 2007
Dan at work - Dan vs MySQL
Todays challenge was to port the back-end code of our company Intranet from SQL Server 2000 to MySQL. This is due to our company Intranet sitting on our web development server (not the best place for it really).
In theory this shouldn't be too difficult a job as the SQL queries that we run to generate the site aren't really that complicated (no nested procedures or anything like that), the only parts that I was a bit concerned about were moving the data (especially binary objects held in database tables) from SQL Server to MySQL.
I didn't want to install MySQL on the same server as SQL Server 2000, then I remembered that I'd already set up a MySQL database on a test server for use with Merak Email server, so that can hold an extra couple of tables till I can sort out a more permanent home for it.
MySQL comes with a migration tool which takes care of moving the tables and data from the old server to the new one. It will also make relocating the database later on an absolute breeze. There were a couple of gotchas - namely codepage support on some of the databases - the data wouldn't move over until I set the target database to "international" mode.
A couple of the ASP pages needed a little tweak here or there - the original SQL Server 2000 table name was "northeast.dbo" (this was to match a remote version of the database used for our "live" client pages) - the MySQL version is "northeast_dbo" so some of the queries needed a little nudge here and there, as well as MySQL not supporting things like CAST or TOP. All told it took a little over five hours to port our company Who's Who database, Newsletters databases (three of them), Editorial Style Guide and Administration pages (including fixing up the ASP pages).
Tomorrow I've got to create a version of our Intranet site for one of our sister sites, and I get the feeling that will be followed by one for another site too. So it looks like things could be a little busy for the next couple of days (at the very least).
In theory this shouldn't be too difficult a job as the SQL queries that we run to generate the site aren't really that complicated (no nested procedures or anything like that), the only parts that I was a bit concerned about were moving the data (especially binary objects held in database tables) from SQL Server to MySQL.
I didn't want to install MySQL on the same server as SQL Server 2000, then I remembered that I'd already set up a MySQL database on a test server for use with Merak Email server, so that can hold an extra couple of tables till I can sort out a more permanent home for it.
MySQL comes with a migration tool which takes care of moving the tables and data from the old server to the new one. It will also make relocating the database later on an absolute breeze. There were a couple of gotchas - namely codepage support on some of the databases - the data wouldn't move over until I set the target database to "international" mode.
A couple of the ASP pages needed a little tweak here or there - the original SQL Server 2000 table name was "northeast.dbo" (this was to match a remote version of the database used for our "live" client pages) - the MySQL version is "northeast_dbo" so some of the queries needed a little nudge here and there, as well as MySQL not supporting things like CAST or TOP. All told it took a little over five hours to port our company Who's Who database, Newsletters databases (three of them), Editorial Style Guide and Administration pages (including fixing up the ASP pages).
Tomorrow I've got to create a version of our Intranet site for one of our sister sites, and I get the feeling that will be followed by one for another site too. So it looks like things could be a little busy for the next couple of days (at the very least).
Subscribe to:
Posts (Atom)
