We have been slowly migrating databases from SQL 2000 to 2005, and as we do so we find that the Full Text Catalog isn't brought over into 2005. This causes lots of issues, one being backup stops working.
So how do you get rid of them?
We have been moving sites from a SQL 2000 machine over to a new SQL server 2005 server and we came across this error:
It is caused by the connection method in the datasource settings. Using Cursor isnt friendly with SQL 2005, but Direct makes it purrrrr like a kitten.
I am just working on migrating a clients site from a third party provider over to our servers and I thought I could use SQL Management Studio and DTS to move the database. Should be easy right? connect to third party server, right click database, copy et voila!
Not so, the other server is running on a non standard port, not the regular port 1433. So I thought well, I could connect with TCP/IP as per instructions and login. There isn't anything in the Connection panel to allow you to specify a port. So I tried servername:portnumber but it didnt work.
Today we were trying to join 2 fields from different tables with a left outer join. This means that if there is nothing in the joining table the rows are returned as null.
By default SQL wont give you a result in the joining request.
Lets have an example. You want to get categories and their parents one level deep.
Scary stuff. However its easy to fix.
I have seen many different ways of doing this, but to my knowledge this is the most accurate way. Using SQL you can have your database return the distance between 2 sets of Lat and Long points, and using some math functions also take into account the curvature of the earth.
Admittedly if you are calculating distances between 2 postcodes in the same state, within 500 miles or so of each other, the curve of the earth has practically no effect. However the distance between Sydney and San Francisco is significantly different when the world isnt flat!
Since we went through and geocoded all of the Australian postcodes and suburbs we have found ourselves finding all sorts of uses for it, and have been madly going through projects and using this data.
We have the data in our master code database, and in order to make use of it in a clients project we must copy the table and its data between databases.
Open up a SQL query window in the destination database and run the following code:
This works so long as the SQL databases are on the same server.
We recently had to work out how to display a random quote from a table in the database.
Clearly we could have created a random number with CF and then simply retrieved that row or id from the database, but what if the rows didn't have consecutive ID's?
We came up with this solution in SQL:
This brilliantly selects a row at 'random' from the given table. And yes, I know I shouldnt use select *!
We started to use Database servers on our local machines at work recently and we decided to go with SQL 2005. The production and development servers we have are all SQL 2000.
Being workstations and laptops we decided that SQL Express would be the most appropriate version for the machines as it is stripped down and uses less resources.
Unfortunately because it is a cut down version it doesnt have the capability to upgrade a SQL 2000 database to SQL 2005. You get some useful error like this:
Thankyou Bill. After some searching with no fruit we were able to get hold of a SQL 2005 developer edition, and this was much better. Now it was simply a case of taking the SQL 200 MDF file and attaching it on the SQL 2005 server. It does some clickking and whirring et voila you are now a fully funcitoning 2005 user.
After this we pushed the mdf file across to the other users local machines that were running Express.
