Sql Server Deadlocks are a huge topic. Simply, they occur when data is updated by two different processes at the same time, prior to committing a transaction. It can also occur on a read whilst a write is occuring on the same data. This is usually created by inefficient code, holding open a transaction longer than […]
View Postntext vs nvarchar(max) in SQL 2005
A colleague today told me that using nvarchar(max) in SQL server could be more beneficial than ntext because you can do full text searching and like commands on the field. Technet has this to say: “Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size […]
View Post sqlMoving SQL 2000 to 2005 and Full Text Search problems
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? To find out what tables are indexed in the […]
View PostUsing Robocopy to keep folders clean
SQL server has always had difficulty with its backup and maintenance plans in removing old backups. Until now this has been a major issue on some of our servers, each month someone has to go through the backup folder and remove the old Transaction Log and Full Backup folders. No longer – Robocopy to the […]
View Post robocopySQL incoming tabular data stream (TDS) remote procedure call error
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 isn’t friendly with SQL 2005, but Direct makes it purrrrr like a kitten. Now if you create your […]
View PostConnecting to SQL Server on non standard port with SQL 2005 Studio
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 […]
View Postconcatenation of null values in SQL from a join of tables

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 […]
View PostSQL Login failed because the account is currently locked out
Scary stuff. However its easy to fix. For example if we are using databaseuser01 to access this database simply run this has something to do with SQL enforcing a password policy on the user, however I don’t know the details.
View PostCalculating distance in Kilometres and Miles from Latitude and Longitude
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 […]
View Post geocodingCopy tables between SQL databases
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 […]
View Post